SQLite as Your Default Database

Most developers reach for PostgreSQL or MySQL before they even finish sketching the schema. That reflex adds layers of complexity: connection pools, migrations, network hops, and ops overhead. SQLite flips the script. It runs inside your process, needs zero setup, and still handles millions of rows without breaking a sweat. The catch? You have to stop treating it like a toy.

Where SQLite actually wins

Startups and solo builders ship faster with SQLite because there’s no server to babysit. No ports to open, no credentials to rotate, no backups to configure. The entire database lives in a single file. Copy it, version it, or toss it in S3, done. That simplicity extends to testing: spin up an in-memory instance, run your suite, and tear it down without leaving residue.

Performance surprises people. SQLite benchmarks faster than client-server databases for most read-heavy workloads. It avoids the network round-trip, and its query planner is shockingly good. Write contention is the only real bottleneck, but even that can be mitigated with WAL mode and batching. For 90% of apps, the bottleneck isn’t the database, it’s the ORM or the N+1 queries you wrote.

When to reach for something else

• You need concurrent writes from multiple machines. SQLite locks the entire file during writes, so distributed systems will hit contention.

• Your dataset exceeds 1TB or grows unpredictably. SQLite files can scale, but sharding and replication are manual work.

• You rely on advanced features like row-level security, materialized views, or geospatial indexes. SQLite covers the basics well, but Postgres has a decade head start on niche extensions.

• Your team already maintains a Postgres cluster. Adding another database just for one app rarely pays off.

Making SQLite production-ready

Treat the database file like code. Store it in Git alongside migrations, and automate restores from CI. Use WAL mode to allow reads during writes, and set a busy timeout so queries don’t fail under contention. For backups, copy the file while the app is running, SQLite guarantees consistency even during writes.

Connection pooling isn’t needed, but you still want to reuse connections. Open one per process and keep it alive. Libraries like better-sqlite3 for Node or sqlite3 for Python make this trivial. Avoid ORMs that hide the simplicity; raw SQL or a lightweight query builder keeps you in control.

Deploying SQLite in the cloud

Fly.io, Railway, and Render all support SQLite out of the box. They give you a persistent volume, so the file survives restarts. For serverless, use LiteFS to replicate the file across instances. It’s a bit more work, but cheaper than managed Postgres and just as reliable for read-heavy workloads.

If you’re on AWS, store the file in EFS or S3 and mount it to your container. The latency is higher, but still acceptable for apps with moderate traffic. For high availability, replicate the file to multiple regions using rsync or a service like Litestream. It’s not automatic failover, but it’s simple and battle-tested.

Schema changes without downtime

SQLite doesn’t support ALTER TABLE for everything, but you can work around it. Create a new table, copy the data, then swap the names. Tools like sqitch or alembic automate this, but writing the SQL yourself keeps the process transparent. Always test migrations on a copy of production data, SQLite’s simplicity doesn’t make it immune to human error.

The hidden cost of simplicity

SQLite’s biggest strength, being a single file, can also be a weakness. If the file corrupts, you’re restoring from backup. Enable PRAGMA integrity_check on startup, and log errors aggressively. Most corruption comes from hardware failures or sudden power loss, so run on reliable infrastructure.

Debugging is different. No slow query logs or connection metrics out of the box. You’ll need to instrument your app to track query times and errors. Libraries like opentelemetry work fine with SQLite, but you have to wire them up yourself.

Start with SQLite tomorrow

• Replace your local dev database with SQLite. No more waiting for Docker to boot.

• Use it for internal tools, prototypes, or side projects. If it breaks, you’ll know in minutes, not hours.

• Benchmark it against your current setup. You might find the performance gap is smaller than you think.

• Deploy a small feature with SQLite in production. Measure memory, latency, and ops overhead before committing.

SQLite isn’t a silver bullet, but it’s the right default for most apps. The next time you sketch a schema, ask yourself: do I really need a separate database server? Chances are, the answer is no.