#doozer API

#doozer: composable SQL queries for Janet

This software is currently Alpha quality and in heavy development!

Doozer is a library, inspired by Elixir's Ecto, which provides the ability to write SQL queries using a convenient, idiomatic Janet DSL, which are highly composable---that is, doozer produces data structures which should be easy to combine in order to create new SQL queries.

Currently supported features:

• SELECT
• WHERE
• JOIN
• Subqueries
• SQL function calls
• Raw SQL fragments

Currently supported backends:

• SQLite
• PostgresQL

#Usage

The basic flow of a doozer query is:

1. Construct a query out of one or more calls to the from macro
2. Render that into concrete SQL using the function to-sql
3. Evaluate the SQL using the library associated with a specific database

For instance, here's an entire session using SQLite:

(use doozer)
(import sqlite3)

(def db (sqlite3/open "chinook.db"))

(def q (from "artists" a :select [name]))
(def sql-and-params (to-sql q))

(sqlite3/eval ;sql-and-params)

#Config

Doozer has multiple backends available, which will output SQL compatible with a specific SQL database.

To configure doozer, set the dynamic variable :doozer/sql-backend to point to the correct value.

Available backends:

• doozer/backends/sqlite/backend
• doozer/backends/postgres/backend

Backend modules also expose a config! convenience function which will set the variable for you. For instance:

(import doozer/backends/sqlite)
(sqlite/config!)

Doozer queries are agnostic of any backend; however, doozer/to-sql will error if no backend is set.

#doozer/backends/postgres

, backend, config, config!

SQL backend for PostgresQL.

#backend

table | source

@{:config @{:sql-param-type :positional} :quote-element <function quote-element>}

The backend for emitting PostgresQL source.

#config

table | source

@{:sql-param-type :positional}

#config!

function | source

(config!)

#doozer/backends/sql

backend

#backend

table | source

@{:assemble-base <function assemble-base> :assemble-join-clauses <function assemble-join-clauses> :assemble-select-clauses <function assemble-select-clauses> :assemble-where-clauses <function assemble-where-clauses> :render-apply <function render-apply> :render-as <function render-as> :render-element <function render-expression-element> :render-function-arguments <function function-arguments> :render-join <function render-join> :render-limit <function render-limit> :render-operation <function render-operation> :render-subquery <function render-subquery>}

The generic doozer backend for all SQL dialects. To be used as a prototype to be overridden by implementation-specific backends (SQLite, PostgresQL, etc.)

#doozer/backends/sqlite

, backend, config!

SQL backend for SQLite.

#backend

table | source

@{:quote-element <function quote-element>}

The backend for emitting SQLite3-compatible source.

#config!

function | source

(config!)

#doozer/common

make-alias, operators

#make-alias

function | source

(make-alias &opt prefix)

#operators

struct | source

{* "%s * %s" + "%s + %s" - "%s - %s" / "%s / %s" < "%s < %s" <= "%s <= %s" = "%s = %s" > "%s > %s" >= "%s >= %s" and "(%s AND %s)" between "%s BETWEEN %s AND %s" exists "EXISTS %s" glob "%s GLOB %s" in "%s IN %s" like "%s LIKE %s" mod "%s % %s" nil? "%s IS NULL" not "NOT %s" not-in "%s NOT IN %s" not= "%s <> %s" or "(%s OR %s)" string "%s || %s" unique "UNIQUE %s"}

#doozer/compose

compose-query

#compose-query

function | source

(compose-query query-or-table binding {:where where :join join :select select})

#doozer

from, get-config, get-sources, join, select, to-sql, where

#from

macro | source

(from query-or-table & forms)

Construct a new doozer query.

query-or-table can be one of two datatypes:

• A string, which should be the name of a table present in the database;
• A symbol bound to an existing query structure.

The query-or-table reference can be wrapped in an as s-expression, eg.:

(select (as "artists" "main_artists_table"))

to specify the table alias used in the query.

forms may be headed by an optional binding symbol, followed by any number of s-expressions, each of which must be headed by one of the following symbols:

#where

where should be followed by a sequence of relations, which will be translated in to WHERE statements in SQL. An example of a query with a where statement:

(from "artists" a (where (= name "Steely Dan")))

This is equivalent to the SQL:

SELECT * FROM artists AS _0000da WHERE _0000da.name = 'Steely Dan'

In other words, (= name "Steely Dan") is treated as a simple relation where a single bare symbol, in this case name, is expected to be a column present on artists and "Steely Dan" is a user-inputted string.

That query can also be written as:

(from "artists" a (where (= (. a name) "Steely Dan")))

