Liking cljdoc? Tell your friends :D

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.

Connection pool component

(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 next.jdbc functions.

Usage with Ragtime

Ragtime is a simple migration library, which provides support for SQL migrations via ragtime.jdbc.

(require '[ragtime.repl :as repl]
         '[ragtime.jdbc :as jdbc])

(repl/migrate {:datastore (jdbc/sql-database connection-pool)
               :migrations (jdbc/load-resources "migrations")})

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

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

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.

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

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*

Modes

You can choose the mode of the column to hash key conversion when defining a model namespace:

(sql.model/load-sql-file "app/some/model.sql" {:mode MODE})

Available modes:

:java.jdbc - lower case, unqalified keys
:next.jdbc - maps with qualified keys
kebab-maps - lower case, unqalified keys, kebab-case

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