Endpoint yang bagus punya satu ciri yang jarang diperhatikan: isinya tidak pernah mengejutkan. GET /artikel selalu mengembalikan daftar terurut yang bisa dilanjutkan ke halaman berikutnya, GET /artikel/:id selalu mengembalikan satu objek lengkap, POST selalu mengembalikan apa yang barusan dibuat. Sifat-sifat itu bukan warisan framework — mereka lahir dari query yang menuliskannya. Pada bagian ini kita menulis query untuk setiap endpoint baca dan tulis artikel: list dengan pagination keyset, detail yang dirangkai menjadi JSON, INSERT yang mengembalikan id, PATCH yang menyunting sebagian, serta filter tag dan pencarian teks.

Peta Endpoint ke Query #

Satu endpoint, satu query inti — dan setiap query membawa aturan main API di dalamnya:

EndpointQuery intiAturan main
GET /artikelSELECT ... ORDER BY dibuat_pada DESC, id DESC LIMIT nhanya yang published dan belum terhapus; kursor = baris terakhir
GET /artikel/:idSELECT jsonb_build_object(...) + jsonb_agg tag0 baris berarti 404
POST /artikelINSERT ... RETURNING id, judul, status, dibuat_padabadan respons 201 Created langsung dari RETURNING
PATCH /artikel/:idUPDATE SET kolom = COALESCE($1, kolom)NULL parameter = “jangan sentuh kolom ini”
DELETE /artikel/:idUPDATE SET dihapus_pada = ...soft delete; dibedah di Bagian 3 bersama semantiknya

Perhatikan bahwa tidak ada satu pun query di proyek ini yang membaca tabel tanpa menyaring dihapus_pada IS NULL — kecuali query internal seperti pemetaan tag. Filter itu adalah harga resmi soft delete: baris mati harus dikecualikan di mana-mana, secara sadar.

Menyiapkan Data Contoh #

Berkas pembuka membangun ulang skema Bagian 1 lalu mengisinya dengan blog mini: tiga penulis, lima tag, dan delapan artikel — lima terbit, dua draft, satu sudah dihapus lembut. Artikel terakhir itu penting: dialah bukti bahwa endpoint yang benar tidak pernah menampilkannya:

sql siapkan-tabel.sql 
-- siapkan-tabel.sql — fondasi Bagian 2: skema dari Bagian 1 plus data contoh
-- blog kecil. Semua UUID dan waktu dipaku agar output dapat dicocokkan.
-- Bagian ini dimaksudkan dapat dijalankan sendiri dari database kosong.

CREATE TABLE pengguna (
    id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    email         text NOT NULL,
    password_hash text NOT NULL,
    dibuat_pada   timestamptz NOT NULL,
    dihapus_pada  timestamptz,
    CONSTRAINT email_pengguna_unik UNIQUE (email)
);

CREATE TABLE artikel (
    id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    penulis_id  uuid NOT NULL REFERENCES pengguna (id),
    judul       text NOT NULL,
    isi         text NOT NULL,
    status      text NOT NULL DEFAULT 'draft',
    dibuat_pada timestamptz NOT NULL,
    diubah_pada timestamptz NOT NULL,
    dihapus_pada timestamptz,
    CONSTRAINT judul_tidak_kosong CHECK (judul <> ''),
    CONSTRAINT isi_tidak_kosong  CHECK (isi <> ''),
    CONSTRAINT status_sah CHECK (status IN ('draft', 'published'))
);

CREATE TABLE tag (
    id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    nama        text NOT NULL,
    dibuat_pada timestamptz NOT NULL,
    CONSTRAINT nama_tag_unik UNIQUE (nama),
    CONSTRAINT tag_selalu_lowercase CHECK (nama = lower(nama))
);

CREATE TABLE artikel_tag (
    artikel_id uuid NOT NULL REFERENCES artikel (id) ON DELETE CASCADE,
    tag_id     uuid NOT NULL REFERENCES tag (id) ON DELETE CASCADE,
    PRIMARY KEY (artikel_id, tag_id)
);

INSERT INTO pengguna (id, email, password_hash, dibuat_pada)
VALUES
    ('11111111-1111-4111-8111-111111111111',
     '[email protected]', 'sandi_hash_budi_placeholder',
     TIMESTAMPTZ '2026-04-01 08:00:00+07'),
    ('22222222-2222-4222-8222-222222222222',
     '[email protected]', 'sandi_hash_siti_placeholder',
     TIMESTAMPTZ '2026-04-02 09:30:00+07'),
    ('33333333-3333-4333-8333-333333333333',
     '[email protected]', 'sandi_hash_dewi_placeholder',
     TIMESTAMPTZ '2026-04-03 10:15:00+07');

INSERT INTO tag (id, nama, dibuat_pada)
VALUES
    ('10000000-0000-4000-8000-000000000001', 'postgresql',
     TIMESTAMPTZ '2026-04-01 08:05:00+07'),
    ('10000000-0000-4000-8000-000000000002', 'sql',
     TIMESTAMPTZ '2026-04-01 08:05:00+07'),
    ('10000000-0000-4000-8000-000000000003', 'database',
     TIMESTAMPTZ '2026-04-01 08:05:00+07'),
    ('10000000-0000-4000-8000-000000000004', 'backend',
     TIMESTAMPTZ '2026-04-01 08:05:00+07'),
    ('10000000-0000-4000-8000-000000000005', 'kinerja',
     TIMESTAMPTZ '2026-04-01 08:05:00+07');

INSERT INTO artikel (id, penulis_id, judul, isi, status,
                     dibuat_pada, diubah_pada, dihapus_pada)
