batch UPDATE

[ethanabrooks]

I am trying to figure out how to do batch update with doobie. I have the following code snippet:

Update[?]( // What type to use here?
  s"""
     |UPDATE mytable SET
     |commitHash = tmp.commitHash
     |config = tmp.config
     |configScript = tmp.configScript
     |containerId = tmp.containerId
     |description = tmp.description
     |killScript = tmp.killScript
     |launchScript = tmp.launchScript
     |FROM
     | (SELECT
     | unnest(?) as commitHash,
     | unnest(?) as config
     | unnest(?) as configScript
     | unnest(?) as containerId
     | unnest(?) as description
     | unnest(?) as killScript
     | unnest(?) as launchScript
     | ) AS tmp
     | WHERE mytable.id = tmp.id;
     |""".stripMargin).updateMany((
  newRows.map(_.commitHash),
  newRows.map(_.config),
  newRows.map(_.configScript),
  newRows.map(_.containerId),
  newRows.map(_.description),
  newRows.map(_.killScript),
  newRows.map(_.launchScript),
))

This would eventually become part of a larger INSERT .... ON CONFLICT ... UPDATE statement. This approach was inspired by this stackoverflow answer. The trouble is that updateMany expects an argument of type F[A] where F is Foldable. This is not possible in my case because each of the ?'s might correspond to a scala list / sql array of a different type, e.g. suppose commitHash is an Int and all the rest are Strings.

It may be that this entire approach is misguided, but this answer from https://github.com/tpolecat/doobie/issues/193#issuecomment-459971953:

@letalvoj In postgres "upsert" is just an insert query with on conflict clause . You can write this query in doobie like any other query. What exactly do you mean by "native support"?

suggests that it should be possible to simply write an on conflict clause like this using doobie. Thanks!

[letalvoj]

Almost seems like it was my quote which scared me off a bit. Yet, it's just a quote I am mentioned in.

Btw. a quick google search yielded a few relevant references, which might help you ;-) https://gitter.im/tpolecat/doobie/archives/2016/02/13 https://gitter.im/tpolecat/doobie/archives/2017/06/09?at=593abc1702c480e6722a3c2c

[ethanabrooks]

Thanks @letalvoj . I actually just realized that I didn't need to use .updateMany(...) in the first place. Here is some working code:

sql"""
|UPDATE mytable SET
|commitHash = tmp.commitHash
|config = tmp.config
|configScript = tmp.configScript
|containerId = tmp.containerId
|description = tmp.description
|FROM
| (SELECT
| unnest(${newRows.map(_.commitHash)}) as commitHash,
| unnest(${newRows.map(_.config)}) as config
| unnest(${newRows.map(_.configScript)}) as configScript
| unnest(${newRows.map(_.containerId)}) as containerId
| unnest(${newRows.map(_.description)}) as description
| ) as tmp
| WHERE runs.name = tmp.name;
|""".stripMargin.update.run

I guess the only thing I would want to know before closing this comment is whether this is "idiomatic" doobie. Like maybe is a way to cut down on the boilerplate? Thanks!

[ethanabrooks]

Actually I realize that the bigger issue is that in order to actually implement INSERT .... ON CONFLICT ... UPDATE, I need to somehow combine the aforementioned statement with a batch update using updateMany. So the insert clause looks like this:

Update[RunRow](
"insert into runs (commitHash, config, configScript, containerId description)
 values (?,?,?,?,?)").updateMany(newRows)

and the update clause looks like

Update[(String, String, String, Option[String], String, String)]("""
|UPDATE mytable SET
|commitHash = tmp.commitHash
|config = tmp.config
|configScript = tmp.configScript
|containerId = tmp.containerId
|description = tmp.description
|FROM
| (SELECT
| unnest(${newRows.map(_.commitHash)}) as commitHash,
| unnest(${newRows.map(_.config)}) as config
| unnest(${newRows.map(_.configScript)}) as configScript
| unnest(${newRows.map(_.containerId)}) as containerId
| unnest(${newRows.map(_.description)}) as description
| ) as tmp
| WHERE runs.name = tmp.name;
|""".stripMargin).run((
newRows.map(_.commitHash),
newRows.map(_.config),
newRows.map(_.configScript),
newRows.map(_.containerId),
newRows.map(_.description),
))

How do I combine these into the same statement? One uses .updateMany(...) and the other uses .run(...).