How I made my Clojure database tests 5x faster

Summary: Setting up and tearing down a test database can be slow. Use a rolled back transaction to quickly reset the database to a known state. You can do that in an :each fixture to run each test in isolation.

On one of my projects, I wrote a bunch of tests that had to hit the database. There was a :once fixture to create all of the tables anew and an :each fixture to delete everything in the tables before each test. That ensured that I was always working with a known empty database. Overall, the tests took about 10 seconds. Woah! That's a long time. But I lived with it.

(defn clear
  "Delete all rows before and after, just for good measure.
  [test]
  (cleardb db) ;; delete all rows from all tables
  (try
    (test)
    (finally
      (cleardb db))))

(defn setupdb [tests]
  (initdb db) ;; create the tables
  (try
    (tests)
    (finally
      (teardown db)))) ;; drop the tables

(use-fixtures :each clear)
(use-fixtures :once setupdb)

Then I remembered a technique someone once mentioned where you use a transaction that you roll back instead of starting with a fresh db each time. It's supposed to be a lot faster.

After a little experimentation, I came up with this:

(defn clear [test]
  (sql/with-db-transaction [db db]
    (sql/db-set-rollback-only! db)
    (binding [db db] ;; rebind dynamic var db, used in tests
      (test))))

We open a transaction, immediately set it to rollback (which it will do when the transaction closes). Then we have to rebind our dynamic db var, which holds the current connection. And inside of that we run the test. Inside of the transaction, anything you write to the database will be available to read. When the test ends, the transaction closes and it rolls back all of the changes, leaving the database empty again.

The result? Running the tests went from 10 seconds to 2 seconds. They still start and end with a clean database, but it's done faster with a transaction.

The one gotcha that I ran into was that the PostgreSQL function now() was always returning the same time within the transaction. I had made an assumption (that was true before) that different calls would happen at different times. That assumption was no longer true inside the transaction. I had to fix the code to not rely on time.

The other part of this technique, which I did not really have to use, was that you can set up your database with test data in the :once fixture. It's costly to set up the test data, but because you're rolling back transactions, once it's set up it's quick to reset it.

If you'd like to learn more about testing in Clojure, you might be interested in my LispCast Intro to clojure.test. In it, we cover test namespaces, assertions, running your tests, and of course fixtures. It's an interactive course with exercises, screencasts, animations, and code. You should also check out the free cheatsheet below.