MySQL/PostgreSQL with Express

SQL databases like MySQL and PostgreSQL are the traditional choice for many applications. They use structured tables with relationships between them. In this chapter, we'll learn how to integrate SQL databases with Express using popular libraries.

Choosing Your SQL Database

Database	When to Use	Node.js Driver
MySQL	Web applications, CMS, e-commerce	`mysql2`, `mysql`
PostgreSQL	Complex queries, geospatial data, enterprise apps	`pg`

Think of SQL databases as spreadsheets with superpowers – they enforce structure, maintain relationships, and let you ask complex questions about your data.

Approach 1: Using Database Drivers (Raw SQL)

This gives you full control but requires writing SQL queries.

MySQL with mysql2

npm install mysql2

<!-- Create connection pool -->
const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb',
  waitForConnections: true,
  connectionLimit: 10
});

<!-- Use in routes -->
app.get('/users', async (req, res) => {
  try {
    const [rows] = await pool.query('SELECT * FROM users');
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

app.post('/users', async (req, res) => {
  try {
    const { name, email } = req.body;
    const [result] = await pool.query(
      'INSERT INTO users (name, email) VALUES (?, ?)',
      [name, email]
    );
    res.status(201).json({ id: result.insertId, name, email });
  } catch (err) {
    res.status(400).json({ error: err.message });
  }
});

PostgreSQL with pg

npm install pg

const { Pool } = require('pg');

const pool = new Pool({
  user: 'postgres',
  host: 'localhost',
  database: 'mydb',
  password: 'password',
  port: 5432
});

app.get('/users', async (req, res) => {
  try {
    const { rows } = await pool.query('SELECT * FROM users');
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

Approach 2: Using an ORM (Sequelize)

ORMs let you work with databases using JavaScript objects instead of writing SQL. Sequelize is a popular ORM for Node.js that supports MySQL, PostgreSQL, SQLite, and more.

npm install sequelize mysql2

<!-- models/index.js -->
const { Sequelize, DataTypes } = require('sequelize');

const sequelize = new Sequelize('mydb', 'root', 'password', {
  host: 'localhost',
  dialect: 'mysql' <!-- or 'postgres' -->
});

const User = sequelize.define('User', {
  name: { type: DataTypes.STRING, allowNull: false },
  email: { type: DataTypes.STRING, allowNull: false, unique: true },
  age: { type: DataTypes.INTEGER }
});

<!-- Sync database (create tables) -->
sequelize.sync({ alter: true });

module.exports = { User, sequelize };

<!-- Using Sequelize in routes -->
const { User } = require('./models');

app.get('/users', async (req, res) => {
  try {
    const users = await User.findAll();
    res.json(users);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

app.post('/users', async (req, res) => {
  try {
    const user = await User.create(req.body);
    res.status(201).json(user);
  } catch (err) {
    res.status(400).json({ error: err.message });
  }
});

Choosing Between Raw SQL and ORM

Raw SQL	ORM (Sequelize)
Full control over queries	Faster development, less SQL to write
Best performance for complex queries	Built-in validation and relationships
Steeper learning curve (need SQL knowledge)	Easier to switch databases

Two Minute Drill

SQL databases use structured tables with relationships.

Use `mysql2` or `pg` for raw SQL queries with connection pooling.

Use Sequelize ORM for a more JavaScript-friendly approach.

Always use parameterized queries to prevent SQL injection.

Choose raw SQL for complex queries, ORM for rapid development.

Take Quiz

Need more clarification?

Drop us an email at career@quipoinfotech.com

Welcome to Quipoin

Quipoin Menu

MySQL/PostgreSQL with Express

Need more clarification?