JSONB supported as of GRDB 7

[Jason-Abbott]

Important caveat: this requires the GRDB custom build process since it depends on SQLite 3.45.0 or higher.

The benefits of BLOB rather than TEXT-backed JSON, according to the SQLite documentation, are "that JSONB is both slightly smaller (by between 5% and 10% in most cases) and can be processed in less than half the number of CPU cycles.”

I was already using a custom build for other reasons so when I saw @groue had updated SQLiteLib as part of GRDB 7, it was easy to start using JSONB.

First verifying with manual SQLite changes

I first vetted the change with manual updates to a test SQLite database. The steps I took were

• Add a <column>_new variant of type BLOB corresponding to the old <column> with TEXT JSON
• Run UPDATE <table> SET <column>_new = jsonb(<column>)
• Drop the old column and rename the new column to the old name in one update

From there, I confirmed the many views and triggers I’ve defined (some of which iterate through the JSON) work without alteration. Indeed, all the previous SQLite JSON functions are documented to work seamlessly whether the JSON is BLOB or TEXT.

The only JSONB-specific command I used was jsonb() for the conversion. I see no need for it in regular data access.

Code changes

Those manual SQLite changes were only for testing. The live database is actually built by GRDB, of course. I mention them just to paint a fuller picture of the difference.

In code, all I needed to do was change column types from .text to .blob. The types I persist as JSON already conform to DatabaseValueConvertible so that was it!

Minor notes

I had SQL check constraints on the JSON columns that I had to update from json_valid(<column>) = 1 to json_valid(<column>, 4) = 1. The second parameter indicates JSONB.

As I write this up, I’m a little worried I’ve overlooked some private extension in my code that made this easy. I don’t think so but if anything comes up, I’ll elaborate here!

[groue]

Thank you @Jason-Abbott for raising the topic of JSONB. I'll probably ask a few questions in the next few days :-)

[Jason-Abbott]

As an oldie who has been writing SQL for three decades 😄, let me plug SQLiteFlow (no affiliation) as a fast and focused tool with JSONB support making it easy to still see the content of these BLOBs.

In fact, this is a nicer view of the JSON than before. Here I updated the text column to JSONB as described above.

[Jason-Abbott]

I have worked a bit today on streamlining the JSONB encoding to avoid the need for string conversions and custom DatabaseValueConvertible conformance.

One hang-up happens here in DatabaseValueEncoder. That value.encode() always throws down to the String version for my basic Codable (but not explicitly DatabaseValueConvertible) types.

I did have some private methods that were glossing over these gaps. I think GRDB will need some signal to determine the DatabaseValue.Storage type for basic Codables.

Knowing the target column type would be a “magical” signal but may not always be feasible or performant. I’ll play around with some options here ...

func encode<T: Encodable>(_ value: T) throws {
  do {
  try value.encode(to: self)
      if requiresJSON {
          // Here we handle empty arrays and dictionaries.
          throw JSONRequiredError()
      }
    } catch is JSONRequiredError {
        let jsonData = try jsonEncoder.encode(value)
        
        // Store JSON String in the database for easier debugging and
        // database inspection. Thanks to SQLite weak typing, we won’t
        // have any trouble decoding this string into data when we
        // eventually perform JSON decoding.
        // TODO: possible optimization: avoid this conversion to string,
        // and store raw data bytes as an SQLite string
        let jsonString = String(decoding: jsonData, as: UTF8.self)
        try jsonString.encode(to: self)
    }
}

[Jason-Abbott]

Edit: This isn’t actually valid. See the following comment.

I made a quick solution that I’ll probably run with for a bit while I focus on other things. I updated the Codable types I want to store as JSONB to instead conform to JSONBConvertible, below. It handles everything, I think. This was quick and dirty so no criticism 🙂

public import GRDB
import OSLog

private let log = Logger.current

public protocol JSONBConvertible: Codable, DatabaseValueConvertible, Sendable {}

public extension JSONBConvertible {
    var databaseValue: DatabaseValue {
        let encoder = DatabaseValue.databaseJSONEncoder()
        do {
            let data = try encoder.encode(self)
            return data.databaseValue
        } catch {
            log.error("Failed to JSONEncode \(Self.Type.self)")
            return DatabaseValue.null
        }
    }

    static func fromDatabaseValue(_ dbValue: DatabaseValue) -> Self? {
        if case let .blob(data) = dbValue.storage {
            let decoder = DatabaseValue.databaseJSONDecoder()
            do {
                return try decoder.decode(self.self, from: data)
            } catch {
                log.error("Failed to JSONDecode \(Self.Type.self)")
                return nil
            }
        } else {
            return nil
        }
    }
}

[Jason-Abbott]

More learnings. It turns out most of the above was working thanks to a bug in SQLite that allows most things JSONish to function as JSON in a BLOB column even if it isn’t JSONB.

The test database conversion I did with jsonb() created valid JSONB but SQLite doesn’t do anything to automatically convert JSON bytes to JSONB upon insert.

The options to make that happen are (in a table where value is a BLOB intended to store JSONB) either

INSERT INTO jsonb_test (value) VALUES (jsonb('{"key1": 1, "key2": "two"}'))
INSERT INTO jsonb_test (value) VALUES (jsonb(x'7b226b657931223a20312c20226b657932223a202274776f227d'))

This is a little tricky to accomplish through GRDB helper functions since an attempt to insert the column value

jsonb('{"key1": 1, "key2": "two"}')

is emitted as (note extra quotes)

'jsonb(''{"key1": 1, "key2": “two"}'')'

which isn’t valid.

Obviously manual SQL will do the trick but I’ve been poking around for something more idiomatic.

This isn’t my top concern so commentary on the topic is likely to remain sporadic 🙂

[Jason-Abbott]

Last update for the day: Rather than try to automate something on the binding end of things, it works fine if the statement takes the form

INSERT INTO table (column) VALUES (jsonb(?))

But once made proper JSONB, it no longer decodes “for free.” Instead, the SELECT must assume the form

SELECT json(column) FROM table

which converts SQLite proprietary JSONB to JSON so JSONDecoder can process it.

At this point, the effort seems to exceed the benefit. In my own case, CloudKit is also hitting these tables so I would need to be careful to jsonb()/json() roundtrip every one of those changes. 😵‍💫

I am going to sleep on it but my inclination is to revert the changes I made, stick with TEXT JSON, and await a time, if ever, when SQLite offers some interfaces that automate the conversions.

I was enthusiastic for a little speed bump and size savings with JSONB but the steps to make it happen feel too brittle for my comfort.

[Jason-Abbott]

Last monologue update 🙂

The other way to make JSONB seamless, besides SQLite doing some kind of auto-conversion, is to implement JSONBEncoder and JSONBDecoder conforming to Swift Encoder and Decoder.

So I did that.

Looking at an existing Rust version of the same, and a Swift binary encoder for a different format, gave me a good start.

I am glad to share code if there’s interest (no signs of that yet 🙂) but since its currently tuned and tested for only my exact Codable types, I’m not going to put up a gist or anything yet.

Having looked at the Swift source for JSONEncoder and Decoder as part of this project, I think a direct binary encoder like this offers a lot of simplification.