SQLite Juga Membutuhkan Edition ala Rust

 (mort.coffee)
3 poin oleh GN⁺ 3 jam lalu | 2 komentar | Bagikan ke WhatsApp
  • SQLite adalah RDBMS mandiri yang banyak digunakan untuk penyimpanan lokal dan proyek embedded, tetapi karena kompatibilitas mundur, ia tetap mempertahankan default yang merugikan keamanan dan performa
  • Secara default, SQLite tidak menegakkan constraint foreign key dan dapat menggunakan ulang ROWID, sehingga bisa muncul masalah integritas data, misalnya postingan pengguna yang sudah dihapus terhubung ke pengguna lain yang mendapat ID yang sama
  • Tabel biasa juga dapat menyimpan tipe data yang berbeda dari kolom yang dideklarasikan; tabel STRICT mencegah hal ini, tetapi strict harus ditentukan per tabel dan berbenturan dengan cara penggunaan nama tipe kustom
  • SQLITE_BUSY yang muncul langsung pada penulisan bersamaan, WAL yang dinonaktifkan, dan pengaturan sinkronisasi yang konservatif masing-masing dapat diperbaiki dengan pragma busy_timeout, journal_mode, dan synchronous
  • Dengan edition berbasis tahun seperti PRAGMA edition = 2026, SQLite dapat memilih satu paket default yang aman, sehingga pengaturan default SQLite bisa terus berkembang tanpa merusak perilaku lama

Kekuatan SQLite dan default yang usang

  • SQLite bisa disebut sebagai database untuk proyek embedded sekaligus standar industri untuk penyimpanan data lokal, dan beberapa perangkat lunak server seperti lobste.rs juga berjalan di SQLite
  • Berbeda dari RDBMS tradisional yang berjalan sebagai proses terpisah, SQLite adalah RDBMS berbentuk library, sehingga perangkat lunak dapat tetap mandiri
  • Karena tidak perlu menulis serializer dan parser kustom seperti pada format file biasa, SQLite memberikan kelebihan RDBMS sekaligus penyimpanan berbasis file
  • Namun masih ada masalah bahwa beberapa default tidak tepat memengaruhi integritas data, konkurensi, dan performa

Masalah integritas akibat foreign key tidak ditegakkan

  • Foreign key adalah sarana utama untuk menjaga konsistensi database dan mencegah referensi terputus, tetapi SQLite secara default tidak menegakkannya
  • Bahkan jika posts.user_id dideklarasikan merujuk ke users.id, pengaturan default tidak mencegah perilaku berikut
    • Membuat postingan tanpa ID pengguna yang valid
    • Menghapus pengguna sambil membiarkan postingan milik pengguna tersebut tetap ada
  • INTEGER PRIMARY KEY menjadi alias untuk ROWID tabel, dan algoritme alokasi ROWID SQLite menggunakan ulang ID dalam beberapa situasi
    • Jika Bob menulis postingan dengan ID 1 lalu hanya akunnya yang dihapus, tidak ada error foreign key yang terjadi
    • Jika kemudian Alice mendapat ID 1 yang digunakan ulang, postingan lama Bob akan terhubung ke Alice
    • Query-nya sendiri berhasil secara normal, sehingga masalah ini lebih sulit ditemukan daripada sekadar referensi yang terputus
  • Foreign key dapat ditegakkan dengan mengaktifkan pragma berikut pada setiap koneksi
PRAGMA foreign_keys = ON;
  • Jika ini diterapkan sejak awal, penghapusan pengguna yang masih memiliki postingan akan dihentikan dengan error FOREIGN KEY constraint failed

