Can not use a SQLExpression as the value in SQLUpdateBuilder.set(_:to:) or SQLInsertBuilder.value(_:)

[0xTim]

Currently, SQLUpdateBuilder can not express a query of the form UPDATE Foo SET bar=(a + 1) WHERE id=?, for example, as set(_:to:) takes an Encodable, not an SQLExpression, as the value to set. This applies to SQLInsertBuilder.value(_:) as well. It is currently necessary to issue a raw query to get this functionality.

[tanner0101]

I added two additional set methods to SQLUpdateBuilder:

func set<T, V>(_ keyPath: KeyPath<T, V>, to expression: Connection.Query.Update.Expression)
func set(_ identifier: Connection.Query.Update.Identifier, to expression: Connection.Query.Update.Expression)

I think those make sense and fit right in with the existing API.

However, I'm not sure what the API for SQLInsertBuilder should look like. The insert builder supports inserting multiple value sets for a single list of columns:

INSERT INTO (...) VALUES (...), (...), (...)

I'm not sure how you would specify how many times you'd like to insert a given column / value pair. With Codable, it's obvious how to handle these situations:

insert().into(User.self).value(user)
// ...
insert().into(User.self).values([userA, userB, userC])

I'm interested to know what kind of use cases you were imagining for this as that could help determine the best API.

[tanner0101]

These are supported in SQLKit 3.0:

SQLUpdateBuilder.set(_ column: SQLExpression, to value: SQLExpression)
SQLInsertBuilder.values(_ values: SQLExpression...)