Clojurians Log v2

<!here> This channel is connected to the HoneySQL repo on GitHub (since the library is getting a bunch of updates). If it gets too noisy for folks, I'll dial that back.

I suppose it's a good time to announce that I'm taking over maintenance from @michaelblume who has been a great steward for the library for quite a while, and now has other pressures on his time.

@seancorfield thank you for doing this.

If folks have time, I'd appreciate some eyes over https://github.com/jkk/honeysql/pull/217 which adds multi-table delete capability (previously requested in PRs 27 and 60!).

and thanks to @michaelblume for his previous stewardship

HoneySQL has remained my favorite way to build dynamic SQL in any language for the last few years

Thank you both for your previous and future work on this

Just curious, do you folks test the output of honeysql before the format? I wonder what is the best testing strategy for it

There's a combination of testing going on: the README examples are all used as tests and then there are specific formatting tests. The specific tests generally check that the data structure formats to the correct SQL. The README tests mostly check that the helpers combine (to produce the right data) and can be formatted to the correct SQL.

It's fair to say that there just aren't enough tests overall tho'... Micheal was (quite rightly) sitting on some PRs that were submitted without tests. I'm slowly working through those, creating new PRs with tests (both README examples, and specific tests).

I'm probably going to update https://github.com/jkk/honeysql/pull/61 tomorrow (which already supercedes https://github.com/jkk/honeysql/pull/28).

Part of the problem there is that those PRs are old and are against the version of HoneySQL before conversion to .cljc happened (so they can't possibly be merged now).

I meant more in a real app...I guess I was kind of wondering if I am doing it right :)

@richiardiandrea Ah, I get it... I think when Fumiko started using it heavily, she wrapped format so that it also printed the SQL to the console for debugging...

I would most certainly test at the generated SQL level, rather than the data level, since that's what's important. And I pretty much only ever use the DSL helpers rather than building the data structure directly.

I wrap format and jdbc/query and jdbc/execute! into exec-runner and query-runner that take a db-spec/pool and a honeysql map. This let's me handle various debugging/analysis/benchmarking via binding variables

For my code, I test the generated maps except in the case where I have my own fn helpers or clauses, and then I test those as part of our internal utility library

I treat the format call as a black box except during reply testing

@bja Are there things that HoneySQL and/or clojure.java.jdbc could provide out of the box, to make your life easier?

I don't think so. You actually built in an explain hook that I need to take advantage of rather than my home rolled stuff

If you factor our the misc debugging things, it's basically a composition of honeysql.core/format and the correct jdbc function

There's still no direct, easy way to access the actual SQL being passed to the JDBC driver itself. Explain is a bit different.

Just so our user API is (query-runner db (-> (select :foo) ...))

@bja have you seen the :return-keys stuff for execute! in the last few releases?

0.7.7 now lets you run :row-fn and :result-set-fn over the generated keys' result set.

Unfortunately only at a glance. I've been super busy being a one man show at my company

Clojure is probably the only way I can still keep this thing afloat

And maintain sanity

Postgres plus honeysql is a bit part of it

Big part of it

I need to migrate to a newer jdbc to take advantage of the reducible querysets

Well, HoneySQL's insert stuff -- which I didn't even know had been added! -- looks to work with execute! rather than the java.jdbc insert! stuff 🙂 so I figured beefing up execute! would be useful.

Oh, yeah, we've starting using reducible-query at work... enjoying that 🙂

I found that the honey map testing is sometimes odd because of the records all over the place

Yeah, I only use jdbc/query, jdbc/execute! And with-db-transaction?? regularly

I might have the name on the transaction thing wrong

@richiardiandrea Ah, yes, all the data readers are backed by records... I can see that being weird to test.

The way I use java.jdbc, anything that returns results goes in a jdbc/query and everything else in the jdbc/execute!

But I run ddl separately since I don't have dynic needs there

Dynamic

DDL is a big pain. There's really no good solution for that (as far as DSLs or other high-level stuff goes).

Probably a to-map would do the job in the tests

We use a utility that sorts .SQL files in a directory and then calls psql

It's been the only sane solution for ddl

Everything else makes weird or dumb assumptions or wants me to learn some half-baked dsl with a ton of edge cases

The script monitors the success and writes a log into a metadata table

@bja we do something similar, except directly in Clojure with db-do-commands to run all our migrations.

Simple stuff, but just enough

Our script is actually in clojure, but we shell out to psql

I don't remember at the time why, but we were under a time crunch and that was a fast path to success

(i.e., a directory full of .sql files, read by Clojure and run via db-do-commands -- and we have a dblevel in the database that we use to determine which migrations to actually apply... that works in dev, for level 0, and production too)

We just maintain an ID in the filename. It's ordered, but not necessarily sequential

We usually use Unix epoch

It made working with multiple branches easier

Filenames are like NNNNN-name.direction.sql

And if you want fancy stuff like transactions you have to write begin yourself

It's a struggle

We inter the filename, timestamp, and source into the DB on success

Are the SQL files loading testing data? Honestly dumb question, why are they ordered by date?

DDL

Sometimes we modify previous ddl

Alter statements or mass data updates

i.e. some gdpr deletes a couple months ago

Ours are either NNNNN_name.sql or NNNNNdev_name.sql and we run all of them on dev/CI, but we only run the non-dev ones on QA/production. And ours are strictly ordered by NNNNN (although we sort on the whole filename).

Oh ok got it

So on dev/ci we can retroactively apply changes by modifying earlier migrations. On QA/production it's strictly incremental, based on the last NNNNN in the database.

We developed a programmatic GDPR erasure tool so we can run it on demand whenever a member asks for their personal data to be "forgotten".

We did too. During discovery, we determined we had a ton of unused data that was a liability.

So we deleted that data enmass

Some half finished or abandoned features from the last 5 years

Thanks @richiardiandrea!

well thank you 😉

@richiardiandrea Your PR left the O/S update stuff in place so Lumo 1.9.0-alpha worked because the newer libstdc++ was installed. I'm trying Lumo 1.8.0 now without the O/S update stuff.

yeah I was thinking about that, but lumo will probably release this at some point and I kind of opted was testing against the newer version

unless it significantly slows down the build it should be good

Lumo 1.8.0 worked. Also removing the Leiningen install works. I'm going to add .m2 to the cache I think, which should speed up subsequent builds.

@richiardiandrea Do you think it's reasonable to cache .nvm as well?

@seancorfield we don't do it in lumo: https://github.com/anmonteiro/lumo/blob/master/.travis.yml#L16-L23

Right, but you're not relying on a fixed version of Lumo being installed... I'll try it and see what happens 🙂

ah you mean the global lumo...if it's installed in there it probably makes sense yes

lumo can also be installed as executable so maybe it is easier to control where it goes

Wow, caching ~/.m2 makes a big difference! 🙂

ah ah yeah 😄