Native JSONB support for SQLite

[TapGhoul]

Heyo! Ever since SQLite 3.45.0, there is native support for JSONB on-disk and can be switched in and out on the fly to textual JSON. If using sqlite-side JSON ops, it performs faster than standard JSON, which is nice for raw queries.

Note, the actual format isn't really any smaller than regular JSON for most data that is to be held. It is smaller for a few primitives (null, true and false each only take 1 byte) and most values - even ints and floats - encode the same as standard JSON. This just moves the payload up to the start of the value on-disk.
In cases where the byte (ascii) size of the value is less than 11 bytes in length, it will be smaller - only a single byte header. But in cases where it's 12 or more, it has no actual size gains.

It can be transparently upgraded in the case of sqlite, as the function json(x) can take both JSON and JSONB internally, and same with the jsonb(x) function to turn it into jsonb itself, so it'd be a matter of wrapping any inserts in jsonb() and any loads in json() - so adding this imposes no breakages to existing on-disk data, with the exception that raw queries sitting outside of the ORM/query builder that are expecting text may no longer function as expected.

There is no way to define this as a custom column type here that has a default affinity of BLOB, but thanks to sqlite's loose typing rules you can reuse the same type - or just do a custom one (jsonb_blob or something) and it'll sort itself out, as per the column/data affinity rules.

Information can be found here:
https://sqlite.org/jsonb.html
https://sqlite.org/json1.html#jminib

[doveq]

how do i use jsonb() when insert data?

like this
ActiveModel {
data: Set( jsonb('{"a": 123}') )
}