SQLite on mobile, desktop and in the browser

Bundling and upgrading on Android and iOS, SQLCipher encryption, desktop packaging, and WASM builds with OPFS persistence.

Mobile platforms

// Android: Room over the framework SQLite, or the bundled driver
@Database(entities = {Book.class}, version = 3, exportSchema = true)
public abstract class AppDatabase extends RoomDatabase {
    public abstract BookDao bookDao();
}

Room.databaseBuilder(context, AppDatabase.class, "app.db")
    .addMigrations(MIGRATION_2_3)
    .setJournalMode(RoomDatabase.JournalMode.WRITE_AHEAD_LOGGING)
    .build();

// WAL leaves app.db-wal and app.db-shm beside the file:
// copy all three when you move or back up the database

// iOS: the system libsqlite3, or GRDB / SQLite.swift as a wrapper
let dbQueue = try DatabaseQueue(path: dbPath)
try dbQueue.write { db in
    try db.execute(sql: "pragma journal_mode = wal")
    try db.execute(sql: "pragma foreign_keys = on")
}

// the file lives in Application Support, not Documents,
// unless the user is meant to see and manage it

• The system SQLite on a phone is old and differs by OS version. If you need FTS5 options, JSONB or STRICT tables, bundle your own library instead of relying on the platform.
• WAL means three files. A backup that copies only the main file loses the most recent transactions, or ships an inconsistent database.
• iCloud or Android auto-backup of a live database can capture a torn file. Back up with the backup API (sqlite3_backup or VACUUM INTO) into a clean copy, then let the platform back that up.
• Migrations run on devices you cannot reach. Never drop user data in a migration; keep a copy until the new code has been live for a release.
• Use pragma integrity_check after an upgrade and on startup when the file was restored from a backup.

Encryption and desktop packaging

-- SQLCipher is a drop-in build with page-level encryption
pragma key = 'x''2DD29CA8...';          -- or a passphrase
pragma cipher_page_size = 4096;
pragma kdf_iter = 256000;                -- key derivation iterations
pragma cipher_hmac_algorithm = HMAC_SHA512;
pragma cipher_kdf_algorithm = PBKDF2_HMAC_SHA512;

-- verify the key is correct before using the database
select count(*) from sqlite_master;      -- fails if the key is wrong
pragma rekey = 'new passphrase';         -- change the key in place

• A plain SQLite file is readable by anything with file access, including a stolen laptop or a rooted device. If the data is sensitive, encrypt the file.
• Store the key in the platform keystore (Keychain, Android Keystore, DPAPI) rather than in the application binary, which anyone can decompile.
• Encryption protects the file at rest. It does not protect against a process that can read the key from memory.
• For desktop packaging, ship the database in a per-user application data directory, not beside the executable - the program directory may be read-only.
• sqlite3_backup into a new file, then VACUUM, is the safest way to produce a consistent copy while the application keeps running.

-- a consistent copy of a live database, without the WAL sidecar problems
vacuum into '/tmp/app-backup.db';

-- and integrity-check the copy before shipping it anywhere
pragma integrity_check;

SQLite in the browser

import initSqlJs from "sql.js";

// sql.js: the database lives in memory, persistence is your job
const SQL = await initSqlJs({ locateFile: (f) => "/wasm/" + f });
const db = new SQL.Database();
db.run("create table note (id integer primary key, body text)");
db.run("insert into note (body) values (?)", ["hello"]);
const bytes = db.export();                 // an ArrayBuffer to store yourself

// wa-sqlite or the official WASM build with OPFS: real persistence
const sqlite3 = await sqlite3InitModule();
const db2 = new sqlite3.oo1.OpfsDb("/mydb.sqlite3");   // origin private file system
db2.exec("create table if not exists note (id integer primary key, body text)");

• The OPFS synchronous access handles need a worker thread and cross-origin isolation (COOP and COEP headers). Without them you fall back to a slower asynchronous VFS.
• Browser storage can be evicted by the browser under pressure unless the origin is persistent. Request persistence and handle the case where it is denied.
• Do not store data only in the browser. A user clearing site data, or switching devices, loses everything unless you sync to a server.
• The same SQL runs in the browser and on the server, which is genuinely useful: a query can run offline and later be replayed against the canonical database.

FAQ

Related

WAL mode, backups, and when not to use SQLite LiteFS, libSQL and replication for serverless apps

Last refreshed 2026-09-18.