It isn't as much as folks who need Postgres features are moving to SQLite just because it is cool, but it is folks who don't want those Postgres features moving to SQLite, because the latter has just enough features they only ever really need.
SQLite made sense as an embedded database on day a desktop or phone because there’s only a single person generally writing to it. The perfect use case.
I don’t understand how it will be usable at all in a website with multiple users. Is the idea to make your site to every user gets their own database? How do you stop SQL injection?
Once you solve all of these problems aren’t you better off just using Postgres?
> I don’t understand how it will be usable at all in a website with multiple users
With WAL mode enabled the database is locked during writes only, and concurrent writes are queued but you can still perform reads concurrently. If you keep your write transactions small and consider that a lot of apps aren't writing a lot, it can give perfectly good performance for a lot of usecases.
> Is the idea to make your site to every user gets their own database?
You can do... I know of B2B apps that give each billable customer their own database.
> How do you stop SQL injection?
In the exact same way you do in all other flavours of SQL - with parameterized queries.
> Once you solve all of these problems aren’t you better off just using Postgres?
Not necessarily. Postgres gives you a different set of problems and limitations to consider and work around.
Most of the problems with Postgres are a result of it not being embedded. If you’re using SQLite in an non embedded fashion I don’t see how you don’t inherit the same problems.
