Database Integration

This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production.

The reference material consulted while preparing these pages is listed in the section bibliography.

This page is written against Express 5.3 (npm express 5.3.0, published 2026-10-09) on Node.js 18 or later, the minimum for Express 5, and it states where Express 4 (latest 4.22.3) behaves differently. Everything was checked against the official documentation and the package repositories on 2026-10-29. Express has no database layer of its own: as the official guide puts it, you load the Node.js driver or ORM for your database in the application and use it from route handlers. What decides whether an application is reliable is therefore not the driver call, but the lifecycle around it: when the connection is opened, how many are shared, what happens when a query fails, and whether everything is closed when the process is asked to stop. The page starts with the approach of the official guide (the built-in node:sqlite module), grows it into a repository layer and a paginated list endpoint for the Bookshelf API used throughout this section, and then shows the same lifecycle with PostgreSQL, MySQL, MongoDB, Redis and the main ORMs and query builders. Sequelize and its migrations get a section of their own. The examples use ESM and the node: prefix for built-in modules, and contain placeholders only, never real secrets. Document databases are treated in depth in MongoDB & Mongoose; the engines themselves are out of scope here.

The lifecycle that every driver shares

Whatever the driver, the same five rules apply, and the rest of the page is a series of variations on them:

Create the pool or client once, at startup, in a module that the rest of the code imports. Opening a connection is expensive (a TCP handshake, TLS, authentication), so a connection per request throws away most of the performance of the database.
Connect before listening. A server that starts accepting requests before the database is reachable answers the first requests with errors. Await the connection check, then call app.listen(), and let the process exit with a clear message if the database cannot be reached.
Pass values as parameters, never concatenated into the SQL text. Parameterized queries are the defence against SQL injection; see Validation & Input Handling for the input side.
Hold a connection only as long as needed, and always release it in a finally block when you checked one out for a transaction.
Close the pool at shutdown, after the HTTP server has stopped accepting and draining requests, so that no in-flight request loses its connection.

flowchart TD A["Process starts"] --> B["Load configuration from environment variables"] B --> C["Create the pool or client once"] C --> D{"Can the database be reached?"} D -->|"no"| E["Log the error and exit with a non-zero code"] D -->|"yes"| F["Run pending migrations (optional, see the Sequelize section)"] F --> G["app.listen(port)"] G --> H["Serve requests: handlers borrow connections from the pool"] H --> I["SIGTERM or SIGINT"] I --> J["server.close() and closeIdleConnections()"] J --> K["pool.end() or client.close()"] K --> L["Process exits with code 0"]

Source: Express: Database integration

The sequence of a single request is shorter. The handler asks the repository, the repository borrows a connection from the pool only for the duration of one query (or one transaction), and the result becomes a response. Errors take the normal Express path described in Error Handling.

sequenceDiagram participant C as Client participant E as Express route handler participant R as Repository participant P as Pool participant D as Database C->>E: GET /api/books?limit=20 E->>R: list with limit and cursor R->>P: query with text and values P->>D: borrow a connection and send the statement D-->>P: rows P-->>R: rows (connection returned to the pool) R-->>E: books and next cursor E-->>C: 200 with JSON and a Link header

SQLite with `node:sqlite`

The official guide uses SQLite as its example because nothing has to be installed or run: Node.js ships a built-in client, node:sqlite, available without a flag since Node.js 22.13.0 and 23.4.0 (earlier 22.x and 23.x releases required --experimental-sqlite). For older Node.js versions the guide points to the third-party better-sqlite3, which has a similar synchronous API. The module is still young: the Node.js documentation marks it with a stability index below "stable" (a release candidate since Node.js 25.7), so check the stability banner of the Node.js version you deploy on and expect small API changes between releases. Express 5 itself needs nothing special for it.

The API is synchronous. A DatabaseSync object opens the file, db.prepare(sql) returns a prepared statement, and the statement offers get() for one row, all() for every row and run() for writes (which returns changes and lastInsertRowid). Parameters are bound with ? placeholders or named parameters, and values come back as plain objects. A minimal application, derived from the official example, looks like this:

// index.mjs
import express from 'express';
import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');

db.exec(`
  CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
  )
`);
db.prepare('INSERT INTO users (name) VALUES (?)').run('Ada Lovelace');

// Prepare once, execute many times.
const findUser = db.prepare('SELECT id, name FROM users WHERE id = ?');

const app = express();

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

app.listen(3000);

Source: Express: Database integration

Three details of this small program are worth explaining, because they recur in every other driver:

The statement is prepared once, outside the handler, and only executed inside it. Preparing parses the SQL once; executing binds new values. It also keeps the SQL text constant, which is what makes the query injection-safe.
req.params.id is a string, and SQLite compares it to the integer key with its usual type affinity. A real API validates and converts it first.
The program is synchronous end to end. A synchronous driver call blocks the single JavaScript thread that serves every other request, which is harmless for a point lookup on a local file and a real problem for a long query. The official guide says as much: for workloads with long-running queries, move the database work to a worker thread or use an asynchronous driver.

Because SQLite is a file, :memory: gives a fresh database per process, which is ideal for tests (see Testing), and a path such as the value of an environment variable gives persistence. Enable foreign keys explicitly with PRAGMA foreign_keys = ON, because SQLite leaves them off by default, and consider PRAGMA journal_mode = WAL for read-heavy services.

A repository layer for Bookshelf

Handlers that contain SQL are hard to test and hard to swap. The structure used for the rest of the section keeps the SQL in a repository module that exports plain functions, and keeps the HTTP concerns in the router. The repository below is written against node:sqlite, but its functions return plain objects and could be reimplemented with pg or an ORM without touching the router.

// src/db.mjs
import { DatabaseSync } from 'node:sqlite';

export function openDatabase(filename = process.env.SQLITE_FILE ?? ':memory:') {
  const db = new DatabaseSync(filename);
  db.exec('PRAGMA foreign_keys = ON');
  db.exec(`
    CREATE TABLE IF NOT EXISTS books (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      title TEXT NOT NULL,
      author TEXT NOT NULL,
      published_year INTEGER,
      created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
    )
  `);
  return db;
}

