sql 2023-08-16 | Slack Archive

Would be nice to include postgres "CREATE INDEX" in HoneySQL. I'm for now running with this for the simplest case:

(sql/register-clause! ::create-index-on
  (fn [clause x]
    [(str (sql/sql-kw clause) " " (sql/format-entity x))])
  :columns)

and using it like

{::rdb/create-index-on :bank-transaction
 :columns [:bank-transaction/case-id
           :bank-transaction/date]}

igrishaev11:08:01

I did this once before, let me find the code...

igrishaev11:08:16

(sql/register-clause!
 :create-index
 (fn [_ {idx-name :name
         :keys [unique?
                if-not-exists?
                on-table
                on-field
                using]}]

   [(clojure.string/join
     " "
     ["CREATE"
      (when unique? "UNIQUE")
      "INDEX"
      (when if-not-exists? "IF NOT EXISTS")
      (name idx-name)
      "ON"
      (name on-table)
      "(" (name on-field) ")"
      (when using "USING")
      (when using using)])])

(sql/format {:create-index
              {:if-not-exists? true
               :name "idx_user_lname"
               :on-table :users
               :on-field :lname}})

["CREATE INDEX IF NOT EXISTS idx_user_lname
  ON users ( lname )"]

igrishaev11:08:36

A bit ugly but works, you're welcome to refactor it

Raghav15:08:55

curious about the use case of writing ddl via honeysql. how do you use it?

Ernesto Garcia15:08:52

I use it in the same way as SQL. I just introduced migratus for managing the versioning.

seancorfield16:08:44

DDL is mostly non-ANSI Standard in practice so each statement tends to vary a lot between dialects. HoneySQL supports the most common "core" DDL -- so there's basic ALTER TABLE / ADD INDEX stuff -- but a lot of each database's DDL is very irregular in structure and hard to model in a data-driven DSL.

seancorfield16:08:13

Feel free to create an issue on GitHub and I'll see about putting in basic CREATE INDEX (based on the machinery of alter/add I expect).

2023-08-16

Channels