SQLingvo is an embedded Clojure and ClojureScript DSL that allows you to build SQL statements within your favorite LISP. The SQL statements used by SQLingvo are compatible with the clojure.jdbc, clojure.java.jdbc, postgres.async and node-postgres libraries.
If you want to execute SQL statements on Node.js, take a look at SQLingvo.node.
Note: SQLingvo is designed for the PostgreSQL database management system. That said, if you can avoid PostgreSQL specific features, you might be lucky and use it with other databases as well.
SQLingvo shadows some functions from the clojure.core
namespace. If you want to use or require all symbols from the
sqlingvo.core namespace you have to exclude the distinct,
group-by and update functions.
(refer-clojure :exclude '[distinct group-by update])
(require '[sqlingvo.core :refer :all])SQLingvo uses a database specification to configure how SQL identifiers are quoted and column and table names are translated between Clojure and your database. The following code defines a database specification using the naming and quoting strategy for PostgreSQL.
(def my-db (db :postgresql))Such a database specification is needed by all functions that
produce SQL statements. The following code uses the database
specification my-db to build a simple SELECT statement.
(sql (select my-db [:first-name]
(from :people)))["SELECT \"first-name\" FROM \"people\""]
The naming strategy is used to configure how column and table
names are translated between Clojure and the SQL dialect of the
database. The strategy can be configured with the :sql-name
entry in a database specification.
The default strategy used is clojure.core/name, which translates
a Clojure keyword to a string.
A common use case is to translate from a keyword to a string and replace all hyphens with underscores. This can be done with the following code:
(require '[clojure.string :as str])
(defn underscore [s]
(str/replace (name s) "-" "_"))
(def my-db' (db :postgresql {:sql-name underscore}))All the hyphens in column and table names are now translated to underscores.
(sql (select my-db' [:first-name]
(from :people)))["SELECT \"first_name\" FROM \"people\""]
The quoting strategy defines how column and table names are quoted
when building SQL. The strategy can be configured with the
:sql-quote entry in a database specification.
You could change the quoting strategy with the following code:
(require '[sqlingvo.util :refer [sql-quote-backtick]])
(def my-db' (db :postgresql {:sql-quote sql-quote-backtick}))Now the column and table names are quoted with back ticks, instead of double quotes.
(sql (select my-db' [:first-name]
(from :people)))["SELECT `first-name` FROM `people`"]
The placeholder strategy defines how placeholders for SQL
parameters are generated when building statements. The default
sql-placeholder-constant strategy always uses the string ?,
the sql-placeholder-count strategy uses increasing values
starting from $1, $2, etc.
The strategy can be configured with the :sql-placeholder entry
in a database specification.
(require '[sqlingvo.util :refer [sql-placeholder-count]])
(def my-db' (db :postgresql {:sql-placeholder sql-placeholder-count}))Now, the placeholders for SQL parameters will contain the index number of the parameter. Use this strategy if you are using SQLingvo with postgres.async.
(sql (select my-db' [:*]
(from :distributors)
(where '(and (= :dname "Anvil Distribution")
(= :zipcode "21201")))))["SELECT * FROM \"distributors\" WHERE ((\"dname\" = $1) and (\"zipcode\" = $2))" "Anvil Distribution" "21201"]
SQLingvo comes with functions for common SQL commands like
select, insert, update and more. These functions return an
instance of sqlingvo.expr.Stmt, a data structure that can be
compiled into SQL with the sql function, or used by other
functions to build derived statements.
Here’s an example:
(def commendy-films-stmt
(select my-db [:id :name]
(from :films)
(where '(= :kind "Comedy"))))In the code above we select all the id and name columns of all
rows in the films table that have a kind column with the value
Comedy. The call to the select function returns and instance of
sqlingvo.expr.Stmt, which is bound to the commendy-films-stmt
var.
(class commendy-films-stmt)sqlingvo.expr.Stmt
This instance can be compiled into SQL with the sql
function. The result is a Clojure vector with the first entry
being the compiled SQL string and the remaining entries the
prepared statement parameters.
(sql commendy-films-stmt)["SELECT \"id\", \"name\" FROM \"films\" WHERE (\"kind\" = ?)" "Comedy"]
Those vectors could be fed to the clojure.jdbc and clojure.java.jdbc libraries to actually execute a statement.
There is a print-method defined for the sqlingvo.expr.Stmt
class, so instances of a statement are printed in their compiled
from. This is convenient when building SQL statements in the
REPL. If you type the following example directly into your
REPL, it prints out the compiled form of the statement.
(select my-db [:id :name]
(from :films)
(where '(= :kind "Comedy")))["SELECT \"id\", \"name\" FROM \"films\" WHERE (\"kind\" = ?)" "Comedy"]
But the return value of the call to the select function above is
still an instance of sqlingvo.expr.Stmt.
(class *1)sqlingvo.expr.Stmt
SQLingvo compiles SQL expressions from Clojure prefix
notation into SQL. There’s built-in support for special
operators, such as +, -, *, / and many others.
(select my-db [1 '(+ 2 (abs 3)) '(upper "Hello")])["SELECT 1, (2 + abs(3)), upper(?)" "Hello"]
You can influence the compilation of functions by extending the
compile-fn multi method. In case a function uses a special
compilation rule that is not built in, take a look at the multi
method implementation of substring to see how to create your own
compilation rule. Or even better, send a PR …
(select my-db ['(substring "Fusion" from 2 for 3)])["SELECT substring(? from 2 for 3)" "Fusion"]
When using SQLingvo to build parameterized SQL statements, you
often want to use the parameters in a SQL expression. This can be
accomplished with syntax quoting. Note the back tick character in
the where clause.
(defn films-by-kind [db kind]
(select db [:id :name]
(from :films)
(where `(= :kind ~kind))))(films-by-kind my-db "Action")["SELECT \"id\", \"name\" FROM \"films\" WHERE (\"kind\" = ?)" "Action"]
The following examples show how to build SQL statements found in
the PostgreSQL documentation with SQLingvo. Note that we don’t
call the sql function anymore, because we are only interested in
the printed result.
Copy from standard input.
(copy my-db :country []
(from :stdin))["COPY \"country\" FROM STDIN"]
Copy data from a file into the country table.
(copy my-db :country []
(from "/usr1/proj/bray/sql/country_data"))["COPY \"country\" FROM ?" "/usr1/proj/bray/sql/country_data"]
Copy data from a file into the country table with columns in the given order.
(copy my-db :country [:id :name]
(from "/usr1/proj/bray/sql/country_data"))["COPY \"country\" (\"id\", \"name\") FROM ?" "/usr1/proj/bray/sql/country_data"]
Define a new database table.
(create-table my-db :films
(column :code :char :length 5 :primary-key? true)
(column :title :varchar :length 40 :not-null? true)
(column :did :integer :not-null? true)
(column :date-prod :date)
(column :kind :varchar :length 10)
(column :len :interval)
(column :created-at :timestamp-with-time-zone :not-null? true :default '(now))
(column :updated-at :timestamp-with-time-zone :not-null? true :default '(now)))["CREATE TABLE \"films\" (\"code\" CHAR(5) PRIMARY KEY, \"title\" VARCHAR(40) NOT NULL, \"did\" INTEGER NOT NULL, \"date-prod\" DATE, \"kind\" VARCHAR(10), \"len\" INTERVAL, \"created-at\" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), \"updated-at\" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now())"]
Clear the table films.
(delete my-db :films)["DELETE FROM \"films\""]
Delete all films but musicals.
(delete my-db :films
(where '(<> :kind "Musical")))["DELETE FROM \"films\" WHERE (\"kind\" <> ?)" "Musical"]
Delete completed tasks, returning full details of the deleted rows.
(delete my-db :tasks
(where '(= :status "DONE"))
(returning :*))["DELETE FROM \"tasks\" WHERE (\"status\" = ?) RETURNING *" "DONE"]
Insert expressions into the films table.
(insert my-db :films [:code :title :did :date-prod :kind]
(values [['(upper "t_601") "Yojimbo" 106 "1961-06-16" "Drama"]]))["INSERT INTO \"films\" (\"code\", \"title\", \"did\", \"date-prod\", \"kind\") VALUES (upper(?), ?, 106, ?, ?)" "t_601" "Yojimbo" "1961-06-16" "Drama"]
Insert expressions and default values into the films table.
(insert my-db :films []
(values [["UA502" "Bananas" 105 :DEFAULT "Comedy" "82 minutes"]
["T_601" "Yojimbo" 106 :DEFAULT "Drama" :DEFAULT]]))["INSERT INTO \"films\" VALUES (?, ?, 105, DEFAULT, ?, ?), (?, ?, 106, DEFAULT, ?, DEFAULT)" "UA502" "Bananas" "Comedy" "82 minutes" "T_601" "Yojimbo" "Drama"]
Insert records into the films table.
(insert my-db :films []
(values [{:code "B6717" :title "Tampopo" :did 110 :date-prod "1985-02-10" :kind "Comedy"},
{:code "HG120" :title "The Dinner Game" :did 140 :date-prod "1985-02-10" :kind "Comedy"}]))["INSERT INTO \"films\" (\"code\", \"title\", \"did\", \"date-prod\", \"kind\") VALUES (?, ?, 110, ?, ?), (?, ?, 140, ?, ?)" "B6717" "Tampopo" "1985-02-10" "Comedy" "HG120" "The Dinner Game" "1985-02-10" "Comedy"]
Insert a row into the films table and return the inserted records.
(insert my-db :films []
(values [{:code "T_601" :title "Yojimbo" :did 106 :date-prod "1961-06-16" :kind "Drama"}])
(returning :*))["INSERT INTO \"films\" (\"code\", \"title\", \"did\", \"date-prod\", \"kind\") VALUES (?, ?, 106, ?, ?) RETURNING *" "T_601" "Yojimbo" "1961-06-16" "Drama"]
Insert a row consisting entirely of default values.
(insert my-db :films []
(values :default))["INSERT INTO \"films\" DEFAULT VALUES"]
Insert rows into the films table from the tmp-films table
with the same column layout as films.
(insert my-db :films []
(select my-db [:*]
(from :tmp-films)
(where '(< :date-prod "2004-05-07"))))["INSERT INTO \"films\" SELECT * FROM \"tmp-films\" WHERE (\"date-prod\" < ?)" "2004-05-07"]
Insert or update new distributors as appropriate. Assumes a unique index has been defined that constrains values appearing in the did column. Note that the special excluded table is used to reference values originally proposed for insertion:
(insert my-db :distributors [:did :dname]
(values [{:did 5 :dname "Gizmo Transglobal"}
{:did 6 :dname "Associated Computing, Inc"}])
(on-conflict [:did]
(do-update {:dname :EXCLUDED.dname})))["INSERT INTO \"distributors\" (\"did\", \"dname\") VALUES (5, ?), (6, ?) ON CONFLICT (\"did\") DO UPDATE SET \"dname\" = EXCLUDED.\"dname\"" "Gizmo Transglobal" "Associated Computing, Inc"]
Insert a distributor, or do nothing for rows proposed for insertion when an existing, excluded row (a row with a matching constrained column or columns after before row insert triggers fire) exists. Example assumes a unique index has been defined that constrains values appearing in the did column:
(insert my-db :distributors [:did :dname]
(values [{:did 7 :dname "Redline GmbH"}])
(on-conflict [:did]
(do-nothing)))["INSERT INTO \"distributors\" (\"did\", \"dname\") VALUES (7, ?) ON CONFLICT (\"did\") DO NOTHING" "Redline GmbH"]
Don’t update existing distributors based in a certain ZIP code.
(insert my-db (as :distributors :d) [:did :dname]
(values [{:did 8 :dname "Anvil Distribution"}])
(on-conflict [:did]
(do-update {:dname '(:|| :EXCLUDED.dname " (formerly " :d.dname ")")})
(where '(:<> :d.zipcode "21201"))))["INSERT INTO \"distributors\" AS \"d\" (\"did\", \"dname\") VALUES (8, ?) ON CONFLICT (\"did\") DO UPDATE SET \"dname\" = (EXCLUDED.\"dname\" || ? || \"d\".\"dname\" || ?) WHERE (\"d\".\"zipcode\" <> ?)" "Anvil Distribution" " (formerly " ")" "21201"]
Name a constraint directly in the statement. Uses associated index to arbitrate taking the DO NOTHING action.
(insert my-db :distributors [:did :dname]
(values [{:did 9 :dname "Antwerp Design"}])
(on-conflict-on-constraint :distributors_pkey
(do-nothing)))["INSERT INTO \"distributors\" (\"did\", \"dname\") VALUES (9, ?) ON CONFLICT ON CONSTRAINT \"distributors_pkey\" DO NOTHING" "Antwerp Design"]
Join the weathers table with the cities table.
(select my-db [:*]
(from :weather)
(join :cities.name :weather.city))["SELECT * FROM \"weather\" JOIN \"cities\" ON (\"cities\".\"name\" = \"weather\".\"city\")"]
The code above is a common use case and is syntactic sugar for the following. Use this version if you want to join on an arbitrary SQL expression.
(select my-db [:*]
(from :weather)
(join :cities '(on (= :cities.name :weather.city))))["SELECT * FROM \"weather\" JOIN \"cities\" ON (\"cities\".\"name\" = \"weather\".\"city\")"]
The type of join can be given as a keyword argument.
(select my-db [:*]
(from :weather)
(join :cities '(on (= :cities.name :weather.city)) :type :inner))["SELECT * FROM \"weather\" INNER JOIN \"cities\" ON (\"cities\".\"name\" = \"weather\".\"city\")"]
Select all films.
(select my-db [:*] (from :films))["SELECT * FROM \"films\""]
Select all Comedy films.
(select my-db [:*]
(from :films)
(where '(= :kind "Comedy")))["SELECT * FROM \"films\" WHERE (\"kind\" = ?)" "Comedy"]
Retrieve the most recent weather report for each location.
(select my-db (distinct [:location :time :report] :on [:location])
(from :weather-reports)
(order-by :location (desc :time)))["SELECT DISTINCT ON (\"location\") \"location\", \"time\", \"report\" FROM \"weather-reports\" ORDER BY \"location\", \"time\" DESC"]
Change the word Drama to Dramatic in the kind column of the
films table.
(update my-db :films {:kind "Dramatic"}
(where '(= :kind "Drama")))["UPDATE \"films\" SET \"kind\" = ? WHERE (\"kind\" = ?)" "Dramatic" "Drama"]
Change all the values in the kind column of the table films to
upper case.
(update my-db :films {:kind '(upper :kind)})["UPDATE \"films\" SET \"kind\" = upper(\"kind\")"]
The sort expression(s) can be any expression that would be valid in the query’s select list.
(select my-db [:a :b]
(from :table-1)
(order-by '(+ :a :b) :c))["SELECT \"a\", \"b\" FROM \"table-1\" ORDER BY (\"a\" + \"b\"), \"c\""]
A sort expression can also be the column label
(select my-db [(as '(+ :a :b) :sum) :c]
(from :table-1)
(order-by :sum))["SELECT (\"a\" + \"b\") AS \"sum\", \"c\" FROM \"table-1\" ORDER BY \"sum\""]
or the number of an output column.
(select my-db [:a '(max :b)]
(from :table-1)
(group-by :a)
(order-by 1))["SELECT \"a\", max(\"b\") FROM \"table-1\" GROUP BY \"a\" ORDER BY 1"]
Groups can be restricted via a HAVING clause.
(select my-db [:city '(max :temp-lo)]
(from :weather)
(group-by :city)
(having '(< (max :temp-lo) 40)))["SELECT \"city\", max(\"temp-lo\") FROM \"weather\" GROUP BY \"city\" HAVING (max(\"temp-lo\") < 40)"]
A bare VALUES command.
(values my-db [[1 "one"] [2 "two"] [3 "three"]])["VALUES (1, ?), (2, ?), (3, ?)" "one" "two" "three"]
This will return a table of two columns and three rows. It’s effectively equivalent to.
(union
{:all true}
(select my-db [(as 1 :column1) (as "one" :column2)])
(select my-db [(as 2 :column1) (as "two" :column2)])
(select my-db [(as 3 :column1) (as "three" :column2)]))["SELECT 1 AS \"column1\", ? AS \"column2\" UNION ALL SELECT 2 AS \"column1\", ? AS \"column2\" UNION ALL SELECT 3 AS \"column1\", ? AS \"column2\"" "one" "two" "three"]
More usually, VALUES is used within a larger SQL command. The most common use is in INSERT.
(insert my-db :films []
(values [{:code "T-601"
:title "Yojimbo"
:did 106
:date-prod "1961-06-16"
:kind "Drama"}]))["INSERT INTO \"films\" (\"code\", \"date-prod\", \"did\", \"kind\", \"title\") VALUES (?, ?, 106, ?, ?)" "T-601" "1961-06-16" "Drama" "Yojimbo"]
In the context of INSERT, entries of a VALUES list can be DEFAULT to indicate that the column default should be used here instead of specifying a value.
(insert my-db :films []
(values [["UA502" "Bananas" 105 :DEFAULT "Comedy" "82 minutes"]
["T_601" "Yojimbo" 106 :DEFAULT "Drama" :DEFAULT]]))["INSERT INTO \"films\" VALUES (?, ?, 105, DEFAULT, ?, ?), (?, ?, 106, DEFAULT, ?, DEFAULT)" "UA502" "Bananas" "Comedy" "82 minutes" "T_601" "Yojimbo" "Drama"]
VALUES can also be used where a sub SELECT might be written, for example in a FROM clause:
(select my-db [:f.*]
(from (as :films :f)
(as (values [["MGM" "Horror"] ["UA" "Sci-Fi"]])
:t [:studio :kind]))
(where '(and (= :f.studio :t.studio)
(= :f.kind :t.kind))))["SELECT \"f\".* FROM \"films\" \"f\", (VALUES (?, ?), (?, ?)) AS \"t\" (\"studio\", \"kind\") WHERE ((\"f\".\"studio\" = \"t\".\"studio\") and (\"f\".\"kind\" = \"t\".\"kind\"))" "MGM" "Horror" "UA" "Sci-Fi"]
Note that an AS clause is required when VALUES is used in a FROM clause, just as is true for SELECT. It is not required that the AS clause specify names for all the columns, but it’s good practice to do so. (The default column names for VALUES are column1, column2, etc in PostgreSQL, but these names might be different in other database systems.)
(update my-db :employees
{:salary '(* :salary :v.increase)}
(from (as (values [[1 200000 1.2] [2 400000 1.4]])
:v [:depno :target :increase]))
(where '(and (= :employees.depno :v.depno)
(>= :employees.sales :v.target))))["UPDATE \"employees\" SET \"salary\" = (\"salary\" * \"v\".\"increase\") FROM (VALUES (1, 200000, 1.2), (2, 400000, 1.4)) AS \"v\" (\"depno\", \"target\", \"increase\") WHERE ((\"employees\".\"depno\" = \"v\".\"depno\") and (\"employees\".\"sales\" >= \"v\".\"target\"))"]
When VALUES is used in INSERT, the values are all automatically coerced to the data type of the corresponding destination column. When it’s used in other contexts, it might be necessary to specify the correct data type. If the entries are all quoted literal constants, coercing the first is sufficient to determine the assumed type for all:
(select my-db [:*]
(from :machines)
(where `(in :ip-address
~(values [['(cast "192.168.0.1" :inet)]
["192.168.0.10"]
["192.168.1.43"]]))))["SELECT * FROM \"machines\" WHERE \"ip-address\" IN (VALUES (CAST(? AS inet)), (?), (?))" "192.168.0.1" "192.168.0.10" "192.168.1.43"]
You can compose more complex SQL statements with common table expressions.
Define the regional-sales and top-regions helper functions.
(defn regional-sales [db]
(select db [:region (as '(sum :amount) :total-sales)]
(from :orders)
(group-by :region)))(defn top-regions [db]
(select db [:region]
(from :regional-sales)
(where `(> :total-sales
~(select db ['(/ (sum :total-sales) 10)]
(from :regional-sales))))))And use them in a common table expression.
(with my-db [:regional-sales (regional-sales my-db)
:top-regions (top-regions my-db)]
(select my-db [:region :product
(as '(sum :quantity) :product-units)
(as '(sum :amount) :product-sales)]
(from :orders)
(where `(in :region ~(select my-db [:region]
(from :top-regions))))
(group-by :region :product)))["WITH \"regional-sales\" AS (SELECT \"region\", sum(\"amount\") AS \"total-sales\" FROM \"orders\" GROUP BY \"region\"), \"top-regions\" AS (SELECT \"region\" FROM \"regional-sales\" WHERE (\"total-sales\" > (SELECT (sum(\"total-sales\") / 10) FROM \"regional-sales\"))) SELECT \"region\", \"product\", sum(\"quantity\") AS \"product-units\", sum(\"amount\") AS \"product-sales\" FROM \"orders\" WHERE \"region\" IN (SELECT \"region\" FROM \"top-regions\") GROUP BY \"region\", \"product\""]
For more complex examples, look at the tests.
Copyright © 2012-2016 r0man
Distributed under the Eclipse Public License, the same as Clojure.