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.

Which problems were you thinking of?

Cloudflare and fly.io both promise hassle free read replicas and backup. They will both offer only a single node capable of writes, because that’s how SQLite rolls.

This is a pretty good fit for a read heavy load that requires SQL and very low latency.

I guess I’m not understanding what the benefit is vs hosted Postgres. Also low latency and setup can be equally trivial - see supabase for example.

Biggest benefit over hosted PostgreSQL is that you get SELECT queries that are measured in microseconds, because SQLite avoids needing network overhead per query.

https://www.sqlite.org/np1queryprob.html

Wouldn’t D1 introduce network overhead?

Yes for writes, but it shouldn't for reads: it looks like it works by replicating the full database down to each edge location where the code is running.

Hope this can give you some concrete answers: https://www.sqlite.org/whentouse.html

The important drawback is async replication and therefore the lack of full consistency. On the other hand, this is the big advantage of hosted Postgres and the like.

Those offerings are great for use-cases that don't need that kind of consistency, which are many.

No and no. I think this is great for Edge computing, where there is currently no solution. So, it's better than nothing.

It all depends on the use-case, of course. A traditional hosted Postgres or MySQL database or cluster is certainly the go-to solution for all who need advanced features or full consistency, which only synchronous replication could provide.

What problems? Both are for different use cases albeit overlapping.

Concurrent writes, for one.