Debug Backend: Query PostgreSQL Gagal karena Asumsi Parser SQL

Masalah utamanya: aplikasi backend bisa terlihat sehat di lingkungan development, tetapi gagal di staging ketika sebuah query PostgreSQL yang valid justru ditolak oleh lapisan parser atau validator SQL buatan sendiri. Jika tim Anda memiliki middleware yang memeriksa, memodifikasi, atau membatasi query sebelum dikirim ke database, maka bug seperti ini bukan kasus langka.

Studi kasus ini membahas pola kegagalan yang praktis: query berjalan baik saat diuji langsung ke PostgreSQL, tetapi request API gagal karena parser internal membuat asumsi yang salah tentang bentuk SQL yang “boleh”. Ini penting karena kompatibilitas PostgreSQL tidak cukup diukur dari query-query sederhana. Proyek seperti pgrust menarik sebagai pengingat bahwa meniru perilaku PostgreSQL dengan benar itu sulit; salah satu arah yang menginspirasi adalah menargetkan kelulusan regression test PostgreSQL, bukan sekadar “bisa menjalankan query umum”.

Mengapa bug ini bisa lolos di dev tetapi pecah di staging?

Dalam banyak tim backend, ada lapisan tambahan di atas database, misalnya:

query validator untuk memblokir operasi tertentu,
rewriter untuk multi-tenant filtering,
auditing layer yang menambahkan komentar atau hint,
safe SQL parser untuk memastikan hanya SELECT yang boleh berjalan.

Masalahnya, parser SQL buatan sendiri sering dibangun dari asumsi sederhana seperti:

query selalu dimulai dengan SELECT,
alias hanya berupa identifier polos,
fungsi tidak akan dipakai di ORDER BY atau FILTER,
WITH dianggap terlalu kompleks lalu ditolak,
operator PostgreSQL diasumsikan sama dengan SQL generik.

Di development, bug ini bisa tersembunyi karena:

dataset kecil dan query lebih sederhana,
fitur tertentu belum aktif,
ORM menghasilkan SQL berbeda tergantung konfigurasi,
validator hanya diaktifkan di staging/production,
jalur kode yang memakai query kompleks belum terkena di dev.

Studi kasus: query valid ditolak parser internal

Gejala yang terlihat

Sebuah endpoint pelaporan gagal di staging dengan pesan seperti:

400 Bad Request
reason: unsupported SQL syntax near "FILTER"

Padahal ketika query yang sama dieksekusi langsung ke PostgreSQL, hasilnya normal.

Query pemicunya misalnya seperti ini:

SELECT
  account_id,
  count(*) FILTER (WHERE status = 'paid') AS paid_count,
  count(*) FILTER (WHERE status = 'failed') AS failed_count
FROM invoices
WHERE created_at >= $1
GROUP BY account_id
ORDER BY paid_count DESC;

Secara PostgreSQL, query di atas valid. Fitur FILTER pada agregasi memang didukung PostgreSQL dan sering dipakai karena lebih jelas daripada menumpuk CASE WHEN di dalam fungsi agregat.

Apa yang terjadi di aplikasi

Di backend, sebelum query dikirim ke database, ada validator buatan sendiri yang mencoba memastikan query hanya berupa SELECT aman dan tidak mengandung kata kunci terlarang. Implementasinya ternyata tidak memakai parser PostgreSQL yang sebenarnya, melainkan tokenisasi sederhana dan beberapa aturan manual.

Pseudocode yang mirip dengan bug nyata biasanya seperti ini:

function validateSql(sql):
    tokens = simpleTokenizer(sql)

    if tokens[0].upper() != "SELECT":
        reject("only SELECT allowed")

    for token in tokens:
        if token.upper() in ["INSERT", "UPDATE", "DELETE", "ALTER", "DROP"]:
            reject("dangerous statement")

    if "FILTER" in tokens:
        reject("unsupported SQL syntax")

    return true

