Aplikasi nyaris tidak pernah menjalankan query yang benar-benar baru: halaman daftar pelanggan mengirim SELECT ... WHERE email = ... yang sama ribuan kali sehari, dan setiap pengiriman itu membawa teks SQL lengkap untuk di-parse ulang oleh server. Parsing dan perencanaan ulang yang terus berulang adalah pekerjaan sia-sia, dan pada saat yang sama teks query yang dirakit manual membuka pintu SQL injection yang sudah kita bedah di chapter SQL Injection . Prepared statement menjawab dua masalah itu sekaligus: teks query dikirim dan di-parse satu kali, lalu dieksekusi berulang hanya dengan mengirim parameter. Chapter ini membedah bagaimana mekanisme tersebut dikelola dari sisi aplikasi — mulai dari rekap PREPARE di sisi server, perilaku otomatis psycopg 3, sampai prepared statement bernama di node-postgres.

Dua Sisi Prepared Statement #

Prepared statement bisa hidup di dua tempat yang berbeda, dan membedakan keduanya mencegah kebingungan saat membaca dokumentasi driver:

AspekSisi server (PREPARE SQL)Sisi driver (protokol extended)
Siapa mengelolaAnda menulis PREPARE/EXECUTE/DEALLOCATEDriver mengirim Parse/Bind secara otomatis
Masa hidupSampai DEALLOCATE atau sesi berakhirDitentukan driver; umumnya selama koneksi hidup
Bentuk parameter$1, $2 dalam definisi statement%s (psycopg) atau $1 (pg), dikirim terpisah
Kapan dipakaiSkrip manual, sesi psql panjangSelalu, dari kode aplikasi

Di sisi server, PREPARE adalah perintah SQL biasa yang dibahas tuntas di chapter Prepared Statement pada section User Management dan Keamanan. Di sisi aplikasi, Anda hampir tidak pernah menulis PREPARE secara manual — driver yang melakukannya lewat protokol extended query di balik layar. Yang Anda tulis hanyalah pola panggilan: teks query yang sama, parameter yang berbeda.

Rekap Cepat: PREPARE di Sisi Server #

Sebelum masuk ke sisi driver, kita segarkan ingatan dengan bentuk server-side-nya. File berikut membuat tabel pelanggan Toko Maju Jaya, menyiapkan statement pencari pelanggan berdasarkan email, mengeksekusinya dua kali, lalu membuktikan bahwa statement bernama tercatat di katalog sesi pg_prepared_statements sebelum akhirnya dijatuhkan dengan DEALLOCATE:

sql rekap-server-side.sql 
-- Rekap cepat prepared statement di sisi server (SQL murni).
-- Pembahasan penuh siklus PREPARE/EXECUTE/DEALLOCATE ada di chapter
-- Prepared Statement pada section User Management dan Keamanan.
CREATE TABLE pelanggan (
    id    integer PRIMARY KEY,
    nama  text NOT NULL,
    email text NOT NULL UNIQUE
);

INSERT INTO pelanggan (id, nama, email) VALUES
    (1, 'Budi', '[email protected]'),
    (2, 'Siti', '[email protected]'),
    (3, 'Ani',  '[email protected]');

-- PREPARE: parse dan rencana eksekusi dikerjakan SEKALI per sesi.
PREPARE cari_pelanggan(text) AS
    SELECT id, nama FROM pelanggan WHERE email = $1;

-- EXECUTE hanya mengirim nilai parameter, bukan teks SQL lengkap.
EXECUTE cari_pelanggan('[email protected]');

EXECUTE cari_pelanggan('[email protected]');

-- Statement bernama tercatat di katalog sesi:
SELECT name, statement FROM pg_prepared_statements ORDER BY name;

-- DEALLOCATE membuang statement dari sesi.
DEALLOCATE cari_pelanggan;

SELECT count(*) AS sisa_statement
FROM pg_prepared_statements
WHERE name = 'cari_pelanggan';

Output yang Dihasilkan:

text 
CREATE TABLE
INSERT 0 3
PREPARE
 id | nama 
----+------
  1 | Budi
(1 row)

 id | nama 
----+------
  2 | Siti
(1 row)

      name      |                      statement                       
