We use SQLite4Java at work, and I’m playing around with our databases from Clojure at the moment and just wanted to quickly jot down how to do it.

First, native library management in SQLite4Java is a little hairy: it’s going to look for the libraries either in java.library.path, sqlite4java.library.path, alongside the SQLite4Java jar file, or you can explicitly tell it where to find things. The Maven config to do this automatically is suitably ugly, and as far as I know, there’s not a good way to do the same thing with Leiningen.

I opted to just explicitly tell it for now. I list the SQLite4Java dependency in my project.clj, copied the native libraries I’m interested in into ${project.dir}/natives, and then just added this at startup time:

(let [native-dir (clojure.java.io/file (System/getProperty "user.dir") "natives")]
  (com.almworks.sqlite4java.SQLite/setLibraryPath (str native-dir)))

(I’m leaving all of the namespace components in there to be explicit: I require/import them as normal in my code.)

Next issue: actually running queries against a database has to be done using a SQLiteConnection instance, and those instances are limited to being used from the thread that created them. That precludes using them from the REPL: you don’t have any control over what thread your expressions are going to be executed from. So, you need to use SQLiteQueue, which wraps a SQLiteConnection and ensures all calls come from a single thread.

SQLiteQueue works by taking SQLiteJob instances (basically just a function) and running them in first-in-first-out order. Unfortunately, SQLiteQueue can’t just take a Callable, so you can’t use Clojure functions directly. Wrapping a function up in a job is pretty easy though, and is the first time I’ve needed to use clojure.core/proxy:

(defn job
  "Returns a SQLiteJob proxy that calls the given function."
  [job-fn]
  (proxy [com.almworks.sqlite4java.SQLiteJob]
    []
    (job [^SQLiteConnection conn]
      (job-fn conn))))

Then using those jobs is done by calling SQLiteQueue::execute, which returns the job again. Those jobs implement Future, so you can use them as normal, or they have a convenience method SQLiteJob::complete that deals with exceptions for you.

(def get-all-user-ids
  (job
    (fn [^SQLiteConnection conn]
      (let [query (.prepare conn "SELECT id FROM users")]
        (try (loop [ids []
                    continue (.step query)]
               (if-not continue
                 ids
                 (recur (conj ids (.columnLong query 0)) (.step query))))
        (finally (.dipose query)))))))

(.complete (.execute queue get-all-user-ids))

So all bundled together, my little library for working with SQLite4Java looks like this right now:

(ns jfischer.db
  (:require [clojure.java.io :as io])
  (:import [com.almworks.sqlite4java SQLiteQueue
                                     SQLiteConnection
                                     SQLiteJob]))

(defn open-db
  "Creates (and starts, because I'm forever forgetting to .start it at
  the REPL) a SQLiteQueue instance. The zero-argument version opens an
  in-memory database, otherwise give it a filename."
 ([] (open-db nil))
 ([filename]
  (.start (SQLiteQueue. (io/file filename)))))

(defn job
  "Returns a SQLiteJob proxy that calls the given function."
  [job-fn]
  (proxy [SQLiteJob]
    []
    (job [^SQLiteConnection conn]
      (job-fn conn))))

(defn exec-job
  "Runs the given job on the database queue and returns its result."
  [^SQLiteQueue db
   ^SQLiteJob job]
  (.complete (.execute db job)))