April 22, 2024

|

Dev

SQLite Rises

SQLite is a production database. Not long ago this phrase would've been anathema — at least on tech Twitter. SQLite was what you played around with in local development before switching to a “real” database like MySQL or PostgreSQL in production.

For some time the common wisdom was to follow the tech titans — if Uber runs MySQL, I should too. While we have much to learn from big tech, their problems differ from those of the indie dev. Now we have more developers building smaller applications, and many find SQLite perfectly suitable for their needs.

Two projects stand out in the SQLite Renaissance. First is libSQL, a fork of SQLite developed by Turso, that adds several features and optimizes for edge deployment. I’ve heard great things about Turso, and Astro, the popular web framework, recently launched AstroDB, managed libSQL with a slick dev experience, which is also getting rave reviews.

Pocketbase is another great tool embracing SQLite. It’s an open source backend-in-a-file that uses SQLite in Write-Ahead Logging (WAL) mode, significantly improving concurrency and performance. Pocketbase also makes it easy to set up automated backups to S3, taking some of the stress out of self-managing.

In our own open source project, GhostRemix, which powers this blog, we also went with SQLite. Though Ghost recommends MySQL in production, we decided SQLite was fine for our use case*. So far so good.

The rise of SQLite isn’t about abandoning other choices or denying their merits. It’s about thinking through the solution to your particular problem and recognizing that what may be necessary for five million users might not be for five.

*You can read more about the design choices behind GhostRemix here.