VALUES
    ('20000000-0000-4000-8000-000000000001',
     '11111111-1111-4111-8111-111111111111',
     'Kenali UUID sebagai Primary Key',
     'Primary key UUID membuat identifier sulit ditebak dan aman diekspos di URL.',
     'published',
     TIMESTAMPTZ '2026-05-01 09:00:00+07',
     TIMESTAMPTZ '2026-05-01 09:00:00+07',
     NULL),
    ('20000000-0000-4000-8000-000000000002',
     '22222222-2222-4222-8222-222222222222',
     'Rancangan Skema untuk REST API',
     'Skema yang baik membuat validasi endpoint menjadi murah.',
     'published',
     TIMESTAMPTZ '2026-05-03 10:00:00+07',
     TIMESTAMPTZ '2026-05-03 10:00:00+07',
     NULL),
    ('20000000-0000-4000-8000-000000000003',
     '33333333-3333-4333-8333-333333333333',
     'Pagination Keyset vs OFFSET',
     'OFFSET membaca lalu membuang baris; keyset melompat lewat index komposit.',
     'published',
     TIMESTAMPTZ '2026-05-05 08:30:00+07',
     TIMESTAMPTZ '2026-05-06 16:45:00+07',
     NULL),
    ('20000000-0000-4000-8000-000000000004',
     '11111111-1111-4111-8111-111111111111',
     'Soft Delete dan Jejak Audit',
     'Kolom dihapus_pada yang NULL memisahkan baris hidup dari baris mati.',
     'draft',
     TIMESTAMPTZ '2026-05-06 11:20:00+07',
     TIMESTAMPTZ '2026-05-06 11:20:00+07',
     NULL),
    ('20000000-0000-4000-8000-000000000005',
     '22222222-2222-4222-8222-222222222222',
     'Validasi Data di Tingkat Database',
     'Constraint yang tertulis di skema bekerja untuk semua aplikasi.',
     'published',
     TIMESTAMPTZ '2026-05-07 14:00:00+07',
     TIMESTAMPTZ '2026-05-07 14:00:00+07',
     NULL),
    ('20000000-0000-4000-8000-000000000006',
     '33333333-3333-4333-8333-333333333333',
     'EXPLAIN sebagai Alat Diagnosis Query',
     'Bacalah rencana eksekusi sebelum menambah index demi index.',
     'draft',
     TIMESTAMPTZ '2026-05-09 07:45:00+07',
     TIMESTAMPTZ '2026-05-09 07:45:00+07',
     NULL),
    ('20000000-0000-4000-8000-000000000007',
     '11111111-1111-4111-8111-111111111111',
     'Transaksi Atomik untuk Endpoint',
     'INSERT artikel dan tag harus berjalan dalam satu transaksi.',
     'published',
     TIMESTAMPTZ '2026-05-10 13:10:00+07',
     TIMESTAMPTZ '2026-05-10 13:10:00+07',
     NULL),
    ('20000000-0000-4000-8000-000000000008',
     '22222222-2222-4222-8222-222222222222',
     'Index Parsial untuk Data Hidup',
     'Index parsial hanya menampung baris yang belum dihapus lembut.',
     'published',
     TIMESTAMPTZ '2026-05-12 09:50:00+07',
     TIMESTAMPTZ '2026-05-12 09:50:00+07',
     TIMESTAMPTZ '2026-05-20 10:00:00+07');

INSERT INTO artikel_tag (artikel_id, tag_id)
VALUES
    ('20000000-0000-4000-8000-000000000001',
     '10000000-0000-4000-8000-000000000001'),
    ('20000000-0000-4000-8000-000000000001',
     '10000000-0000-4000-8000-000000000003'),
    ('20000000-0000-4000-8000-000000000002',
     '10000000-0000-4000-8000-000000000003'),
    ('20000000-0000-4000-8000-000000000002',
     '10000000-0000-4000-8000-000000000004'),
    ('20000000-0000-4000-8000-000000000003',
     '10000000-0000-4000-8000-000000000002'),
    ('20000000-0000-4000-8000-000000000003',
     '10000000-0000-4000-8000-000000000005'),
    ('20000000-0000-4000-8000-000000000004',
     '10000000-0000-4000-8000-000000000003'),
    ('20000000-0000-4000-8000-000000000004',
     '10000000-0000-4000-8000-000000000004'),
    ('20000000-0000-4000-8000-000000000005',
     '10000000-0000-4000-8000-000000000001'),
    ('20000000-0000-4000-8000-000000000005',
     '10000000-0000-4000-8000-000000000002'),
    ('20000000-0000-4000-8000-000000000006',
     '10000000-0000-4000-8000-000000000005'),
    ('20000000-0000-4000-8000-000000000007',
     '10000000-0000-4000-8000-000000000002'),
    ('20000000-0000-4000-8000-000000000007',
     '10000000-0000-4000-8000-000000000004'),
    ('20000000-0000-4000-8000-000000000008',
     '10000000-0000-4000-8000-000000000001'),
    ('20000000-0000-4000-8000-000000000008',
     '10000000-0000-4000-8000-000000000005');

-- Kondisi awal bagian: 8 artikel (5 published, 2 draft, 1 dihapus lembut).
SELECT a.judul, p.email AS penulis, a.status, a.dibuat_pada,
       count(at2.tag_id) AS jumlah_tag
FROM artikel a
JOIN pengguna p ON p.id = a.penulis_id
LEFT JOIN artikel_tag at2 ON at2.artikel_id = a.id
GROUP BY a.judul, p.email, a.status, a.dibuat_pada
ORDER BY a.dibuat_pada;

Output yang Dihasilkan:

text 
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 3
INSERT 0 5
INSERT 0 8
INSERT 0 15
                judul                 |     penulis      |  status   |      dibuat_pada       | jumlah_tag 
--------------------------------------+------------------+-----------+------------------------+------------
 Kenali UUID sebagai Primary Key      | [email protected] | published | 2026-05-01 09:00:00+07 |          2
 Rancangan Skema untuk REST API       | [email protected] | published | 2026-05-03 10:00:00+07 |          2
 Pagination Keyset vs OFFSET          | [email protected] | published | 2026-05-05 08:30:00+07 |          2
 Soft Delete dan Jejak Audit          | [email protected] | draft     | 2026-05-06 11:20:00+07 |          2
 Validasi Data di Tingkat Database    | [email protected] | published | 2026-05-07 14:00:00+07 |          2
 EXPLAIN sebagai Alat Diagnosis Query | [email protected] | draft     | 2026-05-09 07:45:00+07 |          1
 Transaksi Atomik untuk Endpoint      | [email protected] | published | 2026-05-10 13:10:00+07 |          2
 Index Parsial untuk Data Hidup       | [email protected] | published | 2026-05-12 09:50:00+07 |          2
