SQLingvo #+author: r0man #+LANGUAGE: en

[[https://clojars.org/sqlingvo][https://img.shields.io/clojars/v/sqlingvo.svg]] [[https://github.com/r0man/sqlingvo/actions?query=workflow%3A%22Clojure+CI%22][https://github.com/r0man/sqlingvo/workflows/Clojure%20CI/badge.svg]] [[https://versions.deps.co/r0man/sqlingvo][https://versions.deps.co/r0man/sqlingvo/status.svg]] [[https://versions.deps.co/r0man/sqlingvo][https://versions.deps.co/r0man/sqlingvo/downloads.svg]]

/SQLingvo/ is an embedded [[https://clojure.org][Clojure]] and [[https://github.com/clojure/clojurescript][ClojureScript]] /DSL/ that allows you to build /SQL/ statements within your favorite /LISP/. The /SQL/ statements used by /SQLingvo/ are compatible with the [[https://github.com/seancorfield/next-jdbc][next.jdbc]], [[https://github.com/clojure/java.jdbc][clojure.java.jdbc]], [[https://github.com/funcool/clojure.jdbc][clojure.jdbc]], [[https://github.com/alaisi/postgres.async][postgres.async]] and [[https://github.com/brianc/node-postgres][node-postgres]] libraries.

If you want to execute /SQL/ statements on [[https://nodejs.org][Node.js]], take a look at [[https://github.com/r0man/sqlingvo.node][SQLingvo.node]].

Note: /SQLingvo/ is designed for the [[http://www.postgresql.org/][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.

[[https://xkcd.com/1409][https://imgs.xkcd.com/comics/query.png]]

** Usage

/SQLingvo/ shadows some functions from the =clojure.core= namespace, such as =distinct=, =group-by= and =update= functions. It's recommended to require the =sqlingvo.core= namespace via an alias, such as =sql=.

#+BEGIN_SRC clojure :exports code :results silent (require '[sqlingvo.core :as sql]) #+END_SRC

** 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. The following code defines a database specification using the naming and quoting strategy for /PostgreSQL/.

#+BEGIN_SRC clojure :exports code :results silent (def my-db (sql/db :postgresql)) #+END_SRC

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.

#+BEGIN_SRC clojure :exports both :results verbatim (sql/sql (sql/select my-db [:first-name] (sql/from :people))) #+END_SRC

#+RESULTS: : ["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:

#+BEGIN_SRC clojure :exports code :results silent
  (require '[clojure.string :as str])

  (defn underscore [s]
    (str/replace (name s) "-" "_"))

  (def my-db' (sql/db :postgresql {:sql-name underscore}))
#+END_SRC

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

#+BEGIN_SRC clojure :exports both :results verbatim
  (sql/sql (sql/select my-db' [:first-name]
             (sql/from :people)))
#+END_SRC

#+RESULTS:
: ["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.

You could change the quoting strategy with the following code:

#+BEGIN_SRC clojure :exports code :results silent
  (require '[sqlingvo.util :refer [sql-quote-backtick]])
  (def my-db' (sql/db :postgresql {:sql-quote sql-quote-backtick}))
#+END_SRC

Now the column and table names are quoted with back ticks, instead
of double quotes.

#+BEGIN_SRC clojure :exports both :results verbatim
  (sql/sql (sql/select my-db' [:first-name]
             (sql/from :people)))
#+END_SRC

#+RESULTS:
: ["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.

#+BEGIN_SRC clojure :exports code :results silent
  (require '[sqlingvo.util :refer [sql-placeholder-count]])
  (def my-db' (sql/db :postgresql {:sql-placeholder sql-placeholder-count}))
#+END_SRC

Now, the placeholders for /SQL/ parameters will contain the index
number of the parameter. Use this strategy if you are using
/SQLingvo/ with [[https://github.com/alaisi/postgres.async][postgres.async]].

#+BEGIN_SRC clojure :exports both :results verbatim
  (sql/sql (sql/select my-db'  [:*]
             (sql/from :distributors)
             (sql/where '(and (= :dname "Anvil Distribution")
                              (= :zipcode "21201")))))
#+END_SRC

#+RESULTS:
: ["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:

#+BEGIN_SRC clojure :exports code :results silent (def commendy-films-stmt (sql/select my-db [:id :name] (sql/from :films) (sql/where '(= :kind "Comedy")))) #+END_SRC

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.

#+BEGIN_SRC clojure :exports both :results verbatim (class commendy-films-stmt) #+END_SRC

#+RESULTS: : 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.

#+BEGIN_SRC clojure :exports both :results verbatim (sql/sql commendy-films-stmt) #+END_SRC

#+RESULTS: : ["SELECT "id", "name" FROM "films" WHERE ("kind" = ?)" "Comedy"]

Those vectors could be fed to the [[https://github.com/funcool/clojure.jdbc][clojure.jdbc]] and [[https://github.com/clojure/java.jdbc][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.

#+BEGIN_SRC clojure :exports both :results verbatim (sql/select my-db [:id :name] (sql/from :films) (sql/where '(= :kind "Comedy"))) #+END_SRC

#+RESULTS: : ["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=.

#+BEGIN_SRC clojure :exports both :results verbatim (class *1) #+END_SRC

#+RESULTS: : 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.

#+BEGIN_SRC clojure :exports both :results verbatim (sql/select my-db [1 '(+ 2 (abs 3)) '(upper "Hello")]) #+END_SRC

#+RESULTS: : ["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 ...

#+BEGIN_SRC clojure :exports both :results verbatim (sql/select my-db ['(substring "Fusion" from 2 for 3)]) #+END_SRC

#+RESULTS: : ["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.

#+BEGIN_SRC clojure :exports code :results silent (defn films-by-kind [db kind] (sql/select db [:id :name] (sql/from :films) (sql/where `(= :kind ~kind)))) #+END_SRC

#+BEGIN_SRC clojure :exports both :results verbatim (films-by-kind my-db "Action") #+END_SRC

#+RESULTS: : ["SELECT "id", "name" FROM "films" WHERE ("kind" = ?)" "Action"]

** Detailed SQL examples

The following examples show how to build /SQL/ statements found in the /PostgreSQL/ [[ht

Sqlingvo

Install / Use

README