SQLite: the single-file database for single-tenant, mobile, and edge

What is SQLite?

SQLite is an embedded relational database: no separate server component, no network protocol, no daemon. The whole DB lives as a single file on disk, and the application accesses it directly via a C library. Public domain (technically CC0), developed since 2000 by D. Richard Hipp and a small US team, with the stated mission of delivering a century-stable data format.

SQLite is the most-deployed database in the world -- on every Android and iOS smartphone, in every Firefox and Chrome browser, in every Mac and Windows computer, in nearly every embedded device. In May 2026, version 3.46 is stable. The file format is officially guaranteed compatible until 2050 -- this longevity guarantee is unique in the database market.

Functionally SQLite is SQL-92-compliant with modern extensions: window functions, CTEs, JSON operators, full-text search (FTS5), R-Tree index for geo data, sqlite-vec for vector search (since 2024). What SQLite does not have: user/permission management (a SQLite file has an OS-level owner), concurrent writers (only one process writes to a file at a time), server-based replication. Concurrent readers are unlimited -- WAL mode makes reads parallel to writes.

In our stack, SQLite runs in production at Fairlane (legal case management with 60+ tables, 696 Swiss laws as FTS index) and Realty51 (real-estate portal with 60+ tables, 300+ properties). Both applications are single-tenant from the file perspective -- one server, one app, one DB file. Backup via Litestream to S3-compatible storage, with live WAL replication and RPO under 1 second.

Why it matters

SQLite is often underestimated in the SME world -- as "just a test DB" or "for small apps". That is wrong in 2026. Three reasons why SQLite is a first-class production option.

First: operations effort near zero. No separate DB install, no connection pools, no replication config, no backup daemon. The DB is a file that lives with the application. An app restart sidesteps any DB-daemon crash. A backup is a cp on the file (in WAL mode with sqlite3 .backup command). For an SME with 1-2 IT staff, this is a real advantage -- less operations load, fewer failure modes.

Second: speed. SQLite reads go directly through the OS page cache, without IPC overhead. A SELECT over 100k rows with index is typically back in 1-5 ms. Writes via INSERT/UPDATE in transaction bundles reach 50k-100k ops/sec on NVMe SSD. For 99 percent of SME workloads, that is plenty fast.

Third: file portability. A SQLite DB can be copied between servers via scp. An application can be developed locally (with a local DB file) and run in production (with a server DB file) simultaneously -- without configuration effort. Migration between hosting providers is easy. Disaster recovery is easy: copy the file back, start the app.

Which cases is SQLite NOT suited for? Multi-tenant SaaS with high concurrent write load (e.g. a system with 100+ concurrent writers), multi-server deployments with horizontal load balancing, applications with 24/7 mandatory sub-second failover requirement. In those cases you belong on PostgreSQL or a distributed DB.

How it works

A SQLite database is a single file (default: .db or .sqlite extension). The application opens this file via libsqlite3 and writes SQL commands directly -- no network connection, no TCP stack. Drivers for all common languages: better-sqlite3 (Node, sync, fast), sqlite3 (Python standard library), Microsoft.Data.Sqlite (.NET), rusqlite (Rust).

The most important configuration detail is journal mode. Default is DELETE (rollback journal), but for almost every production use you switch to WAL (write-ahead log):

PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL; PRAGMA temp_store=MEMORY; PRAGMA mmap_size=30000000000; PRAGMA foreign_keys=ON;

WAL mode allows concurrent reads parallel to writes -- the writer does not block readers. mmap_size maps the DB file into memory, which speeds reads noticeably. synchronous=NORMAL is a good compromise between durability and speed; FULL is safer but slower.

A schema example with FTS5 (full-text search) and sqlite-vec (vector search):

CREATE TABLE cases ( id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, content TEXT, created_at TEXT DEFAULT CURRENT_TIMESTAMP );

CREATE VIRTUAL TABLE cases_fts USING fts5(title, content, content='cases', content_rowid='id');

CREATE VIRTUAL TABLE cases_vec USING vec0(embedding float[1536]);