(8 rows)

Daftar Artikel: Pagination Keyset #

LIMIT 3 OFFSET 3 terlihat paling sederhana — sampai klien meminta halaman ke-10.001 dan database terpaksa membaca lalu membuang 30.000 baris setiap kali. Pola keyset memakai fakta bahwa daftar sudah terurut: cukup ingat posisi baris terakhir yang dilihat, lalu minta “semua yang berada di bawahnya”. Posisi itu adalah pasangan (dibuat_pada, id) — id ikut serta karena dua artikel bisa lahir pada detik yang sama:

sql daftar-artikel.sql 
-- daftar-artikel.sql — GET /artikel: daftar artikel terbit dengan
-- pagination keyset. Kursor adalah pasangan (dibuat_pada, id) dari baris
-- terakhir halaman sebelumnya; halaman berikutnya dimulai tepat di bawahnya.

-- Total untuk header respons (mis. X-Total-Count), query terpisah.
SELECT count(*) AS total_terbit
FROM artikel
WHERE status = 'published' AND dihapus_pada IS NULL;

-- Halaman 1: tiga artikel terbaru.
SELECT a.id, a.judul, p.email AS penulis, a.dibuat_pada
FROM artikel a
JOIN pengguna p ON p.id = a.penulis_id
WHERE a.status = 'published'
  AND a.dihapus_pada IS NULL
ORDER BY a.dibuat_pada DESC, a.id DESC
LIMIT 3;

-- Halaman 2: kursor = baris terakhir halaman 1,
-- yaitu (dibuat_pada, id) artikel 'Pagination Keyset vs OFFSET'.
SELECT a.id, a.judul, p.email AS penulis, a.dibuat_pada
FROM artikel a
JOIN pengguna p ON p.id = a.penulis_id
WHERE (a.dibuat_pada, a.id) < (TIMESTAMPTZ '2026-05-05 08:30:00+07',
                                '20000000-0000-4000-8000-000000000003'::uuid)
  AND a.status = 'published'
  AND a.dihapus_pada IS NULL
ORDER BY a.dibuat_pada DESC, a.id DESC
LIMIT 3;

Output yang Dihasilkan:

text 
 total_terbit 
--------------
            5
(1 row)

                  id                  |               judul               |     penulis      |      dibuat_pada       
--------------------------------------+-----------------------------------+------------------+------------------------
 20000000-0000-4000-8000-000000000007 | Transaksi Atomik untuk Endpoint   | [email protected] | 2026-05-10 13:10:00+07
 20000000-0000-4000-8000-000000000005 | Validasi Data di Tingkat Database | [email protected] | 2026-05-07 14:00:00+07
 20000000-0000-4000-8000-000000000003 | Pagination Keyset vs OFFSET       | [email protected] | 2026-05-05 08:30:00+07
(3 rows)

                  id                  |              judul              |     penulis      |      dibuat_pada       
--------------------------------------+---------------------------------+------------------+------------------------
 20000000-0000-4000-8000-000000000002 | Rancangan Skema untuk REST API  | [email protected] | 2026-05-03 10:00:00+07
 20000000-0000-4000-8000-000000000001 | Kenali UUID sebagai Primary Key | [email protected] | 2026-05-01 09:00:00+07
(2 rows)

Tiga pernyataan di dalamnya membentuk satu respons GET /artikel yang utuh:

  1. SELECT count(*) untuk total — bahan header X-Total-Count atau info “dari 5 artikel”. Ia dihitung terpisah karena pola keyset sendiri tidak menghitung sisa baris.
  2. Halaman 1 mengembalikan tiga artikel terbaru. Klien menyimpan baris terakhirnya sebagai kursor: (TIMESTAMPTZ '2026-05-05 08:30:00+07', '20000000-...').
  3. Halaman 2 membandingkan baris demi baris lewat perbandingan baris (a.dibuat_pada, a.id) < (kursor_waktu, kursor_id) — PostgreSQL membandingkan pasangan secara leksikografis, persis seperti urutan ORDER BY-nya, sehingga hasilnya stabil dan tidak ada baris yang terlewat atau tampil dua kali.

Detail Artikel: Merangkai Respons JSON #

Endpoint detail mengembalikan satu objek — bukan baris tabel plus tabel tag terpisah yang harus dijahit aplikasi. jsonb_build_object merangkai kolom menjadi objek, dan jsonb_agg menggulung hasil join tag menjadi array. Satu query, satu baris hasil, siap dikirim:

sql detail-artikel.sql 
-- detail-artikel.sql — GET /artikel/:id: satu baris hasil = satu badan
-- respons JSON. jsonb_build_object merangkai kolom, dan jsonb_agg
-- menggulung tag dari relasi banyak-ke-banyak menjadi array.

-- 200 OK: detail artikel lengkap beserta tag.
SELECT jsonb_build_object(
           'id', a.id,
           'judul', a.judul,
           'isi', a.isi,
           'penulis', p.email,
           'status', a.status,
           'dibuat_pada', a.dibuat_pada,
           'diubah_pada', a.diubah_pada,
           'tags', COALESCE(
               jsonb_agg(t.nama ORDER BY t.nama)
                   FILTER (WHERE t.nama IS NOT NULL),
               '[]'::jsonb)
       ) AS respons
FROM artikel a
JOIN pengguna p ON p.id = a.penulis_id
LEFT JOIN artikel_tag at2 ON at2.artikel_id = a.id
LEFT JOIN tag t ON t.id = at2.tag_id
WHERE a.id = '20000000-0000-4000-8000-000000000001'
  AND a.dihapus_pada IS NULL
GROUP BY a.id, a.judul, a.isi, p.email, a.status,
         a.dibuat_pada, a.diubah_pada;

