Skip to content
Tutorials Center
My learning

Topics

Lesson 69 of 133 ยท Data and Storage

Databases from Node.js

How Node.js talks to PostgreSQL, MySQL, MongoDB and Redis - drivers, connection pools, query builders and ORMs, transactions and migrations, with runnable SQLite equivalents.

Almost every real application keeps its data in a database that is separate from the Node.js process: PostgreSQL, MySQL, MongoDB, Redis or something else. Node.js itself does not ship drivers for those servers. You install a driver from npm, give it connection details, and send queries over the network. The ideas are the same whichever database you choose, and understanding them matters more than memorizing one library's API.

This lesson maps the landscape. You will see what the popular drivers look like (as read-only code, because our editor has no npm packages and no database servers), and then you will build the important pieces yourself on top of the built-in SQLite module from SQLite with node:sqlite: a connection pool, a repository, a tiny query builder, transactions with a helper, and a migration runner. Those pieces are what the big libraries do too, just with more features.

Code blocks marked "read-only" show what you would write on your own machine after npm install. They cannot run here, because the packages and servers are missing. Every runnable example uses node:sqlite and is checked in the editor.

The shape of every database client

Whichever database you use, the flow is the same:

  1. Connect to the server (host, port, user, password, database name), usually through a pool of connections.
  2. Send a command: a SQL string with parameters, a document filter or a key.
  3. Receive a result: rows, a count, a document or a value.
  4. Release the connection and, at shutdown, close the pool.

Every one of these steps takes time over the network, so all drivers are asynchronous and return promises. This is the key difference from node:sqlite, which is synchronous because the database lives in the same process. Anything you learn about async/await in async/await Patterns applies to every real driver.

Relational databases: PostgreSQL and MySQL

For PostgreSQL the standard driver is pg. For MySQL and MariaDB it is mysql2. Both use parameter placeholders so that values never become part of the SQL text ($1, $2 in PostgreSQL and ? in MySQL). This is the same protection against SQL injection you saw with SQLite.

Read-only example with pg (needs npm install pg and a PostgreSQL server):

JavaScript
// read-only: requires the "pg" package and a running PostgreSQL server
import pg from 'pg';

const pool = new pg.Pool({
  host: 'localhost',
  port: 5432,
  user: 'app',
  password: process.env.DB_PASSWORD,
  database: 'shop',
  max: 10,                       // at most 10 connections in the pool
});

const { rows } = await pool.query(
  'SELECT id, name FROM customers WHERE country = $1 ORDER BY name',
  ['Norway'],
);
console.log(rows);

await pool.end();                // close all connections on shutdown

And with mysql2, using its promise API:

JavaScript
// read-only: requires the "mysql2" package and a running MySQL server
import mysql from 'mysql2/promise';

const pool = mysql.createPool({
  host: 'localhost',
  user: 'app',
  password: process.env.DB_PASSWORD,
  database: 'shop',
  connectionLimit: 10,
});

const [rows] = await pool.execute(
  'SELECT id, name FROM customers WHERE country = ?',
  ['Norway'],
);
console.log(rows);

await pool.end();

Notice the parts that repeat: a pool object, a parameterized query, a result with rows, and an explicit end() at the end of the program. Passwords come from the environment, never from source code (see Configuration, Secrets and Environment).

Document and key-value stores: MongoDB and Redis

MongoDB stores JSON-like documents in collections, and you query with objects instead of SQL. The official driver is mongodb:

JavaScript
// read-only: requires the "mongodb" package and a running MongoDB server
import { MongoClient } from 'mongodb';

const client = new MongoClient(process.env.MONGO_URL);
await client.connect();

const customers = client.db('shop').collection('customers');
await customers.insertOne({ name: 'Ada', country: 'Norway', tags: ['vip'] });

const found = await customers.find({ country: 'Norway' }).sort({ name: 1 }).toArray();
console.log(found);

await client.close();

Redis is an in-memory key-value store, great for caches, sessions, rate limits and queues. Use redis (the official client) or ioredis:

JavaScript
// read-only: requires the "redis" package and a running Redis server
import { createClient } from 'redis';

const redis = createClient({ url: process.env.REDIS_URL });
await redis.connect();

await redis.set('greeting', 'hello', { EX: 60 });   // expires after 60 seconds
console.log(await redis.get('greeting'));

await redis.quit();

