[FEATURE]: Add support for MERGE

[mikerockett]

Describe what you want

The SQL standard supports MERGE, and Postgres 15+ supports it under the hood.

Syntax:

MERGE
 INTO <target>  [[AS] <new target name>]
USING <source>  [[AS] <new source name>]
   ON <join-condition>
      <when…then rules>

While one can raw-dog it, I think it would be really nice to see it in Drizzle. Something like this:

await db.mergeInto(tableName)
  .usingValues(...upsertRecords) // of inferred insert type
  .updateOnMatched(
    ($new) => ({
      column: $new.value
    })
  )
  .insertOnNotMatched(
    ($new) => ({
      column: $new.value
      other_column: $new.other_value
    })
  )
  .returning()

Rough idea of course, but you get the point. A clean API to use the MERGE statement that will hopefully, one day, be available in all dialects.

Even though Drizzle aims to have a simple builder API, is this something that can be considered?

[eydelrivero]

Any comment on this one from the team? 👀

[L-Mario564]

Any comment on this one from the team? 👀

Not a priority right now, but it's something we can definitely consider in the future.