Getting JSON to work with the DB module

[David Storrs]

tl;dr  I'm having trouble getting JSON support working in the db module when using SQLite and would really appreciate some direction, or confirmation that it's impossible.  I suspect that it's impossible, since the docs list the accepted Racket types as exact-integer?, real?, string?, and bytes?.  I was hoping that having the JSON extension in would add conversion ability to this, but it looks like not.

Longer:

SQLite does not natively support JSON, but there's an extension that can be dynamically- or statically linked.  https://sqlite.org/json1.html

When working with a Postgres database, the DB module will handle transforming things (e.g. hashes) to and from JSON on insert/select, which is insanely useful and convenient.  I'd like to get the same behavior in SQLite, especially since that would let me use the json_agg function which would be a reasonable replacement for Pg's ARAAY_AGG feature, of which I make heavy use.

Here's what I've done so far:

0. I've read the docs on the db module carefully, which has me concerned about whether this is possible at all.  Still, optimism!

1. I've compiled the JSON1 extension into the libsqlite.* files

2. I've verified that JSON is working via the sqlite CLI client (i.e., not the Racket db module)

3. I've put the libsqlite.* files in my /Applications/Racket_v7.1/lib directory (one of the entries in (get-lib-search-dirs)).

At this point I tried this:

> (require json db)

> (define db (sqlite3-connect #:database "foo.db"))

> (query db "create temporary table blogsnort (id integer primary key, data json))

(simple-result '((insert-id . #f) (affected-rows . 0)))

> (query db "insert into blogsnort (data) values ($1)" (hash 'a 1))
; query: cannot convert given value to SQL type
;   given: '#hash((a . 1))
;   type: parameter
;   dialect: SQLite
; [,bt for context]

I tried setting the storage class on the 'data' column to TEXT (I wasn't sure if the JSON extension added a 'JSON' type but figured it was worth trying), but that made no difference.

Am I right that it's simply impossible and that I'll need to manually convert to/from strings?

[Ryan Culpepper]

It is not possible, unfortunately. You must do the conversion to and
from strings yourself.

I've thought about adding a hook for additional conversions based on
declared types, but there's no declared type information at all for
parameters, and the declared type for results is fragile: a column name
has a declared type but no other kind of expression does.

Ryan

> --
> You received this message because you are subscribed to the Google
> Groups "Racket Users" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to racket-users...@googlegroups.com
> <mailto:racket-users...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

[David Storrs]

Okay, thanks.  I wonder if this is a problem space that the sql module could fit into?  Perhaps tag the fields with wrapper functions.

Regardless, I've found a clean way around it by looping through a smart struct defined using the struct-plus-plus module.

#lang at-exp racket
(require struct-plus-plus db json)

(define db (sqlite3-connect #:database "test.db"))
(query-exec
 db
 @~a{CREATE TEMPORARY TABLE user (id INTEGER PRIMARY KEY,
                                  name TEXT NOT NULL,
                                  data JSON NOT NULL DEFAULT '{}')})
(query-exec db "insert into user (name, data) values ('tom jones', '[7]')")
(display "initial rows: ") (query-rows db "select * from user")
(query-exec db "delete from user")

; struct named user                                                                                                           
; id field is optional, defaults to #f, must be #f or exact-positive-integer?                                                 
; name is mandatory, must be string?

; data is optional, defaults to (hash), must be both hash and jsexpr?                                                                                                                        

(struct++ user
          ([(id #f) (or/c #f exact-positive-integer?)]
           [name string?]
           [(data (hash)) (and/c hash? jsexpr?)] )
          (#:convert-for  (db (#:overwrite

                                        (hash 'data
                                                 (lambda (d)
                                                   (with-output-to-string
                                                     (thunk (write-json d)))))))
           #:convert-from (db (vector? (vector id name
                                               (app (curryr with-input-from-string
                                                            read-json)
                                                    data))
                                       (id name data))))

          #:transparent)
(define initial (user++ #:name "tom jones"))
(display "initial user struct: ") (println initial)
(define final (user/convert->db (set-user-data initial (hash 'phone 8675309))))
(display "user data for db: ")(println final)
(query-exec db
       "INSERT INTO user (name, data) VALUES ($1, $2)"
       (hash-ref final 'name) (hash-ref final 'data))

(query-rows db "select * from user")
(db->user++ (query-row db "select id, name, data FROM user"))

;; ------------ EOF

Output:

$ racket test.rkt

initial rows: '(#(1 "tom jones" "[7]"))
initial user struct: (user #f "tom jones" '#hash())
user data for db: '#hash((data . "{\"phone\":8675309}") (id . #f) (name . "tom jones"))
'(#(1 "tom jones" "{\"phone\":8675309}"))
(user 1 "tom jones" '#hasheq((phone . 8675309)))