search

layerware / hugsql

A Clojure library for embracing SQL
https://hugsql.org
Apache License 2.0
690 stars 54 forks source link

Add :r result type for returning inserted values #8

Closed robinheghan closed 8 years ago

robinheghan commented 9 years ago

YeSQL allows you to suffix your sql fns with <! to return the created row. Maybe hugsql should have a :r result type that does the same?

csummers commented 9 years ago

Yes, I'm looking at how to reliably and consistently do this across different databases and different library backends. From a library perspective, it seems to hinge on whether or not .getGeneratedKeys is supported. clojure.java.jdbc does support this. I think clojure.jdbc support is currently commented out in the code.

I'm looking at supporting the single record created and multiple records created for a forthcoming multi-insert functionality.

Also, if you are using a database that supports the RETURNING syntax for INSERTS (like Postgresql), you can support this directly in your SQL statement.

csummers commented 8 years ago

As of 0.4.0, we have commands :insert, or :i! for short, that have behavior similar to Yesql's <! suffix. Specifically, the underlying adapter and libraries must support the JDBC call to .getGeneratedKeys. The return value of this is quite varied across libraries. See Insert for some samples from the test suite.

Implementation note: Since the support for this varies so much, I decided not to change the HugSQLAdapter protocol by adding an insert function. Instead, I now pass the :command and :result along to the adapter in the options and allow the execute function to conditionally change behavior if it encounters a command type that will change which underlying function it should call. You can see an example of this in the adapter code for clojure.java.jdbc.