path: root/README.md

# clojure-sql

`clojure-sql` is a DSL for the creation of SQL queries in [Clojure][1]
code. It has a strong emphasis on *composability* and *abstraction*.

[1]: http://clojure.org/

## Release

Latest release: `0.1.0`

[Leiningen][2] dependency information:

    :::clojure
    [clojure-sql "0.1.0"]

[2]: https://github.com/technomancy/leiningen

## Example Usage

    :::clojure
    (require '[clojure-sql.core :as s])

    (s/table :users)
    ;; => ["SELECT * FROM \"users\" AS \"users1907\""]

    (-> (s/table :users)
        (s/project [:id :username]))
    ;; => ["SELECT \"users1910\".\"id\" AS \"id\", \"users1910\".\"username\" AS \"username\" FROM \"users\" AS \"users1910\""]

    (require '[clojure-sql.jdbc :as jdbc])
    (jdbc/use-jdbc! "postgres://user:pass@localhost:5432/db")

    (-> (s/table :users)
        (s/project [:id :username])
        deref)
    ;; => [{:id 5, :username "username"}]

## Relational Algebra

The constructs and operations available in `clojure-sql` are designed
around those of [Relational Algebra][3]. The majority of operations
take one (or more) queries as arguments and will return a new query as
a result. In this way any transformation can be applied to any query
in a manner which can be easily reasoned about.

A basic query on a table is constructed with the `table` function:
`(table :tablename)`.

[3]: http://en.wikipedia.org/wiki/Relational_Algebra

### Operations

The primary operations available in `clojure-sql` are the following:

* `select`ion: selecting rows which match a condition

        :::clojure
        (s/select (s/table :users) `(= :username "mange"))

    The second argument is a `clojure-sql` expression. This is expressed
    as a quoted Clojure form where the following two assumptions are made:

    * Clojure keywords represent *column names*
    * Clojure symbols represent *SQL functions* and *SQL operators*

    Some more examples of `clojure-sql` expressions and their
    "equivalent" SQL:

        :::clojure
        (let [user-id 5] `(= :user-id ~user-id))
        ;; => "(\"user-id\" = 5)"
        `(and (= :user-id 5) (= :username "mange"))
        ;; => "((\"user-id\" = 5) AND (\"username\" = 'mange'))"
        `(or (= :user-id 10) (= :username "mange"))
        ;; => "((\"user-id\" = 10) OR (\"username\" = 'mange'))"
        `(and (= :name "Barry") (= (length :username) 10))
        ;; => "((\"name\" = 'Barry') AND (\"length\"(\"username\") = 10))

* `project`ion: setting the fields which are exposed by the query

        :::clojure
        (s/project (s/table :users) [:id :username])
        (s/project (s/table :users) {:id :uid, :username :uname}) ;; project and rename in one operation

* `rename`ing: giving an existing field (or fields) a new name

        :::clojure
        (-> (s/table :users)
            (s/project [:id :username])
            (s/rename {:id :uid}))

    Rename can also be given a function which will do the
    transformation. This can be particularly handy when combined with
    the helper functions `as-subobject` and `prefix-names`:

        :::clojure
        (-> (s/table :users)
            (s/project [:id :username])
            (s/rename (s/as-subobject :user)))

    In the final result map fields with a dot will be transformed into
    nested maps. `as-subobject` will put all fields from a query into
    a specified key in the result map. As an example, the above query
    will result in a result map like:

        :::clojure
        {:user {:id 5, :username "mange"}}

* `join`ing: combine two queries into one query

        :::clojure
        (s/join (-> (s/table :users)
                    (s/project [:id :username :person-id]))
                (-> (s/table :people)
                    (s/project [:id :name])
                    (s/rename (s/as-subobject :person)))
                :on `(= :person.id :person-id))

    If a join between two queries has common fields then it is assumed
    to be a natural join (that is: an inner join on common fields).

        :::clojure
        (s/join (-> (s/table :users)
                    (s/project {:id :id, :username :username, :person :person.id}))
                (-> (s/table :people)
                    (s/project [:id :name])
                    (s/rename (s/as-subobject :person))))

    With joins the composability of `clojure-sql` becomes much more
    useful:

        :::clojure
        (def users (-> (s/table :users)
                       (s/project [:id :username :person])))

        (def people (-> (s/table :people)
                        (s/project [:id :name])))

        (s/join (s/rename users (s/as-subobject :user))
                (s/rename people (s/as-subobject :person))
                :on `(= :user.person :person.id))

#### Set operations

At present `clojure-sql` supports two set operations. These operations
have a very strict requirement that all the sets being operated on
must expose *exactly the same* fields.

* `union`ing
* `intersection`

#### Extra-relational operations

A number of extra-relational operations are also available. They are
equally composable as the above relational operators, but they may
introduce subqueries under some circumstances. (The docstrings should
explain those circumstances for each function.)

These operations will not be explained in detail here, but are
hopefully self explanatory.

* `group`ing
* `sort`ing
* `take`ing/`drop`ing

## Database support

`clojure-sql` aims to have an extensible compiler. Compilation of
queries is performed by multimethods which dispatch on an arbitrary
(and otherwise unused) `db` parameter. This allows the compilation of
queries to be special-cased per database. By default `clojure-sql`
will produce SQL for PostgreSQL (work on other databases may be
undertaken in future).

### clojure.java.jdbc

[`clojure.java.jdbc`][4] support is provided, but must be included
explicitly.

    :::clojure
    (require '[clojure-sql.jdbc :as jdbc])

    (jdbc/use-jdbc! "postgres://user:pass@localhost:5432/db")
    ;; => nil

    (deref (-> (s/table :users)
               (s/project [:id :username])))
    ;; => [{:id 5, :username "username"}]

Results are returned from queries in an eager vector.

[4]: https://github.com/clojure/java.jdbc

### Other query executors

If `clojure.java.jdbc` support is not what you're looking for (or if
you're looking for more control of how `clojure.java.jdbc` is
executed) then you can register a query executor with
`clojure-sql.core/set-query-executor!`. A query executor is a function
which is used to run a query when it is executed.

When a query is executed the executor is passed two things: the type
of query being executed, and the compiled query (as vector of
`[query-string & query-args]`). The query executor's return value will
be the return value of the associated query function call (`deref`,
`insert!`, `update!` or `delete!`).

## Changelog

### 0.2.0

* **Breaking:** `group` has changed significantly. It now takes a third
  argument which is an 'auxiliary projection'. The fields in the
  resulting query will be the union of the grouped fields and the
  projection (with the projection taking precedence). Each value in
  the projection must be, in some way, an aggregate of the grouped
  values (this is not, and cannot really be, enforced, but we try to
  warn you if you're obviously wrong).

        :::clojure
        (-> (q/table :users)
            (q/project [:age :name])
            (q/group [:age] {'(string_agg :name ",") :names}))
        ;=> ["SELECT \"users1785\".\"age\" AS \"age\", (\"string_agg\"(\"users1785\".\"name\",?)) AS \"names\" FROM \"users\" AS \"users1785\" GROUP BY \"users1785\".\"age\"" ","]

        (-> (q/table :users)
            (q/project [:age :name])
            (q/group [:age] {:name :name}))
        ;=> Exception! Expr is not a function application - could not possible be an aggregate

* **Breaking:** remove `having`, use `select` instead now

* **Breaking:** make query executors query local, rather than
  global. This changed two things in particular:

    * `table` now takes a second, optional, argument representing a
      query executor

    * `set-query-executor!` is no longer present, use
      `set-default-query-executor!` instead (which will be used
      whenever `table`'s optional argument is omitted)

      As a result of this, the `use-jdbc!` function has been
      removed. To connect to a database via jdbc use this:
      `(q/set-default-query-executor! (clojure-sql.jdbc/jdbc-executor "connection-string"))`

* `sort` can now sort on arbitrary expressions, not just fields

* `union` and `intersection` queries will now always introduce a
  subquery, but they should now compose properly (previously they
  would only join correctly)

* Provide `as-subobject` to help with renaming things with the dot
  notation

        :::clojure
        (-> (table :users)
            (project [:id :username])
            (rename (as-subobject :user))
            deref)
        ;; => {:user {:id 5 :username "username"}}

* Provide `distinct` to make a query only return unique rows


### 0.1.0

* Initial release, so everything's new

## License

Copyright © 2013 Carlo Zancanaro

Distributed under the Eclipse Public License, the same as Clojure.