search

Dzoukr / Dapper.FSharp

Lightweight F# extension for StackOverflow Dapper with support for MSSQL, MySQL, PostgreSQL, and SQLite
MIT License
365 stars 35 forks source link

InsertOutput in MySQL #61

Closed dredgy closed 2 years ago

dredgy commented 2 years ago

Would it be possible to implement an InsertOutput option for MySQL? I want to build a library on top of this one, and whilst I personally use Postgres it would probably be good to have it universal.

This could be done with another select query immediately after using LAST_INSERT_ID() Though I’m not sure on best practice there.

Or could even implement Returning which does work in MariaDb I believe, so gives a little more coverage.

Dzoukr commented 2 years ago

Hi, I think it has been discussed here https://github.com/Dzoukr/Dapper.FSharp/issues/10

Is it possible on MySQL now? I am not following MySQL news so maybe I missed something...

dredgy commented 2 years ago

It is possible on MariaDB, not MySQL. I'm not sure if it's worth spinning off another version for Maria or not.

For MySQL it would have to be done manually but I don't think would be particularly hard to do. Just immediately do the insert query, then a select query as a separate statement, returning the row with ID with LAST_INSERT_ID()

Dzoukr commented 2 years ago

Just immediately do the insert query, then a select query as a separate statement, returning the row with ID with LAST_INSERT_ID()

That's possible to do, but there is still a risk of getting the ID of the row inserted by a different (concurrent) query I think.

dredgy commented 2 years ago

Whilst I’m unsure exactly how it works on .NET, it is handled on a per connection basis so should not ever be a problem but might be if there is one connection per app that’s never closed rather than one connection on every request. So that’s never an issue in PHP, but I can see how it would be on a compiled application.

it’s just such a useful feature to be missing in one database engine!

Dzoukr commented 2 years ago

Closing for now. The intention of this library is to provide wrapper over existing functionality, not adding missing ones. Please reopen when MySQL have support for InsertOutput on a database level.