Adding Postgres' "UPSERT" to SqlHydra

[kurt-mueller-osumc]

According to postgres' insert documentation, the ON CONFLICT clause can be used to determine what should happen if an record already exists in the database: where to ignore the conflict, replace the whole record, or to update only a few select columns.

It'd be sweet if SqlHydra had this feature. I understand that this might have to be a pull request if I want to see this feature. If I wanted to create a pull request for this feature, would you be receptive to it? If so, what advice can you give me - where should I look first in the code?

Thanks for any and all feedback.

[JordanMarr]

Hi Kurt!

I would love to have upsert functionality for all the providers.

For a very near term fix, I hacked the resulting SqlKata query to add an ON CONFLICT clause for a Sqlite project. This adds two extensions methods to the QueryContext: one for INSERT OR REPLACE and one for ON CONFLICT.

Maybe you can start with this and see if you can get it to work for Postgres?

SqliteExtensions.fs:

/// Utility functions for working with SQLite.
module internal Data.SqliteExtensions

open SqlHydra.Query

type QueryContext with
    member this.InsertOrReplace (iq: InsertQuery<'T, _>) =
        let query = iq.ToKataQuery()
        let compiledQuery = this.Compiler.Compile query
        use cmd = this.BuildCommand compiledQuery
        cmd.CommandText <- compiledQuery.Sql.Replace("INSERT", "INSERT OR REPLACE")
        cmd.ExecuteNonQueryAsync()

    /// Transforms a regular INSERT query into an UPSERT by appending "ON CONFLICT DO UPDATE SET".
    /// NOTE: This can only be called on one record at a time.
    member this.UpdateOnConflict (onConflictColumn: string) (columnsToUpdate: string list) (iq: InsertQuery<'T, _>) =
        let query = iq.ToKataQuery()
        let compiledQuery = this.Compiler.Compile(query)
        use cmd = this.BuildCommand compiledQuery

        // Get insert clase from the SqlKata query
        let insertClause = 
            query.Clauses 
            |> Seq.choose (function | :? SqlKata.InsertClause as ic -> Some ic | _ -> None)
            |> Seq.head

        // Create a lookup of SqlKata insert column indexes by column name
        let getColumnIdxByName = 
            insertClause.Columns
            |> Seq.mapi (fun idx colNm -> colNm, idx)
            |> Map.ofSeq            

        // Build upsert clause
        let setLinesStatement = 
            columnsToUpdate
            |> List.map (fun colNm -> $"{colNm}=@p%i{getColumnIdxByName.[colNm]}\n")
            |> (fun lines -> System.String.Join(",", lines))

        let upsertQuery = 
            System.Text.StringBuilder(compiledQuery.Sql)
                .AppendLine($"ON CONFLICT({onConflictColumn}) DO UPDATE SET")
                .AppendLine(setLinesStatement)
                .ToString()

        cmd.CommandText <- upsertQuery
        cmd.ExecuteNonQueryAsync()

Usage:

      let record = 
          { db.Supervisor.UtaEmployeeId = supervisor.Id
            db.Supervisor.UtaEmployeeNo = supervisor.Number
            db.Supervisor.Email = supervisor.Email }

      use ctx = openContext()

      insert {
          into supervisorTable
          entity record
      }
      |> ctx.UpdateOnConflict (nameof record.UtaEmployeeId) 
          [
              nameof record.UtaEmployeeNo
              nameof record.Email
          ]
      |> Task.awaitIgnore

[JordanMarr]

The more I think about it, the more I'm convinced that it would be worth it to have a full fledged upsert CE.

[JordanMarr]

If you want to contribute an upsert builder PR, I would recommend the following path:

• Copy the InsertBuilders.fs to UpsertBuilders.fs
• Add new custom operation to specify onConflict column (that will be added to a list since there my be more than one PK column for a table)
• I think it can probably reuse the existing entity operation used by the insert along with the includeColumn and excludeColumn operations that work with it to specify the columns.
• Call the UpdateOnConflict method above (or some variation of it that works for PostgreSQL) from the Run method on the upsertAsync and upsertTask builder classes.
• Add a few upsert tests

upsertTask (Create openContext) {
    for o in ordersTable do
    entity order
    excludeColumn o.SomeColumnA
    excludeColumn o.SomeColumnB
    onConflict o.Id
}

[JordanMarr]

Upsert added in SqlHydra.Npgsql v1.0.1