Clojure: jdbc/insert with postgres returns 1 instead of newly inserted row

Question

Function which calls insert

 (defn insert-order
   [component order-num email]
     (jdbc/insert! (:ds  component)
         :orders ["\"orderNumner\""  "email"] [order-num email] ))

My database connection conf

(defn ^:private pooled-data-source
  [host dbname user password port]
  {:datasource
   (doto (ComboPooledDataSource.)
     (.setDriverClass "org.postgresql.Driver" )
     (.setJdbcUrl (str "jdbc:postgresql://" host ":" port "/" dbname))
     (.setUser user)
     (.setPassword password))})

When i call (insert-order) => (1) , however I need newly inserted row.

According to http://clojure-doc.org/articles/ecosystem/java_jdbc/using_sql.html it returns newly created row.

My project deps:

:dependencies [[org.clojure/clojure "1.9.0"]
                 [com.stuartsierra/component "0.3.2"]
                 [com.walmartlabs/lacinia "0.30.0"]
                 [com.walmartlabs/lacinia-pedestal "0.10.0"]
                 [org.clojure/java.jdbc "0.7.8"]
                 [org.postgresql/postgresql "42.2.5.jre7"]
                 [com.mchange/c3p0 "0.9.5.2"]
                 [io.aviso/logging "0.3.1"]]

Am I missing something? Or my understanding is wrong

Answer 1

The correct sytnax, for what you are after is only mentioned in the text above the first insert! example, but then not used.

... if your database / driver supports it, you can pass :return-keys as an option to get back the generated keys. As of java.jdbc 0.7.6, this can be a vector of column names to return (for drivers that support that) or a simple Boolean.

So the syntax to get what you want is this:

; create a sample table with timestamp and sequence id
(jdbc/db-do-commands db-spec ["CREATE TABLE test (
                                 id SERIAL, 
                                 ts TIMESTAMP DEFAULT 'now()', 
                                 name TEXT)"])
; Use `:return-keys` to get the actual generated ids 
; and pass it the columns you are after
(jdbc/insert! db-spec :test {:name "Test 1"} {:return-keys ["id" "ts" "name"]})
; => ({:id 3, :ts #inst "2018-12-17T13:19:57.544067000-00:00", :name "Test 1"})

Mix and match with the other arities of insert!