-- 404 Not Found: artikel yang dihapus lembut tidak pernah tampil.
SELECT jsonb_build_object(
           'id', a.id,
           'judul', a.judul,
           'isi', a.isi,
           'penulis', p.email,
           'status', a.status,
           'dibuat_pada', a.dibuat_pada,
           'diubah_pada', a.diubah_pada,
           'tags', COALESCE(
               jsonb_agg(t.nama ORDER BY t.nama)
                   FILTER (WHERE t.nama IS NOT NULL),
               '[]'::jsonb)
       ) AS respons
FROM artikel a
JOIN pengguna p ON p.id = a.penulis_id
LEFT JOIN artikel_tag at2 ON at2.artikel_id = a.id
LEFT JOIN tag t ON t.id = at2.tag_id
WHERE a.id = '20000000-0000-4000-8000-000000000008'
  AND a.dihapus_pada IS NULL
GROUP BY a.id, a.judul, a.isi, p.email, a.status,
         a.dibuat_pada, a.diubah_pada;

Output yang Dihasilkan:

text 
                                                                                                                                                                              respons                                                                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"id": "20000000-0000-4000-8000-000000000001", "isi": "Primary key UUID membuat identifier sulit ditebak dan aman diekspos di URL.", "tags": ["database", "postgresql"], "judul": "Kenali UUID sebagai Primary Key", "status": "published", "penulis": "[email protected]", "dibuat_pada": "2026-05-01T09:00:00+07:00", "diubah_pada": "2026-05-01T09:00:00+07:00"}
(1 row)

 respons 
---------
(0 rows)

Bedah baris-per-barisnya:

  1. jsonb_build_object('id', a.id, ...): pasangan kunci-nilai; tipe timestamptz otomatis dirender sebagai ISO 8601 (2026-05-01T09:00:00+07:00) — format yang langsung bisa diparse klien JavaScript.
  2. LEFT JOIN artikel_tag + LEFT JOIN tag: artikel tanpa tag tetap tampil, tidak lenyap oleh join.
  3. jsonb_agg(t.nama ORDER BY t.nama) FILTER (WHERE t.nama IS NOT NULL): tag digulung terurut; FILTER membuang NULL buatan LEFT JOIN milik artikel tanpa tag. Tanpa ORDER BY di dalam agregat, urutan elemen array bergantung rencana eksekusi — ditambahkan agar output dapat dicocokkan.
  4. COALESCE(..., '[]'::jsonb): artikel tanpa tag mendapat array kosong [], bukan NULL — jauh lebih ramah bagi klien.
  5. Query kedua menghasilkan nol baris: artikel sudah dihapus lembut, sehingga filter dihapus_pada IS NULL membuatnya tak pernah ada bagi API. Bagi aplikasi, nol baris diterjemahkan menjadi 404 Not Found — artikel “tidak ditemukan”, padahal masih ada di database.

Membuat Artikel: INSERT RETURNING #

Endpoint tulis tidak butuh query baca susulan — RETURNING mengembalikan baris yang barusan tersimpan, lengkap dengan nilai yang diisi database:

sql buat-artikel.sql 
-- buat-artikel.sql — POST /artikel: INSERT satu baris lalu RETURNING
-- mengembalikan kolom yang dibutuhkan badan respons 201 Created.
-- Pada server produksi id diisi otomatis oleh DEFAULT gen_random_uuid();
-- pada contoh ini id ditulis literal agar output dapat dicocokkan.

INSERT INTO artikel (id, penulis_id, judul, isi, status,
                     dibuat_pada, diubah_pada)
VALUES ('20000000-0000-4000-8000-000000000009',
        '33333333-3333-4333-8333-333333333333',
        'Trik ILIKE untuk Pencarian',
        'Pencarian ILIKE cukup untuk banyak kasus sebelum pindah ke full-text search.',
        'draft',
        TIMESTAMPTZ '2026-05-13 07:30:00+07',
        TIMESTAMPTZ '2026-05-13 07:30:00+07')
RETURNING id, judul, status, dibuat_pada;

-- GET /artikel/:id untuk artikel yang baru dibuat: tag masih kosong,
-- dirender sebagai array kosong, bukan NULL.
SELECT jsonb_build_object(
           'id', a.id,
           'judul', a.judul,
           'status', a.status,
           'dibuat_pada', a.dibuat_pada,
           'tags', COALESCE(
               jsonb_agg(t.nama ORDER BY t.nama)
                   FILTER (WHERE t.nama IS NOT NULL),
               '[]'::jsonb)
       ) AS respons
FROM artikel a
LEFT JOIN artikel_tag at2 ON at2.artikel_id = a.id
LEFT JOIN tag t ON t.id = at2.tag_id
WHERE a.id = '20000000-0000-4000-8000-000000000009'
  AND a.dihapus_pada IS NULL
GROUP BY a.id, a.judul, a.status, a.dibuat_pada;

Output yang Dihasilkan:

text 
                  id                  |           judul            | status |      dibuat_pada       
--------------------------------------+----------------------------+--------+------------------------
 20000000-0000-4000-8000-000000000009 | Trik ILIKE untuk Pencarian | draft  | 2026-05-13 07:30:00+07
(1 row)

INSERT 0 1
                                                                             respons                                                                              
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"id": "20000000-0000-4000-8000-000000000009", "tags": [], "judul": "Trik ILIKE untuk Pencarian", "status": "draft", "dibuat_pada": "2026-05-13T07:30:00+07:00"}
(1 row)

RETURNING id, judul, status, dibuat_pada persis membentuk badan respons 201 Created: aplikasi cukup menyusun header Location: /artikel/<id> dan mengirim hasil RETURNING-nya. Query kedua memanggil ulang pola detail dari subbab sebelumnya — dan memperlihatkan tags: [] hasil COALESCE, tanda artikel baru memang belum bertag. Pada server produksi kolom id dibiarkan terisi DEFAULT gen_random_uuid() dan nilainya baru diketahui setelah RETURNING; contoh ini menuliskan literal agar output-nya dapat dicocokkan. Operasi RETURNING sendiri dibahas tuntas di chapter RETURNING Clause .

Mengubah Sebagian: PATCH dengan COALESCE #

PATCH berarti “ubah hanya kolom yang dikirim”. Trik klasiknya: aplikasi selalu mengirim parameter untuk semua kolom yang boleh diubah, dengan NULL untuk kolom yang tidak disebut badan request — lalu COALESCE memilih antara nilai baru dan nilai lama:

sql ubah-artikel.sql 
-- ubah-artikel.sql — PATCH /artikel/:id: pembaruan parsial.
-- Aplikasi mengirim satu query dengan parameter untuk semua kolom yang
-- boleh diubah; nilai NULL berarti "kolom tidak ada di badan request,
-- jangan sentuh". COALESCE menerjemahkan aturan itu.

-- PATCH hanya judul: kolom lain NULL, nilai lama dipertahankan.
UPDATE artikel SET
    judul       = COALESCE('Membaca Rencana Eksekusi EXPLAIN', judul),
    isi         = COALESCE(NULL, isi),
    status      = COALESCE(NULL, status),
    diubah_pada = TIMESTAMPTZ '2026-05-13 09:15:00+07'
WHERE id = '20000000-0000-4000-8000-000000000006'
  AND dihapus_pada IS NULL
RETURNING id, judul, status, diubah_pada;

-- PATCH hanya status: menerbitkan artikel tanpa menyentuh judul dan isi.
UPDATE artikel SET
    judul       = COALESCE(NULL, judul),
    isi         = COALESCE(NULL, isi),
    status      = COALESCE('published', status),
    diubah_pada = TIMESTAMPTZ '2026-05-13 10:00:00+07'
WHERE id = '20000000-0000-4000-8000-000000000004'
  AND dihapus_pada IS NULL
RETURNING id, judul, status, diubah_pada;

Output yang Dihasilkan:

text 
                  id                  |              judul               | status |      diubah_pada       
--------------------------------------+----------------------------------+--------+------------------------
 20000000-0000-4000-8000-000000000006 | Membaca Rencana Eksekusi EXPLAIN | draft  | 2026-05-13 09:15:00+07
(1 row)

UPDATE 1
                  id                  |            judul            |  status   |      diubah_pada       
--------------------------------------+-----------------------------+-----------+------------------------
 20000000-0000-4000-8000-000000000004 | Soft Delete dan Jejak Audit | published | 2026-05-13 10:00:00+07
(1 row)

UPDATE 1

Perhatikan dua hal pada hasilnya: UPDATE pertama mengubah judul dan diubah_pada sementara status tetap draft — nilai lama menang karena parameternya NULL; UPDATE kedua hanya mengirim status = 'published' dan judul-isinya tidak tersentuh. diubah_pada selalu ikut diperbarui: kolom ini bukan hiasan audit, melainkan penanda versi yang dipakai mendeteksi tulisan bentrok di Bagian 3.

Filter dan Pencarian #

Query string seperti ?tag=backend&q=index diterjemahkan menjadi tiga pola berbeda:

sql cari-artikel.sql 
-- cari-artikel.sql — filter dan pencarian untuk GET /artikel.
-- Tiga pola: filter kolom biasa (status), filter relasi EXISTS (tag),
-- dan pencarian teks ILIKE pada judul dan isi.

-- GET /artikel?status=draft — draft tersisa setelah artikel lain diterbitkan.
SELECT a.id, a.judul, a.status, a.dibuat_pada
FROM artikel a
WHERE a.status = 'draft'
  AND a.dihapus_pada IS NULL
ORDER BY a.dibuat_pada DESC, a.id DESC;

-- GET /artikel?tag=backend — filter lewat EXISTS pada tabel junction:
-- tidak menambah baris hasil sekalipun artikel punya banyak tag.
SELECT a.id, a.judul, a.status, a.dibuat_pada
FROM artikel a
WHERE EXISTS (
          SELECT 1
          FROM artikel_tag at2
          JOIN tag t ON t.id = at2.tag_id
          WHERE at2.artikel_id = a.id
            AND t.nama = 'backend')
  AND a.dihapus_pada IS NULL
ORDER BY a.dibuat_pada DESC, a.id DESC;

-- GET /artikel?q=index — pencarian case-insensitive di judul dan isi;
-- artikel yang dihapus lembut tetap tidak ikut.
SELECT a.id, a.judul, a.dibuat_pada
FROM artikel a
WHERE (a.judul ILIKE '%index%' OR a.isi ILIKE '%index%')
  AND a.dihapus_pada IS NULL
ORDER BY a.dibuat_pada DESC, a.id DESC;

Output yang Dihasilkan:

text 
                  id                  |              judul               | status |      dibuat_pada       
--------------------------------------+----------------------------------+--------+------------------------
 20000000-0000-4000-8000-000000000009 | Trik ILIKE untuk Pencarian       | draft  | 2026-05-13 07:30:00+07
 20000000-0000-4000-8000-000000000006 | Membaca Rencana Eksekusi EXPLAIN | draft  | 2026-05-09 07:45:00+07
(2 rows)

                  id                  |              judul              |  status   |      dibuat_pada       
--------------------------------------+---------------------------------+-----------+------------------------
 20000000-0000-4000-8000-000000000007 | Transaksi Atomik untuk Endpoint | published | 2026-05-10 13:10:00+07
 20000000-0000-4000-8000-000000000004 | Soft Delete dan Jejak Audit     | published | 2026-05-06 11:20:00+07
 20000000-0000-4000-8000-000000000002 | Rancangan Skema untuk REST API  | published | 2026-05-03 10:00:00+07
(3 rows)

                  id                  |              judul               |      dibuat_pada       
--------------------------------------+----------------------------------+------------------------
 20000000-0000-4000-8000-000000000006 | Membaca Rencana Eksekusi EXPLAIN | 2026-05-09 07:45:00+07
 20000000-0000-4000-8000-000000000003 | Pagination Keyset vs OFFSET      | 2026-05-05 08:30:00+07
(2 rows)
  1. Filter kolom biasa (?status=draft): WHERE a.status = 'draft' — setelah dua PATCH di subbab sebelumnya, tersisa dua draft.
  2. Filter relasi (?tag=backend): EXISTS pada join artikel_tag-tag. Keunggulannya dibanding JOIN langsung: artikel dengan tiga tag pun tetap muncul sekali — tidak perlu DISTINCT atau agregat untuk meratakan hasil. Pola EXISTS dibahas di Subqueries dan CTE .
  3. Pencarian teks (?q=index): ILIKE pada judul dan isi, case-insensitive. Artikel “Index Parsial untuk Data Hidup” yang sudah dihapus lembut tidak ikut walau judulnya paling cocok — filter dihapus_pada IS NULL tetap berlaku di semua pola.

