Backend4 min read

SQLite

Use SQLite effectively for embedded, edge, and serverless databases - WAL mode, concurrent reads, Turso libSQL, and SQLite in production.

SQLiteDatabaseEmbeddedEdge DatabaselibSQL

skill: SQLite category: Backend tags: [SQLite, Database, Embedded, Edge Database, libSQL] readingTime: 4

SQLite is the most deployed database in the world - and it's dramatically underused in web development. A single SQLite file handles tens of thousands of reads per second and thousands of writes per second. For applications that run on a single server or at the edge, SQLite beats PostgreSQL on simplicity and often on performance.

Workflow

Configure SQLite for productionDefault SQLite settings are conservative - WAL mode, larger cache, and PRAGMA tweaks unlock production performance.
Use better-sqlite3 for synchronous Node.jsbetter-sqlite3 is synchronous - no await, no callback hell, simpler code. Synchronous SQLite is faster than async because it avoids event loop overhead.
Run SQLite at the edge with Turso/libSQLTurso extends SQLite with replication - your SQLite database is globally distributed across edge locations with sub-millisecond reads from any edge server.
Query JSON with SQLite JSON functionsSQLite has built-in JSON functions - store flexible data as JSON columns without sacrificing queryability.
Embed full-text search with FTS5SQLite FTS5 provides BM25 full-text search without Elasticsearch infrastructure - perfect for documentation search, note apps, and product search at small scale.

Output Format

SQLite: WAL mode + production pragmas, better-sqlite3 with prepared statements and transactions, Turso for edge, JSON functions, and FTS5 search.

pragma(journal_mode=WAL)
db.prepare().run() / .get() / .all()
db.transaction(() => { multiple writes })
Turso: @libsql/client with authToken
FTS5: virtual table + bm25() ranking

Examples

Input

Output

Edge Cases & Gotchas

WAL mode and multiple processes - WAL mode works with multiple readers from the same process. Multiple OS processes writing to the same SQLite file will deadlock - use a single writer process or Turso/libSQL.
File locking on network storage - SQLite file locking does not work on NFS, SMB, or Docker volumes - use a local filesystem or Turso for multi-node deployments.
Integer affinity for dates - SQLite has no DATE type. Store dates as ISO 8601 text or Unix timestamps. Use datetime() functions for date arithmetic.
SQLite does not support DROP COLUMN in older versions - SQLite 3.35.0+ supports DROP COLUMN. Older versions require create-new-table + insert + rename workaround.

When to Break These Rules

Use PostgreSQL when you need concurrent multi-process writers, row-level security, logical replication, or PostGIS geography - features SQLite doesn't provide.
Use SQLite + Turso for edge databases but switch to PlanetScale (MySQL) or Neon (PostgreSQL) when you need database branching for development workflows.

Reference Files

https://www.sqlite.org/wal.html
https://docs.turso.tech/