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.)
The following installation methods are available:
-
Use the latest version from the pod-registry. This is done by calling
load-podwith a qualified keyword:(require '[babashka.pods :as pods]) (pods/load-pod 'org.babashka/postgresql "0.1.0")
Babashka will automatically download the pod if it is not available on your system yet.
-
Download a binary from Github releases
Pods from this repo require babashka v0.4.3 or later.
The pods expose these namespaces with vars, where <db> must be substituted with
the database type, either hsqldb, postgresql, or oracle:
pod.babashka.<db>:execute!: similar tonext.jdbc/execute!execute-one!: similar tonext.jdbc/execute-one!get-connection: returns connection serialized using maps with a unique identifier keyclose-connection: closes a connection returned fromget-connectionwith-transaction: similar tonext.jdbc/with-transaction
pod.babashka.<db>.sql:insert-multi!: similar tonext.jdbc.sql/insert-multi!
pod.babashka.<db>.transaction:begin: marks the begin of a transaction, expects connection returned fromget-connectionrollback: rolls back transaction, expects connection returned fromget-connectioncommit: commits transaction, expects connection returned fromget-connection
More functions from next.jdbc can be added. PRs welcome.
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.
Inserting arrays works automatically: just pass a Java array, with e.g. (into-array [1 2 3]).
Array columns automatically get converted to Clojure vectors. Converting back into arrays must be done manually.
This section only applies to PostgreSQL for now, but can be extended to other databases.
- Convert to a JSON string manually and insert with
?::text. PostgreSQL will automatically convert the parameter to the right type. Full example:
(db/execute! db ["insert into json_table values (?::text);" (json/generate-string {:a 1})])- Use
db/write-jsonordb/write-jsonb. Full example:
(db/execute! db ["insert into json_table values (?);" (db/write-json {:a 1})])- Both json and jsonb are automatically converted to Clojure values. Keys are keywordized automatically. You can override this behavior by passing a
:pod.babashka.sql/readoption toexecute!:
{: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 stringUse :jsonb to apply these options to jsonb-typed columns.
- Select column as text and deserialize the result manually:
(db/execute! db ["select json::text from json_table;"])In addition to using a sql pod, the following babashka-compatible libraries might be helpful:
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]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.
Set POD_DB_TYPE to either hsqldb, postgresql, or oracle.
Run script/compile
Run script/test.
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
