honeysql 2022-12-02 | Slack Archive

jaide01:12:47

I'm using HoneySQL with node's pg library. Was going smoothly until this road bump: • honeysql formats the queries like ["SELECT * FROM table WHERE field = ?", "value"] • pg expects [ "SELECT * FROM table where field = $1", ["value"] ] Is there an existing honeysql option to use a format that matches what pg expects? Or would it be better to just use the :inline true option with honeysql?

seancorfield01:12:08

There's an open issue for that.

seancorfield01:12:04

I have an idea of how to make it work but there's some edge cases that are a bit gnarly.

jaide01:12:52

Just read through the issue. What are the kinds of edge cases that would mess it up?

seancorfield01:12:33

I'm at the chiropractor right now so I can't look up my notes on it.

jaide01:12:03

No rush, not likely to have a solution, but just curious what kind of edge cases come up with a tool like this

seancorfield01:12:23

I think there were some corner cases around expansion of in and array since those are special cases today.

seancorfield01:12:30

But it's kind of nasty since you can't guarantee everything is generated left to right, especially if folks write their own formatters. So you have to sit out so two passes.

jaide01:12:09

I see. Are there any node libraries known to be compatible with honeysql? It's a green project so can switch to another library

seancorfield01:12:26

No idea. I don't do any JS.

jaide02:12:10

Looked at a few on npmjs, seems they exclusively use that numbered format. I think given that I just have to demo a small project tomorrow I’ll just use the inline option and fix it later.

FiVo10:12:43

Hey, I am trying to figure out how to create a composite primary key.

(def table {:create-table :hello
            :with-columns
            [[:a :int]
             [:b :int]]})

(sql/format table)
;; => ["CREATE TABLE hello (a INT, b INT)"]

(sql/format-expr [:primary-key :a :b])
;; => ["PRIMARY KEY(a, b)"]

How do I combine the two without doing awkward string manipulation?

FiVo10:12:00

Postgres style btw https://kb.objectrocket.com/postgresql/postgresql-composite-primary-keys-629

seancorfield16:12:49

Not sure what you mean by "combine"? The primary key declaration should be part of the columns for the table, yes?

FiVo09:12:48

I would like to add a composite primary key, I only know how to add a "standard" primary keys.

(def table {:create-table :hello
              :with-columns
              [[:a :int [:primary-key]]
               [:b :int [:primary-key]]]})

  (-> (sql/format table) first)
  ;; => "CREATE TABLE hello (a INT PRIMARY KEY, b INT PRIMARY KEY)"

  ;; what I would like to have 
  "CREATE TABLE hello (
       a INT,
       b INT,
       PRIMARY KEY(a,b))"

seancorfield17:12:24

@UL638RXE2

user=> (-> {:create-table :foo :with-columns [[:a :int] [:b :int] [[:primary-key :a :b]]]} (sql/format))
["CREATE TABLE foo (a INT, b INT, PRIMARY KEY(a, b))"]
user=>

See https://cljdoc.org/d/com.github.seancorfield/honeysql/2.4.947/doc/getting-started/sql-special-syntax-#column-descriptors

seancorfield18:12:35

I'll create a ticket to add an example in :create-table and :with-columns showing how to do this, and probably duplicate that example into that linked section.

seancorfield18:12:27

https://github.com/seancorfield/honeysql/issues/445

FiVo08:12:12

Thank you 🙏 . I saw the special syntax section, but didn't make the connection. Haven't used SQL in Clojure. I think I even tried something similar to the above, but didn't nest [:primary-key :a :b] once more.

Noah Bogart14:12:48

My team is trying to move us from v1 to v2. We have a fmt-and-query function that we use everywhere as the primary way to write a query and then execute it with jdbc:

(defn fmt-and-query
  [db-spec query]
  (try (jdbc/query db-spec (sql/format query :quoting :ansi :allow-dashed-names? true))
       (catch Exception exc (handle-exception exc))))

Because we rely on this so much, it's been challenging to switch us to v2, due to the tight coupling of "make a sql query map" and "format and use the query map". How do y'all go about decomplecting your honeysql stuff from your jdbc stuff? What patterns have you come up with to help ease the transition?

seancorfield16:12:00

It would never have occurred to me to combine formatting and querying into a single function like that 👀 Why did you couple those operations in the first place?

seancorfield16:12:52

We use a mix of clojure.java.jdbc and next.jdbc and a mix of HoneySQL v1 and v2. Your combined function ties you to just one of each - that's a lot of coupling.

Noah Bogart17:12:21

haha I didn't do this, the original dev of the project did it in early 2019. Looking at git history, seems they were coupled to not have to repeat the :allow-dashed-names? formatting flags. Glad to know you are doing it the way I've proposed internally. I think set-dialect! will help us out a lot in the transition.

seancorfield18:12:23

Even having two separate wrappers -- one to do the formatting with those options and one to call query with error handling -- would be better, but complecting two separate concerns like that was a poor decision however you slice and dice it 😞

seancorfield18:12:11

FWIW, one of our original devs did something similar for one piece of our codebase but we didn't let it spread to other parts 🙂

👍 1

Noah Bogart20:12:02

Yeah, it's a pain in the butt now, that's for sure. Thanks for the input, will be helpful as we make this transition ourselves

2022-12-02

Channels