Tabel biasa mengizinkan tipe data yang berbeda dari deklarasi

  • Nilai SQLite adalah salah satu dari NULL, INTEGER, REAL, TEXT, atau BLOB, tetapi deklarasi kolom pada tabel biasa tidak membatasi tipe data yang dapat disimpan secara ketat; deklarasi itu menentukan type affinity
  • Untuk kolom dengan affinity INTEGER, aturan penyimpanan berikut berlaku
    • TEXT yang dapat diinterpretasikan sebagai bilangan bulat dikonversi menjadi INTEGER
    • TEXT yang dapat diinterpretasikan sebagai bilangan real dikonversi menjadi REAL, yaitu floating-point presisi ganda
    • Nilai lainnya disimpan apa adanya dengan tipe data aslinya
  • Affinity lain juga memiliki aturan konversi tersendiri
    • Affinity BLOB menyimpan nilai apa adanya
    • Affinity TEXT membiarkan BLOB, TEXT, dan NULL apa adanya, serta mengonversi angka menjadi TEXT
    • Affinity REAL bertindak seperti affinity INTEGER, tetapi mengonversi bilangan bulat menjadi REAL
  • Karena itu, bahkan kolom duration_sec INTEGER dapat menyimpan string nonangka seperti 'Way too long, I mean come on'
  • Dalam proyek nyata, ada juga kasus ketika string '1' dan '0' tercatat alih-alih angka 1 dan 0 untuk boolean, sehingga data harus dibersihkan dan di-debug

Tabel STRICT dan benturan dengan tipe kustom

  • Tabel STRICT memunculkan error tipe seperti cannot store TEXT value in INTEGER column jika tipe data yang salah dimasukkan ke kolom
CREATE TABLE music (
  id INTEGER PRIMARY KEY,
  name TEXT,
  duration_sec INTEGER
) strict;

  • Tidak ada pragma yang membuat semua tabel menjadi strict secara global, sehingga strict harus ditambahkan tanpa terlewat pada setiap pernyataan CREATE TABLE

  • SQLite berpendapat bahwa tipe yang fleksibel itu berguna, tetapi bahkan pada tabel STRICT, kita dapat membuat kolom yang menyimpan semua jenis nilai dengan menyatakan tipe data ANY

    • Kolom yang mengizinkan tipe data arbitrer dan kolom yang memaksa tipe data tertentu dapat dibedakan secara eksplisit

  • Masalah kompatibilitas yang lebih realistis adalah bahwa tabel STRICT tidak hanya menegakkan tipe data, tetapi juga mengubah aturan interpretasi type specifier

  • Cara tabel biasa menginterpretasikan nama tipe

    • Tabel SQLite biasa menentukan affinity berdasarkan string tipe yang dideklarasikan
    • Jika mengandung "INT", diperlakukan sebagai INTEGER
    • Jika mengandung salah satu dari "CHAR", "CLOB", atau "TEXT", menjadi TEXT
    • Jika mengandung "BLOB" atau tipenya dihilangkan, diperlakukan sebagai BLOB
    • Jika mengandung salah satu dari "REAL", "FLOA", atau "DOUB", menjadi REAL
    • Jika tidak memenuhi kondisi mana pun, diperlakukan sebagai NUMERIC
    • Jika tipe longgar digabungkan dengan aturan ini, nama tipe kustom seperti DATETIME, KEY_VALUE_SET, atau COLOR dapat ditempelkan pada kolom
    • Konektor atau wrapper database dapat melihat nama tipe tersebut lalu otomatis melakukan serialisasi dan deserialisasi
    • Bahkan tanpa fitur penanganan khusus, nama tipe itu sendiri mendokumentasikan data yang diharapkan pada kolom
    • Jika STRICT begitu saja dijadikan default, pola penggunaan ini harus ditinggalkan, sehingga diperlukan alias tipe kustom yang eksplisit
    CREATE TYPE KEY_VALUE_SET = TEXT;
    • Jika alias seperti KEY_VALUE_SET dapat digunakan dalam tabel strict, pola data yang diharapkan dari kolom TEXT yang harus di-parse oleh aplikasi juga dapat dicatat di skema
    • Fitur untuk menghubungkan constraint CHECK ke tipe kustom juga akan berguna
    • Standar SQL:1999 sudah memiliki CREATE DOMAIN, yaitu sintaks alias tipe yang mendukung hingga constraint, sehingga SQLite dapat mengimplementasikannya dengan mendukung pernyataan standar ini

