There has been a lot of controversy around SQLite recently. One cohort of people think it is good for production use while the other thinks nothing of it aside from a toy database.
I have been thinking about it a lot and playing with it. As an engineer, its always fun to try out new or in this case old tech. But I have been having a hard time justifying using it for any of my projects.
I think most of the people who are against it get their reasoning wrong. They think its not fast enough or it can't handle the workload. It's fast enough for most use cases especially in the early days.
The issue lies in suddenly making your services stateful. SQLite essentially resides with your service and while that saves round trips to an external database it comes with all sorts of problems.
The overwhelming majority of people use some variation of the cloud such as Railway, Vercel, Heroku (not a VPS or bare metal server). Yes, you can use persistent volumes with some of these services but now there is one more thing you are worrying about.
The nice thing about using stateless services is that in case of any issue you get to start again without almost any impact.
With SQLite, suddenly backups and replications are a bit harder. Yes, you can use Litestream but at that point you lose the simplicity of SQLite and might as well use managed Postgres.
The tooling around SQLite is also not as mature as a client server DB.
If the argument is SQLite lets you get up and running quickly, why would you want to complicate other parts of your stack because of it by introducing a persistent volume or using a VPS. That achieves the opposite of why folks want to use it in the first place.
You can pretty much refute my arguments and come up with a technical solution for most cases but its always going to be forced. You will end up over engineering just because you want to use SQLite.
I try to avoid having favoritism that is counterproductive. At the end of the day, SQLite just doesn't bring enough alpha to negate all of its baggage.
So, is SQLite not usable in any case? No, it has its uses. If you are going to self host a database for whatever reason you might as well use SQLite but if you want the easiest solution that just works, you are better off using a managed client server database.
Here is how I would rank my database preferences