Inside of a relation expression, a . s-expression is exquivalent to the SQL table.column.

Finally, a two-tuple with the first element @ can be used to reference an existing variable:

(def my-name "Metallica")
(to-sql (from "artists" a (where (= name (@ my-name)))))

#join

join should be following by an alternating sequence of bindings and join expressions, in the same style as the let function. For instance:

(from "artists" a (join alb {:table "albums" :on (= ArtistId (. a ArtistId))}))

Which is equivalent to the SQL:

SELECT * FROM artists AS _0000e1
JOIN albums AS _0000e2 ON _0000e2.ArtistId = _0000e1.ArtistId

Join expressions can be written in two different ways. In addition to the more explicit, struct syntax seen above, they can also be written as a simple two-tuple of table and on:

(from "artists" a (join alb ["albums" (= (. alb ArtistId) (. a ArtistId))]))

The above example also demonstrates that ArtistId and (alb ArtistId) are equivalent; in analog to WHERE above, a single symbol in a join expression should be a column present on the table being joined.

The struct syntax also affords an keys:

• :as will specify the AS value for the join, instead of having a unique symbol as in the above examples. This user-specified name can then be used in subsequent compositions onto this query (see where). However, it also poses the risk of name collision and subtle bugs, so it shouldn't be used if it's not required.
• :type : The type of the join, as supported by your SQL backend. Defaults to :inner.

The binding value in a join can be used in WHERE clauses:

(from "artists" a
  (join alb {:table "albums" :on (= ArtistId (. a ArtistId))})
  (where (= (. alb title) "Ride The Lightning")))

#select

select should be followed by a list of relation expressions (see Expression API, below) to select.

#Query Composition

In addition to create new queries, from can be used to compose and reuse existing queries.

If query-or-table is a symbol, it will be looked up and treated as an existing query structure. A new structure will be created which combines the parameters of the existing query with the ones specified in the from call. Thus, small, modular queries can be written once and combined as necessary.

#Expression API

Expressions, such as those found in WHERE clauses or SELECT statements, can be in any of the following forms:

• <symbol>: interpreted as a column name in the implicit row (see where and join, above)
• <non-symbol>: the value will be (safely) interpolated into the query as a literal value (NB: the value types that can be sensibly interpolated into a query are implementation-dependent)
• (. <table-symbol> <column-symbol>): interpreted as a qualified table/row reference
• (. <table-string> <column-symbol>): interpreted as a qualified table/row reference, as above; however, table-string treated as a literal reference to a table name (instead of to the binding used in when writing the query form) and is directly interpolated into the resulting query
• (@ <variable-symbol>): the value of the variable at variable-symbol will be interpolated into the query
• (fn <function-string> & args): the SQL function function-string will be called on args; eg., (fn "count" id) or (fn "coalesce" name "Default Name")
• (raw <fragment-string> & args): Using ? as placeholders in the fragment string, args will be (safely) interpolated into the fragment and the fragment will be unsafely interpolated into the SQL query. eg., (fragment "? || ?" (. "artists" name) "!")
• (subquery <query>): query will be treated as a subquery inside the larger expression. query here can either be a symbol bound to an existent query structure, or a form constructing a new one.

#get-config

function | source

(get-config)

#get-sources

function | source

(get-sources query)

Produce a struct mapping all named sources to the tables they refer to.

This can be used, especially in interactive development, to determine if there's an existing JOIN in query that can be reused in subsequent calls to the where macro.

#join

function | source

(join query join-expr)

Given an existing query, create a new query with an additional single join clause.

Any unqualified symbol references will be treated, as in from, as references to columns on the joined table. No new bindings can be created with this function; however, existing named bindings can be referred to. See the :as key in the :join section of the from documentation for details.

#select

function | source

(select query & select-exprs)

Given an existing query, create a new query with an additional single select clause.

Any unqualified symbol references will be treated, as in from, as references to columns on the main query table. No new bindings can be created with this function; however, existing named bindings can be referred to. See the :as key in the :join section of the from documentation for details.

#to-sql

function | source

(to-sql query)

Render query into SQL. Produces a two-tuple of [sql, parameters], where sql is a SQL string, including parameter placeholders, and parameters is a dictionary mapping those placeholders to the values that should be interpolated into the query.

#where

function | source

(where query where-expr)

Given an existing query, create a new query with an additional single where clause.

Any unqualified symbol references will be treated, as in from, as references to columns on the main query table. No new bindings can be created with this function; however, existing named bindings can be referred to. See the :as key in the :join section of the from documentation for details.