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? -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
