Catatan Tutorials

Database dan dasar SQL

Kenapa kebanyakan aplikasi menyimpan datanya di database relasional, dan SQL yang dibutuhkan sehari-hari: tabel dan key, SELECT dengan WHERE dan ORDER BY, JOIN, GROUP BY, NULL, constraint, transaksi, index, dan placeholder. Setiap contoh dijalankan di database SQLite sungguhan.

7 menit bacaTersedia juga dalam bahasa Inggris →

Di bagian 9, laporan bulanan membaca pesanan dari file CSV. Cara itu cukup untuk satu file ekspor. Tapi tidak lagi cukup saat sepuluh orang menambah pesanan bersamaan, saat Anda butuh “semua pesanan pelanggan ini” dalam satu milidetik, atau saat penulisan yang setengah jadi tidak boleh membuat angka jadi salah. Untuk itulah database ada.

Bagian ini membahas database relasional dan SQL, bahasa yang dipakai hampir semuanya. Contohnya memakai SQLite, yang “is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine”: library kecil, cepat, mandiri, dan andal yang mengimplementasikan mesin database SQL. Wujudnya satu file, sudah ikut terpasang di Python dan PHP, dan SQL-nya sangat mirip dengan yang Anda tulis untuk MySQL atau PostgreSQL.

Tabel, baris, kolom, key

Tutorial PostgreSQL menjelaskan gagasan intinya dengan gamblang: database relasional “is a system for managing data stored in relations. Relation is essentially a mathematical term for table.” Relasi pada dasarnya istilah matematika untuk tabel. Lalu: “Each table is a named collection of rows. Each row of a given table has the same set of named columns, and each column is of a specific data type.” Setiap tabel adalah kumpulan baris, setiap baris punya kolom-kolom yang sama, dan setiap kolom punya tipe data tertentu.

Dua tabel untuk toko kecil:

CREATE TABLE customers (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    city  TEXT
);

CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    ordered_on  TEXT NOT NULL,
    product     TEXT NOT NULL,
    qty         INTEGER NOT NULL CHECK (qty > 0),
    price       INTEGER NOT NULL
);
  • Primary key (id) mengidentifikasi setiap baris secara unik.
  • Foreign key (customer_id REFERENCES customers(id)) menghubungkan pesanan dengan pelanggannya. Nama pelanggan disimpan sekali saja, di customers, bukan disalin ke setiap pesanan. Ubah sekali, semua pesanan ikut melihat nama barunya.
  • Constraint seperti NOT NULL dan CHECK (qty > 0) adalah aturan yang ditegakkan database, kode mana pun yang menulis datanya.

Harga disimpan sebagai rupiah bulat dalam INTEGER, dengan alasan yang sama seperti di bagian 2: floating point bukan tipe yang tepat untuk uang.

Data contohnya: tiga pelanggan (Ana di Bandung, Budi di Jakarta, Cici tanpa kota) dan empat pesanan.

SELECT: mengajukan pertanyaan

SQL menyatakan apa yang Anda inginkan, bukan bagaimana cara mendapatkannya. Database yang memutuskan caranya.

SELECT product, qty, price
FROM orders
WHERE qty >= 3
ORDER BY qty DESC;
('pen', 10, 4000)
('mug', 3, 35000)

WHERE menyaring baris, ORDER BY mengurutkannya, dan SELECT memilih kolomnya. Bandingkan dengan loop dan if yang Anda tulis untuk CSV: logikanya sama, tapi database bisa memakai index dan tidak perlu memuat semua data ke memori.

JOIN: menggabungkan tabel

Pesanan hanya menyimpan customer_id. Untuk menampilkan nama, gabungkan kedua tabel lewat key-nya:

SELECT c.name, o.product, o.qty * o.price AS amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY c.name, o.ordered_on;
('Ana', 'book', 170000)
('Ana', 'mug', 105000)
('Budi', 'pen', 40000)
('Budi', 'book', 85000)

Cici tidak muncul, karena JOIN biasa hanya menyimpan baris yang cocok di kedua sisi. Untuk tetap menampilkan semua pelanggan, termasuk yang belum pernah memesan, pakai LEFT JOIN:

SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id
ORDER BY c.name;
('Ana', 2)
('Budi', 2)
('Cici', 0)

“Pelanggan dengan nol pesanan” adalah persis jenis baris yang hilang diam-diam kalau salah memilih join.

GROUP BY: laporan dalam satu query

Seluruh laporan bulanan dari bagian 9 (saring, kelompokkan, jumlahkan) cukup satu query:

SELECT substr(ordered_on, 1, 7) AS month,
       SUM(qty * price) AS revenue,
       COUNT(*) AS orders
FROM orders
GROUP BY month
ORDER BY month;
('2026-10', 315000, 3)
('2026-11', 85000, 1)