----------------+------------------------------------------------------
 cari_pelanggan | PREPARE cari_pelanggan(text) AS                     +
                |     SELECT id, nama FROM pelanggan WHERE email = $1;
(1 row)

DEALLOCATE
 sisa_statement 
----------------
              0
(1 row)

Tiga pengamatan penting dari hasil eksekusi di atas:

  1. Dua kali EXECUTE, satu kali parse — kedua pemanggilan mengembalikan Budi dan Siti tanpa mengirim ulang teks SELECT lengkap; hanya nilai parameter yang dikirim ke statement yang sudah jadi.
  2. Kolom statement memuat definisi lengkap — katalog sesi menampilkan PREPARE cari_pelanggan(text) AS SELECT ..., bukan hanya isi query-nya; ini wajar karena katalog menyimpan definisi statement, bukan hasil parse-nya.
  3. DEALLOCATE menutup siklus — setelah statement dijatuhkan, sisa_statement bernilai 0: nama cari_pelanggan bisa dipakai ulang untuk definisi baru dalam sesi yang sama.

psycopg Menyiapkan Statement Secara Otomatis #

Di psycopg 3 Anda tidak memanggil fungsi prepare() sama sekali. Driver menghitung sendiri berapa kali sebuah teks query dijalankan; setelah jumlah itu melewati prepare_threshold (bawaan 5), eksekusi berikutnya dikirim sebagai prepared statement bernama di sisi server. Skrip berikut membuktikan perilaku tersebut dengan mengintip pg_prepared_statements setelah setiap eksekusi:

python otomatis-psycopg.py 
# Prepared statement otomatis di psycopg 3.
# Koneksi membaca variabel lingkungan PGHOST/PGPORT/PGUSER/PGDATABASE,
# sama seperti pola libpq yang dipakai psql.
import psycopg

conn = psycopg.connect()
cur = conn.cursor()

print("prepare_threshold bawaan:", conn.prepare_threshold)

EMAIL = [
    "[email protected]",
    "[email protected]",
    "[email protected]",
    "[email protected]",
    "[email protected]",
    "[email protected]",
    "[email protected]",
]

for i, email in enumerate(EMAIL, start=1):
    # Pola panggilan psycopg: teks query yang sama berulang kali,
    # parameter yang berubah. Driver yang memutuskan kapan query
    # cukup sering untuk disiapkan sebagai prepared statement.
    cur.execute("SELECT id, nama FROM pelanggan WHERE email = %s", (email,))
    id_pelanggan, nama = cur.fetchone()
    # Query pemantau diberi nomor unik lewat komentar supaya query
    # pemantau itu sendiri tidak ikut terhitung sebagai kandidat.
    jumlah = cur.execute(
        f"SELECT count(*) FROM pg_prepared_statements -- pantau {i}"
    ).fetchone()[0]
    print(f"eksekusi ke-{i}: {email} -> id {id_pelanggan} ({nama}), "
          f"statement di server = {jumlah}")

cur.execute("SELECT name, statement FROM pg_prepared_statements ORDER BY name")
for nama_statement, teks in cur.fetchall():
    print("statement di server:", nama_statement, "|", teks)

cur.close()
conn.close()

# Threshold dapat diatur sebelum eksekusi pertama.
conn2 = psycopg.connect()
conn2.prepare_threshold = 1
cur2 = conn2.cursor()
for i in range(2):
    cur2.execute("SELECT %s::int + 1 AS hasil", (i,))
    cur2.fetchone()
print("prepare_threshold=1, statement di server:",
      cur2.execute("SELECT count(*) FROM pg_prepared_statements").fetchone()[0])

# None mematikan prepared statement sepenuhnya.
conn3 = psycopg.connect()
conn3.prepare_threshold = None
cur3 = conn3.cursor()
for i in range(7):
    cur3.execute("SELECT %s::int + 1 AS hasil", (i,))
    cur3.fetchone()
print("prepare_threshold=None setelah 7 eksekusi, statement di server:",
      cur3.execute("SELECT count(*) FROM pg_prepared_statements").fetchone()[0])
cur2.close()
cur3.close()
conn2.close()
conn3.close()

