Skip to content
A Clojure DSL to create SQL statements
Clojure
Latest commit 213904e May 15, 2016 @r0man Version 0.8.13-SNAPSHOT
Failed to load latest commit information.
src/sqlingvo Override deref in pprint May 15, 2016
test/sqlingvo Override deref in pprint May 15, 2016
.gitignore Add expr namespace Nov 30, 2014
.travis.yml Revert "Add database drivers" Dec 8, 2015
LICENSE Add LICENSE Feb 18, 2016
README.org Fix typo Apr 16, 2016
project.clj Version 0.8.13-SNAPSHOT May 15, 2016

README.org

SQLingvo

https://img.shields.io/clojars/v/sqlingvo.svg https://travis-ci.org/r0man/sqlingvo.svg http://jarkeeper.com/r0man/sqlingvo/status.svg https://jarkeeper.com/r0man/sqlingvo/downloads.svg

SQLingvo is a Clojure DSL that can help you build SQL statements programmatically. The resulting SQL statements are compatible with the clojure.jdbc, clojure.java.jdbc and postgres.async libraries. SQLingvo is designed for the PostgreSQL database management system. That said, if you can avoid PostgreSQL specific features you might be lucky and may be able to use it with other databases as well.

https://imgs.xkcd.com/comics/query.png

Usage

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])

Database specification

SQLingvo uses a database specification to configure how SQL identifiers are quoted and column and table names are translated between Clojure and your database. There are helper functions in the sqlingvo.db namespace for common database vendors, like PostgreSQL, MySQL and others. The following code defines a database specification using the naming and quoting strategy for PostgreSQL.

(require '[sqlingvo.db :as db])
(def db (db/postgresql))

Such a database specification is needed by all functions that produce SQL statements. The following code uses the database specification db to build a simple SELECT statement.

(sql (select db [:first-name]
       (from :people)))
["SELECT \"first-name\" FROM \"people\""]

Naming strategy

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 db' (db/postgresql {:sql-name underscore}))

All the hyphens in column and table names are now translated to underscores.

(sql (select db' [:first-name]
       (from :people)))
["SELECT \"first_name\" FROM \"people\""]

Quoting strategy

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. The database specification functions in the sqlingvo.db namespace ship with appropriate defaults for each vendor.

You could change the quoting strategy with the following code:

(require '[sqlingvo.util :refer [sql-quote-backtick]])
(def 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 db' [:first-name]
       (from :people)))
["SELECT `first-name` FROM `people`"]

Placeholder strategy

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 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 db'  [:*]
       (from :distributors)
       (where '(and (= :dname "Anvil Distribution")
                    (= :zipcode "21201")))))
["SELECT * FROM \"distributors\" WHERE ((\"dname\" = $1) and (\"zipcode\" = $2))" "Anvil Distribution" "21201"]

SQL statement

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

Printing in the REPL

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

SQL expressions

SQLingvo compiles SQL expressions from Clojure prefix notation into SQL. There’s built-in support for special operators, such as +, -, *, / and many others.

(select 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 db ['(substring "Fusion" from 2 for 3)])
["SELECT substring(? from 2 for 3)" "Fusion"]

Syntax quoting

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 db "Action")
["SELECT \"id\", \"name\" FROM \"films\" WHERE (\"kind\" = ?)" "Action"]

Detailed SQL examples

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

Copy from standard input.

(copy db :country []
  (from :stdin))
["COPY \"country\" FROM STDIN"]

Copy data from a file into the country table.

(copy 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 db :country [:id :name]
  (from "/usr1/proj/bray/sql/country_data"))
["COPY \"country\" (\"id\", \"name\") FROM ?" "/usr1/proj/bray/sql/country_data"]

Create table

Define a new database table.

(create-table 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())"]

Delete

Clear the table films.

(delete db :films)
["DELETE FROM \"films\""]

Delete all films but musicals.

(delete db :films
  (where '(<> :kind "Musical")))
["DELETE FROM \"films\" WHERE (\"kind\" <> ?)" "Musical"]

Delete completed tasks, returning full details of the deleted rows.

(delete db :tasks
  (where '(= :status "DONE"))
  (returning :*))
["DELETE FROM \"tasks\" WHERE (\"status\" = ?) RETURNING *" "DONE"]

Insert

Insert expressions

Insert expressions into the films table.

(insert 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 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

Insert records into the films table.

(insert 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 returning records

Insert a row into the films table and return the inserted records.

(insert 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 default values

Insert a row consisting entirely of default values.

(insert db :films []
  (values :default))
["INSERT INTO \"films\" DEFAULT VALUES"]

Insert from a select statement

Insert rows into the films table from the tmp-films table with the same column layout as films.

(insert db :films []
  (select 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 rows on conflict

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 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 or do nothing on conflict

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 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"]

Insert or update rows on conflict with condition

Don’t update existing distributors based in a certain ZIP code.

(insert 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"]

Insert or do nothing by constraint

Name a constraint directly in the statement. Uses associated index to arbitrate taking the DO NOTHING action.

(insert 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

Join the weathers table with the cities table.

(select 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 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 db [:*]
  (from :weather)
  (join :cities '(on (= :cities.name :weather.city)) :type :inner))
["SELECT * FROM \"weather\" INNER JOIN \"cities\" ON (\"cities\".\"name\" = \"weather\".\"city\")"]

Select

Select all films.

(select db [:*] (from :films))
["SELECT * FROM \"films\""]

Select all Comedy films.

(select db [:*]
  (from :films)
  (where '(= :kind "Comedy")))
["SELECT * FROM \"films\" WHERE (\"kind\" = ?)" "Comedy"]

Retrieve the most recent weather report for each location.

(select 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"]

Update

Change the word Drama to Dramatic in the kind column of the films table.

(update 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 db :films {:kind '(upper :kind)})
["UPDATE \"films\" SET \"kind\" = upper(\"kind\")"]

Order by

The sort expression(s) can be any expression that would be valid in the query’s select list.

(select 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 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 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"]

Having clause

Groups can be restricted via a HAVING clause.

(select 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)"]

Values

A bare VALUES command.

(values 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 db [(as 1 :column1) (as "one" :column2)])
 (select db [(as 2 :column1) (as "two" :column2)])
 (select 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 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 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 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 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 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"]

With Queries / Common table expressions

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 db [:regional-sales (regional-sales db)
          :top-regions (top-regions db)]
  (select db [:region :product
              (as '(sum :quantity) :product-units)
              (as '(sum :amount) :product-sales)]
    (from :orders)
    (where `(in :region ~(select 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.

License

Copyright © 2012-2016 r0man

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

Something went wrong with that request. Please try again.