Penjelasan File SQLite: Struktur, Kasus Penggunaan, dan Batasan Utama

File SQLite adalah file disk tunggal yang menampung seluruh database relasional — tabel, indeks, dan skema secara bersamaan. Dokumentasi resmi SQLite menyebutnya sebagai "file database utama" dan mencatat bahwa "keadaan lengkap dari database SQLite biasanya terkandung" di dalamnya. Kata biasanya memiliki peran yang sangat penting dalam kalimat tersebut.

Jika Anda pernah menerima file .db, .sqlite, atau .sqlite3 dan bertanya-tanya apakah Anda telah menerima semua datanya, format inilah yang perlu Anda pahami dengan baik.

Apa sebenarnya file SQLite itu

SQLite mendeskripsikan dirinya sebagai "pustaka dalam-proses yang mengimplementasikan mesin database SQL transaksional yang mandiri, tanpa server, tanpa konfigurasi." Halaman yang sama menyatakan bahwa SQLite "tidak memiliki proses server yang terpisah." Sebuah aplikasi menghubungkan pustaka tersebut dan membaca filenya.

Dua konsekuensi menyusul. Pertama, database dikirim sebagai satu artefak, itulah sebabnya begitu banyak aplikasi mengirimkan data mereka dengan cara ini. Kedua, formatnya harus sangat stabil, karena file-file tersebut bertahan lebih lama daripada perangkat lunak yang menulisnya. SQLite mencantumkan "format file yang stabil dan tahan lama" di antara fitur-fitur utamanya. Ia juga menyatakan bahwa kodenya berada dalam domain publik, "bebas digunakan untuk tujuan apa pun, komersial maupun pribadi."

Skalanya mudah untuk diremehkan. Halaman tentang SQLite sendiri menyatakan bahwa ia "adalah database yang paling banyak digunakan di dunia dengan lebih banyak aplikasi daripada yang dapat kami hitung."

Apa yang ada di dalam file tersebut

Header 100-byte

Byte pertama mengidentifikasi format tersebut. Pada offset 0, file tersebut membawa string header 16-byte: SQLite format 3\000. Tanda tangan itulah cara alat mengenali file tersebut tanpa memedulikan ekstensinya.

Bidang berikutnya lebih penting daripada yang terlihat. Pada offset 16 terdapat integer 2-byte yang menyimpan "ukuran halaman database dalam byte." Dokumentasi menyatakan bahwa ukuran tersebut "harus berupa pangkat dua antara 512 dan 32768 inklusif, atau nilai 1 yang mewakili ukuran halaman 65536." Semua bidang multibyte dalam header disimpan dengan byte paling signifikan terlebih dahulu.

Dua byte lagi menyusul pada offset 18 dan 19: versi tulis dan versi baca format file. Dokumentasi mencatat nilainya adalah "1 untuk warisan; 2 untuk WAL."

Halaman, bukan baris

Di bawah header, file tersebut merupakan tumpukan halaman dengan ukuran tetap. Spesifikasinya sangat jelas: "File database utama terdiri dari satu atau beberapa halaman. Ukuran halaman adalah pangkat dua antara 512 dan 65536 inklusif. Semua halaman dalam database yang sama memiliki ukuran yang sama."

Halaman diberi nomor mulai dari 1, dan nomor halaman maksimum adalah 4,294,967,294. Tabel dan indeks berada di dalam halaman-halaman tersebut sebagai struktur B-tree, itulah sebabnya editor teks hanya menampilkan sangat sedikit informasi kepada Anda.

File pendamping yang tidak pernah disebut orang

Ini adalah bagian yang sering membuat orang terkecoh. Dokumentasi menyatakan bahwa keadaan lengkap "biasanya" berada dalam satu file. Kemudian ia menyebutkan pengecualiannya. Selama transaksi, SQLite "menyimpan informasi tambahan dalam file kedua yang disebut 'rollback journal'." Dalam mode WAL, file kedua tersebut adalah log write-ahead.

Jadi, salinan yang diambil saat aplikasi sedang menulis mungkin kehilangan data yang telah diterapkan yang masih berada di file pendamping. Jika seorang rekan mengirimi Anda file .db saja tanpa yang lain, dan angka-angkanya terlihat agak usang, ini adalah hal pertama yang harus diperiksa.

