Fork me on GitHub
#sql
<
2020-05-19
>
Johannes F. Knauf04:05:09

I have been playing around a bit with HoneySQL and SQLingvo. I totally understand that nobody would like to use any other DB than Postgres anyways, but in the Enterprise, freedom of choice is limited. Are you aware of any efforts to 1. either provide a similar DSL but with an abstraction for vendor details beyond quoting (a.o. SQL dialects differ in their approach to: quoting, limiting, setting aliases, case sensitivity, ...) 2. or provide a similar DSL specifically tuned for Oracle?

seancorfield05:05:39

@johannes.f.knauf HoneySQL is extensible so that people can written DB-specific extensions, so you could easily provide DB-specific libraries that used a common syntax, but implemented differently for each DB -- if you really wanted a DB-neutral DSL.

Johannes F. Knauf06:05:14

I guess you refer to https://github.com/jkk/honeysql/blob/master/src/honeysql/format.cljc The extension API unfortunately is quite incomplete. format-clause methods, e.g. LIMIT, are hardcoded.

Johannes F. Knauf06:05:28

Well, forking might be an option. However, it feels a bit like the good old LISP curse: It's to simple to wrap your own library, that nobody ever builds and maintains reusable libraries.

valerauko09:05:02

we use this postgres-specific plugin? for honeysql https://github.com/nilenso/honeysql-postgres if there's no such for oracle, you could make one. looking at the above's source code, it's not very complicated

valerauko09:05:12

throwing a PR at honeysql resolving the hardcoding problem you're facing is an option too

Johannes F. Knauf10:05:52

@UAEH11THP Thanks for the hint to honeysql-postgres. I will have a look.

Johannes F. Knauf10:05:20

Regarding PR, I am not sure. One can also consider the simplicity as a feature. In fact, translating :limit 10 to "LIMIT 10" is quite literal and straightforward, while translating it to something DB-specific like "WHERE ROWNUM < 10" might be considered too much magic.

Johannes F. Knauf10:05:53

My impression is: Right now, HoneySQL focuses on writing the exact same SQL in a nice DSL. It is not a way of writing an ANSI SQL like DSL, whose semantics are kept for all target platforms.

Johannes F. Knauf10:05:17

Maybe the author @U050BEF8Y can provide some insight?

valerauko15:05:58

there is a #honeysql channel too!

seancorfield19:05:45

@johannes.f.knauf I am the maintainer of HoneySQL -- Justin moved on a while back.

Johannes F. Knauf03:05:53

Oh, nice! I will ask the question about the general design ideas there.

seancorfield05:05:21

But the reality is that changing databases is very, very rare so DSL portability in code is not a real world consideration that anyone tends to care about.

seancorfield05:05:58

So, I guess the answer to your question is, no, there are no efforts to do either 1. or 2. as far as I know.

jaime18:05:36

Is there a way to configure next.jdbc to convert column names from kebab-case to snake_case and vice-versa when inserting and reading?

seancorfield19:05:53

@jaime.sangcap Yes. You can pass in :column-fn and :table-fn to the next.jdbc.sql functions to convert Clojure names keywords to SQL names, and use a build adapter via :builder-fn to convert SQL names to Clojure names.

seancorfield19:05:02

With the latter, the simplest solution is to use the as-modified-maps builder with :qualifier-fn and :label-fn.

seancorfield19:05:26

This page https://cljdoc.org/d/seancorfield/next.jdbc/1.0.424/doc/getting-started/result-set-builders has an example of turning snake_case in SQL into kebab-case in Clojure.

jaime19:05:37

@seancorfield Thanks for the pointers. It starts to make sense now ❤️

seancorfield19:05:15

An excellent library to use with next.jdbc for that purpose is camel-snake-kebab -- that's what we use at work in a few places

👍 12
jaime19:05:56

How do you minimize repetition of passing the transformation functions (:table-fn, :column-fn, :builder-fn)? Do you wrap the next.jdbc.sql namespace like?

;; myapp.sql
  (defn as-kebab-maps [rs opts]
    (let [kebab #(clojure.string/replace % #"_" "-")]
      (result-set/as-modified-maps rs (assoc opts :qualifier-fn kebab :label-fn kebab))))
  
  (defn insert! [connectable table key-map]
    (jdbc/insert! connectable  table key-map {:builder-fn as-kebab-maps}))
EDIT: The option in the insert! is wrong, should be
{:table-fn #(clojure.string/replace % #"-" "_")
  :column-fn #(clojure.string/replace % #"-" "_")}

seancorfield19:05:58

What I typically do is have a global (def default-jdbc-opts {...}) and just add that to every call.

seancorfield19:05:04

I'm working on some way to set default options that doesn't cause a performance impact on people who don't use that but it's tricky since it relies heavily on Java SQL objects for performance (so there's no easy way to "wrap" things).

seancorfield19:05:59

The Friendly SQL Functions page has this example BTW:

(defn snake-case [s] (str/replace s #"-" "_"))

(sql/insert! ds :my-table {:some "data"} {:table-fn snake-case})

jaime20:05:53

I see. thanks for explaining