In this post I will show you how I’m testing functions in PostgreSQL using pgTAP. PostgreSQL and Flyway are running with Docker Compose, while pgTAP is running in separated container.
What I will cover here
In this post I will show you how to run pgTAP using Docker. In my case I wanted to run it after Flyway applies a schema. While I will show you how to run a test, I won’t go here into details how pgTAP works. You can use my example to get something running locally, but if you are just getting started, you may prefer to run it without Docker first. On the other hand my example is a good playground.
I used a lot of work done by other people. Big parts of it I copied and then adjusted to my needs.
This was the approach I used first docker-pgtap it worked for older version of PostgreSQL, but failed for 12.2. After trying to install dependencies for plv8, image was getting massive, so I kept the script but changed Dockerfile based on this comment
And as usual documentation was helpful.
- Docker Compose
- Code from GitHub
- Psql (not essential but is useful)
- Understanding of existing Flyway migration (optional)
What is inside Docker image
To run PostgreSQL and Flyway from my repository, you have to run
That will start PostgreSQL, then Flyway will apply schema from
sql_versions directory. To run tests we have to wait until PostgreSQL is ready. It will accept connection when you can call it’s endpoint and get empty reply from the server.
It should give you a response:
curl: (52) Empty reply from server
This doesn’t mean that Flyway finished running, when you have many migration scripts, it can take a while. For humans it will be quick, but when you have a script which tries to run tests, migration is too slow.
Our next step is to wait for Flyway. I achieved that by querying PostgreSQL and making sure that Flyway applied all files from
sql_versions directory. This is part of other script, but it can help with understanding.
Because some scripts can be applied multiple times, starting by default with
R__, I had to use
count(DISTINCT script) to get correct number, if we run on empty database
count(*) would work too.
Try to run command below, I had to remove spaces from response to get just a number. While for our tests we can pass password with environment variable, be careful to not do the same with your production database, as it will stay in your command history.
Without changing schema files, you should see
5 as the only output.
Because you can run this query in the middle of Flyway migration, I had to compare this number with number of files to apply. This part of bash script does it.
Once it’s done we can install pgTAP inside PostgreSQL. I’m using sql file, which I have inside pgTAP Docker container.
The last step is to execute tests which happens with
Most of code above you can find in this script, it is part of generated Docker image. This way my co-workers can pull an image from Container Registry and use it quickly.
You can build Docker image with make command.
After creating Docker image we can run tests. We have to start PostgreSQL with Flyway in a background
The next step is to start Docker container with pgTAP from previous section. We have to mount directory with our tests and pass all parameters required by
test.sh, which is an entry point in our container.
Then we want to know if our tests were successful, I search
test_output.txt for this information.
Then I used
cat to print test output so we can see more details.
You can check full script here
What tests are running.
You can see there is one test file, you can add more sql files to this directory. It’s running two tests, one without any data in tables and second test where there is some existing data. You can see function definition here.
If you want to run parts of test manually, you can do it with psql. After running database in Docker Compose, you can connect to it.
Inside psql you can create variables.
\set first_name '\'Audrey\'' \set last_name '\'Jones\'' \set car_make '\'Mitsubishi\'' \set car_model '\'Outlander PHEV\''
Then you can use them to call our function.
The next step is to query tables.
So far all of it is inside a test file. If you want to see results you will have to run execute.
That should give you enough information if your test code is working. If you would prefer to run a full test manually, including pgTAP functions, you have to remove one line from
tests_runner.sh which stops and removes database:
Without it, after you run your tests, database will stay open with pgTAP installed. You can copy and paste entire test into psql, if you remove
ROLLBACK, you can query data left in a database after a test. It is helpful for debugging tests, as you can run one command at a time and check if data is as expected.
While writing tests for PostgreSQL with pgTAP is not an easy process, it is extremely useful. Being able to run it in Docker not only allow you to run it locally, but gives your co-workers easy start, without lots of installation steps. If your build server supports running Docker images, making it part of CI will give you an extra confidence in your database changes, before they hit any environment. Go and add some tests!