Is there a way to do upserts in PostgreSQL

[exyi]

I'm relying on using INSERT ... ON CONFLICT (internal_id) DO NOTHING or ``INSERT ... ON CONFLICT (internal_id) DO UPDATE ...` operations. Especially those updates are tedious to write for wider tables (each property name repeated like 5 times...). Is there a way this library could help me?

If the answer is no, but you'd like to have this functionality, I'd be happy to implement that.

[Dzoukr]

Hi @exyi, I think currently this lib doesn't allow this. Adding PR would be great indeed, but is this kind of syntax available for other databases (MySQL, MSSQL)?

[exyi]

I don't really know - from googling it seems that MySQL support something similar using ON DUPLICATE KEY UPDATE ... cause. Do nothing can be simulated by updating id = id ¯_(ツ)_/¯. MSSQL does not seem to support and I don't think I'd have a way to test it anyway...

How should we structure the API though? I'd imagine something like this:

let! rowsInserted = upsert {
    table "x"
    conflictColumn "internal_id"
    values ...
} |> conn.TryInsert

or

let! rowsInserted = upsert {
    table "x"
    conflictColumns [ "a"; "b" ]
    values ...
} |> conn.TryInsert

Try insert will do ON CONFLICT (<conflictColumns>) DO NOTHING.

Other option would be an InsertOrUpdate method that sets all properties in the conflict section.

Other option would be to extend the insert builder with something like:

insert {
    ...
    onConflictIgnore [ "id" ]
}

or

insert {
    ...
    onConflictUpdate [ "id" ]
}

[travis-leith]

Both MySql and MariaDb support REPLACE INTO ...

[Dzoukr]

Thanks for the info.

MSSQL does not seem to support ...

If there is no support across all three major database engines, then it should not be part of this library - how would DSL for that be handled on unsupported DB?

[exyi]

If there is no support across all three major database engines, then it should not be part of this library - how would DSL for that be handled on unsupported DB?

Ok... I thought it could be part of the PostgreSQL module: https://github.com/Dzoukr/Dapper.FSharp/blob/master/src/Dapper.FSharp/PostgreSQL.fs Then the method conn.TryInsert and conn.InsertOrUpdate would not exist when you'd be using MSSQL.

[Dzoukr]

If it would be only change on each module, then it's ok, but I thought there will be some new DSL keywords? I am confused now. 😄

[exyi]

Yea, I though we could add the upsert "keyword", if you don't have other idea how the API should look like. Is that a problem, though? It would return a different type, so it would be impossible to use by accident. However, I'm not super familiar with the API, so maybe I'm just saying something that does not make much sense 😅

[Dzoukr]

There is one builder (https://github.com/Dzoukr/Dapper.FSharp/blob/master/src/Dapper.FSharp/Builders.fs) = one DSL for all three database engines (resulting in a same record definition https://github.com/Dzoukr/Dapper.FSharp/blob/master/src/Dapper.FSharp/Dapper.FSharp.fs) so that's the problem.

Adding new keyword on DSL level should be done only if we can support it everywhere.

[dungarDot]

Out of my depth here but just chiming in to say that to my knowledge MSSQL uses the MERGE statement/syntax for upserts. Possible i misunderstand the conversation though.

Looking here because i'm actually attempting to use the library now for such a situation and it'd help to have.

[JordanMarr]

@exyi,

I am currently working with SQLite and I wanted to use its upsert capabilities.

Modifying the library to be able to handle upsert across SQL dialects would be awesome, however it won't happen without someone putting in some serious architecting due diligence to make sure it fits in properly with everything else.
Which would be great! But if that's more work than you're interested in, I was able to very easily "hack" it to support SQLite upsert. The basic idea is that you intercept the query that is created by the insert computation expression and hack it to do an upsert.

Some background on upserts

I learned that SQLite has two slightly different variations of upsert:

UPDATE ON CONFLICT

This is the main SQLite upsert. It inserts, and then if the conflict column(s) already exist it will do an update.
Hacking the generated query is pretty doable because the UPDATE ON CONFLICT part is simply appended to the INSERT query.

• NOTE1: there can be more than one ON CONFLICT column (comma separated) to support compound keys, so if you do create a proper API, be sure to account for that.
• NOTE2: Auto number Ids are not supported

INSERT OR REPLACE

This is similar, except that no conflict column is specified, and it will just replace all values with the given insert values.

UPDATE ON CONFLICT USAGE

Here's a sample of an UPDATE ON CONFLICT upsert query:

    /// Inserts or updates a supervisor
    member this.UpsertSupervisor(supervisor: Supervisor) =
        let record = 
            { db.Supervisor.EmployeeId = supervisor.Id
              db.Supervisor.EmployeeNo = supervisor.Number
              db.Supervisor.Email = supervisor.Email }

        use conn = openConnection()
        insert {
            into supervisorTable
            value record
        }
        |> Sqlite.updateOnConflict conn (nameof record.EmployeeId) 
            [
                nameof record.EmployeeNo
                nameof record.Email
            ]
        |> Task.awaitIgnore

Here is the magic updateOnConflict function:

/// 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.
let updateOnConflict (conn: SqliteConnection) (onConflictColumn: string) (columnsToUpdate: string list) (query: InsertQuery<'T>) =
    let query, parameters = query |> Deconstructor.insert
    let setLines = 
        columnsToUpdate
        |> List.map (fun column -> $"{column}=@{column}0\n") // Dapper.FSharp parameter names = @FirstName0, @LastName0, etc...
        |> (fun lines -> System.String.Join(",", lines))
        
    let upsertQuery = 
        System.Text.StringBuilder(query)
            .AppendLine($"ON CONFLICT({onConflictColumn}) DO UPDATE SET")
            .AppendLine(setLines)
            .ToString()
        
    conn.ExecuteAsync(upsertQuery, parameters)

Again, this is hacking the library -- it makes the assumption that parameters will be named according to the current parameter naming convention (which could always change as it's an implementation detail).
However, it works, and you can probably do it pretty easily without having to do the real implementation work.

INSERT OR REPLACE implementation is even easier because it's just a matter of replacing "INSERT" with "INSERT OR REPLACE":

let insertOrReplace (conn: SqliteConnection) (query: InsertQuery<'T>) =
    let query, parameters = query |> Deconstructor.insert
    conn.ExecuteAsync(query.Replace("INSERT", "INSERT OR REPLACE"), parameters)

[davidglassborow]

Out of my depth here but just chiming in to say that to my knowledge MSSQL uses the MERGE statement/syntax for upserts. Possible i misunderstand the conversation though.

Looking here because i'm actually attempting to use the library now for such a situation and it'd help to have.

MERGE in sql server has issues ….. https://sqlperformance.com/2020/09/locking/upsert-anti-pattern

[exyi]

MERGE in sql server has issues ….. https://sqlperformance.com/2020/09/locking/upsert-anti-pattern

I'd rather say that SQL server is an antipattern... His amazing solution is not that great, since it will only work properly in SERIALIZABLE mode which will lead to even more problems on this joke DBMS (otherwise you might get conflict. The whole point of doing upserts is to get rid of them).

If we have to support Microsoft SQL, I'm definitely not going to implement it. I'll close this issue, as it does not seem as easy to solve given the constants. Probably best to rely on custom helper functions for a specific use case...