Database Access Patterns In Node.js

Every Node.js service of any size eventually has the database conversation. Should we use Prisma? Drizzle? Just pg and write SQL? Knex? Kysely? Repositories? A unit of work? The team will burn a week debating it, ship something, and live with the consequences for years.

The honest answer is that all of these tools work. The decision that matters more is whether your team understands what is happening under the abstraction — connection pooling, transactions, locking, and the N+1 problem do not go away when you adopt an ORM. They just get harder to see.

This article is the layered map I wish I had the first time I had to make this call. Start at the bottom; the higher abstractions only make sense if the lower ones do.

Connection Pooling Is Not Optional

Postgres handles each connection as a process. Open one per HTTP request and you will exhaust the database long before you exhaust your Node process. The driver almost always ships with a pool — you just have to use it.

// db.ts
import { Pool } from 'pg';

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10, // per Node process
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 2_000,
});

Two numbers worth thinking about hard:

• max per process × number of processes ≤ Postgres max_connections. A four-replica deployment with max: 20 is 80 connections before you account for migrations, admin tools, or read replicas.
• connectionTimeoutMillis. When the pool is exhausted, new requests queue. A small timeout fails fast and shows up as a clear error; a missing one hides as a tail-latency mystery.

If you are on a serverless platform, you also need a transaction-mode pooler in front of Postgres (PgBouncer, Supabase Pooler, AWS RDS Proxy). One Lambda invocation per request without a pooler will melt the database.

Raw SQL Is The Floor

The most under-rated option is the one nobody recommends on Twitter: parameterized SQL through the driver. It is fast, transparent, and impossible to misunderstand.

const { rows } = await pool.query(
  'SELECT id, email FROM users WHERE tenant_id = $1 AND deleted_at IS NULL LIMIT 50',
  [tenantId],
);

The downsides are real: you write your own type mapping, your own migrations, your own composition for filters that may or may not be present. For small services or for the 5% of queries where performance matters more than ergonomics, this is still the right tool.

The non-negotiable rule: parameterize. Never interpolate user input into a query string. The drivers (pg, mysql2, better-sqlite3) all support $1 / ? placeholders for a reason.

Query Builders: Knex And Kysely

A query builder gives you composability — where(), andWhere(), join() — without inventing an object model. Knex is the classic; Kysely is the modern, fully type-safe alternative for TypeScript codebases.

// Kysely
const users = await db
  .selectFrom('users')
  .select(['id', 'email'])
  .where('tenant_id', '=', tenantId)
  .where('deleted_at', 'is', null)
  .limit(50)
  .execute();

Kysely's appeal: the column names, return types, and joins are checked against your schema at compile time. There is no generated client to keep in sync — you describe the schema in TypeScript types once.

Knex has been around forever and ships with a migration runner. It is JavaScript-first; the types are bolted on. For a JS codebase or a team that wants the migration story without picking an ORM, it is a reasonable default.

ORMs: Prisma And Drizzle

The two ORMs the Node.js ecosystem has converged on:

Prisma is declarative. You write a schema.prisma file, run prisma migrate dev, and get a generated client with rich types. It is opinionated, pleasant to start with, and excellent for greenfield apps. Its trade-off is a Rust-based query engine that runs alongside your Node process and an abstraction that gets in your way the moment your query is unusual.

Drizzle is closer to a query builder than a classical ORM. Schema is plain TypeScript, queries look like SQL (db.select().from(users).where(eq(users.id, 1))), and the runtime overhead is minimal. It is a better fit for teams that already think in SQL and want types without losing visibility.

Both are real options. The decision is usually about how your team thinks: declarative schema and ergonomics first (Prisma) or SQL-shaped, low-magic, edge-friendly (Drizzle).

The Repository Pattern, Lightly

The "repository" word scares people because of how it gets taught — often with three layers of abstract base classes. In a Node.js codebase you do not need that. You need a module per aggregate that exposes intent-shaped functions:

// repositories/users.ts
import { db } from '../db';

export async function findActiveByTenant(tenantId: string) {
  return db
    .selectFrom('users')
    .select(['id', 'email', 'name'])
    .where('tenant_id', '=', tenantId)
    .where('deleted_at', 'is', null)
    .execute();
}

export async function softDelete(id: string) {
  return db.updateTable('users').set({ deleted_at: new Date() }).where('id', '=', id).execute();
}

That is it. No interface, no DI container. The value is twofold: your route handlers stop containing query syntax, and you have one place to add a tenant filter or a soft-delete check the day someone forgets it.

Transactions And Locking

Two patterns you will reach for constantly:

Transactions wrap multiple writes that have to commit together. Every Node.js DB library exposes them as a callback that receives a transactional client. Use the transactional client inside; do not accidentally call the global pool.

await db.transaction().execute(async (trx) => {
  const order = await trx.insertInto('orders').values({ ... }).returning('id').executeTakeFirstOrThrow();
  await trx.insertInto('order_items').values(items.map(i => ({ ...i, order_id: order.id }))).execute();
});

Optimistic vs pessimistic locking is the question of how to handle two requests racing to update the same row.

• Optimistic: keep a version column, UPDATE ... WHERE id = $1 AND version = $2, and retry if zero rows were affected. Cheap, correct, no contention. Right for most app code.
• Pessimistic: SELECT ... FOR UPDATE inside a transaction to lock the row until commit. Right when the work between read and write must not be re-done (charging a card, decrementing inventory).

Pick deliberately. Pessimistic locking under high concurrency turns into a queue.

The N+1 Problem Travels With You

The classic trap: load 50 orders, then for each order call order.customer and fire 50 more queries. Every ORM has a way to express the join up front — Prisma's include, Drizzle's with (via relations()), Kysely's explicit .innerJoin(). The reason the bug keeps happening is that ORMs make the N+1 version look identical to the joined version in your code; only the query count changes.

The defense is observability, not discipline. Log query counts per request in development, and assert in tests that critical endpoints stay under a threshold. Once you can see "this request fired 47 queries" in your terminal, the N+1 finds itself.

A One-Sentence Mental Model

The data layer is a pool, a driver, and a small set of intent-shaped functions on top — every other abstraction is a productivity choice that does not change those three responsibilities.