Here are some of the different strategies you can use to write tests that read or write data from a database, and the benefits and drawbacks of each. I'm usually writing Go code, so the examples here are for Go, but the notes should generalize to any language that runs tests against Postgres, MySQL, or any other database.

At a high level my goal is for the tests to take a minimum of programmer time. Programmer time is spent on the tests in a few different places:

• Writing tests.
• Running the tests.
• Troubleshooting test flakes.
• Troubleshooting written tests that don't behave the way you expect.

Our goal is to minimize the amount of time spent across all four of these areas, from which I have the following principles for a test suite:

• Tests should be fast. Each test should do only what it has to do, which for database tests primarily means it should only create the objects it needs to test the behavior it needs to test, and no more.

• Tests, and test packages, should run in parallel, which usually helps the test suite complete more quickly.

• Tests should be predictable; if tests return unpredictable results, or interact in bad ways, this leads to wasted time tracking down why the tests are failing.

Obviously these are in conflict - parallel tests are less predictable than sequential tests - but there are some things we can do to make tests fast and predictable, and some common techniques that make them slower and unpredictable.

Don't: Reuse the same fixture objects in each test

You might have a set of "standard" test objects that get written to the database — Alice and Bob, and maybe Alice is a power user and Bob just signed up. If you try to create them in a test and they exist in the database already, the tests will upsert them.

This is fine, and you can do it, but it tends to break down in two ways:

• "Default" fixtures tend to accumulate new database objects any time you create a new table, like a snowball rolling downhill. This means any new table makes all of your tests slower.

• If you run tests in parallel, any test that updates or deletes any fixture that other tests rely on can cause unexpected behavior.

Because we are optimizing for programmer time - fast tests, and no unexpected behavior during tests - I tend to want each test to create the objects it needs, and make them each unique.

Don't: Reuse the same set of ~500 names

When you use a library like "Faker" to create objects that are sort of similar but have a common set of variations, for example, a last name derived from the same set of 500 last names, you are bound to run into trouble. It's way too easy to accidentally create two test objects that have the same birthday or same last name, when you didn't expect that to happen, and as a result, end up with one result when you wanted two, or otherwise get test failures that only occur once every 500 runs.

I've previously discussed why Faker is a bad idea, read the post for more.

Do: Use UUID's for anything that needs a "random" or unique input

Instead of creating "Alice" with 1 of 500 pre-seeded last names, or always with the email address "alice@example.com", use UUID's (or a part of a UUID), anywhere you need unique data. So "Alice Jones-a9706630", with email "alice-7cce0183@example.com". The first 8 characters of a UUID have 4294967296 different values; these objects will not conflict with each other during your lifetime.

Don't: Truncate the database after each test

At the end of each test, you could issue a command to truncate or delete all database tables. So each test is something like:

t.Run("TestAccountCreate", func(t *testing.T) {
    assertEquals(t, user.ID, 7) // or whatever
    cleanup(t)
})

The advantage to doing this is that each test cleans up after itself - you can create the same objects in multiple tests even if they might fail a uniqueness constraint, since it gets destroyed between each test instance. The disadvantage to doing this is that you can't run tests in parallel, because one test might issue a truncate right when the other test is writing to the database.

Further, it's not uncommon for tests to hang, or crash, while they run. If that happens, your truncate-after-test runner will not actually run, and the next test will encounter an inconsistent state. These types of failures can be tough to troubleshoot because the failure was actually in the previous test, which your test output might not make clear.

Don't: Truncate the database before each test

This has most of the benefits and drawbacks of the previous section, except you need to truncate everything in the database, because you don't know what test ran before this one. In theory, if you delete after, you only need to delete from tables that you wrote to.

Don't: Truncate the database after each package

You could enclose all of the tests in a parent test, something like this:

t.Run("Package", func(t *testing.T) {
    setUp(t)
    t.Run("Test1", func(t *testing.T) {
    })
    t.Run("Test2", func(t *testing.T) {
    })
    t.Run("Test3", func(t *testing.T) {
    })
    cleanup(t)
})

This way, you only pay the penalty of database setup and cleanup once per package.

However, the default for Go tests is to run as many package tests in parallel as you have CPU's available. So one package may be just finishing on one CPU (and truncating the database) when another package is starting on a different CPU, which causes failures that are hard to troubleshoot. You can set -p 1 to disable package parallelism, but the test suite gets a lot slower if you can only run one package's tests at a time.

Don't: Truncate the database before each package

This has most of the benefits and drawbacks of the previous section, except you need to truncate everything in the database, because you don't know what test ran before this one.

Maybe: Run all tests in their own transaction, then roll it back

The idea here is that you start a transaction in your test suite, then you run the application code, then you invoke a ROLLBACK in the test suite, restoring the database to its pristine state. The application code needs a pointer to the transaction, so the objects it creates exist inside of that.

Conceptually, there's nothing wrong with this method, but if you have any application code that starts a transaction, you will get in trouble with Postgres and MySQL, and you may not have a great time with other database flavors. In theory, you could make BEGIN and COMMIT a no-op in application code called from tests, but you are adding a lot of complexity there and the cost starts to outweigh the benefit.

Do: Avoid truncating the database at all

If each test is creating objects that it needs, and those objects don't conflict with anything else, you basically don't need to truncate the database ever. Sure you can add a command to clean it out if it gets too unwieldy, but you don't need to automatically do it between test runs.

This method is very consistent - tests behave the same way every time, they write the objects they need to the database. It also allows you to run all of your tests in parallel.

This requires a small change to the way you write tests - you can't make any assertions about the total number of objects in a given table, for example. If you want to test upserts by trying to write two records and then running SELECT COUNT(1) FROM table and ensuring only one record exists, that will fail because all of your tests are going to be writing to that table simultaneously. Instead, check for the number of records scoped to an account that's created for that test, or similar.

Maybe: Run each test in its own schema

Before running each test, copy the schema from a 'default' schema, and then run the test against the new schema. When the test completes, drop the schema. There's an example function to clone schema available on the Postgres wiki.

This is certainly a technique that provides you full isolation for each test, which allows you to run tests in parallel and ensure each test has a clean database. However, you have to pay the schema clone cost for each test, plus you have to create a new connection for each test. In my experiments on a 2.5 GHz core i7 Macbook Pro, cloning the database schema took about 200ms, and cloning the entire database took about 400ms. To me, these are too high of startup costs to pay to run a single test.

You could imagine having a background job with the sole purpose of creating enough schemas that you don't have to incur the 200ms latency when you actually run the tests - you could just find one of the already created schemas, though you'd need to wipe created schemas when you ran a migration, and that's getting pretty complex.

Assorted notes

• If each test is creating the objects it needs, and tests don't rely on a clean database state, then you should be able to invoke t.Parallel() at the top of each database test. t.Parallel() will truly try to run each test in parallel, so you may have too much parallelism, but that's a better problem to have than too little. Experiment with the number of connections you have open to the database (or add benchmarks) to find a number that makes the most sense.

• Prepared queries run about 30-40% faster than normal (fast) unprepared queries, because you don't have to pay the overhead of parsing the query statement. The Go *sql.DB object is thread safe, so you want to reuse it across all of your tests, instead of creating a new one in each test and paying the overhead to establish a connection to the database.

  A library like sqlc will help make it easy for you to get prepared queries.

Thanks to Phips Peter, Kyle Conroy, Alan Shreve, and David Strauß for reviewing drafts of this post.

Liked what you read? I am available for hire.