Automatically format the list of update columns?

[landlockedsurfer]

Hi @AugustNagro !

I'm currently trying out Magnum and things are working really nicely!! 😊 Thanks for this wonderful library! 🙏

So far there was just one use case where I got stuck. I tried to format an upsert statement in Postgres SQL but could not find anything to automate the formatting of the set clause.
E.g.:

INSERT INTO user (id, name, age) VALUES (?, ?, ?)
ON CONFLICT (id) DO UPDATE SET 
    name = ?,
    age = ?;

or the "progresque" version:

INSERT INTO user (id, name, age) VALUES (?, ?, ?)
ON CONFLICT (id) DO UPDATE SET 
    name = EXCLUDED.name,
    age = EXCLUDED.age;

Could something like TableInfo.updateCols[T](value:T) help automate the formatting of the set clause?

sql"INSERT INTO $table ${table.insertColumns} values ($user) ON CONFLICT (${user.id}) DO UPDATE SET ${table.updsateCols(user)}"

This would be very useful when new fields are added since this would automatically include the update of values for any newly added columns.

Kind regards,
Manfred

[AugustNagro]

Hi Manfred, thanks for you kind words.

I tried to format an upsert statement in Postgres

ON CONFLICT (id) DO UPDATE SET 
    name = ?,
    age = ?;

I think you're looking for something like the insertOrIgnore example in https://github.com/AugustNagro/magnum?tab=readme-ov-file#future-proof-queries

def insertOrIgnore(creator: UserCreator)(using DbCon): Unit =
  val u = User.Table
  // equiv to
  // INSERT OR IGNORE INTO user (first_name, age) VALUES (?, ?)
  sql"INSERT OR IGNORE INTO $u ${u.insertCols} VALUES ($creator)".update.run()

or the "progresque" version:

INSERT INTO user (id, name, age) VALUES (?, ?, ?)
ON CONFLICT (id) DO UPDATE SET 
    name = EXCLUDED.name,
    age = EXCLUDED.age;

The library can't support this one, since it doesn't know what columns should be set ON CONFLICT.

[AugustNagro]

The library can't support this one

However, if you have a lot of ON CONFLICT queries, you can still use TableInfo object to help scrap the boilerplate. Maybe something like

    def onConflictDoUpdate[EC, E, ID](
        table: TableInfo[EC, E, ID],
        idColumn: ColumnName,
       entityCreator: EC
    ): Frag =
      val updateCols = table.all.columnNames
        .filterNot(_.sqlName == idColumn.sqlName)
        .map(col => sql"$col = EXCLUDED.$col")
        .reduceLeft((a, b) => sql"$a , $b")
      sql"""INSERT INTO $table ${table.insertColumns} VALUES ($entityCreator)
           |ON CONFLICT DO UPDATE SET $updateCols
           |""".stripMargin

[landlockedsurfer]

Hi @AugustNagro !
Thanks for the quick reply! 🙏
This is actually the perfect solution! I did not notice the additional TableInfo fields. This allows for painless refactorings and highlights just how powerful Magnum is! 💪
This is so good, it would make a wonderful addition to the
https://github.com/AugustNagro/magnum?tab=readme-ov-file#future-proof-queries
section. If you want I can provide a pull request for that.
Maybe it would even be possible and useful to add the ID column to TableInfo?

[AugustNagro]

Re-opened #108 , since this is a good usecase.

I agree would be a good example in the readme, lets update it after #108 gets completed.