In-memory database fixtures with Clojure and sqlite

For a recent project, I need to extract data from a sqlite3 database. Writing the Clojure code to retrieve data was very straight-forward with clojure.java-jdbc and java-jdbc/dsl. Naturally, I wanted to have some tests for this code as well. In a previous Python project, I had a lot of fun using sqlite’s in-memory feature to run very speedy database tests, so of course I wanted this for my current Clojure project, too. This turned out not to be so easy as I had expected though, so I’m documenting it here for the next naive soul. My initial attempt with clojure.java.jdbc, java-jdbc/dsl and midje looked basically like this:

    (def testdbspec
      {:subprotocol "sqlite"
       :subname ":memory:"})

    (defn make-bookmark-table []
      (jdbc/with-transaction [db testdbspec]
         (jdbc/db-do-prepared db
           (ddl/create-table :bookmarks
                      [:id :int :primary :key]
                      [:type :int]
                      [:title "longvarchar"]))))

    (defn add-bookmark []
      (jdbc/with-transaction [db testdbspec]
         (jdbc/db-do-prepared db
           (str 
              "INSERT INTO bookmarks (id, type, title) "
              "VALUES ('12453', '2', 'a bookmark')"))))

    (defn setup-database [db]
       (make-bookmark-table)
       (add-bookmark)

    (facts "Testing database access to bookmarks"
       (with-state-changes [(before :facts (setup-database))]
            (fact "We can retrieve a list of bookmarks"
                (fetch-tags :dataspec testdb) => [{:title "a bookmark"}]))))

This will fail quite early, because basically as soon as the with-transaction in make-bookmark-table has finished its work, the connection to the database will be closed. As a result, when the next with-transaction or jdbc\query is run, you’ll connect to a fresh in-memory database which doesn’t have the tables you just created. My old Python test code didn’t have this problem, because the setUp method of the TestCase would create the database connection (via sqlalchemy’s create_engine) and would keep it alive until the TestCase tearDown method would run.

I tried giving back the database connection from make-bookmark-table, but this just results in a “connection closed” error. Unfortunately, clojure.java.jdbc doesn’t support opening and closing the connection yourself. Sure, you can use get-connection, but you can’t feed this into either with-transaction or query. query uses with-open internally, which will conveniently close the connection for you. In a post on the perils of dynamic scope Stuart Sierra calls this the Dynamically-Scoped Singleton Resource and files it under ‘anti-pattern’. I gotten bitten pretty exactly by what Stuart describes: when dealing with sqlite’s in-memory feature, we would like to manage the connection ourselves, but we can’t.

After banging my head against this for a while, the only option I could come up with resorts to extract the relevant with-transaction from the setup code. Instead you have to wrap the tests with the transaction and then call the setup code, like this:

    (defn make-bookmark-table [db]
      (jdbc/db-do-prepared db
           (ddl/create-table :bookmarks
                      [:id :int :primary :key]
                      [:type :int]
                      [:title "longvarchar"])))

    (defn setup-tables [db]
       (make-bookmark-table db))

    (defn add-bookmark [db]
       (jdbc/db-do-prepared db
           (str 
              "INSERT INTO bookmarks (id, type, title) "
              "VALUES ('12453', '2', 'a bookmark' )")))

    (defn remove-bookmark [db]
       (jdbc/db-do-prepared db
            (str "DELETE FROM bookmarks WHERE id = '12453")))

    (facts "Testing database access to bookmarks"
       (jdbc/with-db-transaction [db testdbspec]
            (setup-tables db)
            (with-state-changes [(before :facts (add-boomark db))
                                          (after :facts (remove-boomark db))]
                 (fact "We can retrieve a list of bookmarks"
                     (fetch-tags :dataspec db) => [{:title "a bookmark"}]))))

This works as expected.