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-wal and 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.