Penulisan bersamaan dan SQLITE_BUSY

  • SQLite mengizinkan banyak pembacaan bersamaan, tetapi penulisan hanya diproses satu per satu
  • Dengan pengaturan default, ketika dua proses mencoba memperoleh lock tulis secara bersamaan, salah satunya langsung menerima error SQLITE_BUSY tanpa menunggu
    • Karena penulisan yang melakukan I/O disk memang bisa melambat, lebih wajar jika menunggu lock dilepas selama beberapa waktu
    • Ada juga kasus ketika sistem nyata berhenti sesekali sehingga loop retry harus ditulis sendiri
  • Pengaturan berikut akan mencoba kembali memperoleh lock selama maksimal 5 detik, dan baru setelah itu mengembalikan SQLITE_BUSY
PRAGMA busy_timeout = 5000;
  • Dalam operasi normal, sebaiknya semua penulisan dikumpulkan ke satu proses, dan jika memungkinkan ke satu thread; penulisan bersamaan memang tidak bisa menjadi cepat
  • Namun ada juga kasus operasi normal yang membutuhkan penulisan bersamaan
    • Membersihkan database secara interaktif dengan tool command-line sqlite3
    • Menjalankan skrip untuk tugas administrasi yang jarang dilakukan dan tidak memiliki frontend terpisah
  • Karena pengaturan default, pekerjaan seperti ini dapat memicu SQLITE_BUSY yang tidak terduga dan menghentikan perangkat lunak yang sedang berjalan

WAL dan default performa

  • Jika dikonfigurasi dengan benar, SQLite bisa cukup cepat untuk menjalankan sebagian peran yang biasanya ditangani server besar seperti PostgreSQL atau MySQL, tetapi performa default-nya kurang baik
  • Detail tuning untuk lingkungan server dapat dilihat lebih lanjut di Optimizing SQLite for servers
  • Masalah default yang paling penting adalah Write-Ahead Log, yaitu WAL dinonaktifkan
PRAGMA journal_mode = WAL;
  • WAL meningkatkan kecepatan tulis secara signifikan dalam sebagian besar situasi
  • Jika digunakan bersama pengaturan berikut, jumlah sinkronisasi disk dapat dikurangi secara besar tanpa risiko korupsi data
PRAGMA synchronous = NORMAL;

Edition SQLite berbasis tahun

  • Jika default lama diubah langsung sekarang, perangkat lunak lama bisa rusak dan pengguna dapat menjadi takut untuk meng-upgrade SQLite di masa depan
  • Untuk memilih default yang lebih baik sambil mempertahankan kompatibilitas mundur, satu super pragma dapat ditambahkan
PRAGMA edition = 2026;
  • Edition 2026 setidaknya bertindak sebagai alias untuk pengaturan berikut
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
  • Dalam edition ini, mode strict juga diterapkan sebagai default untuk tabel baru
  • Seperti Rust editions, ini adalah cara untuk secara eksplisit memilih perilaku default baru sambil mempertahankan perilaku kode lama
  • Berbeda dari satu mode tunggal seperti "use strict"; di JavaScript, edition berbasis tahun dapat mengubah paket default yang masuk akal seiring zaman
  • Misalnya, jika WAL2 milik Hctree masuk ke branch utama pada 2034, PRAGMA edition = 2034 di masa depan dapat dikonfigurasi untuk memilih journal_mode = WAL2
  • Sistem edition dapat menjadi kompromi untuk mengembangkan default keamanan dan performa SQLite tanpa merusak perilaku default program lama dan tanpa membuat SQLite terus terikat pada pengaturan masa lalu

Bacaan terkait

2 komentar

 
tomskang 2 jam lalu

Secara teori ini terlihat bagus, tetapi kalau mempertimbangkan benturan kompatibilitas dan overhead, rasanya malah akan merugikan.
SQLite adalah DBMS yang lahir untuk berjalan di lingkungan embedded seperti eGIS,
di dunia embedded, hal seperti asal cast void** lalu menimpa fungsi setelah deklarasi __weak__ itu hal yang biasa. Di dunia seperti itu, apakah benar perlu sejauh itu? Saya jadi ragu.
 
