The next.jdbc project uses the embedded PostgreSQL libraries for testing: https://github.com/seancorfield/next-jdbc/blob/develop/test/next/jdbc/test_fixtures.clj

At work, we use MySQL (with next.jdbc) and we have our own homegrown migration code to create our test database from scratch in CI, but I would highly recommend https://github.com/yogthos/migratus for that, so you don't have to roll your own.

Migrations in QA/production just apply the new changes that are needed, but in dev/test/CI you can start from a fresh database (or no database at all) and run all the migrations to bring things up to the current level. For tests, we have fixtures (as in clojure.test fixtures) that setup database content as needed and/or tear it down.

One thing I will say: use the same DB for dev/test/CI that you plan to use for QA/production! Do not use a different DB just because you think it will be easier to setup/teardown.

You have a sequence of hash maps? Then you can do

(jdbc/execute-batch! ps (map (juxt :the :columns :i :want) my-hash-map-sequence))

If you don't know the keys in advance, you can get them with

(into #{} (comp (map keys) cat) my-hash-map-sequence)

But it is necessary to provide the keys in order, yes? (hence the use of a vector, right?) I think where I'm a little fuzzy is the necessity of explicitly declaring a null row value. What I already did is this

(->> test-data
       (apply merge {})
       (map (comp
             #(apply hash-map %)
             #(assoc % 1 nil)))
       (apply merge (sorted-map)))

You could get the column names and use them in the SQL creation...

(I'd probably use HoneySQL to build the SQL here)

You don't need to add nil entries yourself if you use my approach: the juxt call will produce nil for each key that is missing from the hash map.

I see. I'll give that a try. I know when I'm overthinking things (often). Thank you!

user=> (require '[honey.sql :as sql])
nil
user=> (def data [{:a 1} {:b 2 :a 3} {:a 4 :c 4} {:a 2}])
#'user/data
user=> (def cols (into #{} (comp (map keys) cat) data))
#'user/cols
user=> (def sql (take 1 (sql/format {:insert-into :my-table :columns (seq cols) :values [(repeat (count cols) 0)]})))
#'user/sql
user=> sql
("INSERT INTO my_table (c, b, a) VALUES (?, ?, ?)")
user=> (map (apply juxt cols) data)
([nil nil 1] [nil 2 3] [4 nil 4] [nil nil 2])
user=>

It's a bit of a futz to generate the correct SQL but then throw away the generated parameters so you get the correct thing for jdbc/prepare but at least it is all generic.

I wanted to follow up on this: do you have a working solution now?

I had to put it by for the time bc of other work but I'll be back to it on Monday. I was moving in the right direction though.