Express.js SQL Databases

MySQL, PostgreSQL, SQLite, and ORMs — connect Express to relational databases with confidence

MySQL PostgreSQL SQLite Sequelize

Introduction to SQL Databases in Express.js
Using MySQL with Express.js
Using PostgreSQL with Express.js
Using SQLite with Express.js
ORM Deep Dive: Working with SQL Databases
Best Practices for SQL Databases in Express
Complete Project: Task Management API
Conclusion
Basic MCQs
Advanced MCQs
Interview Q&A

1. Introduction to SQL Databases in Express.js

1.1 Relational Databases vs. NoSQL

When building Express.js applications, choosing the right database is a critical decision. The two main categories are SQL (relational) and NoSQL (non-relational) databases.

Feature	SQL Databases	NoSQL Databases
Data Structure	Tables with rows and columns	Documents (JSON-like), key-value pairs, or graphs
Schema	Fixed, predefined schema	Flexible, schema-less
Relationships	Foreign keys, JOIN operations	Embedded documents or references
Query Language	SQL (Structured Query Language)	Varies by database
Examples	MySQL, PostgreSQL, SQLite	MongoDB, Redis
Best For	Complex queries, transactions, data integrity	Rapid iteration, unstructured data, scalability

When to choose SQL:

Your data has clear relationships (users ↔ posts ↔ comments)
You need ACID transactions and data integrity
You need complex queries with JOINs and aggregations
You're building financial, e-commerce, or reporting systems

When to choose NoSQL:

Your data structure is unpredictable or changes frequently
You need horizontal scaling across many servers
You're building real-time applications or caching layers
You prefer working with JSON-like objects

1.2 Overview of MySQL, PostgreSQL, and SQLite

Database	Type	License	Best For	Key Features
MySQL	Relational	GPL	Web applications, e-commerce	Widespread adoption, replication, mature ecosystem
PostgreSQL	Relational	PostgreSQL License	Complex queries, data integrity	Advanced data types, JSON support, concurrency
SQLite	Embedded	Public Domain	Prototyping, mobile apps, desktop apps	Serverless, zero-configuration, lightweight

1.3 How to Choose the Right Database for Your Project

Choose MySQL when:

You need a reliable, battle-tested database for web applications
You're deploying to shared hosting environments
You want the largest community and ecosystem
Your application uses read-heavy operations

Choose PostgreSQL when:

You need advanced features like custom data types or full-text search
Data integrity and concurrency are critical
You're building analytical or reporting systems
You want native JSON support alongside relational data

Choose SQLite when:

You're building a prototype or learning project
Your application is serverless (CLI tools, desktop apps)
You need an embedded database for mobile or IoT devices
You want zero configuration and easy deployment

2. Using MySQL with Express.js

2.1 Installing MySQL and mysql2 Driver

Local MySQL Installation:

Bash# Ubuntu/Debian
sudo apt-get install mysql-server

# macOS (Homebrew)
brew install mysql
brew services start mysql

# Windows
# Download installer from https://dev.mysql.com/downloads/installer/

Install Node.js dependencies:

Bashnpm install mysql2

The mysql2 driver is recommended over the older mysql package because it supports promises, prepared statements, and is more performant.

2.2 Setting Up a Connection Pool

Connection pools maintain multiple database connections, improving performance for applications with many concurrent requests.

config/database.js:

JavaScript — config/database.jsconst mysql = require('mysql2/promise');

// Create a connection pool
const pool = mysql.createPool({
    host: process.env.DB_HOST || 'localhost',
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || '',
    database: process.env.DB_NAME || 'myapp',
    waitForConnections: true,
    connectionLimit: 10,      // Maximum connections in pool
    queueLimit: 0,            // Unlimited queueing
    enableKeepAlive: true,
    keepAliveInitialDelay: 0
});

// Test the connection
const testConnection = async () => {
    try {
        const connection = await pool.getConnection();
        console.log('✅ MySQL connected successfully');
        connection.release();
        return true;
    } catch (error) {
        console.error('❌ MySQL connection failed:', error.message);
        return false;
    }
};

module.exports = { pool, testConnection };

.env file:

Text — .envDB_HOST=localhost
DB_USER=root
DB_PASSWORD=your_password
DB_NAME=myapp

2.3 Executing Raw SQL Queries

Raw SQL queries give you full control over your database interactions.

models/userModel.js:

JavaScript — models/userModel.jsconst { pool } = require('../config/database');

// CREATE - Insert a user
const createUser = async (userData) => {
    const { name, email, age } = userData;

    const query = `
        INSERT INTO users (name, email, age, created_at)
        VALUES (?, ?, ?, NOW())
    `;

    const [result] = await pool.execute(query, [name, email, age]);
    return { id: result.insertId, ...userData };
};

// READ - Get all users
const getAllUsers = async () => {
    const [rows] = await pool.query('SELECT * FROM users ORDER BY created_at DESC');
    return rows;
};

// READ - Get user by ID
const getUserById = async (id) => {
    const [rows] = await pool.execute('SELECT * FROM users WHERE id = ?', [id]);
    return rows[0] || null;
};

// READ - Search users
const searchUsers = async (searchTerm) => {
    const query = `
        SELECT * FROM users
        WHERE name LIKE ? OR email LIKE ?
        ORDER BY name
    `;
    const searchPattern = `%${searchTerm}%`;
    const [rows] = await pool.execute(query, [searchPattern, searchPattern]);
    return rows;
};

