Node.js Database Integration: MongoDB, PostgreSQL, and MySQL

By 0xmote · Jun 29, 2026 · 24 min read · Updated Sep 27, 2026 Intermediate Tutorial

Key takeaways

Connect Node.js to MongoDB (Mongoose), PostgreSQL (pg, Sequelize), and MySQL (mysql2): connection pools, CRUD, transactions, REST examples, indexes, N+1 fixes, migrations, and production pitfalls.

Introduction

Most Node.js services end up talking to either a document store like MongoDB or a relational database like PostgreSQL or MySQL, and the driver or ORM you pick shapes how you handle connections, transactions and query performance. This post connects Node.js to all three (Mongoose, pg and Sequelize, mysql2), builds REST endpoints on top of them, and then covers the parts that cause trouble later: indexes and N+1 queries, connection pools, migrations, SQL injection, missing transactions, and graceful shutdown.

Choosing a database

Database	Strengths	Typical use
PostgreSQL	Powerful SQL, ACID	Complex queries, finance
MySQL	Fast, ubiquitous	Web apps, CMS
MongoDB	Flexible schema	Prototypes, document APIs
Redis	Very low latency	Cache, rate limits, sessions

The MongoDB-vs-PostgreSQL decision gets treated as a religious debate more often than a technical one, and in my experience the actual deciding factor is rarely raw performance — it’s how well your data’s shape matches each model’s strengths. A document store shines when your data is naturally nested and read together as a unit (a blog post with embedded comments you always fetch alongside it); a relational database shines the moment you need to enforce real constraints across entities (a payment can’t reference a user that doesn’t exist) or run ad-hoc analytical queries joining data in ways you didn’t anticipate when you designed the schema. I’ve seen a “we’ll just use MongoDB, it’s more flexible” decision come back to bite a team hard once the product grew enough relational structure (orders, line items, inventory, all cross-referencing each other) that they ended up hand-rolling the referential integrity a relational database would have enforced for free.

MongoDB (Mongoose)

Install

npm install mongodb
npm install mongoose

Connect

// db.js
const mongoose = require('mongoose');
const connectDB = async () => {
    try {
        await mongoose.connect('mongodb://localhost:27017/mydb', {
            useNewUrlParser: true,
            useUnifiedTopology: true
        });
        console.log('MongoDB connected');
    } catch (err) {
        console.error('MongoDB connection failed:', err.message);
        process.exit(1);
    }
};
module.exports = connectDB;

useNewUrlParser/useUnifiedTopology are worth flagging as version-specific relics rather than something to copy without thinking — they were required options on older Mongoose versions to opt into the (now-default) modern connection engine, and current Mongoose versions no longer need or recognize them at all. Leaving them in isn’t harmful (they’re silently ignored), but their presence is usually a sign of code copied from an older tutorial without checking against the currently-installed Mongoose version — worth double-checking against your actual package.json version rather than assuming every option from an older example still applies.

Schema

// models/User.js
const mongoose = require('mongoose');
const userSchema = new mongoose.Schema({
    name: {
        type: String,
        required: [true, 'Name is required'],
        trim: true,
        minlength: 2,
        maxlength: 50
    },
    email: {
        type: String,
        required: true,
        unique: true,
        lowercase: true,
        match: [/^\S+@\S+\.\S+$/, 'Please enter a valid email']
    },
    age: {
        type: Number,
        min: 0,
        max: 150
    },
    role: {
        type: String,
        enum: ['user', 'admin'],
        default: 'user'
    },
    isActive: {
        type: Boolean,
        default: true
    },
    createdAt: {
        type: Date,
        default: Date.now
    }
});
userSchema.index({ email: 1 });
userSchema.virtual('info').get(function() {
    return `${this.name} (${this.email})`;
});
userSchema.methods.greet = function() {
    return `Hello, ${this.name}!`;
};
userSchema.statics.findByEmail = function(email) {
    return this.findOne({ email });
};
userSchema.pre('save', function(next) {
    console.log('Before save:', this.name);
    next();
});
userSchema.post('save', function(doc) {
    console.log('After save:', doc.name);
});
module.exports = mongoose.model('User', userSchema);

The pre/post hooks and virtual here are where Mongoose earns the “ODM” (object-document mapper) part of its name rather than just being a thin validation layer — a virtual field like info computes a value on the fly from real fields without persisting it as its own document field (useful for anything derived, like a full name or a formatted display string), while pre('save', ...) lets you inject logic that runs automatically on every save without every caller remembering to call it manually. I’ve used pre('save') hooks for password hashing specifically (hash on save, never store plaintext even momentarily), and it’s a genuinely good fit for that — logic that absolutely must run on every write path gets a single, impossible-to-forget enforcement point instead of relying on every route handler remembering to call a helper function correctly.

CRUD

