jsonb support

[kdiogenes]

From https://sqlite.org/jsonb.html, SQLite supports JSONB since version 3.45.0, but if I'm not missing anything, this gem still doesn't.

From this post https://sqlite.org/forum/forumpost/fa6f64e3dc1a5d97, I'm a bit confused about it. When creating a JSONB field on Postgres it's a column type, but for SQLite it appears to be a supported format for a JSON column. I'm assuming this because of this part of the "Migration" section:

Note that all functions accept both text JSON and JSONB. So to start using JSONB, you do not have to modify your database files to convert legacy text JSON into JSONB. Just start writing out JSONB for new entries. The old entries will continue to work. The new entries will just work faster.

There is also some interest in SQLite JSONB on the Rails community: https://discuss.rubyonrails.org/t/add-jsonb-on-sqlite-connection-adapter-table-definition/84689.

So, I would like to know if someone is already working on supporting this or has an idea of how this can be better integrated within this gem.

Best regards!

[flavorjones]

@kdiogenes Thanks for asking this question.

I haven't used the new JSONB features yet, but it seems like instead of using a text column to store JSON, you can use a blob column to store a binary format, is that right?

That seems like something that should work fine today with this gem. Is there a particular use case you can describe that would make this conversation more concrete and help me understand what's not working as expected?

[flavorjones]

Ah, looks like JSONB is a BLOB type. Here's what I'm doing and it seems to be working OK:

#! /usr/bin/env ruby

require "bundler/inline"

gemfile do
  source "https://rubygems.org"
  gem "sqlite3", path: "."
end

FileUtils.rm_f "577-json.db"

db = SQLite3::Database.new "577-json.db"
db.execute "CREATE TABLE t (id integer primary key autoincrement, settings JSONB)"

db.execute %Q(INSERT INTO t (settings) VALUES (jsonb('[{ "a": 1, "b": 2 }]')))

db.execute "SELECT id, json(settings) FROM t"
# => [[1, "[{\"a\":1,\"b\":2}]"]]

[flavorjones]

I acknowledge that the Rails sqlite3 adapter probably does not handle binary storage as well as the PostgreSQL adapter does. That would be a good thing to try to contribute upstream if you've got the time and energy! Let me know if you want help.

[kdiogenes]

So, if I understand correctly, the issue doesn't have to be addressed by this gem, right? I will take a look at the sqlite3 adapter and if it doesn't deplete my time and energy I will try to contribute a PR. This is the code that should handle these things: https://github.com/rails/rails/blob/dd8f7185faeca6ee968a6e9367f6d8601a83b8db/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb, right?. Thanks for the attention!

[kdiogenes]

As I said above, my understanding based on the sqlite docs is that the column should be a BLOB to (opaquely) store the proprietary binary format named JSONB. Have you tried that?

Just would like to register that I tried to use a BLOB field and things get worse on the Rails side:

wave-caption(dev)> Foobar.all
  Foobar Load (3.6ms)  SELECT "foobars".* FROM "foobars" /* loading for pp */ LIMIT 11 /*application='WaveCaption'*/
=> [#<Foobar:0x00007f8d87f91f48 id: 1, settings: "\x8C7foo7bar">, #<Foobar:0x00007f8d883b7e60 id: 2, settings: "{\"foo\":\"bar\"}">]

It reads the binary data, but it's total crap.

[flavorjones]

If it's helpful, the PR that added JSONB support to Rails's PostgreSQL adapter is here: rails/rails#16220

You may also want to look at activerecord/lib/active_record/store.rb which is what enables custom coders/decoders to serialize a column's value to a Ruby type.

The tricky part is that you'll need to update Arel to somehow wrap any column read with json() and any write with jsonb(). I'm not sure how to do that -- I'd have to dig into Active Record to see what features are available. You may want to start by trying to generate the right SQL statements when a column is declared type jsonb.