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:

• next.jdbc
• 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"]}}

Coercions and JSONB

utility-belt.sql comes with all required dependencies for communication with Postgres (including a connection pool, PG adapter and SQL query interface) as wells as necessary coercion setup for Joda DateTime (via clj-time) JSONB data type. To enable these coercions require utility-belt.sql.conv namespace.

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

1. start test Postgres instance ./script/test-postgres
2. 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.

🙋 Note by default, load-sql-file uses backwards compatible mode, and will use lower-case, unqualified keywords when mapping column names in result sets.

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

By convention, it's best to add * suffix to queries defined in the SQL file, and create corresponding function in the Clojure file loading it. E.g.

SQL file function: get-all* Clojure file function, get-all, calls get-all*

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}

• Afonso Tsukamoto
• Łukasz Korecki
• Marketa Adamova