Secara niat, validator ini ingin aman. Secara implementasi, ia justru memutus query PostgreSQL yang valid. Lebih buruk lagi, tim backend kemudian salah menyimpulkan bahwa “PostgreSQL staging berbeda”, padahal sumber masalah ada di lapisan aplikasi sendiri.

Root cause: asumsi parser SQL yang tidak setia pada PostgreSQL

Akar masalahnya bukan hanya token FILTER. Biasanya ada pola yang lebih dalam: tim menganggap SQL sebagai string yang cukup mudah dipecah dengan aturan manual, padahal PostgreSQL memiliki grammar luas, banyak ekspresi, operator khusus, dan konteks sintaks yang tidak bisa ditangani andal dengan regex atau tokenisasi dangkal.

Beberapa contoh bentuk SQL PostgreSQL yang sering mematahkan parser buatan sendiri:

WITH common table expressions,
FILTER (WHERE ...) pada agregat,
RETURNING,
cast seperti ::jsonb,
operator JSON seperti ->, ->>, @>,
array dan operator seperti = ANY(...),
quoted identifier dan string literal yang mirip keyword,
ILIKE, SIMILAR TO,
NULLS FIRST/LAST,
DISTINCT ON.

Di sinilah relevansi pelajaran dari proyek seperti pgrust. Ketika seseorang mencoba membangun implementasi yang kompatibel dengan PostgreSQL, target yang masuk akal bukan “cukup untuk query kita hari ini”, melainkan sedekat mungkin dengan perilaku PostgreSQL yang nyata. Fakta bahwa ada fokus pada regression test PostgreSQL menunjukkan betapa luas dan detail perilaku yang harus ditiru. Jika implementasi database saja perlu tingkat kesetiaan setinggi itu, maka parser SQL setengah jadi di layer aplikasi jelas berisiko tinggi.

Langkah reproduksi yang rapi

Supaya debugging tidak berubah menjadi debat asumsi, buat reproduksi sekecil mungkin.

1. Buktikan query valid di PostgreSQL

Jalankan langsung ke database staging atau salinan lokal yang setara:

PREPARE report_query(timestamp) AS
SELECT
  account_id,
  count(*) FILTER (WHERE status = 'paid') AS paid_count,
  count(*) FILTER (WHERE status = 'failed') AS failed_count
FROM invoices
WHERE created_at >= $1
GROUP BY account_id
ORDER BY paid_count DESC;

EXECUTE report_query('2024-01-01');

Jika ini lolos, maka masalah bukan di engine PostgreSQL.

2. Log SQL mentah sebelum dan sesudah validator

Sering kali ada dua versi query:

query yang dibuat ORM/query builder,
query yang sudah dimodifikasi middleware internal.

Tambahkan logging yang jelas:

logger.info("sql.before_validation", { sql, params })
validated = validateSql(sql)
logger.info("sql.after_validation", { allowed: validated })

Tujuannya bukan hanya melihat string SQL, tetapi juga tahu di titik mana query ditolak.

3. Matikan lapisan parser sementara

Jika arsitektur memungkinkan, jalankan request yang sama dengan validator dinonaktifkan di lingkungan uji internal. Jika query tiba-tiba berhasil, Anda sudah punya isolasi kuat bahwa sumber kegagalan ada di parser/validator, bukan di ORM atau PostgreSQL.

4. Buat unit test minimal dari query pemicu

Jangan debug dari endpoint penuh. Ambil satu string SQL dan satu fungsi validator. Misalnya:

test("valid PostgreSQL aggregate FILTER must be accepted", () => {
  const sql = `
    SELECT account_id,
           count(*) FILTER (WHERE status = 'paid') AS paid_count
    FROM invoices
    GROUP BY account_id
  `

  expect(validateSql(sql)).toBe(true)
})

Test seperti ini sangat efektif untuk mengunci akar masalah.

Teknik isolasi bug yang efektif

Bandingkan AST nyata vs tokenisasi internal