// src/books-repository.mjs
export function createBooksRepository(db) {
  const insert = db.prepare(
    'INSERT INTO books (title, author, published_year) VALUES (?, ?, ?)',
  );
  const byId = db.prepare(
    'SELECT id, title, author, published_year AS publishedYear FROM books WHERE id = ?',
  );
  const firstPage = db.prepare(
    'SELECT id, title, author, published_year AS publishedYear FROM books ORDER BY id LIMIT ?',
  );
  const nextPage = db.prepare(
    'SELECT id, title, author, published_year AS publishedYear FROM books WHERE id > ? ORDER BY id LIMIT ?',
  );
  const remove = db.prepare('DELETE FROM books WHERE id = ?');

  return {
    create({ title, author, publishedYear = null }) {
      const { lastInsertRowid } = insert.run(title, author, publishedYear);
      return byId.get(lastInsertRowid);
    },
    findById(id) {
      return byId.get(id);
    },
    /** Returns up to `limit` books after the given cursor, plus the cursor of the next page. */
    list({ limit, after }) {
      // Ask for one extra row to learn whether another page exists.
      const rows =
        after === undefined
          ? firstPage.all(limit + 1)
          : nextPage.all(after, limit + 1);
      const hasMore = rows.length > limit;
      const items = hasMore ? rows.slice(0, limit) : rows;
      return { items, nextCursor: hasMore ? items.at(-1).id : null };
    },
    delete(id) {
      return remove.run(id).changes > 0;
    },
  };
}

Source: Node.js: SQLite

SQLite returns the integer lastInsertRowid as a number by default (a bigint only when the statement is configured to read big integers), so it can be passed straight back to byId.get().

Pagination and `Link` headers

A list endpoint that returns every row is a production incident waiting for the table to grow. There are two common strategies, and the choice is a database question as much as an HTTP one. The engine-level comparison is in the database section of this site; what matters from Express is how the parameters arrive and how the next page is announced.

Strategy How it works Trade-offs

Strategy	How it works	Trade-offs
Offset (`LIMIT n OFFSET m`)	The client sends `page` and `pageSize`, or `limit` and `offset`; the database skips the first rows.	Simple, and allows jumping to page 50. The database still reads and discards every skipped row, so deep pages are slow, and rows inserted or deleted while the client pages cause duplicates or gaps.
Keyset, also called cursor pagination (`WHERE id > ? ORDER BY id LIMIT n`)	The client sends the last key it has seen (or an opaque cursor that encodes it); the database seeks to it through an index.	Cost is constant however deep the client goes and results are stable under concurrent writes. No random access to a page number, and the sort key must be unique (add a tie-breaker column, for example `(published_year, id)`).

Offset (LIMIT n OFFSET m)

The client sends page and pageSize, or limit and offset; the database skips the first rows.

Simple, and allows jumping to page 50. The database still reads and discards every skipped row, so deep pages are slow, and rows inserted or deleted while the client pages cause duplicates or gaps.

Keyset, also called cursor pagination (WHERE id > ? ORDER BY id LIMIT n)

The client sends the last key it has seen (or an opaque cursor that encodes it); the database seeks to it through an index.

Cost is constant however deep the client goes and results are stable under concurrent writes. No random access to a page number, and the sort key must be unique (add a tie-breaker column, for example (published_year, id)).

Both read their parameters from req.query. In Express 5 req.query is a getter that parses the query string with the simple parser by default (Node’s node:querystring), so ?limit=20 arrives as the string '20', and repeated or bracketed keys do not become arrays or nested objects unless the query parser setting is changed (see Application & Settings). Validate and clamp every number, because a client can ask for limit=1000000 or limit=-1:

// src/books-router.mjs
import { Router } from 'express';

const DEFAULT_LIMIT = 20;
const MAX_LIMIT = 100;

function parseLimit(value) {
  const limit = Number.parseInt(value ?? DEFAULT_LIMIT, 10);
  if (!Number.isInteger(limit) || limit < 1) {
    return null;
  }
  return Math.min(limit, MAX_LIMIT);
}

export function createBooksRouter(books) {
  const router = Router();

  router.get('/', (req, res) => {
    const limit = parseLimit(req.query.limit);
    const after = req.query.after === undefined ? undefined : Number(req.query.after);
    if (limit === null || (after !== undefined && !Number.isInteger(after))) {
      return res.status(400).json({ error: 'limit and after must be positive integers' });
    }

    const { items, nextCursor } = books.list({ limit, after });

    if (nextCursor !== null) {
      const next = new URL(req.originalUrl, `${req.protocol}://${req.host}`);
      next.searchParams.set('after', String(nextCursor));
      res.links({ next: next.pathname + next.search });
    }
    res.json({ items, nextCursor });
  });

  router.get('/:id', (req, res) => {
    const book = books.findById(Number(req.params.id));
    if (!book) {
      return res.status(404).json({ error: 'Book not found' });
    }
    res.json(book);
  });

  router.post('/', (req, res) => {
    const { title, author, publishedYear } = req.body ?? {};
    if (typeof title !== 'string' || typeof author !== 'string') {
      return res.status(400).json({ error: 'title and author are required strings' });
    }
    const book = books.create({ title, author, publishedYear });
    res.status(201).location(`${req.baseUrl}/${book.id}`).json(book);
  });

  router.delete('/:id', (req, res) => {
    if (!books.delete(Number(req.params.id))) {
      return res.status(404).json({ error: 'Book not found' });
    }
    res.sendStatus(204);
  });

  return router;
}

Source: Express API: Response (res.links)

res.links() is the Express helper for the Link response header defined by RFC 8288: it accepts an object whose keys are relation types (next, prev, first, last) and whose values are the URLs, and joins them into one header such as <https://api.example.com/api/books?after=20>; rel=next. Clients can follow rel=next without knowing how the cursor is built, which lets the server change the scheme later. Repeated calls to res.links() append to the header, so call it once per relation set. Putting the cursor in the JSON body as well, as above, is common for browser clients that cannot easily read headers across origins; if the API is called cross-origin, list Link in the Access-Control-Expose-Headers response header (the exposedHeaders option of the cors middleware).

