Feature Request: Upserting multiple records to a single statement

[EverybodyKurts]

Hi Jordan,

First, as always, thank you for your hard work on this package. I've benefited professionally and personally from SqlHydra. It's not much, but I threw you $50.00 for the great work you've done.

According to the postgres documentation, multiple records can be upserted on a single sql statement. They provide a simple example:

INSERT INTO distributors (did, dname)
    VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
    ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;

I would like to do this in SqlHydra, by perhaps using the entities keyword in either the insertAsync or upsertAsync computation expression, to upsert multiple records in a single sql statement.

[JordanMarr]

Thanks Kurt, I am really glad to hear that. And thank you for the sponsorship! 🙏

That seems very doable. I'll take a look and see what's already there and what's missing.

[JordanMarr]

It looks like that is already implemented.

This query:

  let currencies = 
      [ 0 .. 2 ] 
      |> List.map (fun i -> 
          {
              sales.currency.currencycode = $"BC{i}"
              sales.currency.name = "BitCoin"
              sales.currency.modifieddate = System.DateTime.Now
          }
      )
      |> AtLeastOne.tryCreate
      |> Option.get

  let! rowsInserted = 
      insert {
          for c in sales.currency do
          entities currencies
          onConflictDoUpdate c.currencycode (
              c.name,
              c.modifieddate
          )
      }
      |> ctx.InsertAsync

Yields this CommandText:

INSERT INTO "sales"."currency" ("currencycode", "name", "modifieddate") VALUES (@p0, @p1, @p2), (@p3, @p4, @p5), (@p6, @p7, @p8)
ON CONFLICT(currencycode) DO UPDATE SET
name=EXCLUDED."name"
,modifieddate=EXCLUDED."modifieddate"

;

However, this wasn't immediately clear because the new the new ctx.Logger feature that was recently contributed is logging the SQL to early for inserts (which have a fair amount of manual query "touch-ups" to do that are being missed).