PostgreSQL #

PostgreSQL is the most feature-rich open-source relational database and the closest to full SQL standards. From JSON/JSONB data type support, arrays, enums, built-in full-text search, to window functions — PostgreSQL provides almost everything you need without switching databases. The standard library for TypeScript is pg (node-postgres), which is mature, battle-tested, and natively supports all PostgreSQL features. There are several PostgreSQL characteristics that set it apart from MySQL and SQL Server: parameters use $1, $2, $3 (positional), column and table names are case-insensitive by default and stored in lowercase, and PostgreSQL has a very powerful RETURNING clause for getting row data after INSERT/UPDATE/DELETE. Understanding these idioms from the start will make your TypeScript + PostgreSQL code feel natural and efficient.

Installation and Setup #

The main library is pg, along with its type definitions available as a separate package.

# Install the package
npm install pg
npm install --save-dev @types/pg

// tsconfig.json
{
  "compilerOptions": {
    "target": "ES2020",
    "module": "commonjs",
    "lib": ["ES2020"],
    "strict": true,
    "esModuleInterop": true,
    "resolveJsonModule": true,
    "outDir": "./dist",
    "rootDir": "./src"
  }
}

The recommended project structure:

src/
  ├── db/
  │   ├── connection.ts      -- pool singleton
  │   ├── migrations/        -- SQL migration files
  │   └── seeds/             -- development seed data
  ├── models/
  │   ├── user.model.ts
  │   └── produk.model.ts
  ├── repositories/
  │   ├── base.repository.ts
  │   └── user.repository.ts
  └── index.ts

Connection Pool Configuration #

pg provides the Pool class for connection management. PostgreSQL pools are simpler than Oracle’s — no explicit connect() needed; connections are created lazily when first required.

import { Pool, PoolClient, QueryResult } from 'pg';

// Pool configuration
const pool = new Pool({
  host: process.env.DB_HOST ?? 'localhost',
  port: Number(process.env.DB_PORT ?? 5432),
  database: process.env.DB_NAME ?? 'toko_online',
  user: process.env.DB_USER ?? 'postgres',
  password: process.env.DB_PASSWORD ?? '',
  max: 10,                  // maximum simultaneous connections
  idleTimeoutMillis: 30000, // close idle connections after 30 seconds
  connectionTimeoutMillis: 5000,  // timeout while waiting for a pool connection
  // Alternative: use a connection string
  // connectionString: process.env.DATABASE_URL,
  // ssl: { rejectUnauthorized: false }  // for Heroku / Supabase
});

// Handle pool errors — important to prevent crashes
pool.on('error', (err) => {
  console.error('Unexpected error on idle pg client:', err);
});

export default pool;

export async function closePool(): Promise<void> {
  await pool.end();
}

process.on('SIGTERM', closePool);
process.on('SIGINT', closePool);

For environment variable configuration, pg supports DATABASE_URL automatically:

// The most concise way — pg automatically reads the DATABASE_URL env
// DATABASE_URL=postgresql://user:***@host:5432/dbname
const pool = new Pool();  // without explicit config

flowchart TD
    A[new Pool config] --> B[Pool ready\nlazy initialization]
    B --> C[pool.query or\npool.connect is called]
    C --> D{Idle connection\navailable?}
    D -- Yes --> E[Use the\nexisting connection]
    D -- No --> F{Count < max?}
    F -- Yes --> G[Create a new connection\nto PostgreSQL]
    F -- No --> H[Wait until\na connection is idle]
    E --> I[Execute the query]
    G --> I
    H --> E
    I --> J{Using pool.query?}
    J -- Yes --> K[Connection automatically\nreturned to the pool]
    J -- No --> L[Client.release\nmust be called manually]
    K --> B
    L --> B

pg has two ways to query: pool.query() which manages connections automatically, and pool.connect() which gives you a PoolClient for manual control. If you use pool.connect(), you must call client.release() in the finally block — no exceptions. Forgetting to call release() will exhaust all pool connections and hang the entire application.