The application that wires it together separates createApp, which can be tested without a socket, from the code that connects and listens:

// src/app.mjs
import express from 'express';
import { createBooksRouter } from './books-router.mjs';
import { createBooksRepository } from './books-repository.mjs';

export function createApp(db) {
  const app = express();
  app.use(express.json({ limit: '100kb' }));
  app.use('/api/books', createBooksRouter(createBooksRepository(db)));

  app.use((req, res) => {
    res.status(404).json({ error: `No resource at ${req.method} ${req.path}` });
  });
  app.use((err, req, res, next) => {
    if (res.headersSent) {
      return next(err);
    }
    console.error(err);
    res.status(err.status ?? 500).json({ error: 'Internal Server Error' });
  });
  return app;
}

// src/server.mjs
import { once } from 'node:events';
import { createApp } from './app.mjs';
import { openDatabase } from './db.mjs';

const port = Number(process.env.PORT ?? 3000);

// 1. Connect first: a failure here must stop the process before any request is accepted.
const db = openDatabase();

// 2. Only then listen.
const server = createApp(db).listen(port);
await once(server, 'listening');
console.log(`Bookshelf listening on port ${port}`);

// 3. Close everything in order: stop accepting, drain, then close the database.
async function shutdown(signal) {
  console.log(`${signal} received, shutting down`);
  server.close(() => {
    db.close();
    process.exit(0);
  });
  server.closeIdleConnections();
  setTimeout(() => process.exit(1), 10_000).unref();
}
process.once('SIGTERM', () => shutdown('SIGTERM'));
process.once('SIGINT', () => shutdown('SIGINT'));

Source: Express: Database integration

Run it with node --watch src/server.mjs (add --env-file=.env for local configuration) and request /api/books?limit=2 after creating a few books with POST /api/books. The graceful-shutdown mechanics, including keep-alive connections and Kubernetes probes, are covered in Health Checks & Graceful Shutdown; the point here is the order: the HTTP server stops first, the database closes last.

PostgreSQL with `pg`

For PostgreSQL the official guide lists pg (node-postgres) and postgres (postgres.js). pg 8 is the most widely used, is promise-based, and has a built-in pool. Install it with npm install pg; the connection settings come from the standard PG* environment variables or from a connection string. Never hard-code them.

// src/pg-pool.mjs
import pg from 'pg';

export const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL, // for example postgres://user:password@host:5432/bookshelf
  max: 10,                       // maximum clients in the pool
  idleTimeoutMillis: 30_000,     // close clients idle for this long
  connectionTimeoutMillis: 5_000, // fail fast when the pool is exhausted or the server is down
  statement_timeout: 10_000,     // server-side limit per statement (milliseconds)
});

// An idle client can fail (the server restarts, a firewall drops the connection).
// Without a listener this 'error' event would crash the process.
pool.on('error', (err) => {
  console.error('Unexpected error on an idle PostgreSQL client', err);
});

Source: node-postgres: Pool

pool.query(text, values) borrows a client, runs the statement and returns the client to the pool by itself, which is what a handler should use for a single statement. Values travel separately from the SQL text, with $1, $2, … as placeholders, and the server never parses them as SQL:

// src/books-pg-repository.mjs
import { pool } from './pg-pool.mjs';

export async function findBook(id) {
  const { rows } = await pool.query(
    'SELECT id, title, author, published_year AS "publishedYear" FROM books WHERE id = $1',
    [id],
  );
  return rows[0];
}

export async function listBooks({ limit, after = 0 }) {
  const { rows } = await pool.query(
    'SELECT id, title, author FROM books WHERE id > $1 ORDER BY id LIMIT $2',
    [after, limit + 1],
  );
  return rows;
}

// src/books-pg-router.mjs
import { Router } from 'express';
import { findBook } from './books-pg-repository.mjs';

export const booksRouter = Router();

// Express 5 forwards a rejected promise to the error handlers, so there is no try/catch and no wrapper.
booksRouter.get('/:id', async (req, res) => {
  const book = await findBook(Number(req.params.id));
  if (!book) {
    return res.status(404).json({ error: 'Book not found' });
  }
  res.json(book);
});

On Express 4 the same async handler needs a try/catch that calls next(err), because a rejected promise is not forwarded; see Error Handling.

Transactions per request

A transaction must run on a single connection, so it cannot use pool.query(), which may pick a different client for each call. Check a client out with pool.connect(), run BEGIN, the statements and COMMIT on it, roll back on any error, and release the client in a finally block. A client that is not released stays checked out forever, and after max such leaks every request waits for connectionTimeoutMillis and fails.

// src/transactions.mjs
import { pool } from './pg-pool.mjs';

/** Runs fn(client) inside a transaction and returns its result. */
export async function withTransaction(fn) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const result = await fn(client);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

export function borrowBook({ bookId, memberId }) {
  return withTransaction(async (client) => {
    const { rows } = await client.query(
      'UPDATE books SET available = false WHERE id = $1 AND available RETURNING id',
      [bookId],
    );
    if (rows.length === 0) {
      throw Object.assign(new Error('Book is not available'), { status: 409 });
    }
    await client.query('INSERT INTO loans (book_id, member_id) VALUES ($1, $2)', [bookId, memberId]);
    return { bookId, memberId };
  });
}

Source: Express: Database integration and node-postgres: Transactions

Two refinements are worth knowing. If ROLLBACK itself fails (the connection broke), release the client with client.release(err) so the pool discards it instead of reusing it. And keep transactions short: do not call another HTTP service or wait for user input between BEGIN and COMMIT, because the connection and any locks are held the whole time.

MySQL and MariaDB with `mysql2`

The guide lists mysql2 for MySQL and MariaDB (the mariadb connector is the alternative for MariaDB). mysql2 offers a promise API under mysql2/promise and, importantly, two ways of sending values. query() interpolates the values into the SQL text on the client, with escaping, while execute() uses real server-side prepared statements, where the values never become part of the SQL. Prefer execute() when the statement is repeated. Placeholders are ?.