// UPDATE - Update user
const updateUser = async (id, userData) => {
    const { name, email, age } = userData;

    const [result] = await pool.execute(
        'UPDATE users SET name = ?, email = ?, age = ? WHERE id = ?',
        [name, email, age, id]
    );

    return result.affectedRows > 0;
};

// DELETE - Delete user
const deleteUser = async (id) => {
    const [result] = await pool.execute('DELETE FROM users WHERE id = ?', [id]);
    return result.affectedRows > 0;
};

module.exports = {
    createUser,
    getAllUsers,
    getUserById,
    searchUsers,
    updateUser,
    deleteUser
};

Using in routes:

JavaScript — routes/users.jsconst express = require('express');
const router = express.Router();
const userModel = require('../models/userModel');

// GET all users
router.get('/users', async (req, res) => {
    try {
        const users = await userModel.getAllUsers();
        res.json({ success: true, data: users });
    } catch (error) {
        res.status(500).json({ success: false, error: error.message });
    }
});

// POST create user
router.post('/users', async (req, res) => {
    try {
        const { name, email, age } = req.body;

        if (!name || !email) {
            return res.status(400).json({
                success: false,
                error: 'Name and email are required'
            });
        }

        const newUser = await userModel.createUser({ name, email, age });
        res.status(201).json({ success: true, data: newUser });
    } catch (error) {
        res.status(500).json({ success: false, error: error.message });
    }
});

2.4 Using an ORM: Sequelize with MySQL

Sequelize is a promise-based ORM that supports MySQL, PostgreSQL, SQLite, and more.

Installation:

Bashnpm install sequelize mysql2

config/sequelize.js:

JavaScript — config/sequelize.jsconst { Sequelize } = require('sequelize');

const sequelize = new Sequelize(
    process.env.DB_NAME,
    process.env.DB_USER,
    process.env.DB_PASSWORD,
    {
        host: process.env.DB_HOST,
        dialect: 'mysql',
        logging: process.env.NODE_ENV === 'development' ? console.log : false,
        pool: {
            max: 10,
            min: 0,
            acquire: 30000,
            idle: 10000
        }
    }
);

const testConnection = async () => {
    try {
        await sequelize.authenticate();
        console.log('✅ Sequelize connected to MySQL');
    } catch (error) {
        console.error('❌ Unable to connect:', error);
    }
};

module.exports = { sequelize, testConnection };

models/User.js (Sequelize model):

JavaScript — models/User.jsconst { DataTypes } = require('sequelize');
const { sequelize } = require('../config/sequelize');

const User = sequelize.define('User', {
    id: {
        type: DataTypes.INTEGER,
        autoIncrement: true,
        primaryKey: true
    },
    name: {
        type: DataTypes.STRING(100),
        allowNull: false,
        validate: {
            len: [2, 100],
            notEmpty: true
        }
    },
    email: {
        type: DataTypes.STRING(255),
        allowNull: false,
        unique: true,
        validate: {
            isEmail: true,
            notEmpty: true
        }
    },
    age: {
        type: DataTypes.INTEGER,
        validate: {
            min: 0,
            max: 120,
            isInt: true
        }
    }
}, {
    tableName: 'users',
    timestamps: true,
    createdAt: 'created_at',
    updatedAt: 'updated_at'
});

module.exports = User;

2.5 Complete CRUD Example with MySQL

server.js:

JavaScript — server.jsconst express = require('express');
const { sequelize } = require('./config/sequelize');
const User = require('./models/User');

const app = express();
app.use(express.json());

// Sync database (create tables if they don't exist)
sequelize.sync({ alter: true })
    .then(() => console.log('Database synced'))
    .catch(err => console.error('Sync error:', err));

// ============== CRUD Routes ==============

// CREATE
app.post('/api/users', async (req, res) => {
    try {
        const user = await User.create(req.body);
        res.status(201).json(user);
    } catch (error) {
        if (error.name === 'SequelizeValidationError') {
            return res.status(400).json({ errors: error.errors });
        }
        res.status(500).json({ error: error.message });
    }
});

// READ all
app.get('/api/users', async (req, res) => {
    const { limit = 10, offset = 0 } = req.query;
    const users = await User.findAndCountAll({
        limit: parseInt(limit),
        offset: parseInt(offset),
        order: [['created_at', 'DESC']]
    });
    res.json({
        total: users.count,
        page: Math.floor(offset / limit) + 1,
        data: users.rows
    });
});

// READ one
app.get('/api/users/:id', async (req, res) => {
    const user = await User.findByPk(req.params.id);
    if (!user) return res.status(404).json({ error: 'User not found' });
    res.json(user);
});

// UPDATE
app.put('/api/users/:id', async (req, res) => {
    const user = await User.findByPk(req.params.id);
    if (!user) return res.status(404).json({ error: 'User not found' });

    await user.update(req.body);
    res.json(user);
});

// DELETE
app.delete('/api/users/:id', async (req, res) => {
    const user = await User.findByPk(req.params.id);
    if (!user) return res.status(404).json({ error: 'User not found' });

    await user.destroy();
    res.status(204).send();
});

app.listen(3000, () => console.log('Server running on port 3000'));

3. Using PostgreSQL with Express.js

3.1 Installing PostgreSQL and pg Driver

Local PostgreSQL Installation:

