Database Integration

Express.js Intermediate — Database Integration

Express.js Intermediate/Advanced

Chapter 2 of 8 · Database Integration

Database Integration

Every real Express application eventually needs to read and write persistent data. This chapter covers integrating MySQL with Express using the mysql2 package — the fastest, most actively maintained MySQL driver for Node.js. The three things that matter most: always use a connection pool (not a single connection), always use parameterized queries (never string-concat SQL), and always release connections back to the pool when you're done with them.

mysql2 vs mysql

mysql (legacy)

Callback-based only. Requires wrapping in util.promisify for async/await. No built-in prepared statements — uses client-side escaping (still safe but different semantics). Actively receives only security fixes.

mysql2 (use this)

Native Promise support via mysql2/promise. Server-side prepared statements via execute() — the SQL is parsed once, parameters are sent separately, SQL injection is structurally impossible. Faster than mysql, drop-in compatible API.

// npm install mysql2

// Two import paths — you almost always want the promise one
const mysql = require('mysql2/promise');   // ✓ async/await
const mysql = require('mysql2');           // ✗ callback-based — avoid
    

Connection Pools

A single connection can handle only one query at a time. Under real load — multiple requests arriving simultaneously — they would queue behind each other. A pool maintains multiple open connections and assigns them to incoming queries on demand, then returns them when done. Creating a connection is expensive (TCP handshake + MySQL auth). A pool reuses them.

// src/db/pool.js — create once, export, import everywhere
const mysql = require('mysql2/promise');

const pool = mysql.createPool({
    host:             process.env.DB_HOST     || 'localhost',
    port:             process.env.DB_PORT     || 3306,
    user:             process.env.DB_USER,
    password:         process.env.DB_PASS,
    database:         process.env.DB_NAME,
    waitForConnections: true,   // queue requests when pool is exhausted
    connectionLimit:    10,      // max simultaneous connections
    queueLimit:         0,       // 0 = unlimited queue (requests wait forever)
    timezone:           'Z',      // store/retrieve dates as UTC
});

module.exports = pool;
    

Option	Default	Notes
connectionLimit	10	Total open connections to MySQL. A good starting point — raise under sustained high traffic.
waitForConnections	true	If false, throws immediately when pool is full instead of queuing.
queueLimit	0	Max queued requests. 0 = unlimited. Set a number (e.g. 100) to shed load instead of building an infinite queue.
idleTimeout	60000	Idle connections are closed after this many ms. Keeps the pool healthy.
enableKeepAlive	false	Set true + keepAliveInitialDelay: 10000 to prevent MySQL's 8-hour timeout from dropping long-lived connections.

execute() vs query()

This is the most important distinction in the whole chapter. pool.execute() sends the SQL template and the parameters to MySQL in separate messages. MySQL parses the SQL first, then receives the parameters as raw binary data — they are never treated as SQL, making injection structurally impossible. pool.query() sends a complete string after client-side escaping. Both work, but execute() is safer and faster (MySQL caches the parsed statement).

✅ execute() — always use for user data

const [rows] = await pool.execute(
    'SELECT * FROM users WHERE email = ?',
    [email]     // ← separate, never becomes SQL
);
            

⚠ query() — safe only for static SQL

// Fine for schema queries with no user input
const [tables] = await pool.query(
    'SHOW TABLES'
);
// Do NOT do this:
// pool.query(`SELECT * FROM users WHERE email = '${email}'`)
            

Basic CRUD Pattern

// src/models/product.js
const pool = require('../db/pool');

async function findAll() {
    const [rows] = await pool.execute('SELECT * FROM products ORDER BY id');
    return rows;
}

async function findById(id) {
    const [rows] = await pool.execute(
        'SELECT * FROM products WHERE id = ?',
        [id]
    );
    return rows[0] ?? null;   // undefined → null
}

async function create({ name, price, stock = 0 }) {
    const [result] = await pool.execute(
        'INSERT INTO products (name, price, stock) VALUES (?, ?, ?)',
        [name, price, stock]
    );
    return { id: result.insertId, name, price, stock };
}

async function update(id, fields) {
    // Build SET clause dynamically from only the supplied fields
    const allowed  = ['name', 'price', 'stock'];
    const entries  = Object.entries(fields).filter(([k]) => allowed.includes(k));
    if (entries.length === 0) return null;
    const setClauses = entries.map(([k]) => `${k} = ?`).join(', ');
    const values    = [...entries.map(([, v]) => v), id];
    const [result]  = await pool.execute(
        `UPDATE products SET ${setClauses} WHERE id = ?`,
        values
    );
    return result.affectedRows > 0 ? findById(id) : null;
}

async function remove(id) {
    const [result] = await pool.execute(
        'DELETE FROM products WHERE id = ?',
        [id]
    );
    return result.affectedRows > 0;
}

module.exports = { findAll, findById, create, update, remove };
    

What execute() returns

