There's a popular startup reflex that goes like this: "We should use Postgres because we're going to scale." That's like buying a tour bus for your daily commute because someday you might make money from carpooling.
SQLite is reliable and usually the right answer. It's a single-file database with a serious track record. It also deletes an entire category of problems: provisioning, credentials, network hiccups, and connection pools. And yes, I know there's a loud "Postgres for everything" crowd. I'm pretty sure y'all are the same people who insisted I needed microservices for a todo app.
When SQLite is genuinely fine
SQLite shines when your app is mostly reading, or when it runs in one place:
- Edge and embedded: desktop apps, mobile apps, IoT, CLI tools.
- Local Prototypes and MVPs: ship fast, learn faster, worry later when you realize your prototype is now production.
- Read-heavy apps: dashboards, internal tools, content sites, analytics views.
- Single-process deployments: one box, one app process, one database file.
If you're building something like "upload PDFs and search them," SQLite plus a decent indexing strategy will handle more than you think. It can pretty easily power apps with millions of rows.
The real limit: write contention (a.k.a. the one-writer rule)
SQLite can handle lots of reads and some writes, but it has a key constraint: only one writer at a time.
That means high write concurrency (many users constantly writing) can cause:
- increased latency on writes
- "database is locked" errors (if the code is sloppy)
- throughput ceilings that don't care about your dreams
Things that help (but aren't silver bullets):
- enable WAL mode (better read/write concurrency)
- keep transactions short
- batch writes
- avoid long-running write transactions
Two lines that make SQLite significantly better at handling concurrent access:
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
Not "The Solution" but "A Solution"
If your product is write-heavy by nature (chat, high-frequency events, multi-tenant OLTP under load), SQLite isn't the write...wright...rite...correct choice. When you're building from the ground-up and don't want to worry about RDBMs setup on day one do this:
- Use an ORM or query layer that supports both (SQLAlchemy, Django ORM, etc.).
- Keep SQL portable: avoid vendor-specific features until you need them. (You probably don't. Yet.)
- Centralize database access behind a repository/service layer.
Future-you should not have to grep 400 files because Past-you got excited and inlined SQL in a view handler. Past-you is unreliable. - Use migrations from day one (Alembic, Django migrations, Flyway).
Here's the basic SQLAlchemy setup. This is the magic incantation that gives you a clean seam for swapping databases later:
# db.py
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine("sqlite:///app.db", future=True)
SessionLocal = sessionmaker(bind=engine, autoflush=False, autocommit=False)
Later, swapping to Postgres becomes a config change, not a rewrite.
Connection sanity (because people love making this weird)
SQLite also pairs nicely with my "stop making 10,000 connections" philosophy. You don't need a pool the size of a small nation. You need clean boundaries and predictable access patterns. (I've written about connection pooling insanity before - same principles apply.) If you want the official knobs and dials, the SQLAlchemy engine docs are here: https://docs.sqlalchemy.org/en/20/core/engines.html
SQLite is probably enough. And if it isn't, you'll know because production will tell you. Not because you read it in a blog post.
-Sethers