Redis is the usual next step when the in-process caches from Caching in Memory are no longer enough, because several Node.js processes can share one Redis server.

How to choose

Need Good first choice
Structured data, relations, reports, transactions PostgreSQL (or MySQL)
Small app, tool, tests, single machine SQLite
Flexible documents that change shape often MongoDB
Cache, sessions, counters, queues, leaderboards Redis
Full-text search at scale A search engine (Meilisearch, OpenSearch)

Start with a relational database unless you have a clear reason not to. Most data has relationships, and SQL is a skill that lasts decades. You can add Redis or a search engine later.

Connection pools

Opening a connection to a database server is slow: a TCP handshake, TLS, authentication, session setup. Doing that for every request would waste most of your time. A connection pool opens a limited number of connections once and lends them out. When a request needs the database it acquires a connection, uses it, and releases it back. When all connections are busy, new requests wait in a queue.

Why a limit? A database server can only run so many queries efficiently. PostgreSQL, for example, uses a whole process per connection, so thousands of connections hurt it. A pool of 10 to 20 per Node.js process is a common starting point. Remember: connections per process x number of processes must stay below what the server allows.

Our SQLite connections are synchronous, so we cannot show the waiting for real I/O, but we can simulate it. This small pool shows the real mechanics: a fixed set of connections, a queue of waiters, and a release that hands a connection to the next waiter.

Node.js A connection pool by hand

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Four jobs shared two connections, so C and D had to wait until A and B finished. The finally block is the most important line: a connection that is not released is leaked, and after max leaks your whole application hangs, because every new request waits forever for a connection that nobody returns. A leaked connection is among the most common production incidents in Node.js backends.

Keep SQL out of your handlers: repositories

If every route handler contains SQL, a change to a table means hunting through the whole project. A repository is a small module that owns the queries for one kind of data and offers methods with domain names: findById, create, listByCountry. The rest of the code never sees SQL, and in tests you can replace the repository with a fake. The next example builds one on SQLite, wrapped in async methods so that switching to a real asynchronous driver later would not change callers.

Node.js A repository over SQLite

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Observe two details. The rows are converted into ordinary objects ({ ...row }), so callers do not depend on the driver's row type. And lastInsertRowid is turned into a number. Every driver has such small differences, and a repository is the place to hide them.

Query builders and ORMs

Writing SQL strings by hand is clear and fast, but dynamic queries get messy: optional filters, sorting, pagination. Two kinds of libraries help.

A query builder (Knex, Kysely) lets you compose SQL with method calls, still thinking in tables and columns. It always produces parameterized SQL. An ORM (object-relational mapper: Prisma, Sequelize, TypeORM, Drizzle) goes further: it maps tables to classes or typed models, handles relations (user.posts) and often generates migrations.

Read-only example of both styles (needs the packages and a database):

JavaScript
// read-only: Knex query builder
const adults = await knex('users')
  .where('country', 'UK')
  .andWhere('age', '>=', 18)
  .orderBy('name')
  .limit(10);

// read-only: Prisma ORM (models come from a schema file)
const user = await prisma.user.findUnique({
  where: { id: 42 },
  include: { posts: true },     // loads the related rows too
});

The trade-offs are real. ORMs save a lot of boilerplate and give type safety, but they can hide slow queries. The famous example is the N+1 problem: you load 100 users, then loop and load each user's posts with a separate query, producing 101 queries instead of one join. Whichever tool you use, learn to read the SQL it generates, and use the database's EXPLAIN command on slow queries.

To make the idea concrete, here is a minimal query builder. It collects conditions and values separately, and only ever puts ? placeholders into the SQL text. Column names are checked against an allow-list, because identifiers cannot be parameters.

Node.js A tiny query builder

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Real builders add joins, grouping, inserts, updates and per-database dialects (quoting, RETURNING, placeholders). But the core idea is exactly this: keep the SQL text and the values apart.

Transactions

A transaction makes a group of changes atomic: all of them happen or none do. With a pool the rule is strict: all statements of one transaction must run on the same connection. If you called pool.query for each statement, they could run on different connections, and BEGIN on one connection means nothing to another. With real drivers the pattern is:

JavaScript
// read-only: transaction with pg
const client = await pool.connect();       // one dedicated connection
try {
  await client.query('BEGIN');
  await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [50, 1]);
  await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [50, 2]);
  await client.query('COMMIT');
} catch (err) {
  await client.query('ROLLBACK');
  throw err;
} finally {
  client.release();                        // back to the pool, in every case
}

