Realistic, easy, and fast enough: database tests with Docker
You’re writing an application that talks to a database—PostgreSQL, MySQL, MongoDB—and you need to write some tests. You want three things out of your tests:
- Realism: the closer your test setup is to production, the more likely you are to catch bugs with tests.
- Speed: Slow tests are expensive tests.
- Ease-of-use: You want running and writing tests to be easy, otherwise you’re less likely to do it.
The problem is that interacting with a database is slow, so you want to avoid writing tests that talk to the database. But if you’re not talking to a real database, how do you ensure realism?
Luckily, with modern tools like Docker, you can actually have all three at the same time: realism, ease-of-use, and speed! But first, let’s consider some alternatives.
Some less-than-ideal solutions
If we start with the assumption that interacting with a database is slow, what are some of the ways people deal with this problem?
Test doubles: You can write a fake or mock to replace the code that interacts with the database. Unfortunately, even when you use verified fakes you still suffer from a lack of a realism. Your database can enforce many constraints—uniqueness, relationships, and so on—that are time-consuming and therefore expensive to implement in a fake.
SQLite: If you use a RDBMS like PostgreSQL in production, it can be replaced by SQLite running in memory. This is faster, but now you’re using a different database, which has different features and different bugs.
Rollback before commit: Another solution is to talk to the real database, but ROLLBACK your transaction before it’s committed. This allows you to skip the most expensive step of your database interaction. Unfortunately this isn’t possible in all applications, and can limit the kinds of tests you can do.
Maybe databases aren’t slow?
Consider the following program, which simulates a test suite that uses a database by doing 10,000 database inserts:
import psycopg2 def go(): conn = psycopg2.connect( "user=postgres host=localhost port=5432") conn.autocommit = True cursor = conn.cursor() cursor.execute("CREATE DATABASE test;") conn = psycopg2.connect( "user=postgres host=localhost port=5432 dbname=test" ) conn.autocommit = True cursor = conn.cursor() cursor.execute( "CREATE TABLE test (id serial PRIMARY KEY, " + "num integer, data varchar);") for i in range(10000): cursor.execute( "INSERT INTO test (data) VALUES ('hello!');") go()
In the original version of this article, written two years before this update, the elapsed time was 25 seconds. Either I made a mistake originally, I was using a much slower hard drive, or perhaps some other settings changed, because now when I run it, it takes about 1.8 seconds:
$ time python dbtests.py real 0m1.820s user 0m0.114s sys 0m0.146s
This isn’t too bad for 10,000 rows! Many database tests won’t actually insert that many rows, and you don’t need to create the database from scratch each time, and modern SSDs have gotten a lot faster. You might also be able to do this setup once, and then reuse it across tests.
Still, can we do better?
Writing a file is fast, writing to disk is slow
Writing to an actual hard drive is slow compared to writing to memory, although modern SSDs are much better than older, spinning hard drives. So when a program writes a file, by default the data doesn’t get written to disk: instead, it gets stored in memory, and at some point the operating system will write that memory to disk.
But by that point the process has moved on to doing something else. Most of the time, then, writing to a file appears to be quite fast from the process’ point of view.
Unlike many programs, databases want data written to disk immediately: when you commit a transaction, they want to be sure that data will be there.
And so they force the data to disk immediately, e.g. by calling the POSIX
As a result, database commits are slow, because they have to wait for the disk writes.
When you’re running tests, however, you don’t care about safely storing data! If your tests crash and you lose all the data, who cares?
If you could just tell your database that you don’t care about safety, you can get a nice speed bump; how big a speed bump depends on your hard drive. In my case:
$ time python dbtests.py real 0m1.294s user 0m0.109s sys 0m0.160s
That’s almost a third less time, with no changes to your code. Let’s see how you can do that in your own test setup.
Docker as a controlled database environment
One of the criteria for writing tests we mentioned at the start was ease-of-use, and Docker makes it super-easy to run a database. Here’s all you have to do to get PostgreSQL running:
$ docker run --rm -p 5432:5432 postgres:11.1
That will download an image and start up PostgreSQL v11.1, available on port 5432, running in its own isolated filesystem. You can startup 2, or 3, or 17 servers just as easily.
And even better, you have the ability control the runtime environment for the process.
First, you can substitute a configuration file for the default one, and in that configuration file you can set the appropriate option to disable forcing writes to disk. The documentation for the Docker PostgreSQL image explains how to do this for that database.
Second, for the PostgreSQL image you can pass configuration options on the command line. This is all it takes to get the performance improvement you want:
$ docker run --rm -p 5432:5432 postgres:11.1 -c fsync=off
Third, you can mount a custom directory at any point within the isolated process filesystem. You can mount a local directory from your main filesystem, or more interestingly for our purposes, when running Docker on Linux you can mount a RAM-based filesystem, so all writes go to memory:
$ docker run --rm \ --mount type=tmpfs,destination=/var/lib/postgresql/data \ -p 5432:5432 postgres:11.1
This latter option is the most generic, in that it’s the same for all databases; you just need to know which directory to override. But it does require you being able to fit all data in RAM.
The ideal choice
So here is the ideal testing choice for most situations:
- Writing tests that just use the database normally—as it would be used in production—gives you realism.
- Using Docker gives you ease-of-use: it’s very easy to start up a new, isolated database instance.
- With modern SSDs, databases aren’t that slow: Disk performance is vastly improved from the days of spinning hard drives that had to physically move to read from a particular location.
- Disabling fsync() gives you even more speed. Using database-specific options, or just Docker’s ability to override a directory with a RAM disk, you can improve performance even more without changing the way you interact with the database; how much improvement depends on how slow your storage is.
And pretty much all databases have a way to disable
No need to fear the database: with just a little setup, you can write tests that are fast, realistic, and easy to run.
The concise and action-oriented guide to Docker packaging for production
Docker packaging for production is complicated, with as many as 70+ best practices to get right. And you want small images, fast builds, and your Python application running securely.
Take the fast path to learning best practices, by using the Python on Docker Production Handbook.
Learn practical Python software engineering skills you can use at your job
Sign up for my newsletter, and join over 6700 Python developers and data scientists learning practical tools and techniques, from Python performance to Docker packaging, with a free new article in your inbox every week.