Di SQLite, prioritaskan penggunaan tabel STRICT

 (evanhahn.com)
1 poin oleh GN⁺ 3 jam lalu | 1 komentar | Bagikan ke WhatsApp
  • Dengan menambahkan STRICT di akhir definisi tabel, kesalahan tipe seperti teks sembarang masuk ke kolom bilangan bulat dapat dicegah lebih awal, sehingga meningkatkan integritas data
  • Saat penyisipan dan pembaruan, tipe diperiksa, tetapi nilai yang dapat dikonversi tanpa kehilangan seperti '123' tetap diizinkan; tipe kolom dibatasi pada INT, INTEGER, REAL, TEXT, BLOB, ANY
  • Untuk kolom yang perlu menampung beberapa tipe, ANY dapat ditentukan, sehingga validasi ketat dan penyimpanan fleksibel bisa diterapkan bersama dalam satu tabel
  • Tabel yang sudah ada tidak bisa langsung diubah menjadi STRICT, sehingga diperlukan pembuatan tabel baru dan penyalinan data; data lama yang salah harus dibersihkan atau dikonversi tipenya
  • Tabel STRICT hanya didukung pada SQLite 3.37.0 ke atas; meskipun secara teori ada biaya pemeriksaan, eksperimen tidak resmi tidak menemukan perbedaan performa atau ukuran file yang jelas

Membuat tabel STRICT

  • Tabel STRICT di SQLite menerapkan pemeriksaan tipe yang lebih ketat, mirip dengan mesin SQL lain
  • Cukup tambahkan STRICT di akhir definisi CREATE TABLE
CREATE TABLE people (name TEXT) STRICT;

Pemeriksaan tipe pada penyisipan dan pembaruan

  • Tabel SQLite biasa dapat menyimpan teks seperti 'garbage' bahkan di kolom INTEGER, tetapi tabel STRICT memperlakukan ketidakcocokan tipe sebagai kesalahan
CREATE TABLE people_nonstrict (age INTEGER);
INSERT INTO people_nonstrict (age) VALUES ('garbage');
-- diproses normal

CREATE TABLE people_strict (age INTEGER) STRICT;
INSERT INTO people_strict (age) VALUES ('garbage');
-- kesalahan: cannot store TEXT value in INTEGER column
  • Validasi yang sama juga diterapkan pada UPDATE, sehingga tipe yang salah tetap dicegah saat nilai diubah setelah disimpan
  • Namun, jika nilai dapat dikonversi tanpa kehilangan, tabel STRICT tetap mengizinkannya
    • String '123' dapat dikonversi sepenuhnya menjadi bilangan bulat 123, sehingga dua penyisipan di bawah ini diproses secara sama
INSERT INTO people_strict (age) VALUES ('123');
INSERT INTO people_strict (age) VALUES (123);

Batasan tipe pada tahap definisi tabel

  • Pada tabel biasa, nama tipe yang tidak didukung SQLite pun dapat digunakan dalam deklarasi kolom
    • GARBAGE, DATETIME, JSON, UUID, BLOBB semuanya diterima
    • Deklarasi seperti ini bisa berasal dari salah ketik atau kesalahpahaman tentang tipe data yang didukung SQLite
  • Tabel STRICT akan menghasilkan kesalahan pada tahap pembuatan jika nama tipe yang tidak didukung digunakan
CREATE TABLE tbl (name GARBAGE) STRICT;
CREATE TABLE tbl (name DATETIME) STRICT;
CREATE TABLE tbl (name JSON) STRICT;
CREATE TABLE tbl (name UUID) STRICT;
CREATE TABLE tbl (name BLOBB) STRICT;
  • Tipe kolom yang diizinkan hanya INT, INTEGER, REAL, TEXT, BLOB, ANY
  • Semua kolom harus memiliki tipe, sehingga tipe tidak bisa dihilangkan seperti CREATE TABLE tbl (name)

Menjaga fleksibilitas dengan ANY

  • Jika perlu menyimpan data dengan tipe yang tidak tetap, kolom ANY dapat digunakan
  • Di dalam tabel STRICT sekalipun, kolom ANY dapat menyimpan semua tipe seperti bilangan bulat, teks, bilangan real, dan BLOB
