There's a moment in every project where someone says "we need a real database." What they mean is MySQL. Or Postgres. What they almost never mean is "I've analyzed our requirements and SQLite can't handle them."
They mean "SQLite feels too small for what we're building."
That feeling is usually wrong.
What SQLite actually is
SQLite is a database engine that runs inside your application. No server process. No TCP connections. No user management. No configuration. Your database is a single file. You open it, you query it, you close it.
This sounds like a toy. It isn't.
SQLite handles 281 terabytes per database. It processes millions of rows without breaking a sweat. It supports transactions, triggers, views, CTEs, window functions, JSON operations, full-text search, and R-trees. It has been deployed on literally every smartphone on Earth. It's in your web browser right now.
When people say SQLite isn't a "real database," they're confusing simplicity with weakness.
The mental model problem
Here's what I think actually happens: developers learn databases through MySQL or Postgres tutorials. They internalize a mental model where a database is a service — something with its own process, its own port, its own auth, its own admin tools.
SQLite doesn't look like that model. So it feels wrong. Not wrong as in "technically insufficient" — wrong as in "this isn't how databases are supposed to work."
But that model is just one architecture, not a law of nature.
When SQLite is genuinely the right choice
Read-heavy workloads. Blogs, content sites, documentation, dashboards, analytics displays — anything where reads vastly outnumber writes. SQLite reads are fast. Faster than MySQL in many benchmarks, because there's no network round-trip.
Single-server applications. If your app runs on one machine (and most apps do, despite what the architecture astronauts tell you), SQLite eliminates an entire category of operational complexity. No database server to monitor, update, back up separately, or debug when the connection pool fills up.
Prototypes that become products. Start with SQLite. If you actually hit its limits — which almost certainly means you need concurrent writes from multiple servers — migrate then. Not before. You'll understand your data model much better by the time you need to migrate, and the migration will be cleaner for it.
Embedded analytics. Collecting metrics, counting things, tracking trends — SQLite handles this elegantly. Write a row, query a rollup, done. No Redis. No time-series database. Just SQL and a file.
When SQLite is the wrong choice
I'm not an absolutist. Don't use SQLite when:
- Multiple servers need to write to the same database simultaneously
- You need replication or failover
- You have heavy concurrent write loads (hundreds of writes per second from different processes)
- You need fine-grained user permissions at the database level
That's a shorter list than most people expect.
The backup is a file copy
This point deserves its own section because it's quietly revolutionary.
To back up a Postgres database, you run pg_dump, which locks tables, serializes data, and produces a SQL file you then have to restore with psql. Or you set up WAL archiving and base backups and point-in-time recovery and now you need a PhD in Postgres internals.
To back up SQLite, you copy the file. That's it. cp database.db database-backup.db. Or use the .backup command if you want it to be transaction-safe. Either way, you're done in the time it takes to read this sentence.
Deployment is equally simple. Your database travels with your application. scp it to a new server and you're running. No CREATE DATABASE, no GRANT ALL PRIVILEGES, no connection strings with seventeen parameters.
Try it before you dismiss it
If you've been reaching for MySQL or Postgres out of habit, try SQLite on your next project. Not as a learning exercise — as the actual production database. Keep it until you hit a real limitation, not an imagined one.
You might be surprised how long that takes.
Comments
Loading comments...