db/insert return value

[p4ulcristian]

Hi, (lighthouse.core/insert conn {:person/name "Gen Eric"}) returns (1) if it succeded. In the readme at insert I found this: "p is auto-resolved to (get p :person/id)". So what do I misunderstand? how can I get back the id of the inserted row? Thank you, appreciating your work!

[swlkr]

Hm, yeah definitely shouldn't have put that in the readme, so I guess it could happen two ways:

1. I could take it out of the readme and just have it working with postgres and not sqlite
2. It would work in postgres in the same transaction with returning * and in sqlite, it could work by querying in the insert function and returning the row

Unsure yet about which way to go, I should probably have similar behavior across databases...

[alexjuda]

Hi, I stumbled upon similar issue, I needed to retrieve id of the inserted row. The workaround I managed to achieve this with was to define my own implementation of transact with an additional argument passed to jdbc/execute! (no ns aliases for clarity):

(defn transact
  "Takes a jdbc connection, a query, an optional map of params interpolated to query, and an options map passed to jdbc
      Ex: (transact conn '[:delete
                           :from todo
                           :where [todo/id 1]])
  "
  ([conn query query-params jdbc-opts]
   (let [schema (lighthouse.core/schema conn)
         db (lighthouse.util/db conn)
         sql (lighthouse.sql/sql-vec db schema query query-params)]
     (clojure.java.jdbc/execute! conn sql jdbc-opts))) ;; notice the additional parameter here
  ([conn query]
   (transact conn query {} {}))
  ([conn query query-params]
   (transact conn query query-params {})))

This way I could pass appropriate option to get the id back in generated keys map:

(let [res (transact conn
                    [:insert :person/name :values ["Alex"]]
                    {}
                    {:return-keys true} ;; <= passed to jdbc/execute!
                    )]
  (get res (keyword "last_insert_rowid()")))

Haven't tested this with Postgres yet, I'm using SQLite at the moment. Do you think it would make sense to add this modification in the lib, @swlkr?
Thanks for awesome library!