Output yang Dihasilkan:

text 
prepare_threshold bawaan: 5
eksekusi ke-1: [email protected] -> id 1 (Budi), statement di server = 0
eksekusi ke-2: [email protected] -> id 2 (Siti), statement di server = 0
eksekusi ke-3: [email protected] -> id 3 (Ani), statement di server = 0
eksekusi ke-4: [email protected] -> id 1 (Budi), statement di server = 0
eksekusi ke-5: [email protected] -> id 2 (Siti), statement di server = 0
eksekusi ke-6: [email protected] -> id 3 (Ani), statement di server = 1
eksekusi ke-7: [email protected] -> id 1 (Budi), statement di server = 1
statement di server: _pg3_0 | SELECT id, nama FROM pelanggan WHERE email = $1
prepare_threshold=1, statement di server: 1
prepare_threshold=None setelah 7 eksekusi, statement di server: 0

Mari kita bedah perilaku yang terekam dalam output tersebut:

  1. Baris eksekusi ke-1 sampai ke-5 — kolom terakhir tetap 0: psycopg masih mengirim query sebagai statement tanpa nama, meskipun parameter sudah berpisah dari teks SQL sejak panggilan pertama.
  2. Baris eksekusi ke-6 — kolom terakhir berubah menjadi 1: teks query yang sama sudah dijalankan lima kali, melewati prepare_threshold, sehingga eksekusi keenam dijalankan melalui statement bernama _pg3_0.
  3. Baris statement di server — psycopg menerjemahkan penanda %s menjadi parameter bernomor $1 di protokol; teks yang tercatat di server adalah SELECT id, nama FROM pelanggan WHERE email = $1.
  4. Dua blok terakhir — prepare_threshold=1 membuat statement disiapkan mulai eksekusi kedua, sedangkan None mematikan fitur ini sepenuhnya sehingga tujuh eksekusi pun tidak meninggalkan statement bernama.

Pola Panggilan Berulang dan executemany #

Untuk tulis-data, pola yang sama berlaku: satu teks INSERT, banyak panggilan dengan parameter berbeda. Skrip berikut memasukkan enam baris item_pesanan lewat enam panggilan execute, memeriksa statement apa yang tersimpan di server, lalu menambah dua baris lagi lewat executemany — satu panggilan untuk banyak baris:

python eksekusi-berulang.py 
# Pola panggilan berulang dan executemany di psycopg 3.
import psycopg

conn = psycopg.connect()
cur = conn.cursor()

cur.execute("""
    CREATE TABLE item_pesanan (
        id      integer PRIMARY KEY,
        pesanan integer NOT NULL,
        produk  text NOT NULL,
        jumlah  integer NOT NULL
    )
""")
conn.commit()

data = [
    (1, 101, "Beras 5 kg", 2),
    (2, 101, "Gula 1 kg", 1),
    (3, 101, "Kopi sachet", 12),
    (4, 102, "Teh celup", 3),
    (5, 102, "Minyak goreng 1 L", 1),
    (6, 102, "Sabun mandi", 4),
]

# Teks INSERT yang sama dipanggil enam kali dengan parameter berbeda.
# Mulai panggilan berikutnya psycopg mengirimkannya sebagai prepared
# statement: parse sudah selesai, pengiriman berikutnya hanya parameter.
for baris in data:
    cur.execute(
        "INSERT INTO item_pesanan (id, pesanan, produk, jumlah) "
        "VALUES (%s, %s, %s, %s)",
        baris,
    )
conn.commit()

cur.execute("SELECT count(*) FROM item_pesanan")
print("baris setelah 6 kali execute:", cur.fetchone()[0])

cur.execute("SELECT name, statement FROM pg_prepared_statements ORDER BY name")
print("statement yang tersimpan di server:")
for nama, teks in cur.fetchall():
    print(" ", nama, "|", teks)

# executemany mengirim banyak baris lewat satu panggilan (pipeline).
cur.executemany(
    "INSERT INTO item_pesanan (id, pesanan, produk, jumlah) "
    "VALUES (%s, %s, %s, %s)",
    [
        (7, 102, "Sampo sachet", 10),
        (8, 102, "Deterjen 800 g", 1),
    ],
)
conn.commit()

