What is sqlite4clj?

⚠️ WARNING: This project is highly experimental and not production ready.

⚠️ WARNING: API can change at any time! Use at your own risk.

Conceptually sqlite4clj is inspired by sqlite4java a sqlite libaray that doesn't use the JDBC interface. The goal of sqlite4clj is to have a minimalist FFI binding to SQLite's C API using Java 22 FFI (project panama). Tighter integration with SQLite can in theory offer better performance and features not available through JDBC interfaces.

By using coffi to interface with SQLite's C API directly with FFI we bypass the need for: sqlite-jdbc, hikariCP and next.jdbc. This massively reduces the amount of code that needs to be maintained (and a much smaller jar), allows us to use Clojure to interface with SQLite directly. It also makes it easier to add SQLite specific features. In my case I was looking to cache prepared statement for each connection (which is not possible with HikariCP) but can lead to considerable performance gains on complex queries.

This also frees up scope for common things like binary encoding and decoding as well as compression decompression to leverage SQLite's blob type.

Currently, this project is very much a proof of concept. But, I'm hoping to ultimately make it production ready.

Usage

Currently this library is not on maven so you have to add it via git deps (note: coffi requires at least Java 22):

andersmurphy/sqlite4clj 
{:git/url "https://github.com/andersmurphy/sqlite4clj"
 :git/sha "1a82b2b425b22539f9cc17b4cbdd892676c2a9f9"}

Initialise a db:

(ns scratch
  (:require [sqlite4clj.core :as d]))
   
(defonce db
  (d/init-db! "database.db"
    {:read-only true
     :pool-size 4
     :pragma    {:foreign_keys false}}))

This creates a :reader connection pool with a number of connections equal to :pool-size and a single :writer connection. Single writer at the application level allows you to get the most out of SQLite's performance in addition to preventing SQLITE_BUSY and SQLITE_LOCKED messages. Finally, it makes it trivial to do transaction batching at the application layer for increased write throughput.

Running a read query:

(d/q (:reader db)
  ["SELECT chunk_id, state FROM cell WHERE chunk_id = ?" 1978])

=>
[[1978 0]
 [1978 0]
 [1978 0]
 [1978 0]
 [1978 0]
 [1978 0]
 ...]

Unwrapped results when querying a single column:

(d/q (:reader db)
  ["SELECT chunk_id, state FROM cell WHERE chunk_id = ?" 1978])

=>
[1978
 1978
 1978
 1978
 ...]

Inserting and updating:

(d/q (:writer db)
  ["INSERT INTO session (id, checks) VALUES (?, ?)" "user1" 1])

=>
[]

(d/q (:writer db)
  ["UPDATE session SET id = ?, checks = ? where id = ?"
   "user1" 2 "user1"])
=>
[]

Write transactions:

(d/with-write-tx [tx writer]
  (let [sid "user1"
        [checks] (d/q db ["SELECT checks FROM session WHERE id = ?" sid])]
    (if checks
      (d/q tx ["UPDATE session SET checks = ? WHERE id = ?" checks sid])
      (d/q tx ["INSERT INTO session (id, checks) VALUES (?, ?)" sid 1]))))

Read transactions:

(d/with-read-tx [tx writer]
  (= (d/q tx ["SELECT checks FROM session WHERE id = ?" "user1"])
     (d/q tx ["SELECT checks FROM session WHERE id = ?" "user2"])))

Connection pools not thread pools

The connection pools are not thread pools, they use a LinkedBlockingQueue to limit/queue access. Unlike thread pool this allows for having as many databases as you want without taking up large amount of memory particularly if you run your queries from virtual threads. With SQLite it's common to have many databases for isolation and convenience. It's not uncommon to have a database per tenant or user, or even simply as a persistent cache. So making this cheap is important as it's one of SQLite's super powers.

Why is this fast?

The two main speedups are from caching query statements at a connection level and using inline caching of column reading functions.

Further reading

Clojure: SQLite C API with project Panama and Coffi

Building SQLite from source

gcc -shared -Os -I. -fPIC -DSQLITE_DQS=0 \
   -DSQLITE_THREADSAFE=2 \
   -DSQLITE_DEFAULT_MEMSTATUS=0 \
   -DSQLITE_DEFAULT_WAL_SYNCHRONOUS=1 \
   -DSQLITE_LIKE_DOESNT_MATCH_BLOBS \
   -DSQLITE_MAX_EXPR_DEPTH=0 \
   -DSQLITE_OMIT_DECLTYPE \
   -DSQLITE_OMIT_DEPRECATED \
   -DSQLITE_OMIT_PROGRESS_CALLBACK \
   -DSQLITE_OMIT_SHARED_CACHE \
   -DSQLITE_USE_ALLOCA \
   -DSQLITE_STRICT_SUBTYPE=1 \
   -DSQLITE_OMIT_AUTOINIT \
   -DSQLITE_DISABLE_PAGECACHE_OVERFLOW_STATS \
   -DSQLITE_ENABLE_STAT4 \
   sqlite3.c -lpthread -ldl -lm -o sqlite3.so