SQLite & Local-First Apps
Build local-first apps with SQLite: embedded database patterns, wa-sqlite in the browser via OPFS, sync strategies, and conflict resolution.
Local-first apps store data on the user's device and sync with the server when connected. SQLite is the ideal embedded database - zero-config, fast, and available on every platform. Modern tooling (wa-sqlite, electric-sql, Turso) brings SQLite to browsers, edge functions, and distributed systems.
Local-first UX is a competitive differentiator - apps that work offline feel fast and reliable. Use SQLite for the local store. Design sync around CRDTs or operation logs to handle conflicts. Start with optimistic updates and last-write-wins for simple cases.
Workflow
- Use SQLite for embedded local storageSQLite is the world's most deployed database - zero configuration, single-file, ACID-compliant, and fast for local storage.
- Run SQLite in the browser via OPFSBrowser SQLite with OPFS gives web apps the same embedded database capabilities as native apps.
- Design optimistic updatesOptimistic updates make the app feel instant - changes apply locally first, sync in the background.
- Handle sync and conflicts with electric-sqlelectric-sql provides two-way sync between PostgreSQL and SQLite (client) with automatic conflict resolution.
- Deploy SQLite at the edge with TursoTurso provides globally distributed SQLite - one primary with many read replicas, or embedded replicas on edge nodes.
Output Format
better-sqlite3 or Bun SQLite for Node.js, wa-sqlite + OPFS for browser, outbox pattern for sync queue, electric-sql or Turso for automatic sync, WAL mode for concurrent reads.
Examples
Bun SQLite: `const db = new Database(":memory:"); db.run("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT UNIQUE)"); const insert = db.prepare("INSERT INTO users (name, email) VALUES (?, ?)"); insert.run("Alice", "alice@example.com"); const users = db.query("SELECT * FROM users").all()`OPFS SQLite: `// In a SharedWorker or Service Worker (OPFS sync API only available in workers): const db = new sqlite3.oo1.OpfsDb("/app.db"); db.exec("CREATE TABLE IF NOT EXISTS tasks (id TEXT PRIMARY KEY, title TEXT, done INTEGER DEFAULT 0, updated_at INTEGER)"); db.exec("INSERT INTO tasks VALUES (?,?,?,?)", { bind: [crypto.randomUUID(), "Buy groceries", 0, Date.now()] })`Outbox sync: `async function writeWithSync(db: Database, data: Todo) { db.transaction(() => { db.run("INSERT OR REPLACE INTO todos VALUES (?,?,?,?)", [data.id, data.text, data.done, Date.now()]); db.run("INSERT INTO outbox (id, operation, payload) VALUES (?,?,?)", [crypto.randomUUID(), "upsert", JSON.stringify(data)]) })(); queueMicrotask(() => syncOutbox(db)) }`Edge Cases & Gotchas
- SQLite concurrency: SQLite has a single-writer model. In multi-process setups (multiple Node.js workers), use WAL mode and handle `SQLITE_BUSY` errors with retry. For high-write scenarios, consider a separate write queue
- WASM bundle size: sqlite-wasm WASM binary is ~1.5MB. Use dynamic import to lazy-load: `const { default: init } = await import("@sqlite.org/sqlite-wasm")`. Cache in a service worker after first load
- Migration in local-first: schema migrations are tricky - each device may be at a different schema version. Use a migration version table and apply migrations sequentially on startup
When to Break These Rules
- For collaboration-heavy apps (Google Docs-style real-time co-editing), SQLite + sync is insufficient. Use a purpose-built CRDT library (Automerge, Yjs) with WebSocket for real-time collaboration.
Reference Files
wa-sqlite: https://github.com/rhashimoto/wa-sqliteelectric-sql: https://electric-sql.com/Turso: https://turso.tech/SQLite WASM: https://sqlite.org/wasm/doc/trunk/index.mdLocal-first guide: https://www.inkandswitch.com/local-first/