Defining Types for Database Rows #

pg returns query results as plain JavaScript objects with column names as keys. Because PostgreSQL stores column names in lowercase, your TypeScript types can directly use snake_case column names as-is.

import { QueryResult } from 'pg';

// Type for the users table
interface User {
  id: number;
  nama: string;
  email: string;
  password_hash: string;
  role: 'admin' | 'user' | 'moderator';
  aktif: boolean;          // PostgreSQL has native BOOLEAN — automatically a boolean
  dibuat_pada: Date;
  diperbarui_pada: Date | null;
}

interface Produk {
  id: number;
  nama: string;
  deskripsi: string | null;
  harga: string;           // NUMERIC/DECIMAL is returned as a string by pg!
  stok: number;
  kategori_id: number;
  gambar_url: string | null;
  metadata: Record<string, unknown> | null;  // JSONB column
  dibuat_pada: Date;
}

// Helper to parse the price from string to number
function parseProduk(row: Produk): Produk & { harga: number } {
  return {
    ...row,
    harga: parseFloat(row.harga),
  };
}

PostgreSQL data type to TypeScript mapping:

PostgreSQL Type	TypeScript Type	Notes
`INTEGER`, `SMALLINT`	`number`
`BIGINT`	`string`	Too large for JS numbers
`NUMERIC`, `DECIMAL`	`string`	`pg` returns a string for precision
`REAL`, `DOUBLE PRECISION`	`number`
`BOOLEAN`	`boolean`	Auto-converted
`VARCHAR`, `TEXT`	`string`
`TIMESTAMP`, `TIMESTAMPTZ`	`Date`
`UUID`	`string`	UUID string format
`JSONB`, `JSON`	`object`	Auto-parsed
`ARRAY`	`T[]`	Auto-converted to an array
`ENUM`	`string`	Needs a manual cast to a union type

pg returns NUMERIC and DECIMAL columns as strings, not numbers — to preserve precision when converting to JavaScript floating point. Always parse explicitly with parseFloat() or use a BigDecimal when precision is critical, e.g. for financial calculations.

Basic Queries — SELECT #

PostgreSQL uses $1, $2, $3 as positional placeholders for parameterized queries. Their order matches the value positions in the parameter array.

import pool from './db/connection';

// ── Simple SELECT without parameters
async function semuaUser(): Promise<User[]> {
  const result = await pool.query<User>(
    'SELECT id, nama, email, role, aktif, dibuat_pada FROM users WHERE aktif = true ORDER BY dibuat_pada DESC'
  );
  return result.rows;
}

// ── SELECT with parameters — $1, $2, etc.
async function cariUserById(id: number): Promise<User | null> {
  const result = await pool.query<User>(
    'SELECT * FROM users WHERE id = $1 AND aktif = true',
    [id]
  );
  return result.rows[0] ?? null;
}

// ── SELECT with multiple parameters
async function produkByKategoriDanHarga(
  kategoriId: number,
  hargaMin: number,
  hargaMax: number
): Promise<Produk[]> {
  const result = await pool.query<Produk>(
    `SELECT *
     FROM produk
     WHERE kategori_id = $1
       AND harga BETWEEN $2 AND $3
       AND stok > 0
     ORDER BY harga ASC`,
    [kategoriId, hargaMin, hargaMax]
  );
  return result.rows;
}

// ── SELECT with ILIKE — case-insensitive LIKE, a PostgreSQL feature
async function cariProdukByNama(keyword: string): Promise<Produk[]> {
  // Escape PostgreSQL's special LIKE characters: %, _, \
  const safeKeyword = keyword.replace(/[%_\\]/g, '\\$&');
  const result = await pool.query<Produk>(
    `SELECT * FROM produk
     WHERE nama ILIKE $1
     LIMIT 20`,
    [`%${safeKeyword}%`]
  );
  return result.rows;
}

