Aplikasi web modern jarang berbicara langsung dengan penggunanya lewat halaman — mereka berbicara lewat REST API: deretan endpoint seperti GET /artikel dan POST /pendaftaran yang dipanggil aplikasi mobile, situs front-end, dan layanan pihak ketiga. Di balik setiap endpoint yang responsnya cepat dan konsisten ada satu database yang dirancang dengan sadar untuk pola akses itu. Pada proyek terakhir ini kita membangun database yang menopang REST API untuk platform blog: skema yang aman diekspos ke dunia luar, query yang pas untuk tiap endpoint, transaksi yang menjaga operasi multi-tabel tetap utuh, dan index yang dibenarkan bukti EXPLAIN — bukan tebakan.

Yang Akan Kita Bangun #

Blog platform kecil dengan tiga resource — pengguna, artikel, dan tag — tapi dengan keputusan desain yang sama dengan API produksi skala besar:

  • UUID sebagai primary key di seluruh tabel: identifier tidak berurutan sehingga tidak bisa dienumerasi dari URL (/artikel/1, /artikel/2, …), aman dibuat di banyak server sekaligus, dan langsung diisi gen_random_uuid().
  • Kolom audit dibuat_pada dan diubah_pada bertipe timestamptz — bahan header respons, deteksi perubahan, dan konkurensi optimis.
  • Soft delete lewat kolom dihapus_pada yang boleh NULL: artikel bisa “di-restore”, sejarah tetap teraudit — dengan perdebatan versus penghapusan permanen ala GDPR dibahas terbuka.
  • Kata sandi tidak pernah disimpan apa adanya — hanya password_hash dari aplikasi (bcrypt/argon2); contoh memakai placeholder yang jelas.
  • Email unik hanya di antara akun aktif lewat index unik parsial: akun yang dihapus lembut membebaskan emailnya untuk didaftarkan ulang.
  • Endpoint list memakai pagination keyset (dibuat_pada, id), endpoint detail merangkai respons JSON lewat jsonb_build_object dan jsonb_agg, PATCH memperbarui parsial lewat COALESCE, DELETE adalah UPDATE penanda waktu.
  • Endpoint tulis dibungkus transaksi (artikel plus tag atomik), pelanggaran constraint dipetakan ke status HTTP yang tepat lewat SQLSTATE, dan pembaruan bentrok dideteksi lewat konkurensi optimis pada diubah_pada.
  • Optimasi ditutup dengan audit index per endpoint, bukti EXPLAIN sebelum-sesudah, deteksi pola N+1 lewat pg_stat_statements, dan rekap connection pooling.

Data contoh deterministik: UUID dan waktu dipaku (misalnya TIMESTAMPTZ '2026-05-01 09:00:00+07') supaya setiap output dapat Anda cocokkan persis dengan eksekusi sendiri — sementara Bagian 4 menambahkan 1.000 artikel buatan generate_series agar rencana eksekusi punya sesuatu yang layak dioptimasi.

Skema Database #

Empat tabel saling mengunci seperti berikut:

Notasi ||--o{ dibaca “satu ke nol-atau-banyak”: satu pengguna menulis banyak artikel; satu artikel boleh punya banyak tag dan satu tag menandai banyak artikel — relasi banyak-ke-banyak yang dijembatani tabel junction artikel_tag dengan primary key komposit (artikel_id, tag_id) sehingga tag yang sama tidak pernah menempel dua kali pada satu artikel. Kolom dihapus_pada yang hadir di pengguna dan artikel adalah mesin soft delete: NULL berarti hidup, terisi berarti mati secara administratif.

Prasyarat #

Proyek ini menyusun ulang materi yang sudah dipelajari, bukan memperkenalkan fitur baru. Pastikan Anda nyaman dengan chapter berikut:

Tidak ada extension khusus yang wajib dipasang sejak awal; pg_stat_statements (sudah dimuat server) muncul saat dibutuhkan di Bagian 4. Cukup psql dan PostgreSQL 17 yang terpasang sejak Instalasi PostgreSQL .

Peta Bagian #

Proyek dikerjakan dalam empat bagian berurutan. Setiap bagian menghasilkan database yang benar-benar bekerja pada tahapnya:

BagianFokus
Bagian 1: Perancangan SkemaDari daftar endpoint ke empat tabel: UUID, kolom audit, soft delete, hash kata sandi, dan uji constraint
Bagian 2: Query untuk APISatu query per endpoint: list dengan pagination keyset, detail JSON dengan tag, INSERT RETURNING, PATCH dengan COALESCE, filter dan pencarian
Bagian 3: Transaksi dan Validasi DataPOST artikel+tag atomik, pemetaan SQLSTATE ke status HTTP, konkurensi optimis, dan soft delete yang membebaskan email
Bagian 4: Optimasi Query APIAudit index per endpoint, EXPLAIN sebelum-sesudah, pola N+1 dan hitungannya, serta pg_stat_statements sebagai radar

Setiap bagian mandiri: berkas SQL-nya membangun ulang tabel yang dibutuhkan di awal, sehingga Anda bisa masuk dari bagian mana pun tanpa membawa bekas eksekusi bagian sebelumnya.

Cara Mengikuti Proyek #

Kerjakan tiap bagian sambil mengetik ulang atau menyalin berkas SQL-nya, lalu jalankan dengan psql seperti biasa:

bash 
psql -X -f bagian/perancangan-skema/buat-tabel.sql -d api_blog

Aturan mainnya sama dengan seluruh Course: setiap blok kode pada bagian-bagian berikut adalah berkas nyata yang dieksekusi sungguhan, dan blok Output yang Dihasilkan di bawahnya adalah hasil eksekusi tersebut, bukan tulisan tangan. Satu pengecualian diumumkan terbuka: rencana eksekusi EXPLAIN pada Bagian 4 ditandai manual karena angka biayanya berbeda antar mesin — outputnya tetap hasil eksekusi nyata, dengan catatan agar Anda tidak membandingkan angka cost secara harfiah.

Selain berkas per bagian, halaman overview ini juga membawa artefak lengkap hasil akhir proyek pada bagian Berkas Sumber di bawah halaman ini: database/skema.sql (skema final dengan index unik parsial dan index beban kerja), database/isi-data.sql (data contoh blog), queries/ (empat query endpoint siap pakai: daftar, detail, cari, statistik tag), serta tests/ (dua berkas uji LULUS/GAGAL). Setelah menyelesaikan keempat bagian, terapkan keduanya ke database kosong, jalankan berkas uji, lalu bandingkan hasilnya.

Semua keputusan penting API lahir di skema — dan skema itulah yang kita rangkai lebih dulu. Mari kita mulai Bagian 1.

Referensi #

# Bagian 1: Perancangan Skema

Rancang skema untuk resource API dengan UUID, timestamp audit, dan soft delete.

Buka → # Bagian 2: Query untuk API

Susun query list-detail dengan pagination keyset dan filtering per endpoint.

Buka → # Bagian 3: Transaksi dan Validasi Data

Bungkus operasi endpoint dalam transaksi dengan validasi dan error mapping.

Buka → # Bagian 4: Optimasi Query API

Optimalkan query endpoint dengan index yang tepat dan EXPLAIN sebagai bukti.

Buka →

Berkas Sumber

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

database/isi-data.sql

Unduh berkas
sql 
-- isi-data.sql — data contoh blog: 3 pengguna, 5 tag, 8 artikel
-- (5 published, 2 draft, 1 dihapus lembut), 15 hubungan artikel-tag.
-- Jalankan setelah database/skema.sql:
--   psql -X -f database/skema.sql -f database/isi-data.sql
-- Semua UUID dan waktu tetap agar output query dapat dicocokkan.

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;

database/skema.sql

Unduh berkas
sql 
-- skema.sql — skema lengkap database di balik REST API blog.
-- Terapkan pada database kosong:
--   psql -X -f database/skema.sql
-- Isi data contoh dengan database/isi-data.sql setelahnya.
-- Skema ini adalah hasil akhir proyek: empat tabel, constraint bisnis,
-- index unik parsial untuk email aktif, index beban kerja endpoint,
-- dan dokumentasi kolom lewat COMMENT ON.

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
);

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)
);

-- Email unik hanya di antara pengguna aktif: email akun yang dihapus
-- lembut boleh didaftarkan ulang (dibahas pada Bagian 3).
CREATE UNIQUE INDEX pengguna_email_aktif
    ON pengguna (email)
    WHERE dihapus_pada IS NULL;

-- Index beban kerja endpoint (dibahas pada Bagian 4).
-- Feed terbit: meniru persis query GET /artikel — parsial dan terurut.
CREATE INDEX idx_artikel_umpan
    ON artikel (dibuat_pada DESC, id DESC)
    WHERE status = 'published' AND dihapus_pada IS NULL;

-- GET /pengguna/:id/artikel — foreign key tidak membuat index otomatis.
CREATE INDEX idx_artikel_penulis ON artikel (penulis_id);