Bash# Ubuntu/Debian
sudo apt-get install postgresql postgresql-contrib
sudo systemctl start postgresql

# macOS (Homebrew)
brew install postgresql
brew services start postgresql

# Create a database
createdb myapp

Install Node.js dependencies:

Bashnpm install pg pg-hstore

The pg package is the official PostgreSQL client for Node.js.

3.2 Connection Configuration

config/postgres.js:

JavaScript — config/postgres.jsconst { Pool } = require('pg');

const pool = new Pool({
    host: process.env.DB_HOST || 'localhost',
    port: process.env.DB_PORT || 5432,
    user: process.env.DB_USER || 'postgres',
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME || 'myapp',
    max: 20,           // Maximum pool size
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 2000
});

// Event handlers
pool.on('connect', () => console.log('Connected to PostgreSQL'));
pool.on('error', (err) => console.error('Unexpected error on idle client', err));

const testConnection = async () => {
    try {
        const client = await pool.connect();
        const result = await client.query('SELECT NOW()');
        console.log('✅ PostgreSQL connected:', result.rows[0].now);
        client.release();
        return true;
    } catch (error) {
        console.error('❌ PostgreSQL connection failed:', error.message);
        return false;
    }
};

module.exports = { pool, testConnection };

3.3 Raw SQL Queries with pg

models/productModel.js:

JavaScript — models/productModel.jsconst { pool } = require('../config/postgres');

// Create products table (run once)
const initTable = async () => {
    const query = `
        CREATE TABLE IF NOT EXISTS products (
            id SERIAL PRIMARY KEY,
            name VARCHAR(200) NOT NULL,
            price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
            category VARCHAR(100),
            in_stock BOOLEAN DEFAULT true,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    `;
    await pool.query(query);
};

// CREATE
const createProduct = async (product) => {
    const { name, price, category, in_stock } = product;
    const query = `
        INSERT INTO products (name, price, category, in_stock)
        VALUES ($1, $2, $3, $4)
        RETURNING *
    `;
    const result = await pool.query(query, [name, price, category, in_stock]);
    return result.rows[0];
};

// READ with filtering and pagination
const getProducts = async (filters = {}) => {
    let query = 'SELECT * FROM products WHERE 1=1';
    const values = [];
    let paramCount = 1;

    if (filters.category) {
        query += ` AND category = $${paramCount++}`;
        values.push(filters.category);
    }

    if (filters.minPrice) {
        query += ` AND price >= $${paramCount++}`;
        values.push(filters.minPrice);
    }

    if (filters.maxPrice) {
        query += ` AND price <= $${paramCount++}`;
        values.push(filters.maxPrice);
    }

    query += ' ORDER BY created_at DESC';

    if (filters.limit) {
        query += ` LIMIT $${paramCount++}`;
        values.push(filters.limit);
    }

    if (filters.offset) {
        query += ` OFFSET $${paramCount++}`;
        values.push(filters.offset);
    }

    const result = await pool.query(query, values);
    return result.rows;
};

// READ one
const getProductById = async (id) => {
    const query = 'SELECT * FROM products WHERE id = $1';
    const result = await pool.query(query, [id]);
    return result.rows[0];
};

// UPDATE
const updateProduct = async (id, updates) => {
    const fields = [];
    const values = [];
    let paramCount = 1;

    for (const [key, value] of Object.entries(updates)) {
        if (value !== undefined) {
            fields.push(`${key} = $${paramCount++}`);
            values.push(value);
        }
    }

    if (fields.length === 0) return null;

    values.push(id);
    const query = `
        UPDATE products
        SET ${fields.join(', ')}, updated_at = CURRENT_TIMESTAMP
        WHERE id = $${paramCount}
        RETURNING *
    `;

    const result = await pool.query(query, values);
    return result.rows[0];
};

// DELETE
const deleteProduct = async (id) => {
    const result = await pool.query('DELETE FROM products WHERE id = $1 RETURNING id', [id]);
    return result.rowCount > 0;
};

module.exports = {
    initTable,
    createProduct,
    getProducts,
    getProductById,
    updateProduct,
    deleteProduct
};

3.4 Using an ORM: Sequelize with PostgreSQL

Sequelize works with PostgreSQL using the same API as MySQL—just change the dialect.

config/sequelize-pg.js:

JavaScript — config/sequelize-pg.jsconst { Sequelize } = require('sequelize');

const sequelize = new Sequelize(process.env.DATABASE_URL, {
    dialect: 'postgres',
    dialectOptions: {
        ssl: process.env.NODE_ENV === 'production' ? {
            require: true,
            rejectUnauthorized: false
        } : false
    },
    logging: false,
    pool: {
        max: 10,
        min: 0,
        acquire: 30000,
        idle: 10000
    }
});

module.exports = sequelize;

.env for PostgreSQL:

Text — .envDATABASE_URL=postgresql://username:password@localhost:5432/myapp

3.5 Alternative: Using Prisma ORM with PostgreSQL

Prisma is a modern ORM that offers excellent TypeScript support and an intuitive schema definition.

Installation:

Bashnpm install prisma --save-dev
npx prisma init

prisma/schema.prisma:

