storage: migrate from SQLite to H2

add ADR-0002 about migrating to H2

8 files changed, 52 insertions, 31 deletions

diff --git a/doc/adr/0002-why-h2.md b/doc/adr/0002-why-h2.md
new file mode 100644
index 0000000..4dcf10e
--- /dev/null
+++ b/doc/adr/0002-why-h2.md
@@ -0,0 +1,21 @@
+# ADR: Use H2 database

+

+Date: 2026-10-23

+

+## Context

+

+SQLite is the ideal choice for small services like webmention receivers, but in the Java ecosystem, the only production-ready SQLite JDBC is [`org.xerial/sqlite-jdbc`](https://github.com/xerial/sqlite-jdbc), which has its limitations.

+

+`sqlite-jdbc` is **not truly cross-platform**. It is mostly written in C and compiled to `.so`. Then native libraries are bundled to `.jar` and called from Java. SQLite itself is super lightweight, but bundling native libraries of **every OS and arch** is extremely heavy. Plus, `sqlite-jdbc` does not support OpenBSD, NetBSD and many platforms. I had to manually compile C libraries for weepinbell to work on those platforms and it'll also make `.jar` much heavier.[^1]

+

+It's safe to say that, at least for the time being, SQLite is not suitable for Java and Clojure.

+

+## Decision

+

+The closest thing to SQLite in Java/Clojure ecosystem is [H2](https://www.h2database.com/html/main.html). It's file-based, light-weight (~ 2.5 MB) and very fast.

+

+It's also a SQL database, with some minor incompatibilties with SQLite (syntax-wise and feature-wise). Migrating from SQLite to H2 has very little cost.

+

+The SQLite version was never released and shipped to users. The H2 version will replace SQLite in the `v1.0.0` release. Apart from the git history. It'll be like we never supported SQLite.

+

+[^1]: Also see [ADR-0001](./0001-sqlite-jdbc-exclusions.md)

diff --git a/project.clj b/project.clj
index c4188b3..1b45e41 100644
--- a/project.clj
+++ b/project.clj
@@ -22,7 +22,7 @@
                  [org.nibor.autolink/autolink "0.12.0"]
                  [io.github.tonsky/fast-edn "1.2.0"]
                  [com.github.seancorfield/next.jdbc "1.3.828"]
-                 [org.xerial/sqlite-jdbc "3.53.4.0"]
+                 [com.h2database/h2 "2.3.232"]
                  [nrepl "1.8.0"]]
   :managed-dependencies [[org.clojure/tools.reader "1.6.0"]
                          [org.ow2.asm/asm "9.11.1"]]
diff --git a/resources/org/sqlite/native/OpenBSD/x86_64/libsqlitejdbc.so b/resources/org/sqlite/native/OpenBSD/x86_64/libsqlitejdbc.so
deleted file mode 100755
index fc57dd4..0000000
--- a/resources/org/sqlite/native/OpenBSD/x86_64/libsqlitejdbc.so
+++ /dev/null
diff --git a/resources/sql/sqlite_init.sql b/resources/sql/h2_init.sql
index 012e9c6..2ca12bd 100644
--- a/resources/sql/sqlite_init.sql
+++ b/resources/sql/h2_init.sql
@@ -1,5 +1,5 @@
 create table if not exists webmention (
-  id integer primary key autoincrement,
+  id integer auto_increment primary key,
   hash text not null unique,
   wm_source text not null,
   wm_target text not null,
@@ -7,11 +7,11 @@ create table if not exists webmention (
   wm_target_domain text not null,
   wm_source_path text not null,
   wm_target_path text not null,
-  lastmod integer not null
+  lastmod bigint not null
 );
 
 create table if not exists microformats (
-  id integer primary key autoincrement,
+  id integer auto_increment primary key,
   wm_id integer not null,
   mf_type text not null,
   mf_data text not null, /* schema on read, store EDN string here */
@@ -22,7 +22,7 @@ create table if not exists microformats (
 );
 
 create table if not exists proxied_photo (
-  id integer primary key autoincrement,
+  id integer auto_increment primary key,
   wm_id integer not null,
   url text not null,
 
diff --git a/src/weepinbell/core.clj b/src/weepinbell/core.clj
index cb0033a..903d228 100644
--- a/src/weepinbell/core.clj
+++ b/src/weepinbell/core.clj
@@ -4,7 +4,7 @@
             [weepinbell.config :refer [load-config]]
             [weepinbell.webhook :as webhook]
             [weepinbell.storage.edn-storage :as edn]
-            [weepinbell.storage.sqlite :as sqlite]
+            [weepinbell.storage.h2 :as h2]
             [weepinbell.storage.worker :as worker]
             [clojure.core.async :refer [chan >!! <!!]]
             [taoensso.timbre :as timbre]
@@ -32,7 +32,7 @@
         data-path (:data-path config)
         bare-store (case (:storage-type config)
                      :edn (edn/use-store data-path)
-                     :sqlite (sqlite/use-store data-path))
+                     :h2 (h2/use-store data-path))
         [stop-worker async-store] (worker/start bare-store)
         ;; Hooks, a map of callback functions that processor calls 
         ;; on certain events. Can be used for webhooks and more.
@@ -85,6 +85,6 @@
 
 (defn -main [& args]
   (case (first args)
-    "migrate-db" (apply m/edn->sqlite (rest args))
+    "migrate-db" (apply m/edn->h2 (rest args))
     (start-engine)))
 
diff --git a/src/weepinbell/migration.clj b/src/weepinbell/migration.clj
index 4883b99..6579d84 100644
--- a/src/weepinbell/migration.clj
+++ b/src/weepinbell/migration.clj
@@ -1,26 +1,26 @@
 (ns weepinbell.migration

-  (:require [weepinbell.storage.sqlite :as sqlite]

+  (:require [weepinbell.storage.h2 :as h2]

             [weepinbell.storage.edn-storage :as edns])

-  (:import [org.sqlite SQLiteException]))

+  #_(:import [org.h2 SQLiteException]))

 

-(defn edn->sqlite 

+(defn edn->h2

   "Given a EDN file and a SQLite DB path, migrate data from that

    edn file to SQLite DB. It appends data to DB so the destination

    db can be empty or it can has some data."

-  [edn-file sqlite-db]

-  {:pre [(some? edn-file) (some? sqlite-db)]}

-  (let [ds (sqlite/make-datasource sqlite-db)]

-    (sqlite/create-tables ds)

+  [edn-file h2-db]

+  {:pre [(some? edn-file) (some? h2-db)]}

+  (let [ds (h2/make-datasource h2-db)]

+    (h2/create-tables ds)

     (edns/with-webmentions edn-file

       (fn [wms]

         (doseq [wm wms]

           (let [id (get-in wm [:webmention :id])]

             (println "Appending webmention" id "to database")

             (try 

-              (sqlite/insert-webmention ds wm)

-              (catch SQLiteException e

-                (println "Something wrong with data or database")

-                (println (.getMessage e)))

+              (h2/insert-webmention ds wm)

+;;              (catch SQLiteException e

+;;                (println "Something wrong with data or database")

+;;                (println (.getMessage e)))

               (catch Exception e

                 (println "Something unexpected happened.")

                 (println (.getMessage e))))))))

diff --git a/src/weepinbell/storage/sqlite.clj b/src/weepinbell/storage/h2.clj
index 59feb37..b42b5ba 100644
--- a/src/weepinbell/storage/sqlite.clj
+++ b/src/weepinbell/storage/h2.clj
@@ -1,4 +1,4 @@
-(ns weepinbell.storage.sqlite
+(ns weepinbell.storage.h2
   (:require [clojure.string :refer [blank? split]]
             [next.jdbc :as jdbc]
             [next.jdbc.result-set :refer [as-unqualified-lower-maps]]
@@ -55,7 +55,7 @@
   (let [mf (:microformats data)
         photo (get-in data [:extra :proxied-photo])
         wm_result (sql/insert! ds :webmention (wm->schema data))
-        wm_id (get wm_result (keyword "last_insert_rowid()"))]
+        wm_id (:id wm_result)]
     (when mf
       (sql/insert! ds :microformats (mf->schema wm_id data)))
     (when photo
@@ -113,14 +113,14 @@
 
 (defn make-db [db-file]
   (io/make-parents db-file)
-  {:dbtype "sqlite" :dbname db-file})
+  {:dbtype "h2" :dbname db-file})
 (defn make-ds [db]
   (jdbc/get-datasource db))
 (defn make-datasource [db-file]
   (jdbc/with-options (make-ds (make-db db-file))
                      {:builder-fn as-unqualified-lower-maps}))
 
-(def init-sql-script (io/resource "sql/sqlite_init.sql"))
+(def init-sql-script (io/resource "sql/h2_init.sql"))
 (defn create-tables [ds]
   (doseq [:let [statements (-> init-sql-script slurp (split #";"))]
           statement (remove blank? statements)]
diff --git a/test/weepinbell/storage_test.clj b/test/weepinbell/storage_test.clj
index b0df532..0885855 100644
--- a/test/weepinbell/storage_test.clj
+++ b/test/weepinbell/storage_test.clj
@@ -1,7 +1,7 @@
 (ns weepinbell.storage-test

   (:require [clojure.test :refer [testing is deftest]]

             [weepinbell.storage.edn-storage :as edns]

-            [weepinbell.storage.sqlite :as sqlite]

+            [weepinbell.storage.h2 :as h2]

             [weepinbell.storage.worker :as worker]

             [clojure.java.io :as io]

             [clojure.spec.alpha :as s]

@@ -12,16 +12,16 @@
 (def test-edn-file "tmp/testfile")

 (when (.exists (io/file test-edn-file))

   (io/delete-file test-edn-file))

-(def test-sqlite-db "tmp/testsqlite.db")

-(when (.exists (io/file test-sqlite-db))

-  (io/delete-file test-sqlite-db))

+(def test-h2-db "tmp/testh2")

+(when (.exists (io/file test-h2-db))

+  (io/delete-file test-h2-db))

 

 (def edn-storage (edns/use-store test-edn-file))

-(def sqlite-storage (sqlite/use-store test-sqlite-db))

+(def h2-storage (h2/use-store test-h2-db))

 (def storages [edn-storage

-               sqlite-storage

-               (last (worker/start sqlite-storage))

-               (last (worker/start edn-storage))])

+               h2-storage

+               (last (worker/start edn-storage))

+               (last (worker/start h2-storage))])

 

 ;; check if storages conform spec