Sqlite supports recursive CTE

I think you're missing the point.

I want to develop on postgres and test on postgres because I run postgres in production and I want to take advantage of all of its features. I don't understand why a person would 1) develop/test on a different database than production or 2) restrict one's self to the lowest common denominator of database features.

Test what you fly, fly what you test.

And JSONB, but you can't insert or update in a CTE like you can with Postgres.

Does something like PGlite work for your use case? https://pglite.dev/

I want test with the same API as my database, which means using the same database. Setting up an instance of Postgres/MySQL/whatever isn't hard. Maybe harder if you're using an online database that doesn't fit into a container, but that's a different problem.

You can just simply run postgres, why bother with pglite?

postgres installs easily on WSL2 or whatever Linux distribution you're using.

You don't even need to install it. It doesn't take a lot of code to run initdb to create a temporary instance, write a suitable configuration file, launch the postmaster process, and delete the temporary database directory tree after terminating the database after testing is complete. On a not-too-fast Linux system, the time for all that is around half a second. Too much overhead for individual unit tests, but fast enough to run once per test suite run.

Agreed. Running docker-compose and postgres-alpine works just fine.

Unit tests work well with PGlite, at least in the TS/JS world where it natively sits at the moment. You can have a unique Postgres instance for each unit test that's up and running in just a few ms.

It's possible to use PGlite from any language using native Postgres clients and pg-gateway, but you lose some of the nice test DX when it's embed directly in the test code.

I'm hopeful that we can bring PGlite to other platforms, it's being actively worked on.

The other thing I hope we can look at at some point is instant forks of in memory databases, it would make it possible to setup a test db once and then reset it to a known state for each test.

(I work on PGlite)

Embedded/in-memory Postgres for testing is easier than you might think.

SQLite does have recursive CTEs, but yeah, everything else that's all about types other than the ones SQLite supports...