GROUP BY mengumpulkan baris dengan nilai yang sama, dan fungsi agregat (SUM, COUNT, AVG, MIN, MAX) menghitung satu nilai per kelompok. Perhatikan bahwa substr(..., 1, 7) aman di sini karena tanggalnya disimpan konsisten sebagai YYYY-MM-DD. Itu satu alasan lagi untuk membiarkan database menyimpan format yang bersih, bukan format apa adanya dari CSV (bagian 15).

NULL bukan sebuah nilai

Kota Cici adalah NULL: tidak diketahui. NULL berperilaku berbeda dari apa pun, dan pasti mengejutkan semua orang setidaknya sekali. Dokumentasi PostgreSQL memperingatkan: “Do not write expression = NULL because NULL is not “equal to” NULL. (The null value represents an unknown value, and it is not known whether two unknown values are equal.)” Jangan menulis = NULL, karena NULL mewakili nilai yang tidak diketahui, dan tidak bisa dipastikan apakah dua nilai yang tidak diketahui itu sama.

SELECT name FROM customers WHERE city = NULL;   -- no rows
SELECT name FROM customers WHERE city IS NULL;  -- Cici
[]
[('Cici',)]

Query pertama bukan error. Ia hanya diam-diam tidak mengembalikan apa-apa. Pakai IS NULL dan IS NOT NULL.

Constraint: database berkata tidak

Constraint melindungi data bahkan saat kode aplikasi punya bug:

INSERT ... qty = 0          → IntegrityError: CHECK constraint failed: qty > 0
INSERT ... customer_id = 99 → IntegrityError: FOREIGN KEY constraint failed

Ini fail-fast dari bagian 13, di garis pertahanan terakhir. Satu detail SQLite yang perlu diketahui: “Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection”. Foreign key nonaktif secara bawaan dan harus dinyalakan di setiap koneksi, dengan PRAGMA foreign_keys = ON. MySQL (InnoDB) dan PostgreSQL menegakkannya secara bawaan.

Transaksi: semua atau tidak sama sekali

Membuat pesanan berarti dua penulisan: tambahkan pesanan, lalu kurangi stok. Kalau yang kedua gagal, yang pertama tidak boleh tertinggal. Transaksi mengelompokkan keduanya. Wikipedia menggambarkan ACID sebagai “a set of properties of database transactions intended to guarantee data validity despite errors”, sekumpulan sifat transaksi yang menjamin data tetap valid meski ada kesalahan, dan transaksi yang atomik “either succeed completely or fail completely”: berhasil seluruhnya atau gagal seluruhnya.

Di sqlite3 Python, with db: membungkus sebuah blok dalam transaksi yang di-commit kalau bloknya selesai dan di-rollback kalau terjadi exception:

def place_order(product, qty):
    with db:
        db.execute("INSERT INTO orders (product, qty) VALUES (?, ?)", (product, qty))
        db.execute("UPDATE stock SET qty = qty - ? WHERE product = ?", (qty, product))

place_order("book", 3)   # stock 5 → 2
place_order("book", 4)   # only 2 left
failed: CHECK constraint failed: qty >= 0
stock:  2
orders: [(1, 'book', 3)]

INSERT untuk pesanan kedua sempat berjalan, tapi saat pengurangan stok gagal, seluruh transaksi di-rollback. Tidak ada pesanan 4 buku yang stoknya tidak pernah dikurangi. Tanpa transaksi, pesanan yatim itu akan tertinggal di tabel. Di PHP, hal yang sama adalah $pdo->beginTransaction(), lalu commit() atau rollBack(); Laravel membungkusnya sebagai DB::transaction(...).

Index: tidak membaca setiap baris

Mencari semua pesanan untuk satu pelanggan tanpa bantuan berarti memeriksa setiap baris: full scan, O(n) dari bagian 10. Index adalah struktur terurut pada sebuah kolom yang membuat database bisa langsung melompat ke baris yang cocok, seperti indeks di bagian belakang buku.

EXPLAIN QUERY PLAN menunjukkan apa yang akan dilakukan SQLite; perintah ini memberi “a high-level description of the strategy or plan that SQLite uses to implement a specific SQL query”, gambaran umum strategi yang dipakai untuk menjalankan sebuah query. Dengan satu juta pesanan, di mesin yang dipakai untuk menulis catatan ini:

plan: SCAN orders
100 lookups without index: 2.888 s
plan: SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
100 lookups with index:    0.0030 s

Satu baris, CREATE INDEX idx_orders_customer ON orders(customer_id);, dan rencananya berubah dari SCAN menjadi SEARCH, hampir seribu kali lebih cepat. Index tidak gratis: memakan ruang dan membuat setiap insert sedikit lebih lambat, jadi tambahkan hanya untuk kolom yang memang sering dipakai untuk menyaring, join, atau mengurutkan. Saat sebuah halaman lambat, query plan adalah salah satu hal pertama yang perlu dicek. MySQL dan PostgreSQL juga punya EXPLAIN.

