support postgresql json types

[mccraigmccraig]

i added support for json types to java.jdbc, along the lines of :+1:

http://hiim.tv/clojure/2014/05/15/clojure-postgres-json/

this works fine with korma when SELECTing, but when UPDATEing it throws inside :

https://github.com/korma/Korma/blob/master/src/korma/sql/engine.clj#L314

i couldn't obviously see a way around this, since map values seem to be treated specially by korma, but it certainly would be nice to be able to write json values via korma

[immoh]

At least one of the reasons to treat maps differently is raw which creates a map:

(raw "now()")
;;=> {:korma.sql.utils/generated "now()"}

(sql-only (update :foo (set-fields {:bar (raw "now()")})))
;;=> "UPDATE \"foo\" SET \"bar\" = now()"

It would be possible to identify these maps for example by adding some metadata to them, allowing user maps to be passed to clojure.java.jdbc without modifications.

[vincentjames501]

+1. Is there anyway to do this currently?

[immoh]

Take a look at raw and exec-raw.

[erez-rabih]

Any progress on this? I'd be happy to submit a PR but I need some pointers on where the changes are required.

[immoh]

Are there any changes required?

Korma doesn't prevent you from inserting json:

(insert :foo (values {:bar (doto (org.postgresql.util.PGobject.) 
                             (.setType "json") 
                             (.setValue "{\"value\":1}"))}))

[erez-rabih]

1. I didn't know that was possible to do, I followed http://hiim.tv/clojure/2014/05/15/clojure-postgres-json/ which is currently the best documented way I've seen to use JSON values with postgresql. Maybe it is worth adding this snippet to Korma docs?
2. The example you gave is for inserting JSON values. What would it take to read JSON values?
3. Korma's DSL is very expressive I think it deserves a more integrated way to work with JSON values.

[immoh]

I suppose if you select from JSONB column, you will get a PGObject that you can read JSON from.

This is a PostgreSQL-specific feature. Having more integrated way would require having PostgeSQL driver as a dependency. This is something I don't want to do.

[sammikko]

Check out this library for automatic JSON conversion for Postgres: https://github.com/mpg-project/mpg

[erez-rabih]

@sammikko Will it know to insert maps has JSON values to Postgres? Because as I understand it, this is not supported on the Korma level

[sammikko]

@erez-rabih AFAIK, Korma uses clojure.java.jdbc and mpg library extends clojure.java.jdbc protocols to add support PostgreSQL- specific classes like PGObject. So it should work. I haven't used it extensively but I tried some selects and it seems to work, the JSON columns are returned as Clojure maps.

[erez-rabih]

@sammikko Select should work that's right, but I can't see how inserts should work since Korma does not pass maps as they are to the underlying JDBC driver.

[sammikko]

@erez-rabih I see, that's a problem. What are maps passed as to the JDBC driver when using Korma? Maybe it's possible to extend the clojure.java.jdbc protocols to support that too.

[ku1ik]

When you manually create PGobject like @immoh showed it works:

(insert :foo (values {:bar (doto (org.postgresql.util.PGobject.) 
                             (.setType "json") 
                             (.setValue "{\"value\":1}"))}))

However, extending ISQLValue doesn't work:

 (extend-protocol clojure.java.jdbc/ISQLValue
   clojure.lang.PersistentArrayMap
   (sql-value [val]
     (doto (org.postgresql.util.PGobject.)
       (.setType "json")
       (.setValue (json/encode val)))))

(because it tries to serialize korma-prepared maps into PGobject)

I guess one could create defrecord JSONB, pass these to korma, and extend ISQLValue for this new type.