PreparedStatement use with optional parameters

[duncangroenewald]

Some explanation of how to set bindings for optional values in a prepared statement would be helpful. It seems you can't set binding is the property is optional. If so how do you set the property (column) to NULL in a prepared statement if that is even possible.

I could find no example of such a scenario.

Thanks

[rgcottrell]

I was able to create bindings to optional properties in a PostgresPreparedStatement by creating an extension to PostgresBindings. I was then able to verify that NULL values were inserted into the database.

extension PostgresBindings {
    public mutating func append<Value: PostgresDynamicTypeEncodable>(_ value: Optional<Value>) {
        switch value {
        case .some(let value):
            append(value)
        case .none:
            appendNull()
        }
    }
}

With the extension in place, you can then construct a prepared statement the way you would expect. Here is an example of how that might look:

struct InsertStatement: PostgresPreparedStatement {
    static let sql = "INSERT INTO mytable (id, value) VALUES ($1, $2)"

    var id: UUID
    var value: String?

    func makeBindings() -> PostgresBindings {
        var bindings = PostgresBindings(capacity: 2)
        bindings.append(id)
        bindings.append(value)
        return bindings
    }

    func decodeRow(_ row: PostgresRow) {}
}

A downside of creating the extension in my own code is that it is unavailable to the PostgresNIOMacros package. As a result I have to create the prepared statement by hand, which involves a lot more boilerplate and chance for errors.

[fabianfett]

cc @lovetodream.

@rgcottrell would you be interested in providing a patch to fix this in PostgresNIO directly?

[rgcottrell]

@fabianfett Sure, I can take a look at putting together a patch.