sqlboiler
sqlboiler copied to clipboard
volatiletech
Mysql SELECT ... LOCK IN SHARE MODE
What version of SQLBoiler are you using (sqlboiler --version)?
SQLBoiler v2.5.1
Hi
Is it possible to generate select query with LOCK IN SHARE MODE using Query Mods?
You should be able to use: https://github.com/volatiletech/sqlboiler/blob/master/queries/qm/query_mods.go#L141
It's called FOR because I think in PSQL and MySQL most of the locking statements begin with for. It essentially just tags things on the end of the query so it should work fine. There's nothing special about it, you could technically use it to slap on anything to the end of the query. Re-open if this doesn't work for you :)
Looks like it will generate FOR LOCK IN SHARE MODE, but mysql will return syntax error on that
https://github.com/volatiletech/sqlboiler/blob/86c580f53724a794e2f807707653f480616d455d/queries/query_builders.go#L393
Ah, I forgot that we actually added a FOR. Ugh. So this becomes kind of icky, we could add another query mod for LOCK but it's getting a bit silly since the syntax diverges so wildly in all the SQL dialects. Maybe something generic to tag on the end of a query is preferable. What do you think?
Yeah. Could do something like that. It sort of hurts in a way, but I don't know of anyway around it. Also, is there anything that's stopping you from using a raw query here? Is it too painful/inconvenient? I'm sort of shying away from the idea of adding too many things to the query mods, this might be acceptable but I'd like to hear why the raw query is undesirable here. Maybe we could improve that for advanced queries.
There are exiting (and complex enough) queries that were written using QueryMods. For adding lock we are must rewrite those with raw query. That is annoying and more error prone. So appending sql using qm will be realy usefull.
Did a resolution ever materialize for this? I've updated to the latest code and tried With() but that also produces a syntax error. Thankfully our explicit locking is very limited so I am using raw sql where needed, but I like QMs for the safety.
@visortim I never added this because it didn't feel "right". Basically having the equivalent of a "half raw query". But I don't care enough to fight it :) I'd accept a PR for this.
@visortim I never added this because it didn't feel "right". Basically having the equivalent of a "half raw query". But I don't care enough to fight it :) I'd accept a PR for this.
No, I tend to agree with you and trust you judgement that something like AppendSQL() is an anti-pattern and would prefer to continue with raw SQL if a more wholistic/type-safe solution doesn't exist.