Clojurians Log v2

Update: Fix. Somehow, a few moments later the code started working, likely because I manually sent the extend-protocol to the REPL. So the cause seem to be namespace loading [order]. Question about next.jdbc (1.09.384). I have code that sometimes does not behave as expected - namely I expect to read Timestamps as java.time.Instant but sometimes it does not happen. The test:

(satisfies? next.jdbc.result-set/ReadableColumn java.sql.Timestamp)
(:TS (execute-one! ds ["SELECT cbm_timestamp as TS FROM tem_organization WHERE organization_number = ?" "1234"]))
=> #inst"2020-09-03T12:43:42.000000000-00:00" ;; WRONG - expected java.time.Instant, not java.util.Date!

(extend-protocol next.jdbc.result-set/ReadableColumn
  Timestamp
  (read-column-by-label [^Timestamp x _] (.toInstant x))
  (read-column-by-index [^Timestamp x _2 _3] (.toInstant x)))

(next.jdbc.result-set/read-column-by-label (java.sql.Timestamp. 0) "label")
=> #t/inst"1970-01-01T00:00:00.000-00:00" ; t/inst is my reader tag for java.time.Instant

@holyjak Yeah, namespace loading order would have been my guess for that. Glad you got it working!

hum, tried writing a Flyway JavaMigration by reifying org.flywaydb.core.api.migration.JavaMigration, and was so close until realising that Flyway tries to lookup the location of the code in disk (something like (.getLocation (.getCodeSource (.getProtectionDomain (.getClass <the-migration>)))) which is null when running at the repl without AOT 😕

after writing the above, realising that there might be another rabbit hole through https://flywaydb.org/documentation/api/javadoc/org/flywaydb/core/api/resolver/MigrationResolver

@viesti You can set the location yourself. Here's a snippet showing how I run flyway migrations in a small project:

(ns rocks.mygiftlist.migrate
  (:require [clojure.string :as str])
  (:import [org.flywaydb.core Flyway]
           [org.flywaydb.core.api Location]))

(defn database-url->datasource-args [database-url]
  (let [{:keys [userInfo host port path]} (bean (java.net.URI. database-url))
        [username password] (str/split userInfo #":")]
    [(str "jdbc:postgresql://" host ":" port path) username password]))

(defn -main [& _args]
  (let [[jdbc-url username password] (database-url->datasource-args (System/getenv "DATABASE_URL"))]
    (.. (Flyway/configure)
      (dataSource jdbc-url username password)
      (locations (into-array Location [(Location. "filesystem:./migrations")]))
      (load)
      (migrate))))

Ah, you're using java migrations. My mistake, then. I do believe those need to be compiled in advance. :(

yeah

found a related thing from the internet, https://github.com/quarkusio/quarkus/pull/5325, where they generate in-memory classes with a suitable ProtectionDomain that satisfies Flyway

I think there's some rabbit holes ahead 🙂

Good luck!

Hi, I am not sure if this has been already discussed topic. I am using Postgres and next.jdbc. I don’t know how to insert data to a table with a column that stores an array of composite types (composite_type[]). Next.jdbc documentation refers to postgres jdbc driver documentation https://jdbc.postgresql.org/documentation/head/arrays.html. I understand that Postgres JDBC driver supports only few array types with PreparedStatemet? Do I understand it correctly? Is there some other way how to achieve this with next.jdbc? Thank you for your answers.

Thanks! :)

The driver supports only primitive array types via setObject() but you can create and set other array types by overriding the default next.jdbc behavior.

See https://cljdoc.org/d/seancorfield/next.jdbc/1.1.547/doc/getting-started/prepared-statements#prepared-statement-parameters where it shows using protocol extension via metadata to pass in your own set-parameter implementation, which could call setArray()

You can call (.getConnection ps) on your PreparedStatement to get the connection, on which you could call .createArrayOf() passing the database type name, and (into-array your-vector), and then you would call (.setArray ps i array) to set the array-valued parameter.

@michal.kurtak ^

(defn set-array [data ^PreparedStatement ps ^long i]
  (let [con (.getConnection ps)
        array (.createArrayOf con "composite_type" (into-array data))]
    (.setArray ps i array)))
(defn composite-array [data] (with-meta data {'next.jdbc.prepare/set-parameter set-array}))
...
(sql/insert! ds :table {:comp_col (composite-array some-vector)})

If you need it for multiple types, you could parameterize set-array with the DB type name...

(defn set-array [comp-type]
  (fn [data ^PreparedStatement ps ^long i]
    (let [con (.getConnection ps)
          array (.createArrayOf con comp-type (into-array data))]
      (.setArray ps i array))))
(defn composite-array [comp-type data] (with-meta data {'next.jdbc.prepare/set-parameter (set-array comp-type)}))
...
(sql/insert! ds :table {:comp_col (composite-array "composite_type" some-vector)})

(usual caveats: completely untested, off the top of my head code!)

@seancorfield thank you very much. i’ll try to experiment with set-parameter and .createArrayOf.

I'm trying to store EDN maps in PostgreSQL. Storing maps as VARCHAR / TEXT makes persisting easy:

(extend-protocol next.jdbc.prepare/SettableParameter
  IPersistentMap
  (set-parameter [^IPersistentMap v ^PreparedStatement ps ^long i]
    (.setString ps i (pr-str v))))

CREATE SCHEMA db;

CREATE TYPE db.edn AS (x VARCHAR);

CREATE TABLE db.err
 (id        UUID NOT NULL,
  throwable EDN  NOT NULL,
  PRIMARY KEY (id));

(extend-protocol next.jdbc.result-set/ReadableColumn
  PGobject
  (read-column-by-label [^PGobject v _  ] (edn/read-string (.getValue v)))
  (read-column-by-index [^PGobject v _ _] (edn/read-string (.getValue v))))

(extend-protocol next.jdbc.prepare/SettableParameter
  IPersistentMap
  (set-parameter [^IPersistentMap v ^PreparedStatement ps ^long i]
    (.setObject ps i (doto (PGobject.)
                       (.setType  "edn")
                       (.setValue (pr-str v))))))

(db/insert! (db-con) :err {:id (java.util.UUID/randomUUID)
                           :throwable (try (/ 2 0)
                                           (catch Throwable ex
                                             (Throwable->map ex)))})
;=> Execution error (PSQLException) at org.postgresql.core.v3.QueryExecutorImpl/receiveErrorResponse (QueryExecutorImpl.java:2553).
    ERROR: malformed record literal: "{:via [{:type java.lang.ArithmeticException ... } Detail: Missing left parenthesis.