# datahike-sqlite SQLite storage backend for Datahike, the open datalog database ## datahike-sqlite.core # datahike-sqlite.core ## datahike-sqlite.konserve # datahike-sqlite.konserve ## default-table [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L22-L22) --- ## sqlite-bulk-insert-batch-size [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L24-L24) --- ## default-sqlite-pragmas [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L25-L32) --- ## with-write-tx ```clojure (with-write-tx db f) ``` Wrapper around the with-write-tx macro, for use in situations where we cannot use macros directly. [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L34-L38) --- ## init-db ```clojure (init-db db-spec) ``` [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L40-L46) --- ## create-statement ```clojure (create-statement table) ``` [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L48-L49) --- ## upsert-statement ```clojure (upsert-statement table id header meta value) ``` [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L51-L55) --- ## select-exists-statement ```clojure (select-exists-statement table id) ``` [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L57-L58) --- ## delete-row-statement ```clojure (delete-row-statement table store-key) ``` [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L60-L61) --- ## select-row-statement ```clojure (select-row-statement table id) ``` [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L63-L64) --- ## select-rows-statement ```clojure (select-rows-statement table ids) ``` [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L66-L69) --- ## select-ids-statement ```clojure (select-ids-statement table ids) ``` [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L71-L74) --- ## select-table-exists-statement ```clojure (select-table-exists-statement table) ``` [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L76-L77) --- ## select-all-ids-statement ```clojure (select-all-ids-statement table) ``` [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L79-L80) --- ## delete-rows-statement ```clojure (delete-rows-statement table store-keys) ``` [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L82-L85) --- ## update-id-statement ```clojure (update-id-statement table from to) ``` [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L87-L88) --- ## multi-insert-statement ```clojure (multi-insert-statement table batch-size) ``` [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L90-L98) --- ## copy-row-statement ```clojure (copy-row-statement table to from) ``` [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L100-L104) --- ## delete-store-statement ```clojure (delete-store-statement table) ``` [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L106-L107) --- ## change-row-id ```clojure (change-row-id db table from to) ``` [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L109-L113) --- ## read-all ```clojure (read-all db table id) ``` [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L115-L122) --- ## ->SQLiteRow ```clojure (->SQLiteRow db key data cache) ``` [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L129-L171) --- ## SQLiteRow [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L129-L171) --- ## map->SQLiteRow ```clojure (map->SQLiteRow m) ``` [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L129-L171) --- ## ->SQLiteTable ```clojure (->SQLiteTable db-spec db table) ``` [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L195-L276) --- ## SQLiteTable [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L195-L276) --- ## map->SQLiteTable ```clojure (map->SQLiteTable m) ``` [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L195-L276) --- ## prepare-spec ```clojure (prepare-spec db-spec opts-table) ``` [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L278-L282) --- ## connect-store ```clojure (connect-store db-spec & {:keys [opts] :as params}) ``` [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L284-L301) --- ## release ```clojure (release store env) ``` Closes the SQLite database connection pools. [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L303-L310) --- ## delete-store ```clojure (delete-store db-spec & {:keys [table opts]}) ``` [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L312-L317) --- ## store-exists? ```clojure (store-exists? db-spec & {:keys [table opts]}) ``` [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/konserve.clj#L319-L329) ## datahike-sqlite.store-config # datahike-sqlite.store-config ## default-table [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/store_config.clj#L7-L7) --- ## derive-store-id ```clojure (derive-store-id {:keys [dbname table]}) ``` [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/store_config.clj#L9-L13) --- ## store-id ```clojure (store-id store-config) ``` [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/store_config.clj#L15-L18) --- ## ensure-store-id ```clojure (ensure-store-id store-config) ``` [source,window=_blank](https://github.com/outskirtslabs/datahike-sqlite/blob/main/src/datahike_sqlite/store_config.clj#L20-L24) ## Datahike SQLite Backend # 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.