Blob columns

[seanmrich]

What's the recommended approach for columns using a BLOB type? Taking inspiration from the UUID implementation, I tried this:

extension MyValue {
  public struct BytesRepresentation: QueryRepresentable {
    public var queryOutput: MyValue

    public init(queryOutput: MyValue) {
      self.queryOutput = queryOutput
    }
  }
}

extension MyValue.BytesRepresentation: QueryBindable {
  public var queryBinding: QueryBinding {
    let data = try! JSONEncoder().encode(queryOutput)
    return .blob([UInt8](data))
  }
}

extension MyValue.BytesRepresentation: QueryDecodable {
  public init(decoder: inout some QueryDecoder) throws {
    let bytes = try [UInt8](decoder: &decoder)
    let data = Data(bytes)
    let spec = try JSONDecoder().decode(MyValue.self, from: data)
    self.init(queryOutput: spec)
  }
}

extension MyValue.BytesRepresentation: SQLiteType {
  public static var typeAffinity: SQLiteTypeAffinity {
    [UInt8].typeAffinity
  }
}

Then I can use it with the @Column macro:

@Table
structure TableValue {
  @Column(as: MyValue.BytesRepresentation)
  var value: MyValue
}

I don't love using the force unwrap, but you could use withErrorReporting and return an empty [UInt8] if there's an error. Is there a better way?

[stephencelis]

@seanmrich Can you explain what you're trying to achieve here? The library comes with a Codable.JSONRepresentation type for doing this kind of thing. Are you trying to do JSONB, instead?

I don't love using the force unwrap, but you could use withErrorReporting and return an empty [UInt8] if there's an error. Is there a better way?

Do you mean the force try!? The QueryBinding type has an invalid case for error handling:

extension MyValue.BytesRepresentation: QueryBindable {
  public var queryBinding: QueryBinding {
    do {
      return try .blob([UInt8](JSONEncoder().encode(queryOutput)))
    } catch {
      return .invalid(error)
    }
  }
}

[seanmrich]

I have some existing tables built with GRDB, and one has a column with a blob type. It's encoded from a custom Codable swift type. Perhaps this could be altered to use JSON at some point, but I'm just trying to translate from GRDB's TableRecord for the moment.
I missed the invalid case so thanks for that.

[aehlke]

@stephencelis does sharing-grdb work ok with JSONB? I would love to use https://github.com/Jason-Abbott/swift-sqlite-jsonb for fast JSONB encoding/decoding. Is that something easy to extend sharing-grdb with? (Sorry to derail)

[mbrandonw]

@aehlke You should be able to create a custom QueryBindable and QueryRepresentable conformance that performs the JSONB encoding/decoding under the hood, using Jason Abbott's code. You can even use the JSONRepresentation that comes with the library as inspiration for how its done.

That should allow you to create a JSONBRepresentation that can be used like this:

@Table
struct Item {
  @Column(as: [String].JSONBRepresentation.self)
  var notes: [String] = []
}

[aehlke]

Thanks so much for the assistance. I will contribute it back somehow if I get it working.