2024-01-08 honeysql | Clojure Slack Archive

honeysql

Aaron Cooley 2024-01-08T17:07:37.926419Z

Hello! Is there a way to add WITH (NOLOCK) to FROM clauses (for MS SQL) in honeysql 2.0? Reading the history a bit here, looks like (from [:mytable [:m "WITH (NOLOCK)"]]) worked in 1.0, but can confirm this doesn't work in 2.0.

p-himik 2024-01-08T17:38:09.894249Z

Maybe this will be enough?

(sql/format {:select :*
             :from [[:table [:t [:raw "WITH (NOLOCK)"]]]]})
=> ["SELECT * FROM table AS t WITH (NOLOCK)"]

seancorfield 2024-01-08T17:40:45.691529Z

I was going to suggest this, similar approach:

user=> (sql/format {:from [[:table [:table [:with :NOLOCK]]]]})
["FROM table AS table WITH(NOLOCK)"]

If neither of those satisfy your needs @aaronsama feel free to create a GH issue and I'll have a think about it. I don't know whether the AS clause needs to go after the WITH there?

p-himik 2024-01-08T17:44:48.513989Z

Oh, neat, it even allows specifying things like [:with :NOLOCK :NOWAIT], with multiple hints. As an alternative that doesn't generate AS, it should be possible to register a custom clause in the user space, like :table-hint, so that something like this works:

{:select      :*
 :from        :t
 :table-hints [:nolock]}

Although it makes little sense if those hints are for a particular table and not the whole FROM - hard to tell with just a glance over the docs.

p-himik 2024-01-08T17:45:34.888919Z

> affect only the table or view referenced in that clause Ah, OK, a custom clause makes no sense then, unless it also replaces :from.

seancorfield 2024-01-08T17:46:56.422989Z

I've been working with :from quite a bit recently to add support for temporal qualifiers so adding lock qualifiers should fit right in there -- but I need links to specific database docs to be able to tell exactly what the syntax should be (preferably multiple different databases' docs 🙂 )

Aaron Cooley 2024-01-08T17:51:07.034999Z

Confirming that @p-himik’s solution worked for me, namely:

(sql/format {:select :*
             :from [[:table [:t [:raw "WITH (NOLOCK)"]]]]})
=> ["SELECT * FROM table AS t WITH (NOLOCK)"]

Obviously it's a bit more complex than would be ideal, and I'd prefer not to have to create unnecessary table aliases, but at least there is a current solution. If looking for long term ways to handle this, having a :hints option that plugs directly into the :from clause feels the most natural and elegant to me, e.g.

(sql/format {:select :*
             :from [:table [:hints :nolock]})
=> ["SELECT * FROM table WITH (NOLOCK)"]

seancorfield 2024-01-08T18:28:19.052979Z

Just a note, it would have to be :from [[:table [:hints ..]]] since :from can take multiple tables in some situations. Also the second item (after :table) is treated as an alias so figuring out an appropriate syntax for this is non-trivial -- please create a GH issue about it, so I don't forget.

Clojurians Log v2

honeysql