Cara membuka file SQLite

Ada tiga cara, dan cara yang tepat bergantung pada apa yang ingin Anda lakukan selanjutnya.

Membacanya dengan penampil. Penampil SQLite berbasis desktop dan browser membuka file, mencantumkan tabel, dan memungkinkan Anda mengeklik baris-barisnya. Ini adalah cara tercepat untuk menjawab "apa saja yang ada di sini," dan biasanya cukup untuk pandangan pertama.

Melakukan kueri dengan baris perintah atau pustaka. Shell sqlite3 dan pengikatan pustaka standar di Python, Node, dan sebagian besar bahasa lainnya membaca format ini secara langsung. Ini adalah cara yang digunakan ketika Anda sudah mengetahui skema dan menginginkan angka tertentu.

Mengekspor tabel dan menganalisisnya di tempat lain. Ekspor tabel ke CSV dan masukkan ke alat apa pun yang sudah digunakan tim Anda. Anda akan kehilangan hubungan antar tabel, yang merupakan satu-satunya hal yang dilindungi oleh format tersebut. Jika memungkinkan, ekspor hasil yang telah digabungkan daripada tabel mentah.

Mengapa alat mungkin menyatakan bahwa file tersebut bukan database

Spesifikasi menjelaskan hal ini. Setiap file yang valid dimulai dengan string header 16-byte, SQLite format 3\000. Pembaca yang membuka file dan tidak menemukan tanda tangan tersebut pada offset 0 berarti tidak menerima database SQLite.

Tiga penyebab umum mencakup sebagian besar kasus. File ditransfer secara tidak lengkap, sehingga header-nya ada tetapi sisanya terpotong. File tersebut dienkripsi atau dibungkus oleh aplikasi, sehingga byte pertamanya adalah hal lain. Atau ekstensinya menyesatkan, dan apa yang sebenarnya Anda terima adalah ekspor biasa yang diganti namanya oleh seseorang yang berniat membantu.

Seberapa besar ukuran file SQLite yang bisa dicapai

Lebih besar dari yang biasanya tersirat dalam pertanyaan tersebut. Halaman batas SQLite menyatakan bahwa ukuran maksimum file database adalah 4,294,967,294 halaman. Pada ukuran halaman maksimum 65,536 byte, itu berarti ukuran database maksimum sekitar 281 terabyte.

Halaman tersebut sangat jujur tentang angka itu. Ia mencatat batas atas "belum diuji karena para pengembang tidak memiliki akses ke perangkat keras yang mampu mencapai batas ini."

Jumlah baris dibatasi oleh dinding yang sama. Batas maksimum teoretis adalah 2^64 baris dalam satu tabel. Dokumentasi menunjukkan bahwa batas ini "tidak dapat dicapai karena ukuran database maksimum sebesar 281 terabyte akan tercapai terlebih dahulu."

Untuk pekerjaan praktis, kesimpulan yang berguna adalah kebalikan dari sebuah batasan. Jika seseorang memberi Anda file .db dan memperingatkan bahwa ukurannya besar, formatnya hampir pasti bukan hal yang akan menghalangi Anda. Ukuran halaman yang dipilih saat file dibuat, dan apakah file tersebut membawa indeks, akan jauh lebih memengaruhi pengalaman penggunaan daripada batas atas yang didokumentasikan.

Di mana Anda akan menemui file SQLite

• Ekspor aplikasi. Aplikasi desktop dan seluler sering kali menyimpan riwayat, pengaturan, dan log pesan dalam file SQLite yang dapat Anda salin.
• Penyerahan analitik. Insinyur mengirimkan snapshot sebagai satu file daripada memberikan akses database.
• Perangkat dan telemetri. Sistem tertanam menulis secara lokal karena tidak ada server untuk diajak berkomunikasi.
• Arsip. Stabilitas jangka panjang format ini menjadikannya pilihan umum untuk kumpulan data yang harus tetap dapat dibaca selama bertahun-tahun.
• Internal browser dan alat. Banyak alat lokal menyimpan keadaan dengan cara ini, itulah sebabnya ekstensi tersebut muncul di tiket dukungan.

