Home

Awesome

Babashka sql pods

Babashka pods for interacting with SQL databases.

Supported databases:

PRs for other SQL databases are welcome. (Look at #15 + #20 for an example of adding a new DB.)

Install

The following installation methods are available:

<!-- - With [brew](https://brew.sh/): `brew install borkdude/brew/pod-babashka-<db>` --> <!-- where `<db>` must be substited with the database type, either `hsqldb` or --> <!-- `postgresql`. -->

Compatibility

Pods from this repo require babashka v0.4.3 or later.

Available vars

The pods expose these namespaces with vars, where <db> must be substituted with the database type, either hsqldb, postgresql, or oracle:

More functions from next.jdbc can be added. PRs welcome.

Run

An example using pod-babashka-postgresql:

(require '[babashka.pods :as pods])

;; load from pod registry:
(pods/load-pod 'org.babashka/postgresql "0.1.0")
;; or load from system path:
;; (pods/load-pod "pod-babashka-postgresql")
;; or load from a relative or absolute path:
;; (pods/load-pod "./pod-babashka-postgresql")

(require '[pod.babashka.postgresql :as pg])

(def db {:dbtype   "postgresql"
         :host     "your-db-host-name"
         :dbname   "your-db"
         :user     "develop"
         :password "develop"
         :port     5432})

(pg/execute! db ["select version()"])
;;=> [{:version "PostgreSQL 9.5.18 on x86_64-pc-linux-gnu (Debian 9.5.18-1.pgdg90+1), compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit"}]

An example using pod-babashka-hsqldb:

(require '[babashka.pods :as pods])

;; load from pod registry:
(pods/load-pod 'org.babashka/hsqldb "0.1.0")
;; or load from system path:
;; (pods/load-pod "pod-babashka-hsqldb")
;; or load from a relative or absolute path:
;; (pods/load-pod "./pod-babashka-hsqldb")

(require '[pod.babashka.hsqldb :as db])

(def db "jdbc:hsqldb:mem:testdb;sql.syntax_mys=true")
(db/execute! db ["create table foo ( foo int );"])
;;=> [#:next.jdbc{:update-count 0}]

(db/execute! db ["create table foo ( foo int );"])
;;=> ... error output from pod omitted
;;=> clojure.lang.ExceptionInfo: object name already exists: FOO in statement [create table foo ( foo int )] [at line 6, column 1]

(db/execute! db ["insert into foo values (1, 2, 3);"])
;;=> [#:next.jdbc{:update-count 3}]

(db/execute! db ["select * from foo;"])
;;=> [#:FOO{:FOO 1} #:FOO{:FOO 2} #:FOO{:FOO 3}]

A more elaborate example can be found here.

Arrays

Writing arrays

Inserting arrays works automatically: just pass a Java array, with e.g. (into-array [1 2 3]).

Reading arrays

Array columns automatically get converted to Clojure vectors. Converting back into arrays must be done manually.

JSON

This section only applies to PostgreSQL for now, but can be extended to other databases.

Writing JSON

(db/execute! db ["insert into json_table values (?::text);" (json/generate-string {:a 1})])
(db/execute! db ["insert into json_table values (?);" (db/write-json {:a 1})])

Reading JSON

{:pod.babashka.sql/read {:json :parse+keywordize}} ;; default
{:pod.babashka.sql/read {:json :parse}} ;; no keyword keys
{:pod.babashka.sql/read {:json :string}} ;; json as raw string

Use :jsonb to apply these options to jsonb-typed columns.

(db/execute! db ["select json::text from json_table;"])

Libraries

In addition to using a sql pod, the following babashka-compatible libraries might be helpful:

honeysql

Turn Clojure data structures into SQL.

Needs babashka >= 0.2.6. Babashka is tested against HoneySQL version 2.2.861 in CI.

Example:

(ns honeysql-script
  (:require [babashka.deps :as deps]
            [babashka.pods :as pods]))

;; Load HoneySQL from Clojars:
(deps/add-deps '{:deps {com.github.seancorfield/honeysql {:mvn/version "2.2.861"}}})

(require '[honey.sql :as hsql])

(hsql/format {:select [:a :b :c] :from [:foo] :where [:= :a 1]})
;;=> ["SELECT a, b, c FROM foo WHERE a = ?" 1]

Troubleshooting

MS SQL Server support

If you are connecting to SQL Server, you may try connecting like this:

(require '[pod.babashka.mssql :as sql])
(def db {:dbtype "mssql" :host "my-dbhost" :dbname "my_db" :integratedSecurity true})
(sql/execute! db ...)

Using integrated security like this will not work (yet?) - you will get an error:

----- Error --------------------------------------------------------------------
Type:     clojure.lang.ExceptionInfo
Message:  This driver is not configured for integrated authentication. ClientConnectionId:889ad681-4fdf-409c-b12c-9eef93129023

As a workaround, you can use this pod, which connects via a .NET library.

If you are using SQL Server, you may also be interested in this pod, which has a function to reformat/indent your SQL.

Dev

Set POD_DB_TYPE to either hsqldb, postgresql, or oracle.

Build

Run script/compile

Test

Run script/test.

License

Copyright © 2020-2021 Michiel Borkent

Distributed under the EPL License. See LICENSE.

Helidon license HyperSQL license MySQL JDBC license MSSQL JDBC license PostgreSQL JDBC license