// ── SELECT with IN — PostgreSQL supports array binds with ANY
async function produkByIds(ids: number[]): Promise<Produk[]> {
  if (ids.length === 0) return [];
  const result = await pool.query<Produk>(
    'SELECT * FROM produk WHERE id = ANY($1)',
    [ids]  // PostgreSQL can bind arrays directly!
  );
  return result.rows;
}

// ── SELECT with JSONB — query a JSON column
async function produkByMetadata(key: string, value: string): Promise<Produk[]> {
  const result = await pool.query<Produk>(
    `SELECT * FROM produk
     WHERE metadata @> $1::jsonb`,
    [JSON.stringify({ [key]: value })]
  );
  return result.rows;
}

// ANTI-PATTERN: string interpolation — vulnerable to SQL injection
async function cariUserTidakAman(email: string): Promise<User[]> {
  const result = await pool.query<User>(
    `SELECT * FROM users WHERE email = '${email}'`  // DON'T!
  );
  return result.rows;
}

// CORRECT: always use parameterized queries with $n
async function cariUserAman(email: string): Promise<User | null> {
  const result = await pool.query<User>(
    'SELECT * FROM users WHERE email = $1',
    [email]
  );
  return result.rows[0] ?? null;
}

INSERT Operations #

PostgreSQL has a very powerful RETURNING clause — it can return any columns from the newly inserted, updated, or deleted rows.

import pool from './db/connection';

interface InputUser {
  nama: string;
  email: string;
  passwordHash: string;
  role?: 'admin' | 'user' | 'moderator';
}

// ── INSERT with RETURNING — get the entire created row
async function buatUser(input: InputUser): Promise<User> {
  const result = await pool.query<User>(
    `INSERT INTO users (nama, email, password_hash, role, aktif, dibuat_pada)
     VALUES ($1, $2, $3, $4, true, NOW())
     RETURNING *`,
    [input.nama, input.email, input.passwordHash, input.role ?? 'user']
  );
  return result.rows[0];
}

// ── INSERT — only get the id
async function buatProduk(
  input: Omit<Produk, 'id' | 'dibuat_pada'>
): Promise<number> {
  const result = await pool.query<{ id: number }>(
    `INSERT INTO produk (nama, deskripsi, harga, stok, kategori_id, dibuat_pada)
     VALUES ($1, $2, $3, $4, $5, NOW())
     RETURNING id`,
    [input.nama, input.deskripsi, input.harga, input.stok, input.kategori_id]
  );
  return result.rows[0].id;
}

// ── INSERT with ON CONFLICT — idiomatic PostgreSQL upsert
async function upsertUser(input: InputUser): Promise<User> {
  const result = await pool.query<User>(
    `INSERT INTO users (nama, email, password_hash, role, aktif, dibuat_pada)
     VALUES ($1, $2, $3, $4, true, NOW())
     ON CONFLICT (email)
     DO UPDATE SET
       nama            = EXCLUDED.nama,
       password_hash   = EXCLUDED.password_hash,
       diperbarui_pada = NOW()
     RETURNING *`,
    [input.nama, input.email, input.passwordHash, input.role ?? 'user']
  );
  return result.rows[0];
}

// ── Batch INSERT — unnest for efficient bulk inserts
async function buatBanyakProduk(
  produkList: Array<{ nama: string; harga: number; stok: number; kategoriId: number }>
): Promise<number> {
  if (produkList.length === 0) return 0;

  // unnest is the idiomatic way to bulk insert in PostgreSQL
  const namas      = produkList.map(p => p.nama);
  const hargas     = produkList.map(p => p.harga);
  const stoks      = produkList.map(p => p.stok);
  const kategoriIds = produkList.map(p => p.kategoriId);

  const result = await pool.query(
    `INSERT INTO produk (nama, harga, stok, kategori_id, dibuat_pada)
     SELECT * FROM UNNEST($1::text[], $2::numeric[], $3::int[], $4::int[],
                          ARRAY_FILL(NOW()::timestamp, ARRAY[$5]))`,
    [namas, hargas, stoks, kategoriIds, produkList.length]
  );
  return result.rowCount ?? 0;
}

