The integration test vs unit test debate is a minefield. More particularly, see J.B. Rainsberger's talk, "Integrated Tests Are A Scam". I don't want to dig in to this particularly for this post, except to say that definitely if you are working on a greenfield project, watch that talk first.

Now for those who for reasons have to or want to do integration tests, there are several options. The fundamental problem is that databases are inherently stateful. Therefore, you need some way of getting back to a clean state at the beginning of each test.

We assume that the user is using some kind of database migration tool to manage the database development. That is that they already have "up" and "down" migrations. In this case, it's reasonable to solve the problem by doing the following:

• Before each test:
  1. Run the "down" migrations.
  2. Run the "up" migrations.

This is relatively trivial to implement as a fixture, assuming that you are using clojure.test.

The problem comes when this becomes intolerably slow, which can easily happen once you get above 10s of migrations. Let me refer back to the Rainsberger talk now, which is a must-watch. If you want to stick with integration testing, you'll need some hack or workaround. There are several possibilities.

One possibility is using database transactional rollbacks. I found that this was impractical when interacting with real code, because of the possibility of transactions committing from within the system under test itself.

There are several guides to this method:

• http://www.lispcast.com/clojure-database-test-faster
• http://bobnadler.com/articles/2015/03/04/database-tests-with-rollbacks-in-clojure.html
• https://www.reddit.com/r/Clojure/comments/376wjn/using_database_transactions_in_tests/

However I found this wouldn't work for when the code under test was using more hairy database interactions.

Another method that you can use is the following:

• Create a template database t that already has all "up" migrations applied.
• Assume an ephemeral database named e.
• For each test:
  1. Drop e.
  2. Make sure that t has all up migrations applied (should be a no-op).
  3. Create e from template database t.
  4. Run test against e.

The concept of a "template database" is PostgreSQL-specific, hence the post title. In my experience, this procedure will introduce about a second of latency to each test in practice (that's a rough measurement based on my laptop). In this way it definitely precludes running the whole suite at once (and please refer back to the Rainsberger talk), but can make isolated feature development much faster.

You can see the code below. Note that some-reporter is a Ragtime reporter, which has the signature [data-store op id], as below.

(defn reporter [data-store op id]
  ; ignore data-store
  (case op
    :up (debugp "migrating" id)
    :down (debugp "rolling back %s" id)
    :else (break)))

This is the functionality, with drop-and-create being the top-level function that the fixture should use.

; If the migration already happened, these tests will be fast in any case.
(defn migrate [db-spec]
  (tracep "using authentication details" db-spec)
  (repl/migrate {:datastore  (ragtime.jdbc/sql-database db-spec)
                 :migrations (ragtime.jdbc/load-resources "migrations")
                 :reporter some-reporter}))


;; returns a vector
(defn get-drop-commands [test-databases-conf]
  (let [leaf-db-name (get-in test-databases-conf [:leaf :database])]
    [(format "DROP DATABASE IF EXISTS %s" leaf-db-name)
     (format "CREATE DATABASE %s WITH TEMPLATE %s OWNER %s"
             leaf-db-name 
             (get-in test-databases-conf [:root :database])
             (get-in test-databases-conf [:leaf :username]))]))

;; also returns a vector
(defn get-reassign-commands [test-databases-conf]
  (tracep "about to reassign" true)
  [(format "REASSIGN OWNED BY %s TO %s"
           (get-in test-databases-conf [:root :username])
           (get-in test-databases-conf [:leaf :username]))])

(defn drop-and-create [test-databases-conf]
  (try 
    (jdbc/db-do-commands test-db/root-postgres-db false
                         (get-drop-commands test-databases-conf))
    (catch Exception e
      (doseq [e0 e]
        (println (.getMessage e0)))))

  (try
    (jdbc/db-do-commands (merge test-db/leaf-postgres-db
                                (select-keys test-db/root-postgres-db [:user :password]))
                         false
                         (get-reassign-commands test-databases-conf))

    (catch java.sql.SQLException e
      (doseq [e0 e]
        (errorf e "unable to reassign permissions to ephemeral test role")))))

The following function also exists to forcibly disconnect other connections, because this functionality has the unfortunate property that it will fail if any other connections are already open.

;; Requires account used by the template database to have the
;; superuser privilege in postgres.
(defn disconnect-other-connections []
  (try
    (jdbc/db-do-commands test-db/root-postgres-db false 
                         "SELECT pg_terminate_backend(pg_stat_activity.pid)
                      FROM pg_stat_activity
                      WHERE pg_stat_activity.datname = 'my_ephemeral_test'
                      AND pid <> pg_backend_pid()")
    (catch Exception e
      (doseq [e0 e]
       (println (.getMessage e0))))))

All that remains is to wrap this up in a clojure.test fixture. You can use the disconnect-other-connections functionality or you can choose not to. Bear in mind that this will introduce a large amount of complexity into your test environment. Now you have to manage several databases in the test environment, as well as role name/password permissions for each one.