Untuk apa file WAL dan jurnal digunakan

Anda mungkin telah menyalin file .db dan menemukan file -wal atau -journal berada di sampingnya. Itu adalah file pendamping yang dijelaskan dalam spesifikasi, dan menghapusnya adalah cara orang kehilangan data.

Rollback journal adalah mekanisme yang lebih lama. Sebelum mengubah halaman, SQLite menulis versi asli halaman tersebut ke dalam jurnal. Jika penulisan terganggu, versi asli dapat dikembalikan, yang membuat transaksi bertahan dari kegagalan sistem.

Write-ahead log membalikkan pengaturan tersebut. Perubahan masuk ke log terlebih dahulu dan file utama diperbarui kemudian. Header menandai mode mana yang sedang digunakan database. Versi tulis format file pada offset 18 adalah "1 untuk warisan; 2 untuk WAL."

Aturan praktisnya mengikuti langsung dari kalimat tentang keadaan lengkap. Misalkan database berada dalam mode WAL dan seseorang hanya memberi Anda file utama. Perubahan terbaru yang telah diterapkan mungkin masih berada di log yang tidak Anda terima.

Jadi, ketika Anda diberikan file database, ajukan dua pertanyaan. Apakah aplikasi ditutup dengan bersih saat salinan diambil, dan apakah ada hal lain yang menyertainya? Kedua jawaban biasanya adalah ya, dan satu waktu ketika jawabannya tidak adalah saat angka-angkanya diam-diam tidak sesuai dengan produksi.

File SQLite vs CSV vs Parquet

	File SQLite	CSV	Parquet
Bentuk	Banyak tabel, satu file	Satu tabel, satu file	Satu tabel, satu file atau folder
Tipe	Disimpan bersama data	Disimpulkan oleh pembaca	Disimpan bersama data
Hubungan	Dipertahankan, melalui kunci dan indeks	Hilang	Hilang
Dapat dibaca manusia	Tidak	Ya	Tidak
Ditulis untuk dikueri	Ya, dengan SQL	Tidak	Ya, oleh mesin analitik
Kegagalan umum	Kehilangan file pendamping jurnal atau WAL	Penebakan tipe dan pembatas	Dukungan toolchain

Jika Anda sering bekerja dengan format-format ini, penjelasan kami tentang file Parquet dan file TSV membahas hal yang sama untuk kedua format tersebut.

Mengapa tim memilih format ini

Tidak ada yang perlu dijalankan. Karena SQLite "tidak memiliki proses server yang terpisah," penyerahan data hanyalah berupa penyalinan file, bukan tiket penyediaan (provisioning).

Tipe data tetap bertahan. Kolom tanggal akan tiba sebagai tanggal. Siapa pun yang pernah melihat pembaca CSV mengubah pengidentifikasi menjadi notasi ilmiah pasti memahami nilai dari hal ini.

Hubungan juga bertahan. Beberapa tabel yang saling terkait tetap berada bersama dalam satu artefak, sehingga penggabungan (join) yang membuat data tersebut bermakna tetap tersedia.

Daya tahan sudah dirancang sejak awal. SQLite mencantumkan transaksi "bahkan setelah kehilangan daya" di antara fitur-fitur utamanya, itulah sebabnya begitu banyak perangkat lunak tertanam yang mengandalkannya.

Batasan yang perlu diketahui

Satu file, satu penulis pada satu waktu. Mesin ini tertanam dan bukan dilayani, sehingga model konkurensinya berbeda dari database klien-server. Itu adalah pilihan desain, bukan cacat, tetapi hal itu menentukan kegunaan file tersebut.

Ukuran halaman ditetapkan saat pembuatan. Setiap halaman dalam database memiliki ukuran yang sama, dan ukuran tersebut dicatat dalam header. Anda memilihnya sekali.

Aturan file pendamping lagi. Rutinitas penyalinan, pencadangan, atau pengunggahan apa pun yang hanya mengambil file utama dapat melewatkan apa pun yang ada di dalam jurnal atau log write-ahead.

Ketidakjelasan (Opacity). File SQLite tidak dapat dipindai dengan cepat seperti file CSV. Membacanya memerlukan alat, yang merupakan hambatan yang sering kali menunda banyak analisis.

