Setting up PostgreSQL for running integration tests • Hacker News Board

Re: Setting up PostgreSQL for running integration tests

#21

Post by chrsig » Fri, Apr 19, 2024, 1:36 PM UTC

vbezhenar wrote: post #12

There'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…

I can +1 this approach and report a good amount of success with it using mysql.

it really enabled end to end level testing as well as being able to stand up development instances quickly.

Re: Setting up PostgreSQL for running integration tests

#22

Post by samwillis » Fri, Apr 19, 2024, 1:37 PM UTC

I work on PGlite [0], integration testing is absolutely something we want it to be useful for. For an early data point the Drizzle integrations tests run in about 1/4 the time with PGlite in-memory than a full Postgres.

The OP mentions lack of extensions being a blocker for them using it now, they are coming soon. Additionally it's currently limited by being single connection, and again I hope we can remove this limitation.

I also have a few other ideas around how to make supper fast testing possible.

http://github.com/electric-sql/pglite

Re: Setting up PostgreSQL for running integration tests

#23

Post by bognition » Fri, Apr 19, 2024, 1:48 PM UTC

vbezhenar wrote: post #12

There'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…

This is absolutely the correct answer. Testing infra should be ephemeral and mostly stateless. Prior to docker you had to figure out ways to mock the database or use something to approximate it with a lite weight DB like H2 or SqlLite.

With docker you can build out the test image with default usernames/passwords/etc...

Then as your install gets more complicated with stored procedures and the like you can add them to your test database and update local testing tooling and CI/CD to use that.

The massive benefit here is that you're using the exact same code to power your tests as you use to power your production systems. This eliminates issues that are the caused by differences between prod & test environments and anyone who's debugged those issues know how long they can take because it can take a really long time to figure out that is where the issue lies.

Re: Setting up PostgreSQL for running integration tests

#24

Post by xyzzy_plugh » Fri, Apr 19, 2024, 2:02 PM UTC

samwillis wrote: post #22

I work on PGlite [0], integration testing is absolutely something we want it to be useful for. For an early data point the Drizzle integrations tests run in about 1/4 the time with PGlite in-memory than a full Postgres. The OP mentions lack of extensions being a blocker for them using it now, they are coming soon. Additionally it's currently limited by being single connection, and again I hope we can remove this limi…

This is intriguing but I'm curious how you handle, or plan to handle, different isolation levels? In the past I've written tests to assert serializable guarantees, for example.

Re: Setting up PostgreSQL for running integration tests

#25

Post by beeboobaa3 » Fri, Apr 19, 2024, 2:13 PM UTC

vbezhenar wrote: post #12

There'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…

You can save some time and complexity and just run a single container and first, once, set up your template database `my_template`, then create your testing databases using `CREATE DATABASE foo TEMPLATE my_template`. Basically TFA.

This will be much faster than restarting postgres a bunch of times, since this will just `cp` the database files on disk from the template to the new database.

The only "problem" is your application will need to switch to the new database name. You can also just put pgbouncer in front and let it solve that, if you want.

Re: Setting up PostgreSQL for running integration tests

#26

Post by dherikb » Fri, Apr 19, 2024, 2:13 PM UTC

I am using TestContainers, what basically is able to run a Docker image of PostgreSQL, abstracting a lot of details. You can find a working example of this setup for integration tests using Go, testify and PosgreSQL here: https://github.com/dherik/ddd-golang-project

For Java services using MySQL, I was able to use just the H2 database (in-memory) many times. Does a decent job and it's very compatible with MySQL. If you try to avoid specific features from the databases, this in-memory database can do a decent (and fast) job running integration tests.

Re: Setting up PostgreSQL for running integration tests

#27

Post by hn_throwaway_99 » Fri, Apr 19, 2024, 2:37 PM UTC

vbezhenar wrote: post #12

There'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…

I would think the fundamental issue with this is similar to what the author described with template databases:

> However, on its own, template databases are not fast enough for our use case. The time it takes to create a new database from a template database is still too high for running thousands of tests:

And then in the timing shows that this took about 2 seconds. Launching another container is surely going to be at least that slow, correct?

So it's clear the author is trying to get an "absolutely clean slate" for each of potentially many tests. That may not be what all teams need, but I will say we had an absolute beast of a time as we grew our test suite that, as we parallelized it, we would get random tests failures for tests stepping on each other's toes, so I really like the approach of starting with a totally clean template for each test.

Re: Setting up PostgreSQL for running integration tests

#28

Post by williamdclt » Fri, Apr 19, 2024, 2:41 PM UTC

dherikb wrote: post #26

I am using TestContainers, what basically is able to run a Docker image of PostgreSQL, abstracting a lot of details. You can find a working example of this setup for integration tests using Go, testify and PosgreSQL here: https://github.com/dherik/ddd-golang-project For Java services using MySQL, I was able to use just the H2 database (in-memory) many times. Does a decent job and it's very compatible with MySQL. If y…

The whole point of the article is to make it _fast_: can you share how fast it is to spin up your database with TestContainers?

Re: Setting up PostgreSQL for running integration tests

#29

Post by dventimi » Fri, Apr 19, 2024, 2:45 PM UTC

jamesrr39 wrote: post #17

dventimi wrote: post #16

Wait, 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.

Dang! I didn't know that either. Kinda subtle. Blink and you'll miss it.

Re: Setting up PostgreSQL for running integration tests

#30

Post by stuaxo » Fri, Apr 19, 2024, 2:53 PM UTC

Could probably use tmpfs to store the database, as that defaults to memory (at first at least), I wonder if that is measurably different from mounting a memory based fs.