I would definitely not try using this in a write heavy workload but could be interesting to consider in a write occasionally scenario.
At the time I was able to push it into something like 10 simultaneous write accesses (transparently serialized by SQLite). It was an adaptation of a MS Access file that would fail at approximately 5 people opening it at the same time (independently of their actual usage). Luckily I got access to a Postgres installation shortly afterwards so I didn't have to push SQLite any further.
The main consideration is typically about how the database is used. "Probably only one will be a writer" is a strong argument in favor of SQLite, which doesn't really have much of a concurrency story for writes (multiple processes can open the same SQLite database for writing, but they implement a block so only one at a time can actually write).
The file locks can be tricky to get working over NFS/CIFS with multiple computers, but it's still possible.
I think it's a pretty different use case to what the parent wanted
Exact same story for its HyperSQL and Derby friends: http://hsqldb.org/doc/2.0/guide/running-chapt.html#rgc_hsqld... https://db.apache.org/derby/#What+is+Apache+Derby%3F
Redis is considered an in memory store as well, even though it can persist it's data on disk too. And the capability isn't what makes it in-memory either, as you can store a SQLite db only in-memory too, and nobody would consider it to be an in-memory database. You could technically do the same for PostgreSQL, though that's not officially supported and needs a little effort on the users part.
I guess my phrasing was poor if you misunderstood me there, but I do stand by what I said: while H2 is a great project in Java-Land, it's very different to what I understood the parents goal to be.
That's true in theory, but I do find that they end up competing in practice when you're looking for something in between the two extremes.
I often seem to find myself in a position where I need a database, but it's only for one day's data, which is not huge but not tiny (say 100's of MB). It may be helpful to have a couple of processes accessing it, but probably only one will be a writer, and it's very likely (but not certain) that those processes will be on the same computer. It useful to have that data in a single file for archiving. Those are situations where you currently face a geniune choice between PostgreSQL and SQLite - more because both of them are a bit wrong, rather than because they're both perfect for it.
I wish there was something in the middle: a separate program you could spin up, so it's a client-server database like PostgreSQL, but it's just a single binary that doesn't need installing. And then it operates on a single file that it creates on the fly (or can open an existing one of course), a lot like SQLite. Hmm, as I think about it, it actually wouldn't be hard to make program that does that by making a thin wrapper around SQLite with e.g. gRPC.