Always an array of two elements: [rows, fields] for SELECT, [ResultSetHeader, fields] for INSERT/UPDATE/DELETE. The ResultSetHeader has insertId (last auto-increment ID), affectedRows, and changedRows. You almost always destructure just the first: const [rows] = await pool.execute(...).

Mapping MySQL Errors to HTTP Status Codes

MySQL throws errors with a code string property. Your service or error handler should catch these and map them to meaningful HTTP responses before they bubble up as 500s.

MySQL code	HTTP status	When it happens
ER_DUP_ENTRY	409 Conflict	INSERT or UPDATE violates a UNIQUE constraint (duplicate email, SKU, etc.)
ER_NO_REFERENCED_ROW_2	422 Unprocessable	INSERT/UPDATE references a foreign key that doesn't exist (e.g. invalid category_id)
ER_ROW_IS_REFERENCED_2	409 Conflict	DELETE would orphan child rows that reference this record
ER_DATA_TOO_LONG	422 Unprocessable	Value exceeds column length — usually a bug in validation, but can come from user input
ER_BAD_NULL_ERROR	422 Unprocessable	NULL inserted into a NOT NULL column without a default
ECONNREFUSED	503 Service Unavailable	MySQL server is down or unreachable — always log this, never expose to clients

// src/utils/dbErrors.js — centralised MySQL → HTTP mapping
function handleDbError(err) {
    switch (err.code) {
        case 'ER_DUP_ENTRY': {
            // MySQL includes the duplicate value in err.message — extract the key name
            const match = err.message.match(/for key '(.+?)'/);
            const key   = match ? match[1] : 'unknown';
            const e     = new Error(`Duplicate value for key: ${key}`);
            e.status    = 409;
            return e;
        }
        case 'ER_NO_REFERENCED_ROW_2': {
            const e  = new Error('Referenced record does not exist');
            e.status = 422;
            return e;
        }
        case 'ER_ROW_IS_REFERENCED_2': {
            const e  = new Error('Cannot delete: other records depend on this one');
            e.status = 409;
            return e;
        }
        default:
            return err;   // unknown DB error → will become 500
    }
}

module.exports = { handleDbError };

// Usage in a service
async function create(data) {
    try {
        return await ProductModel.create(data);
    } catch (err) {
        throw handleDbError(err);
    }
}
    

Transactions

A transaction groups multiple SQL statements so they either all succeed together or all roll back together. You need a transaction any time you are making changes that must be atomic — a bank transfer, decrementing stock while creating an order, or inserting related rows into two tables simultaneously.

── Transaction lifecycle ───────────────────────────────────────── 1. pool.getConnection() ← borrow one specific connection 2. conn.beginTransaction() ← START TRANSACTION 3. conn.execute(...) ← first statement 4. conn.execute(...) ← second statement 5a. conn.commit() ← success path 5b. conn.rollback() ← error path (undo everything) 6. conn.release() ← ALWAYS — returns connection to pool (goes in finally{} so it runs even if commit/rollback throws)

// Transfer balance between two accounts — must be atomic
async function transfer(fromId, toId, amount) {
    const conn = await pool.getConnection();
    try {
        await conn.beginTransaction();

        // Debit — check balance first (use FOR UPDATE to lock the row)
        const [[from]] = await conn.execute(
            'SELECT balance FROM accounts WHERE id = ? FOR UPDATE',
            [fromId]
        );
        if (!from) throw createError(404, 'Source account not found');
        if (from.balance < amount) throw createError(422, 'Insufficient funds');

        await conn.execute(
            'UPDATE accounts SET balance = balance - ? WHERE id = ?',
            [amount, fromId]
        );

        // Credit
        const [creditResult] = await conn.execute(
            'UPDATE accounts SET balance = balance + ? WHERE id = ?',
            [amount, toId]
        );
        if (creditResult.affectedRows === 0) throw createError(404, 'Target account not found');

        await conn.commit();
        return { success: true, amount };

    } catch (err) {
        await conn.rollback();
        throw err;
    } finally {
        conn.release();   // ← MUST run even when catch re-throws
    }
}
    

⚠ Always release() in a finally block

If you forget conn.release(), the connection is permanently checked out from the pool. After connectionLimit requests that hit this path, the pool is exhausted and every subsequent request hangs forever (or until queueLimit is exceeded). The bug is silent until the pool runs out — and it will run out. Put release() in finally {} and it cannot be skipped.

Query Helpers

Rather than writing the same execute-and-destructure boilerplate on every model, you can wrap common patterns in helper functions once. These are thin utilities — not an ORM — so you keep full control over your SQL.

// src/db/helpers.js
const pool = require('./pool');

// Run a query, return just the rows array
async function query(sql, params = []) {
    const [rows] = await pool.execute(sql, params);
    return rows;
}

// SELECT that returns one row or null
async function queryOne(sql, params = []) {
    const rows = await query(sql, params);
    return rows[0] ?? null;
}

// INSERT/UPDATE/DELETE — returns the ResultSetHeader
async function run(sql, params = []) {
    const [result] = await pool.execute(sql, params);
    return result;  // { insertId, affectedRows, changedRows, ... }
}

