2025-05-31 sql | Clojure Slack Archive

sql

adi 2025-05-31T17:25:53.212049Z

Solved a problem and learned something obvious, while typing my frustrated error report here. TL;DR: running VACUUM too early in my app's migration path was the problem. • Because vacuuming requires schema. • My schema requires third-party extensions. • The from-scratch migration path "just works" because there's nothing to VACUUM. • Because, obviously, the schema does not exist at the time of creating the DB. ◦ (Narrator, with dripping sarcasam: Surpriiiiiise!) • The second time around, the schema does exist. • Which means the migration path tries to VACUUM before the extensions are loaded. ◦ And so... the following vexation occurs to the uninitiated (pun intended). --- sqlite-jdbc's runtime load extension behaviour is was vexing me (latest-greatet xerial, and next.jdbc)

com.github.seancorfield/next.jdbc {:mvn/version "1.3.1002"}
org.xerial/sqlite-jdbc {:mvn/version "3.49.1.0"}

I have an sqlite3 db containing a schema that uses a function provided by a third-party extension. • On creating a fresh DB, the app's migration path "just works". ◦ Extensions are loaded. ◦ Migrations run. ◦ Tests pass for database CRUD operations. • Now, if I /halt/ the session, and try to DB again, I get an SQLite error. ◦ The exact same application start-up path fails!

Execution error (SQLiteException) at org.sqlite.core.DB/newSQLException (DB.java:1179).
[SQLITE_ERROR] SQL error or missing database (no such function: TEXT_SPLIT)

--- Narrator: and so our hapless programmer discovers yet again, that he is the bug, not the JDBC driver. --- P.S. See ugly code in the comment thread. Good ol' println -driven Scientific Debugging (TM) saved the day yet again.

adi 2025-05-31T17:30:38.899359Z

"Ugly But Works" Scientific Debugging (TM). ---

(defn set-up!
  ;; 
  [dbname {:keys [dbtype] :as db-spec}]
  (let [db-spec (assoc db-spec :dbname dbname)
        ;; 
        config (doto (new SQLiteConfig)
                 (.enableLoadExtension true))
        _ (println "sqliteconfig is " config)
        properties (.toProperties config)
        connection
        (DriverManager/getConnection
         "jdbc:sqlite:/path/to/evalapplydotorg/multiproject/dev_primary.sqlite3"
         properties)
        _ (println "connection is " connection)
        statement (.createStatement connection)
        _ (println "createstamement thingy" statement)
        ;; _ (.executeQuery statement "PRAGMA enable_load_extension = ON")
        _ (.executeQuery statement "SELECT load_extension('/path/to/evalapplydotorg/multiproject/parts/resources/sqlite-extensions/sqlean/text.so')")
        _ (.executeQuery statement "SELECT load_extension('/path/to/evalapplydotorg/multiproject/parts/resources/sqlite-extensions/sqlean/uuid.so')")
        datasource ^HikariDataSource (jdbc-conn/->pool
                                      HikariDataSource
                                      db-spec)]
    (log/info "==== Starting DB with db-spec ====" db-spec)
    ;; next.jdbc doc says to fetch and then open/close a connection
    ;; to initialize a pool and perform validation check.
    ;; Using with-open on the read and write paths does it for us.
    (with-open [connection (jdbc/get-connection datasource)]
      ;; xerial's pragma ENUM properties do not include auto_vacuum,
      ;; so we have to do this manually, instead of via config. Sigh.

      (println "WITH-OPENED CONNECTION IS: " connection)

      #_(jdbc/execute! connection [(format "PRAGMA auto_vacuum = %s"
                                         (:auto_vacuum db-spec))])
      ;; auto_vacuum value gets set only after vacuum

      #_(println "ABOUT TO VACUUM ")   ;; <---- AHAAAAA!!!!!! FOUND YOU #%$!^&.
      #_(jdbc/execute! connection ["VACUUM"])

      ;; Always run optimize at startup, after all
      ;; configurations are set and DDLs are executed.
      ;; 

      (println "ABOUT TO PRAGMA OPTIMIZE ")
      (jdbc/execute! connection ["PRAGMA optimize"]))
    (log/info "Initialized pooled datasource PRAGMAS:" (get-pragma-settings datasource))
    datasource))

adi 2025-06-01T16:26:03.495519Z

The bug I made is dead. Long live the bug.

Clojurians Log v2

sql