// src/mysql-pool.mjs
import mysql from 'mysql2/promise';

export const pool = mysql.createPool({
  uri: process.env.DATABASE_URL, // for example mysql://user:password@host:3306/bookshelf
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,        // 0 means unlimited queued requests; set a bound to fail fast
  connectTimeout: 5_000,
});

export async function findBook(id) {
  const [rows] = await pool.execute(
    'SELECT id, title, author FROM books WHERE id = ?',
    [id],
  );
  return rows[0];
}

export async function withTransaction(fn) {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();
    const result = await fn(connection);
    await connection.commit();
    return result;
  } catch (err) {
    await connection.rollback();
    throw err;
  } finally {
    connection.release();
  }
}

Source: mysql2 documentation

Note the differences from pg: results are a pair [rows, fields], a transaction is started with connection.beginTransaction() rather than a manual BEGIN, and a LIMIT ? placeholder bound with execute() must be an integer, so convert query-string values with Number() first. At shutdown call await pool.end().

MongoDB and Redis clients

The official guide also covers document and key-value stores; the lifecycle is identical. The mongodb driver (7.x) keeps its own connection pool inside one MongoClient, so create a single client for the process. The driver connects lazily on the first operation, but calling connect() explicitly at startup makes the failure happen before listen():

// src/mongo.mjs
import { MongoClient } from 'mongodb';

const client = new MongoClient(process.env.MONGODB_URI, { maxPoolSize: 10 }); // mongodb+srv://user:password@cluster.example.net/

export async function connectMongo() {
  await client.connect();
  await client.db('admin').command({ ping: 1 });
  return client.db('bookshelf');
}

export const closeMongo = () => client.close();

For Redis, redis 5 and later (node-redis) uses one client per connection, and it requires an error listener, because an unhandled error event on an event emitter throws and ends the process:

// src/redis.mjs
import { createClient } from 'redis';

export const redis = createClient({ url: process.env.REDIS_URL }); // redis://localhost:6379

redis.on('error', (err) => console.error('Redis client error', err));

export const connectRedis = () => redis.connect();
export const closeRedis = () => redis.close();

Source: MongoDB Node.js driver and redis/node-redis

Mongoose, the object-document mapper that most Express tutorials use for MongoDB, has its own page: MongoDB & Mongoose. For caching and sessions with Redis, see Performance Best Practices and Cookies & Sessions.

ORMs and query builders

The official guide lists Prisma and Sequelize as examples of ORMs, and the ecosystem is wider. The tools differ in how much they hide. A plain driver gives you SQL and rows; a query builder gives you composable SQL in JavaScript; an ORM gives you entities, relations and usually migrations. None of them changes the lifecycle rules of the first section.

Tool Style Migrations Notes for Express

Tool	Style	Migrations	Notes for Express
Prisma 7	Schema-first ORM: a `schema.prisma` file generates a typed client.	Built in (`prisma migrate`).	Create one `PrismaClient` for the process and call `$disconnect()` at shutdown. Version 7 connects through a driver adapter such as `@prisma/adapter-pg`; the generated client lives in an output folder you choose.
Drizzle ORM	TypeScript-first, SQL-like query builder with schema in code.	`drizzle-kit` generates and applies SQL files.	A thin layer over `pg`, `mysql2` or `node:sqlite`-style drivers; you pass your own pool.
Sequelize 6	Promise-based ORM with models, associations and hooks.	`sequelize-cli` or Umzug.	Mature, large user base; see the next section. Version 7 is still an alpha line.
TypeORM	Decorator-based (or `EntitySchema`) data mapper and active record ORM.	Built-in generation and running of migrations.	One `DataSource` initialized before `listen()`; `synchronize: true` is for development only.
Knex	SQL query builder with a schema builder.	Built in (`knex migrate`).	Own pool configuration; `destroy()` at shutdown.
Kysely	Type-safe SQL query builder, no ORM features.	Provides a migration API you drive yourself.	Accepts the `pg` pool or `mysql2` pool you already have.

Prisma 7

Schema-first ORM: a schema.prisma file generates a typed client.

Built in (prisma migrate).

Create one PrismaClient for the process and call $disconnect() at shutdown. Version 7 connects through a driver adapter such as @prisma/adapter-pg; the generated client lives in an output folder you choose.

Drizzle ORM

TypeScript-first, SQL-like query builder with schema in code.

drizzle-kit generates and applies SQL files.

A thin layer over pg, mysql2 or node:sqlite-style drivers; you pass your own pool.

Sequelize 6

Promise-based ORM with models, associations and hooks.

sequelize-cli or Umzug.

Mature, large user base; see the next section. Version 7 is still an alpha line.

TypeORM

Decorator-based (or EntitySchema) data mapper and active record ORM.

Built-in generation and running of migrations.

One DataSource initialized before listen(); synchronize: true is for development only.

Knex

SQL query builder with a schema builder.

Built in (knex migrate).

Own pool configuration; destroy() at shutdown.

Kysely

Type-safe SQL query builder, no ORM features.

Provides a migration API you drive yourself.

Accepts the pg pool or mysql2 pool you already have.

Source: Express: Database integration

The following snippets each create the client once and expose a finder for a book, so they can replace the repository of the earlier sections; the PostgreSQL pool of the previous section is reused where the tool accepts one.

Prisma 7 (schema in prisma/schema.prisma, client generated with npx prisma generate):

// src/prisma.mjs
import { PrismaPg } from '@prisma/adapter-pg';
import { PrismaClient } from '../generated/prisma/client.js';

const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL });
export const prisma = new PrismaClient({ adapter });

export const findBook = (id) => prisma.book.findUnique({ where: { id } });
export const closePrisma = () => prisma.$disconnect();

Drizzle ORM:

