GRDB Upsert: Selective Column Updates Without Exclusion?

Hey there, fellow GRDB.swift enthusiasts! Ever found yourself wrestling with the nuances of upsert operations, particularly when it comes to selective column updates? I know I have! The core of the issue often revolves around the onConflict -> doUpdate closure, where you typically have to specify which columns shouldn't be updated using .noOverwrite. While this works, it can become a maintenance headache, especially as your table schemas evolve. What if you could flip the script and only specify the columns you want to update?

The Upsert Dilemma: Exclusion vs. Inclusion

Let's face it: keeping track of all the columns you don't want to update can get cumbersome. Every time you add a new column to your table, you need to remember to update your upsert logic to include .noOverwrite for that column if it shouldn't be touched during the update. This approach can quickly become error-prone and a source of potential bugs. The risk is pretty high! Think about it: a missing .noOverwrite could inadvertently overwrite data in a column you meant to preserve. On the other hand, the idea of explicitly defining the columns you do want to update feels much cleaner and more maintainable. It aligns better with the principle of least astonishment: you explicitly state your intent.

Current Approach and its Limitations

The most common approach involves leveraging the onConflict clause with a doUpdate closure. Inside this closure, you specify how to handle conflicts. Often, this means updating some columns and leaving others untouched. However, the standard implementation usually requires you to list all the columns you don't want to update by appending .noOverwrite. This works, but as your table schema grows, the risk of missing a column in your .noOverwrite list increases. This is particularly true if you have many columns. Your code might look something like this (simplified for clarity):

 try db.upsert(myRecord) {
     $0.onConflict(do: .update(Set(myRecord.updatableColumns.map { ".columnName" })))
 }

In this snippet, myRecord.updatableColumns would likely contain the columns you don't want to overwrite. While functional, it demands constant vigilance to ensure that every new column is accounted for. The larger your table, the more unwieldy this approach becomes. This also means you are prone to making mistakes, since you need to always maintain a list of updatableColumns. This can become tedious really fast!

The Allure of Selective Updates

The alternative – selective updates – is compelling because it focuses on what you do want to change. Imagine a scenario where you have a record with several columns, but you only want to update a handful of them based on certain conditions. With selective updates, you'd explicitly specify those columns, making your code more readable and less prone to errors. If a new column is added to the table, it wouldn't affect your update logic unless you specifically intended to include it. This greatly improves maintainability and reduces the cognitive load required to understand and modify your code.

Exploring Alternatives and Solutions

So, is there a more GRDB-native way to achieve this? Unfortunately, GRDB.swift's built-in upsert doesn't directly offer a syntax for explicitly including columns for update. However, we can explore several strategies to achieve the desired behavior. These are designed to provide workarounds and alternative approaches, leveraging GRDB's flexibility while maintaining a clean and maintainable codebase.

Leveraging Raw SQL within GRDB

As the original poster pointed out, using raw SQL is a viable option. GRDB provides excellent support for executing raw SQL queries, which gives you complete control over the update process. Here's a refined example based on the provided code snippet, incorporating best practices and ensuring clarity:

 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 \(record.upsertRSSUpdateSQL)
 RETURNING *"

 try db.execute(sql, arguments: StatementArguments(values))

In this approach, rssUpdatableColumns defines the columns to be updated, and the updateString dynamically constructs the SET clause of your UPDATE statement. This gives you fine-grained control over the update process. While using raw SQL might seem less