SQLite: when to use it (and when not to)

The architecture meeting ran two hours. The team debated PostgreSQL versus MySQL. Someone brought up Aurora Serverless. Another person mentioned CockroachDB "just to have it on the table." Nobody said SQLite — it felt too amateur. The project was an ETL script that ran once a day and needed to join two CSV files. SQLite would have solved it in ten minutes.

This post isn't about when SQLite is "good enough." It's about when SQLite is the right choice — and about the cases where you'll regret ignoring its real limits.

SQLite is not a toy database

It's the most widely deployed database in the world. It's in your iPhone, your Android, Firefox, Chrome, Slack desktop, VSCode, Photoshop. The estimate is over one trillion active instances. The official documentation explicitly recommends production use — with clear caveats on when not to.

The fundamental difference from Postgres or MySQL is architectural: SQLite has no server. No separate process, no TCP connection, no network authentication. It's a library that reads and writes directly to a file. Your application links the library, opens the file, and runs normal SQL queries.

import sqlite3

conn = sqlite3.connect("my_database.db")
cursor = conn.execute("SELECT * FROM orders WHERE status = 'pending'")
rows = cursor.fetchall()

This isn't a hidden limitation. It's intentional design with real advantages in many contexts.

Where SQLite beats Postgres without debate

Desktop and mobile applications

Any app that needs local persistence and runs on a controlled device — desktop, mobile, terminal — SQLite is the natural choice. No server to configure, no port to open, no service to keep running. The database is the file.

VSCode stores extensions, history, and state in SQLite. iOS uses SQLite for contacts, calendar, and messages. Not because Apple lacks the infrastructure to run Postgres — but because for local device data, a SQLite file is strictly better.

Integration tests

Standing up a Postgres database per test suite requires Docker, or a running server, or CI setup with service containers. With SQLite in memory, it's one line:

conn = sqlite3.connect(":memory:")

The database exists, accepts the full schema, runs queries, and disappears when the process ends. Zero disk I/O, zero shared state between tests, zero cleanup race conditions.

For most cases where you're testing repository logic — not Postgres-specific dialect compatibility — in-memory SQLite is faster, simpler, and more isolated than any alternative.

Edge computing and CDN workers

The most recent SQLite wave has been at the edge. Cloudflare D1, Turso, and Fly.io's LiteFS put SQLite into distributed production — with read replicas in multiple regions and centralized writes. The logic is simple: if a database fits in a file, you can replicate it geographically without the complexity of a Postgres cluster.

Turso measures read latency in microseconds on local replicas. For read-heavy workloads with centralized writes, it's an architecture that makes more sense than routing every read to a Postgres instance in us-east-1.

CLI tools and processing scripts

If you're building a command-line tool that needs persistence — result cache, execution history, local index — SQLite is the right format. More structured than JSON, more queryable than CSV, lighter than a server.

# SQLite as a queryable data format
sqlite3 data.db "SELECT city, COUNT(*) FROM customers GROUP BY city ORDER BY 2 DESC"

You can distribute the .db file as an artifact, open it in DB Browser for SQLite, query it from any language. It's a data format with SQL on top.

The real limits — where not to use it

SQLite is explicit about its anti-cases. The documentation lists:

High write concurrency. SQLite uses file-level locking. When one process writes, all others trying to write are blocked. With WAL mode (Write-Ahead Logging), reads don't block writes — but writes still serialize. If you have dozens of workers writing simultaneously, you'll accumulate contention and timeouts.

# This can work with WAL mode
conn = sqlite3.connect("database.db")
conn.execute("PRAGMA journal_mode=WAL")

# But ten workers writing simultaneously will still serialize

Data that belongs on the network, not a local disk. If the database needs to be accessed by multiple servers simultaneously, SQLite on a network filesystem (NFS, EFS, SMB) is a recipe for corruption. File locking over a network is unreliable. In that scenario, you need a server-based database — Postgres, MySQL, whatever fits.

Advanced types and validations. SQLite has a permissive type system by design. You can insert text into an INTEGER column without an error. For systems that depend on strict type constraints, complex check constraints, or custom types (like Postgres's JSONB, UUID, INET), you'll feel the difference.

High concurrent write volume in a web service. A web server receiving a thousand simultaneous writes to the same SQLite will have problems. It's not hypothetical — it's the documented limit. For those workloads, Postgres exists for good reasons.

WAL mode: what changes in practice

SQLite's default mode uses a rollback journal. WAL (Write-Ahead Logging) is an alternative that significantly improves concurrent read performance:

conn.execute("PRAGMA journal_mode=WAL")
conn.execute("PRAGMA synchronous=NORMAL")  # tradeoff: faster, slightly less durable

With WAL, readers don't block the writer and the writer doesn't block readers. Serialization still exists between writers, but total throughput improves substantially for read-heavy workloads.

For desktop applications and scripts, enabling WAL mode and tuning cache_size resolves most SQLite performance complaints.

SQLite in production: the real criterion

The right question isn't "is it big enough to deserve Postgres?" — it's "is the database local to the application, or does it need to be accessed over the network?"

If the database lives in the same process or on the same machine as the application, and writes aren't highly concurrent, SQLite is a legitimate production choice. SQLite has decades of uptime, zero external dependencies, trivial backup (copy the file), and a complete SQL API.

If the database needs to be shared across multiple servers, or if concurrent write volume is high, use Postgres or MySQL. Not because SQLite is inferior — but because those are the cases it was explicitly not designed to solve.

This criterion is different from the SQL versus NoSQL discussion — if you want to understand when a relational database makes sense compared to document or key-value alternatives, I wrote about that in relational vs NoSQL databases.

SQL queries work regardless of the database

Something SQLite makes clear: SQL doesn't belong to any specific database. Most queries you write for Postgres work in SQLite with minor adjustments — and vice versa. Before running any query in production, I format and review it in the SQL Formatter, which supports multiple dialects and makes the logic readable before anything else.

Frequently asked questions

Is SQLite production-ready or just for development?

It's production-ready — with conditions. The official SQLite documentation explicitly lists production use cases: desktop applications, mobile, IoT, low-to-medium traffic websites (under ~100k requests/day in most cases), and any system where the database is local to the application. The criterion is write concurrency, not project size.

What's the SQLite database size limit?

The theoretical limit is 281 TB. In practice, SQLite databases in the hundreds of gigabytes work fine for reads. The real bottleneck isn't size — it's write concurrency. A 50 GB database with heavy reads and occasional writes will perform better in SQLite than a 5 GB database with 200 concurrent writes per second.

Does SQLite support ACID transactions?

Yes, fully. Atomicity, consistency, isolation, and durability are all implemented. SQLite was designed to be correct before being fast — ACID properties are guaranteed even in the event of a process crash or power loss (with synchronous=FULL, which is the default).

How do I back up SQLite in production?

# Online copy without locking the database (SQLite 3.27+)
sqlite3 production.db ".backup backup_$(date +%Y%m%d).db"

Backup is a file copy. For critical production systems, automate with WAL mode and online backup — no need to stop the application.

SQLite isn't a choice made by someone who doesn't know the options

It's a choice made by someone who knows enough not to use a bazooka where a knife works.

The tendency to inflate infrastructure because "you'll need scale someday" creates systems that are more complex, more expensive, and harder to operate — with no real benefit while the volume doesn't justify it. For scripts, CLI tools, desktop apps, tests, edge workers, and systems with low writes and high reads, SQLite is the right tool.

For a web service with multiple servers and concurrent writes, it isn't. That tradeoff isn't an opinion — it's in the official documentation, and whoever ignores it will hit the limit at the worst possible moment.