const User = require('./models/User');
async function createUser() {
    const user = new User({
        name: 'Alice',
        email: '[email protected]',
        age: 25
    });
    await user.save();
    const user2 = await User.create({
        name: 'Bob',
        email: '[email protected]',
        age: 30
    });
}
async function readUsers() {
    const users = await User.find();
    const adults = await User.find({ age: { $gte: 18 } });
    const user = await User.findOne({ email: '[email protected]' });
    const userById = await User.findById('507f1f77bcf86cd799439011');
    const names = await User.find().select('name email -_id');
    const sorted = await User.find().sort({ age: -1 });
    const page = 1;
    const limit = 10;
    const paginated = await User.find()
        .skip((page - 1) * limit)
        .limit(limit);
    return users;
}
async function updateUser(id) {
    await User.findByIdAndUpdate(id, { age: 26 }, { new: true, runValidators: true });
    const user2 = await User.findById(id);
    user2.age = 26;
    await user2.save();
    await User.updateMany({ age: { $lt: 18 } }, { isActive: false });
}
async function deleteUser(id) {
    await User.findByIdAndDelete(id);
    await User.deleteOne({ email: '[email protected]' });
    await User.deleteMany({ isActive: false });
}

The two update styles in updateUser — findByIdAndUpdate versus fetch-then-mutate-then-save — aren’t interchangeable conveniences, and picking the wrong one has bitten me in a real app before. findByIdAndUpdate sends one atomic update directly to MongoDB, which matters under concurrent writes: two requests updating the same document via findByIdAndUpdate can’t clobber each other’s changes to different fields, since each is a single atomic operation. The fetch-then-save pattern (findById → mutate → .save()) has a real race window between the read and the write — if another request modifies the document in that gap, .save() can silently overwrite it with stale data from the first request’s read, a classic lost-update bug that’s genuinely hard to reproduce in testing since it only shows up under real concurrent load.

Relationships

// models/Post.js
const postSchema = new mongoose.Schema({
    title: { type: String, required: true },
    content: { type: String, required: true },
    author: {
        type: mongoose.Schema.Types.ObjectId,
        ref: 'User',
        required: true
    },
    tags: [String],
    createdAt: { type: Date, default: Date.now }
});
const Post = mongoose.model('Post', postSchema);
const post = await Post.create({
    title: 'First post',
    content: 'Body',
    author: userId
});
const posts = await Post.find().populate('author');
const posts2 = await Post.find().populate('author', 'name email');

populate is doing something worth being explicit about, since it’s easy to assume MongoDB has real joins the way a relational database does — it doesn’t. Under the hood, .populate('author') runs a second, separate query against the User collection for every distinct author ObjectId found in the results, then stitches the results together in application code; it’s a convenience layer over two round trips, not a single atomic join at the database engine level. This matters for exactly the N+1 concern covered later in the Query Optimization section — populate solves the N+1 query count problem (one extra query instead of one per document), but it’s still fundamentally more round trips than a SQL JOIN resolves in a single query plan, which is one of the genuine tradeoffs of the document model versus relational for heavily cross-referenced data.

PostgreSQL (pg, Sequelize)

Install

npm install pg
npm install sequelize

Raw queries with `pg`

const { Pool } = require('pg');
const pool = new Pool({
    host: 'localhost',
    port: 5432,
    database: 'mydb',
    user: 'postgres',
    password: 'password',
    max: 20,
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 2000
});
module.exports = pool;

max: 20 deserves a specific gut-check before copying it into a real deployment: it’s a per-process limit, and if your app runs multiple instances (multiple containers, PM2 cluster mode, serverless functions each opening their own pool), the actual total connections against the database is max × number of instances — I’ve seen a service hit PostgreSQL’s default max_connections (typically 100) in production purely because nobody multiplied the per-instance pool size by the instance count when scaling out horizontally, and the failure mode (new connections refused) shows up as a confusing, intermittent error under load rather than an obvious configuration mistake.

const pool = require('./db');
async function getUsers() {
    const result = await pool.query('SELECT * FROM users');
    return result.rows;
}
async function createUser(name, email) {
    const query = 'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *';
    const result = await pool.query(query, [name, email]);
    return result.rows[0];
}
async function transferMoney(fromId, toId, amount) {
    const client = await pool.connect();
    try {
        await client.query('BEGIN');
        await client.query(
            'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
            [amount, fromId]
        );
        await client.query(
            'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
            [amount, toId]
        );
        await client.query('COMMIT');
        console.log('Transfer succeeded');
    } catch (err) {
        await client.query('ROLLBACK');
        console.error('Transfer failed:', err.message);
        throw err;
    } finally {
        client.release();
    }
}

