Fork me on GitHub

I am seeing some strange behavior with next.jdbc and h2 (might be a bug). Porting migratus from -> next.jdbc I encountered this issue that causes tests to fail. The next statement returns nil - though it should return a record of inserted rows

(next.jdbc.sql/insert! (:db config) "foo_bar" {:id -1})
with trace
TRACE t12951: (next.jdbc.sql/insert! {:dbtype "h2", :dbname "./site.db"} "foo_bar" {:id -1})
TRACE t12952: | (next.jdbc.sql/insert! {:dbtype "h2", :dbname "./site.db"} "foo_bar" {:id -1} {})
TRACE t12953: | | (next.jdbc/execute-one! {:dbtype "h2", :dbname "./site.db"} ["INSERT INTO foo_bar (id) VALUES (?)" -1] {:return-keys true})
TRACE t12954: | | | (next.jdbc.protocols/-execute-one {:dbtype "h2", :dbname "./site.db"} ["INSERT INTO foo_bar (id) VALUES (?)" -1] {:return-keys true, :next.jdbc/sql-params ["INSERT INTO foo_bar (id) VALUES (?)" -1]})
TRACE t12955: | | | | (next.jdbc.protocols/get-datasource {:dbtype "h2", :dbname "./site.db"})
TRACE t12955: | | | | => #object[next.jdbc.connection$url_PLUS_etc$reify__6940 0x53ad92b8 "jdbc:h2:./site.db"]
TRACE t12956: | | | | (next.jdbc.protocols/-execute-one #object[next.jdbc.connection$url_PLUS_etc$reify__6940 0x53ad92b8 "jdbc:h2:./site.db"] ["INSERT INTO foo_bar (id) VALUES (?)" -1] {:return-keys true, :next.jdbc/sql-params ["INSERT INTO foo_bar (id) VALUES (?)" -1]})
TRACE t12957: | | | | | (next.jdbc.protocols/get-connection #object[next.jdbc.connection$url_PLUS_etc$reify__6940 0x53ad92b8 "jdbc:h2:./site.db"] {:return-keys true, :next.jdbc/sql-params ["INSERT INTO foo_bar (id) VALUES (?)" -1]})
TRACE t12957: | | | | | => #object[org.h2.jdbc.JdbcConnection 0x25b3a2ad "conn18: url=jdbc:h2:./site.db user="]
TRACE t12956: | | | | => nil
TRACE t12954: | | | => nil
TRACE t12953: | | => nil
TRACE t12952: | => nil
TRACE t12951: => nil
running the statement like this works as expected:
(jdbc/execute-one! (:db config) ["insert into foo_bar(id) values (?)" -1])
TRACE t12978: (next.jdbc/execute-one! {:dbtype "h2", :dbname "./site.db"} ["insert into foo_bar(id) values (?)" -1])
TRACE t12979: | (next.jdbc.protocols/-execute-one {:dbtype "h2", :dbname "./site.db"} ["insert into foo_bar(id) values (?)" -1] #:next.jdbc{:sql-params ["insert into foo_bar(id) values (?)" -1]})
TRACE t12980: | | (next.jdbc.protocols/get-datasource {:dbtype "h2", :dbname "./site.db"})
TRACE t12980: | | => #object[next.jdbc.connection$url_PLUS_etc$reify__6940 0x39b3aee9 "jdbc:h2:./site.db"]
TRACE t12981: | | (next.jdbc.protocols/-execute-one #object[next.jdbc.connection$url_PLUS_etc$reify__6940 0x39b3aee9 "jdbc:h2:./site.db"] ["insert into foo_bar(id) values (?)" -1] #:next.jdbc{:sql-params ["insert into foo_bar(id) values (?)" -1]})
TRACE t12982: | | | (next.jdbc.protocols/get-connection #object[next.jdbc.connection$url_PLUS_etc$reify__6940 0x39b3aee9 "jdbc:h2:./site.db"] #:next.jdbc{:sql-params ["insert into foo_bar(id) values (?)" -1]})
TRACE t12982: | | | => #object[org.h2.jdbc.JdbcConnection 0x33b2250e "conn21: url=jdbc:h2:./site.db user="]
TRACE t12981: | | => #:next.jdbc{:update-count 1}
TRACE t12979: | => #:next.jdbc{:update-count 1}
TRACE t12978: => #:next.jdbc{:update-count 1}
#:next.jdbc{:update-count 1}
I tried with h2 and (latest)


so it seems that adding {:return-keys true} returns nil . This is not obvious from docs . Is this an issue with the code , the docs or myself ?


@U011NGC5FFY This is one of those annoying differences between databases/drivers. With :return-keys true, the underlying PreparedStatement is set up to tell the driver to return any new generated keys -- but it's up to the database/driver what it actually returns, and different DBs return different things, including nil. Without :return-keys true you get back either a ResultSet or an update count -- which is why you get {:next.jdbc/update-count 1} from execute-one!.


is it something that is worth adding to a Known Issues you will hit section ? btw, thanks


See All the Options where :return-keys behavior is explained in detail...


Any function that creates a PreparedStatement will additionally accept the following options: :return-keys -- a truthy value asks that the JDBC driver to return any generated keys created by the operation; it can be true or it can be a vector of keywords identifying column names that should be returned. Not all databases or drivers support all of these options, or all values for any given option. If :return-keys is a vector of column names and that is not supported, next.jdbc will attempt a generic "return generated keys" option instead. If that is not supported, next.jdbc will fall back to a regular SQL operation. If other options are not supported, you may get a SQLException.


I am getting nil


With the version of H2 that next.jdbc is tested again (1.4.200), I think you should get uppercase keys per this test (in sql_test.cljc):

(deftest test-insert-delete
  (let [new-key (cond (derby?)    :1
                      (jtds?)     :ID
                      (maria?)    :insert_id
                      (mssql?)    :GENERATED_KEYS
                      (mysql?)    :GENERATED_KEY
                      (postgres?) :fruit/id
                      (sqlite?)   (keyword "last_insert_rowid()")
                      :else       :FRUIT/ID)]
    (testing "single insert/delete"
      (is (== 5 (new-key (sql/insert! (ds) :fruit
                                      {:name (as-varchar "Kiwi")
                                       :appearance "green & fuzzy"
                                       :cost 100 :grade (as-real 99.9)}))))
      (is (= 5 (count (sql/query (ds) ["select * from fruit"]))))
      (is (= {:next.jdbc/update-count 1}
             (sql/delete! (ds) :fruit {:id 5})))
      (is (= 4 (count (sql/query (ds) ["select * from fruit"])))))


I updated to the latest h2 -"2.1.214"


But it will depend on how your table is defined and whether your primary key is defined in a way that it will auto-generate a unique value for the driver to return.


once I am done with what I am working with I might downgrade to test that - but I recall it was providing the same results


able is defined like

(str "CREATE TABLE " table-name
             " (id BIGINT UNIQUE NOT NULL, applied " timestamp-column-type
             ", description VARCHAR(1024) )")


In the tests, the primary key is defined as AUTO_INCREMENT PRIMARY KEY for H2.


no auto generated pk


since pk is the migration id - defined in file


Right, so you won't get "generated keys" back because your primary key doesn't generate anything.


thanks for taking the time to explain - kind of makes sense that it should work like this


but nil was unexpected - at least in the test


If there are no generated keys to return... ¯\(ツ)


JDBC is often not very intuitive


down to last test and migratus will join next.jdbc familly


Has anyone used Postgres COPY FROM to populate their DB from EDN? Looking for some tips/example snippets to get started.


did not try but curios to see the outcome. I think you can use the FROM PROGRAM form and use babashka with data.csv to transform edn into csv. I think it might be faster to convert directly to CSV from your service / data producer.


please share your results

👍 1
sheluchin16:07:42 could be helpful for anyone trying to do this. Haven't tried it myself yet.

👍 1