Jika sistem Anda mengklaim “mem-parse SQL”, tanyakan: apakah ia benar-benar menghasilkan struktur yang paham grammar, atau hanya memecah token lalu menerapkan aturan tekstual?

Gejala parser dangkal biasanya:

tidak membedakan konteks keyword sebagai nama alias, fungsi, atau bagian sintaks,
mudah rusak oleh tanda kurung bertingkat,
salah mengenali string literal,
menolak fitur PostgreSQL yang sah karena tidak ada di daftar putih.

Kurangi query sampai titik gagal minimum

Mulailah dari query lengkap, lalu sederhanakan sedikit demi sedikit:

hapus ORDER BY,
hapus WHERE,
sisakan satu agregat dengan FILTER,
uji versi tanpa alias,
uji versi dengan count(*) biasa.

Tujuannya menemukan token atau pola minimal yang memicu penolakan. Sering kali hasilnya mengejutkan: parser gagal bukan pada konsep besar query, melainkan pada satu fitur grammar spesifik.

Periksa perbedaan jalur dev dan staging

Checklist yang perlu dicek:

apakah validator aktif hanya di staging,
apakah konfigurasi feature flag berbeda,
apakah ORM menghasilkan SQL lain karena perbedaan driver/config,
apakah logging di dev menyensor atau mengubah query,
apakah staging memakai plugin query enforcement tambahan.

Perbaikan kode: jangan menebak grammar PostgreSQL

Opsi terbaik: hilangkan parser buatan sendiri jika tidak benar-benar perlu

Kalau validator hanya bertujuan membatasi operasi berbahaya, cara paling aman sering kali bukan mem-parse semua SQL secara manual, melainkan:

batasi jalur kode agar hanya memakai query builder tertentu,
gunakan kredensial database dengan hak akses minimum,
pisahkan koneksi read-only dan read-write,
batasi statement di level role, schema, atau transaction policy.

Ini lebih kuat daripada mencoba menebak niat query dari teks mentahnya.

Jika parser tetap diperlukan, gunakan pendekatan yang lebih sempit dan eksplisit

Misalnya, jika tujuan Anda hanya memastikan endpoint analitik tidak menjalankan mutasi, jangan buat parser SQL generik. Definisikan kontrak yang lebih kecil:

hanya query yang dibangun dari modul internal tertentu,
hanya AST dari query builder, bukan string SQL bebas,
atau validasi berbasis jenis operasi sebelum SQL di-render.

Contoh pergeseran pendekatan:

// buruk: memeriksa string SQL final
function allowReadOnly(sqlText) {
  return naiveSqlValidator(sqlText)
}

// lebih baik: memeriksa representasi operasi sebelum dirender
function allowReadOnly(queryObject) {
  return queryObject.type === "select"
      && queryObject.ctes.every(cte => cte.type === "select")
}

Jika Anda memang harus menerima SQL mentah, gunakan parser yang benar-benar memahami dialect PostgreSQL atau minimal jangan menolak sintaks valid hanya karena tidak dikenal. Dalam konteks keamanan, fail-closed memang terdengar aman, tetapi jika implementasi grammar tidak lengkap, Anda akan menolak banyak query sah dan menciptakan gangguan operasional. Trade-off ini harus disadari.

Patch minimum untuk kasus ini

Dalam studi kasus kita, perbaikan tercepat adalah menghapus aturan yang menganggap FILTER sebagai sintaks terlarang, lalu menambah test regresi. Tetapi patch minimum saja belum cukup jika arsitektur dasarnya tetap rapuh.

Contoh perbaikan bertahap:

function validateSql(sql):
    tokens = betterTokenizer(sql)

    statementType = detectTopLevelStatement(tokens)
    if statementType != "SELECT":
        reject("only SELECT allowed")

    if containsTopLevelMutation(tokens):
        reject("dangerous statement")

    // jangan blacklist syntax PostgreSQL valid hanya karena belum dipahami
    return true

