Fork me on GitHub
#honeysql
<
2021-02-17
>
seancorfield03:02:45

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

seancorfield04:02:05

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

😍 6
👍 3
zackteo06:02:16

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

seancorfield17:02:02

@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).

borkdude21:02:54

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)

borkdude22:02:37

:parameterizer
does what I want, I think :)

seancorfield22:02:08

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.

borkdude22:02:54

wow... I'll try!

borkdude22:02:16

@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"}])
3) And then I show how to use the pod-babashka-sqlite3 pod so you don't need the :inline true option but can interact with sqlite as if it's a local library.

3
seancorfield22:02:23

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

borkdude22:02:31

will do, so far so good

borkdude22:02:46

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

borkdude22:02:21

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

seancorfield22:02:19

As in create table?

borkdude22:02:46

ooh, thanks

seancorfield22:02:30

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).

borkdude22:02:28

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]])))))))

borkdude22:02:34

So for values in v1 I needed to do:

(h/values (map (juxt :name :scientific-name)
                                 animals))
Does v2 maybe accept maps?

borkdude22:02:37

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

borkdude22:02:31

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

seancorfield22:02:52

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

borkdude22:02:10

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

seancorfield22:02:44

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

seancorfield22:02:50

I can see arguments for both.

borkdude22:02:46

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

borkdude22:02:56

so the current behavior could stay as it is

borkdude22:02:55

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

borkdude22:02:50

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

borkdude22:02:00

Afk now. Thanks for the improvements so far!

3