CREATE TABLE tbl (value ANY) STRICT;

INSERT INTO tbl (value) VALUES (123);
INSERT INTO tbl (value) VALUES ('text');
INSERT INTO tbl (value) VALUES (12.34);
INSERT INTO tbl (value) VALUES (X'8647');

Migrasi tabel yang sudah ada

  • Tidak ada cara untuk langsung mengubah tabel non-strict yang sudah ada menjadi tabel STRICT dengan ALTER, sehingga lebih sederhana jika membuatnya ketat sejak awal
  • Untuk mengonversi tabel yang sudah ada, perlu melalui prosedur membuat tabel STRICT baru → menyalin data → mengganti tabel lama
CREATE TABLE new_people (name TEXT) STRICT;
INSERT INTO new_people SELECT * FROM people;
DROP TABLE people;
ALTER TABLE new_people RENAME TO people;
  • Jika data lama memiliki tipe yang salah, misalnya teks di kolom bilangan bulat, proses penyalinan dapat menghasilkan kesalahan
    • Sebelum migrasi, data mungkin perlu dibersihkan atau dikonversi tipenya dengan CAST
  • Kebijakan menerapkan STRICT hanya pada tabel baru juga memungkinkan, tetapi tingkat validasi akan berbeda antar tabel, sehingga perilakunya bisa lebih sulit diprediksi dibandingkan jika semua tabel longgar

Kapan tipe fleksibel cocok digunakan

  • Tim pengembang SQLite secara terpisah merangkum keunggulan tipe fleksibel dan memberikan contoh kasus ketika perilaku default berguna
  • Kegunaan yang mungkin masuk akal untuk tabel non-strict antara lain:
    • Penyimpanan key-value murni yang menampung berbagai tipe apa adanya
    • Ruang untuk menyimpan atribut tambahan dengan tipe yang berbeda-beda
    • Kasus ketika mengimpor CSV yang berantakan secara langsung dan harus mempertahankan nilai yang tidak valid tanpa kehilangan
  • Namun, tipe yang tidak terduga dapat menyebabkan bug yang halus, sehingga pada tabel umum lebih menguntungkan untuk langsung menghasilkan kesalahan daripada mengizinkannya secara diam-diam
  • Di kode sumber SQLite juga ada komentar yang menyebut tabel non-strict sebagai legacy, tetapi ini sulit dijadikan dasar yang lebih tepercaya daripada dokumentasi resmi

Kompatibilitas versi

  • Tabel STRICT diperkenalkan di SQLite 3.37.0 yang dirilis pada November 2021
  • Versi SQLite sebelumnya tidak dapat menggunakan tabel STRICT
  • Versi lama juga tidak dapat membaca database yang berisi tabel STRICT
    • Jika tabel STRICT dibuat dengan SQLite terbaru lalu database yang sama dibuka dengan SQLite 3.36.0, akan terjadi kesalahan

Performa dan ruang penyimpanan

  • Karena tabel STRICT memeriksa tipe data tambahan saat penyisipan dan pembaruan, secara teori tabel ini bisa lebih lambat
  • Dalam eksperimen tidak resmi yang menyisipkan jutaan baris ke tabel dengan 100 kolom, tidak terlihat perbedaan performa yang jelas di berbagai komputer
  • Ukuran file database di disk juga sama, tetapi karena ini bukan benchmark menyeluruh, mungkin ada perbedaan yang tidak terdeteksi
  • Ada kemungkinan performa justru membaik karena nilai yang tidak sesuai dengan afinitas kolom (column affinity) dicegah agar tidak tersimpan secara tidak sengaja, tetapi ini belum diverifikasi secara terpisah

Hal yang perlu dipertimbangkan saat menerapkan

  • Tabel STRICT tidak menyelesaikan semua masalah data, tetapi mengurangi kesalahan terkait tipe dan memperkuat integritas data
  • Dalam kebanyakan kasus, penerapannya sederhana karena cukup menambahkan STRICT ke definisi tabel
  • Pada tabel umum, manfaat dari validasi tipe yang ketat lebih besar daripada beban migrasi, ketidakcocokan dengan versi lama, dan fleksibilitas yang terbatas

Bacaan terkait

