Talking to a database

Choosing one

The default answer for an application database is PostgreSQL. Pick something else when you know why.

	Good at	Watch out for
PostgreSQL	Almost everything. Relations, JSON, full text, constraints	Nothing much — start here
SQLite	A single file, zero setup, astonishingly fast reads	One writer at a time
MySQL	Long-established, everywhere	Older versions have sharp edges
MongoDB	Documents that vary in shape	You still need a schema; you just enforce it yourself
Redis	Cache, sessions, queues, rate limits	Not your source of truth

A client and a pool

Opening a connection costs a round trip and a handshake. A pool keeps a few open and hands them out — and the pool size is a real limit: ten connections serving a hundred concurrent requests means ninety are queued, which is usually better than overwhelming the database.

one pool for the process, not one per request

import { Pool } from "pg";

export const db = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,                       // connections, not requests
  idleTimeoutMillis: 30_000,
});

const { rows } = await db.query("SELECT * FROM doors WHERE id = $1", [id]);

Parameters, always

pg uses $1, $2; mysql2 uses ?; better-sqlite3 uses ? or :named.
Table and column names cannot be parameters. If they come from user input, check them against a fixed list you wrote.

the difference is not style

// SQL injection. The value becomes part of the statement.
db.query(`SELECT * FROM doors WHERE name = '${name}'`);
// name = "'; DROP TABLE doors; --"

// The value is sent separately and is never parsed as SQL.
db.query("SELECT * FROM doors WHERE name = $1", [name]);

Escaping is not a defence

Every attempt to make a value safe by quoting or stripping characters has been defeated. The only correct approach is to keep values out of the statement entirely — which is also faster, because the database can reuse the query plan.

Transactions

Every statement in a transaction must run on the same connection — which is why you take a client out of the pool instead of calling pool.query four times.

all of it, or none of it

const client = await pool.connect();
try {
  await client.query("BEGIN");
  await client.query("UPDATE stock SET count = count - 1 WHERE id = $1", [id]);
  await client.query("INSERT INTO orders (door_id) VALUES ($1)", [id]);
  await client.query("COMMIT");
} catch (err) {
  await client.query("ROLLBACK");
  throw err;
} finally {
  client.release();              // give the connection back, whatever happened
}

Migrations

Numbered, committed, applied in order, never edited after they have run anywhere.
Every change goes through one — including the ones you made by hand at three in the morning.
Deploy in two steps when a change is destructive: add the new column, ship the code that writes both, backfill, then remove the old one.

the schema is code, and lives in the repository

-- 003_add_finish.sql
ALTER TABLE doors ADD COLUMN finish text NOT NULL DEFAULT 'painted';
CREATE INDEX doors_finish_idx ON doors (finish);

The two performance problems you will meet

Index the columns you filter, join and sort on. An index makes reads fast and writes slightly slower, so index deliberately rather than everywhere.

N+1, and the missing index

// 1 + N queries: one for the list, one per row
const doors = await db.query("SELECT * FROM doors");
for (const d of doors.rows) d.orders = await db.query("SELECT * FROM orders WHERE door_id = $1", [d.id]);

// one query
const rows = await db.query(
  "SELECT d.*, o.id AS order_id FROM doors d LEFT JOIN orders o ON o.door_id = d.id"
);

// and when a query is slow, ask the database why
EXPLAIN ANALYZE SELECT * FROM doors WHERE finish = 'veneer';

Raw SQL, a query builder, or an ORM

Whatever you use, know what SQL it sends. Every one of them has a way to log it — turn that on early.

Raw SQL — total control, no magic. You write the mapping by hand.
Query builder (Kysely, Drizzle) — typed SQL that still reads like SQL. A very good middle.
ORM (Prisma, TypeORM) — models and migrations generated for you; wonderful until you need a query it will not express, and then you drop to raw SQL anyway.