Fork me on GitHub
#babashka
<
2020-04-23
>
ahmed1hsn07:04:22

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

borkdude07:04:26

@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

tomisme09:04:28

Maybe one day babashka could use something like https://github.com/dscarpetti/codax šŸ˜„

stijn10:04:48

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

borkdude11:04:43

@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

borkdude12:04:11

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

ddouglass12:04:52

surprise functionality is the best functionality

borkdude12:04:28

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

ddouglass12:04:39

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

borkdude12:04:10

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"}]

borkdude12:04:26

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

cldwalker14:04:46

Works on osx and psql 11.3 for me. Excited to use this at work if it lands! Fwiw I paired this with https://github.com/cldwalker/clj-clis/blob/master/clj-table for a more psql feel:

$ ./bb tmp.clj | clj-table                                                                                                                                                                                          

|               :Trial/name |            :Trial/created_at |
|---------------------------+------------------------------|
| Gabriel Seeded Trial ID 1 | Sat Aug 21 00:17:18 PDT 2021 |
| Gabriel Seeded Trial ID 2 | Fri Nov 30 08:18:10 PST 2018 |

borkdude14:04:43

print-table looks simple enough to add to babashka. I made an issue for it: https://github.com/borkdude/babashka/issues/375

borkdude14:04:03

thanks for testing.

cldwalker15:04:06

Cool. That'd be handy

borkdude15:04:23

The reason not all of pprint is there right now, is that we chose fipp instead of normal clojure.pprint, because I could not get clojure.pprint to work with graalvm

borkdude12:04:42

it only works with postgresql for the moment

ddouglass12:04:23

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

borkdude12:04:06

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

ddouglass13:04:39

āœ˜1 āžœ (cd scripting && ./run.sh kube-ops.tools.counting)
[{:count 2693}]

ddouglass13:04:42

works for me

borkdude13:04:06

@ddouglass Cool! Did you build it from source?

ddouglass13:04:21

i did not, i downloaded the osx binary

ddouglass13:04:26

from ci builds

borkdude13:04:39

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

ddouglass13:04:04

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

borkdude13:04:13

oh, didn't know you can do that šŸ™‚

ddouglass13:04:17

ĀÆ\(惄)/ĀÆ

borkdude13:04:40

cool, nice to see that it worked for you too

ddouglass13:04:53

yeah it's going to be nice

ddouglass13:04:00

technically our db code was only like 30 lines

ddouglass13:04:04

but now it's down to 13 šŸ™‚

belea.simion14:04:06

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:

borkdude14:04:15

@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?

belea.simion14:04:15

Now I get ClassCastException: ava.lang.String cannot be cast java.lang.Character.

borkdude14:04:39

Can you do (prn (type x) (type y))

borkdude14:04:47

you're comparing two different types it seems?

belea.simion14:04:53

it says java.lang.String for both

borkdude14:04:51

What about (prn (hash x) (hash y))?

belea.simion16:04:54

one looks like a proper hash, the other either a string or an empty string.

borkdude16:04:26

can you try to isolate this into some code that I can run myself? I don't know what I'm looking at anymore

stijn19:04:17

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

ddouglass19:04:29

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)

ddouglass19:04:50

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

borkdude19:04:09

@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

borkdude19:04:52

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

stijn19:04:28

ok, that's indeed quite a lot of options, I'll read into it šŸ™‚

stijn19:04:58

the classpath option is also pretty nice

borkdude19:04:17

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

stijn19:04:29

still keeps your scripts readable and structured

stijn19:04:38

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

stijn19:04:38

@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! :)

borkdude19:04:59

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

pez20:04:29

Thanks!

pez20:04:13

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. šŸ˜Ž

kah0ona20:04:36

should regexes work in babashka? šŸ™‚

nate20:04:46

they should, I've used them

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

dpsutton20:04:49

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

kah0ona20:04:20

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

kah0ona20:04:18

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

kah0ona20:04:56

results in an error

kah0ona20:04:02

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

souenzzo20:04:26

~ clj -e  '(re-find "abc" #"\p{C}" "")'
Execution error (ArityException) at user/eval1 (REPL:1).
Wrong number of args (3) passed to: clojure.core/re-find

Full report at:
/tmp/clojure-2536549118888962578.edn

kah0ona20:04:29

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

borkdude20:04:21

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 |

cldwalker20:04:44

Awesome! Can't wait for the next release šŸ˜ƒ