Can we connect to database with babashka? To write scripts that involve db queries, mutations, transactions.

@ahmed1hsn not without shelling out, today. I do have an example where I’m using SQLite by shelling out in the examples directory. I do want to see if we can bake some JDBC thing into bb later

Maybe one day babashka could use something like https://github.com/dscarpetti/codax 😄

@borkdude haven't checked in on babashka for a month or so, quite some impressive progress! kudos on the statically linked linux binary also. nice to be able to use it on alpine.

@tomisme well, that could be useful, but I'd like to see if I can get some JDBC like thing working as well: https://github.com/borkdude/babashka/issues/372

wow, it seems to work...

(def db
  {:dbtype   "postgres"
   :dbname   "medline"
   :host     "localhost"
   :port     5555
   :user     "develop"
   :password "develop"})

(require '[clojure.java.jdbc :as jdbc])
(jdbc/query db ["select count(*) from who_ictrp"])

$ ./bb /tmp/jdbc.clj
({:count 10})

surprise functionality is the best functionality

adding next.jdbc might be better than clojure.java.jdbc

i was just going to ask about that, given next.jdbc is the next defacto standard

Alright. It seems to work:

(def db
  {:dbtype   "postgres"
   :dbname   "medline"
   :host     "localhost"
   :port     5555
   :user     "develop"
   :password "develop"})

(require '[next.jdbc :as next])
(next/execute!
 (next/get-datasource db)
 ["select title from who_ictrp limit 1"])

$ ./bb /tmp/jdbc.clj
[{:who_ictrp/title "6x3 Crossover, Bioavailability, Safety and Tolerability Among Different Eutropin Formulations in Healthy Volunteers"}]

hmm, nice

if any of you would like to try this out, I now added most of the next.jdbc namespace and binaries are posted here: https://clojurians.slack.com/archives/CSDUA8S6B/p1587645883001500

it only works with postgresql for the moment

i can try it out in a little bit, would remove a chunk of our db output-parsing 👍

I'm pretty excited about this, since we work with postgres at work too and this would allow inspecting the database in little scripts.. and spewing out some stats. less pgadmin, more clojure

✘1 ➜ (cd scripting && ./run.sh kube-ops.tools.counting)
[{:count 2693}]

works for me

@ddouglass Cool! Did you build it from source?

i did not, i downloaded the osx binary

from ci builds

ah, I didn't see your name in the channel

oh heh, i opened the link but didn't join

oh, didn't know you can do that 🙂

¯\(ツ)/¯

cool, nice to see that it worked for you too

yeah it's going to be nice

technically our db code was only like 30 lines

but now it's down to 13 🙂

Hey! A question about equality. I want to check if an update is successful. The data sources are an api and a database query thru shell. The api sends things as an int. The database gives me the same id as a string. I convert the api response to a string. When I do (= string-from-number-api-source string-from-db-call) I get false but the values are the same e.g (= "123" "123") when I print them out. When I try it in the repl it works as expected, as well as when I do str/includes?. When I try str/includes thought I get this even if I wrap them in a str before I compare them:

@belea.simion sometimes there can be invisible control characters or whitespaces. Can you do (map int ...) over the id and then check if they are the same on both sides?

what is the recommended way for reuse in babashka? i.e. sharing some common scripting functionalities across multiple scripts (that might live in totally different environments / setups). Would that be deps.clj + uberscript (to ensure that the target build is as small as possible)?

we use it for moitoring ES, et al. we use lein to create a classpath and set that on calls to bb with --classpath $(cat .bb-env)

build that into a docker image, put it up in kube, and voila

@stijn there is load-file to load files from disk, babashka.curl to fetch sources from the web, babashka.classpath to add sources to the classpath dynamically so you can require them. also you can use --uberscript to assemble a single script. I don't have one standard way of doing things right now, it's just a matter of what works in your situation

and like Darin suggests, you can use lein or clojure to do dependency resolution. clojure is especially nice since you can use git deps, so you can also put your scripts in a github repo without pushing them to clojars or something

ok, that's indeed quite a lot of options, I'll read into it 🙂

the classpath option is also pretty nice

deps.clj is just a replacement for clojure, it's nice to have, but functionally, it's identical

still keeps your scripts readable and structured

btw, really nice that there is now nrepl support, works very well with calva

@pez I'm mostly using Cursive for clojure work, but it doesn't play well with the 'shell script' structure of a babashka script. So tried out Calva just today, and really impressed by how fast I was up to speed, nice work! :)

I'm also impressed by it, great work @pez and @brandon.ringe

Thanks!

Calva also has too many ideas about a project structure, which get in the way for one-file scripting. Babashka sort of topple things and disturb circles. But that's fun. We'll be happy to play catch-up. 😎

hi!

should regexes work in babashka? 🙂

they should, I've used them

$ bb '(re-matches #"foo.*" "foobar")'
"foobar"

(re-find #"bob" "bob") works for me.

(string/replace l #"\p{C}" "")

bb '(re-find "abc" #"\p{C}" "")' ah ok

results in an error

clojure.lang.ExceptionInfo: [line 1, col 20] Unsupported escape character: \p.

aaaah forget it. my mistake.. i was running a very old version, whilst under assumptoin i had latest

Nice idea of @cldwalker to add clojure.pprint/print-table to print query results:

(require '[next.jdbc :as jdbc]
         '[clojure.pprint :refer [print-table]])

(defn query []
  (let [db {:dbtype   "postgres"
            :dbname   "medline"
            :host     "localhost"
            :port     5555
            :user     "develop"
            :password "develop"}]
    (jdbc/execute!
     (jdbc/get-datasource db)
     ["select main_id, published from who_ictrp limit 5"])))

(print-table (query))

$ ./bb /tmp/jdbc.clj

| :who_ictrp/main_id | :who_ictrp/published |
|--------------------+----------------------|
|       NCT04085224 |           2019-09-10 |
|       NCT04086225 |           2019-01-17 |
|        NCT04084184 |           2019-03-15 |
|        NCT04084197 |           2018-12-19 |
|        NCT04085042 |           2020-06-01 |