Pola ILIKE '%index%' cukup untuk blog ini; ketika katalog membesar dan pencarian jadi fitur utama, giliran full-text search dengan tsvector dan GIN index mengambil alih — dibahas di section Full-Text Search .

Referensi #

Berkas Sumber

Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.

buat-artikel.sql

Unduh berkas
sql 
-- buat-artikel.sql — POST /artikel: INSERT satu baris lalu RETURNING
-- mengembalikan kolom yang dibutuhkan badan respons 201 Created.
-- Pada server produksi id diisi otomatis oleh DEFAULT gen_random_uuid();
-- pada contoh ini id ditulis literal agar output dapat dicocokkan.

INSERT INTO artikel (id, penulis_id, judul, isi, status,
                     dibuat_pada, diubah_pada)
VALUES ('20000000-0000-4000-8000-000000000009',
        '33333333-3333-4333-8333-333333333333',
        'Trik ILIKE untuk Pencarian',
        'Pencarian ILIKE cukup untuk banyak kasus sebelum pindah ke full-text search.',
        'draft',
        TIMESTAMPTZ '2026-05-13 07:30:00+07',
        TIMESTAMPTZ '2026-05-13 07:30:00+07')
RETURNING id, judul, status, dibuat_pada;

-- GET /artikel/:id untuk artikel yang baru dibuat: tag masih kosong,
-- dirender sebagai array kosong, bukan NULL.
SELECT jsonb_build_object(
           'id', a.id,
           'judul', a.judul,
           'status', a.status,
           'dibuat_pada', a.dibuat_pada,
           'tags', COALESCE(
               jsonb_agg(t.nama ORDER BY t.nama)
                   FILTER (WHERE t.nama IS NOT NULL),
               '[]'::jsonb)
       ) AS respons
FROM artikel a
LEFT JOIN artikel_tag at2 ON at2.artikel_id = a.id
LEFT JOIN tag t ON t.id = at2.tag_id
WHERE a.id = '20000000-0000-4000-8000-000000000009'
  AND a.dihapus_pada IS NULL
GROUP BY a.id, a.judul, a.status, a.dibuat_pada;

cari-artikel.sql

Unduh berkas
sql 
-- cari-artikel.sql — filter dan pencarian untuk GET /artikel.
-- Tiga pola: filter kolom biasa (status), filter relasi EXISTS (tag),
-- dan pencarian teks ILIKE pada judul dan isi.

-- GET /artikel?status=draft — draft tersisa setelah artikel lain diterbitkan.
SELECT a.id, a.judul, a.status, a.dibuat_pada
FROM artikel a
WHERE a.status = 'draft'
  AND a.dihapus_pada IS NULL
ORDER BY a.dibuat_pada DESC, a.id DESC;

-- GET /artikel?tag=backend — filter lewat EXISTS pada tabel junction:
-- tidak menambah baris hasil sekalipun artikel punya banyak tag.
SELECT a.id, a.judul, a.status, a.dibuat_pada
FROM artikel a
WHERE EXISTS (
          SELECT 1
          FROM artikel_tag at2
          JOIN tag t ON t.id = at2.tag_id
          WHERE at2.artikel_id = a.id
            AND t.nama = 'backend')
  AND a.dihapus_pada IS NULL
ORDER BY a.dibuat_pada DESC, a.id DESC;

-- GET /artikel?q=index — pencarian case-insensitive di judul dan isi;
-- artikel yang dihapus lembut tetap tidak ikut.
SELECT a.id, a.judul, a.dibuat_pada
FROM artikel a
WHERE (a.judul ILIKE '%index%' OR a.isi ILIKE '%index%')
  AND a.dihapus_pada IS NULL
ORDER BY a.dibuat_pada DESC, a.id DESC;

daftar-artikel.sql

Unduh berkas
sql 
-- daftar-artikel.sql — GET /artikel: daftar artikel terbit dengan
-- pagination keyset. Kursor adalah pasangan (dibuat_pada, id) dari baris
-- terakhir halaman sebelumnya; halaman berikutnya dimulai tepat di bawahnya.

-- Total untuk header respons (mis. X-Total-Count), query terpisah.
SELECT count(*) AS total_terbit
FROM artikel
WHERE status = 'published' AND dihapus_pada IS NULL;

-- Halaman 1: tiga artikel terbaru.
SELECT a.id, a.judul, p.email AS penulis, a.dibuat_pada
FROM artikel a
JOIN pengguna p ON p.id = a.penulis_id
WHERE a.status = 'published'
  AND a.dihapus_pada IS NULL
ORDER BY a.dibuat_pada DESC, a.id DESC
LIMIT 3;

-- Halaman 2: kursor = baris terakhir halaman 1,
-- yaitu (dibuat_pada, id) artikel 'Pagination Keyset vs OFFSET'.
SELECT a.id, a.judul, p.email AS penulis, a.dibuat_pada
FROM artikel a
JOIN pengguna p ON p.id = a.penulis_id
WHERE (a.dibuat_pada, a.id) < (TIMESTAMPTZ '2026-05-05 08:30:00+07',
                                '20000000-0000-4000-8000-000000000003'::uuid)
  AND a.status = 'published'
  AND a.dihapus_pada IS NULL
ORDER BY a.dibuat_pada DESC, a.id DESC
LIMIT 3;

detail-artikel.sql

Unduh berkas
sql 
-- detail-artikel.sql — GET /artikel/:id: satu baris hasil = satu badan
-- respons JSON. jsonb_build_object merangkai kolom, dan jsonb_agg
-- menggulung tag dari relasi banyak-ke-banyak menjadi array.

