SQLite

What it is

SQLite is a single-file embedded SQL database. There is no server process; the database engine is a C library linked into your application, and the database itself is a single file on disk. D. Richard Hipp released the first version in 2000, and the source code is in the public domain — not MIT, not Apache, not GPL — literally no copyright.

It is, by orders of magnitude, the most widely deployed database in the world. Every Android device, every iOS device, every macOS install, every web browser, and countless desktop apps ship SQLite. Firefox uses it for bookmarks, Apple for CoreData backing, and most “I just need a database” tools default to it.

Why people use it

• Zero ops. No server to run, no port to expose, no users to manage. You open a file and run SQL.
• Atomic, durable transactions over a single file. A SQLite database can be safely copied or backed up by copying the file (with care to flush WAL).
• Public domain. No license terms to comply with. You can embed it in anything, including closed-source commercial products, with no obligation.
• Cross-platform file format. One .db file moves between Linux, macOS, Windows, iOS, Android, and any system with a C compiler.
• Faster than the network. When your “database” is a few MB on local disk, no Postgres setup will ever be faster than SQLite.

When to use SQLite

• Mobile and embedded apps. This is its native use case.
• Application-local state. Browser history, app settings, cache, undo buffers — anything that lives with the app and doesn’t need network access.
• CLI tools and dev tools that need a database.
• Tests. Many teams run a SQLite copy of their schema for fast unit tests.
• Single-writer web apps. Personal projects, internal tools, low-traffic sites. With WAL mode and reasonable schema design, SQLite handles surprising amounts of traffic.
• Static-ish data shipped with the app. A weather app shipping a pre-populated city database, for example.

When not to use SQLite

• Multiple writers concurrently. SQLite serializes writes through a single global lock. High write contention causes contention and write timeouts.
• Distributed systems. It’s single-node by design. Litestream, rqlite, and Turso layer distribution on top, but they make different trade-offs.
• Multi-user web apps with significant write load. Postgres or MySQL exist for a reason.
• When you need network access to the database directly. SQLite is in-process; you can’t connect to it from another machine without an intermediary.
• Fine-grained user and permission management. SQLite has no roles, GRANT/REVOKE, or row-level security.

Notable trade-offs

• Single-writer architecture. WAL mode allows readers and writers to coexist, but only one writer is active at a time. This is fine for many workloads and a hard wall for others.
• Type affinity, not strict types. Until STRICT tables (added 2021), SQLite would coerce data into the declared column type loosely. STRICT mode fixes this; older codebases may not use it.
• No built-in replication. External tools (Litestream, rqlite, Turso, Cloudflare D1) add this with different consistency models.
• File-level locking on network filesystems is dangerous. Don’t put your SQLite database on NFS or SMB without understanding the failure modes.
• Schema migrations are limited. ALTER TABLE supports adding columns but not modifying or dropping them in older versions; you often rewrite the table. (Newer versions improved this.)

Ecosystem

The SQLite ecosystem in 2024–2026 has become a strategic area for edge computing:

• Litestream. Streaming replication to S3-compatible storage. Open source, by Ben Johnson (who joined Fly.io). Lets you treat SQLite as a real production database with disaster recovery.
• rqlite. Distributed SQLite via Raft consensus. Open source.
• Turso. Commercial distributed SQLite-on-the-edge, built on libSQL (an open-source SQLite fork that adds vector search and replication).
• Cloudflare D1. Managed SQLite at the edge, runs in Cloudflare Workers.
• libSQL. Turso’s open-source fork of SQLite with extensions for vector search, native HTTP, and replication.
• chDB / DuckDB. Embedded analytical engines that complement SQLite for analytical workloads — many apps now ship both.