JSON columns with Date and/or Bool fail decoding

[janigro]

Problem Description

When trying to decode a query that has a JSON column with booleans and/or date values, it fails with an error. Consider the following minimal example:

struct MyContent: Content {
    let options: Options
    
    struct Options: Codable {
        let isEnabled: Bool
        let due: Date
    }
}

let query: SQLQueryString = "SELECT JSON_OBJECT('isEnabled', 1, 'due', NOW()) options"

let results = try await db.raw(query).first(decoding: MyContent.self)

Running this code will produce:

Value of type 'Bool' required for key 'isEnabled'.

if we change the Bool to Int, we now get an error because of the Date value:

"Value of type 'Double' required for key 'due'.

The second error can be omitted by changing the type in the due property, from Date to String

Expected Behavior

Not sure if this is a bug report, or a feature request. But ideally, Fluent should be able to decode Bool and Date types. Or maybe I'm missing something obvious of why this is not the case?

Workaround

In the meantime, I'm implementing Econdable.init(from decoder: Decoder) to workaround the problem:

struct MyContent: Content {
    let options: Options
    
    struct Options: Codable {
        let isEnabled: Bool
        let due: Date
        
        init(from decoder: Decoder) throws {
            let container: KeyedDecodingContainer<CodingKeys> = try decoder.container(keyedBy: CodingKeys.self)
            
            self.isEnabled = (try container.decode(Int.self, forKey: CodingKeys.isEnabled) != 0)
            
            let d = try container.decode(String.self, forKey: CodingKeys.due)
            
            let formatter = DateFormatter()
            formatter.locale = Locale(identifier: "en_US_POSIX")
            formatter.dateFormat = "yyyy-MM-dd HH:mm:ss.SSSSSS"
            formatter.timeZone = TimeZone(secondsFromGMT: 0)
            
            guard let due = formatter.date(from: (try container.decode(String.self, forKey: CodingKeys.due))) else {
                throw DecodingError.typeMismatch(Date.self, DecodingError.Context(codingPath: [CodingKeys.due], debugDescription: "String could not be converted to Date"))
            }
            
            self.due = due
        }
    }
}

This workaround now produces proper JSON content:

{
  "options": {
    "due": "2023-01-13T11:49:59Z",
    "isEnabled": true
  }
}

Environment

mysql server: 8.0.27
framework: 4.67.4
toolbox: 18.6.0

[0xTim]

cc @gwynne

[gwynne]

• The issue with isEnabled is due to SQLKit, MySQL, and JSONDecoder all refusing to perform an implicit conversion between integer and boolean value - 1 and true are not the same thing in either MySQL or JSON.
• The issue with due is due to indirecting through a JSON encoding without configuring a date decoding strategy on the JSONDecoder MySQLKit uses; you're therefore getting the default use of Date's builtin Codable conformance, which expects to encode and decode the date's timeIntervalSinceReferenceDate value.

For the first issue, replace 1 with true in your JSON_OBJECT() call and it will work.

For the Date issue, you have two options - the quick and dirty one that'll probably leave you with other issues later on, or the "correct" one. The quick and dirty fix is to just write the query thus:

let query = """
    SELECT JSON_OBJECT(
        'isEnabled', true,
        'due', UNIX_TIMESTAMP() - 978307200.0
    ) options
    """ as SQLQueryString

978307200.0 is Date.timeIntervalBetween1970AndReferenceDate, the conversion factor between the UNIX epoch (1970-01-01T00:00:00.000Z) and the macOS reference date (2001-01-01T00:00:00.000Z). Date expects the latter in its default conformance, but most things that aren't macOS only understand the former 😞.

(Note: I moved the SQLQueryString type annotation to the end and used multiline string syntax, but these are purely cosmetic changes that make it more readable in GitHub's relatively narrow rendering (and happen to also match my preferred style); neither has any functional effect on the code or is any more or less idiomatic than the original formatting.)

The arguably "correct" way is to configure the underlying JSON coders in use by MySQLKIt appropriately, which requires doing this when you configure the database connection, assuming you're using Fluent to actually connect to the database:

let iso8601JsonDecoder = JSONDecoder(), iso8601JsonEncoder = JSONEncoder()
iso8601JsonDecoder.dateDecodingStrategy = .iso8601
iso8601JsonEncoder.dateEncodingStrategy = .iso8601
app.databases.use(.mysql(
    /* url: or hostname:port:username: etc. here */,
    encoder: .init(json: iso8601JsonEncoder),
    decoder: .init(json: iso8601JsonDecoder)
))

If you're connecting directly via SQLKit/MySQLKIt, you can pass the same encoder: and decoder: arguments to MySQLDatabase.sql(encoder:decoder:).

(P.S.: As a matter of completionism, I would be remiss not to mention that your query can be more formally written this way:

try await db.select()
    .column(SQLFunction("json_object", args:
        SQLLiteral.string("isEnabled"), SQLLiteral.boolean(true),
        SQLLiteral.string("due"), SQLFunction("now")
    ), as: "options")
    .first(decoding: MyContent.self)

I'm the first to agree this is rather clumsy and awkward compared to the raw query syntax. I will be doing something about that when I get a chance 😅)