Placeholder, selalu

Nilai dari pengguna tidak boleh ditempel langsung ke teks SQL. Ini alasannya, dengan “nama” yang diketik di kotak pencarian:

name = "x' OR '1'='1"

unsafe = f"SELECT name, phone FROM customers WHERE name = '{name}'"
# SELECT name, phone FROM customers WHERE name = 'x' OR '1'='1'
db.execute(unsafe).fetchall()
string-built: [('Ana', '0811'), ('Budi', '0812'), ('Cici', '0813')]
placeholder:  []

Input tadi menjadi bagian dari SQL dan mengubah kondisinya jadi “selalu benar”. Nomor telepon semua pelanggan bocor. Itulah SQL injection, dan bagian 22 akan membahasnya lebih dalam. Dengan placeholder, nilainya dikirim terpisah dari SQL dan hanya bisa menjadi sebuah nilai. Dokumentasi Python: “Always use placeholders instead of string formatting to bind Python values to SQL statements, to avoid SQL injection attacks”. Selalu pakai placeholder, bukan format string, untuk memasukkan nilai ke SQL.

PDO di PHP melakukan hal yang sama dengan prepared statement. Manualnya: “The parameters to prepared statements don’t need to be quoted; the driver automatically handles this.” Parameter tidak perlu diberi tanda kutip; driver yang mengurusnya.

$stmt = $db->prepare('SELECT name, phone FROM customers WHERE name = :name');
$stmt->execute(['name' => "x' OR '1'='1"]);   // → array(0) {}
$stmt->execute(['name' => 'Ana']);            // → [{"name":"Ana","phone":"0811"}]

Manualnya juga jujur memberi catatan: kalau bagian lain dari query “built up with unescaped input, SQL injection is still possible”, injection tetap mungkin terjadi. Placeholder bekerja untuk nilai, bukan untuk nama tabel atau kolom. Kalau itu harus berubah-ubah, pilih dari daftar tetap di kode.

ORM dan query builder

Kebanyakan framework menyediakan ORM (Eloquent, Django ORM, Prisma, GORM) yang membuat Anda bisa menulis Order::where('qty', '>=', 3)->get() alih-alih SQL. ORM memakai placeholder untuk Anda dan menghemat banyak ketikan. ORM juga abstraksi yang bisa bocor (bagian 11): masalah 500 query di dalam loop di bagian itu adalah kesalahan ORM klasik. Mengetahui SQL di baliknya adalah cara untuk menyadarinya, dan kebanyakan ORM bisa mencatat SQL yang dijalankannya. Nyalakan fitur itu saat ada yang lambat.

Kenapa ini penting sehari-hari

  • Data yang benar datang dari constraint dan transaksi, bukan dari berharap setiap jalur kode sempurna.
  • Kecepatan biasanya datang dari index yang tepat dan jumlah query yang lebih sedikit, bukan dari hardware yang lebih kencang.
  • Laporan sering cukup satu GROUP BY yang ditulis dengan baik, bukan loop di kode aplikasi.
  • Keamanan dimulai dari placeholder. Tidak ada situasi di mana SQL yang disusun dari string berisi input pengguna bisa dianggap aman.
  • Query hasil AI sering hampir benar: JOIN yang seharusnya LEFT JOIN, = NULL, index yang terlupa, query di dalam loop. Jalankan dengan data sungguhan dan baca query plan-nya.

Coba sendiri

  1. Buat kedua tabel di SQLite (sqlite3 shop.db) dan ulangi query-query di atas.
  2. Tulis query pendapatan per pelanggan per bulan, termasuk pelanggan dengan nol pesanan.
  3. Masukkan 100.000 baris, ukur waktu sebuah query dengan dan tanpa index, lalu bandingkan output EXPLAIN QUERY PLAN-nya.
  4. Cari tempat di kode Anda yang menyusun SQL dengan penggabungan atau interpolasi string. Tulis ulang dengan placeholder.

Ringkasan

  • Database relasional menyimpan data dalam tabel berisi baris dan kolom bertipe, dihubungkan oleh primary key dan foreign key.
  • SELECT / WHERE / ORDER BY mengajukan pertanyaan; JOIN menggabungkan tabel (pakai LEFT JOIN untuk tetap menyimpan baris tanpa pasangan); GROUP BY dengan fungsi agregat membangun laporan.
  • NULL berarti tidak diketahui: pakai IS NULL, jangan pernah = NULL.
  • Constraint membuat database menolak data yang buruk; transaksi membuat perubahan bertahap jadi semua-atau-tidak-sama-sekali.
  • Index mengubah full scan menjadi pencarian cepat; cek dengan EXPLAIN.
  • Placeholder, selalu, untuk setiap nilai yang datang dari luar.
  • ORM membantu, tapi ketahui SQL yang dihasilkannya.

Referensi