Introduction: SQLite is everywhere (even when you don't notice it)
SQLite is one of those technologies that quietly runs huge parts of the software world, while rarely getting the respect—or skepticism—it deserves. It's not a startup buzzword database, it's not a “cloud-native” platform, and it doesn't come with a dashboard full of graphs to justify a monthly bill. It's a small C library that implements a complete SQL database engine, and it often ships inside your app rather than living as a separate service. If you've ever used a browser, a phone, or a desktop app, you've almost certainly used software that uses SQLite under the hood. That isn't hype; it's a practical consequence of how easy it is to embed and how little operational overhead it requires. Reference: SQLite is explicitly described as an “embedded” database library in its own documentation, and it stores entire databases in a single cross-platform file. Source: https://www.sqlite.org/whentouse.html and https://www.sqlite.org/onefile.html
But let's be brutally honest: SQLite is also commonly misunderstood. People either treat it like a toy (it's not), or they treat it like a drop-in replacement for Postgres/MySQL in every situation (it's not that either). The real value of SQLite is that it removes an entire category of infrastructure problems - until your requirements cross certain lines, at which point it can become a painful bottleneck or a reliability risk. The point of this article is to give you a clear mental model of how SQLite works, what it's genuinely great at, what it's objectively bad at, and how to decide—without ego—whether you should use it in your next project.
What SQLite actually is (and what it isn't)
SQLite is an embedded SQL database engine, delivered as a library that your application links to. That design choice changes everything. Unlike Postgres or MySQL—where the database is a separate process you connect to over a socket—SQLite runs in the same process as your code. The database is typically stored in a single file on disk (though it can also be in-memory), and the engine is responsible for parsing SQL, optimizing queries, handling transactions, locking, and durability guarantees. The “single file database” property is not marketing; it's a core feature documented by SQLite itself, and it's why it's popular for local-first apps, mobile apps, and tooling. Sources: https://www.sqlite.org/onefile.html and https://www.sqlite.org/inmemorydb.html
What SQLite is not is a client/server database with built-in user management, network authentication, or multi-host scaling. SQLite doesn't “listen” on a port; it doesn't ship with a server daemon you manage; it doesn't provide role-based access control the way Postgres does. You can absolutely build systems around it that support concurrency and networking, but you're building those layers yourself. SQLite's own “Appropriate Uses” guidance is blunt: it's excellent for many use cases, but not for scenarios with very high write concurrency or cases where you need multiple machines writing to the same database file. That limitation is rooted in how locking works and how writes are serialized. Source: https://www.sqlite.org/whentouse.html
Why SQLite matters: the operational win is real (and underrated)
The biggest practical advantage of SQLite is operational simplicity—so simple it's easy to dismiss until you've had to babysit a database server at 3 a.m. SQLite eliminates provisioning, version drift across environments, database connection pool tuning, and a lot of “it works locally but not in staging” failures. You can package the database with your application, run it in a CLI tool, ship it inside a desktop app, or drop it into an edge device with minimal resources. That matters because reliability isn't only about the database engine; it's also about all the moving parts you introduce around it. A single local file plus a battle-tested SQL engine can beat a “real” database setup when your workload is small to medium and you care about reducing infrastructure complexity. This is consistent with SQLite's own positioning: it's designed to be small, fast, reliable, and self-contained. Sources: https://www.sqlite.org/about.html and https://www.sqlite.org/whentouse.html
There's also a performance angle that surprises people: for many workloads, SQLite is extremely fast because there's no network hop and no server process boundary. Your app calls into a library, and reads/writes a file using the OS. That doesn't mean “SQLite is faster than Postgres” in general—because Postgres wins in many concurrent and server-heavy scenarios—but it does mean that for a single-user or low-concurrency app, SQLite can feel instantly responsive. The brutality here is that teams often choose Postgres by default not because they need it, but because it feels like the “serious” choice. If your product doesn't need multi-user write concurrency, database-level access controls, or heavy server-side analytics, SQLite can be the more professional decision precisely because it reduces the system's surface area.
How SQLite handles concurrency (this is the deal-breaker for many apps)
SQLite can handle multiple readers at the same time, but writing is fundamentally more constrained. In simple terms, a single database file is a shared resource, and SQLite must coordinate access using locks to preserve correctness. Historically, SQLite's default rollback journal mode meant writers could block readers and vice versa. SQLite later introduced Write-Ahead Logging (WAL), which improves read/write concurrency by allowing readers to proceed while a writer appends changes to a WAL file, then checkpoints those changes back into the main database file. WAL is real, documented, and widely used because it makes SQLite far more pleasant under mixed workloads. Source: https://www.sqlite.org/wal.html
Here's the brutally honest part: WAL helps, but it doesn't turn SQLite into Postgres. SQLite still allows only one writer at a time, and if your app has many concurrent write requests—especially across threads/processes—you'll hit “database is locked” errors or latency spikes unless you design carefully. That might be fine for a mobile app (one user), a desktop app, a small internal tool, or a service with mostly reads and occasional writes. It becomes painful for high-traffic APIs where lots of requests attempt inserts/updates concurrently. SQLite's own guidance explicitly warns against high-concurrency write-heavy server use cases. Source: https://www.sqlite.org/whentouse.html
Practical guidance: if you're using SQLite in a server, treat it like a shared file with transactional semantics, not like an infinitely scalable server. Use short transactions, avoid long-running writes, batch inserts, and be realistic about throughput. If you need multiple instances of your web service writing to the same database from different machines, SQLite is usually the wrong fit—because a single database file on a shared network filesystem is a recipe for corruption or locking nightmares unless you deeply understand the storage guarantees (and even then, it's risky). SQLite explicitly cautions about network filesystems. Source: https://www.sqlite.org/faq.html#q5
SQLite vs Postgres/MySQL: the honest comparison that matters in real projects
The SQLite vs Postgres/MySQL debate gets polluted by ideology. The correct comparison is not “which database is better,” but “which failure modes do you prefer.” Postgres/MySQL give you a server process built to coordinate many clients, manage concurrent writes, provide authentication/authorization, and support robust replication and backups in production patterns that are widely understood. They also introduce operational overhead: migrations across environments, connection pool saturation, tuning, networking issues, and the need for observability. SQLite removes most of that overhead, but in exchange you accept different constraints: single-node storage, limited write concurrency, and fewer built-in “platform” features. SQLite's own “Appropriate Uses” page practically reads like a checklist for this tradeoff. Source: https://www.sqlite.org/whentouse.html
Feature-wise, SQLite supports a large subset of SQL, ACID transactions, indexes, triggers, and views. It does not have some features people rely on in Postgres, and its type system is famously different: SQLite uses dynamic typing with “type affinities,” which can be a benefit (flexibility) or a footgun (unexpected data quality issues) depending on your discipline. This is documented in SQLite's typing documentation. Source: https://www.sqlite.org/datatype3.html. Postgres, in contrast, is strongly typed and has advanced features like rich indexing types, strong concurrency controls (MVCC), and mature extension ecosystems. That doesn't automatically make Postgres the right default; it just means you should choose it when you genuinely need those properties.
A useful rule: if your application is fundamentally single-user (or “single writer”) and you value shipping fast with minimal ops, SQLite is usually a strong choice. If your application is fundamentally multi-user with frequent writes, needs database-level permissions, or you expect to scale horizontally with multiple app instances writing concurrently, Postgres/MySQL are usually the safer choice. It's not about prestige; it's about physics and operating constraints.
When SQLite is the best choice (real-world use cases that aren't gimmicks)
SQLite shines when the database is part of the product rather than a separate service. Think: mobile apps, desktop apps, browser-related tooling, embedded devices, offline-first applications, and developer tools. In these contexts, a single-file database is not just convenient—it's a distribution strategy. You can back it up by copying a file (with caveats about doing so safely), you can inspect it with standard tools, and you can ship it with test fixtures that behave the same across machines. SQLite explicitly supports in-memory databases for testing and ephemeral workloads, which is another reason it's popular in CI and local development. Sources: https://www.sqlite.org/inmemorydb.html and https://www.sqlite.org/backup.html
SQLite is also excellent as an “edge cache” or local read model. A service might use Postgres as the system of record, but still use SQLite locally inside a worker, CLI, or agent to store state, queue work, or cache data. That hybrid pattern is underrated because it avoids overloading your main database with small coordination writes. The brutal truth: many systems end up reinventing a fragile file-based state store when they could have used SQLite with transactions and queryability. If you need a reliable local store that can survive crashes and power loss (assuming proper configuration and a sane filesystem), SQLite is a mature option with decades of production use.
When you should not use SQLite (the “it will hurt later” scenarios)
If you're building a multi-tenant SaaS where hundreds or thousands of users are writing concurrently, SQLite can become an obstacle rather than an accelerator. You can mitigate some pain with WAL mode, careful transaction boundaries, and by funneling writes through a single process, but those are architectural constraints you wouldn't accept if you started with a client/server database. SQLite is also a poor fit when you need robust built-in replication, failover, and high availability across machines. Those concerns aren't “enterprise fluff”; they become urgent the moment your database is mission-critical and downtime costs money. SQLite doesn't ship with those capabilities because it's not designed to be a distributed server. Source: https://www.sqlite.org/whentouse.html
Another honest reason to avoid SQLite is organizational: if your team already has a mature Postgres platform, tooling, and operational playbooks, then “no ops” isn't a benefit—it's a deviation. Conversely, if your team has no database expertise, SQLite can be safer because it removes a category of production failures, but it can also hide problems until load increases. Finally, be cautious when your app runs on a network filesystem (NFS, SMB) or other storage with unclear POSIX locking guarantees. SQLite's FAQ explicitly warns that network filesystems can cause database corruption due to broken locking semantics, and it advises against it unless you know what you're doing. Source: https://www.sqlite.org/faq.html#q5
Practical setup: a small SQLite example that won't betray you
A lot of SQLite misuse comes from sloppy defaults: long transactions, no busy timeout, and naive concurrency assumptions. A practical baseline is: enable WAL (where appropriate), set a busy timeout, and keep write transactions short. Below is a minimal example in Python using the built-in sqlite3 module (no external dependency). The Python standard library includes sqlite3, which wraps SQLite, making it a realistic example for many apps. Source: Python docs for sqlite3: https://docs.python.org/3/library/sqlite3.html
import sqlite3
from contextlib import closing
DB_PATH = "app.db"
def init_db():
with closing(sqlite3.connect(DB_PATH)) as conn:
conn.execute("PRAGMA journal_mode=WAL;") # better read/write concurrency
conn.execute("PRAGMA foreign_keys=ON;")
conn.execute("PRAGMA busy_timeout=5000;") # wait up to 5s if locked
conn.execute("""
CREATE TABLE IF NOT EXISTS events (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
""")
conn.commit()
def add_event(name: str):
with closing(sqlite3.connect(DB_PATH)) as conn:
conn.execute("PRAGMA busy_timeout=5000;")
# keep transactions short
conn.execute("INSERT INTO events(name) VALUES (?);", (name,))
conn.commit()
def list_events(limit: int = 10):
with closing(sqlite3.connect(DB_PATH)) as conn:
cur = conn.execute(
"SELECT id, name, created_at FROM events ORDER BY id DESC LIMIT ?;",
(limit,)
)
return cur.fetchall()
if __name__ == "__main__":
init_db()
add_event("sqlite-wal-basics")
print(list_events())
If you run this in a web server with multiple workers, you'll still need to be careful. SQLite will not magically handle unlimited concurrent writes just because you enabled WAL. WAL improves the common case, but the “one writer at a time” rule still exists. Treat SQLite as a strong local database, not as a shared write hub for a distributed system.
80/20: the small set of insights that prevents 80% of SQLite mistakes
Most SQLite failures don't come from “SQLite being bad.” They come from teams using it as if it were Postgres, and then being surprised by file-based realities. The 80/20 is straightforward: (1) decide whether your workload is single-node and mostly single-writer; (2) keep write transactions short; (3) use WAL when mixed reads/writes matter; (4) don't put the database on flaky network storage; and (5) test under realistic concurrency, not just happy-path unit tests. Each of those points is grounded in SQLite's own documentation about appropriate use and WAL behavior. Sources: https://www.sqlite.org/whentouse.html and https://www.sqlite.org/wal.html
If you do just those few things, SQLite becomes boring—in the best way. It will sit there and do its job without requiring a “database team.” Ignore them, and you'll end up building a mess of retries, timeouts, and mystery lock errors that you'll incorrectly blame on the tool. The brutally honest takeaway is that SQLite doesn't forgive architectural mismatches. But if your architecture fits, it's one of the most pragmatic database choices you can make.
Five key actions: how to decide in 10 minutes
Start with the decision, not the implementation. Ask: “Will this app need many concurrent writes from many users?” If yes, default to Postgres/MySQL unless you have a strong reason not to. If no, SQLite is likely viable and may be the better choice. Next, evaluate deployment: if you want to ship a single binary/container with minimal dependencies, SQLite aligns with that goal. Then consider operational maturity: if you don't have monitoring, backups, and on-call coverage for a database server, SQLite reduces risk—again, within its concurrency limits. All of these are consistent with the official “when to use SQLite” guidance. Source: https://www.sqlite.org/whentouse.html
Concrete steps:
- Define expected write concurrency (numbers, not vibes).
- If you pick SQLite, plan for WAL + short transactions + busy timeout.
- Decide where the database file lives (local disk, not network share).
- Load test with realistic concurrency and data sizes.
- Document the “migration plan” to Postgres if growth demands it—because sometimes it will.
Conclusion: SQLite is a professional choice—when it matches your constraints
SQLite is not a toy database, and it's not a universal database. It's a carefully engineered embedded SQL engine optimized for reliability, simplicity, and local storage. That makes it incredibly powerful for the right class of applications: single-user, offline-first, embedded, edge, developer tooling, and any product where “no separate database server” is a major advantage. Those claims are not opinion; they align with SQLite's own documentation and the design goals of the project. Sources: https://www.sqlite.org/about.html and https://www.sqlite.org/whentouse.html
The brutally honest advice is to stop treating database choice as identity. Use SQLite when you want fewer moving parts and your write concurrency is naturally limited. Use Postgres/MySQL when you need multi-user concurrency, server features, and scaling patterns that SQLite is not designed to provide. If you can articulate your workload clearly, the decision becomes obvious—and you won't end up rewriting your persistence layer under pressure. SQLite doesn't need hype. It needs the right job.