Mark (aka Winsaucerer) here to show you how you can test your PostgreSQL database like a sorcerer.
We are going to be using Spawn, a SQL build system supporting migrations and testing.
You do not need to be using Spawn for migrations in order to use it for testing.
Spawn does not require any extension installed.
All you need is the CLI and a psql connection to the database for Spawn to connect through.
Spawn was built to solve some migration pains I've experienced, but I happily discovered that when used for testing, it is very powerful.
To show you some of that power, we're going to use a contrived database example.
It uses golden file testing to determine success.
When the test runs, we capture the stdout and stderr output from psql, and compare that to expected output.
Testing with Spawn involves these steps: Create a new test with and fill out the test steps Check test outputs with (or view the SQL that will be sent to psql via ) When outputs are as expected, create the golden file with Run the test and compare to expected output with For now, Spawn only supports connecting via psql, which means that you have access to all the features that psql provides.
To get started, follow the Spawn install instructions: Install Spawn And then create a new folder on your system, and initialise a new project with a docker compose config ready for us to play with: You now have a running docker based PostgreSQL database and a file configured to connect to it.
We are not assuming that you are using Spawn or any other tool for migrations, so you can manually create and update the database by connecting directly using psql: Create the database ⚠️ Caution This post is not intended as an example of how to build an orders database.
The design of this database is aimed at demonstrating how to test features.
There are some design choices made here that I would not use for a serious orders database design.
Create the initial tables in PostgreSQL like so: Build our first test Let's create our first test: This will have created a very basic test inside , with content similar to the following: Spawn executes your test file through psql, captures the textual output, and compares it against an expected "golden" file. records the current output, while reruns the SQL and diffs any changes to the expected output.
To see what output this test would produce: You should see output similar to the following: Imagine we were satisfied with this as our test.
We can then tell Spawn that the output it currently generates is the expected output, and then we can run the actual test comparison: Now, change the test from to and run compare again: The test will fail, because the file differs from the actual output.
We see a report like this (which may be nicely coloured in your terminal): From the diff, we can see we expected a result of , but got . 💡 Tip For ideas on how to handle non-deterministic output in tests, visit Non-determinism in Tests.
Let's make a real test.
First order of business, we want to create a copy of the database so that we can rerun the test multiple times without making permanent changes to our database.
We could wrap the test in a transaction and roll it back (Spawn works fine this way), but some operations can’t run inside a transaction block (like creating databases), and sometimes you want to test behavior across commits.
Using gives you a clean slate every time without relying on rollback.
Using the following pattern, we use the database created as the base for tests, and create a copy of it using within which we can freely make changes.
As soon as the test is done, it cleans up the temporary test database and our changes are gone. 💡 Transactions vs You can write your tests however you please.
You could use the base database and wrap everything in and , or you can use to have a copy that you're free to do anything in.
Every run of the test is done within its own copy of the base database.
If we run the test multiple times (), the count doesn't change, despite adding a row each time, because each time the test runs it starts from the same base state. ⚠️ Caution You can only use if there are no connections to the base database that you are copying.
If you have a session open, you may need to connect back to to allow this to work.
But it's just a little tedious to create items by hand, so let's create a macro to simplify adding items to the database.
Spawn uses minijinja for templates, with more details and examples available in the Spawn template docs.
Create a file in like so: This macro creates the insert, with default values for everything except name.
Note that for the primary key we have .
If we provide an item id it uses that, but if we don't provide one then the default value is used.
We must pass through safe because Spawn defaults to escaping any input values as literals, but we want to appear without quote marks. i.e., to appear as and not .
Using the filter tells Spawn to display this as it is.
Now in our original script, we can replace the insert with a single call to the macro: The macros produce the SQL for us, which you can see by running : And : Now, we might find ourselves wanting to use this same dataset across multiple tests, so let's do two things: Create this list based on a json input Create a macro that fills out this table for us in one go In a real project, you might already have test data in the database you used as the base for , but we'll use this as an example to show how you could have some data that's used by some tests but not all.
Create a json file in : 💡 Tip Spawn escapes values like strings automatically as literals.
Visit SQL escaping and security for more information.
Let's also create a new macro which is going to read this json, and loop over the items to create all our rows!
Let's put it in (we don't have to use the extension here, but for consistency I have): This macro loads the array from , loops over each, and calls our earlier macro to create the insert statement for each!
Update our test file to use the new macro, like so: And let's build and then run.
First, build shows us that the apostrophe in is properly escaped: And the output for the test shows the four items: Now we have an easy to include test fixture of items!
Creating an order To demonstrate how we can do some tests with functions and triggers, let's create a function for creating an order, and a trigger that updates quantity on hand when orders are created, updated, or deleted.
First, let's create a function for creating an order, and apply it to our base database: Now we'll create a trigger function and trigger to automatically update inventory when order items change.
This is not a way that I would recommend building an order platform, but it's useful from the perspective of showing how easy it is to test triggers and functions with Spawn.
This trigger updates the column in the table when an order item is updated.
And since the table has a constraint that prohibits values lower than 0, we will get an error when a change would result in dropping our stock below
0.
Create this in the base database: And now we can update our test to create orders.
Let's try to create two orders, the first of which we expect to succeed, and the second to fail.
I've also included some notes to help with understanding the tests in the future, as well as selecting items from the table as we go, to see how stock levels change over time: We expect the first order to succeed, and the second to fail, and that's exactly what we see (): That's finished!
Our test validates that basic use of the order works as expected, and that the trigger updates the underlying table in the expected way.
We've included some notes to make things easier for future testers to understand what is going on.
Let's set the current output as the expected output for our test, and run it: And one final validation: connect to the database, and drop the trigger from the table and rerun compare, just to see th