try/finally around client.release() — not just try/catch — is the detail that actually matters here, and it’s easy to write this wrong in a way that looks correct: release() has to run whether the transaction commits, rolls back, or the code throws somewhere unexpected, because a client checked out via pool.connect() isn’t returned to the pool automatically — forgetting to release it (or only releasing it on the success path) leaks a connection out of the pool permanently for the life of the process. Enough leaked connections and the pool eventually exhausts itself, at which point every new request hangs waiting for a connection that’s never coming back, a genuinely nasty failure mode to diagnose in production since it looks like the database itself is unresponsive rather than a connection leak in application code.

Sequelize

const { Sequelize } = require('sequelize');
const sequelize = new Sequelize('mydb', 'postgres', 'password', {
    host: 'localhost',
    dialect: 'postgres',
    logging: false,
    pool: { max: 5, min: 0, acquire: 30000, idle: 10000 }
});
module.exports = sequelize;

logging: false is worth calling out simply because leaving it at Sequelize’s default (which logs every generated SQL statement to the console) is genuinely useful during development for seeing exactly what query an ORM call actually produced, but it’s real noise — and a real information leak, since generated SQL can reveal schema and query shape — in a production log stream. Flipping it based on NODE_ENV (verbose in dev, silent or routed to a real logger in production) is the pattern worth adopting rather than picking one setting for both environments.

const { DataTypes } = require('sequelize');
const sequelize = require('../db');
const { Op } = require('sequelize');
const User = sequelize.define('User', {
    id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    name: {
        type: DataTypes.STRING(100),
        allowNull: false,
        validate: { len: [2, 50] }
    },
    email: {
        type: DataTypes.STRING(255),
        allowNull: false,
        unique: true,
        validate: { isEmail: true }
    },
    age: {
        type: DataTypes.INTEGER,
        validate: { min: 0, max: 150 }
    },
    role: {
        type: DataTypes.ENUM('user', 'admin'),
        defaultValue: 'user'
    }
}, {
    tableName: 'users',
    timestamps: true
});
module.exports = User;

sequelize.define(...) here is describing the model’s shape in JavaScript, and it’s worth understanding that this alone doesn’t create or alter the actual users table in the database — that’s a separate, deliberate step (sync() for quick prototyping, or the real migrations covered in Section 7) precisely so that a model definition change in code doesn’t silently and automatically mutate a production schema the moment the app restarts. Treating the model definition as documentation of the expected shape and migrations as the actual mechanism that gets the database to match it is the safer mental model, especially once a schema has real production data that a naive auto-sync could damage.

const { Op } = require('sequelize');
const User = require('./models/User');
const user = await User.create({
    name: 'Alice',
    email: '[email protected]',
    age: 25
});
const users = await User.findAll();
const one = await User.findByPk(1);
const filtered = await User.findAll({
    where: { age: { [Op.gte]: 18 } },
    order: [['createdAt', 'DESC']],
    limit: 10,
    offset: 0
});
await User.update({ age: 26 }, { where: { id: 1 } });
await User.destroy({ where: { id: 1 } });

Op.gte and friends exist because plain JavaScript object literals have no native way to express “greater than or equal” as a key — { age: { [Op.gte]: 18 } } uses a computed property key (the Symbol Op.gte evaluates to) specifically so Sequelize can distinguish “match this exact nested object” from “apply this comparison operator,” which a plain string key like { age: { gte: 18 } } couldn’t do unambiguously. It’s worth knowing these operators exist as real Symbols under the hood, not magic strings — which is also why they have to be imported and referenced via Op.x rather than typed as plain object keys.

MySQL

npm install mysql2

const mysql = require('mysql2/promise');
const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'mydb',
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});
module.exports = pool;

const pool = require('./db');
async function getUsers() {
    const [rows] = await pool.query('SELECT * FROM users');
    return rows;
}
// The destructured `[rows]` here is mysql2-specific and easy to get wrong
// coming from `pg`, where `pool.query(...)` returns a result object with a
// `.rows` property directly. mysql2's promise API instead returns a
// two-element array — [rows, fields] — so `const [rows] = await ...`
// destructures out just the row data and discards the column metadata in
// `fields`, which most application code never needs but which is genuinely
// there if you do (column types, names — useful for building generic
// tooling on top of raw query results).
async function transferMoney(fromId, toId, amount) {
    const connection = await pool.getConnection();
    try {
        await connection.beginTransaction();
        await connection.query(
            'UPDATE accounts SET balance = balance - ? WHERE id = ?',
            [amount, fromId]
        );
        await connection.query(
            'UPDATE accounts SET balance = balance + ? WHERE id = ?',
            [amount, toId]
        );
        await connection.commit();
        console.log('Transfer succeeded');
    } catch (err) {
        await connection.rollback();
        console.error('Transfer failed:', err.message);
        throw err;
    } finally {
        connection.release();
    }
}