// src/drizzle.mjs
import { drizzle } from 'drizzle-orm/node-postgres';
import { eq } from 'drizzle-orm';
import { integer, pgTable, serial, text } from 'drizzle-orm/pg-core';
import { pool } from './pg-pool.mjs';

export const books = pgTable('books', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  author: text('author').notNull(),
  publishedYear: integer('published_year'),
});

export const db = drizzle({ client: pool });

export const findBook = async (id) => {
  const [book] = await db.select().from(books).where(eq(books.id, id)).limit(1);
  return book;
};

Sequelize 6, shown in detail in the next section.

TypeORM (without decorators, through EntitySchema):

// src/typeorm.mjs
import { DataSource, EntitySchema } from 'typeorm';

export const Book = new EntitySchema({
  name: 'Book',
  tableName: 'books',
  columns: {
    id: { type: Number, primary: true, generated: true },
    title: { type: String },
    author: { type: String },
  },
});

export const dataSource = new DataSource({
  type: 'postgres',
  url: process.env.DATABASE_URL,
  entities: [Book],
  synchronize: false, // never true in production: use migrations
});

export const connectTypeorm = () => dataSource.initialize();
export const findBook = (id) => dataSource.getRepository(Book).findOneBy({ id });
export const closeTypeorm = () => dataSource.destroy();

Knex:

// src/knex.mjs
import knex from 'knex';

export const db = knex({
  client: 'pg',
  connection: process.env.DATABASE_URL,
  pool: { min: 0, max: 10 },
});

export const findBook = (id) => db('books').where({ id }).first();
export const closeKnex = () => db.destroy();

Kysely:

// src/kysely.mjs
import { Kysely, PostgresDialect } from 'kysely';
import { pool } from './pg-pool.mjs';

export const db = new Kysely({ dialect: new PostgresDialect({ pool }) });

export const findBook = (id) =>
  db.selectFrom('books').selectAll().where('id', '=', id).executeTakeFirst();

export const closeKysely = () => db.destroy(); // also ends the pool

Source: Prisma, Drizzle ORM, TypeORM, Knex.js and Kysely

Choosing between them is a team question more than a technical one. A plain driver or a query builder keeps the SQL visible and is easy to reason about for performance; an ORM saves repetitive code and standardizes migrations but adds a second model of the schema that can drift. Whatever the choice, keep it behind a repository module, as the earlier sections do, so the Express layer does not depend on it. Whichever tool builds the SQL, verify what it sends: ORMs make it easy to issue one query per row (the N+1 problem), so log queries in development and see Debugging & Logging.

Sequelize and migrations

Sequelize is the relational ORM used in older Express books, and it is still maintained: the stable line is 6.x (6.37.8 on 2026-10-29, the latest dist-tag), while 7.0 is published only as an alpha. It supports PostgreSQL, MySQL, MariaDB, SQLite and SQL Server, and the matching driver (pg, mysql2, sqlite3, tedious, …) must be installed separately. A Sequelize instance owns a connection pool, so it follows the lifecycle rules of the first section.

// src/sequelize.mjs
import { DataTypes, Sequelize } from 'sequelize';

export const sequelize = new Sequelize(process.env.DATABASE_URL, {
  dialect: 'postgres',
  logging: process.env.SQL_LOG === 'true' ? console.log : false,
  pool: { max: 10, min: 0, acquire: 30_000, idle: 10_000 },
});

export const Author = sequelize.define('Author', {
  name: { type: DataTypes.STRING, allowNull: false },
});

export const Book = sequelize.define(
  'Book',
  {
    title: { type: DataTypes.STRING, allowNull: false },
    publishedYear: { type: DataTypes.INTEGER, field: 'published_year' },
  },
  { tableName: 'books' },
);

Author.hasMany(Book, { foreignKey: { name: 'authorId', allowNull: false } });
Book.belongsTo(Author, { foreignKey: 'authorId' });

Source: Sequelize v6: Getting started

sequelize.define() registers a model with columns, an id primary key and, by default, createdAt and updatedAt timestamps. Queries return promises, so route handlers are ordinary async functions:

// src/sequelize-router.mjs
import { Router } from 'express';
import { Op } from 'sequelize';
import { Author, Book, sequelize } from './sequelize.mjs';

export const router = Router();

router.get('/', async (req, res) => {
  const limit = Math.min(Number.parseInt(req.query.limit ?? '20', 10) || 20, 100);
  const after = Number.parseInt(req.query.after ?? '0', 10) || 0;

  const rows = await Book.findAll({
    where: { id: { [Op.gt]: after } }, // operators are Symbols; string aliases such as $gt are not enabled
    include: [{ model: Author, attributes: ['id', 'name'] }],
    order: [['id', 'ASC']],
    limit: limit + 1,
  });
  const items = rows.slice(0, limit);
  res.json({ items, nextCursor: rows.length > limit ? items.at(-1).id : null });
});

router.post('/', async (req, res) => {
  const { title, authorName } = req.body;
  // A managed transaction commits when the callback resolves and rolls back when it throws.
  const book = await sequelize.transaction(async (transaction) => {
    const [author] = await Author.findOrCreate({
      where: { name: authorName },
      transaction,
    });
    return Book.create({ title, authorId: author.id }, { transaction });
  });
  res.status(201).json(book);
});

Source: Sequelize v6: Model querying basics

A managed transaction (the callback form) is the safest way to get the commit-or-rollback behaviour of the PostgreSQL section without repeating the try/catch/finally code. If model classes should not depend on the instance, pass transaction explicitly as above; Sequelize can also bind transactions through continuation-local storage, but that option is global and needs care.

Why `sync()` is not a migration strategy

sequelize.sync() creates missing tables from the models, sync({ alter: true }) tries to change existing ones, and sync({ force: true }) drops and recreates them, deleting all data. These calls are convenient in a test or a first prototype, and dangerous in production: alter can drop columns or fail halfway, and nothing records which changes were applied to which database. A production schema is changed by migrations, versioned and ordered scripts, each with an up and a down, whose applied state is stored in the database itself. The same advice applies to TypeORM (synchronize: true) and to Mongoose’s autoIndex on large collections.