-- GET /artikel?tag=... — arah akses "dari tag ke daftar artikel".
CREATE INDEX idx_artikel_tag_tag ON artikel_tag (tag_id, artikel_id);

COMMENT ON TABLE pengguna IS 'Akun penulis; login memakai email, kata sandi hanya tersimpan sebagai hash.';
COMMENT ON COLUMN pengguna.dihapus_pada IS 'NULL berarti akun aktif; terisi berarti akun dihapus lembut (soft delete).';
COMMENT ON TABLE artikel IS 'Satu baris = satu artikel blog; penulis tidak dapat diubah setelah dibuat.';
COMMENT ON COLUMN artikel.diubah_pada IS 'Waktu revisi terakhir; dipakai sebagai penanda versi untuk konkurensi optimis.';
COMMENT ON TABLE tag IS 'Label artikel, selalu lowercase dan unik — bentuk yang aman dipakai di URL.';
COMMENT ON TABLE artikel_tag IS 'Tabel junction relasi banyak-ke-banyak artikel dan tag.';

queries/cari-artikel.sql

Unduh berkas
sql 
-- cari-artikel.sql — query endpoint siap pakai hasil akhir proyek.
-- Jalankan pada database yang memuat database/skema.sql
-- dan database/isi-data.sql.
-- 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;

queries/daftar-artikel.sql

Unduh berkas
sql 
-- daftar-artikel.sql — query endpoint siap pakai hasil akhir proyek.
-- Jalankan pada database yang memuat database/skema.sql
-- dan database/isi-data.sql.
-- 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;

queries/detail-artikel.sql

Unduh berkas
sql 
-- detail-artikel.sql — query endpoint siap pakai hasil akhir proyek.
-- Jalankan pada database yang memuat database/skema.sql
-- dan database/isi-data.sql.
-- 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;

queries/statistik-tag.sql

Unduh berkas
sql 
-- statistik-tag.sql — GET /tag: daftar tag beserta jumlah artikel terbit
-- yang memakainya. Kolom terbit hanya menghitung artikel published yang
-- belum dihapus lembut — angka yang benar-benar terlihat pengunjung API.
-- Jalankan pada database yang memuat database/skema.sql
-- dan database/isi-data.sql.

SELECT t.nama,
       count(at.artikel_id) FILTER (WHERE a.status = 'published'
                                    AND a.dihapus_pada IS NULL)
           AS artikel_terbit,
       count(at.artikel_id) AS total_hubungan
FROM tag t
LEFT JOIN artikel_tag at ON at.tag_id = t.id
LEFT JOIN artikel a ON a.id = at.artikel_id
GROUP BY t.nama
ORDER BY t.nama;

tests/uji-endpoint.sql

Unduh berkas
sql 
-- uji-endpoint.sql — uji perilaku endpoint pada database terisi.
-- Jalankan pada database yang memuat database/skema.sql dan database/isi-data.sql:
--   psql -X -f database/skema.sql -f database/isi-data.sql -f tests/uji-endpoint.sql
-- Lulus bila setiap baris kolom status bernilai LULUS.
-- Uji ini mengubah data (soft delete, insert); jalankan pada salinan,
-- atau ulangi dari skema.sql + isi-data.sql untuk pengujian berikutnya.

CREATE TEMP TABLE hasil_uji (
    nomor  integer,
    kasus  text,
    status text
);

-- Kasus 1: GET /artikel — artikel yang dihapus lembut tidak tampil:
-- harus tepat 5 artikel terbit.
INSERT INTO hasil_uji
VALUES (1, 'daftar artikel terbit berjumlah tepat 5',
        CASE WHEN (SELECT count(*) FROM artikel
                   WHERE status = 'published' AND dihapus_pada IS NULL) = 5
             THEN 'LULUS' ELSE 'GAGAL' END);

-- Kasus 2: GET /artikel/:id untuk artikel yang dihapus lembut — 0 baris
-- (semantik 404 Not Found).
INSERT INTO hasil_uji
VALUES (2, 'detail artikel terhapus menghasilkan 0 baris',
        CASE WHEN (SELECT count(*) FROM artikel
                   WHERE id = '20000000-0000-4000-8000-000000000008'
                     AND dihapus_pada IS NULL) = 0
             THEN 'LULUS' ELSE 'GAGAL' END);

-- Kasus 3: PATCH dengan salinan diubah_pada usang — 0 baris ter-update
-- (semantik 409 Conflict, konkurensi optimis).
UPDATE artikel
SET judul = 'Pagination Keyset vs OFFSET (versi terlambat)',
    diubah_pada = TIMESTAMPTZ '2026-06-01 09:00:00+07'
