This may be a good approach, but it departs a little from "it's just a file with ACID semantics, abuse it all you like". If you have multiple reader/writer processes then, if I read your post correctly, you'd need a gatekeeper process that batches the writes. And it needs, I suppose, to support more than query+data. I remember running a transaction that inserts the data, queries last inserted rowid and returns that to the user. It's not super straightforward to implement via a separate process.
But in any case, by the time you do that, you need a monitored (hand-rolled) service and at least some of the allure of a db-in-a-file goes away.
Again I'm not being mean about SQLite, it's a great piece of technology. Just sharing my war stories with others who may want to push it hard one day too.
SQLite, for me at least, is an embedded database it should be relatively tightly coupled to your application if you want to get the most out of it in the context of web servers. After all it's part of your application.
Again, maybe it's because I'm using Clojure on the JVM (with both real and virtual threads as well as bunch of great concurrent data-structure). But, setting up a "process" (thread) to do that batching is not hard, it's also easy for the individual functions to return results to their call sites via the Java promise API (after the batch completes).
All of this runs in a single process. With a single deployable uberjar/artefact.
> setting up a "process" (thread) to do that batching is not hard
Processes and threads are different things with specific meanings.
How would this approach work in an application server that implements concurrency with forking/multiple child processes (Python, ruby, PHP, node cluster, and many many more)?
The biggest benefit of SQLite is that, as GP says, it can be used like a file. Many places I’ve worked have used it to coordinate between all sorts of processes—often different processes written in different languages!
>How would this approach work in an application server that implements concurrency with forking/multiple child processes (Python, ruby, PHP, node cluster, and many many more)?
Sadly it mostly doesn't without a lot of work. You need to use languages that are not single threaded (have native multithreading support): Clojure/Java/Go/C# etc.
It's why python/ruby/php/node etc are fundamentally constrained. I'd argue those languages are the ones that pushed the horizontal scaling trend as they struggle to get the most out of a single machine.
But in any case, by the time you do that, you need a monitored (hand-rolled) service and at least some of the allure of a db-in-a-file goes away.
Again I'm not being mean about SQLite, it's a great piece of technology. Just sharing my war stories with others who may want to push it hard one day too.