A helper that takes a function removes the repeated boilerplate. This runnable version has the same structure on SQLite, and it checks a business rule inside the transaction, so a failure leaves no half-done work.

Node.js A transaction helper and a business rule

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

This helper contains an await inside the transaction, which is fine for it only because work here does no real asynchronous waiting. With a synchronous SQLite connection a truly asynchronous body can let other code run in the middle of the transaction. With a real client from a pool, each transaction owns a separate connection, so that is not a problem. Keep transactions short in any case: they hold locks, and long ones block other users.

Choose the isolation level deliberately when correctness depends on it. The default (READ COMMITTED in PostgreSQL) is fine for most work, but "check balance, then update" races need either a single atomic UPDATE ... WHERE balance >= ?, a row lock (SELECT ... FOR UPDATE), or a stricter isolation level with retries on serialization failures.

Migrations

Your schema evolves while the application is running in production, so you cannot just recreate the database. A migration is a versioned, ordered change (create a table, add a column, backfill data) that is applied once per database and recorded in a table. Tools such as Knex, Prisma Migrate, Flyway and node-pg-migrate do this, and they all share the rules below.

  • Migrations only move forward and are never edited after release. A mistake is fixed by a new migration.
  • Each runs in a transaction when the database supports it (PostgreSQL does for most DDL; MySQL commits DDL implicitly).
  • The database stores which ones ran, so deploying twice is safe.
  • Big changes use several steps ("expand and contract"): add the new column, deploy code that writes both, backfill, switch reads, then remove the old column. That keeps old and new code working while a rollout is in progress.

A migration runner with a schema_migrations table is only a few lines:

Node.js A migration runner with a history table

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

The broken migration rolled back cleanly and stopped the chain, so the database is left in a known state: migrations 1 and 2 are recorded, the others are not. On the second run migration 3 fails again until a developer fixes it with a new, correct migration.

Practical advice for production

  • Always use parameters. Never build SQL from request data. See Web Application Security.
  • Set timeouts. Give every query and connection attempt a deadline, so a stuck database cannot freeze your server. See Concurrency Control and Queues.
  • Size your pool to the database, not to your traffic, and release connections in finally.
  • Index what you filter and sort by. A missing index is the most common reason for a slow query. Check plans with EXPLAIN.
  • Paginate. Never SELECT * a table with millions of rows into memory. Page with LIMIT and keyset conditions, or stream rows (see Processing Large Data Efficiently).
  • Shut down cleanly. On SIGTERM stop accepting work, wait for running queries, then pool.end() (see Graceful Shutdown and Health Checks).
  • Handle transient errors. Networks fail. Retry idempotent reads with backoff; be careful with retrying writes.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why do real database drivers return promises while node:sqlite is synchronous?

    A network round trip takes time during which Node.js should serve other work. SQLite runs inside the process, so a blocking call is short.
  2. 2. What is the main purpose of a connection pool?

    Connecting is slow and servers can only handle so many connections. A pool opens a few and lends them out.
  3. 3. What happens if code acquires a pooled connection and forgets to release it after an error?

    The pool cannot know the connection is free. After max leaks the queue never moves. Release in a finally block.
  4. 4. Why must all statements of one transaction run on the same connection?

    A transaction is state of one session. Statements sent through other pooled connections are outside it.
  5. 5. What is the N+1 query problem?

    100 users plus a separate query for each user's posts make 101 queries. A join or an include loads the same data in one or two.

Summary

  • Node.js uses npm drivers to reach servers: pg (PostgreSQL), mysql2, mongodb, redis. They are asynchronous and take parameters, not string-built SQL.
  • Pools reuse a few connections, queue extra work, and must always be released in finally and closed at shutdown.
  • Hide queries behind repositories so the rest of the code does not know about SQL or the driver.
  • Query builders and ORMs reduce boilerplate. Watch the SQL they produce, especially N+1 patterns.
  • A transaction needs one dedicated connection and should be short. Use a helper with BEGIN, COMMIT, ROLLBACK.
  • Migrations are ordered, forward-only and recorded in the database. Failures must roll back and stop.
  • Start with a relational database; add Redis, a document store or search when a concrete need appears.

Next: Hashing and HMAC