Querying postgres using composite keys with YeSQL / Clojure

composite-keys-in-yesql

;; currently anonymous record types are not supported by postgres/jdbc
;; however, it is possible to create a custom type and query using that

CREATE TYPE some_composite_key AS (first_id UUID,second_id TEXT);

;; implement jdbc/ISQLValue
(defrecord SomeCompositeKey [first_id second_id]
  jdbc/ISQLValue
  (sql-value [_]
    (doto (PGobject.)
      (.setType "some_composite_key")
      (.setValue (format "(%s,%s)" first_id second_id)))))

;; then in YeSQL, we can do this
-- name: db-get-some-things
SELECT * FROM some_table
WHERE (first_id,second_id) IN (:some_composite_keys);

;; and, call from Clojure like this
(db-get-some-things
    {:some_composite_keys
       [(->SomeCompositeKey "11ecad7c-9b3a-4f1a-ad66-3a2566642989" "52de8508e4b04f23cfa70e58")
        (->SomeCompositeKey "11ecad7c-9b3a-4f1a-ad66-3a2566642989" "52de8508e4b04f23cfa70e9c")]}
    {:connection *connection*})