Ini masih belum sempurna, tetapi setidaknya mengurangi penolakan palsu terhadap fitur PostgreSQL yang sah.

Pengujian regresi yang seharusnya ditambahkan

Setelah bug diperbaiki, jangan berhenti di satu kasus. Tambahkan suite regresi yang merepresentasikan grammar PostgreSQL yang sering dipakai aplikasi Anda.

Contoh test yang layak ditambahkan

cases = [
  "SELECT count(*) FILTER (WHERE status = 'paid') FROM invoices",
  "WITH t AS (SELECT 1) SELECT * FROM t",
  "SELECT payload->>'type' FROM events",
  "SELECT * FROM users WHERE id = ANY($1)",
  "SELECT created_at::date FROM orders",
  "SELECT DISTINCT ON (account_id) account_id, created_at FROM invoices ORDER BY account_id, created_at DESC"
]

for sql in cases:
    assert validateSql(sql) == true

Jika sistem Anda melakukan rewrite, tambahkan juga test integrasi:

query masuk,
query divalidasi atau diubah,
query hasil akhir dieksekusi pada PostgreSQL sungguhan,
hasilnya diverifikasi.

Ini penting karena parser bisa lolos di level unit test tetapi tetap menghasilkan SQL rusak setelah tahap rewrite.

Kenapa regression test sangat penting

Bug parser SQL jarang berhenti di satu token. Hari ini FILTER, besok WITH, lusa operator JSON. Tanpa kumpulan test regresi, tim akan terus memadamkan kebakaran per kasus. Inspirasi dari proyek yang mengejar kompatibilitas PostgreSQL melalui test suite adalah pelajaran penting: kesetiaan perilaku dibangun dengan test yang luas dan berulang, bukan kepercayaan diri terhadap parser manual.

Checklist pencegahan untuk tim backend

Jangan parse SQL dengan regex kecuali kasusnya sangat sempit dan terdokumentasi jelas.
Validasi sedini mungkin di level abstraksi yang lebih tinggi, misalnya AST query builder, bukan string SQL final.
Gunakan pembatasan hak akses database sebagai kontrol utama, bukan validator string sebagai satu-satunya pertahanan.
Pastikan dev dan staging memiliki jalur eksekusi yang serupa, terutama feature flag dan middleware query.
Log query sebelum dan sesudah transformasi untuk mempercepat isolasi bug.
Simpan contoh query produksi yang pernah gagal sebagai bagian dari test suite regresi.
Jangan blacklist fitur PostgreSQL valid hanya karena belum dipahami parser internal.
Uji query terhadap PostgreSQL nyata, bukan hanya mock atau asumsi tokenizer.

Pelajaran utama

Kasus query PostgreSQL gagal karena asumsi parser SQL biasanya bukan masalah database, melainkan masalah kesetiaan lapisan aplikasi terhadap grammar dan perilaku PostgreSQL. Saat aplikasi lolos di dev tetapi gagal di staging, langkah paling berguna adalah membuktikan apakah query valid di PostgreSQL, lalu mengisolasi seluruh lapisan yang menyentuh SQL sebelum eksekusi.

Jika tim backend membangun validator atau rewriter SQL sendiri, anggap itu sebagai komponen berisiko tinggi. PostgreSQL punya sintaks kaya dan terus dipakai secara kreatif oleh ORM, query builder, dan engineer. Pelajarannya sederhana: kalau tidak benar-benar perlu, jangan menebak grammar SQL. Kalau memang perlu, ukur keberhasilan dengan kompatibilitas yang bisa diuji, bukan dengan asumsi bahwa beberapa query sederhana sudah cukup mewakili PostgreSQL.

Catatan praktis: saat menemukan query valid yang ditolak parser internal, simpan query mentahnya, tambahkan test unit validator, test integrasi ke PostgreSQL nyata, dan audit apakah kontrol keamanan sebenarnya lebih tepat dipindahkan ke level privilege database atau abstraksi query yang lebih tinggi.

Quick Search

shopping_cart Keranjang