FTS5 is the built-in full-text index, sqlite-vec is a separate extension for embeddings. Both run in the same DB file.

For live replication and cloud backup, Litestream is the standard tool. It runs as a sidecar process next to the application, watches the WAL file, and replicates changes continuously to S3, MinIO, GCS, or Azure Blob. RPO under 1 second, restore via litestream restore -o app.db s3://bucket/app.db. On disaster recovery: new server, Litestream restore, start app -- typically 5-15 minutes.

Limits: SQLite files scale technically up to 281 TB, practically comfortable up to 100-500 GB. Write concurrency is limited to one transaction at a time. Concurrent readers are unlimited. Whoever needs more concurrent writes looks at rqlite (Raft-based SQLite cluster) or LiteFS (Fly.io tool for SQLite replication).

SQLite to production in 5 steps

1. 01Build app with SQLite driver (better-sqlite3, sqlite3, etc), place DB file in a persistent directory.
2. 02Set PRAGMAs: journal_mode=WAL, synchronous=NORMAL, mmap_size to RAM size, foreign_keys=ON. Create indexes on all filter columns.
3. 03Install and configure Litestream: replication to S3-compatible storage (Hetzner Storage Box, Wasabi EU, MinIO), sync interval 1 second.
4. 04Set up backup verification: weekly Litestream restore in staging, data consistency check via sample queries.
5. 05Wire monitoring: application-level metrics via Prometheus (query latency, transaction rate), disk-space alert at > 80 percent.

When to use SQLite

The right choice when (a) an application is single-tenant (one server, one app, one DB), (b) concurrent writes stay under 50 per second, or (c) the effort of a separate DB server stack should be avoided.

Concrete scenarios: a legal case management for a firm with 5-50 lawyers (Fairlane), a real-estate portal app for a local provider (Realty51), a custom-developed fiduciary system for one office, a desktop app like accounting or client management software, an embedded app on Raspberry Pi / industrial PC, a mobile app with offline sync (iOS/Android).

Also interesting: write-light high-read workloads. SQLite with WAL mode and mmap_size easily handles 1000+ reads per second from a file. Wikipedia-style read-mostly wikis, static content with dynamic search, knowledge bases. When 95 percent of requests are reads, the concurrent-write limitation is no issue.

Edge computing: SQLite runs on Cloudflare Workers (via D1), Fly.io (with LiteFS), Turso (distributed SQLite databases), AWS Lambda. For edge workloads with read-mostly load, SQLite is the optimally distributed DB -- the file is copied to edge nodes, reads are local, writes go to a central node.

When not to use

Multi-tenant SaaS with high concurrent write load: when 100+ users write concurrently (e.g. a CRM with 200 salespeople in real time), the single-writer limit becomes a bottleneck. PostgreSQL is right here.

Multi-server deployments with horizontal load balancing: when the app runs on multiple app servers behind a load balancer, the SQLite file becomes a single point of failure. You could distribute with LiteFS or rqlite, but that is operationally heavier than a classic Postgres setup.

Reports and analytics with complex aggregates over tens of millions of rows: SQLite is not columnar, aggregate performance is OK but not outstanding. For reporting on 100M+ rows, DuckDB (also embedded!) is significantly faster -- often 50-100x on pure analytics.

24/7 mandatory sub-second failover: SQLite is not a high-availability system. On a file crash, restore takes minutes, not milliseconds. Anyone needing SLA with RTO under 1 min belongs on Postgres with standby replication or CockroachDB.

Large backups over network: copying a 100 GB SQLite file takes 10-60 minutes depending on network. With Litestream, incremental replication is possible, but initial restore stays slow. For really big DBs (> 500 GB), Postgres with pg_basebackup is better.

Trade-offs

FAQ

Related topics

Sources

1. SQLite official documentation · 2026-05
2. SQLite long-term file format guarantee · 2026-05
3. Litestream documentation -- live SQLite replication · 2026-04
4. sqlite-vec extension GitHub releases · 2026-05