cur.execute(
    "SELECT pesanan, count(*) AS item, sum(jumlah) AS unit "
    "FROM item_pesanan GROUP BY pesanan ORDER BY pesanan"
)
for pesanan, item, unit in cur.fetchall():
    print(f"pesanan {pesanan}: {item} item, total {unit} unit")

cur.close()
conn.close()

Output yang Dihasilkan:

text 
baris setelah 6 kali execute: 6
statement yang tersimpan di server:
  _pg3_0 | INSERT INTO item_pesanan (id, pesanan, produk, jumlah) VALUES ($1, $2, $3, $4)
pesanan 101: 3 item, total 15 unit
pesanan 102: 5 item, total 19 unit

Output membuktikan bahwa keenam INSERT memakai satu teks query yang sama, dan psycopg menyimpannya sebagai _pg3_0 dengan parameter $1 sampai $4. Ringkasan akhir menunjukkan delapan baris terbagi rapi: pesanan 101 memuat 3 item sebanyak 15 unit, pesanan 102 memuat 5 item sebanyak 19 unit. executemany mengirim kedua baris tambahan dalam satu panggilan sehingga driver tidak perlu menunggu balasan per baris.

Prepared Statement Bernama di node-postgres #

node-postgres memakai pendekatan yang berbeda dan penting dipahami dengan tepat. Setiap query berparameter (values diisi) dikirim melalui protokol extended dengan statement tanpa nama: parameter tetap terpisah dari teks SQL sehingga aman dari injeksi, tetapi teks query di-parse ulang pada setiap panggilan dan tidak muncul di pg_prepared_statements. Untuk mendapat manfaat parse-sekali, Anda memberi properti name pada objek query. Skrip berikut membandingkan kedua bentuk itu:

javascript named-statement-node.mjs 
// Prepared statement bernama di node-postgres (paket pg).
// Koneksi membaca variabel lingkungan PGHOST/PGPORT/PGUSER/PGDATABASE.
import pg from "pg";

const client = new pg.Client();
await client.connect();

// Query berparameter TANPA nama: dikirim lewat protokol extended dengan
// statement tanpa nama. Parameter tetap terpisah dari teks SQL (aman
// dari injeksi), tetapi teks query di-parse ulang pada tiap panggilan.
const tanpaNama = await client.query(
  "SELECT id, nama FROM pelanggan WHERE email = $1",
  ["[email protected]"],
);
console.log("query tanpa nama menemukan:", tanpaNama.rows[0].nama);

const cek = await client.query(
  "SELECT count(*) AS n FROM pg_prepared_statements",
);
console.log("statement bernama di server:", cek.rows[0].n);

// Query BERNAMA: teks di-parse sekali, lalu dijalankan ulang cukup
// dengan mengirim parameter pada nama statement yang sama.
const pertama = await client.query({
  name: "cari_email",
  text: "SELECT id, nama FROM pelanggan WHERE email = $1",
  values: ["[email protected]"],
});
console.log("query bernama pertama:", pertama.rows[0].nama);

const kedua = await client.query({
  name: "cari_email",
  text: "SELECT id, nama FROM pelanggan WHERE email = $1",
  values: ["[email protected]"],
});
console.log("query bernama kedua:", kedua.rows[0].nama);

const daftar = await client.query(
  "SELECT name, statement FROM pg_prepared_statements ORDER BY name",
);
console.log("isi pg_prepared_statements:");
for (const baris of daftar.rows) {
  console.log(" ", baris.name, "|", baris.statement);
}

await client.end();

Output yang Dihasilkan:

text 
query tanpa nama menemukan: Budi
statement bernama di server: 0
query bernama pertama: Siti
query bernama kedua: Ani
isi pg_prepared_statements:
  cari_email | SELECT id, nama FROM pelanggan WHERE email = $1

Baris kedua output (statement bernama di server: 0) adalah bukti bahwa query berparameter tanpa nama tidak meninggalkan jejak di katalog sesi — teksnya di-parse ulang setiap kali. Sebaliknya, dua pemanggilan query bernama cari_email hanya menghasilkan satu entri di pg_prepared_statements: parse terjadi sekali, pemanggilan kedua cukup mengirim parameter untuk statement yang sudah ada.

