upsert with explicit inclusion of rows instead of exclusion?

[jubishop]

As far as I can tell reading the documentation for upsert, the onConflict->doUpdate closure requires you to explicitly specify which columns should not get updated by appending .noOverwrite to Columns you want to keep unchanged.

Is it possible to do the opposite and only specify the columns you want to update? I have logic where I know I specifically only want to update some columns, and itll be a bigger maintenance risk to always add a .noOverwrite for any new column I add to that table. my current raw sql solution looks like this, with some parts left out for simplicity:

let updateString =     
  rssUpdatableColumns
    .map { column, _ in
      "\(column.name) = excluded.\(column.name)"
    }
    .joined(separator: ", ")

let container = try record.databaseDictionary
let columnNames = container.keys.sorted()
let values = columnNames.map { container[$0]! }

let columnsSQL = columnNames.joined(separator: ", ")
let placeholders = columnNames.map { _ in "?" }.joined(separator: ", ")

let sql = """
  INSERT INTO \(T.databaseTableName) (\(columnsSQL))
  VALUES (\(placeholders))
  ON CONFLICT DO UPDATE SET \(updateString)
  RETURNING *
"""

this works in only updating the columns defined in rssUpdatableColumns but im wondering if theres more grdb native syntax support for this?

[jubishop]

thanks for your help as always!

[groue]

hello @jubishop,

That API does not exist yet. And your feature request is grounded. After all, the behavior you are after is the behavior of the raw SQL.

I suppose you can achieve this with an intermediate record type that only contains the RSS columns you want to write, but I'm not sure this is very practical.

[jubishop]

what do you think of this as a generic extension for making it work? (not a merge proposal just as something for a user like myself to implement in my own app code to achieve this functionality):

extension PersistableRecord {
  func upsertLimitedColumns<U: FetchableRecord>(_ db: Database, columns: [any ColumnExpression])
    throws -> U
  {
    let container = try databaseDictionary

    let columnsSQL = container.map(\.key).joined(separator: ", ")
    let placeholders = Array(repeating: "?", count: container.count).joined(separator: ", ")
    let updateSQL = columns.map { "\($0.name) = excluded.\($0.name)" }.joined(separator: ", ")

    let sql = """
      INSERT INTO \(Self.databaseTableName) (\(columnsSQL))
      VALUES (\(placeholders))
      ON CONFLICT DO UPDATE SET \(updateSQL)
      RETURNING *
      """

    guard
      let result = try U.fetchOne(
        db,
        sql: sql,
        arguments: StatementArguments(container.map(\.value))
      )
    else { throw DatabaseError(message: "Upsert returned no rows") }

    return result
  }
}