sequenceDiagram
    participant App
    participant PostgreSQL

    App->>PostgreSQL: INSERT INTO users (...)\nVALUES ($1, $2, ...)\nRETURNING *
    PostgreSQL->>PostgreSQL: Insert a new row
    PostgreSQL-->>App: rows[0] = the whole new row
    Note over App: No separate SELECT needed\nto get the newly created data

UPDATE and DELETE Operations #

RETURNING in PostgreSQL also applies to UPDATE and DELETE — a feature that doesn’t exist in MySQL and is very useful for audit logs or data confirmation.

import pool from './db/connection';

// ── UPDATE with RETURNING
async function updateUser(
  id: number,
  data: Partial<Pick<User, 'nama' | 'email' | 'role'>>
): Promise<User | null> {
  const entries = Object.entries(data).filter(([, v]) => v !== undefined);
  if (entries.length === 0) return null;

  // Whitelist of updatable columns
  const allowedColumns = new Set(['nama', 'email', 'role']);
  const safeEntries = entries.filter(([k]) => allowedColumns.has(k));
  if (safeEntries.length === 0) return null;

  const setClauses = safeEntries.map(([col], i) => `${col} = $${i + 2}`);
  const values = safeEntries.map(([, v]) => v);

  const result = await pool.query<User>(
    `UPDATE users
     SET ${setClauses.join(', ')}, diperbarui_pada = NOW()
     WHERE id = $1 AND aktif = true
     RETURNING *`,
    [id, ...values]
  );
  return result.rows[0] ?? null;
}

// ── Update stock — atomic increment/decrement
async function updateStokProduk(
  id: number,
  delta: number
): Promise<{ stok_lama: number; stok_baru: number } | null> {
  const result = await pool.query<{ stok_lama: number; stok_baru: number }>(
    `UPDATE produk
     SET stok = stok + $2
     WHERE id = $1 AND (stok + $2) >= 0
     RETURNING
       stok - $2 AS stok_lama,
       stok      AS stok_baru`,
    [id, delta]
  );
  return result.rows[0] ?? null;
}

// ── DELETE with RETURNING — get the data before it's deleted
async function hapusUser(id: number): Promise<User | null> {
  const result = await pool.query<User>(
    'DELETE FROM users WHERE id = $1 RETURNING *',
    [id]
  );
  return result.rows[0] ?? null;  // null if not found
}

// ── Soft delete
async function softDeleteUser(id: number): Promise<boolean> {
  const result = await pool.query(
    `UPDATE users
     SET aktif = false, dihapus_pada = NOW()
     WHERE id = $1 AND aktif = true`,
    [id]
  );
  return (result.rowCount ?? 0) > 0;
}

Transactions #

PostgreSQL uses BEGIN, COMMIT, and ROLLBACK executed through a PoolClient. You must take a connection from the pool first so all queries in one transaction use the same connection.

import { PoolClient } from 'pg';
import pool from './db/connection';

interface ItemPesanan {
  produkId: number;
  jumlah: number;
  hargaSatuan: number;
}

