# 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.