Notice the parameter placeholder syntax differs from the pg example earlier — mysql2 uses positional ? placeholders filled in argument order, while pg uses numbered $1, $2 placeholders that can be referenced (and even repeated) explicitly by number. Both exist specifically to prevent SQL injection by keeping user-supplied values entirely separate from the query structure (covered more directly in the Common Pitfalls section later) — but mixing up the two drivers’ placeholder styles, easy to do when a codebase touches both, produces a syntax error rather than a silent injection vulnerability, which is at least a loud failure rather than a quiet one.

REST API examples

MongoDB + Express

const express = require('express');
const mongoose = require('mongoose');
const User = require('./models/User');
const app = express();
app.use(express.json());
mongoose.connect('mongodb://localhost:27017/mydb')
    .then(() => console.log('MongoDB connected'))
    .catch(err => console.error('Connection failed:', err));
app.get('/api/users', async (req, res) => {
    try {
        const { page = 1, limit = 10, sort = '-createdAt' } = req.query;
        const users = await User.find()
            .sort(sort)
            .skip((page - 1) * limit)
            .limit(parseInt(limit, 10));
        const total = await User.countDocuments();
        res.json({
            users,
            pagination: {
                page: parseInt(page, 10),
                limit: parseInt(limit, 10),
                total,
                pages: Math.ceil(total / limit)
            }
        });
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});
app.get('/api/users/:id', async (req, res) => {
    try {
        const user = await User.findById(req.params.id);
        if (!user) {
            return res.status(404).json({ error: 'User not found' });
        }
        res.json(user);
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});
app.post('/api/users', async (req, res) => {
    try {
        const user = await User.create(req.body);
        res.status(201).json(user);
    } catch (err) {
        if (err.name === 'ValidationError') {
            return res.status(400).json({ error: err.message });
        }
        res.status(500).json({ error: err.message });
    }
});
app.put('/api/users/:id', async (req, res) => {
    try {
        const user = await User.findByIdAndUpdate(
            req.params.id,
            req.body,
            { new: true, runValidators: true }
        );
        if (!user) {
            return res.status(404).json({ error: 'User not found' });
        }
        res.json(user);
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});
app.delete('/api/users/:id', async (req, res) => {
    try {
        const user = await User.findByIdAndDelete(req.params.id);
        if (!user) {
            return res.status(404).json({ error: 'User not found' });
        }
        res.status(204).send();
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});
app.listen(3000, () => {
    console.log('Server listening on http://localhost:3000');
});

The err.name === 'ValidationError' check in the POST handler is doing real, necessary work distinguishing two genuinely different failure categories that a naive catch-all would conflate — a validation error (bad user input: a malformed email, a missing required field) is the client’s fault and correctly maps to a 400, while any other error (a database connection drop, an unexpected bug) is a server-side problem correctly mapped to a 500. Returning a generic 500 for both, which is what happens if this check is left out, means client-facing validation feedback disappears — the frontend gets an opaque “Internal Server Error” instead of the actual, actionable “email is required” message Mongoose’s validation already generated for free.

PostgreSQL + Express (illustrative)

Worth flagging directly rather than letting it slip by: despite the section header, the code below is actually written in MySQL/mysql2 style, not PostgreSQL’s — ? positional placeholders (PostgreSQL uses numbered $1, $2), result.insertId for the newly-created row’s ID (PostgreSQL has no insertId; you’d use INSERT ... RETURNING * and read the returned row instead, as shown in the raw pg example earlier in this guide), and err.code === 'ER_DUP_ENTRY' for a duplicate-key error (PostgreSQL’s equivalent is the SQLSTATE code '23505', not a MySQL-specific error string). If you’re actually targeting PostgreSQL, adapt this example to the pg-style query and error-handling patterns shown in Section 2 rather than copying it as-is.

const express = require('express');
const pool = require('./db');
const app = express();
app.use(express.json());
app.get('/api/users', async (req, res) => {
    try {
        const { page = 1, limit = 10 } = req.query;
        const offset = (page - 1) * limit;
        const [users] = await pool.query(
            'SELECT * FROM users ORDER BY created_at DESC LIMIT ? OFFSET ?',
            [parseInt(limit, 10), offset]
        );
        const [[{ total }]] = await pool.query('SELECT COUNT(*) as total FROM users');
        res.json({
            users,
            pagination: {
                page: parseInt(page, 10),
                limit: parseInt(limit, 10),
                total,
                pages: Math.ceil(total / limit)
            }
        });
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});
app.post('/api/users', async (req, res) => {
    try {
        const { name, email, age } = req.body;
        const [result] = await pool.query(
            'INSERT INTO users (name, email, age) VALUES (?, ?, ?)',
            [name, email, age]
        );
        const [users] = await pool.query('SELECT * FROM users WHERE id = ?', [result.insertId]);
        res.status(201).json(users[0]);
    } catch (err) {
        if (err.code === 'ER_DUP_ENTRY') {
            return res.status(400).json({ error: 'Email already exists' });
        }
        res.status(500).json({ error: err.message });
    }
});
app.listen(3000);

Query optimization

Indexes

userSchema.index({ email: 1 });
userSchema.index({ name: 1, age: -1 });
userSchema.index({ email: 1 }, { unique: true });
await pool.query('CREATE INDEX idx_email ON users(email)');
await pool.query('CREATE INDEX idx_name_age ON users(name, age)');
await pool.query('CREATE UNIQUE INDEX idx_email_unique ON users(email)');

The compound index { name: 1, age: -1 } (and its SQL equivalent idx_name_age) is worth understanding as more than “index both fields” — field order in a compound index matters, because the index is only efficiently usable as a prefix match: this specific index speeds up queries filtering/sorting by name alone, or by name and then age together, but it does not meaningfully help a query that filters by age alone, since age isn’t the leading field. Creating indexes without thinking about which field order actually matches your real query patterns is a common way to end up with indexes that exist, get maintained on every write (a real cost), and never actually get used by the query planner — worth checking EXPLAIN/.explain() output against real queries rather than assuming an index helps just because it exists.

Fixing N+1

// Bad: N+1 queries
const posts = await Post.find();
for (const post of posts) {
    const author = await User.findById(post.author);
    console.log(author.name);
}
// Good: populate
const posts = await Post.find().populate('author');
for (const post of posts) {
    console.log(post.author.name);
}

The “bad” version here is a genuinely easy trap to fall into because it reads perfectly naturally — a for loop with an await inside feels like ordinary, correct async code, and it works on a small dataset in local testing, which is exactly why it tends to slip past code review. The problem only becomes visible under real data volume: 100 posts means 1 query to fetch them plus 100 more, one per iteration, each waiting on its own round trip to the database — and I’ve genuinely seen a page go from a barely-noticeable few hundred milliseconds locally (a handful of posts, low network latency to a local dev database) to several seconds in production once real data volume and real network latency to a remote database both kicked in simultaneously. populate collapses that into exactly 2 queries total, regardless of how many posts there are — the fix is one word, but only once you recognize the pattern that requires it.

Projection

const users = await User.find().select('name email');
const [usersPg] = await pool.query('SELECT name, email FROM users');

Projection is easy to treat as a minor optimization, but it’s worth taking seriously on any document with large or numerous fields — User.find() with no .select() pulls every field of every matching document across the wire, including ones a given code path never actually reads, which wastes both database I/O and network bandwidth proportional to document size and result count. It’s also a real security-relevant habit, not just a performance one: explicitly selecting only the fields a given response actually needs (rather than serializing an entire document and hoping nothing sensitive leaks through) is a meaningfully safer default than accidentally including a password hash or an internal field in an API response because nobody thought to exclude it.

Pagination helpers

async function paginateUsersMongo(page = 1, limit = 10) {
    const skip = (page - 1) * limit;
    const [users, total] = await Promise.all([
        User.find().skip(skip).limit(limit),
        User.countDocuments()
    ]);
    return {
        users,
        page,
        limit,
        total,
        pages: Math.ceil(total / limit)
    };
}
async function paginateUsersSql(page = 1, limit = 10) {
    const offset = (page - 1) * limit;
    const [users] = await pool.query(
        'SELECT * FROM users LIMIT ? OFFSET ?',
        [limit, offset]
    );
    const [[{ total }]] = await pool.query('SELECT COUNT(*) as total FROM users');
    return {
        users,
        page,
        limit,
        total,
        pages: Math.ceil(total / limit)
    };
}

Promise.all running the data query and the count query concurrently (in the Mongo version) rather than sequentially is a small detail worth not skipping — both queries are independent of each other’s results, so awaiting them one after another wastes exactly the round-trip time of whichever one happens to run second, for no benefit. It’s a cheap, easy win that’s worth reaching for as a default whenever a function needs the results of two or more independent async operations, rather than reflexively writing sequential await statements just because that’s the most linear way to read the code.

Worth flagging a real scaling limitation of offset-based pagination itself, not just this implementation: LIMIT ? OFFSET ? (and Mongo’s .skip().limit()) requires the database to actually scan through and discard every row before the offset, so page 1000 of a large table is meaningfully slower than page 1 — the further into a large result set you paginate, the more wasted work each request does. Cursor-based pagination (using the last-seen document’s ID or a sort key as the starting point for the next page, rather than a raw numeric offset) avoids that scaling problem entirely, at the cost of not being able to jump directly to an arbitrary page number — a tradeoff worth knowing about before offset-based pagination becomes a real bottleneck on a large, frequently-paginated table.

Connection pools

Every driver here exposes essentially the same three-knob shape — a maximum, an idle timeout, and a connection timeout — because they’re all solving the same underlying problem: opening a new TCP connection and completing a database handshake (auth, TLS negotiation) is expensive, often tens of milliseconds, so paying that cost on every single query would make a busy app noticeably slower than reusing a small set of already-open connections. The max/min bounds exist to balance two competing failure modes — too few connections and requests queue up waiting for one to free up under load, too many and you risk overwhelming the database server itself (which has its own connection limit, often lower than you’d expect, and each idle connection still costs the server memory).

mongoose.connect('mongodb://localhost:27017/mydb', {
    maxPoolSize: 10,
    minPoolSize: 2,
    maxIdleTimeMS: 30000
});
const { Pool } = require('pg');
const pool = new Pool({
    max: 20,
    min: 5,
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 2000
});
const mysql = require('mysql2/promise');
mysql.createPool({
    connectionLimit: 10,
    queueLimit: 0,
    waitForConnections: true
});

Pool events (pg)

pool.on('connect', () => console.log('New client connected'));
pool.on('acquire', () => console.log('Client acquired'));
pool.on('release', () => console.log('Client released'));

These events are worth wiring up during a real debugging session, not on every project by default — they’re primarily useful for diagnosing pool exhaustion, where acquire events start piling up (many clients requesting a connection) without a matching rate of release events, which is the signature pattern of a connection leak like the one covered in the Common Pitfalls section below. In steady-state healthy operation they’re mostly noise; the value is in temporarily enabling them (or a metrics-based equivalent) when connection-pool behavior is genuinely suspect.

Migrations (Sequelize)

npm install --save-dev sequelize-cli
npx sequelize-cli init
npx sequelize-cli migration:generate --name create-users-table

Migrations exist to solve a problem sequelize.define() and even sync({ force: true }) can’t safely solve on a production database: they’re a versioned, ordered, incremental history of schema changes, each one small and reversible (that’s what the paired up/down functions are for), applied one at a time in a known sequence across every environment. This matters enormously the moment a database has real, irreplaceable data in it — I’ve seen sync({ alter: true }) (Sequelize’s “just make the database match my model definition” auto-sync) drop and recreate a column because it guessed the intended change wrong, silently destroying real production data with no rollback path, which is exactly the class of accident a reviewed, incremental migration with an explicit down function is designed to prevent.

// migrations/20260329-create-users-table.js
module.exports = {
    up: async (queryInterface, Sequelize) => {
        await queryInterface.createTable('users', {
            id: {
                type: Sequelize.INTEGER,
                primaryKey: true,
                autoIncrement: true
            },
            name: {
                type: Sequelize.STRING(100),
                allowNull: false
            },
            email: {
                type: Sequelize.STRING(255),
                allowNull: false,
                unique: true
            },
            age: {
                type: Sequelize.INTEGER
            },
            created_at: {
                type: Sequelize.DATE,
                defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
            },
            updated_at: {
                type: Sequelize.DATE,
                defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
            }
        });
        await queryInterface.addIndex('users', ['email']);
    },
    down: async (queryInterface) => {
        await queryInterface.dropTable('users');
    }
};

npx sequelize-cli db:migrate
npx sequelize-cli db:migrate:undo
npx sequelize-cli db:migrate:undo:all

down deserves more attention than it typically gets in practice — it’s tempting to write it once, never test it, and assume it’s correct, but an untested down function is a rollback plan you’ve never actually verified works, which is precisely the moment you’d want to rely on it most (a bad deploy, a migration that needs to be reverted under pressure). Actually running db:migrate:undo against a real (non-production) database as part of testing a new migration, not just db:migrate, is the only way to know the rollback genuinely restores the prior schema rather than erroring out or leaving the database in a half-migrated state.

Example: blog API (MongoDB)

This example ties several earlier concepts together into something closer to a real feature: the compound index on { author: 1, createdAt: -1 } exists specifically because the query pattern (filter by author, sort by newest first) is exactly what an index on those two fields together, in that order, is built to serve efficiently — MongoDB can use it to satisfy both the filter and the sort in one index scan, rather than filtering first and then sorting the results separately. The text index ({ title: 'text', content: 'text' }) is a different, purpose-built mechanism for the /search route below it — genuine full-text search with relevance scoring ($meta: 'textScore'), not a LIKE '%query%'-style substring match, which is the naive approach this kind of index exists specifically to avoid needing.

// models/Post.js
const mongoose = require('mongoose');
const postSchema = new mongoose.Schema({
    title: {
        type: String,
        required: true,
        trim: true,
        minlength: 1,
        maxlength: 200
    },
    content: { type: String, required: true },
    author: {
        type: mongoose.Schema.Types.ObjectId,
        ref: 'User',
        required: true
    },
    tags: [String],
    published: { type: Boolean, default: false },
    views: { type: Number, default: 0 }
}, { timestamps: true });
postSchema.index({ title: 'text', content: 'text' });
postSchema.index({ author: 1, createdAt: -1 });
postSchema.virtual('url').get(function() {
    return `/posts/${this._id}`;
});
module.exports = mongoose.model('Post', postSchema);

// routes/posts.js
const express = require('express');
const router = express.Router();
const Post = require('../models/Post');
router.get('/', async (req, res) => {
    try {
        const { page = 1, limit = 10, tag, author } = req.query;
        const query = {};
        if (tag) query.tags = tag;
        if (author) query.author = author;
        const posts = await Post.find(query)
            .populate('author', 'name email')
            .sort({ createdAt: -1 })
            .skip((page - 1) * limit)
            .limit(parseInt(limit, 10));
        const total = await Post.countDocuments(query);
        res.json({
            posts,
            pagination: {
                page: parseInt(page, 10),
                limit: parseInt(limit, 10),
                total,
                pages: Math.ceil(total / limit)
            }
        });
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});
router.get('/search', async (req, res) => {
    try {
        const { q } = req.query;
        if (!q) {
            return res.status(400).json({ error: 'Search query required' });
        }
        const posts = await Post.find(
            { $text: { $search: q } },
            { score: { $meta: 'textScore' } }
        )
            .sort({ score: { $meta: 'textScore' } })
            .populate('author', 'name');
        res.json({ posts, count: posts.length });
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});
router.post('/', async (req, res) => {
    try {
        const post = await Post.create({
            ...req.body,
            author: req.user.id
        });
        await post.populate('author', 'name email');
        res.status(201).json(post);
    } catch (err) {
        if (err.name === 'ValidationError') {
            return res.status(400).json({ error: err.message });
        }
        res.status(500).json({ error: err.message });
    }
});
router.post('/:id/view', async (req, res) => {
    try {
        const post = await Post.findByIdAndUpdate(
            req.params.id,
            { $inc: { views: 1 } },
            { new: true }
        );
        if (!post) {
            return res.status(404).json({ error: 'Post not found' });
        }
        res.json({ views: post.views });
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});
module.exports = router;

Common pitfalls

Connection leaks

This is the same connection-leak mistake covered earlier with pool.connect(), shown again here because it’s genuinely the single most common production incident I’ve seen tied to database code, and it’s worth seeing in its most minimal, unmissable form. The bad() version looks completely correct — it does the query, returns the rows, nothing looks obviously wrong — and that’s exactly the danger: it works perfectly until the query throws (a timeout, a malformed query, a transient network blip), at which point the function exits without ever reaching a connection.release() call, and that connection is gone from the pool’s available set forever. A service can run for hours or days accumulating leaked connections one error at a time, with no symptom at all until the pool is fully exhausted and every new request starts hanging — which makes this a genuinely nasty bug to trace back to its root cause after the fact, since the crash happens far in time and code from the actual leak.

async function bad() {
    const connection = await pool.getConnection();
    const [rows] = await connection.query('SELECT * FROM users');
    return rows;
}
async function good() {
    const connection = await pool.getConnection();
    try {
        const [rows] = await connection.query('SELECT * FROM users');
        return rows;
    } finally {
        connection.release();
    }
}

SQL injection

The vulnerable version isn’t a contrived example — string-interpolating user input directly into a query is exactly what happens when someone reaches for a template literal out of habit, since it looks like the more “modern” JavaScript way to build a string compared to the placeholder syntax. The actual risk: an email input like ' OR '1'='1 doesn’t just fail to match a real user, it changes the query’s logical structure entirely, turning a single-row lookup into “return every row” — and more sophisticated payloads can chain additional statements, exfiltrate data through error messages, or worse, depending on what the query is doing and what privileges the database user has. Parameterized queries (the ?/$1 placeholder forms used throughout this guide) aren’t a stylistic preference over string interpolation, they’re the actual fix — the driver sends the query structure and the user-supplied values as separate things to the database, so a malicious string can never be reinterpreted as SQL syntax no matter what it contains.

async function vulnerable(email) {
    const query = `SELECT * FROM users WHERE email = '${email}'`;
    const [rows] = await pool.query(query);
    return rows;
}
async function safe(email) {
    const [rows] = await pool.query(
        'SELECT * FROM users WHERE email = ?',
        [email]
    );
    return rows;
}

Missing transactions

The badTransfer version’s real danger is that it’s correct in the overwhelmingly common case and silently wrong in the rare one — if the process crashes, the connection drops, or the second query throws for any reason after the first one has already committed, the money is deducted from fromId but never credited to toId, and that money is now simply gone from the system’s accounting with no error surfaced to anyone. This is exactly the kind of bug that passes every normal test (both queries usually succeed) and only manifests under real production failure conditions, which is precisely when a financial discrepancy is most costly and hardest to trace back to its cause. Wrapping both statements in a real transaction is what gives you the actual guarantee this operation needs: either both updates apply, or neither does — there’s no possible intermediate state where money has left one account without arriving at the other, which is the entire reason transactions exist as a primitive rather than a convenience.

async function badTransfer(fromId, toId, amount) {
    await pool.query('UPDATE accounts SET balance = balance - ? WHERE id = ?', [amount, fromId]);
    await pool.query('UPDATE accounts SET balance = balance + ? WHERE id = ?', [amount, toId]);
}
async function goodTransfer(fromId, toId, amount) {
    const connection = await pool.getConnection();
    try {
        await connection.beginTransaction();
        await connection.query('UPDATE accounts SET balance = balance - ? WHERE id = ?', [amount, fromId]);
        await connection.query('UPDATE accounts SET balance = balance + ? WHERE id = ?', [amount, toId]);
        await connection.commit();
    } catch (err) {
        await connection.rollback();
        throw err;
    } finally {
        connection.release();
    }
}

Production tips

Environment-specific config

The pattern worth noticing here goes beyond “use environment variables” — it’s that credentials and connection details are read from process.env only in the production branch, while development hardcodes plausible local defaults directly. This isn’t inconsistency; local development credentials pointing at a throwaway local database carry essentially no risk if they end up in version control, while production credentials absolutely cannot, so treating the two environments asymmetrically here is deliberate rather than an oversight. ssl: true on the production Postgres config is easy to skip in a quick example but matters for real deployments — without it, credentials and query data travel to a remote database server in plaintext over the network, which is a meaningfully different risk profile than a local Postgres instance on the same machine.

module.exports = {
    development: {
        mongodb: 'mongodb://localhost:27017/mydb-dev',
        postgres: {
            host: 'localhost',
            database: 'mydb_dev',
            user: 'postgres',
            password: 'password'
        }
    },
    production: {
        mongodb: process.env.MONGODB_URI,
        postgres: {
            host: process.env.DB_HOST,
            database: process.env.DB_NAME,
            user: process.env.DB_USER,
            password: process.env.DB_PASSWORD,
            ssl: true
        }
    }
};

Retry MongoDB connection

This matters more in container-orchestrated deployments than a lot of teams initially expect — in Docker Compose or Kubernetes, an application container frequently starts up before its database container has finished initializing and is actually ready to accept connections, and a Node process that fails immediately on the first connection attempt with no retry logic just crash-loops forever waiting for a database that would have been ready a few seconds later. Exponential backoff (Math.pow(2, i) * 1000 — 1s, 2s, 4s, 8s…) rather than a fixed retry interval matters too: it avoids hammering a database that’s still starting up (or genuinely down) with an aggressive fixed-interval retry storm, while still recovering quickly for the common case where the database becomes available within the first second or two.

async function connectWithRetry(maxRetries = 5) {
    for (let i = 0; i < maxRetries; i++) {
        try {
            await mongoose.connect('mongodb://localhost:27017/mydb');
            console.log('MongoDB connected');
            return;
        } catch (err) {
            console.error(`Connection failed (${i + 1}/${maxRetries}):`, err.message);
            if (i === maxRetries - 1) throw err;
            const delay = Math.pow(2, i) * 1000;
            console.log(`Retrying in ${delay}ms...`);
            await new Promise(resolve => setTimeout(resolve, delay));
        }
    }
}

Graceful shutdown

SIGTERM is specifically the signal orchestration platforms (Docker, Kubernetes, most PaaS providers) send before forcibly killing a container during a deploy, scale-down, or restart — and without a handler for it, the default behavior is an immediate, hard process exit, which can leave in-flight database writes half-committed or connections in a genuinely inconsistent state on the server side. Handling it gracefully (closing connections cleanly, letting in-flight requests finish, then exiting) is what turns routine deploys from an occasional source of dropped requests and connection errors into a non-event users never notice — worth treating as a standard, close-to-mandatory pattern for anything running in a container-orchestrated environment rather than an advanced production nicety.

const mongoose = require('mongoose');
async function gracefulShutdown() {
    console.log('Shutting down...');
    try {
        await mongoose.connection.close();
        console.log('MongoDB closed');
        await pool.end();
        console.log('PostgreSQL pool closed');
        process.exit(0);
    } catch (err) {
        console.error('Shutdown error:', err.message);
        process.exit(1);
    }
}
process.on('SIGTERM', gracefulShutdown);
process.on('SIGINT', gracefulShutdown);

Key takeaways

Introduction

Choosing a database

MongoDB (Mongoose)

Install

Connect

Schema

CRUD

Relationships

PostgreSQL (pg, Sequelize)

Install

Raw queries with pg

Sequelize

MySQL

REST API examples

MongoDB + Express

PostgreSQL + Express (illustrative)

Query optimization

Indexes

Fixing N+1

Projection

Pagination helpers

Connection pools

Pool events (pg)

Migrations (Sequelize)

Example: blog API (MongoDB)

Common pitfalls

Connection leaks

SQL injection

Missing transactions

Production tips

Environment-specific config

Retry MongoDB connection

Graceful shutdown

Related Articles

Raw queries with `pg`