Documentation

Here you will always find the most recent documentation for the ClojureQL project.

General

ClojureQL is designed to have a consistent uniform API. The following rules are always guaranteed:

• All functions which work on tables return a new table. This allows you to compound multiple operations.
• Any function which name ends in a bang (!) forces execution of the query
• All functions take the table as their first argument, allowing use of the -> threading operator
• In all places where you specificy columns can you use both keywords and strings, however certain compiler optimizations are only available when using keywords.

To execute a query you must dereference it. Here are two equivalent examples of dereferencing:

@(table db :t1)

(deref (table db :t1))

Column specification syntax

ClojureQL uses a bit of syntax to keep your queries concise and elegant. Below are example conversions of keywords to SQL column specifications. (note: It is not necessary to learn this syntax as strings can be used instead, however it is recommended)

Connection specification

Example

(def db
 {:classname   "com.mysql.jdbc.Driver"
  :subprotocol "mysql"
  :user              "cql"
  :password     "cql"
  :subname      "//localhost:3306/cql"})

Complete specification

DriverManager:
  :classname    (required) a String, the jdbc driver class name
  :subprotocol (required) a String, the jdbc subprotocol
  :subname      (required) a String, the jdbc subname
  (others)         (optional) passed to the driver as properties.

DataSource:
  :datasource  (optional) a javax.sql.DataSource
  :username    (optional) a String
  :password    (optional) a String, required if :username is supplied

JNDI:
  :name             (optional) a String or javax.naming.Name
  :environment (optional) a java.util.Map"

Options:
  :auto-commit (optional) a Boolean
  :fetch-size      (optional) an integer

Public functions for composing queries

table (args: table-name, or: connection-info, table-name)

Creates a table object optionally associated with a connection specification. This can be either a keyword from a previous call to (open-global) or a hash-map.

select (args: this, predicate)

Confines the query to rows for which the predicate is true

Ex. (select (table :users) (where (= :id 5)))

project (args: this, fields)

Confines the query to the fieldlist supplied in fields

Ex. (project (table :users) [:email])

take (args: table, n)

Limits the query to n number of rows

drop (args: table, n)

Skips n number of rows

sort (args: table, spec)

Sorts a table as per the definition given in spec.

Ex. (sort (table :t1) [:id :rank]) ; implicitly ASC

 (sort (table :t2) [:id#asc :rank#desc])

distinct (args: table)

Makes the query distinct

join (args: this, table2, join_on)

Joins two tables on join_on

Ex. (join (table :one) (table :two) :id) (join (table :one) (table :two) (where (= :one.col :two.col)))

outer-join (args: this, table2, type, join_on)

Joins two tables on join_on and sets the direction of the join. type can be :right, :left, :full etc. Backend support may vary.

Ex. (outer-join (table :one) (table :two) :left :id) (outer-join (table :one) (table :two) :left (where (= :one.id :two.id)))

rename (args: this, newnames)

Renames colums when joining. Newnames is a map of replacement pairs

Ex. (-> (join (table :one) (table :two) :id) (project [:id]) (rename {:one.id :idx}))

aggregate (args: either (this, aggregates) or (this aggregates, group-by))

Selects aggregates from a table. Aggregates are denoted with the :function/field syntax. They can be aliased by supplying a vector [:function/field :as :myfn]. Optionally accepts a group-by argument

Ex. (-> (table :one) (aggregate [[:count/* :as :cnt]] [:id]))

modify (args: this, modifiers)

Allows for arbitrary modifiers to be applied on the result. Can either be called directly or via helper interfaces like 'distinct'.

Ex. (-> (table :one) (modify \"TOP 5\")) ; MSSqls special LIMIT syntax (-> (table :one) distinct)

difference (args: this, relations, opts)

Selects the difference between tables. Mode can take a keyword which can be anything which your backend supports. Commonly :all is used to allow duplicate rows.

Ex. (-> (table :one) (difference (table :two) :all))

intersection (args: this, relations, opts)

Selects the intersection between tables. Mode can take a keyword which can be anything which your backend supports. Commonly :all is used to allow duplicate rows.

Ex. (-> (table :one) (intersection (table :two) :all))

union (args: this, relations, opts)

Selects the union between tables. Mode can take a keyword which can be anything which your backend supports. Commonly :all is used to allow duplicate rows.

Ex. (-> (table :one) (union (table :two) :all))

Table manipulation functions

conj! (args: this, records)

Inserts record(s) into the table

Ex. (conj! (table :one) {:age 22}) (conj! (table :one) [{:age 22} {:age 23}])

disj! (args: this, predicate)

Deletes record(s) from the table

Ex. (disj! (table :one) (where (= :age 22)))

update-in! (args: this, pred, records)

Inserts or updates record(s) where pred is true. Accepts records as both maps and collections.

Ex. (update-in! (table :one) (where (= :id 5)) {:age 22})

Helper functions

pick! (args: table, keyword)

For queries where you know only a single result will be returned, pick! calls the keyword on that result. You can supply multiple keywords in a collection. Returns nil for no-hits, throws an exception on multiple hits.

Ex. (-> (table :users) (select (where (= :id 5))) ; We know this will only match 1 row
(pick! :email))

where (args: clause)

Constructs a where-clause for queries.

Ex. (where (or (< :a 2) (>= :b 4))) => \"((a < ?) OR (b >= ?))\"

If you call str on the result, you'll get the above. If you call (:env) you will see the captured environment

Use as: (select tble (where ...))

with-results (args: [results, table])

Executes the body, wherein the results of the query can be accessed via the name supplies as results.

Ex. (with-results [res table] (println res))"