Normally I'd say the main difference between postgres and SQLite is that the latter is in-process. Now that they can both be in-process, is there a more detailed comparison of the two? When might I prefer one over the other?
Well, Sqlite is battle-tested to hell and back... and with PGlite I managed to get a `memory access out of bounds` in less than 3min of playing around. So perhaps maybe not prod-ready yet, even if it's bigger brother is one of the most solid DBs. (To be fair I've also seen & experienced issues with SQLite - no software is bug-free.)
What I always wonder is which people use SQLite to run tests for a Postgres application. Isn’t the difference in dialect pretty much always an issue unless you do only the most basic type of queries? pglite fills a hole where imho currently only dockerized Postgres sits
You can write all queries twice and run the same test suite on both, then use sqlite in other tests. Of course at that point you can just use a struct that stores all the same data.
