Databases Basics

8 examples to get you started with databases for Node.js backends - 6 basic and 2 intermediate.

Prerequisites

npm install pg
npm install -D typescript@5.6 tsx @types/pg

For pool tuning and ORM trade-offs, see node-postgres (pg) and Prisma.

Basic Examples

1. SQL vs Document - When to Pick Each

• Most B2B APIs default to Postgres with pg or Drizzle/Prisma
• Document stores fit catalog blobs, event logs, and rapid schema churn
• Hybrid is common: Postgres for money, Redis for cache, S3 for files

Related: MongoDB Driver - document patterns

2. Environment-Driven Connection String

// src/config.ts
import { z } from "zod";
 
const env = z.object({
  DATABASE_URL: z.string().url(),
}).parse(process.env);
 
export const databaseUrl = env.DATABASE_URL;

• Never hardcode credentials in source
• DATABASE_URL format: postgres://user:pass@host:5432/dbname
• Validate at boot so the process fails before accepting traffic

3. Minimal pg Pool

import pg from "pg";
 
const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  idleTimeoutMillis: 30_000,
});
 
export async function getUser(id:

• Always use $1, $2 placeholders - never string concat
• max should match expected concurrent queries, not CPU count alone
• Export one pool per process; do not create pools per request

Related: Connection Pool Tuning - sizing math

4. Graceful Pool Shutdown

import pg from "pg";
 
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
 
process.on("SIGTERM", async () => {
  await pool.end();
  process.exit(

• pool.end() waits for checked-out clients to return
• Pair with HTTP server close() in graceful shutdown handlers
• Missing shutdown leaks connections until Postgres kills idle sessions

5. Simple Transaction Wrapper

import type pg from "pg";
 
export async function withTransaction<T>(
  pool: pg.Pool,
  fn: (client: pg.PoolClient) => Promise<

• Use one client for the whole transaction
• Keep transactions short - no outbound HTTP inside BEGIN/COMMIT
• Idempotency keys help when retries replay after rollback

6. Health Check Query

import type { Pool } from "pg";
 
export async function checkDatabase(pool: Pool): Promise<boolean> {
  try {
    await pool.query("SELECT 1");
    return true

• Readiness probes should verify DB connectivity, not only process up
• Use a lightweight query; avoid scanning large tables
• Log pool totalCount and idleCount on repeated failures

Intermediate Examples

7. Repository Boundary (Port)

// src/domain/user.ts
export type User = { id: string; email: string };
 
export interface UserRepository {
  findById(id: string): Promise<User | null>;

• Routes call repositories or use cases, not raw SQL inline
• Swap Postgres for in-memory repo in unit tests
• ORMs still sit behind this boundary

Related: Use Cases & Services - orchestration layer

8. Choosing Driver vs ORM

• Greenfield typed APIs: Drizzle or Prisma accelerate delivery
• Heavy reporting SQL: pg or Drizzle with raw queries
• One ORM per service - avoid mixing Prisma and Drizzle in the same module

FAQs

Related

• node-postgres (pg) - pool and transactions
• Prisma - schema-first ORM
• Drizzle ORM - SQL-first typing
• Connection Pool Tuning - max and PgBouncer
• Databases Best Practices - team checklist

Stack versions: This page was written for Node.js 24.18.0 (Active LTS), npm 10+, TypeScript 5.6+, Express 5, Fastify 5, and NestJS 11.