Database Integration

Node.js Intermediate — Database Integration

Node.js Intermediate

Chapter 5 of 8 · Database Integration

Database Integration

Most real Node applications need a database. This chapter covers mysql2 — the de facto MySQL/MariaDB driver for Node — focusing on its promise API, prepared statements, and connection pooling. These three things together give you safe queries, good performance, and clean async code. The same patterns apply to any SQL driver, so the habits here transfer directly to PostgreSQL (pg), SQLite (better-sqlite3), and others.

Why mysql2, Not mysql

Promise support built in

Import from mysql2/promise to get an API that returns Promises natively — no need to promisify anything or use callbacks.

Prepared statements

execute() sends the SQL and the data separately, so the database parses the query once and the data can never be interpreted as SQL — the correct way to prevent SQL injection.

Better performance

Faster packet parsing than the original mysql package; the prepared-statement cache means repeated queries skip the parse step entirely.

Active maintenance

The original mysql package is largely unmaintained. mysql2 is its spiritual successor and is what the ecosystem now reaches for.

# Install
npm install mysql2
    

Connecting — Single Connection

A single connection is fine for scripts, CLIs, and learning. For web servers handling concurrent requests, use a pool (covered below).

const mysql = require('mysql2/promise');

const conn = await mysql.createConnection({
    host:     'localhost',
    user:     'app_user',
    password: process.env.DB_PASSWORD,
    database: 'my_app',
});

console.log('Connected!');

// Always close when done with a single connection
await conn.end();
    

🔴 Never Hardcode Credentials

Database passwords belong in environment variables (process.env.DB_PASSWORD), loaded from a .env file via dotenv in development and from your hosting platform's secrets manager in production. A password committed to source control is effectively public — rotate it immediately if it happens.

query() vs execute() — Know the Difference

Both methods run SQL, but they work differently under the hood. Use execute() for almost everything.

Method	How it works	Use when
`query(sql, values)`	Interpolates values client-side using escaping, sends one complete SQL string to the server.	Dynamic identifiers (table/column names) that can't be parameterised, or one-off admin queries.
`execute(sql, values)`	Sends the SQL template and the values in separate packets. Server parses the template once and caches it. Data is never interpreted as SQL.	Every query that accepts user-supplied data. Also faster for repeated queries.

// query() — client-side escaping (acceptable but not preferred)
const [rows] = await conn.query(
    'SELECT * FROM users WHERE email = ?',
    [userEmail]
);

// execute() — true prepared statement (preferred for user data)
const [rows] = await conn.execute(
    'SELECT * FROM users WHERE email = ?',
    [userEmail]
);
    

Reading Query Results

Both query() and execute() resolve to a two-element array: [rows, fields]. Destructuring the first element is the idiomatic pattern.

// SELECT — rows is an array of plain objects (RowDataPacket[])
const [rows] = await conn.execute('SELECT id, name, email FROM users');

for (const user of rows) {
    console.log(user.id, user.name, user.email);
}

// One row — check length or use LIMIT 1
const [rows] = await conn.execute(
    'SELECT * FROM users WHERE id = ? LIMIT 1', [userId]
);
const user = rows[0] ?? null;  // null if not found

// INSERT / UPDATE / DELETE — result is a ResultSetHeader
const [result] = await conn.execute(
    'INSERT INTO users (name, email) VALUES (?, ?)',
    ['Alice', 'alice@example.com']
);
console.log(result.insertId);      // auto-increment ID of the new row
console.log(result.affectedRows);   // number of rows inserted/updated/deleted
    

Placeholders — ? and Named

mysql2 supports two placeholder styles. Positional ? is universal; named placeholders require enabling an option and use a slightly different syntax.

// Positional — values array must match ? order
await conn.execute(
    'UPDATE users SET name = ?, email = ? WHERE id = ?',
    [newName, newEmail, userId]
);

// Named — requires namedPlaceholders: true in connection config
const conn = await mysql.createConnection({
    host: 'localhost', user: 'app', password: '...', database: 'db',
    namedPlaceholders: true,
});

await conn.execute(
    'UPDATE users SET name = :name, email = :email WHERE id = :id',
    { name: newName, email: newEmail, id: userId }
);
    

💡 You Can't Parameterise Identifiers

Placeholders only work for values — not table names, column names, or SQL keywords. If you need a dynamic identifier, validate it against an allowlist of known names and interpolate it yourself:

const ALLOWED = ['name', 'email', 'created_at'];
if (!ALLOWED.includes(col)) throw new Error('Invalid column');
conn.execute(`SELECT ${col} FROM users WHERE id = ?`, [id])

Connection Pooling

A web server handles many requests concurrently. A single connection processes one query at a time — all others queue behind it. A pool maintains several open connections, handing one to each incoming request and returning it when the query finishes. This is how databases are used in production.

const pool = mysql.createPool({
    host:            'localhost',
    user:            'app_user',
    password:        process.env.DB_PASSWORD,
    database:        'my_app',
    waitForConnections: true,   // queue requests when all connections are busy
    connectionLimit:    10,      // max open connections (tune to your DB server's limit)
    queueLimit:         0,       // 0 = unlimited queue; set a number to reject when full
    enableKeepAlive:    true,   // keeps idle connections alive (prevents server-side timeout drops)
    keepAliveInitialDelay: 0,
});

// pool.execute() and pool.query() borrow a connection automatically —
// you don't need to call pool.getConnection() for simple queries
const [rows] = await pool.execute('SELECT * FROM users WHERE active = ?', [1]);
    