Hemat Parse, Hati-hati Rencana Generik #

Manfaat prepared statement terasa pada dua lapis: protokol menghemat pengiriman dan parsing teks query, dan server dapat menjalankan ulang rencana eksekusi yang sudah tersusun. Namun rencana eksekusi yang disimpan memiliki kekhasan sejak PostgreSQL 12: lima eksekusi pertama memakai rencana custom yang dihitung dengan nilai parameter sesungguhnya, lalu server boleh beralih ke rencana generic yang dihitung untuk nilai rata-rata. Untuk query dengan kondisi seperti WHERE kolom_flag = %s yang sangat timpang, rencana generik bisa lebih buruk daripada rencana khusus — mekanisme penuhnya dibahas di chapter Query Planner .

Rangkuman manfaat dan catatan per manfaatnya:

ManfaatMekanismeCatatan
Aman dari injeksiParameter tidak pernah masuk ke parser sebagai SQLBerlaku sejak panggilan pertama, tanpa menunggu threshold
Parsing sekaliTeks query di-parse saat statement disiapkanpsycopg: mulai eksekusi ke-6 (bawaan); node-pg: saat query bernama
Rencana eksekusi tersimpanServer menjalankan ulang rencana yang samaWaspadai rencana generik untuk data timpang
Round trip lebih rampingHanya parameter yang dikirim ulangTerasa pada query pendek yang dipanggil sangat sering

Prepared statement membuat interaksi aplikasi–database tetap stabil dan cepat selama struktur query berubah-ubah parameter. Namun ada bagian database yang prepared statement tidak menjangkau: skema itu sendiri, yang terus berevolusi seiring fitur aplikasi. Perubahan CREATE TABLE sampai ALTER TABLE itu perlu dibawa dalam berkas berversi yang bisa direview dan dijalankan ulang dengan teratur — pekerjaan chapter berikutnya, Database Migration.

Referensi #

Berkas Sumber

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

eksekusi-berulang.py

Unduh berkas
python 
# Pola panggilan berulang dan executemany di psycopg 3.
import psycopg

conn = psycopg.connect()
cur = conn.cursor()

cur.execute("""
    CREATE TABLE item_pesanan (
        id      integer PRIMARY KEY,
        pesanan integer NOT NULL,
        produk  text NOT NULL,
        jumlah  integer NOT NULL
    )
""")
conn.commit()

data = [
    (1, 101, "Beras 5 kg", 2),
    (2, 101, "Gula 1 kg", 1),
    (3, 101, "Kopi sachet", 12),
    (4, 102, "Teh celup", 3),
    (5, 102, "Minyak goreng 1 L", 1),
    (6, 102, "Sabun mandi", 4),
]

# Teks INSERT yang sama dipanggil enam kali dengan parameter berbeda.
# Mulai panggilan berikutnya psycopg mengirimkannya sebagai prepared
# statement: parse sudah selesai, pengiriman berikutnya hanya parameter.
for baris in data:
    cur.execute(
        "INSERT INTO item_pesanan (id, pesanan, produk, jumlah) "
        "VALUES (%s, %s, %s, %s)",
        baris,
    )
conn.commit()

cur.execute("SELECT count(*) FROM item_pesanan")
print("baris setelah 6 kali execute:", cur.fetchone()[0])

cur.execute("SELECT name, statement FROM pg_prepared_statements ORDER BY name")
print("statement yang tersimpan di server:")
for nama, teks in cur.fetchall():
    print(" ", nama, "|", teks)

# executemany mengirim banyak baris lewat satu panggilan (pipeline).
cur.executemany(
    "INSERT INTO item_pesanan (id, pesanan, produk, jumlah) "
    "VALUES (%s, %s, %s, %s)",
    [
        (7, 102, "Sampo sachet", 10),
        (8, 102, "Deterjen 800 g", 1),
    ],
)
conn.commit()

cur.execute(
    "SELECT pesanan, count(*) AS item, sum(jumlah) AS unit "
    "FROM item_pesanan GROUP BY pesanan ORDER BY pesanan"
)
for pesanan, item, unit in cur.fetchall():
    print(f"pesanan {pesanan}: {item} item, total {unit} unit")