-- 200 OK: detail artikel lengkap beserta tag.
SELECT jsonb_build_object(
           'id', a.id,
           'judul', a.judul,
           'isi', a.isi,
           'penulis', p.email,
           'status', a.status,
           'dibuat_pada', a.dibuat_pada,
           'diubah_pada', a.diubah_pada,
           'tags', COALESCE(
               jsonb_agg(t.nama ORDER BY t.nama)
                   FILTER (WHERE t.nama IS NOT NULL),
               '[]'::jsonb)
       ) AS respons
FROM artikel a
JOIN pengguna p ON p.id = a.penulis_id
LEFT JOIN artikel_tag at2 ON at2.artikel_id = a.id
LEFT JOIN tag t ON t.id = at2.tag_id
WHERE a.id = '20000000-0000-4000-8000-000000000001'
  AND a.dihapus_pada IS NULL
GROUP BY a.id, a.judul, a.isi, p.email, a.status,
         a.dibuat_pada, a.diubah_pada;

-- 404 Not Found: artikel yang dihapus lembut tidak pernah tampil.
SELECT jsonb_build_object(
           'id', a.id,
           'judul', a.judul,
           'isi', a.isi,
           'penulis', p.email,
           'status', a.status,
           'dibuat_pada', a.dibuat_pada,
           'diubah_pada', a.diubah_pada,
           'tags', COALESCE(
               jsonb_agg(t.nama ORDER BY t.nama)
                   FILTER (WHERE t.nama IS NOT NULL),
               '[]'::jsonb)
       ) AS respons
FROM artikel a
JOIN pengguna p ON p.id = a.penulis_id
LEFT JOIN artikel_tag at2 ON at2.artikel_id = a.id
LEFT JOIN tag t ON t.id = at2.tag_id
WHERE a.id = '20000000-0000-4000-8000-000000000008'
  AND a.dihapus_pada IS NULL
GROUP BY a.id, a.judul, a.isi, p.email, a.status,
         a.dibuat_pada, a.diubah_pada;

siapkan-tabel.sql

Unduh berkas
sql 
-- siapkan-tabel.sql — fondasi Bagian 2: skema dari Bagian 1 plus data contoh
-- blog kecil. Semua UUID dan waktu dipaku agar output dapat dicocokkan.
-- Bagian ini dimaksudkan dapat dijalankan sendiri dari database kosong.

CREATE TABLE pengguna (
    id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    email         text NOT NULL,
    password_hash text NOT NULL,
    dibuat_pada   timestamptz NOT NULL,
    dihapus_pada  timestamptz,
    CONSTRAINT email_pengguna_unik UNIQUE (email)
);

CREATE TABLE artikel (
    id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    penulis_id  uuid NOT NULL REFERENCES pengguna (id),
    judul       text NOT NULL,
    isi         text NOT NULL,
    status      text NOT NULL DEFAULT 'draft',
    dibuat_pada timestamptz NOT NULL,
    diubah_pada timestamptz NOT NULL,
    dihapus_pada timestamptz,
    CONSTRAINT judul_tidak_kosong CHECK (judul <> ''),
    CONSTRAINT isi_tidak_kosong  CHECK (isi <> ''),
    CONSTRAINT status_sah CHECK (status IN ('draft', 'published'))
);

CREATE TABLE tag (
    id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    nama        text NOT NULL,
    dibuat_pada timestamptz NOT NULL,
    CONSTRAINT nama_tag_unik UNIQUE (nama),
    CONSTRAINT tag_selalu_lowercase CHECK (nama = lower(nama))
);

CREATE TABLE artikel_tag (
    artikel_id uuid NOT NULL REFERENCES artikel (id) ON DELETE CASCADE,
    tag_id     uuid NOT NULL REFERENCES tag (id) ON DELETE CASCADE,
    PRIMARY KEY (artikel_id, tag_id)
);

INSERT INTO pengguna (id, email, password_hash, dibuat_pada)
VALUES
    ('11111111-1111-4111-8111-111111111111',
     '[email protected]', 'sandi_hash_budi_placeholder',
     TIMESTAMPTZ '2026-04-01 08:00:00+07'),
    ('22222222-2222-4222-8222-222222222222',
     '[email protected]', 'sandi_hash_siti_placeholder',
     TIMESTAMPTZ '2026-04-02 09:30:00+07'),
    ('33333333-3333-4333-8333-333333333333',
     '[email protected]', 'sandi_hash_dewi_placeholder',
     TIMESTAMPTZ '2026-04-03 10:15:00+07');

INSERT INTO tag (id, nama, dibuat_pada)
VALUES
    ('10000000-0000-4000-8000-000000000001', 'postgresql',
     TIMESTAMPTZ '2026-04-01 08:05:00+07'),
    ('10000000-0000-4000-8000-000000000002', 'sql',
     TIMESTAMPTZ '2026-04-01 08:05:00+07'),
    ('10000000-0000-4000-8000-000000000003', 'database',
     TIMESTAMPTZ '2026-04-01 08:05:00+07'),
    ('10000000-0000-4000-8000-000000000004', 'backend',
     TIMESTAMPTZ '2026-04-01 08:05:00+07'),
    ('10000000-0000-4000-8000-000000000005', 'kinerja',
     TIMESTAMPTZ '2026-04-01 08:05:00+07');

INSERT INTO artikel (id, penulis_id, judul, isi, status,
                     dibuat_pada, diubah_pada, dihapus_pada)
