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.
Max
In theory enabling sqlite’s shared cache would work for this purpose:
{:classname "org.sqlite.JDBC" :subprotocol "sqlite" :subname "file::memory:?cache=shared"}
Sources: https://www.sqlite.org/inmemorydb.html and https://groups.google.com/forum/#!topic/xerial/gGeqCw3KzvU
In practice it didn’t work for me and I ended up switching to H2 in-memory database, where this works:
{:classname "org.h2.Driver" :subprotocol "h2:mem" :subname "test;DB[underscore]CLOSE[underscore]DELAY=-1" :user "sa" :password ""}
(Replace [underscore] above with an underscore. Couldn’t be typed as such, the markdown parser seems a little aggressive.)