async function buatPesanan(
  userId: number,
  items: ItemPesanan[]
): Promise<number> {
  const client: PoolClient = await pool.connect();

  try {
    await client.query('BEGIN');

    const totalHarga = items.reduce(
      (sum, item) => sum + item.hargaSatuan * item.jumlah, 0
    );

    // 1. Create the order record
    const r1 = await client.query<{ id: number }>(
      `INSERT INTO pesanan (user_id, total_harga, status, dibuat_pada)
       VALUES ($1, $2, 'pending', NOW())
       RETURNING id`,
      [userId, totalHarga]
    );
    const pesananId = r1.rows[0].id;

    // 2. Process each item
    for (const item of items) {
      await client.query(
        `INSERT INTO pesanan_item (pesanan_id, produk_id, jumlah, harga_satuan)
         VALUES ($1, $2, $3, $4)`,
        [pesananId, item.produkId, item.jumlah, item.hargaSatuan]
      );

      // Decrease stock — atomic, fails if stock is insufficient
      const rStok = await client.query(
        `UPDATE produk
         SET stok = stok - $1
         WHERE id = $2 AND stok >= $1`,
        [item.jumlah, item.produkId]
      );

      if ((rStok.rowCount ?? 0) === 0) {
        await client.query('ROLLBACK');
        throw new Error(`Stok tidak cukup untuk produk ID ${item.produkId}`);
      }
    }

    await client.query('COMMIT');
    return pesananId;

  } catch (error) {
    try { await client.query('ROLLBACK'); } catch { /* ignore */ }
    throw error;
  } finally {
    client.release();  // REQUIRED: return the connection to the pool
  }
}

Generic transaction helper:

async function withTransaction<T>(
  callback: (client: PoolClient) => Promise<T>
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const result = await callback(client);
    await client.query('COMMIT');
    return result;
  } catch (error) {
    try { await client.query('ROLLBACK'); } catch { /* ignore */ }
    throw error;
  } finally {
    client.release();
  }
}

// Usage — far cleaner
const pesananId = await withTransaction(async (client) => {
  const r1 = await client.query<{ id: number }>(
    `INSERT INTO pesanan (user_id, total_harga)
     VALUES ($1, $2) RETURNING id`,
    [userId, total]
  );
  await client.query(
    'INSERT INTO pesanan_item (pesanan_id, produk_id) VALUES ($1, $2)',
    [r1.rows[0].id, produkId]
  );
  return r1.rows[0].id;
});

flowchart TD
    A[client = pool.connect] --> B[client.query BEGIN]
    B --> C[First query]
    C --> D{Error?}
    D -- No --> E{More\noperations?}
    E -- Yes --> C
    E -- No --> F[client.query COMMIT]
    D -- Yes --> G[client.query ROLLBACK]
    F --> H[client.release]
    G --> H
    H --> I[Connection back\nto the pool]

Error Handling #

pg uses DatabaseError (a class from pg-protocol) which has a code property in the standard 5-character SQLSTATE format.

import { DatabaseError } from 'pg';

// The most commonly encountered PostgreSQL SQLSTATE codes
const PG_ERRORS = {
  UNIQUE_VIOLATION:     '23505',  // duplicate key value violates unique constraint
  FOREIGN_KEY_VIOLATION: '23503', // insert or update violates foreign key constraint
  NOT_NULL_VIOLATION:   '23502',  // null value in column violates not-null constraint
  CHECK_VIOLATION:      '23514',  // new row violates check constraint
  DEADLOCK_DETECTED:    '40P01',  // deadlock detected
  SERIALIZATION_FAILURE: '40001', // could not serialize access due to concurrent update
  UNDEFINED_TABLE:      '42P01',  // relation does not exist
  UNDEFINED_COLUMN:     '42703',  // column does not exist
  LOCK_NOT_AVAILABLE:   '55P03',  // lock not available (nowait)
} as const;

class AppDatabaseError extends Error {
  constructor(message: string, public readonly code?: string) {
    super(message);
    this.name = 'AppDatabaseError';
  }
}

class UniqueViolationError extends AppDatabaseError {
  constructor(public readonly constraint?: string) {
    super(`Duplikat data${constraint ? ` pada constraint '${constraint}'` : ''}`);
    this.name = 'UniqueViolationError';
  }
}

class ForeignKeyError extends AppDatabaseError {
  constructor(public readonly constraint?: string) {
    super(`Referensi data tidak valid${constraint ? `: ${constraint}` : ''}`);
    this.name = 'ForeignKeyError';
  }
}