Prisma — schema.prismagenerator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  posts     Post[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Using Prisma Client:

JavaScriptconst { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

// Create user with posts
const user = await prisma.user.create({
    data: {
        email: 'john@example.com',
        name: 'John Doe',
        posts: {
            create: [
                { title: 'First Post', content: 'Hello World!' },
                { title: 'Second Post', published: true }
            ]
        }
    },
    include: { posts: true }
});

// Query with relations
const usersWithPosts = await prisma.user.findMany({
    where: { posts: { some: { published: true } } },
    include: { posts: true }
});

4. Using SQLite with Express.js

4.1 What Makes SQLite Special (Serverless Database)

SQLite is unique among databases because it's serverless—the database engine runs directly within your application. Key characteristics:

No separate server process to install and manage
Zero configuration — just open a file
Lightweight — the entire library is < 1MB
ACID compliant with full transaction support
Ideal for prototyping, desktop apps, mobile apps, and embedded systems

When to use SQLite vs MySQL/PostgreSQL:

Use SQLite	Use MySQL/PostgreSQL
Prototyping and learning	Production web applications
High concurrency is low (< 100 writes/sec)	Many concurrent writes expected
Single-machine deployment	Multiple servers / horizontal scaling
Embedded or desktop applications	Client-server architecture

4.2 Setting Up SQLite with Node.js

Node.js now includes a built-in node:sqlite module (experimental as of Node.js 22+). For production, you might still want the better-sqlite3 package.

Installation options:

Bash# Option 1: Built-in module (Node.js 22+)
# No installation needed, just use --experimental-sqlite flag

# Option 2: better-sqlite3 (recommended for production)
npm install better-sqlite3

# Option 3: sqlite3 (async, callback-based)
npm install sqlite3

4.3 Using the Built-in node:sqlite Module

JavaScript — server.js// server.js - Run with: node --experimental-sqlite server.js
const { DatabaseSync } = require('node:sqlite');

// Open or create database file
const db = new DatabaseSync('myapp.sqlite');

// Enable foreign keys
db.exec('PRAGMA foreign_keys = ON');

// Create tables
db.exec(`
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL,
        age INTEGER,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
`);

db.exec(`
    CREATE TABLE IF NOT EXISTS posts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        content TEXT,
        user_id INTEGER,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    )
`);

// CRUD Operations
const insertUser = db.prepare(`
    INSERT INTO users (name, email, age) VALUES (?, ?, ?)
`);

const getUserById = db.prepare('SELECT * FROM users WHERE id = ?');
const getAllUsers = db.prepare('SELECT * FROM users ORDER BY created_at DESC');
const updateUser = db.prepare('UPDATE users SET name = ?, email = ?, age = ? WHERE id = ?');
const deleteUser = db.prepare('DELETE FROM users WHERE id = ?');

// Transaction example
const createUserWithPost = (userData, postContent) => {
    const transaction = db.transaction(() => {
        const userInfo = insertUser.run(userData.name, userData.email, userData.age);
        const userId = userInfo.lastInsertRowid;

        const insertPost = db.prepare('INSERT INTO posts (title, content, user_id) VALUES (?, ?, ?)');
        insertPost.run('Welcome Post', postContent, userId);

        return { userId, postId: insertPost.lastInsertRowid };
    });

    return transaction();
};

4.4 Complete CRUD Example with SQLite

models/quoteModel.js (SQLite with better-sqlite3):

JavaScript — models/quoteModel.jsconst Database = require('better-sqlite3');

const db = new Database('quotes.db');

// Initialize database
db.exec(`
    CREATE TABLE IF NOT EXISTS quotes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        text TEXT NOT NULL,
        author TEXT NOT NULL,
        category TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
`);

// CREATE
const createQuote = (quote) => {
    const stmt = db.prepare(`
        INSERT INTO quotes (text, author, category)
        VALUES (@text, @author, @category)
    `);
    const result = stmt.run(quote);
    return { id: result.lastInsertRowid, ...quote };
};

// READ all with sorting
const getAllQuotes = (sortBy = 'created_at', order = 'DESC') => {
    const validSorts = ['created_at', 'author', 'category'];
    const sort = validSorts.includes(sortBy) ? sortBy : 'created_at';

    const stmt = db.prepare(`
        SELECT * FROM quotes
        ORDER BY ${sort} ${order === 'DESC' ? 'DESC' : 'ASC'}
    `);
    return stmt.all();
};

// READ by category
const getQuotesByCategory = (category) => {
    const stmt = db.prepare('SELECT * FROM quotes WHERE category = ? ORDER BY created_at DESC');
    return stmt.all(category);
};

// READ search
const searchQuotes = (searchTerm) => {
    const stmt = db.prepare(`
        SELECT * FROM quotes
        WHERE text LIKE ? OR author LIKE ?
        ORDER BY created_at DESC
    `);
    const pattern = `%${searchTerm}%`;
    return stmt.all(pattern, pattern);
};

// READ random quote
const getRandomQuote = () => {
    const stmt = db.prepare('SELECT * FROM quotes ORDER BY RANDOM() LIMIT 1');
    return stmt.get();
};

// UPDATE
const updateQuote = (id, updates) => {
    const fields = [];
    const values = [];

    if (updates.text !== undefined) {
        fields.push('text = ?');
        values.push(updates.text);
    }
    if (updates.author !== undefined) {
        fields.push('author = ?');
        values.push(updates.author);
    }
    if (updates.category !== undefined) {
        fields.push('category = ?');
        values.push(updates.category);
    }

    if (fields.length === 0) return null;

    values.push(id);
    const stmt = db.prepare(`
        UPDATE quotes SET ${fields.join(', ')} WHERE id = ?
    `);

    const result = stmt.run(...values);
    return result.changes > 0;
};

// DELETE
const deleteQuote = (id) => {
    const stmt = db.prepare('DELETE FROM quotes WHERE id = ?');
    const result = stmt.run(id);
    return result.changes > 0;
};

module.exports = {
    createQuote,
    getAllQuotes,
    getQuotesByCategory,
    searchQuotes,
    getRandomQuote,
    updateQuote,
    deleteQuote
};

routes/quotes.js:

JavaScript — routes/quotes.jsconst express = require('express');
const router = express.Router();
const quoteModel = require('../models/quoteModel');

// GET all quotes
router.get('/', (req, res) => {
    const { sortBy, order } = req.query;
    const quotes = quoteModel.getAllQuotes(sortBy, order);
    res.json({ success: true, count: quotes.length, data: quotes });
});

// GET random quote
router.get('/random', (req, res) => {
    const quote = quoteModel.getRandomQuote();
    if (!quote) return res.status(404).json({ error: 'No quotes found' });
    res.json({ success: true, data: quote });
});

// GET quotes by category
router.get('/category/:category', (req, res) => {
    const quotes = quoteModel.getQuotesByCategory(req.params.category);
    res.json({ success: true, count: quotes.length, data: quotes });
});

// GET search quotes
router.get('/search', (req, res) => {
    const { q } = req.query;
    if (!q) return res.status(400).json({ error: 'Search term required' });

    const quotes = quoteModel.searchQuotes(q);
    res.json({ success: true, count: quotes.length, data: quotes });
});

// POST create quote
router.post('/', (req, res) => {
    const { text, author, category } = req.body;

    if (!text || !author) {
        return res.status(400).json({ error: 'Text and author are required' });
    }

    const newQuote = quoteModel.createQuote({ text, author, category });
    res.status(201).json({ success: true, data: newQuote });
});

// PUT update quote
router.put('/:id', (req, res) => {
    const id = parseInt(req.params.id);
    const { text, author, category } = req.body;

    const updated = quoteModel.updateQuote(id, { text, author, category });
    if (!updated) return res.status(404).json({ error: 'Quote not found' });

    res.json({ success: true, message: 'Quote updated' });
});

// DELETE quote
router.delete('/:id', (req, res) => {
    const id = parseInt(req.params.id);
    const deleted = quoteModel.deleteQuote(id);
    if (!deleted) return res.status(404).json({ error: 'Quote not found' });

    res.status(204).send();
});

module.exports = router;

5. ORM Deep Dive: Working with SQL Databases

5.1 What is an ORM and Why Use It?

An ORM (Object-Relational Mapper) is a library that maps database tables to JavaScript objects, allowing you to interact with your database using JavaScript instead of SQL.

Benefits of using an ORM:

Productivity: Write database queries using JavaScript instead of SQL
Type safety: TypeScript support for query results
Validation: Built-in data validation before saving
Relationships: Easy handling of associations between models
Migrations: Manage schema changes programmatically
Database agnostic: Switch databases with minimal code changes

Drawbacks of using an ORM:

Performance overhead: ORMs generate SQL, which may not be optimal
Learning curve: Need to learn the ORM's specific API
Complex queries: Very complex queries may be harder to express than raw SQL

5.2 Sequelize ORM (MySQL, PostgreSQL, SQLite)

Sequelize is the most established ORM for Node.js, supporting MySQL, PostgreSQL, SQLite, and MSSQL.

Installation:

Bashnpm install sequelize
npm install mysql2 pg pg-hstore sqlite3  # Install only your database driver

Basic Setup:

JavaScriptconst { Sequelize, DataTypes } = require('sequelize');

// Different dialects - same API!
const sequelize = new Sequelize('database', 'username', 'password', {
    host: 'localhost',
    dialect: 'mysql' // or 'postgres' or 'sqlite'
});

// SQLite example
const sqliteDb = new Sequelize({
    dialect: 'sqlite',
    storage: 'database.sqlite'
});

Model Definition:

JavaScriptconst Task = sequelize.define('Task', {
    title: {
        type: DataTypes.STRING,
        allowNull: false,
        validate: {
            len: [3, 100]
        }
    },
    completed: {
        type: DataTypes.BOOLEAN,
        defaultValue: false
    },
    dueDate: {
        type: DataTypes.DATE,
        validate: {
            isDate: true
        }
    }
}, {
    timestamps: true,
    underscored: true  // Use snake_case in database
});

// Relationships
User.hasMany(Task);
Task.belongsTo(User);

5.3 Knex.js Query Builder

Knex.js is a query builder, not a full ORM. It provides a fluent API for constructing SQL queries while giving you more control than an ORM.

Installation:

Bashnpm install knex
npm install pg  # or mysql2 or sqlite3

Setup:

JavaScriptconst knex = require('knex')({
    client: 'postgresql',
    connection: {
        host: '127.0.0.1',
        port: 5432,
        user: 'postgres',
        password: 'password',
        database: 'myapp'
    },
    pool: { min: 0, max: 10 }
});

// SQLite
const sqliteKnex = require('knex')({
    client: 'sqlite3',
    connection: { filename: './data.db' },
    useNullAsDefault: true
});

Query Examples:

JavaScript// SELECT
const users = await knex('users')
    .select('id', 'name', 'email')
    .where('age', '>', 18)
    .orderBy('name', 'asc')
    .limit(10);

// JOIN
const postsWithAuthors = await knex('posts')
    .join('users', 'posts.user_id', 'users.id')
    .select('posts.*', 'users.name as author_name');

// INSERT
const [newUserId] = await knex('users').insert({
    name: 'John Doe',
    email: 'john@example.com'
});

// UPDATE
await knex('users')
    .where('id', userId)
    .update({ last_login: new Date() });

// DELETE
await knex('users').where('id', userId).del();

// Transactions
await knex.transaction(async (trx) => {
    await trx('accounts').decrement('balance', 100).where('id', 1);
    await trx('accounts').increment('balance', 100).where('id', 2);
});

5.4 Prisma ORM Overview

Prisma is a modern ORM that takes a different approach—you define your schema in a .prisma file, and it generates a type-safe client.

Key features:

Type-safe database client (excellent TypeScript support)
Declarative schema definition
Powerful relation handling
Visual data browser (Prisma Studio)

Comparison with Sequelize:

Feature	Sequelize	Prisma
Approach	Code-first (JavaScript models)	Schema-first (.prisma file)
TypeScript	Manual or with types	Auto-generated, fully type-safe
Performance	Good	Better (Rust-based query engine)
Learning curve	Moderate	Gentler (declarative schema)
Migrations	Manual or auto	Auto-generated
Community	Larger, more mature	Growing rapidly

5.5 Comparison: Sequelize vs. Prisma vs. Knex

Feature	Sequelize	Prisma	Knex.js
Type	Full ORM	Full ORM	Query Builder
TypeScript	Partial (manual types)	Excellent (auto-generated)	Basic
Performance	Moderate	High	Highest
SQL Control	Low (abstracted)	Medium	Full control
Learning Curve	Medium	Low (schema-first)	Medium (need SQL knowledge)
Migrations	Built-in	Built-in	Built-in (via CLI)
Relationships	Manual definition	Automatic inference	Manual (JOINs)
Best For	Complex business logic	Type-safe apps	Performance-critical apps

When to choose each:

Sequelize: You need a mature ORM with extensive features and don't mind writing model definitions in JavaScript
Prisma: You want excellent TypeScript support, auto-generated client, and modern tooling
Knex.js: You prefer writing SQL but want parameterization and connection pooling help

6. Best Practices for SQL Databases in Express

6.1 Connection Pooling

Always use connection pooling to manage database connections efficiently:

JavaScript// MySQL - connection pooling example
const pool = mysql.createPool({
    connectionLimit: 10,    // Adjust based on your server capacity
    queueLimit: 0,          // Unlimited queue (wait for available connection)
    waitForConnections: true,
    enableKeepAlive: true
});

// PostgreSQL - using pg Pool
const { Pool } = require('pg');
const pool = new Pool({
    max: 20,
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 2000
});

Connection pool best practices:

Set max based on your database's capacity (start with 10-20)
Always release connections back to the pool
Handle connection errors gracefully
Close the pool when your application shuts down

6.2 Preventing SQL Injection

ALWAYS use parameterized queries or prepared statements. Never concatenate user input into SQL strings.

JavaScript// ❌ DANGEROUS - SQL injection vulnerability
const userId = req.params.id;
const query = `SELECT * FROM users WHERE id = ${userId}`;

// ✅ SAFE - Parameterized query (mysql2)
const [rows] = await pool.execute('SELECT * FROM users WHERE id = ?', [userId]);

// ✅ SAFE - Parameterized query (pg)
const result = await pool.query('SELECT * FROM users WHERE id = $1', [userId]);

// ✅ SAFE - Parameterized query (better-sqlite3)
const stmt = db.prepare('SELECT * FROM users WHERE id = ?');
const user = stmt.get(userId);

6.3 Managing Migrations

Why use migrations? Migrations allow you to track and version database schema changes, making it easy to update databases across environments.

Sequelize migrations example:

Bashnpx sequelize-cli migration:generate --name create-users-table

JavaScript — migrations/20240101000000-create-users-table.jsmodule.exports = {
    up: async (queryInterface, Sequelize) => {
        await queryInterface.createTable('users', {
            id: { type: Sequelize.INTEGER, autoIncrement: true, primaryKey: true },
            name: { type: Sequelize.STRING, allowNull: false },
            email: { type: Sequelize.STRING, allowNull: false, unique: true },
            created_at: { type: Sequelize.DATE, defaultValue: Sequelize.NOW }
        });
    },
    down: async (queryInterface) => {
        await queryInterface.dropTable('users');
    }
};

Knex migrations example:

Bashnpx knex migrate:make create_users_table

JavaScript — migrations/20240101000000_create_users_table.jsexports.up = function(knex) {
    return knex.schema.createTable('users', (table) => {
        table.increments('id');
        table.string('name').notNullable();
        table.string('email').unique().notNullable();
        table.timestamps(true, true);
    });
};

exports.down = function(knex) {
    return knex.schema.dropTable('users');
};

6.4 Error Handling

Always implement proper error handling for database operations:

JavaScriptconst express = require('express');
const app = express();

// Custom error classes
class DatabaseError extends Error {
    constructor(message, originalError) {
        super(message);
        this.name = 'DatabaseError';
        this.originalError = originalError;
    }
}

// Database error handler middleware
const handleDatabaseError = (err, req, res, next) => {
    // MySQL duplicate entry error
    if (err.code === 'ER_DUP_ENTRY') {
        return res.status(409).json({
            error: 'Duplicate entry',
            field: err.sqlMessage.match(/for key '(.+?)'/)?.[1]
        });
    }

    // PostgreSQL unique violation
    if (err.code === '23505') {
        return res.status(409).json({ error: 'Duplicate entry' });
    }

    // SQLite constraint failed
    if (err.code === 'SQLITE_CONSTRAINT') {
        return res.status(409).json({ error: 'Constraint violation' });
    }

    // Connection errors
    if (err.code === 'ECONNREFUSED') {
        console.error('Database connection refused');
        return res.status(503).json({ error: 'Database unavailable' });
    }

    // Pass through to general error handler
    next(err);
};

// Route with error handling
app.get('/api/users/:id', async (req, res, next) => {
    try {
        const user = await User.findByPk(req.params.id);
        if (!user) {
            return res.status(404).json({ error: 'User not found' });
        }
        res.json(user);
    } catch (error) {
        next(new DatabaseError('Failed to fetch user', error));
    }
});

app.use(handleDatabaseError);
app.use((err, req, res, next) => {
    console.error('Unhandled error:', err);
    res.status(500).json({ error: 'Internal server error' });
});

7. Complete Project: Task Management API

Here's a complete API that works with all three databases (MySQL, PostgreSQL, SQLite) using Sequelize:

package.json:

JSON — package.json{
    "name": "task-api",
    "version": "1.0.0",
    "scripts": {
        "start": "node server.js",
        "dev": "nodemon server.js",
        "db:migrate": "npx sequelize-cli db:migrate"
    },
    "dependencies": {
        "express": "^4.18.2",
        "sequelize": "^6.35.0",
        "dotenv": "^16.3.0",
        "mysql2": "^3.6.0",
        "pg": "^8.11.0",
        "sqlite3": "^5.1.6"
    }
}

server.js:

JavaScript — server.jsrequire('dotenv').config();
const express = require('express');
const { sequelize } = require('./models');

const app = express();
app.use(express.json());

// Import routes
const taskRoutes = require('./routes/tasks');
app.use('/api/tasks', taskRoutes);

// Database connection and server start
const startServer = async () => {
    try {
        await sequelize.authenticate();
        console.log(`✅ Connected to ${process.env.DB_DIALECT} database`);

        // Sync models (create tables if they don't exist)
        await sequelize.sync({ alter: true });
        console.log('✅ Database synced');

        app.listen(3000, () => {
            console.log('🚀 Server running on port 3000');
        });
    } catch (error) {
        console.error('❌ Failed to start server:', error.message);
        process.exit(1);
    }
};

startServer();

models/index.js:

JavaScript — models/index.jsconst { Sequelize } = require('sequelize');

// Database configuration based on environment variable
let sequelize;

switch (process.env.DB_DIALECT) {
    case 'mysql':
        sequelize = new Sequelize(
            process.env.DB_NAME,
            process.env.DB_USER,
            process.env.DB_PASSWORD,
            {
                host: process.env.DB_HOST,
                dialect: 'mysql',
                logging: false
            }
        );
        break;
    case 'postgres':
        sequelize = new Sequelize(process.env.DATABASE_URL, {
            dialect: 'postgres',
            logging: false,
            dialectOptions: {
                ssl: process.env.NODE_ENV === 'production'
            }
        });
        break;
    case 'sqlite':
        sequelize = new Sequelize({
            dialect: 'sqlite',
            storage: './database.sqlite',
            logging: false
        });
        break;
    default:
        throw new Error('Unsupported database dialect');
}

const Task = require('./Task')(sequelize);
const User = require('./User')(sequelize);

// Define associations
User.hasMany(Task);
Task.belongsTo(User);

module.exports = { sequelize, Task, User };

models/Task.js:

JavaScript — models/Task.jsconst { DataTypes } = require('sequelize');

module.exports = (sequelize) => {
    const Task = sequelize.define('Task', {
        id: {
            type: DataTypes.INTEGER,
            autoIncrement: true,
            primaryKey: true
        },
        title: {
            type: DataTypes.STRING(200),
            allowNull: false,
            validate: {
                notEmpty: { msg: 'Title cannot be empty' },
                len: { args: [3, 200], msg: 'Title must be 3-200 characters' }
            }
        },
        description: {
            type: DataTypes.TEXT,
            allowNull: true
        },
        status: {
            type: DataTypes.ENUM('pending', 'in_progress', 'completed'),
            defaultValue: 'pending'
        },
        priority: {
            type: DataTypes.ENUM('low', 'medium', 'high'),
            defaultValue: 'medium'
        },
        dueDate: {
            type: DataTypes.DATE,
            allowNull: true,
            validate: {
                isDate: true
            }
        },
        userId: {
            type: DataTypes.INTEGER,
            allowNull: false,
            references: { model: 'Users', key: 'id' }
        }
    }, {
        tableName: 'tasks',
        timestamps: true,
        underscored: true
    });

    return Task;
};

routes/tasks.js:

JavaScript — routes/tasks.jsconst express = require('express');
const router = express.Router();
const { Task, User } = require('../models');
const { Op } = require('sequelize');

// GET all tasks with filters
router.get('/', async (req, res) => {
    const { status, priority, userId, limit = 10, offset = 0 } = req.query;

    const where = {};
    if (status) where.status = status;
    if (priority) where.priority = priority;
    if (userId) where.userId = userId;

    const tasks = await Task.findAndCountAll({
        where,
        limit: parseInt(limit),
        offset: parseInt(offset),
        order: [['created_at', 'DESC']],
        include: [{ model: User, attributes: ['id', 'name', 'email'] }]
    });

    res.json({
        total: tasks.count,
        page: Math.floor(offset / limit) + 1,
        limit: parseInt(limit),
        data: tasks.rows
    });
});

// GET single task
router.get('/:id', async (req, res) => {
    const task = await Task.findByPk(req.params.id, {
        include: [{ model: User, attributes: ['id', 'name', 'email'] }]
    });

    if (!task) {
        return res.status(404).json({ error: 'Task not found' });
    }
    res.json(task);
});

// POST create task
router.post('/', async (req, res) => {
    const { title, description, status, priority, dueDate, userId } = req.body;

    // Validate user exists
    const user = await User.findByPk(userId);
    if (!user) {
        return res.status(400).json({ error: 'User not found' });
    }

    const task = await Task.create({
        title, description, status, priority, dueDate, userId
    });

    res.status(201).json(task);
});

// PUT update task
router.put('/:id', async (req, res) => {
    const task = await Task.findByPk(req.params.id);
    if (!task) {
        return res.status(404).json({ error: 'Task not found' });
    }

    await task.update(req.body);
    res.json(task);
});

// PATCH update task status
router.patch('/:id/status', async (req, res) => {
    const { status } = req.body;

    if (!['pending', 'in_progress', 'completed'].includes(status)) {
        return res.status(400).json({ error: 'Invalid status' });
    }

    const task = await Task.findByPk(req.params.id);
    if (!task) {
        return res.status(404).json({ error: 'Task not found' });
    }

    task.status = status;
    await task.save();
    res.json(task);
});

// DELETE task
router.delete('/:id', async (req, res) => {
    const task = await Task.findByPk(req.params.id);
    if (!task) {
        return res.status(404).json({ error: 'Task not found' });
    }

    await task.destroy();
    res.status(204).send();
});

// GET overdue tasks
router.get('/overdue/all', async (req, res) => {
    const overdueTasks = await Task.findAll({
        where: {
            dueDate: { [Op.lt]: new Date() },
            status: { [Op.ne]: 'completed' }
        },
        order: [['dueDate', 'ASC']],
        include: [{ model: User, attributes: ['name', 'email'] }]
    });

    res.json({
        count: overdueTasks.length,
        data: overdueTasks
    });
});

module.exports = router;

.env file examples:

Text — .env# MySQL
DB_DIALECT=mysql
DB_HOST=localhost
DB_NAME=taskdb
DB_USER=root
DB_PASSWORD=password

# PostgreSQL
DB_DIALECT=postgres
DATABASE_URL=postgresql://postgres:password@localhost:5432/taskdb

# SQLite
DB_DIALECT=sqlite
# No additional connection string needed

8. Conclusion

You've now learned how to integrate three major SQL databases with Express.js:

Quick Reference Guide

Database	Best For	Connection String	Key Package
MySQL	Web apps, read-heavy workloads	`mysql://user:pass@localhost/db`	`mysql2`
PostgreSQL	Complex queries, data integrity	`postgresql://user:pass@localhost/db`	`pg`
SQLite	Prototyping, desktop, embedded	File path (./data.db)	`better-sqlite3`

Key Takeaways

Choose the right tool for the job: MySQL for web standards, PostgreSQL for advanced features, SQLite for simplicity
Always use parameterized queries to prevent SQL injection—never concatenate user input
Use connection pooling to manage database connections efficiently
ORMs (Sequelize, Prisma) increase productivity at the cost of some performance; query builders (Knex) offer more control
Implement proper error handling for database operations to provide meaningful feedback

Express.js SQL Databases

Table of Contents

1. Introduction to SQL Databases in Express.js

1.1 Relational Databases vs. NoSQL

1.2 Overview of MySQL, PostgreSQL, and SQLite

1.3 How to Choose the Right Database for Your Project

2. Using MySQL with Express.js

2.1 Installing MySQL and mysql2 Driver

2.2 Setting Up a Connection Pool

2.3 Executing Raw SQL Queries

2.4 Using an ORM: Sequelize with MySQL

2.5 Complete CRUD Example with MySQL

3. Using PostgreSQL with Express.js

3.1 Installing PostgreSQL and pg Driver

3.2 Connection Configuration

3.3 Raw SQL Queries with pg

3.4 Using an ORM: Sequelize with PostgreSQL

3.5 Alternative: Using Prisma ORM with PostgreSQL

4. Using SQLite with Express.js

4.1 What Makes SQLite Special (Serverless Database)

4.2 Setting Up SQLite with Node.js

4.3 Using the Built-in node:sqlite Module

4.4 Complete CRUD Example with SQLite

5. ORM Deep Dive: Working with SQL Databases

5.1 What is an ORM and Why Use It?

5.2 Sequelize ORM (MySQL, PostgreSQL, SQLite)

5.3 Knex.js Query Builder

5.4 Prisma ORM Overview

5.5 Comparison: Sequelize vs. Prisma vs. Knex

6. Best Practices for SQL Databases in Express

6.1 Connection Pooling

6.2 Preventing SQL Injection

6.3 Managing Migrations

6.4 Error Handling

7. Complete Project: Task Management API

8. Conclusion