Clojurians Log v2

seancorfield/honeysql {:mvn/version "2.0.0-alpha2"} is available for testing. It's mostly an overhaul and expansion of the documentation around v2 but also includes a number of small bug fixes and improvements, mostly around the insert/upsert syntax for PostgreSQL. Everything in nilenso/honeysql-postgres is built-in for this release and mostly documented (there are some small differences) -- see: https://cljdoc.org/d/seancorfield/honeysql/2.0.0-alpha2/doc/getting-started/postgresql-support and nearly all of the helper functions have docstrings now -- see: https://cljdoc.org/d/seancorfield/honeysql/2.0.0-alpha2/api/honey.sql.helpers

At this point, the core API feels pretty stable to me and the overall shape and scope of the library also feels fairly well "baked" so I think it's mostly a matter of documentation to reach a "gold" 2.0 release, although I'm still ploughing though the open issues from the 2.0 milestone: https://github.com/seancorfield/honeysql/milestone/7

Whoops. I think I might have glanced past that - and I think I might have been trying out how other forms of PostGIS queries might work. Looks like I can use sql/call for the most part then 🙂 and tap into sql/raw and sql/format for finer manipulation

@zackteo Yup, in 2.0 it's easier because you never need sql/call and sql/raw has just become a :raw "function" (and :inline is more powerful now too).

Is there an option in honeysql to convert a sqlvec string (with question marks) and values into one string with the values embedded? This might be an anti-pattern but for shelling out I want to use this (just for a demo)

:parameterizer

In 1.x, yes, :parameterizer but in 2.x, you can just pass :inline true to sql/format and it attempts to inline all parameters.

wow... I'll try!

@seancorfield That worked. With babashka! I'm writing a small demo for a presention. 1) First I show how shitty it is to write manual sql using string functions and send that through java.shell to sqlite. 2) Then I show that you can use honeysql to take care of the sql.

(defn insert-sql [animals]
  (sql/format (-> (h/insert-into :animals)
                  (h/columns :name :scientific_name)
                  (h/values (map (juxt :name :scientific-name)
                                 animals)))
              {:inline true}))

(defn insert-rows! [animals]
  (let [sql (insert-sql animals)
        {:keys [:exit :err]}
        (query (first sql))]
    (when (not (zero? exit))
      (println "Error inserting var!" err)
      (System/exit exit))))

(insert-rows! [{:name "Bison" :scientific-name "Bos gaurus"}
               {:name "Duck"  :scientific-name "Anas Platyrhynchos"}])

LMK if you run into anything in 2.0.0 that causes problems or you'd like to see enhanced.

will do, so far so good

I was looking for something around create, I guess most people just do that as a raw string and not using data structures?

I don't really need it, I just wondered if there was something

As in create table?

https://cljdoc.org/d/seancorfield/honeysql/2.0.0-alpha2/doc/getting-started#ddl-statements which leads you to https://cljdoc.org/d/seancorfield/honeysql/2.0.0-alpha2/doc/getting-started/postgresql-support (because the DDL stuff all used to be in the nilenso library, but now it is supported in core).

DDL documentation is the first section here: https://cljdoc.org/d/seancorfield/honeysql/2.0.0-alpha2/doc/getting-started/sql-clause-reference and the helpers are all listed here https://cljdoc.org/d/seancorfield/honeysql/2.0.0-alpha2/api/honey.sql.helpers

ooh, thanks

You can't -- currently -- add clauses to a create table statement after the column definitions, but that is coming maybe in the next alpha (since I need it for MySQL).

Worked.

(defn create-db! []
  (when (not (.exists (io/file db)))
    (query (first
            (sql/format
             (-> (h/create-table :animals)
                 (h/with-columns [[:name :text]
                                  [:scientific-name :text]])))))))

So for values in v1 I needed to do:

(h/values (map (juxt :name :scientific-name)
                                 animals))

eh, I guess it does, reading this in the README now

oh yes, it only works when you leave out the h/columns clause, is this expected?

Yes -- because the column names come from the (union of) the hash maps instea.

I guess h/columns could act as select-keys maybe?

Maybe? Or maybe throw an exception if the h/columns are not in the hash map keys?

I can see arguments for both.

yeah, maybe the user should just map a select-keys over the maps first

so the current behavior could stay as it is

not sure

but right now it yields invalid sql when you combine columns + maps

maybe throw in that case with a message like: you should explicitly format the maps yourself with the desired columns

Afk now. Thanks for the improvements so far!