Test Postgres in Python Like SQLite (opens in new tab)

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.

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

It's remarkably easy to integrate. We have a tests/db module with a single conftest.py file that does everything. Every test suite run spins up a fresh container (no state), runs all migrations against it to prepare it for duty, and then the test suite continues. You could do this on a per-test basis for ultra isolation, but this works for us. That change would simply be to modify the `scope` value.

Also a convenient `conn` fixture at the end which allows any test in the module to get a new db connection.

linky: https://gist.github.com/whalesalad/6ecd284460ac3836a6c2b9ca8...

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.

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

Thanks for sharing this, I'm not a Node/Bun dev but it's quite understandable. One question: does each test/database need to wait for migrations to run, or are you somehow using template databases to run them once and then quickly clone a new database for each test?

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.

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.

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

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!)

It is for testing python projects that connect to postgres.

So often what you do is when unit testing, you need to test CRUD-type operations between the application and the database. So you generally have to spin up a temporary database just to delete it at the end of the tests. Commonly this is done with SQLite during testing, even if you built the application for postgres in production. Because it is fast and easy and you can interact with it as a file, not requiring connection configurations and added bloat.

But then sometimes your app gets complicated enough that you are using Postgres features that SQLite doesn't have comparables to. So now you need a PG instance just for testing, which is a headache.

So this project bridges the gap. Giving you the feature completedness and consistency across envs of using postgres, but with the conveniences of using SQLite.

This is also an option(verifies data schema, query in some way) with SF SQL translated to duckdb SQL and testing it.

https://www.reddit.com/r/SQL/comments/10s4608/comment/j722e1...

I'm developing https://github.com/buremba/universql for this use-case, though DuckDB is used under the hood.

aha, SF is pure managed service, thus 100% local/or even lite-local of SF seems not feasible, maybe clickhouse/databend could be consider to enable such flexibility of lightweight testing?

TIL this exists, thank you! It's seems obvious in retrospect.

Would love to see a benchmark between this, pglite and testcontainers.

Thanks, duno this before and it looks like the ultimate shape py-pglite in js world~~