> 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.
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.