PostgreSQL #

PostgreSQL adalah database relasional open-source yang paling kaya fitur dan paling mendekati standar SQL penuh. Dari dukungan tipe data JSON/JSONB, array, enum, full-text search bawaan, hingga window functions — PostgreSQL menyediakan hampir semua yang kamu butuhkan tanpa harus berganti database. Library standar untuk TypeScript adalah pg (node-postgres) yang mature, battle-tested, dan mendukung semua fitur PostgreSQL secara native. Ada beberapa karakteristik PostgreSQL yang membedakannya dari MySQL dan SQL Server: parameter menggunakan $1, $2, $3 (positional), nama kolom dan tabel secara default case-insensitive dan disimpan lowercase, dan PostgreSQL memiliki RETURNING clause yang sangat powerful untuk mendapatkan data baris setelah INSERT/UPDATE/DELETE. Memahami idiom-idiom ini dari awal akan membuat kode TypeScript + PostgreSQL kamu terasa natural dan efisien.

Instalasi dan Setup #

Library utama adalah pg beserta type definitions-nya yang tersedia sebagai paket terpisah.

# Instalasi paket
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"
  }
}

Struktur project yang direkomendasikan:

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

Konfigurasi Connection Pool #

pg menyediakan kelas Pool untuk manajemen koneksi. Pool PostgreSQL lebih sederhana dari Oracle — tidak perlu connect() eksplisit, koneksi dibuat secara lazy saat pertama kali dibutuhkan.

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

// Konfigurasi pool
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,                  // maksimum koneksi bersamaan
  idleTimeoutMillis: 30000, // tutup koneksi idle setelah 30 detik
  connectionTimeoutMillis: 5000,  // timeout saat tunggu koneksi dari pool
  // Alternatif: gunakan connection string
  // connectionString: process.env.DATABASE_URL,
  // ssl: { rejectUnauthorized: false }  // untuk Heroku / Supabase
});

// Handle error pada pool — penting untuk mencegah crash
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);

Untuk konfigurasi via environment variable, pg mendukung DATABASE_URL secara otomatis:

// Cara paling ringkas — pg otomatis baca env DATABASE_URL
// DATABASE_URL=postgresql://user:password@host:5432/dbname
const pool = new Pool();  // tanpa config eksplisit

flowchart TD
    A[new Pool config] --> B[Pool siap\nlazy initialization]
    B --> C[pool.query atau\npool.connect dipanggil]
    C --> D{Ada koneksi\nidle?}
    D -- Ya --> E[Gunakan koneksi\nyang ada]
    D -- Tidak --> F{Jumlah < max?}
    F -- Ya --> G[Buat koneksi baru\nke PostgreSQL]
    F -- Tidak --> H[Tunggu hingga\nada koneksi idle]
    E --> I[Eksekusi query]
    G --> I
    H --> E
    I --> J{Pakai pool.query?}
    J -- Ya --> K[Koneksi otomatis\ndikembalikan ke pool]
    J -- Tidak --> L[Client.release\nwajib dipanggil manual]
    K --> B
    L --> B

pg punya dua cara query: pool.query() yang mengelola koneksi otomatis, dan pool.connect() yang memberi kamu PoolClient untuk kontrol manual. Jika menggunakan pool.connect(), kamu wajib memanggil client.release() di blok finally — tidak ada pengecualian. Lupa memanggil release() akan menghabiskan semua koneksi di pool dan membuat seluruh aplikasi hang.

Mendefinisikan Tipe untuk Row Database #

pg mengembalikan hasil query sebagai objek JavaScript biasa dengan nama kolom sebagai key. Karena PostgreSQL menyimpan nama kolom dalam lowercase, tipe TypeScript kamu bisa langsung menggunakan camelCase dengan konvensi snake_case sesuai nama kolom asli.

import { QueryResult } from 'pg';

// Tipe untuk tabel users
interface User {
  id: number;
  nama: string;
  email: string;
  password_hash: string;
  role: 'admin' | 'user' | 'moderator';
  aktif: boolean;          // PostgreSQL punya BOOLEAN native — otomatis jadi boolean
  dibuat_pada: Date;
  diperbarui_pada: Date | null;
}

interface Produk {
  id: number;
  nama: string;
  deskripsi: string | null;
  harga: string;           // NUMERIC/DECIMAL dikembalikan sebagai string oleh pg!
  stok: number;
  kategori_id: number;
  gambar_url: string | null;
  metadata: Record<string, unknown> | null;  // kolom JSONB
  dibuat_pada: Date;
}

// Helper untuk parse harga dari string ke number
function parseProduk(row: Produk): Produk & { harga: number } {
  return {
    ...row,
    harga: parseFloat(row.harga),
  };
}

Pemetaan tipe data PostgreSQL ke TypeScript:

PostgreSQL Type	TypeScript Type	Catatan
`INTEGER`, `SMALLINT`	`number`
`BIGINT`	`string`	Terlalu besar untuk JS number
`NUMERIC`, `DECIMAL`	`string`	`pg` mengembalikan string untuk presisi
`REAL`, `DOUBLE PRECISION`	`number`
`BOOLEAN`	`boolean`	Otomatis dikonversi
`VARCHAR`, `TEXT`	`string`
`TIMESTAMP`, `TIMESTAMPTZ`	`Date`
`UUID`	`string`	Format UUID string
`JSONB`, `JSON`	`object`	Otomatis di-parse
`ARRAY`	`T[]`	Otomatis dikonversi ke array
`ENUM`	`string`	Perlu cast manual ke union type

pg mengembalikan kolom NUMERIC dan DECIMAL sebagai string, bukan number — untuk menjaga presisi agar tidak hilang saat dikonversi ke JavaScript floating point. Selalu parse eksplisit dengan parseFloat() atau gunakan BigDecimal jika presisi kritis, misalnya untuk kalkulasi keuangan.

Query Dasar — SELECT #

PostgreSQL menggunakan $1, $2, $3 sebagai positional placeholder untuk parameterized query. Urutannya sesuai posisi nilai dalam array parameter.

import pool from './db/connection';

// ── SELECT sederhana tanpa parameter
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 dengan parameter — $1, $2, dst
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 dengan multiple parameter
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 dengan ILIKE — case-insensitive LIKE, fitur PostgreSQL
async function cariProdukByNama(keyword: string): Promise<Produk[]> {
  // Escape karakter khusus LIKE PostgreSQL: %, _, \
  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 dengan IN — PostgreSQL mendukung bind array dengan 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 bisa bind array langsung!
  );
  return result.rows;
}

// ── SELECT dengan JSONB — query kolom JSON
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 interpolasi — rentan SQL injection
async function cariUserTidakAman(email: string): Promise<User[]> {
  const result = await pool.query<User>(
    `SELECT * FROM users WHERE email = '${email}'`  // JANGAN!
  );
  return result.rows;
}

// BENAR: selalu gunakan parameterized query dengan $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;
}

Operasi INSERT #

PostgreSQL memiliki klausa RETURNING yang sangat powerful — bisa mengembalikan kolom apa saja dari baris yang baru diinsert, diupdate, atau didelete.

import pool from './db/connection';

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

// ── INSERT dengan RETURNING — mendapat seluruh baris yang dibuat
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 — hanya ambil 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 dengan ON CONFLICT — upsert idiomatis PostgreSQL
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];
}

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

  // unnest adalah cara idiomatis bulk insert di 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: Sisipkan baris baru
    PostgreSQL-->>App: rows[0] = seluruh baris baru
    Note over App: Tidak perlu SELECT terpisah\nuntuk ambil data yang baru dibuat

Operasi UPDATE dan DELETE #

RETURNING di PostgreSQL berlaku untuk UPDATE dan DELETE juga — fitur yang tidak ada di MySQL dan sangat berguna untuk audit log atau konfirmasi data.

import pool from './db/connection';

// ── UPDATE dengan 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 kolom yang boleh diupdate
  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 stok — 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 dengan RETURNING — dapatkan data sebelum dihapus
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 jika tidak ditemukan
}

// ── 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;
}

Transaction #

PostgreSQL menggunakan BEGIN, COMMIT, dan ROLLBACK yang dieksekusi melalui PoolClient. Kamu harus mengambil koneksi dari pool terlebih dahulu agar semua query dalam satu transaksi menggunakan koneksi yang sama.

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. Buat record pesanan
    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. Proses setiap 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]
      );

      // Kurangi stok — atomic, gagal jika stok tidak cukup
      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 { /* abaikan */ }
    throw error;
  } finally {
    client.release();  // WAJIB: kembalikan koneksi ke pool
  }
}

Helper generik untuk transaction:

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 { /* abaikan */ }
    throw error;
  } finally {
    client.release();
  }
}

// Penggunaan — jauh lebih bersih
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[Query pertama]
    C --> D{Error?}
    D -- Tidak --> E{Masih ada\noperasi?}
    E -- Ya --> C
    E -- Tidak --> F[client.query COMMIT]
    D -- Ya --> G[client.query ROLLBACK]
    F --> H[client.release]
    G --> H
    H --> I[Koneksi kembali\nke pool]

Error Handling #

pg menggunakan DatabaseError (kelas dari pg-protocol) yang memiliki properti code dengan format 5-karakter SQLSTATE standar.

import { DatabaseError } from 'pg';

// SQLSTATE codes PostgreSQL yang paling sering ditemui
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 untuk deadlock dan serialization failure
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 — Fitur Unggulan PostgreSQL #

