utility-belt.sql
A set of utilities for working with JDBC (SQL) data stores
Consist of:
- a connection pool component
- set of utils extending JDBC protocols for reading/writing pg values (mostly JSON and datetimes)
- helpers for writing SQL queries
Built on top of:
- Cheshire for JSON
- HugSQL for SQL queries
- clj-time for datetime handling
- hikari-cp and the fantastic hikari-cp Clojure wrapper for the connection pooling functionality
Mostly used with Postgres and H2, but should work with anything that's supported by JDBC.
Usage
(def db-pool
(component/start (utility-belt.sql.component.connection-pool/create config))
Then use can use the running component as an argument passed to HugSQL functions or as the connection to clojure.tools.jdbc
Configuration
Production
Postgres configuration, with Aero:
{ :pg {:auto-commit true
:pool-name "my-cool-service"
:adapter "postgresql"
:username #or [#env PG_USER "service_name"]
:password #or [#env PG_PASSWORD "password"]
:server-name #or [#env PG_HOST "127.0.0.1"]
:port-number #long #or [#env PG_PORT "5432"]
:maximum-pool-size #long #or [#env PG_MAX_POOL_SIZE 2]
:database-name #or [#env PG_NAME "service_name"]}}
Note: utility-belt.sql
comes with all required dependencies for communication with Postgres (including a connection pool, PG adapter and SQL query interface).
Tests
Using H2 for in-memory or file based DB:
{:adapter "h2" :url "jdbc:h2:mem:"}
Dependencies, in :dev
Lein profile:
:profiles {:dev {:resource-paths ["dev-resources"]
:dependencies [[com.h2database/h2 "1.4.196"]]}}
Running utility-belt.sql test locally
- start test Postgres instance
./script/test-postgres
- run tests via
lein test
or in the repl via your test runner of choice
Defining SQL queries and functions
We're using HugSQL for defining SQL queries and turning them into functions.
utility-belt.sql.model
namespace provides a helper which makes it easy to load these SQL files:
file: some.model.sql
-- :name get-all*
select * from some_table where team_uuid = :team-uuid
file: some_model.clj
(ns some.model
:require [utility-belt.sql.model :as sql.model]))
(sql.model/load-sql-file "app/some/model.sql")
;; will pull in `get-all*` into current ns
Debugging queries
HugSQL has a handy functionality of creating functions returning
query vectors (can be used with clojure.java.jdbc
!). They are also
useful for debugging:
(require '[utility-belt.sql.model :as sm])
(sm/load-sql-file-vec-fns "some.file.sql")
;; now, if some.file.sql defined get-all* fn we can do:
(get-all*-sqlvec {:team-uuid "abcdef"})
;; and it will return
[ "SELECT * from some_table where team_uuid = ? ", "abcdef"]
Authors
In alphabetical order