WHERE id = '20000000-0000-4000-8000-000000000003'
  AND diubah_pada = TIMESTAMPTZ '2026-05-05 08:30:00+07'
  AND dihapus_pada IS NULL;

INSERT INTO hasil_uji
VALUES (3, 'PATCH dengan diubah_pada usang meng-update 0 baris',
        CASE WHEN NOT EXISTS (SELECT 1 FROM artikel
                              WHERE id = '20000000-0000-4000-8000-000000000003'
                                AND judul = 'Pagination Keyset vs OFFSET (versi terlambat)')
             THEN 'LULUS' ELSE 'GAGAL' END);

-- Kasus 4: POST /artikel dengan tag — satu transaksi atomik; artikel
-- dan kedua tag harus utuh bersama.
BEGIN;
INSERT INTO artikel (id, penulis_id, judul, isi, status,
                     dibuat_pada, diubah_pada)
VALUES ('90000000-0000-4000-8000-000000000010',
        '33333333-3333-4333-8333-333333333333',
        'Artikel uji transaksi',
        'Artikel ini dibuat berkas uji endpoint.',
        'published',
        TIMESTAMPTZ '2026-06-01 10:00:00+07',
        TIMESTAMPTZ '2026-06-01 10:00:00+07');

INSERT INTO artikel_tag (artikel_id, tag_id)
VALUES ('90000000-0000-4000-8000-000000000010',
        '10000000-0000-4000-8000-000000000002'),
       ('90000000-0000-4000-8000-000000000010',
        '10000000-0000-4000-8000-000000000005');
COMMIT;

INSERT INTO hasil_uji
VALUES (4, 'POST artikel dengan tag tersimpan utuh (2 tag)',
        CASE WHEN (SELECT count(*) FROM artikel_tag at2
                   WHERE at2.artikel_id = '90000000-0000-4000-8000-000000000010') = 2
             THEN 'LULUS' ELSE 'GAGAL' END);

-- Kasus 5: DELETE /pengguna/:id (soft delete) lalu POST /pendaftaran
-- dengan email yang sama — diterima kembali karena index unik parsial.
UPDATE pengguna
SET dihapus_pada = TIMESTAMPTZ '2026-06-01 11:00:00+07'
WHERE id = '22222222-2222-4222-8222-222222222222'
  AND dihapus_pada IS NULL;

INSERT INTO pengguna (id, email, password_hash, dibuat_pada)
VALUES ('90000000-0000-4000-8000-000000000020',
        '[email protected]', 'sandi_hash_uji_placeholder',
        TIMESTAMPTZ '2026-06-01 11:05:00+07');

INSERT INTO hasil_uji
VALUES (5, 'email akun terhapus dapat didaftarkan ulang',
        CASE WHEN EXISTS (SELECT 1 FROM pengguna
                          WHERE email = '[email protected]'
                            AND id = '90000000-0000-4000-8000-000000000020')
             THEN 'LULUS' ELSE 'GAGAL' END);

-- Kasus 6: POST /login untuk akun yang dihapus lembut — 0 baris.
INSERT INTO hasil_uji
VALUES (6, 'login akun terhapus menghasilkan 0 baris',
        CASE WHEN (SELECT count(*) FROM pengguna
                   WHERE email = '[email protected]' AND dihapus_pada IS NULL) = 1
             THEN 'LULUS' ELSE 'GAGAL' END);
-- Catatan kasus 6: tepat satu akun aktif bernama [email protected] —
-- yang baru; akun lama sudah nonaktif sehingga tidak bisa masuk.

SELECT nomor, kasus, status
FROM hasil_uji
ORDER BY nomor;

tests/uji-integritas.sql

Unduh berkas
sql 
-- uji-integritas.sql — uji constraint bisnis pada database terisi.
-- Jalankan pada database yang memuat database/skema.sql dan database/isi-data.sql:
--   psql -X -f database/skema.sql -f database/isi-data.sql -f tests/uji-integritas.sql
-- Lulus bila setiap baris kolom status bernilai LULUS.
-- Baris uji memakai UUID awalan 90000000 agar mudah dideteksi bila bocor.

CREATE TEMP TABLE hasil_uji (
    nomor      integer,
    kasus      text,
    diharapkan text,
    aktual     text,
    status     text
);

DO $$
DECLARE
    v_state text;
