Test Postgres in Python Like SQLite

This isn’t because of the size. Frankly, this is appealing because we already use both python and node package managers, so not needing to reach for another binary install mechanism is really appealing.

Haha yeah, I put a (I know) in the readme

> Effortless Setup: No PostgreSQL install needed—just Node.js(I know)!

Was just to have kind of SQLite dx in 1 hour thus did so.

And then I thought why not open source it?

Maybe in v2 I could abstract actual binary with same dx

Ding ding ding. Testcontainers is a fanatstic way to write the most important tests (arguably) for your app. Don't test E2E with a mock, just use a real database.

If that feels hard to you (to set up your app pointing to another DB, run a single E2E-testable part of your app with it's own DB connection, etc), fix that.

That's exactly what we are using for our tests in a new pull request to add support to Postgres, https://github.com/BSC-ES/autosubmit/pull/2187

The last GH Actions jobs with SQLite and Python 3.9 took 3m 41s, and the same tests with Postgres took 4m 11s. Running a single test locally in PyCharm also executes in less than 1 second. You notice some bootstrap happening, but once the container image is downloaded locally, it's really quite fast.

yeah, w/o py-pglite attempt this should be the only approach, the pglite ideally could make it more flexibly/lightweight in unittest cases, but as you mentioned it's never felt slow, it should be fine to working on it.

And actually, more e2e cases I think it's way better to not use the lite backend.

the non-container solutions would do more like the lifecycle mgmt/isolated env prep/tear-down with elegantly designed abstractions. While I think similar abstractions could be done on top of containers.

Maybe we ideally could have unified abstractions on both container-based, wasm evantually to boost dx yet with different expectation of speed vs compatibility.

For Python specifically I use pytest-docker: https://pypi.org/project/pytest-docker/

wow thanks! Should I use bun instead of node now?

Yes, it can. It's not especially hard to start up a Postgres instance, and with a couple of config tweaks you can improve the startup time. I've had this working nicely at a previous job, it's under a couple of seconds to start.

Thanks! This is the ultimate shape I am going to pursue!

I think the ultimate version in such use case would be carefully wire-up the baremetal one with ad-hoc in-mem-disk or tempdir :), this could be a future backend of py-pglite(planned in v2).

For now, it's more accessible for me to hack it in hours and it works.

Exactly; in the past I've had reasonable success with test fixtures based on temp dirs and a templated docker compose file. Just needs docker in the environment, which is not too far fetched.

This is something I've explored as part of my work on PGlite. It's possible but needs quite a bit of work, and would come with some limitations until Postgres upstream make some changes.

You will need to use an unrolled main loop similar to what we have in PGlite, using the "single user mode" (you likely don't want to fork sub processes like a normal Postgres). The problems come with then trying to run multiple instances in a single process, Postgres makes heavy use of global vars for state (they can as they fork for each session), these would clash if you had multiple instances. There is work happening to possibly make Postgres multi-threaded, this will solve that problem.

The long term ambition of the PGlite project is to create a libpglite, a low level embedded Postgres with a C api, that will enable all this. We quite far off though - happy to have people join the project to help make it happen!

wow, thanks! it should be feasible! and as I recall there are such thing from some databases(chromadb, milvus-lite) in the py-first communities.

we could think big to someday do that within py-pglite project actually.

let me put it as the roadmap of v2(much more work to do!)