I once built an app for compliance reasons that needed a little bit of dynamic data and quarterly updates that allowed us to stay in business in a few of our minor markets (worth cool tens of millions) and a SQLite DB with some basic turn key CRUD admin tools was fantastic. The data was larger than a simple YAML/TOML file would be tolerable to live with and needed some love and attention by lawyers. I could update/let them update the DB in dev and check in the DB file (it was smaller than our very unreasonably large TOML file we used for app config) and had the same app in every environment without managing MySQL or Postgres migrations. The whole thing happily ran on three EC2 micro instances and given the relatively low traffic (low thousands of visitors per month) it was a great set of technical decisions.
For that narrow use case at least, I am a huge believer in doing this.
Another prod use case for SQLite I’ve implemented was in taking a market specific search engine component (eventually replaced with Elasticsearch after a few years of R&D, but I digress) where I needed to implement sharding, reordering of results, and a few extensions to the engine that nominally relied on INI files for the limited extensibility it did support. The engine itself lacked any network coordination so I scheduled things through updated files lobbed into S3 and let a scheduled job pull them down to each machine. If a specific shard was to be out of service that data was in SQLite along with what operations to run. There are a lot of reasons for why this was the case and happens a bit over a decade ago so there are much better ways of doing this now. RQLite didn’t exist yet for example and there was a very dumb license restriction keeping MySQL and Postgres off the table (kill me). Simply syncing SQLite files across S3 sufficed since the engine itself had a more severe write lock on indexing than SQLite did. I could just swap the files while I waited for that operation to complete. Since I could actually change how SQLite worked, some full text search operations moved to SQLite since the licensed product wasn’t good at that as it was marketed/intended to be used for its faceted search and proprietary ontology. Overall SQLite was the nicest part of that experience as well. Well at least until we moved to Elasticsearch and I no longer had insane license terms I had to code around.
For that narrow use case at least, I am a huge believer in doing this.
Another prod use case for SQLite I’ve implemented was in taking a market specific search engine component (eventually replaced with Elasticsearch after a few years of R&D, but I digress) where I needed to implement sharding, reordering of results, and a few extensions to the engine that nominally relied on INI files for the limited extensibility it did support. The engine itself lacked any network coordination so I scheduled things through updated files lobbed into S3 and let a scheduled job pull them down to each machine. If a specific shard was to be out of service that data was in SQLite along with what operations to run. There are a lot of reasons for why this was the case and happens a bit over a decade ago so there are much better ways of doing this now. RQLite didn’t exist yet for example and there was a very dumb license restriction keeping MySQL and Postgres off the table (kill me). Simply syncing SQLite files across S3 sufficed since the engine itself had a more severe write lock on indexing than SQLite did. I could just swap the files while I waited for that operation to complete. Since I could actually change how SQLite worked, some full text search operations moved to SQLite since the licensed product wasn’t good at that as it was marketed/intended to be used for its faceted search and proprietary ontology. Overall SQLite was the nicest part of that experience as well. Well at least until we moved to Elasticsearch and I no longer had insane license terms I had to code around.