# Datahike SQLite Backend

> SQLite storage backend for Datahike, the open datalog database.

![doc](https://img.shields.io/badge/doc-outskirtslabs-orange.svg)
![status: experimental](https://img.shields.io/badge/status-experimental-red.svg)

This library provides a backend for
[Datahike](https://github.com/replikativ/datahike) using
[SQLite](https://www.sqlite.org) as the backing store, with direct native
integration through
[sqlite4clj](https://github.com/andersmurphy/sqlite4clj).

Project status: **[Experimental](https://docs.outskirtslabs.com/open-source-vital-signs#experimental)**.

## Why sqlite4clj?

Unlike the [datahike-jdbc](https://github.com/replikativ/datahike-jdbc/)
backend, this implementation uses
[sqlite4clj](https://github.com/andersmurphy/sqlite4clj) - a minimalist
FFI binding to SQLite’s C API using Java 22’s Foreign Function Interface
(Project Panama). This approach offers several advantages:

* Bypasses JDBC overhead by interfacing directly with SQLite’s C API
through FFI for direct SQLite access.
* SQLite’s embedded nature doesn’t require thread-backed connection
pools like c3p0/HikariCP, eliminating that complexity.
* Provides better performance through cached prepared statements per
connection and inline caching of column reading functions.
* Eliminates dependencies on sqlite-jdbc, c3p0, and next.jdbc for a
smaller footprint.
* Easier access to SQLite-specific features and pragmas for targeted
optimizations.
* More suitable architecture for SQLite’s single-writer, multiple-reader
model.

## Installation

Include the library in your deps.edn:

```clojure
 io.replikativ/datahike   {:mvn/version "0.7.1624"} ;; Use latest 0.7.x version
 ramblurr/datahike-sqlite {:git/url "https://github.com/ramblurr/datahike-sqlite"
                           :git/sha "c94e449be351b13c7b279d39ee3266cc22dd8f7d"}
```

### Dependencies

* The https://clojars.org/io.replikativ/datahike[io.replikativ/datahike
dependency] must be provided by your project and must be 0.7.x or greater
* [sqlite4clj](https://github.com/andersmurphy/sqlite4clj) requires Java
22 or later.
* If you use this library as a git dependency, you will need to prepare
the library with `clj -X:deps prep`.
* You must include
`:jvm-opts ++[++"--enable-native-access=ALL-UNNAMED"++]++` in your
deps.edn alias.
* When creating an executable jar file, you can avoid the need to pass
this argument by adding the manifest attribute
`Enable-Native-Access: ALL-UNNAMED` to your jar.

### Example

```clojure
(require
 '[datahike-sqlite.core] ;; required to pull in the multi-method implementations
 '[datahike.api :as d])

(def cfg {:store {:backend :sqlite
                  :dbname  "foobar.sqlite"
                  ;; required by modern konserve/datahike
                  :id #uuid "550e8400-e29b-41d4-a716-446655440000"
                  ;; see sqlite4clj.core/init-db! for the possible options
                  :sqlite-opts {:pool-size 4}}})

(d/database-exists? cfg)
;; => false

(d/create-database cfg)

(def conn (d/connect cfg))

(d/transact conn [{:db/ident       :artifact
                   :db/valueType   :db.type/string
                   :db/cardinality :db.cardinality/one}
                  {:db/ident       :level
                   :db/valueType   :db.type/long
                   :db/cardinality :db.cardinality/one}])

(d/transact conn [{:artifact "Mighty Teapot" :level 20}])

(d/q '[:find (pull ?e [*])
       :in $ ?artifact
       :where [?e :artifact ?artifact]]
     @conn "Mighty Teapot")


(d/release conn)

;; this will delete the table in the sqlite file,
;; but will not delete the sqlite file itself
(d/delete-database cfg)
```

## Documentation

* [Docs](https://docs.outskirtslabs.com/datahike-sqlite/next/)
* [API Reference](https://docs.outskirtslabs.com/datahike-sqlite/next/api)
* [Support via GitHub Issues](https://github.com/outskirtslabs/datahike-sqlite/issues)

## Configuration

The value for `:store` is a configuration map. To invoke datahike-sqlite
(this library) you must include `:backend :sqlite` in that map.

You must also include `:dbname`, a path to the SQLite file.

Konserve 0.9 and newer Datahike releases require a UUID under `:id`.
Use a stable UUID literal in your store config.

You can optionally include the key `:sqlite-opts` with an options map
which will be passed to
[`sqlite4clj.core/init-db!`](https://github.com/andersmurphy/sqlite4clj).

## Development

### Benchmarking

The SQLite KV benchmark suite lives in
`bench/datahike_sqlite/kv_benchmark.clj`.

Run the full suite with:

```bash
clojure -M:dev:bench -m datahike-sqlite.kv-benchmark
```

Run a smaller smoke benchmark with:

```bash
clojure -M:dev:bench -m datahike-sqlite.kv-benchmark '{:mode :smoke}'
```

Run the suite at a specific entry count with:

```bash
clojure -M:dev:bench -m datahike-sqlite.kv-benchmark 1000
```

Write the benchmark results to a file with:

```bash
clojure -M:dev:bench -m datahike-sqlite.kv-benchmark '{:n 1000 :output "bench/results/sqlite-kv.txt"}'
```

### Testing

```bash
bb test
```

### Formatting

```shell
bb fmt
```

### Linting

```shell
bb lint
```

## License: MIT License

Copyright © 2025 Casey Link casey@outskirtslabs.com

Distributed under the [MIT](https://spdx.org/licenses/MIT.html), like dathike.