// Getting a connection explicitly — needed for transactions
const conn = await pool.getConnection();
try {
    const [rows] = await conn.execute('SELECT ...');
    // ...
} finally {
    conn.release();  // always release — omitting this leaks connections
}
    

💡 Create the Pool Once, Use It Everywhere

Instantiate the pool once when your app starts — typically in a db.js module that exports it — and import it wherever you need it. Creating a new pool per request defeats the purpose and exhausts the database's connection limit.

// db.js
const pool = mysql.createPool({ ... });
module.exports = pool;

// anywhere else
const pool = require('./db');
const [rows] = await pool.execute('SELECT ...');

Transactions

A transaction groups multiple queries so they either all succeed or all fail together. Essential for operations that must be atomic — transferring money, creating an order and decrementing stock, linking rows across tables.

async function transferFunds(fromId, toId, amount) {
    const conn = await pool.getConnection();
    await conn.beginTransaction();

    try {
        // Debit the sender
        const [debit] = await conn.execute(
            'UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?',
            [amount, fromId, amount]
        );
        if (debit.affectedRows === 0) {
            throw new Error('Insufficient funds or account not found');
        }

        // Credit the recipient
        await conn.execute(
            'UPDATE accounts SET balance = balance + ? WHERE id = ?',
            [amount, toId]
        );

        // Log the transfer
        await conn.execute(
            'INSERT INTO transfers (from_id, to_id, amount) VALUES (?, ?, ?)',
            [fromId, toId, amount]
        );

        await conn.commit();
        console.log('Transfer complete');

    } catch (err) {
        await conn.rollback();  // undo all changes if anything failed
        throw err;
    } finally {
        conn.release();        // always release, even on error
    }
}
    

Error Handling

Database errors are thrown as standard JavaScript errors with extra properties. The most useful ones:

try {
    await conn.execute('INSERT INTO users (email) VALUES (?)', [email]);
} catch (err) {
    // MySQL error numbers — common ones worth handling explicitly
    if (err.code === 'ER_DUP_ENTRY') {         // errno 1062 — UNIQUE constraint
        throw new Error('Email already registered');
    }
    if (err.code === 'ER_NO_REFERENCED_ROW_2') { // errno 1452 — FK violation
        throw new Error('Referenced record does not exist');
    }
    if (err.code === 'ECONNREFUSED') {          // DB server not running
        throw new Error('Cannot reach database server');
    }
    throw err;  // re-throw anything unexpected
}

// Useful error properties
// err.code    — string error code (e.g. 'ER_DUP_ENTRY')
// err.errno   — numeric MySQL error number
// err.sql     — the SQL that caused the error
// err.message — human-readable description
    

Structuring Database Code — A db.js Module

Rather than scattering pool.execute() calls through your route handlers, collect SQL queries into a dedicated module. Each function wraps one logical operation and handles its own errors, keeping routes thin.

// db.js — pool singleton + query helpers
const mysql = require('mysql2/promise');

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     ?? 'app',
    connectionLimit: 10,
    waitForConnections: true,
});

async function findUserById(id) {
    const [rows] = await pool.execute(
        'SELECT id, name, email FROM users WHERE id = ? LIMIT 1', [id]
    );
    return rows[0] ?? null;
}

async function createUser(name, email, passwordHash) {
    const [result] = await pool.execute(
        'INSERT INTO users (name, email, password_hash) VALUES (?, ?, ?)',
        [name, email, passwordHash]
    );
    return result.insertId;
}

async function updateUserEmail(id, email) {
    const [result] = await pool.execute(
        'UPDATE users SET email = ? WHERE id = ?', [email, id]
    );
    return result.affectedRows > 0;
}

module.exports = { pool, findUserById, createUser, updateUserEmail };
    

// routes/users.js — thin route handlers, no raw SQL
const { findUserById, createUser } = require('../db');

router.get('/:id', async (req, res) => {
    const user = await findUserById(req.params.id);
    if (!user) return res.status(404).json({ error: 'Not found' });
    res.json(user);
});
    

⚠️ Common Mistakes

Using query() with user input — always use execute() for any value that comes from outside your code. query()'s escaping is client-side and can be bypassed in edge cases; execute()'s prepared-statement approach is structural and cannot.

Forgetting conn.release() in a finally block — if an error is thrown before release(), the connection leaks back into the pool unusable, eventually starving the application of connections under load.

Creating a pool per request — a pool should be a module-level singleton. Creating one per HTTP request spins up new connections on every hit and exhausts the database server's connection limit quickly.

Coding Challenges

Challenge 1 — User Repository

Build a UserRepository class that wraps a mysql2 pool and exposes: findById(id), findByEmail(email), create({ name, email, passwordHash }), update(id, fields) (where fields is a partial object of columns to update), and delete(id). The update() method must build the SET clause dynamically from the provided fields while still using parameterised values — never string-interpolate user data. Return null from finders when no row is found.

View sample solution ↗

Challenge 2 — Transactional Order Placement

Write a placeOrder(userId, items) function where items is an array of { productId, quantity }. The function must, within a single transaction: (1) verify each product exists and has sufficient stock, (2) create an order row, (3) insert one order_items row per item, (4) decrement the stock for each product. If any step fails — including insufficient stock — roll back the entire transaction and throw a descriptive error. Return the new order ID on success.

View sample solution ↗

Challenge 3 — Paginated Search

Write a searchUsers(options) function that accepts { query, page, pageSize, orderBy, orderDir } and returns { data, total, page, pageSize, totalPages }. The text search should match against both name and email using LIKE. The orderBy column must be validated against an allowlist before being interpolated into the SQL. The function should run the data query and the count query in parallel using Promise.all to minimise round-trip time.

View sample solution ↗