Sequelize has two supported ways to run migrations:

sequelize-cli (6.6.x) reads a .sequelizerc file for the folders and a configuration file for the connections, creates files with npx sequelize-cli migration:generate --name create-books, applies them with db:migrate and reverts with db:migrate:undo. It stores the executed file names in a SequelizeMeta table. It is a command-line tool: the migration files are separate from your application code. By default the generated files use CommonJS, so in an ESM project use the .cjs extension, configured in .sequelizerc.
Umzug (3.x), the library that powers the CLI, runs migrations from your own code. It is useful to run pending migrations at startup, or from an npm script, without a second tool, and it works with any storage. Sequelize’s own documentation recommends it for programmatic use.

// src/migrator.mjs
import { pathToFileURL } from 'node:url';
import { SequelizeStorage, Umzug } from 'umzug';
import { sequelize } from './sequelize.mjs';

export const umzug = new Umzug({
  migrations: {
    glob: 'migrations/*.mjs',
    // Each migration module exports up(queryInterface) and down(queryInterface).
    resolve: ({ name, path }) => ({
      name,
      up: async () => (await import(pathToFileURL(path).href)).up(sequelize.getQueryInterface()),
      down: async () => (await import(pathToFileURL(path).href)).down(sequelize.getQueryInterface()),
    }),
  },
  storage: new SequelizeStorage({ sequelize }),
  logger: console,
});

// migrations/2026.10.29T10.00.00.create-books.mjs
import { DataTypes } from 'sequelize';

export async function up(queryInterface) {
  await queryInterface.createTable('Authors', {
    id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
    name: { type: DataTypes.STRING, allowNull: false },
    createdAt: { type: DataTypes.DATE, allowNull: false },
    updatedAt: { type: DataTypes.DATE, allowNull: false },
  });
  await queryInterface.createTable('books', {
    id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
    title: { type: DataTypes.STRING, allowNull: false },
    published_year: { type: DataTypes.INTEGER },
    authorId: {
      type: DataTypes.INTEGER,
      allowNull: false,
      references: { model: 'Authors', key: 'id' },
      onDelete: 'RESTRICT',
    },
    createdAt: { type: DataTypes.DATE, allowNull: false },
    updatedAt: { type: DataTypes.DATE, allowNull: false },
  });
  await queryInterface.addIndex('books', ['authorId']);
}

export async function down(queryInterface) {
  await queryInterface.dropTable('books');
  await queryInterface.dropTable('Authors');
}

// src/server-sequelize.mjs
import { createApp } from './app-sequelize.mjs';
import { umzug } from './migrator.mjs';
import { sequelize } from './sequelize.mjs';

// Connect first, then migrate, then listen.
await sequelize.authenticate();
await umzug.up(); // applies only the migrations not yet recorded in the storage table

const server = createApp().listen(Number(process.env.PORT ?? 3000));

process.once('SIGTERM', () => {
  server.close(async () => {
    await sequelize.close(); // ends the pool
    process.exit(0);
  });
  server.closeIdleConnections();
});

Source: Sequelize v6: Migrations and sequelize/umzug

Applying migrations automatically at startup is the simplest approach for one instance. With several replicas starting at once it is a race: two processes may try to apply the same migration. Run migrations as a separate step of the release (a job in the pipeline, an init container, or node src/migrate.mjs before the rollout), and design each migration to be compatible with the previous version of the code, so that old and new instances can run side by side during a deployment. A deployment view of this is in Deployment & Containers.

Connecting before listening

Applications written as tutorials often connect and listen independently, and it works until the database is slow. The rule is a sequence, not two parallel starts. In Express 5 the listen() callback receives an error argument (for example EADDRINUSE), and the server emits error and listening events, so the startup code can wait for both resources in a straight line:

// src/start.mjs
import { once } from 'node:events';
import { createApp } from './app.mjs';
import { pool } from './pg-pool.mjs';

try {
  await pool.query('SELECT 1'); // proves that the credentials and the network path work
} catch (err) {
  console.error('Database is not reachable, refusing to start', err);
  process.exit(1);
}

const server = createApp(pool).listen(Number(process.env.PORT ?? 3000));
await once(server, 'listening'); // rejects if the server emits 'error' first, such as EADDRINUSE

Source: Express API: Application (app.listen), Node.js: Events (events.once)

Exiting is usually the right response to a failed connection at startup: the orchestrator restarts the process, and a persistent misconfiguration shows up as a crash loop instead of a service that is "up" but returns errors. After startup, a database outage should not crash the process. Make the readiness probe fail while the database is unreachable so that traffic stops, and let the pool reconnect by itself; the pattern is in Health Checks & Graceful Shutdown.

Parameterized queries and SQL injection

Everything user-controlled that reaches a query must travel as a bound value: route parameters, query strings, body fields and headers. The drivers above all offer placeholders (?, $1, named parameters) and ORMs bind values by default. Building SQL with template strings or plus-sign concatenation defeats this protection even in code that uses an ORM, through its raw-query escape hatches (sequelize.query(), prisma.$queryRawUnsafe(), knex.raw()).

Identifiers (table names, column names, sort directions) cannot be bound as values. When a client may choose the sort column, map the request value to a fixed allow-list in code:

// src/sorting.mjs
const SORTABLE = new Map([
  ['title', 'title'],
  ['author', 'author'],
  ['year', 'published_year'],
]);

export function orderBy(sort = 'title', direction = 'asc') {
  const column = SORTABLE.get(sort);
  if (!column) {
    return null; // the caller answers 400
  }
  return `${column} ${direction === 'desc' ? 'DESC' : 'ASC'}`; // both parts come from constants
}

Source: Express: Database integration

The checks that keep input well-formed before it reaches the database are in Validation & Input Handling.

Cancelling work when the client disconnects

When a client closes the connection before the response is sent, Express does not stop the handler: the query keeps running and its result is discarded, which wastes a pooled connection under load. Node.js emits close on the response object when the underlying connection ends; if res.writableFinished is still false at that moment the client left early. Turning that event into an AbortSignal lets the handler stop between steps, and lets the drivers and libraries that accept a signal option, fetch among them, cancel their work:

// src/abort-on-disconnect.mjs
export function abortOnDisconnect(req, res, next) {
  const controller = new AbortController();
  res.on('close', () => {
    if (!res.writableFinished) {
      controller.abort(new Error('Client disconnected'));
    }
  });
  req.signal = controller.signal;
  next();
}

// In a handler that performs several steps:
//   req.signal.throwIfAborted();           // stop before the next expensive step
//   const response = await fetch(url, { signal: req.signal });

Not every driver supports a signal. For those that do not, bound the damage on the server side instead: statement_timeout (PostgreSQL), max_execution_time (MySQL, for SELECT) or a driver maxTimeMS option (MongoDB) cap how long a statement may run regardless of the client. The official guide mentions the same idea for shutdown: an application that already uses an AbortSignal to coordinate cancellation can pass it to app.listen() and abort it instead of calling server.close() directly.

Source: Express: Database integration

Handling database errors

Database drivers raise errors with codes that describe what happened, and an Express API should translate the ones a client can fix and hide the rest. A unique-constraint violation is a 409, not a 500, and the SQL text or connection details must never reach the response.

Driver Error signal Typical response

Driver	Error signal	Typical response
`pg`	`23505` (unique violation), `23503` (foreign key), `23502` (not null) and `22P02` (invalid text representation) in `err.code`	`409` or `400`
`mysql2`	`ER_DUP_ENTRY` or `ER_NO_REFERENCED_ROW_2` in `err.code`	`409` or `400`
`node:sqlite`	`ERR_SQLITE_ERROR` in `err.code`, with the SQLite extended code in `err.errcode` (for example `2067` for a unique violation)	`409` or `400`
Sequelize	`SequelizeUniqueConstraintError`, `SequelizeValidationError`, `SequelizeForeignKeyConstraintError`	`409` or `400`
Pool exhausted or connection lost	`pg`: "timeout exceeded when trying to connect"; `mysql2`: `PROTOCOL_CONNECTION_LOST`, `ECONNREFUSED`	`503`

pg

23505 (unique violation), 23503 (foreign key), 23502 (not null) and 22P02 (invalid text representation) in err.code

409 or 400

mysql2

ER_DUP_ENTRY or ER_NO_REFERENCED_ROW_2 in err.code

409 or 400

node:sqlite

ERR_SQLITE_ERROR in err.code, with the SQLite extended code in err.errcode (for example 2067 for a unique violation)

409 or 400

Sequelize

SequelizeUniqueConstraintError, SequelizeValidationError, SequelizeForeignKeyConstraintError

409 or 400

Pool exhausted or connection lost

pg: "timeout exceeded when trying to connect"; mysql2: PROTOCOL_CONNECTION_LOST, ECONNREFUSED

503

Source: PostgreSQL: Error codes

Map these in a dedicated error-handling middleware placed before the generic handler, in the style of Error Handling:

// src/database-errors.mjs
export function databaseErrors(err, req, res, next) {
  if (res.headersSent) {
    return next(err);
  }
  if (err.code === '23505' || err.code === 'ER_DUP_ENTRY' || err.name === 'SequelizeUniqueConstraintError') {
    return res.status(409).json({ error: 'Resource already exists' });
  }
  if (err.code === '23503' || err.code === '22P02') {
    return res.status(400).json({ error: 'Invalid reference or value' });
  }
  if (err.code === 'ECONNREFUSED' || err.code === 'PROTOCOL_CONNECTION_LOST') {
    return res.status(503).json({ error: 'Database temporarily unavailable' });
  }
  next(err); // anything else is a genuine 500
}

What changed recently

Express 5 removes the need for wrappers around async handlers, which makes every database call in this page a plain await without try/catch. On Express 4 add the catch, or upgrade.
node:sqlite became available without a flag in Node.js 22.13.0 and 23.4.0, gained prepared-statement helpers such as tagged-template stores in later releases, and is progressing through the Node.js stability levels (release candidate from Node.js 25.7). It makes a database available to an Express example without any dependency.
req.query is a getter in Express 5, and the default query parser is simple, so bracketed parameters used by older pagination libraries no longer produce nested objects unless the query parser setting is changed.
Prisma 7 connects through driver adapters and generates the client into a project folder, replacing the older default of generating into node_modules. Prisma 8 is in release-candidate stage as of the check date.
Sequelize 6 is the stable line and Sequelize 7 is published as an alpha. String operator aliases such as $gt are disabled and operators are imported from Op.
TypeORM is now at 1.x, and its older createConnection() API was replaced by DataSource long ago.
Redis: redis 4 and later is promise-based and needs an explicit connect() call; the callback style of earlier versions is gone.
Hosted databases changed: MongoDB sandboxes such as mLab and MongoHQ no longer exist (MongoDB Atlas replaced them), and free-tier Heroku Postgres add-ons were discontinued in November 2022.

Where the books are outdated

The consulted book that uses a relational database (Node.js 6.x Blueprints, 2016, chapter 2) targets Express 4.13, Node.js 6.3 and Sequelize 3 era APIs. Most of its ideas hold, but several details need updating:

Older practice Where it appears Current behaviour

Older practice	Where it appears	Current behaviour
Creating the schema with `sequelize.sync()` (sometimes with `force: true`) when the server starts.	Monteiro 2016, chapter 2	Use migrations with `sequelize-cli` or Umzug; `sync()` is for tests and prototypes. See Sequelize and migrations.
Operator aliases as object keys, such as `$gt` or `$like`, and the callbacks or `.success()` style of Sequelize 1 to 3.	Monteiro 2016, chapter 2	Sequelize 6 uses the `Op` symbols and returns standard promises, usable with `async`/`await`.
Loading models with `sequelize.import()` or by scanning a `models` folder with `fs`.	Monteiro 2016, chapter 2	`import` models as ordinary ES modules (or CommonJS `require`); `sequelize.import()` was removed in Sequelize 6.
Database credentials written in a configuration file that is committed with the project.	Monteiro 2016, chapter 2	Read the connection string from the environment (`DATABASE_URL`, `--env-file`), and keep secrets out of the repository.
Connecting and calling `app.listen()` independently, or listening inside the connection callback with no error path.	Mongoose and Sequelize examples in several books	`await` the connection check, exit on failure, then listen. See Connecting before listening.
A hosted sandbox database from the era (mLab or MongoHQ) in connection strings.	Mongoose chapters	Use MongoDB Atlas, a self-hosted instance or a container; see MongoDB & Mongoose.
Pagination that returns every row, or builds `OFFSET` from a client-supplied page with no upper bound.	Several REST examples	Clamp `limit`, prefer keyset pagination for large tables, and announce the next page with a `Link` header. See Pagination and `Link` headers.