cur.close()
conn.close()

otomatis-psycopg.py

Unduh berkas
python 
# Prepared statement otomatis di psycopg 3.
# Koneksi membaca variabel lingkungan PGHOST/PGPORT/PGUSER/PGDATABASE,
# sama seperti pola libpq yang dipakai psql.
import psycopg

conn = psycopg.connect()
cur = conn.cursor()

print("prepare_threshold bawaan:", conn.prepare_threshold)

EMAIL = [
    "[email protected]",
    "[email protected]",
    "[email protected]",
    "[email protected]",
    "[email protected]",
    "[email protected]",
    "[email protected]",
]

for i, email in enumerate(EMAIL, start=1):
    # Pola panggilan psycopg: teks query yang sama berulang kali,
    # parameter yang berubah. Driver yang memutuskan kapan query
    # cukup sering untuk disiapkan sebagai prepared statement.
    cur.execute("SELECT id, nama FROM pelanggan WHERE email = %s", (email,))
    id_pelanggan, nama = cur.fetchone()
    # Query pemantau diberi nomor unik lewat komentar supaya query
    # pemantau itu sendiri tidak ikut terhitung sebagai kandidat.
    jumlah = cur.execute(
        f"SELECT count(*) FROM pg_prepared_statements -- pantau {i}"
    ).fetchone()[0]
    print(f"eksekusi ke-{i}: {email} -> id {id_pelanggan} ({nama}), "
          f"statement di server = {jumlah}")

cur.execute("SELECT name, statement FROM pg_prepared_statements ORDER BY name")
for nama_statement, teks in cur.fetchall():
    print("statement di server:", nama_statement, "|", teks)

cur.close()
conn.close()

# Threshold dapat diatur sebelum eksekusi pertama.
conn2 = psycopg.connect()
conn2.prepare_threshold = 1
cur2 = conn2.cursor()
for i in range(2):
    cur2.execute("SELECT %s::int + 1 AS hasil", (i,))
    cur2.fetchone()
print("prepare_threshold=1, statement di server:",
      cur2.execute("SELECT count(*) FROM pg_prepared_statements").fetchone()[0])

# None mematikan prepared statement sepenuhnya.
conn3 = psycopg.connect()
conn3.prepare_threshold = None
cur3 = conn3.cursor()
for i in range(7):
    cur3.execute("SELECT %s::int + 1 AS hasil", (i,))
    cur3.fetchone()
print("prepare_threshold=None setelah 7 eksekusi, statement di server:",
      cur3.execute("SELECT count(*) FROM pg_prepared_statements").fetchone()[0])
cur2.close()
cur3.close()
conn2.close()
conn3.close()

rekap-server-side.sql

Unduh berkas
sql 
-- Rekap cepat prepared statement di sisi server (SQL murni).
-- Pembahasan penuh siklus PREPARE/EXECUTE/DEALLOCATE ada di chapter
-- Prepared Statement pada section User Management dan Keamanan.
CREATE TABLE pelanggan (
    id    integer PRIMARY KEY,
    nama  text NOT NULL,
    email text NOT NULL UNIQUE
);

INSERT INTO pelanggan (id, nama, email) VALUES
    (1, 'Budi', '[email protected]'),
    (2, 'Siti', '[email protected]'),
    (3, 'Ani',  '[email protected]');

-- PREPARE: parse dan rencana eksekusi dikerjakan SEKALI per sesi.
PREPARE cari_pelanggan(text) AS
    SELECT id, nama FROM pelanggan WHERE email = $1;

-- EXECUTE hanya mengirim nilai parameter, bukan teks SQL lengkap.
EXECUTE cari_pelanggan('[email protected]');

EXECUTE cari_pelanggan('[email protected]');

-- Statement bernama tercatat di katalog sesi:
SELECT name, statement FROM pg_prepared_statements ORDER BY name;

-- DEALLOCATE membuang statement dari sesi.
DEALLOCATE cari_pelanggan;

SELECT count(*) AS sisa_statement
FROM pg_prepared_statements
WHERE name = 'cari_pelanggan';