Handbooks / Node.js / Chapter 7

Databases from Node

7 pages · ~5 min✓ Reviewed

Builds on Error Handling and Async Patterns. Next up: Authentication and Security Basics.

Node.jsPage 1

Connection Pools: The Mental Model

1. What a Pool Is

A pool keeps a fixed set of open DB connections and lends them out
New TCP + auth connection per query is slow
The server caps connections, so unbounded opens fail

2. Lifecycle and Options

Acquireclient from pool
Queryrun statements
Releaseback to pool

All clients busy: requests queue until one frees
Or the acquire timeout fires and the request errors

Node.jsPage 2

Parameterised Queries with pg

1. Placeholders

pool.query(text, values) with $1, $2 placeholders
Values travel separately from the SQL text
Input is never parsed as SQL: no injection
Rows in result.rows, count in result.rowCount

javascript

const result = await pool.query(
  'SELECT * FROM users WHERE email = $1 AND active = $2',
  [email, true]
);
console.log(result.rows, result.rowCount);

Node.jsPage 3

Transactions

1. What a transaction is

A transaction groups statements: all commit or all roll back (ACID)
Every statement must run on the same connection
pool.query may pick a different connection each call, so it cannot hold a transaction

2. pg pattern

javascript

const client = await pool.connect();
try {
  await client.query('BEGIN');
  // all statements use client
  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK'); throw e;
} finally { client.release(); }

release() in finally, even after ROLLBACK

Node.jsPage 4

ORMs vs Query Builders: Prisma and Drizzle

1. Builder vs ORM

Query builder (Knex, Kysely): chained methods build SQL, stays close to SQL
ORM: maps tables to models, with relations and a schema layer

2. Prisma vs Drizzle

	Prisma

Node.jsPage 5

MongoDB Driver Basics and Clean Shutdown

1. Client and Collection

One MongoClient per process; the pool is built in (maxPoolSize 100)
await client.connect() once, then reuse; never one per request
No schema; BSON docs get an auto _id (ObjectId)

const col = client.db('app').collection('users');
await col.insertOne({ name: 'Ann', active: true });

2. CRUD Calls

Node.jsPage 6

Database Access Cheat Sheet

1. Pool and pg

One Pool per process, created at startup
max × instances < server max_connections
Options: max, idleTimeoutMillis, connectionTimeoutMillis
pool.query(sql, [vals]) with $1, $2
Never interpolate values into the SQL string

2. Transactions

Node.jsPage 7

Check yourself

You run pool.query('BEGIN'), then two pool.query updates, then pool.query('COMMIT'). The second update fails. What gets rolled back?

Possibly nothing: each pool.query can use a different connection.
BEGIN may sit on one connection and the updates on others, so there is no real transaction.
Fix: pool.connect(), run every statement on that client, client.release() in finally.

Inside prisma.$transaction(async (tx) => {...}) you write await prisma.order.create(...), then throw. Is the order rolled back?

pg option	Default	Meaning
`max`	10	Most clients in the pool
`idleTimeoutMillis`	10000	Idle client closed after this
`connectionTimeoutMillis`	0	Acquire wait; 0 = wait forever

Position	Can be $1?
Value in WHERE / INSERT	yes
Table or column name	no
ORDER BY direction	no

	Prisma	Drizzle
Batch	`$transaction([op1, op2])`	n/a
Interactive	`$transaction(async (tx) => {...})`	`db.transaction(async (tx) => {...})`
Timeout	default 5s	n/a
Rollback	throw inside	throw, or `tx.rollback()`

Schema	schema.prisma is the source of truth	Defined in TypeScript
Client	`prisma generate` makes a typed client	No codegen step
Migrations	`prisma migrate dev`	`drizzle-kit` generates them
Style	Models, `include`	SQL-like API

Op	Call
Create	`insertOne` / `insertMany`
Read	`find(f).toArray()`, `findOne(f)`
Update	`updateOne(f, { $set: {...} })`
Delete	`deleteOne(f)`

Databases from Node

Connection Pools: The Mental Model

1. What a Pool Is

2. Lifecycle and Options

Parameterised Queries with pg

1. Placeholders

Transactions

1. What a transaction is

2. pg pattern

ORMs vs Query Builders: Prisma and Drizzle

1. Builder vs ORM

2. Prisma vs Drizzle

MongoDB Driver Basics and Clean Shutdown

1. Client and Collection

2. CRUD Calls

Database Access Cheat Sheet

1. Pool and pg

2. Transactions

Check yourself

3. Sizing and Mistakes

2. Values, Not Identifiers

3. Mistakes

3. Prisma and Drizzle

4. Mistakes

3. Trade-offs and N+1

3. Mistakes and Shutdown

3. Pick a Tool

4. Shutdown Order

Tool	Choose for
Prisma	Most DX, generated types
Drizzle / Kysely	SQL-like, lightweight
Raw `pg`	Full control