class DeadlockError extends AppDatabaseError {
  constructor() {
    super('Deadlock terdeteksi — coba ulangi operasi');
    this.name = 'DeadlockError';
  }
}

function tanganiPgError(error: unknown): never {
  if (error instanceof DatabaseError) {
    switch (error.code) {
      case PG_ERRORS.UNIQUE_VIOLATION:
        throw new UniqueViolationError(error.constraint);
      case PG_ERRORS.FOREIGN_KEY_VIOLATION:
        throw new ForeignKeyError(error.constraint);
      case PG_ERRORS.NOT_NULL_VIOLATION:
        throw new AppDatabaseError(
          `Kolom '${error.column}' tidak boleh kosong`, error.code
        );
      case PG_ERRORS.CHECK_VIOLATION:
        throw new AppDatabaseError(
          `Nilai melanggar constraint '${error.constraint}'`, error.code
        );
      case PG_ERRORS.DEADLOCK_DETECTED:
      case PG_ERRORS.SERIALIZATION_FAILURE:
        throw new DeadlockError();
    }
  }
  throw new AppDatabaseError(`Error database: ${String(error)}`);
}

// Retry for deadlocks and serialization failures
async function withRetry<T>(
  fn: () => Promise<T>,
  maxRetry = 3,
  delayMs = 100
): Promise<T> {
  for (let attempt = 1; attempt <= maxRetry; attempt++) {
    try {
      return await fn();
    } catch (error) {
      if (error instanceof DeadlockError && attempt < maxRetry) {
        await new Promise(res => setTimeout(res, delayMs * attempt));
        continue;
      }
      throw error;
    }
  }
  throw new Error('Unreachable');
}

JSONB — PostgreSQL’s Flagship Feature #

PostgreSQL is the only relational database that natively supports high-performance JSON queries through the JSONB type. This enables flexible schemas without leaving SQL.

import pool from './db/connection';

interface ProdukDenganMetadata extends Produk {
  metadata: {
    warna?: string[];
    ukuran?: string[];
    berat_gram?: number;
    tags?: string[];
    [key: string]: unknown;
  } | null;
}

// ── INSERT with JSONB
async function buatProdukDenganMetadata(
  produk: Omit<ProdukDenganMetadata, 'id' | 'dibuat_pada'>
): Promise<ProdukDenganMetadata> {
  const result = await pool.query<ProdukDenganMetadata>(
    `INSERT INTO produk (nama, harga, stok, metadata, dibuat_pada)
     VALUES ($1, $2, $3, $4::jsonb, NOW())
     RETURNING *`,
    [produk.nama, produk.harga, produk.stok, JSON.stringify(produk.metadata)]
  );
  return result.rows[0];
}

// ── Query with JSONB operators
async function produkByWarna(warna: string): Promise<ProdukDenganMetadata[]> {
  const result = await pool.query<ProdukDenganMetadata>(
    // @> : does the JSON on the left contain the JSON on the right?
    `SELECT * FROM produk
     WHERE metadata->'warna' @> $1::jsonb`,
    [JSON.stringify([warna])]
  );
  return result.rows;
}

async function produkByTag(tag: string): Promise<ProdukDenganMetadata[]> {
  const result = await pool.query<ProdukDenganMetadata>(
    // ? : does the key/element exist in the JSON?
    `SELECT * FROM produk
     WHERE metadata->'tags' ? $1`,
    [tag]
  );
  return result.rows;
}

// ── Update a specific JSONB field — without replacing the whole object
async function updateMetadataProduk(
  id: number,
  updates: Record<string, unknown>
): Promise<boolean> {
  const result = await pool.query(
    // jsonb_set or || for merging
    `UPDATE produk
     SET metadata = COALESCE(metadata, '{}'::jsonb) || $2::jsonb
     WHERE id = $1`,
    [id, JSON.stringify(updates)]
  );
  return (result.rowCount ?? 0) > 0;
}