Creating the schema with sequelize.sync() (sometimes with force: true) when the server starts.

Monteiro 2016, chapter 2

Use migrations with sequelize-cli or Umzug; sync() is for tests and prototypes. See Sequelize and migrations.

Operator aliases as object keys, such as $gt or $like, and the callbacks or .success() style of Sequelize 1 to 3.

Monteiro 2016, chapter 2

Sequelize 6 uses the Op symbols and returns standard promises, usable with async/await.

Loading models with sequelize.import() or by scanning a models folder with fs.

Monteiro 2016, chapter 2

import models as ordinary ES modules (or CommonJS require); sequelize.import() was removed in Sequelize 6.

Database credentials written in a configuration file that is committed with the project.

Monteiro 2016, chapter 2

Read the connection string from the environment (DATABASE_URL, --env-file), and keep secrets out of the repository.

Connecting and calling app.listen() independently, or listening inside the connection callback with no error path.

Mongoose and Sequelize examples in several books

await the connection check, exit on failure, then listen. See Connecting before listening.

A hosted sandbox database from the era (mLab or MongoHQ) in connection strings.

Mongoose chapters

Use MongoDB Atlas, a self-hosted instance or a container; see MongoDB & Mongoose.

Pagination that returns every row, or builds OFFSET from a client-supplied page with no upper bound.

Several REST examples

Clamp limit, prefer keyset pagination for large tables, and announce the next page with a Link header. See Pagination and Link headers.

Common pitfalls

Symptom Cause Fix

Symptom	Cause	Fix
The process crashes with an unhandled `error` event after the database restarts.	No `error` listener on the `pg` pool or the Redis client.	Add an `error` listener with `pool.on` and `redis.on`.
Requests hang, then fail after the pool timeout.	A client checked out with `pool.connect()` is never released, often on an early `return` or a thrown error.	Always release in `finally`, or use `pool.query()` and managed transactions.
A transaction partly commits.	Statements ran through `pool.query()` and so on different connections.	Run every statement of a transaction on the same client or `transaction` object.
A connection per request, and the database refuses connections under load.	The client or pool is created inside the handler.	Create it once in a module and import it.
`listen()` succeeds but every request fails with a connection error.	The server started before the database was reachable.	Check the connection first and exit on failure.
Shutdown is cut short and in-flight queries fail.	The pool is closed before the HTTP server has drained.	`server.close()` first, then close the pool in its callback.
Sorting by a client-supplied column opens SQL injection.	The column name is interpolated into the SQL.	Allow-list identifiers in code.
A long synchronous query makes the whole API unresponsive.	`node:sqlite` and `better-sqlite3` block the event loop.	Move heavy queries to a worker thread or use an asynchronous driver.
Tests interfere with each other.	They share a persistent database file.	Use `:memory:` for SQLite, or a disposable container for other engines.

The process crashes with an unhandled error event after the database restarts.

No error listener on the pg pool or the Redis client.

Add an error listener with pool.on and redis.on.

Requests hang, then fail after the pool timeout.

A client checked out with pool.connect() is never released, often on an early return or a thrown error.

Always release in finally, or use pool.query() and managed transactions.

A transaction partly commits.

Statements ran through pool.query() and so on different connections.

Run every statement of a transaction on the same client or transaction object.

A connection per request, and the database refuses connections under load.

The client or pool is created inside the handler.

Create it once in a module and import it.

listen() succeeds but every request fails with a connection error.

The server started before the database was reachable.

Check the connection first and exit on failure.

Shutdown is cut short and in-flight queries fail.

The pool is closed before the HTTP server has drained.

server.close() first, then close the pool in its callback.

Sorting by a client-supplied column opens SQL injection.

The column name is interpolated into the SQL.

Allow-list identifiers in code.

A long synchronous query makes the whole API unresponsive.

node:sqlite and better-sqlite3 block the event loop.

Move heavy queries to a worker thread or use an asynchronous driver.

Tests interfere with each other.

They share a persistent database file.

Use :memory: for SQLite, or a disposable container for other engines.

References

Express: Database integration
Express API: Response
Express: Moving to Express 5
Node.js: SQLite
node-postgres
mysql2 documentation
MongoDB Node.js driver
redis/node-redis
Prisma documentation
Drizzle ORM documentation
Sequelize v6 documentation
sequelize/umzug
TypeORM documentation
Knex.js documentation
Kysely documentation
RFC 8288: Web Linking
Publisher pages of the consulted books are listed in the section bibliography.
Related pages: Error Handling, MongoDB & Mongoose, Validation & Input Handling, Health Checks & Graceful Shutdown, Testing

Database Integration

The lifecycle that every driver shares

SQLite with node:sqlite

A repository layer for Bookshelf

Pagination and Link headers

PostgreSQL with pg

Transactions per request

MySQL and MariaDB with mysql2

MongoDB and Redis clients

ORMs and query builders

Sequelize and migrations

Why sync() is not a migration strategy

Connecting before listening

Parameterized queries and SQL injection

Cancelling work when the client disconnects

Handling database errors

What changed recently

Where the books are outdated

Common pitfalls

References

SQLite with `node:sqlite`

Pagination and `Link` headers

PostgreSQL with `pg`

MySQL and MariaDB with `mysql2`

Why `sync()` is not a migration strategy