Databases Basics

Q: Do I need an ORM?

No. pg plus SQL files is valid. ORMs help migrations and TypeScript inference at the cost of abstraction leaks.

Q: Can NestJS use pg directly?

Yes via @nestjs/typeorm, Prisma module, or custom provider wrapping Pool.

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

Need	SQL (Postgres)	Document (MongoDB)
Joins and constraints	Strong fit	Weaker; embed or $lookup
Schema evolution	Migrations	Flexible fields
Reporting / BI	Mature tooling	Aggregation pipelines
ACID transactions	Native multi-row	Multi-doc since 4.0

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: string) {
  const { rows } = await pool.query(
    "SELECT id, email FROM users WHERE id = $1",
    [id]
  );
  return rows[0] ?? null;
}

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(0);
});

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<T>
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    const result = await fn(client);
    await client.query("COMMIT");
    return result;
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

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;
  } catch {
    return false;
  }
}

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>;
  create(email: string): Promise<User>;
}
 
// src/infrastructure/pg-user-repo.ts
import type { Pool } from "pg";
import type { User, UserRepository } from "../domain/user";
 
export function createPgUserRepo(pool: Pool): UserRepository {
  return {
    async findById(id) {
      const { rows } = await pool.query(
        "SELECT id, email FROM users WHERE id = $1",
        [id]
      );
      return rows[0] ?? null;
    },
    async create(email) {
      const { rows } = await pool.query(
        "INSERT INTO users (email) VALUES ($1) RETURNING id, email",
        [email]
      );
      return rows[0];
    },
  };
}

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

Layer	`pg` raw	Drizzle	Prisma
Control	Full SQL	SQL-first typed	Schema-first
Migrations	flyway, dbmate, drizzle-kit	drizzle-kit	prisma migrate
N+1 risk	You control joins	You control joins	Watch `include`
Learning curve	Low	Medium	Medium

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

Postgres or MySQL for Node APIs?

Both work. Postgres leads for JSONB, extensions, and managed cloud options. Pick what your team operates well.

Do I need an ORM?

No. pg plus SQL files is valid. ORMs help migrations and TypeScript inference at the cost of abstraction leaks.

How many pools per Node process?

One per database URL. Multiple pools to the same DB waste connections.

Serverless and Postgres?

Use a pooler (PgBouncer, Neon, Supavisor). See Connection Pool Tuning.

Where do migrations run?

In CI/CD before deploy, never manual prod DDL. See Databases Best Practices.

Can NestJS use `pg` directly?

Yes via @nestjs/typeorm, Prisma module, or custom provider wrapping Pool.

How do I test without a real database?

In-memory repository for unit tests; Testcontainers Postgres for integration tests.

What is the biggest beginner mistake?

Creating a new connection per request instead of pooling.

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

Node.js Best Practices (GitHub) - Curated production best practices.
npm Official Website & Registry - Package search, publishing, and documentation.
DigitalOcean Node.js Tutorials - Production-oriented how-tos.
Node.js Design Patterns, 4th Edition - Production-grade application patterns.
Distributed Systems with Node.js - Enterprise-ready distributed systems.

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.

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

Need	SQL (Postgres)	Document (MongoDB)
Joins and constraints	Strong fit	Weaker; embed or $lookup
Schema evolution	Migrations	Flexible fields
Reporting / BI	Mature tooling	Aggregation pipelines
ACID transactions	Native multi-row	Multi-doc since 4.0

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: string) {
  const { rows } = await pool.query(
    "SELECT id, email FROM users WHERE id = $1",
    [id]
  );
  return rows[0] ?? null;
}

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(0);
});

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<T>
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    const result = await fn(client);
    await client.query("COMMIT");
    return result;
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

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;
  } catch {
    return false;
  }
}

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>;
  create(email: string): Promise<User>;
}
 
// src/infrastructure/pg-user-repo.ts
import type { Pool } from "pg";
import type { User, UserRepository } from "../domain/user";
 
export function createPgUserRepo(pool: Pool): UserRepository {
  return {
    async findById(id) {
      const { rows } = await pool.query(
        "SELECT id, email FROM users WHERE id = $1",
        [id]
      );
      return rows[0] ?? null;
    },
    async create(email) {
      const { rows } = await pool.query(
        "INSERT INTO users (email) VALUES ($1) RETURNING id, email",
        [email]
      );
      return rows[0];
    },
  };
}

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

Layer	`pg` raw	Drizzle	Prisma
Control	Full SQL	SQL-first typed	Schema-first
Migrations	flyway, dbmate, drizzle-kit	drizzle-kit	prisma migrate
N+1 risk	You control joins	You control joins	Watch `include`
Learning curve	Low	Medium	Medium

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

Postgres or MySQL for Node APIs?

Both work. Postgres leads for JSONB, extensions, and managed cloud options. Pick what your team operates well.

Do I need an ORM?

No. pg plus SQL files is valid. ORMs help migrations and TypeScript inference at the cost of abstraction leaks.

How many pools per Node process?

One per database URL. Multiple pools to the same DB waste connections.

Serverless and Postgres?

Use a pooler (PgBouncer, Neon, Supavisor). See Connection Pool Tuning.

Where do migrations run?

In CI/CD before deploy, never manual prod DDL. See Databases Best Practices.

Can NestJS use `pg` directly?

Yes via @nestjs/typeorm, Prisma module, or custom provider wrapping Pool.

How do I test without a real database?

In-memory repository for unit tests; Testcontainers Postgres for integration tests.

What is the biggest beginner mistake?

Creating a new connection per request instead of pooling.

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

Node.js Best Practices (GitHub) - Curated production best practices.
npm Official Website & Registry - Package search, publishing, and documentation.
DigitalOcean Node.js Tutorials - Production-oriented how-tos.
Node.js Design Patterns, 4th Edition - Production-grade application patterns.
Distributed Systems with Node.js - Enterprise-ready distributed systems.

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.

Databases Basics

Search Documentation

Databases Basics