SQLite — A Single-File DB for Local Apps
SQLite — A Single-File DB for Local Apps
SQLite is an embedded database that holds a SQL database in a single file. It is one of the most frequently chosen options for local storage in desktop and mobile apps.
1. About SQLite
SQLite is a library D. Richard Hipp first released in May 2000. It is written in C and distributed in the public domain (copyright intentionally relinquished). It has a separate CLA process for accepting contributions.
The official site describes itself as "the most used database engine in the world." Citations include being embedded in iOS, Android, Windows 10, macOS, most browsers, aviation systems, automobiles, and mobile phones.
Because the license is public domain, distribution has almost no restrictions. For organizations that need commercial support, the SQLite Consortium and a paid license option exist separately.
2. Single-file model
One DB = one file (my.db). All tables, indexes, and triggers live inside it. Unlike a client-server model where a separate process runs (PostgreSQL, MySQL), the library operates inside the host process.
Consequences of this model:
- Almost no setup — the host only needs to open the file.
- Backup is simple — copy the file (a live DB, however, requires lock consideration).
- No network latency — function calls in the same process.
- Multiple processes writing — possible but with lock contention. The single-writer + multiple-readers pattern is the most natural.
3. Journal mode and WAL
To preserve consistency even when a write is interrupted by a crash, SQLite writes a journal. Two modes are common.
- rollback journal (default) — back up the pre-change page to a separate file. Delete after the transaction completes.
- WAL (Write-Ahead Logging, 3.7.0 / 2010) — append changes to a separate
.db-waland merge into the main file periodically (checkpoint).
WAL benefits:
- Reads and writes can happen concurrently (readers see a snapshot before WAL applies).
- Generally faster writes.
Activation is one line:
PRAGMA journal_mode = WAL;
WAL relies on the synchronization correctness of the underlying file system. It is not recommended on some network file systems (NFS) (officially documented).
4. Concurrency and types
SQLite's concurrency model is simple.
- Shared lock (SHARED) — multiple readers concurrently.
- Reserved lock (RESERVED) — reservation for the next writer.
- Exclusive lock (EXCLUSIVE) — actual write.
Under WAL mode, readers and writers do not contend on the same lock, so concurrency is better.
SQLite has a loose type model called type affinity. Even when you declare a column type, values of other types may be stored.
CREATE TABLE t (id INTEGER, name TEXT);
INSERT INTO t VALUES ("hello", 42); -- passes
From 3.37.0 (2021), the STRICT option was added to enable strict type checking.
CREATE TABLE t (id INTEGER, name TEXT) STRICT;
5. Extensions
- JSON1 — built-in. Store, index, and query JSON values.
- FTS5 — full-text search.
- R*Tree — spatial index.
- vector / sqlite-vec — vector search (external extension, 2024).
All of this works inside a single file.
6. Integrating with Tauri
Two paths to use SQLite from Tauri.
tauri-plugin-sql — official plugin. Uses Rust's sqlx to access SQLite/MySQL/Postgres through the same interface. Send SQL strings from JS.
import Database from "@tauri-apps/plugin-sql"
const db = await Database.load("sqlite:app.db")
await db.execute("CREATE TABLE IF NOT EXISTS food (id INTEGER PRIMARY KEY, name TEXT)")
await db.execute("INSERT INTO food (name) VALUES ($1)", ["김치"])
const rows = await db.select<{id: number; name: string}[]>("SELECT * FROM food")
rusqlite directly — touch SQLite only inside Rust code and expose only domain functions to JS. Type safety and performance are better, but more code is needed.
#[tauri::command]
fn add_food(name: String, db: tauri::State<DbPool>) -> Result<i64, String> {
let conn = db.0.get().map_err(|e| e.to_string())?;
conn.execute("INSERT INTO food (name) VALUES (?1)", [&name])
.map_err(|e| e.to_string())?;
Ok(conn.last_insert_rowid())
}
7. File location conventions
The standard is to place the DB in each OS's user data directory.
- Windows:
%APPDATA%\<app-id>\(C:\Users\Alice\AppData\Roaming\com.example.app\) - macOS:
~/Library/Application Support/<app-id>/ - Linux:
~/.local/share/<app-id>/(XDG) - Android:
/data/data/<package>/files/ - iOS:
Library/Application Support/in the app sandbox
Tauri's app.path().app_data_dir() abstracts this.
8. Backup and sync patterns
Simply copying the live DB file may catch it mid-transaction, with corruption risk. Safe paths:
VACUUM INTO 'backup.db'— produces a consistent copy of the live DB.- Backup API (
sqlite3_backup_*) — page-level copy from a live DB to another DB.
9. Browser and local storage candidates
| Storage | Location | Capacity | Notes |
|---|---|---|---|
localStorage |
browser | about 5~10 MB | sync API, strings only, small settings. |
sessionStorage |
browser | similar | tab-scoped lifetime. |
| IndexedDB | browser | hundreds of MB to several GB (quota) | async, object storage, indexes. Weak query expression. |
| OPFS | browser | large | file-level API. Combine with WASM SQLite. |
| WASM SQLite | browser | on top of OPFS | official. Real SQLite inside the browser. |
| SQLite (native app) | host filesystem | disk limit | the topic of this article. |
| Dexie.js | on top of IndexedDB | familiar API wrapper. |
For web apps, IndexedDB or WASM SQLite; for desktop and mobile, native SQLite is the natural choice.
10. Recommended PRAGMAs
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA temp_store = MEMORY;
synchronous = NORMAL is a trade-off between performance and durability. Places like financial data are safer with FULL.
11. Common pitfalls
Multiple processes writing — only one writer at a time. Attempting while another process is writing yields SQLITE_BUSY. Serialize via busy_timeout or a queue.
Thread safety — SQLite has different thread-safe modes depending on compile options. rusqlite enforces that connection objects cannot be shared across threads (Send + !Sync). Go through a pool (r2d2) or a channel.
WAL companion files — when backing up db.db, db.db-wal and db.db-shm must come along too. Using VACUUM INTO to produce a single file is safer.
Network filesystems — environments like NFS have different lock semantics. Corruption is reported, and they are officially discouraged.
Looseness of types — tables created without STRICT accept values of wrong types. New tables should use STRICT.
Shared directories — placing a live DB inside a cloud sync folder (OneDrive, iCloud, Dropbox) risks corruption when sync collides with locks.
Emojis and surrogates — SQLite handles UTF-8, but characters outside BMP (emojis) cut at wrong positions can lead to corruption. Normalize at input.
Closing thoughts
SQLite is the simplest answer for a local app. Single file + standard SQL + public domain license — these three overwhelm the alternatives. Enabling WAL mode + STRICT tables + foreign_keys ON at the start makes operational burden almost zero.
Next
- ocr-stt-tts
- native-integrations
We refer to SQLite official, SQLite About, Most Widely Deployed, WAL Mode, Datatype In SQLite, tauri-plugin-sql, rusqlite, sqlx, SQLite WASM, and Dexie.js.