Comments (12)
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...
from dapper.fsharp.
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)?
from dapper.fsharp.
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" ]
}
from dapper.fsharp.
Both MySql and MariaDb support REPLACE INTO ...
from dapper.fsharp.
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?
from dapper.fsharp.
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.
from dapper.fsharp.
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. 😄
from dapper.fsharp.
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 😅
from dapper.fsharp.
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.
from dapper.fsharp.
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.
from dapper.fsharp.
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)
from dapper.fsharp.
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
from dapper.fsharp.
Related Issues (20)
- Add TypeHandler for fieldless and single case DUs
- Support union/union all cases HOT 3
- SQLite.OptionTypes.register seems to be missing `float`
- Is there a query that implements something like `QueryFirst`, `QueryFirstOrDefault`? HOT 2
- Question about the Where operator AND and OR HOT 3
- Are multi column joins no longer supported? HOT 9
- Sqlite and multiple type registration HOT 3
- Structures in columns HOT 3
- Working with auto increment IDs HOT 3
- namespace 'LinqBuilders' is not defined HOT 8
- Postgresql Full Text Searching HOT 1
- how to use transactions in DapperFSharp? HOT 3
- Adding pipeline API? HOT 1
- How do typehandlers in F# work?
- Not Implemented Exception While Using SRTP HOT 6
- "count" problem with inner join in v4.5.1+ HOT 2
- `CLIMutable` seems to be necessary for some records HOT 4
- Record attribute to use capitalized word for record key on F# but lowercase for database HOT 2
- Batch insert support HOT 6
- Support select with `IAsyncEnumerable<'t>` result
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from dapper.fsharp.