Maybe a bug with insert :return-keys with sqlite

[clearwily]

Forgive me if this is the wrong place to ask this question. I do not have
enough clojure or sql database experience to feel confident that this a bug.

The short version is that "insert" with :return-keys returns a vector that
contains a map with an invalid clojure keyword instead of something like
[{"id" 3}]

My deps
:deps {
com.github.seancorfield/next.jdbc {:mvn/version "1.3.1070"}
org.xerial/sqlite-jdbc {:mvn/version "3.46.1.3"}
}

~/devel/test-jdbc $ clojure
Clojure 1.12.1

user=> (require '[next.jdbc :as jdbc])
nil

user=> (def db-file "next-test.db")
#'user/db-file

user=> (def db {:dbtype "sqlite" :dbname db-file})
#'user/db

user=> (def ds (jdbc/get-datasource db))
#'user/ds

;; is this 'ds' needed for sqlite? or will using db work? db seems to work
;; but following the docs here

user=> (def schema-some-data
(str "create table"
" table_1 ("
"id integer primary key," ; unique key generated by the database
"name text" ; random string
")"))
#'user/schema-some-data

user=> (jdbc/execute! ds [schema-some-data])
[#:next.jdbc{:update-count 0}]

user=> (jdbc/with-transaction [tx ds]
(let [result (jdbc/execute!
tx ["insert into table_1(name) values(?)" "some-name"])]
result))
[#:next.jdbc{:update-count 1}]

;; I need to get the db generated "id" for use as a foreign key in other
;; tables but the "result" above does not have it. Guessing it's a sqlite limitation.

;; so try using :return-keys

user=> (jdbc/with-transaction [tx ds]
(let [result (jdbc/execute!
tx ["insert into table_1(name) values(?)" "another-name"]
{:return-keys ["id"]})]
result))
[{:last_insert_rowid() 2}]

;; The "id" I need is in the result but the key ":last_insert_rowid()" is an
;; invalid clojure keyword.

user=> (jdbc/with-transaction [tx ds]
(let [result (jdbc/execute!
tx ["insert into table_1(name) values(?)" "yet-again"]
{:return-keys ["id"]})]
(first (vals (first result)))))
3

;; So I use the "(first (vals (first" hack to get the value I need but this
;; only works if I want one thing back from the last row inserted.
;; It seems that :return-keys should return something like
;; [{"id" 3}]

[seancorfield]

Not a bug. next.jdbc returns whatever the JDBC driver provides, with map keys converted to keywords programmatically. Not all such keywords can be read, but that's true of a lot of things that can be created programmatically.

You might try ["insert into table_1(name) values(?) returning *" "yet-again"] to see whether you get a better result.

next.jdbc is a very thin wrapper around JDBC -- which means you're at the mercy of each database's own quirks in a lot of areas.

[clearwily]

Thanks for the quick response. "returning *" does what I need. I appreciate your open source work and always read your blogs.

…

On Wed, Nov 26, 2025 at 2:25 PM Sean Corfield ***@***.***> wrote: Not a bug. next.jdbc returns whatever the JDBC driver provides, with map keys converted to keywords programmatically. Not all such keywords can be read, but that's true of a lot of things that can be created programmatically. You might try ["insert into table_1(name) values(?) returning *" "yet-again"] to see whether you get a better result. next.jdbc is a very thin wrapper around JDBC -- which means you're at the mercy of each database's own quirks in a lot of areas. — Reply to this email directly, view it on GitHub <#311 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ALOXUUM3Y5K7EDVQ62IIVED36YLFLAVCNFSM6AAAAACNJWRRPWVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTKMBZGA3TCOA> . You are receiving this because you authored the thread.Message ID: ***@***.***>