BEGIN
    -- Kasus 1: email pengguna duplikat pada akun aktif (index unik parsial).
    BEGIN
        INSERT INTO pengguna (id, email, password_hash, dibuat_pada)
        VALUES ('90000000-0000-4000-8000-000000000001',
                '[email protected]', 'sandi_hash_uji_placeholder',
                TIMESTAMPTZ '2026-06-01 08:00:00+07');
        INSERT INTO hasil_uji VALUES (1, 'email pengguna duplikat', '23505', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (1, 'email pengguna duplikat', '23505', v_state,
            CASE WHEN v_state = '23505' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 2: nama tag duplikat.
    BEGIN
        INSERT INTO tag (id, nama, dibuat_pada)
        VALUES ('90000000-0000-4000-8000-000000000002', 'postgresql',
                TIMESTAMPTZ '2026-06-01 08:05:00+07');
        INSERT INTO hasil_uji VALUES (2, 'nama tag duplikat', '23505', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (2, 'nama tag duplikat', '23505', v_state,
            CASE WHEN v_state = '23505' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 3: pasangan artikel-tag yang sama dua kali (primary key junction).
    BEGIN
        INSERT INTO artikel_tag (artikel_id, tag_id)
        VALUES ('20000000-0000-4000-8000-000000000001',
                '10000000-0000-4000-8000-000000000001');
        INSERT INTO hasil_uji VALUES (3, 'pasangan artikel-tag ganda', '23505', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (3, 'pasangan artikel-tag ganda', '23505', v_state,
            CASE WHEN v_state = '23505' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 4: penulis tidak dikenal pada POST /artikel.
    BEGIN
        INSERT INTO artikel (id, penulis_id, judul, isi, status,
                             dibuat_pada, diubah_pada)
        VALUES ('90000000-0000-4000-8000-000000000003',
                '99999999-9999-4999-8999-999999999999',
                'Penulis hantu', 'Penulis tidak ada di tabel pengguna.', 'draft',
                TIMESTAMPTZ '2026-06-01 08:10:00+07',
                TIMESTAMPTZ '2026-06-01 08:10:00+07');
        INSERT INTO hasil_uji VALUES (4, 'penulis tidak dikenal', '23503', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (4, 'penulis tidak dikenal', '23503', v_state,
            CASE WHEN v_state = '23503' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 5: status di luar draft/published.
    BEGIN
        INSERT INTO artikel (id, penulis_id, judul, isi, status,
                             dibuat_pada, diubah_pada)
        VALUES ('90000000-0000-4000-8000-000000000004',
                '11111111-1111-4111-8111-111111111111',
                'Status karatan', 'Uji status archived.', 'archived',
                TIMESTAMPTZ '2026-06-01 08:15:00+07',
                TIMESTAMPTZ '2026-06-01 08:15:00+07');
        INSERT INTO hasil_uji VALUES (5, 'status di luar draft/published', '23514', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (5, 'status di luar draft/published', '23514', v_state,
            CASE WHEN v_state = '23514' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 6: judul string kosong.
    BEGIN
        INSERT INTO artikel (id, penulis_id, judul, isi, status,
                             dibuat_pada, diubah_pada)
        VALUES ('90000000-0000-4000-8000-000000000005',
                '11111111-1111-4111-8111-111111111111',
                '', 'Uji judul kosong.', 'draft',
                TIMESTAMPTZ '2026-06-01 08:20:00+07',
                TIMESTAMPTZ '2026-06-01 08:20:00+07');
        INSERT INTO hasil_uji VALUES (6, 'judul string kosong', '23514', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (6, 'judul string kosong', '23514', v_state,
            CASE WHEN v_state = '23514' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 7: tag dengan huruf besar.
    BEGIN
        INSERT INTO tag (id, nama, dibuat_pada)
        VALUES ('90000000-0000-4000-8000-000000000006', 'PostgreSQL',
                TIMESTAMPTZ '2026-06-01 08:25:00+07');
        INSERT INTO hasil_uji VALUES (7, 'tag dengan huruf besar', '23514', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (7, 'tag dengan huruf besar', '23514', v_state,
            CASE WHEN v_state = '23514' THEN 'LULUS' ELSE 'GAGAL' END);
    END;
END $$;

SELECT nomor, kasus, diharapkan, aktual, status
FROM hasil_uji
ORDER BY nomor;

-- Pemeriksaan bocor: tidak ada baris uji yang lolos masuk.
SELECT count(*) AS baris_uji_bocor
FROM artikel
WHERE id::text LIKE '90000000-%'
UNION ALL
SELECT count(*)
FROM pengguna
WHERE id::text LIKE '90000000-%'
UNION ALL
SELECT count(*)
FROM tag
WHERE id::text LIKE '90000000-%';