Postgres focuses more on this use case. It's probably still going to do a better job with concurrency, or at least have more features. It'll also cost more to pay for as a service, so you decide if it's worth. Whereas HC-tree is an obvious win if you're already using SQLite. It's fine for two things to approach similar use cases from different sides.
Personally I want the opposite direction, a sort of Postgres-lite that uses a single flat file and lives inside a lib, for things where the power of the full thing isn't needed yet but I want to design my backend code against a Postgres interface. I've seen Wasm Postgres examples, and maybe there are others, but nothing mainstream. And yes there was this SQL standard that everything was supposed to conform to, but that went out the window long ago.
I have an absolutely horrifying Python script that makes a little sort-of-container that spins up a brand new MySQL instance, initializes it with a provided set of statements (from mysqldump), and runs it. And cleans it up when it’s done.
And I have a wrapper that does it in a shell.
This makes it possible to run MySQL-dependent tests, in parallel, on a dev machine, without too much misery.
(MySQL is awful. Don’t use it for new designs. MariaDB isn’t obviously better. AWS RDS MySQL is, in my considered opinion after wasting days on it, even worse. RDS CANNOT INGEST THE OUTPUT OF mysqldump WITHOUT ILL-CONSIDERED AND POORLY DOCUMENTED KLUDGES!!! WTF!! Don’t get me started on how defective every MySQL replication mode is.)
Postgres in my experience can handle like 100 connections with the default config I get installing it on my Ubuntu server, but at some point you want pg-bouncer, and I vaguely remember MySQL having higher limits. Idk, it's been a while cause I've almost always limited the number of connections in my own code instead.
I use an embedded postgres testing library for the JVM that does something along those lines.
Well no actually it just unpacks the tar file in a temp dir and runs the full postgres, but it mostly feels like what you describe (minus the single file part) and starts surprisingly fast. That would totally work for a little proof of concept (https://github.com/zonkyio/embedded-postgres)
I used hypersql/hsqldb for unit tests. The Oracle mode was usually close enough. It’s pretty close for postgres. Sometimes there’s small issues (I think using “INSERT INTO … RETURNING” required column name in caps for some reason) but overall it’s almost always what you want for unit testing with java.
I haven't done this, but I'd want it. I'd like to write my code to use a Postgres database, except swap in something similar to a SQLite database instead when I'm either testing or not yet at scale. SQLite does everything in just a library linked with your code, unlike Postgres which is a separate running server. I can't simply use SQLite for this because its API is different from Postgres's.
Doesn't almost every language have some kind of abstraction layer that allows this?
JDBC/ODBC/DB-API/Sequelize ORM (ignoring the ORM part).
This used to be one of the major use cases for SQLite - running automated tests and being able to throw away the database file (or manage it at different points in the tests).
Yeah, but the abstraction layers get in the way if/when I need to optimize queries or use special features, plus it's easier to just write SQL. I'd rather target a specific DBMS and rewrite my queries if I ever change it.
But raw SQL made for Postgres doesn't necessarily work for SQLite and vice versa. That's what I mean by the SQL standard being thrown out the window. You might be able to write SQL that works in both if you're careful and avoid a lot of features.
Personally I want the opposite direction, a sort of Postgres-lite that uses a single flat file and lives inside a lib, for things where the power of the full thing isn't needed yet but I want to design my backend code against a Postgres interface. I've seen Wasm Postgres examples, and maybe there are others, but nothing mainstream. And yes there was this SQL standard that everything was supposed to conform to, but that went out the window long ago.