Using AnyJSON

[Artem-Viveritsa]

Hi! I have a remote postgres with which I synchronize GRDB for each user. One of the columns is of type jsonb with completely different data. There can be both json objects and just values ​​at the top level without keys.

The AnyJSON code from the Supabase client was perfect for working with such data:
AnyJSON.swift

My question is, what is the most correct way from the point of view of the GRDB architecture to save and read this data locally? String or data? Where is the best place to convert them from AnyJSON to something more suitable? Search or filtering by this field is not supposed.

There are options for writing initializers from Row, encoders and decoders, DatabaseValueConvertible for this column and several more. In general, what is the most competent approach?

[groue]

Hello @Artem-Viveritsa,

To refresh my ideas, I read the JSON Support article in GRDB doc. You're describing what this document calls a "flexible schema" (it is not fixed, but controlled by the remote service). So I'd suggest:

Store the JSON as JSON text in the database. JSON blobs are supposed to be JSONB (not the same format as in PostgreSQL), but not all versions of SQLite have support for JSONB. Sticking to JSON text is good enough:

try db.create(table: "myRecord") { t in
    t.primaryKey("id", .text)
    t.column("info", .jsonText).notNull() // A JSON column
}

Expose this JSON as a Data property in a Swift record, but make sure it is saved as TEXT in the database. This Data will be easier to deal with from standard Swift APIs that deal with JSON - none of them accept a String.

struct MyRecord: Codable {
    var id: String
    var info: Data // JSON UTF8 data
}

extension MyRecord: FetchableRecord, PersistableRecord {
    static func databaseDataEncodingStrategy(for column: String) -> DatabaseDataEncodingStrategy {
        .text
    }
}

Finally, only perform conversions from this Data to AnyJSON or any such type when needed. This will avoid to pay the cost of encoding or decoding raw JSON each time you want to deal with a MyRecord instance:

// There's no need to decode JSON in this scenario,
// so we should not spend CPU cycles decoding JSON:
let myRecord = try MyRecord.find(db, id: "42")
try myRecord.delete(db)

--

That's my two cents.

[Artem-Viveritsa]

Thanks for the answer, @groue! This is similar to what I'm doing now, except I'm trying to save to a string in order to put valid json into Postgres when there are changes in the local database (synchronization).

But because there are conversions to strings and back, double quotes appear like these "\"2025-06-15T20:38:24.191\"", "42", "true" and so on. Locally, this all works fine, it is converted to the required type automatically, but on the Postgres side it remains like this (numbers and booleans as strings, and strings in double quotes).

I would like to understand in what place and how I can get valid json from the String or Data column. This is only needed for sending to the server.

And since the data needs to be sent to the server anyway, maybe it is better to save it right away in a usable form without saving processor time? 🤔

The simplest solution would be to remove double quotes manually (and this even works), but this is a hack. I would like to find the root cause and set up the correct conversion.

Ideally, a json column can only contain (any Codable)? (int, double, string, date, struct, etc.).

[groue]

This is similar to what I'm doing now, except I'm trying to save to a string in order to put valid json into Postgres

I think we are in agreement here: we save JSON strings in the database. That's what the sample code above does.

But because there are conversions to strings and back, double quotes appear [...]. Locally, this all works fine, it is converted to the required type automatically, but on the Postgres side it remains like this (numbers and booleans as strings, and strings in double quotes).

I'm not sure I understand if you're describing the corruption of persisted data in local SQLite, or in remote Postgres, or in memory, or just regular character escaping in the debugger. In case of corruption, I don't understand when it happens and who corrupts the data.

[Artem-Viveritsa]

Your suggestion to use Data gave me another idea. To "normalize" the data and keep it readable (in case there is a need to search by JSON) I did it like this:

extension Encodable {
    func toString(with encoder: JSONEncoder = PostgresEncoder) throws -> String? {
        switch self {
            
        case let string as String: return string
        case let date as Date:     return date.iso8601String
        case let int as Int:       return String(int)
        case let double as Double: return String(double)
        case let bool as Bool:     return bool ? "true" : "false"
        case let uuid as UUID:     return uuid.uuidString
            
        default:                   return String(data: try encoder.encode(self), encoding: .utf8)
            
        }
    }
}

extension String {
    func toCodable<T: Codable>(as type: T.Type, with decoder: JSONDecoder = PostgresDecoder) -> T? {
        switch type {
            
        case is String.Type: return self as? T
        case is Date.Type:   return self.iso8601 as? T
        case is Int.Type:    return Int(self) as? T
        case is Double.Type: return Double(self) as? T
        case is Bool.Type:   return (self.lowercased() == "true") as? T
        case is UUID.Type:   return UUID(uuidString: self) as? T
            
        default:
            if let data = self.data(using: .utf8) {
                return try? decoder.decode(T.self, from: data)
            }
            return nil
            
        }
    }
}

Thanks!

[groue]

Glad you found a satisfying solution :-)