The most useful PostgreSQL JSONB operators:

Operator	Description	Example
`->`	Get a field as JSON	`metadata->'warna'`
`->>`	Get a field as text	`metadata->>'nama_brand'`
`@>`	Left JSON contains right	`metadata @> '{"aktif":true}'`
`<@`	Left JSON contained in right	`'{"a":1}' <@ metadata`
`?`	Key/element exists in JSON	`metadata ? 'warna'`
`?	`	Any of the keys exist
`?&`	All keys exist	`metadata ?& ARRAY['a','b']`
`		`

Pagination with PostgreSQL #

PostgreSQL uses the simple LIMIT ... OFFSET syntax, with COUNT(*) OVER() support for getting the total without a separate query.

import pool from './db/connection';

type UserSortField = 'nama' | 'email' | 'dibuat_pada';
const ALLOWED_SORT = new Set<UserSortField>(['nama', 'email', 'dibuat_pada']);

interface PaginasiResult<T> {
  data: T[];
  total: number;
  halaman: number;
  perHalaman: number;
  totalHalaman: number;
}

async function getUserPaginasi(
  halaman: number,
  perHalaman: number,
  sortBy: UserSortField = 'dibuat_pada',
  sortOrder: 'ASC' | 'DESC' = 'DESC'
): Promise<PaginasiResult<User>> {
  // Whitelist validation for interpolated columns
  const safeSort = ALLOWED_SORT.has(sortBy) ? sortBy : 'dibuat_pada';
  const safeOrder = sortOrder === 'ASC' ? 'ASC' : 'DESC';
  const offset = (halaman - 1) * perHalaman;

  // Window function COUNT(*) OVER() — total and data in one query
  const result = await pool.query<User & { total_rows: string }>(
    `SELECT
       id, nama, email, role, aktif, dibuat_pada,
       COUNT(*) OVER() AS total_rows
     FROM users
     WHERE aktif = true
     ORDER BY ${safeSort} ${safeOrder}
     LIMIT $1 OFFSET $2`,
    [perHalaman, offset]
  );

  const total = parseInt(result.rows[0]?.total_rows ?? '0', 10);

  return {
    data: result.rows.map(({ total_rows: _, ...user }) => user as User),
    total,
    halaman,
    perHalaman,
    totalHalaman: Math.ceil(total / perHalaman),
  };
}

The Repository Pattern #

// src/repositories/user.repository.ts
import { PoolClient } from 'pg';
import pool from '../db/connection';
import { tanganiPgError } from './error-handler';

export interface BuatUserInput {
  nama: string;
  email: string;
  passwordHash: string;
  role?: 'admin' | 'user' | 'moderator';
}

export class UserRepository {
  private db: typeof pool | PoolClient;

  // Can accept a pool (for regular queries) or a client (for transactions)
  constructor(db: typeof pool | PoolClient = pool) {
    this.db = db;
  }

  async findById(id: number): Promise<User | null> {
    const result = await this.db.query<User>(
      'SELECT * FROM users WHERE id = $1 AND aktif = true',
      [id]
    );
    return result.rows[0] ?? null;
  }

  async findByEmail(email: string): Promise<User | null> {
    const result = await this.db.query<User>(
      'SELECT * FROM users WHERE email = $1',
      [email]
    );
    return result.rows[0] ?? null;
  }

  async create(input: BuatUserInput): Promise<User> {
    try {
      const result = await this.db.query<User>(
        `INSERT INTO users (nama, email, password_hash, role, aktif, dibuat_pada)
         VALUES ($1, $2, $3, $4, true, NOW())
         RETURNING *`,
        [input.nama, input.email, input.passwordHash, input.role ?? 'user']
      );
      return result.rows[0];
    } catch (error) {
      tanganiPgError(error);
    }
  }

