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.
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.
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.
Starting each test with a totally clean template ensures consistent reproducibility, I’ll give you that, but it also isn’t real world, either. You only have the data that the test seeds which favors tests (and by extension business logic) written only with the “happy path” in mind. I think the smell for when tests are stepping on each other causing flakey runs, is that the logic being tested isn’t written for non-happy paths, or, the tests are asserting entirely too specific datasets or outcomes and anything else results in a failure. In the real world, other systems may very well be touching the same data or tables that your code is interacting with, so it being able to handle that kind of situation will produce a more fault tolerant system overall, which will serve to deliver value even if other systems go haywire and produce unexpected data you are looking at. Of course the need to handle that extra complexity is going to vary depending on business needs or other determining factors.
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.
This 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).
In Ruby on Rails there is a way to do multi-request integration tests with single PostgreSQL/MariaDB database where transactions are used. The trick is to keep and share just one connection to database.
With PostgreSQL at the beginning of the test the outer transaction is opened, then connection is shared with application, test steps are performed (including Selenium etc), requests open/close nested transactions etc. Once steps are done the test closes the outer transaction and the database is back to initial state for the next test. It is very simple and handled by the framework.
In fact it can be even more sophisticated. E.g. an integration test class can preload some data (fixtures) for all the test cases of the class. For that the another nested transaction is used to not repeat the data load process for every test case.
Interesting, we've just been looking at something similar at work. Unfortunately we've got a bunch of application config and test data in the database and no fast process for seeding it. We're now building a Postgres Docker image with a baked-in database. There were some tricks for making a small database and it's important to change `PGDATA` to something else, otherwise the `VOLUME` directive (in the official Postgres image) causes a full copy of all the files. It looks promising, though (accepting connections in ~200ms).
> The other limitation of template databases to be aware of is that no other sessions can be connected to the source database while it is being copied. CREATE DATABASE will fail if any other connection exists when it starts; during the copy operation, new connections to the source database are prevented. It is an easy enough limitation to work around using a mutex pattern, but it is something to be aware of.
-- Prevent new connections to template db
update pg_database
set datallowconn = false
where datname = 'my_template_db';
-- Close all current connections
select pg_terminate_backend(procpid)
from pg_stat_activity
where datname = 'my_template_db';
I’ve been delighted with this approach and have never had a single glitch. Highly recommended, although I think I’ve heard from some denizens of HN that they’ve got even faster performance by manually copying data files instead of using template databases. That feels ickier, and the overhead I see is only 10-20ms per test so I have no great need to push further. YMMV!
Readit News
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.
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.
Your solution requires spinning up hundreds of docker images per test run..
We do the same thing as described with MS SQL; takes about 1 sec to get a fresh DB that way.
While MS SQL takes 30 seconds or something from Docker image.
> 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.
Deleted Comment
Deleted Comment
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.
it really enabled end to end level testing as well as being able to stand up development instances quickly.
- 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).
With PostgreSQL at the beginning of the test the outer transaction is opened, then connection is shared with application, test steps are performed (including Selenium etc), requests open/close nested transactions etc. Once steps are done the test closes the outer transaction and the database is back to initial state for the next test. It is very simple and handled by the framework.
In fact it can be even more sophisticated. E.g. an integration test class can preload some data (fixtures) for all the test cases of the class. For that the another nested transaction is used to not repeat the data load process for every test case.
MariaDB doesn't have nested transactions, however in that case RoR uses SAVEPOINTs mechanism. https://mariadb.com/kb/en/savepoint/
https://medium.com/@kova98/easy-test-database-reset-in-net-w...
Deleted Comment