GN⁺ 3 jam lalu
Pendapat Lobste.rs

  • Kemarin saya melihat kabar bahwa lobste.rs beralih ke SQLite (https://lobste.rs/s/ko1ji1/lobste_rs_is_now_running_on_sqlite), lalu menulis postingan ini
    Saat membaca percakapan waktu itu, saya teringat ketidaknyamanan kecil pada nilai bawaan SQLite dan merangkumnya dalam sebuah tulisan blog

    • Saya juga membaca percakapan itu dan sampai pada kesimpulan yang sama
      Kalau ada kesempatan, tulisan ini juga layak dibagikan di forum SQLite: https://sqlite.org/forum/forum

  • Dalam SQL, CREATE TYPE digunakan untuk membuat tipe komposit, dan Postgres mendukung range, enum, array, hingga tipe dasar yang cukup kompleks
    Fitur yang diinginkan sebenarnya lebih dekat ke CREATE DOMAIN. Kita bisa membuat newtype dari tipe konkret dasar sekaligus menetapkan nilai bawaan dan batasan, sehingga skema yang mengulang batasan CHECK yang sama di banyak tabel menjadi jauh lebih ringkas. Misalnya bisa ditulis seperti create domain mything as text; atau create domain mything as integer not null check (VALUE < 5);

    • Saya seharusnya lebih dulu memeriksa apakah sudah ada fitur alias tipe yang sudah ada. Saya akan menambahkan hal itu ke tulisan

  • Perlu juga dibahas jebakan yang cukup berbahaya bahwa busy_timeout saja tidak bisa mencegah error SQLITE_BUSY yang muncul seketika
    Jika transaksi dimulai dengan kueri hanya-baca lalu menjalankan kueri tulis untuk menaikkan lock yang sedang dipegang, ketika ada penulisan lain maka ia akan langsung gagal tanpa memedulikan pengaturan busy_timeout. Solusinya adalah memakai begin immediate

    • Saya tahu ini bukan bug sungguhan, tapi sejujurnya ini terasa hampir seperti perilaku bug

  • Perlu mempertimbangkan bahwa sebagian pengaturan yang diikat ke edition yang diusulkan adalah properti koneksi dalam model SQLite, sementara sisanya adalah properti file database atau tabel individual
    PRAGMA journal_mode = WAL tetap tersimpan pada file database, tetapi PRAGMA foreign_keys = ON harus diatur ulang pada setiap koneksi. STRICT adalah properti per tabel, sehingga dalam satu database dimungkinkan mencampur tabel yang strict dan yang tidak. Jadi perlu diputuskan apakah PRAGMA terpadu ini akan didefinisikan sebagai properti file database atau koneksi. Saya juga penasaran apakah ada mekanisme bawaan yang memberi peringatan agar pustaka versi lama tidak merusak file dengan mengubah fitur yang tidak dikenalnya

  • Pendekatan edition pada dasarnya tampak sebagai kompromi yang baik antara tujuan tim pengembang SQLite untuk mempertahankan kompatibilitas ke belakang dan kebutuhan pengguna yang menginginkan nilai bawaan modern yang tidak terlalu membingungkan

  • Belakangan ini saya sangat tertarik pada RocksDB. Seperti SQLite, ini adalah pustaka database embedded, tetapi justru saya melihat tidak adanya SQL sebagai kelebihan besar
    Saya merasa keharusan mengekspresikan semuanya dalam SQL hanya menghambat saat bekerja dengan database

    • RocksDB adalah database key-value sedangkan SQLite adalah database relasional, jadi menurut saya keduanya adalah konsep berbeda yang dibuat untuk tujuan yang berbeda
      Sulit membayangkan bagaimana sistem yang memakai SQL seperti lobste.rs bisa dipindahkan ke RocksDB atau database key-value lainnya

  • Saya tidak setuju bahwa menetapkan busy_timeout adalah solusinya. SQLite sangat kuat performanya bila menggunakan satu thread penulis lalu melakukan batch pada transaksi logis yang saling tidak terkait
    Cara konkretnya dijelaskan di https://andersmurphy.com/2025/12/…

    • Untuk beban kerja umum, memang lebih tepat membatasi operasi tulis menjadi satu dan tidak membuat struktur di mana banyak penulisan terus-menerus saling berebut
      Namun juga umum untuk mengubah database secara langsung demi pekerjaan pembersihan, atau menjalankan tugas administrasi sesekali lewat skrip tanpa perlu membuat layar admin terpisah. Dalam situasi seperti itu, tidak baik jika server atau aplikasi bisa berhenti secara acak hanya karena waktu eksekusinya kebetulan bertabrakan