In .NET we have TestContainers and Respawn. https://medium.com/@kova98/easy-test-database-reset-in-net-w...
[deleted]
Setting up PostgreSQL for running integration tests
11–20 of 50 posts
Re: Setting up PostgreSQL for running integration tests
#12There's one idea I'm not sure being described.
Basically you create docker container from some postgres image.
Then you run DDL scripts.
Then you stop this container and commit it as a new image.
And now you can create new container from this new image and use it for test. You can even parallelize tests by launching multiple containers. It should be fast enough thanks to docker overlay magic.
And it should work with any persistent solution, not just postgres.
Re: Setting up PostgreSQL for running integration tests
#13The article mentions copying databases using templates. Once your database has got big enough, that might not make sense.
You could use a zfs fs for your cluster, snapshot it, then mount it in a new cluster. This would prevent copying the data, as zfs would do copy on write. So you get your isolation, and speed on large DB's. You do need to run a new postgres cluster though.
Re: Setting up PostgreSQL for running integration tests
#14Why can't you just create a pool of databases and truncate all tables after each run ? That should be pretty fast
Re: Setting up PostgreSQL for running integration tests
#15With disclaimer I work at Neon, branching might be a good option here. https://neon.tech/docs/guides/branching-test-queries
Re: Setting up PostgreSQL for running integration tests
#16Wait, how do you create a "memory disk"? That seems like an important step.
Re: Setting up PostgreSQL for running integration tests
#17Wait, how do you create a "memory disk"? That seems like an important step.
They use the `--tmpfs` flag when calling `docker run`. More info: https://docs.docker.com/storage/tmpfs/
Pretty neat, I didn't know about it before.
Re: Setting up PostgreSQL for running integration tests
#18I've never had a problem working https://pgtap.org/ into CI.
I know the article title says "integration tests" but when a lot of functionality is done inside PostgreSQL then you can cover a lot of the test pyramid with unit tests directly in the DB as well.
The test database orchestration from the article pairs really well with pgTAP for isolation.
Re: Setting up PostgreSQL for running integration tests
#19This is a great start, but I’d recommend three more improvements:
- Follow this guide to disable all durability settings. We don’t care if the DB can recover from a crash, since it’s only test data: https://www.postgresql.org/docs/current/non-durability.html (I wouldn’t worry about unclogged tables, personally)
- Set wal_level=minimal, which requires max_wal_senders=0. This reduces the amount of data written to [mem]disk in the first place.
- The big one: create a volume in /var/run/postgresql/ and share it with your application so that you can connect over the Unix domain socket rather than TCP. This is substantially faster, especially when you create new connections per test (or per thread).
Re: Setting up PostgreSQL for running integration tests
#20Wow. So much effort in reinventing the wheel. The transaction approach is obviously not suited for integration testing as commits/rollbacks are part of the package too. There is testcontainers + flyway/liquibase. Problem solved.
Testcontainers are anything but fast in my experience. With the image pre-pulled, the most basic mysql testcontainer takes about 5 seconds from calling RunContainer to it being ready to go.
I use TestContainers in my Java Spring Boot application coupled with Liquibase. I have it set up so that the PG instance is created once with Liquibase initializing it from vanilla PG to a fully created empty application database, then all the integration tests reuse the same by overriding the stop() method to an empty one.
Running the full suite of tests is about 5-10 mins on a relatively low-powered build server, whereas on my more robust development workstation it's about 3 mins. Getting a TestContainer running an empty DB for integration tests ends up being a very small portion of the time.
Most of the time any work being performed is done in a transaction and rolled back after the test is completed, though there are some instances where I'm testing code that requires multiple transactions with committed data needing to be available. The one ugly area here is that in some of these outlier test cases I need to add code to manually clear out that data.