It's really not. You have multiple read connections and a single write connection. You batch over that single write connections. The items of a batch is just a function with a sequence of queries and or application logic. That means these functions can read their own writes (as they can read using the write connection). This gives you read-after-write consistency. Because, all these functions are in a batch transaction they can read the writes of functions that have been run before them in the same batch.
Generally I find SQLite works best with a CQRS model, where you push view updates to your clients. So reads and writes are separate. In this model you only need read-after-write for transaction logic (i.e doing a read to check for something before doing an update).
Writes can come from multiple threads, they will just be put on a queue. Personally, I use an optimistic concurrency queue for batches, and there's only one "batcher" that has the write connection.
The key difference between this and what SQLite does is quite significant.
SQLite doesn't queue those writes instead they storm the place, taking turns at asking "can I go now" and sleeping for (tens, hundreds of) milliseconds at a time.
This only gets "worse" as computers get faster: imagine how many write transactions a serial writer could complete (WAL mode and normal synchronous mode) while all your writers are sleeping after the previous one left.
If you have a single limited pool, your readers will now be stuck waiting for an available connection taken by sleeping writers etc.
It's fairer and more efficient if your application handles the writer queue.
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.
Generally I find SQLite works best with a CQRS model, where you push view updates to your clients. So reads and writes are separate. In this model you only need read-after-write for transaction logic (i.e doing a read to check for something before doing an update).
Writes can come from multiple threads, they will just be put on a queue. Personally, I use an optimistic concurrency queue for batches, and there's only one "batcher" that has the write connection.
The key difference between this and what SQLite does is quite significant.
SQLite doesn't queue those writes instead they storm the place, taking turns at asking "can I go now" and sleeping for (tens, hundreds of) milliseconds at a time.
This only gets "worse" as computers get faster: imagine how many write transactions a serial writer could complete (WAL mode and normal synchronous mode) while all your writers are sleeping after the previous one left.
If you have a single limited pool, your readers will now be stuck waiting for an available connection taken by sleeping writers etc.
It's fairer and more efficient if your application handles the writer queue.