module.exports = { query, queryOne, run };

// Usage — much less boilerplate per model
const { query, queryOne, run } = require('../db/helpers');

const getAll   = ()     => query('SELECT * FROM products ORDER BY id');
const getById  = (id)   => queryOne('SELECT * FROM products WHERE id = ?', [id]);
const insert   = (data) => run('INSERT INTO products (name, price) VALUES (?, ?)', [data.name, data.price]);
const destroy  = (id)   => run('DELETE FROM products WHERE id = ?', [id]);
    

Wiring into MVC

// src/services/productsService.js — business logic, no HTTP
const ProductModel          = require('../models/product');
const { handleDbError }    = require('../utils/dbErrors');
const createError           = require('../utils/createError');

async function list()      { return ProductModel.findAll(); }

async function get(id) {
    const p = await ProductModel.findById(id);
    if (!p) throw createError(404, `Product ${id} not found`);
    return p;
}

async function create(data) {
    if (!data?.name)  throw createError(422, 'name is required');
    if (data.price == null || data.price < 0) throw createError(422, 'price must be >= 0');
    try {
        return await ProductModel.create(data);
    } catch (err) {
        throw handleDbError(err);
    }
}

module.exports = { list, get, create };

// src/controllers/productsController.js — thin, HTTP-only
const svc = require('../services/productsService');

module.exports = {
    list:   async (req, res) => res.json(await svc.list()),
    show:   async (req, res) => res.json(await svc.get(req.params.id)),
    create: async (req, res) => {
        const p = await svc.create(req.body ?? {});
        res.status(201).set('Location', `/api/v1/products/${p.id}`).json(p);
    },
};
    

Quick Reference

Operation	API
pool.execute(sql, params)	Prepared statement — always use for user data. Returns `[rows, fields]`.
pool.query(sql)	Plain string query — only for static SQL with no user input.
result.insertId	The auto-increment ID of the row just inserted.
result.affectedRows	Rows affected by UPDATE or DELETE. 0 means the WHERE matched nothing.
pool.getConnection()	Borrow a specific connection from the pool (needed for transactions).
conn.beginTransaction()	Start a transaction on the borrowed connection.
conn.commit()	Commit all changes made since beginTransaction().
conn.rollback()	Undo all changes since beginTransaction().
conn.release()	Return the connection to the pool. Goes in finally{} — never skip it.
err.code	MySQL error code string (e.g. `'ER_DUP_ENTRY'`). Check this in catch blocks to map to HTTP status codes.

Coding Challenges

Challenge 1 — Products CRUD with Real MySQL

Create a products table (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL UNIQUE, price DECIMAL(10,2) NOT NULL, stock INT DEFAULT 0). Write a ProductModel module using mysql2/promise that implements findAll(), findById(id), create({name, price, stock}), update(id, fields) (only update supplied fields using a dynamic SET clause — filter against an allowlist of ['name','price','stock']), and remove(id). Write integration tests using a dedicated test database (express_test). In beforeAll: create the table. In afterAll: drop the table and call pool.end() to close all connections (required for Jest to exit cleanly). In beforeEach: truncate the table and reset the auto-increment counter. Tests: findAll returns empty array initially; create returns the new record with insertId; findById returns null for a missing id; update only changes supplied fields; remove returns true on success and false for a missing id.

View sample solution ↗

Challenge 2 — Query Helpers & Error Mapping

Write a src/db/helpers.js module exporting query(sql, params), queryOne(sql, params), and run(sql, params) as described in the chapter. Write a src/utils/dbErrors.js module with a handleDbError(err) function that maps ER_DUP_ENTRY → 409, ER_NO_REFERENCED_ROW_2 → 422, ER_ROW_IS_REFERENCED_2 → 409, and leaves other errors unchanged. Rewrite the ProductModel from Challenge 1 to use the helpers (each model function becomes a one-liner). Add a categories table with a foreign key from products.category_id to categories.id. Write integration tests verifying: creating a product with a duplicate name returns 409 with a message containing "Duplicate"; creating a product with a non-existent category_id returns 422; deleting a category that has products returns 409; unknown MySQL errors pass through unchanged.

View sample solution ↗

Challenge 3 — Transactions: Stock Reservation

Add an orders table and an order_items table. Write a createOrder(items) function where items is an array of {productId, quantity}. The function must run as a single transaction: for each item, SELECT the product with FOR UPDATE (to lock the row against concurrent modifications), check that stock >= quantity (throw 422 with a clear message if not), then UPDATE products SET stock = stock - quantity. After all items are processed, INSERT a row into orders and one row per item into order_items. Commit on success, rollback on any error, and always release the connection. Write integration tests: a successful order reduces product stock; an order with insufficient stock for any item rolls back all stock changes (verify no stock was decremented); an order referencing a non-existent product throws 404 and rolls back; ordering from two items where the second is out of stock leaves the first item's stock unchanged.

View sample solution ↗