PostgreSQL adalah satu-satunya database relasional yang mendukung query JSON secara native dengan performa tinggi melalui tipe JSONB. Ini memungkinkan skema yang fleksibel tanpa meninggalkan 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 dengan 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 dengan operator JSONB
async function produkByWarna(warna: string): Promise<ProdukDenganMetadata[]> {
  const result = await pool.query<ProdukDenganMetadata>(
    // @> : apakah JSON sebelah kiri mengandung JSON sebelah kanan?
    `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>(
    // ? : apakah key/elemen ada di JSON?
    `SELECT * FROM produk
     WHERE metadata->'tags' ? $1`,
    [tag]
  );
  return result.rows;
}

// ── UPDATE field JSONB tertentu — tanpa replace seluruh objek
async function updateMetadataProduk(
  id: number,
  updates: Record<string, unknown>
): Promise<boolean> {
  const result = await pool.query(
    // jsonb_set atau || untuk merge
    `UPDATE produk
     SET metadata = COALESCE(metadata, '{}'::jsonb) || $2::jsonb
     WHERE id = $1`,
    [id, JSON.stringify(updates)]
  );
  return (result.rowCount ?? 0) > 0;
}

Operator JSONB PostgreSQL yang paling berguna:

Operator	Keterangan	Contoh
`->`	Ambil field sebagai JSON	`metadata->'warna'`
`->>`	Ambil field sebagai text	`metadata->>'nama_brand'`
`@>`	JSON kiri mengandung kanan	`metadata @> '{"aktif":true}'`
`<@`	JSON kiri terkandung di kanan	`'{"a":1}' <@ metadata`
`?`	Key/elemen ada di JSON	`metadata ? 'warna'`
`?	`	Salah satu key ada
`?&`	Semua key ada	`metadata ?& ARRAY['a','b']`
`		`

Paginasi dengan PostgreSQL #

PostgreSQL menggunakan LIMIT ... OFFSET yang sederhana, dengan dukungan COUNT(*) OVER() untuk mendapatkan total tanpa query terpisah.

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>> {
  // Validasi whitelist untuk kolom yang diinterpolasi
  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 dan data dalam satu 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),
  };
}

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;

  // Bisa menerima pool (untuk query biasa) atau client (untuk transaksi)
  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();

Kapan Beralih ke Pendekatan Lain #

Tetap gunakan pg langsung jika:
  ✓ Query kompleks yang butuh kontrol penuh atas SQL
  ✓ Perlu fitur PostgreSQL spesifik: JSONB operators, unnest, window functions
  ✓ Performa kritis — pg adalah driver paling cepat untuk PostgreSQL di Node.js
  ✓ Tim sudah familiar SQL dan tidak butuh abstraksi ORM
  ✓ Aplikasi dengan query yang bervariasi dan sulit dimodelkan ORM

Pertimbangkan ORM / Query Builder jika:
  ✗ Banyak model dengan relasi kompleks — Prisma atau TypeORM
  ✗ Butuh migrasi otomatis dari schema — Prisma migrate atau TypeORM
  ✗ Rapid prototyping — Prisma sangat produktif untuk PostgreSQL
  ✗ Tim kurang familiar SQL — ORM membantu tapi bisa menyembunyikan performa
  ✗ Multi-database support — TypeORM atau Knex.js

Library	Pendekatan	Cocok Untuk
`pg`	Query langsung	Kontrol penuh, performa terbaik
`Knex.js`	Query builder	SQL dinamis, multi-database
`TypeORM`	ORM + dekorator	Enterprise, banyak relasi
`Prisma`	ORM + schema file	Rapid dev, DX terbaik
`Drizzle`	ORM TypeScript-first	Tipe kuat, ringan
`Kysely`	Query builder type-safe	TypeScript-first, query builder

Ringkasan #

pool.query() untuk query biasa, pool.connect() untuk transaksi — pool.query() mengelola koneksi otomatis; gunakan pool.connect() hanya saat butuh satu koneksi untuk beberapa query (transaksi).
client.release() wajib di finally — satu-satunya hal yang lebih buruk dari lupa menutup file adalah lupa me-release koneksi database; seluruh aplikasi akan hang saat pool habis.
Parameter $1, $2, $3 bersifat positional — berbeda dari MySQL (?) dan MSSQL (@nama); urutan nilai dalam array harus persis sesuai urutan $n dalam query.
RETURNING * menghilangkan kebutuhan SELECT setelah INSERT/UPDATE/DELETE — gunakan ini untuk mendapat data terbaru tanpa round-trip query tambahan.
ON CONFLICT DO UPDATE adalah sintaks upsert idiomatis PostgreSQL — lebih atomic dan efisien dari pola cek-lalu-insert.
= ANY($1) untuk query IN dengan array** — PostgreSQL bisa menerima array JavaScript langsung sebagai bind parameter, tidak perlu membangun placeholder dinamis.
Kolom NUMERIC/DECIMAL dikembalikan sebagai string — selalu parse dengan parseFloat() atau Number() sebelum digunakan dalam kalkulasi.
ILIKE untuk pencarian case-insensitive** — fitur PostgreSQL yang tidak ada di MySQL; lebih idiomatis dari LOWER(kolom) LIKE LOWER($1).
JSONB untuk data semi-terstruktur — jika skema sering berubah atau data bervariasi per baris, kolom JSONB dengan indeks GIN lebih fleksibel dari banyak kolom nullable.
Validasi whitelist untuk nama kolom dinamis — nama kolom tidak bisa diparameterkan dengan $n; selalu validasi dari Set sebelum diinterpolasi ke string query.

← Sebelumnya: Oracle Berikutnya: MongoDB →