VALUES
    ('20000000-0000-4000-8000-000000000001',
     '11111111-1111-4111-8111-111111111111',
     'Kenali UUID sebagai Primary Key',
     'Primary key UUID membuat identifier sulit ditebak dan aman diekspos di URL.',
     'published',
     TIMESTAMPTZ '2026-05-01 09:00:00+07',
     TIMESTAMPTZ '2026-05-01 09:00:00+07',
     NULL),
    ('20000000-0000-4000-8000-000000000002',
     '22222222-2222-4222-8222-222222222222',
     'Rancangan Skema untuk REST API',
     'Skema yang baik membuat validasi endpoint menjadi murah.',
     'published',
     TIMESTAMPTZ '2026-05-03 10:00:00+07',
     TIMESTAMPTZ '2026-05-03 10:00:00+07',
     NULL),
    ('20000000-0000-4000-8000-000000000003',
     '33333333-3333-4333-8333-333333333333',
     'Pagination Keyset vs OFFSET',
     'OFFSET membaca lalu membuang baris; keyset melompat lewat index komposit.',
     'published',
     TIMESTAMPTZ '2026-05-05 08:30:00+07',
     TIMESTAMPTZ '2026-05-06 16:45:00+07',
     NULL),
    ('20000000-0000-4000-8000-000000000004',
     '11111111-1111-4111-8111-111111111111',
     'Soft Delete dan Jejak Audit',
     'Kolom dihapus_pada yang NULL memisahkan baris hidup dari baris mati.',
     'draft',
     TIMESTAMPTZ '2026-05-06 11:20:00+07',
     TIMESTAMPTZ '2026-05-06 11:20:00+07',
     NULL),
    ('20000000-0000-4000-8000-000000000005',
     '22222222-2222-4222-8222-222222222222',
     'Validasi Data di Tingkat Database',
     'Constraint yang tertulis di skema bekerja untuk semua aplikasi.',
     'published',
     TIMESTAMPTZ '2026-05-07 14:00:00+07',
     TIMESTAMPTZ '2026-05-07 14:00:00+07',
     NULL),
    ('20000000-0000-4000-8000-000000000006',
     '33333333-3333-4333-8333-333333333333',
     'EXPLAIN sebagai Alat Diagnosis Query',
     'Bacalah rencana eksekusi sebelum menambah index demi index.',
     'draft',
     TIMESTAMPTZ '2026-05-09 07:45:00+07',
     TIMESTAMPTZ '2026-05-09 07:45:00+07',
     NULL),
    ('20000000-0000-4000-8000-000000000007',
     '11111111-1111-4111-8111-111111111111',
     'Transaksi Atomik untuk Endpoint',
     'INSERT artikel dan tag harus berjalan dalam satu transaksi.',
     'published',
     TIMESTAMPTZ '2026-05-10 13:10:00+07',
     TIMESTAMPTZ '2026-05-10 13:10:00+07',
     NULL),
    ('20000000-0000-4000-8000-000000000008',
     '22222222-2222-4222-8222-222222222222',
     'Index Parsial untuk Data Hidup',
     'Index parsial hanya menampung baris yang belum dihapus lembut.',
     'published',
     TIMESTAMPTZ '2026-05-12 09:50:00+07',
     TIMESTAMPTZ '2026-05-12 09:50:00+07',
     TIMESTAMPTZ '2026-05-20 10:00:00+07');

INSERT INTO artikel_tag (artikel_id, tag_id)
VALUES
    ('20000000-0000-4000-8000-000000000001',
     '10000000-0000-4000-8000-000000000001'),
    ('20000000-0000-4000-8000-000000000001',
     '10000000-0000-4000-8000-000000000003'),
    ('20000000-0000-4000-8000-000000000002',
     '10000000-0000-4000-8000-000000000003'),
    ('20000000-0000-4000-8000-000000000002',
     '10000000-0000-4000-8000-000000000004'),
    ('20000000-0000-4000-8000-000000000003',
     '10000000-0000-4000-8000-000000000002'),
    ('20000000-0000-4000-8000-000000000003',
     '10000000-0000-4000-8000-000000000005'),
    ('20000000-0000-4000-8000-000000000004',
     '10000000-0000-4000-8000-000000000003'),
    ('20000000-0000-4000-8000-000000000004',
     '10000000-0000-4000-8000-000000000004'),
    ('20000000-0000-4000-8000-000000000005',
     '10000000-0000-4000-8000-000000000001'),
    ('20000000-0000-4000-8000-000000000005',
     '10000000-0000-4000-8000-000000000002'),
    ('20000000-0000-4000-8000-000000000006',
     '10000000-0000-4000-8000-000000000005'),
    ('20000000-0000-4000-8000-000000000007',
     '10000000-0000-4000-8000-000000000002'),
    ('20000000-0000-4000-8000-000000000007',
     '10000000-0000-4000-8000-000000000004'),
    ('20000000-0000-4000-8000-000000000008',
     '10000000-0000-4000-8000-000000000001'),
    ('20000000-0000-4000-8000-000000000008',
     '10000000-0000-4000-8000-000000000005');

-- Kondisi awal bagian: 8 artikel (5 published, 2 draft, 1 dihapus lembut).
SELECT a.judul, p.email AS penulis, a.status, a.dibuat_pada,
       count(at2.tag_id) AS jumlah_tag
FROM artikel a
JOIN pengguna p ON p.id = a.penulis_id
LEFT JOIN artikel_tag at2 ON at2.artikel_id = a.id
GROUP BY a.judul, p.email, a.status, a.dibuat_pada
ORDER BY a.dibuat_pada;

ubah-artikel.sql

Unduh berkas
sql 
-- ubah-artikel.sql — PATCH /artikel/:id: pembaruan parsial.
-- Aplikasi mengirim satu query dengan parameter untuk semua kolom yang
-- boleh diubah; nilai NULL berarti "kolom tidak ada di badan request,
-- jangan sentuh". COALESCE menerjemahkan aturan itu.

-- PATCH hanya judul: kolom lain NULL, nilai lama dipertahankan.
UPDATE artikel SET
    judul       = COALESCE('Membaca Rencana Eksekusi EXPLAIN', judul),
    isi         = COALESCE(NULL, isi),
    status      = COALESCE(NULL, status),
    diubah_pada = TIMESTAMPTZ '2026-05-13 09:15:00+07'
WHERE id = '20000000-0000-4000-8000-000000000006'
  AND dihapus_pada IS NULL
RETURNING id, judul, status, diubah_pada;

-- PATCH hanya status: menerbitkan artikel tanpa menyentuh judul dan isi.
UPDATE artikel SET
    judul       = COALESCE(NULL, judul),
    isi         = COALESCE(NULL, isi),
    status      = COALESCE('published', status),
    diubah_pada = TIMESTAMPTZ '2026-05-13 10:00:00+07'
WHERE id = '20000000-0000-4000-8000-000000000004'
  AND dihapus_pada IS NULL
RETURNING id, judul, status, diubah_pada;