1 komentar

 
GN⁺ 3 jam lalu
Opini Hacker News

  • Berangkat dari fakta bahwa SQLite tidak memiliki fitur untuk ALTER tabel agar berubah ke mode ketat, sehingga data dari tabel non-ketat harus disalin ke tabel ketat, sqlite-utils 4.1 menambahkan fitur konversi dua arah
    Di CLI bisa digunakan dengan uvx sqlite-utils transform data.db mytable --strict, sedangkan di Python dengan db.table("mytable").transform(strict=True)
    Catatan rilis dapat dilihat di https://sqlite-utils.datasette.io/en/stable/changelog.html#v..., dokumentasi Python API di https://sqlite-utils.datasette.io/en/stable/python-api.html#..., dan dokumentasi CLI di https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...

  • https://sqlite.org/flextypegood.html menjelaskan mengapa penentuan tipe yang fleksibel menjadi default dan kemungkinan tidak akan berubah ke depannya, tetapi itu tidak sejalan dengan pengalaman nyata
    Berbeda dengan penjelasan bahwa memasukkan nama pelanggan ke integer Customer.creditScore pun dapat dengan mudah ditemukan dan diperbaiki, baris yang rusak sulit dipulihkan dan datanya bisa saja sudah hilang sepenuhnya
    Juga sangat sulit menyetujui logika bahwa pemeriksaan tipe yang ketat hanya mencegah kesalahan yang mudah ditemukan, sehingga justru mempersulit deteksi dan perbaikan bug

    • Sulit memahami sudut pandang bahwa keamanan dan kepercayaan yang diberikan konfigurasi database yang andal justru membuat bug data lebih sulit diperbaiki
      Di Postgres, biasanya sebanyak mungkin pemeriksaan dan pengaman ditambahkan, sehingga kita tidak perlu mencari-cari belakangan kesalahan yang sejak awal semestinya tidak terjadi
    • MongoDB juga pernah memiliki logika serupa bahwa apa pun bisa disimpan, tetapi setelah penggunaan nyata kebanyakan orang menyadari bahwa itu umumnya merupakan sifat yang buruk
      Ini tampak lebih seperti hasil dari era ketika SQLite dibuat dan keyakinan kuat penulisnya, bukan prinsip rekayasa yang ketat; kesannya, setelah banyak dikritik, posisinya tetap dipertahankan dengan alasan apa pun yang bisa digunakan
      Tipe seperti JSON atau HSTORE di Postgres dapat menyediakan fleksibilitas yang diinginkan, tetapi hampir selalu lebih baik menjadikannya pilihan saat diperlukan, bukan memaksakan penentuan tipe tanpa batas sebagai default

  • Akan lebih baik jika STRICT menjadi default
    Selain poin ini, pengembang SQLite adalah orang hebat yang membuat alat yang luar biasa

    • Bahkan foreign key tidak aktif secara default sehingga harus menggunakan PRAGMA foreign_keys = ON;
      Masalah yang lebih besar adalah tidak ada pragma yang setara untuk tabel ketat, sehingga setiap CREATE TABLE harus diberi STRICT yang non-standar
      Pragma STRICT global juga pernah dipertimbangkan tetapi tidak diimplementasikan; materi terkait dapat dilihat di https://sqlite.org/foreignkeys.html dan https://sqlite.org/forum/forumpost/1b9d073a37ca5998
    • SQLite mengutamakan kompatibilitas ke belakang, sehingga hampir tidak pernah mengubah nilai default
      Tujuannya agar perangkat lunak untuk SQLite 3.53, setelah ditingkatkan ke 3.54, tidak tiba-tiba membuat tabel ketat saat CREATE TABLE, lalu memunculkan error dan merusak semuanya
    • Masalah serupa juga ada pada constraint foreign key yang dinonaktifkan secara default demi kompatibilitas
      Membuat penasaran apakah dulu SQLite hanya mendukung sintaks foreign key tanpa mengimplementasikan fungsi sebenarnya
    • Juga diperlukan tipe tanggal dan timestamp yang benar, bukan sekadar string biasa
    • Tidak ada alasan bagi database untuk mengizinkan kesalahan memasukkan tipe yang salah, dan filosofi type safety SQLite terasa mengecewakan
      Pernah ada kode yang diterapkan ke ribuan perangkat yang menyimpan string '1' dan '0' di kolom boolean, lalu harus dibersihkan; itu sama sekali tidak menyenangkan
      Tipe timestamp juga tidak ada sehingga harus disimpan di kolom teks, dan bahkan fungsi tanggal/waktu standar menghasilkan yyyy-mm-dd HH:MM:SS yang harus diasumsikan secara implisit sebagai UTC, alih-alih format ISO yyyy-mm-ddTHH:MM:SSZ
      SQLite benar-benar proyek yang luar biasa, tetapi beberapa keputusan desainnya membingungkan

  • Dari sudut pandang yang datang dari lingkungan SQL enterprise, saya tidak menganggap SQLite secara serius karena tipe field pada dasarnya tidak dipaksakan, dan saya juga terkejut ketika SQLite menjadi dasar metadata aplikasi smartphone
    Ini mengingatkan pada kisah lama jaringan: memilih UDP karena latensi rendah dan kesederhanaannya, lalu membuat ulang sendiri sebagian besar fitur keandalan TCP di tingkat aplikasi

    • Jika mengimplementasikannya sendiri, Anda bisa membuatnya berbeda dari TCP dan kadang memperoleh efek besar; QUIC dan HTTP/3 adalah contohnya
      Namun mengimplementasikan sendiri pemeriksaan tipe di atas SQLite yang tidak ketat tampaknya tidak memberi kekuatan serupa
    • Foreign key sama sekali belum didukung sebelum 2009 dan hingga kini juga tidak diaktifkan secara default: https://sqlite.org/foreignkeys.html
    • Ada juga kasus pendekatan seperti ini berhasil menjadi bisnis nyata: https://aeron.io/
    • Jika pemeriksaan tipe tidak didukung sepenuhnya, saya bisa memahami sikap yang tidak menganggap SQLite serius, tetapi sebagian besar database juga tidak menyediakan konfigurasi default yang sempurna
      PostgreSQL pun default-nya ditujukan untuk sistem berspesifikasi rendah, sehingga perlu konfigurasi untuk mendapatkan performa optimal
      Selain itu, karena TCP berbasis stream, banyak aplikasi mengalami masalah HOL blocking, dan jika Anda membuat sendiri lapisan keandalan di atas UDP, setelah banyak pekerjaan pun Anda bisa mendapatkan hasil yang lebih baik daripada TCP
    • Jika Anda tertarik pada Jeep atau Bronco, Anda tidak akan mencari pengulas mobil umum
      Mereka menilainya berisik dan buruk pengendaliannya, karena mereka mendekati produk yang sejak awal ditujukan untuk kegunaan lain seolah-olah hanya use case mereka sendiri yang penting

  • Tabel strict memang punya kekurangan, yaitu tidak bisa memakai beberapa tipe seperti Date, tetapi tetap seharusnya menjadi default
    Jika beberapa aplikasi berbagi database, tipe yang dideklarasikan harus bisa dipercaya; jika salah satunya menyimpan string di kolom angka, semuanya akan rusak
    Sebaliknya, embedded database, yang merupakan penggunaan utama SQLite, biasanya hanya dipakai oleh satu aplikasi, sehingga ada juga keuntungan dapat mengembangkan skema alih-alih membuat database baru dan menyalinnya
    Dalam hal ini, kode aplikasi tahu apa yang harus diharapkan dari tiap kolom, termasuk tipe campuran

    • SQLite hanya memiliki 5 tipe data: INTEGER, TEXT, BLOB, REAL, NUMERIC: https://sqlite.org/datatype3.html
    • SQLite tidak memiliki tipe data tanggal, dan juga tidak ada cara memanggil EXPLAIN pada query SELECT sembarang untuk mendapatkan nama tipe yang secara formal ditentukan
      Karena itu, meski memakai nama seperti DATE atau DATETIME, bahkan itu pun tidak bisa diinferensikan
    • Date bukan tipe nyata; yang dibuat hanyalah kolom dengan numeric affinity

  • Mode strict membatasi notasi tipe kolom sehingga tidak bisa memakai nama yang lebih bermakna, dan membuat kode tidak bisa memanfaatkan nama itu saat memetakan tipe database ke tipe aplikasi; akibatnya justru menghambat sistem tipe yang lebih ketat di lapisan aplikasi
    Detailnya ada di https://hn.algolia.com/?query=chrismorgan+strict+sqlite&type..., dan jika menangani database dengan crate seperti sqlx di Rust, saya rasa lebih baik menghindari mode strict

  • Untuk database dan RPC, saya menginginkan tipe yang ketat, tetapi SQLite memiliki kegunaan yang agak berbeda; setelah mencobanya langsung, mungkin Anda akan lebih memahami https://sqlite.org/flextypegood.html
    Misalnya, tujuan agar skrip sembarang yang bukan untuk SQLite pun secara kebetulan dapat berjalan adalah karakteristik yang biasanya tidak dipertimbangkan di DBMS lain

    • Bagian pro dan kontra dalam dokumen tersebut melewatkan argumen inti
      Keunggulan utama penentuan tipe yang fleksibel adalah mudah mengembangkan skema
      Setiap kali kebutuhan embedded database berubah, alih-alih membuat database baru dan memigrasikan data, Anda bisa mengubah skema di tempat; dan jika hanya satu aplikasi yang membaca dan menulis, tidak mengejutkan bila string ditambahkan ke kolom integer
      Sebaliknya, jika beberapa aplikasi diperbarui sesuai jadwal masing-masing dan berbagi database yang sama, skema adalah kontrak, sehingga satu tipe yang salah dapat merusak aplikasi lain
      Jika tabel yang dibuat oleh satu aplikasi digunakan oleh aplikasi lain, tipe data yang disepakati harus dipatuhi secara ketat
    • Kebanyakan orang memeriksa pada waktu kompilasi apakah kode mematuhi struktur tipe, dan skema SQLite yang dikembangkan bersama menyediakan informasi tipe yang diperlukan untuk analisis statis
      Memeriksanya lagi saat runtime tidak memberi keuntungan nyata, dan kode yang melalui analisis statis dipastikan memasukkan string tidak akan tiba-tiba bermutasi lalu memasukkan integer
      SQLite berbeda dari Postgres, tempat beberapa aplikasi berbagi data dan harus memercayai pihak ketiga
      Di lingkungan seperti Postgres, validasi runtime wajib, tetapi SQLite pada dasarnya dirancang untuk satu aplikasi dan satu database, sehingga cukup memercayai kode sendiri yang bisa dievaluasi pada waktu kompilasi
      Dalam situasi tidak lazim ketika beberapa aplikasi berbagi satu file, tabel strict dapat diaktifkan

  • Tipe data dinamis mungkin cocok untuk penggunaan seperti penyimpanan key-value sederhana, tetapi perlu ditanyakan mana yang sesuai dengan principle of least surprise: INTEGER menerima 'hello world' tanpa error, atau nilai semacam itu hanya bisa dimasukkan jika harus secara eksplisit memakai keyword seperti NONSTRICT atau tipe ANY
    Sebagian besar pengguna SQLite tampaknya tidak akan menduga bahwa yang pertama mungkin terjadi

    • Ini mungkin karena SQLite bertujuan menjadi database tanpa tipe, tetapi tetap ingin sintaksnya terlihat seperti SQL standar
      Ini juga sejalan dengan penjelasan bahwa mereka berharap kode untuk DBMS lain kebetulan dapat berjalan di SQLite
      Namun tetap saja sangat mengejutkan bahwa pengguna tidak menghilangkan tipe, melainkan secara eksplisit menulis INTEGER, lalu masih bisa memasukkan teks

  • Pernah ada kasus UUID yang dimulai dengan nilai seperti 08123… diparse sebagai oktal, sehingga sebagian keliru dikonversi menjadi angka
    Itu membingungkan dan menyebalkan, tetapi terselesaikan dengan menerapkan STRICT dan membuat ulang seluruh tabel

    • Itu bukan akibat SQLite, melainkan driver bahasa yang terlalu banyak membantu
      SQLite tidak memiliki tipe oktal

  • Gunakan CREATE TABLE ... STRICT WITHOUT ROWID sebagai default, dan saya tidak melihat alasan untuk membuatnya berbeda