  async update(id: number, data: Partial<Pick<User, 'nama' | 'email'>>): Promise<User | null> {
    const entries = Object.entries(data).filter(([, v]) => v !== undefined);
    if (entries.length === 0) return null;

    const allowed = new Set(['nama', 'email']);
    const safe = entries.filter(([k]) => allowed.has(k));
    if (safe.length === 0) return null;

    const sets = safe.map(([col], i) => `${col} = $${i + 2}`);
    const vals = safe.map(([, v]) => v);

    const result = await this.db.query<User>(
      `UPDATE users SET ${sets.join(', ')}, diperbarui_pada = NOW()
       WHERE id = $1 RETURNING *`,
      [id, ...vals]
    );
    return result.rows[0] ?? null;
  }

  async delete(id: number): Promise<boolean> {
    const result = await this.db.query(
      'UPDATE users SET aktif = false WHERE id = $1 AND aktif = true',
      [id]
    );
    return (result.rowCount ?? 0) > 0;
  }

  async count(): Promise<number> {
    const result = await this.db.query<{ count: string }>(
      'SELECT COUNT(*) AS count FROM users WHERE aktif = true'
    );
    return parseInt(result.rows[0].count, 10);
  }
}

export const userRepo = new UserRepository();

When to Switch to Another Approach #

Keep using pg directly if:
  ✓ Complex queries needing full control over SQL
  ✓ Need PostgreSQL-specific features: JSONB operators, unnest, window functions
  ✓ Critical performance — pg is the fastest driver for PostgreSQL in Node.js
  ✓ The team is familiar with SQL and doesn't need ORM abstraction
  ✓ Applications with varied queries that are hard to model with an ORM

Consider an ORM / Query Builder if:
  ✗ Many models with complex relationships — Prisma or TypeORM
  ✗ Need automatic migrations from schemas — Prisma migrate or TypeORM
  ✗ Rapid prototyping — Prisma is very productive for PostgreSQL
  ✗ The team is less familiar with SQL — ORMs help but can hide performance issues
  ✗ Multi-database support — TypeORM or Knex.js

Library	Approach	Best For
`pg`	Direct queries	Full control, best performance
`Knex.js`	Query builder	Dynamic SQL, multi-database
`TypeORM`	ORM + decorators	Enterprise, many relationships
`Prisma`	ORM + schema file	Rapid dev, best DX
`Drizzle`	TypeScript-first ORM	Strong types, lightweight
`Kysely`	Type-safe query builder	TypeScript-first, query builder

Summary #

pool.query() for regular queries, pool.connect() for transactions — pool.query() manages connections automatically; use pool.connect() only when you need one connection for multiple queries (transactions).
client.release() is required in finally — the only thing worse than forgetting to close a file is forgetting to release a database connection; the whole application hangs when the pool runs out.
$1, $2, $3 parameters are positional — unlike MySQL (?) and MSSQL (@nama); the value order in the array must exactly match the $n order in the query.
RETURNING * eliminates the need for a SELECT after INSERT/UPDATE/DELETE — use it to get the latest data without an extra query round-trip.
ON CONFLICT DO UPDATE is PostgreSQL’s idiomatic upsert syntax — more atomic and efficient than a check-then-insert pattern.
= ANY($1) for IN queries with arrays — PostgreSQL can accept JavaScript arrays directly as bind parameters, no need to build dynamic placeholders.
NUMERIC/DECIMAL columns are returned as strings — always parse with parseFloat() or Number() before use in calculations.
ILIKE for case-insensitive search — a PostgreSQL feature not present in MySQL; more idiomatic than LOWER(kolom) LIKE LOWER($1).
JSONB for semi-structured data — if the schema changes often or data varies per row, a JSONB column with a GIN index is more flexible than many nullable columns.
Validate dynamic column names with a whitelist — column names can’t be parameterized with $n; always validate from a Set before interpolating into a query string.

← Previous: Oracle Next: MongoDB →