Since DHH’s RailsWorld 2023 keynote (part on Solid Cache) I’m obsessed with the idea of using SQLite as a productiondatabase for my side projects (given I have NVMe storage dedicated server).
past 15 years I worked mainly with Postgres & MySQL so SQLite is new realm for me.
I’ve spent several weeks reading on SQLite best practices for prod webapp most of which comes from Go lang & Python community. Therefore I wanted to check directly with Rails friends if any experience to share and gotchas to look for given it will be a Rails project
I understand SQLite locks entire DB on write and allow only one write = it may suck on projects expecting too many concurrent writes. My project will be something like a Blog so I expect mostly reads and moderate load of writes. I expect couple of hundreds (maybe thousands) users with couple of hundred/thousand “articles” each. I’m not planing to make it the next Facebook
I also checked ActiveRecord SQLite adapter source and I can see many of recommended best practices for prod webapp SQLite are already implemented like: PRAGMA journal_mode = WAL, PRAGMA busy_timeout, PRAGMA foreign_keys = ON;
One that seems to be missing is enforce STRICT types (not to confuse with strict strings and Rails :strict option) But I assume Rails is converting types on application level (so 1 saved to SQLite text field is converted to “1”)
So any ideas, recommendations, past experience with SQLite as a production Rails DB would be amazing. Thank you
Out of interest, I am not qualified to help. : Can you map this to 1 sqlitedb for each user (1 writer/thousands of readers) or must everybody write into the same DB?
Honest answer: I don’t know. I think thanks to recent Rails multi DB it should be possible
It would be interesting solution if you have to physicaly separate every client data to own db for security
But in regular application I don’t see why one would need that. SQLite can handle lots of data. If we are talking several million rows SQLite is good (I’ve listened to Changelog podcast with SQLite author and yeah they have some large use cases and SQLite is fine with them as it has multithread support on reads).
I also was interested in a similar thing for my latest personal project. Fly.io has been doing some interesting work with SQLite so I recommend reading some of their blog posts. Specifically Litestream allows for durability by streaming WAL logs to an S3 compat bucket. Also LiteFS by Fly.io, while still in development, is adding horizontal scaling to SQLite which is very compelling.
End the end, it wasn’t scalability that made me shy away from SQLite but features. PostgreSQL is a powerhouse of features that just weren’t available in SQLite. Even simple things like UUID-based primary keys.
yes Ben Johnson (form Fly.io, creator of Litestream) is the voice of confidence in the “SQLite in production” world. He has some amazing talks on it (whoever is interested): video1, video2, Changelog interview
Good point about the features. Best way to describe SQLite is that it contains the 95% of stuff developer usually use in Rails app. So no such fancy stuff as JSON columns or material views,…
The problem here (from my experience so YMMV) is that it’s not 95% of apps don’t need those features but 95% of a single app doesn’t need them. I may not use jsonb fields on many tables but just about every app has a few. It’s just too useful. Without it you end up with an over-normalized schema with an overuse of JOIN operations. Relational databases are great and what you need 95% of the time. Until they aren’t and jsonb gives you an escape hatch to a document-oriented database without changing technologies.
The same goes for a lot of other PostgreSQL features. So while SQLite gives you 95% of what you need your app will eventually need that other 5%. It might be jsonb columns, it might be materialized views, etc but it will happen in my experience.
I have used SQLite in production before. A small rails app 10 years ago. As long as you only do reads from this database we are good, the issue comes when you write when someone wants to read. The whole file gets locked.
I doubt I used that. Again it was 10 years ago. But when the SQLite db was being written (which was really rare) the site would be down for the incoming request. But as soon as the write is done its back to normal. I think the website might have had few 100 request per minute at its peak. It was a simple static site with some API’s for a graph that’s all.
Hey Thomas, author of that blog here. If you ever have any questions as you use SQLite with your Rails app, feel free to reach out. My contact info is at the bottom of every blog page.
I have been running a few apps in production for a couple years now with SQLite. I have authored many PRs to Rails to improve support for SQLite, and I am quite confident that it would work amazingly well for your blog.
But in regular application I don’t see why one would need that. SQLite can handle lots of data. If we are talking several million rows SQLite is good (I’ve listened to 