Cara mendapatkan jawaban dari file SQLite

Cara tradisional adalah dengan menginstal klien, membuka file, mempelajari skema, dan mulai menulis SQL. Itu tidak masalah jika Anda sudah mengetahui tabel-tabelnya. Namun, cara ini lambat jika Anda baru menerima file tersebut pagi ini dan rapat diadakan sore ini.

Cara yang lebih singkat adalah dengan mengajukan pertanyaan secara langsung. Powerdrill Bloom memungkinkan Anda bekerja dengan data Anda menggunakan bahasa alami dan mengembalikan jawaban beserta sumbernya. Halaman beranda menjanjikan bahwa "setiap angka dikembalikan bersama dengan halaman, baris, dan data di baliknya." Dari sana, ruang kerja yang sama dapat menghasilkan bagan, lembar kerja, atau presentasi singkat.

Dua halaman terkait patut diketahui jika ini adalah alur kerja rutin Anda. Chat with Database membahas jalur percakapan ke dalam data terstruktur, dan Text to SQL membahas kasus di mana Anda menginginkan kueri itu sendiri. Jika penyerahan data Anda tiba sebagai ekspor datar, halaman CSV AI assistant membahas jalur tersebut.

Satu hal lagi yang diberitahukan oleh header kepada Anda

Karena ukuran halaman berada pada offset tetap, Anda dapat mempelajari sesuatu yang berguna tentang file sebelum membukanya dengan benar. Database yang dibuat dengan halaman 4.096-byte berperilaku berbeda dari database yang dibuat dengan halaman 65.536-byte. Pilihan tersebut dibuat sekali, saat file dibuat.

Ini bukan angka yang dapat Anda ubah begitu saja setelahnya. Ini termasuk dalam kategori keputusan skema, bukan sekadar pengaturan.

Kesimpulan

File SQLite adalah seluruh database dalam satu artefak. File ini menyimpan tanda tangan 16-byte, ukuran halaman yang dicatat pada offset 16, dan tumpukan halaman berukuran tetap yang membawa tabel dan indeks Anda. File ini mudah dipindahkan, mempertahankan tipe datanya, dan tetap dapat dibaca selama bertahun-tahun.

Ingat satu peringatan yang sangat diperhatikan oleh spesifikasi. Keadaan lengkap biasanya ada di dalam file tersebut. Selama transaksi, sebagian darinya berada di rollback journal atau log write-ahead di sampingnya. Periksa file pendamping sebelum Anda memercayai salinan tersebut.

Ketika Anda memiliki file tersebut dan membutuhkan jawaban alih-alih skema, coba Powerdrill Bloom dan ajukan pertanyaan Anda langsung ke data tersebut.

Pertanyaan yang sering diajukan

Apa perbedaan antara .db, .sqlite, dan .sqlite3?

Tidak ada perbedaan struktural. Ketiganya adalah ekstensi konvensional untuk format yang sama, dan pengidentifikasi aslinya adalah string header 16-byte SQLite format 3\000 di awal file.

Bagaimana cara mengetahui ukuran halaman yang digunakan oleh file SQLite?

Hal itu dicatat dalam header. Integer 2-byte pada offset 16 menyimpan ukuran halaman dalam byte. Ukuran tersebut harus berupa pangkat dua antara 512 dan 32768, atau nilai 1 yang mewakili 65536.

Apakah file SQLite merupakan database yang lengkap?

Biasanya, tetapi tidak selalu. Dokumentasi menyatakan bahwa selama transaksi, SQLite menyimpan informasi tambahan dalam rollback journal. Dalam mode WAL, informasi tersebut dialihkan ke log write-ahead.

Apakah saya bisa membuka file SQLite di Excel?

Tidak secara langsung, karena file tersebut menyimpan halaman B-tree dan bukan baris teks. Jalur yang umum adalah mengekspor tabel ke CSV terlebih dahulu, atau menggunakan alat yang membaca format database dan mengembalikan hasilnya.

Apakah SQLite gratis untuk digunakan secara komersial?

Ya. SQLite menyatakan bahwa kodenya berada dalam domain publik dan "bebas digunakan untuk tujuan apa pun, komersial maupun pribadi."