SQuirreL is a library for creating SQL expressions programmatically. It is not intended to look like SQL, and is probably overkill unless you actually need to dynamically build complex queries.
SQuirreL tries to fulfil three goals:
SQuirreL is designed for the actively building complex queries in code, not for typing out fixed queries.
Testing the query-building code should be easy, without resorting to string matching.
The supported SQL variants should be specified fully and separately, without being hard-coded into the generic library code, and it should be easy to add new variants.
So far SQuirreL supports PostgreSQL only.
Leiningen coordinate:
[com.borkdal/squirrel "0.3.1"]
For PostgreSQL, require the namespace
com.borkdal.squirrel.postgresql
. See the
API documentation
for details,
select
is a good starting point.
(All example queries refer to booktown.sql from Practical PostgreSQL.)
SQuirreL is used like this:
(compile-sql
(select (star)
(table-name "books")))
=> "select * from books"
Or like this:
(compile-sql
(select (column "id")
(column "title")
(table-name "books")))
=> "select id, title from books"
A more complex expression:
(compile-sql
(select
(column "books.id")
(column "title")
(column "authors.id")
(column "last_name")
(table-name "books")
(table-name "authors")
(where
(compare-equals "books.author_id"
"authors.id"))
(order-by "books.id")))
=> (str "select books.id, title, authors.id, last_name from books, authors"
" where (books.author_id = authors.id) order by books.id")
Assume that want to build an API for generating aggregates from a database. As part of that we need a function that takes parameters specifying the aggregates, and that creates an SQL expression to get this data from the database.
The example is again based on booktown.sql.
We want to write a function that takes three parameters, according to
[& {:keys [fields aggregate aggregate-field]}]
.
fields
: This should be a vector of the fields to include in the
result set - they can be selected from the set #{:date :month :title :publication :last-name :first-name :subject}
.aggregate
: This is the aggregate to use - it can be selected from
#{:count :min :max}
.aggregate-field
: This specifies the aggregation field. It can be
nil
, or selected from the fields above.So, for instance, calling (build-query :fields [:date :title] :aggregate :count)
should count the number of books shipped per title
per date.
The resulting query will look like this:
select
date(shipments.ship_date),
books.title,
count(*)
from
shipments,
books,
editions,
customers,
authors,
subjects
where
((customers.id = shipments.customer_id)
and (books.author_id = authors.id)
and (books.id = editions.book_id)
and (shipments.isbn = editions.isbn)
and (books.subject_id = subjects.id))
group by
date(shipments.ship_date),
books.title
To start this off, we need to specify the tables we are queries. We therefore create a list of table names:
(def ^:const ^:private tables
["shipments"
"books"
"editions"
"customers"
"authors"
"subjects"])
The table expression entities can now be created:
(defn- get-table-expressions
[]
(map #(table-expression (table-name %)) tables))
We can easily test this code (using midje):
(facts "table expressions"
(let [table-expressions (get-table-expressions)]
(fact "should be sequence"
table-expressions => seq?)
(fact "should have six table-expression entities"
table-expressions => (six-of table-expression?))
(facts "table names"
(let [table-names (map :name table-expressions)]
(fact "should have table-name entities"
table-names => (six-of table-name?))
(fact "should have the correct table names"
(map :string table-names) => (just ["books"
"editions"
"subjects"
"shipments"
"customers"
"authors"] :in-any-order))))))
For the sake of demonstration, these tests are a bit more elaborate the normal, but they show how easy it is to make sure that the query components are correct. Notice how this is all done using the entities directly, all without matching strings in the final SQL expression.
The next step is to generate the join conditions using the where entity.
Like before, we start defining the column names we are joining:
(def ^:const ^:private join-conditions
{"shipments.isbn" "editions.isbn"
"books.id" "editions.book_id"
"customers.id" "shipments.customer_id"
"books.author_id" "authors.id"
"books.subject_id" "subjects.id"})
We can now easily create the compare-equals entities:
(defn- get-join-conditions
[]
(where
(map #(compare-equals (first %) (second %))
join-conditions)))
Again, testing is easy:
(facts "join conditions"
(let [where (get-join-conditions)]
(fact "should be where"
where => where?)
(facts "compare-equals"
(let [compare-equals (:conditions where)]
(fact "should have compare-equals entities"
compare-equals => (five-of compare-equals?))
(facts "expressions"
(let [expressions (map :expressions compare-equals)]
(fact "should be sequence"
expressions => seq?)
(fact "should have five vectors"
expressions => (five-of vector?))
(fact "should have the correct field names"
expressions => (just [["books.id" "editions.book_id"]
["books.author_id" "authors.id"]
["books.subject_id" "subjects.id"]
["customers.id" "shipments.customer_id"]
["shipments.isbn" "editions.isbn"]] :in-any-order))))))))
So far, everything we have done has been independent of the parameters.
For the columns specification, we will have to consider the function parameters.
First, we define the relationship between the keywords and the actual column names:
(def ^:const ^:private field-names-mapping
{:date "date(shipments.ship_date)"
:month "date_trunc('month', shipments.ship_date)"
:title "books.title"
:publication "editions.publication"
:last-name "authors.last_name"
:first-name "authors.first_name"
:subject "subjects.subject"})
Then we can create the actual column entities:
(defn- get-columns
[fields]
(map #(column (field-names-mapping %)) fields))
And for the tests:
(facts "columns"
(let [columns (get-columns [:date :title :subject])]
(fact "should be sequence"
columns => seq?)
(fact "should have column entities"
columns => (three-of column?))
(facts "expressions"
(let [expressions (map :expression columns)]
(fact "should be sequence"
expressions => seq?)
(fact "should be strings"
expressions => (three-of string?))
(fact "should be correct field names"
expressions => (just ["date(shipments.ship_date)"
"books.title"
"subjects.subject"]))))))
We now need to create the column for the aggregate, first by mapping the keywords to the actual functions:
(def ^:const ^:private aggregator-mapping
{:count "count"
:min "min"
:max "max"})
Then we can create a new column entity:
(defn- get-aggregate-column
[aggregate
aggregate-field]
(column
(function-call
(function-name
(aggregator-mapping aggregate))
(if aggregate-field
(field-names-mapping aggregate-field)
(star)))))
With lots and lots of tests:
(facts "aggregate column"
(facts "count"
(let [column (get-aggregate-column :count nil)]
(fact "should be column"
column => column?)
(facts "expression"
(let [call (:expression column)]
(fact "should be a function call"
call => function-call?)
(facts "function name"
(let [function-name (:function-name call)]
(fact "should have a function name"
function-name => function-name?)
(fact "should be calling count"
(:string function-name) => "count")))
(fact "should have star"
(:star call) => truthy)))))
(facts "minimum publication date"
(let [column (get-aggregate-column :min :publication)]
(fact "should be column"
column => column?)
(facts "expression"
(let [call (:expression column)]
(fact "should be a function call"
call => function-call?)
(facts "function name"
(let [function-name (:function-name call)]
(fact "should have a function name"
function-name => function-name?)
(fact "should be calling min"
(:string function-name) => "min")))
(fact "parameters"
(let [parameters (:parameters call)]
(fact "should have a single string"
parameters => (one-of string?))
(fact "should be publication field"
parameters => (just ["editions.publication"])))))))))
Finally, the group-by clauses.
This is now simple, since all the non-aggregate columns should be included:
(defn- get-group-bys
[fields]
(map #(group (field-names-mapping %)) fields))
With more tests:
(facts "group-by"
(let [group-by (get-group-bys [:date :title])]
(fact "should be sequence"
group-by => seq?)
(fact "should have two group entities"
group-by => (two-of group?))
(facts "expressions"
(let [fields (map :expression group-by)]
(fact "should have two strings"
fields => (two-of string?))
(fact "should have correct fields"
fields => (just ["date(shipments.ship_date)"
"books.title"]))))))
We can now use the above to write the actual build-query
function:
(defn build-query
[&
{:keys [fields aggregate aggregate-field]
:or {fields [:date :title]
aggregate :count
aggregate-field nil}}]
(-> (select)
(add (get-table-expressions))
(add (get-columns fields))
(add (get-join-conditions))
(add (get-aggregate-column aggregate aggregate-field))
(add (get-group-bys fields))))
With more tests:
(facts "build-query"
(let [query (build-query :fields [:date :title] :aggregate :count)]
(fact "should be select entity"
query => select?)
(fact "should have three columns"
(:columns query) => (three-of column?))
(fact "should have six table expressions"
(:from-items query) => (six-of table-expression?))
(fact "should have a single where"
(:wheres query) => (one-of where?))
(facts "where"
(let [where (first (:wheres query))]
(fact "should have five compare-equals"
(:conditions where) => (five-of compare-equals?))))
(fact "should have two group-bys"
(:groups query) => (two-of group?))))
The number of books shipped per day:
(compile-sql
(build-query :fields [:date :title] :aggregate :count))
=> (str "select date(shipments.ship_date), books.title, count(*)"
" from shipments, books, editions, customers, authors, subjects"
" where ((books.author_id = authors.id)"
" and (books.id = editions.book_id)"
" and (books.subject_id = subjects.id)"
" and (customers.id = shipments.customer_id)"
" and (shipments.isbn = editions.isbn))"
" group by date(shipments.ship_date), books.title")
The earliest publication for books shipped per day:
(compile-sql
(build-query :fields [:date] :aggregate :min :aggregate-field :publication))
=> (str "select date(shipments.ship_date), min(editions.publication)"
" from shipments, books, editions, customers, authors, subjects"
" where ((books.author_id = authors.id)"
" and (books.id = editions.book_id)"
" and (books.subject_id = subjects.id)"
" and (customers.id = shipments.customer_id)"
" and (shipments.isbn = editions.isbn))"
" group by date(shipments.ship_date)")
The total number of books shipped per author:
(compile-sql
(build-query :fields [:last-name :first-name] :aggregate :count))
=> (str "select authors.last_name, authors.first_name, count(*)"
" from shipments, books, editions, customers, authors, subjects"
" where ((books.author_id = authors.id)"
" and (books.id = editions.book_id)"
" and (books.subject_id = subjects.id)"
" and (customers.id = shipments.customer_id)"
" and (shipments.isbn = editions.isbn))"
" group by authors.last_name, authors.first_name")
The number of books shipped per author per date:
(compile-sql
(build-query :fields [:date :last-name :first-name] :aggregate :count))
=> (str "select date(shipments.ship_date), authors.last_name, authors.first_name, count(*)"
" from shipments, books, editions, customers, authors, subjects"
" where ((books.author_id = authors.id)"
" and (books.id = editions.book_id)"
" and (books.subject_id = subjects.id)"
" and (customers.id = shipments.customer_id)"
" and (shipments.isbn = editions.isbn))"
" group by date(shipments.ship_date), authors.last_name, authors.first_name")
The number of books shipped per subject per month:
(compile-sql
(build-query :fields [:month :subject] :aggregate :count))
=> (str "select date_trunc('month', shipments.ship_date), subjects.subject, count(*)"
" from shipments, books, editions, customers, authors, subjects"
" where ((books.author_id = authors.id)"
" and (books.id = editions.book_id)"
" and (books.subject_id = subjects.id)"
" and (customers.id = shipments.customer_id)"
" and (shipments.isbn = editions.isbn))"
" group by date_trunc('month', shipments.ship_date), subjects.subject")
The last shipping date per title:
(compile-sql
(build-query :fields [:title :last-name :first-name] :aggregate :max :aggregate-field :date))
=> (str "select books.title, authors.last_name, authors.first_name,"
" max(date(shipments.ship_date))"
" from shipments, books, editions, customers, authors, subjects"
" where ((books.author_id = authors.id)"
" and (books.id = editions.book_id)"
" and (books.subject_id = subjects.id)"
" and (customers.id = shipments.customer_id)"
" and (shipments.isbn = editions.isbn))"
" group by books.title, authors.last_name, authors.first_name")
There are two main styles of writing tests.
The first is to inspect the individual parts of the entities, like shown in the examples above.
The other is to compare entities directly, like this:
(fact "direct comparisons"
(get-group-bys [:date :title])
=> [(group "date(shipments.ship_date)")
(group "books.title")])
The choice depends on context and personal preferences.
In order to see the available entities, start with select and follow the links.
All entities follow the same pattern, so we will use Column as an example.
The following are available:
Use like this:
(compile-sql
(column "title")) => "title"
The function make-column, also used to created entities - unless you really need a function you should probably use the entity function above instead.
The function column?, used to check if an entity is a Column.
The SQL variants are specified using a DSL defined by entity.clj.
This example from
language_def.clj
defined the Column
entity, building on Expression
and
ColumnAlias
entities:
(entity/def-entity [Column [[:single Expression expression]
[:single ColumnAlias alias]]]
(utils/spaced-str
(defs/compile-sql expression)
(when (seq alias)
(utils/spaced-str
"as"
(defs/compile-sql alias)))))
language_def.clj is basically a reflection of the PostgreSQL specification.
Check the documentation for more information on how the DSL works, specifically com.borkdal.squirrel.entity.
Can you improve this documentation?Edit on GitHub
cljdoc is a website building & hosting documentation for Clojure/Script libraries
× close