guile-sql ¶

2.1 SQL as data ¶

guile-sql represents SQL queries as lists of clauses — ordinary Scheme data built from pairs, keywords, and symbols. A query is not a string, not a syntax object, and not an opaque record. It is a list that you can inspect with assq, transform with map and filter, extend with cons, and build dynamically with quasiquote.

This representation makes queries composable at runtime. A function can accept a query, add a WHERE clause, and return the result — without knowing or caring what the rest of the query looks like. sql-merge provides structured merging (AND-combining WHERE clauses, concatenating SELECT lists, and so on), but it is a convenience built on top of the list representation, not a prerequisite for working with queries.

Because queries are first-class Scheme values, they can be stored in variables, passed as arguments, returned from functions, and assembled from parts. There is no separate query-builder API to learn: Scheme itself is the query builder.

2.2 Parameterized queries ¶

When sql->string renders a DML query, every Scheme value in expression position becomes a numbered placeholder ($1, $2, …) in the SQL output, and the values are returned alongside the SQL string as a separate list. Values never appear inside the SQL string itself — any string the user supplies flows through as a bound parameter, so SQL injection is impossible by construction.

DDL contexts are different: no widely-used database accepts bound parameters in CREATE TABLE, ALTER TABLE, or CREATE TRIGGER, and migrations need to be re-runnable from a plain SQL file without a params vector. Inside column-spec contexts and trigger bodies, scalars render as SQL literals ('strings' single-quoted with escaping, numbers and booleans as-is). See Inline context for the full list of clauses and a description of the inline-propagation rule.

The placeholder style is configurable via the #:placeholder keyword argument to sql->string. Built-in options include placeholder-dollar ($1, $2 — the default, for PostgreSQL), placeholder-question (? — for MySQL and SQLite), and placeholder-colon (:1, :2 — for Oracle). Custom styles are plain Scheme procedures.

2.3 SQL coverage ¶

The core library handles the full range of DML statements: SELECT with JOINs (INNER, LEFT, RIGHT, FULL, CROSS), common table expressions (WITH, WITH RECURSIVE), window functions (OVER with PARTITION BY, ORDER BY, and frame clauses; FILTER; WITHIN GROUP; named windows), set operations (UNION, INTERSECT, EXCEPT with ALL variants), subqueries in FROM, WHERE, and SELECT positions, and ON CONFLICT upserts with DO NOTHING or DO UPDATE SET.

DDL support covers CREATE, ALTER, and DROP TABLE with full column definitions and constraints; indexes (including expression indexes); views; triggers (header); and TRUNCATE. The (sql pg) module adds PostgreSQL-specific DDL: materialized views, extensions, COMMENT ON, and CONCURRENTLY. The (sql sqlite) module adds SQLite-specific syntax: the MATCH operator (FTS5 / RTree), CREATE VIRTUAL TABLE, and the BEGIN … END trigger body.

Transaction control (BEGIN, COMMIT, ROLLBACK) is supported with isolation-level and access-mode options, plus SQLite’s DEFERRED / IMMEDIATE / EXCLUSIVE lock-mode forms. See Transactions.

sql->string also accepts #:pretty #t to produce indented, human-readable SQL output suitable for debugging and logging.

2.4 Extensibility ¶

Three registries allow users to extend guile-sql without modifying the library itself:

register-op!

Adds a new infix or prefix operator.

register-form!

Adds a new keyword expression form (for structural constructs like BETWEEN, CASE, or OVER).

register-clause!

Adds a new top-level clause, with associated statement-type classification, ordering anchors, merge strategy, and override chain support.

The (sql pg) and (sql sqlite) modules are built entirely on these registries (exposed via (sql dialect)). They contain no special hooks or privileged access to internal APIs — any user extension has the same capabilities.

2.5 What guile-sql is not ¶

guile-sql is a pure SQL generator. It takes Scheme data in and produces a parameterized SQL string out. It does not manage database connections, execute queries, pool connections, map results to objects, or run migrations.

This is a deliberate choice. By limiting its scope to SQL generation, guile-sql can be used with any database driver (see Database Integration) and any application architecture. An ORM, a migration tool, or a web framework can use guile-sql for query generation without guile-sql imposing opinions about the rest of the stack.

Next: Building from a release tarball, Up: Installation   [Contents][Index]

3.1 Dependencies ¶

• GNU Guile 3.0 https#://www.gnu.org/software/guile/
• GNU Autoconf and Automake (required when building from Git)
• Texinfo (optional, to build this manual)

Next: Building from Git, Previous: Dependencies, Up: Installation   [Contents][Index]

3.2 Building from a release tarball ¶

./configure && make && make install

Previous: Building from a release tarball, Up: Installation   [Contents][Index]

3.3 Building from Git ¶

Clone the repository from https://git.sr.ht/~campbellr/guile-sql:

git clone https://git.sr.ht/~campbellr/guile-sql
cd guile-sql
./bootstrap && ./configure && make && make install

To run tests without installing:

make check

To run the library without installing:

./pre-inst-env guile

To build this manual:

make
info doc/sql.info

Next: Dynamic queries, Up: Quick Start   [Contents][Index]

4.1 Basic SELECT ¶

(use-modules (sql))

(sql->string
  '((#:select id name email)
    (#:from users)
    (#:where (#:= active #t))
    (#:order-by (#:asc name))
    (#:limit 20)))
⇒ ("SELECT id, name, email FROM users WHERE active = $1 ORDER BY name ASC LIMIT $2" #t 20)

The input is plain Scheme data — a list of clauses. The output is a list whose car is the SQL string and whose cdr is the parameter values in order.

Next: Composable filter functions, Previous: Basic SELECT, Up: Quick Start   [Contents][Index]

4.2 Dynamic queries ¶

For queries with runtime values, quasiquote works naturally:

(let ((uid 42) (page 2) (size 20))
  (sql->string
    `((#:select id name email)
      (#:from users)
      (#:where (#:= user-id ,uid))
      (#:limit ,size)
      (#:offset ,(* page size)))))
⇒ ("SELECT id, name, email FROM users WHERE user_id = $1 LIMIT $2 OFFSET $3" 42 20 40)

Previous: Dynamic queries, Up: Quick Start   [Contents][Index]

4.3 Composable filter functions ¶

Plain functions that return partial clause lists compose with sql-merge:

(define (active-only q)
  (sql-merge q '((#:where (#:= active #t)))))

(define (paginate q page size)
  (sql-merge q `((#:limit ,size) (#:offset ,(* page size)))))

(define (with-roles q)
  (sql-merge q '((#:left-join (#:as roles r) (#:on (#:= users.role-id r.id)))
                 (#:select r.name))))

(sql->string
  (paginate
    (with-roles
      (active-only
        '((#:select users.id users.name)
          (#:from users))))
    2 20))
⇒ ("SELECT users.id, users.name, r.name FROM users LEFT JOIN roles AS r ON users.role_id = r.id WHERE active = $1 LIMIT $2 OFFSET $3" #t 20 40)

Next: Query structure, Up: Data Model   [Contents][Index]

5.1 Type discipline ¶

guile-sql uses three Scheme types with distinct SQL roles:

SQL structure markers are Guile keywords — the #:foo type, distinct from plain symbols. Because keywords are a separate type, keyword? cleanly identifies them, and there is no ambiguity between a clause marker and an identifier. Queries use standard quoted data notation:

'((#:select id name) (#:from users) (#:where (#:= active #t)))

If you prefer the shorter :symbol notation, enable Guile’s prefix keyword read syntax (see Keyword Read Syntax in Guile Reference Manual).

The #:null keyword is special: it represents SQL NULL and is never parameterized. See null-symbol.

Next: Subquery detection, Previous: Type discipline, Up: Data Model   [Contents][Index]

5.2 Query structure ¶

A query is a list of clauses. Each clause is (#:clause-name arg …). Because symbols are interned in Guile, assq works reliably:

'((#:select id (#:as created-at created))
  (#:from users)
  (#:left-join (#:as orders o) (#:on (#:= users.id orders.user-id)))
  (#:where (#:and (#:= users.id 42) (#:not (#:is-null orders.status))))
  (#:order-by (#:desc created-at))
  (#:limit 10)
  (#:offset 20))

Standard list operations always work — no special API is needed:

;; Read a clause
(assq #:where q)
⇒ (#:where (#:and (#:= active #t) (#:> age 18)))

;; Read a clause's arguments
(cdr (assq #:select q))
⇒ (id (#:as created-at created))

;; Check whether a clause is present
(assq #:limit q)     ⇒ (#:limit 10) or #f

;; Remove a clause
(filter (lambda (c) (not (eq? (car c) #:offset))) q)

The formatter sorts clauses into canonical SQL order regardless of their order in the input list. See Canonical clause ordering.

Next: Identifier conversion, Previous: Query structure, Up: Data Model   [Contents][Index]

5.3 Subquery detection ¶

When a list appears in expression position, the formatter automatically distinguishes subqueries from expression lists by inspecting the first element:

• Expression: (#:= a b) — first element is a Guile keyword.
• Function call: (count *) — first element is a plain symbol.
• Query: ((#:select …) …) — first element is a list starting with a Guile keyword.
• Plain list: ((name "Bob") …) — first element is a plain symbol. Not a subquery.

No registry check is needed. (keyword? (caar x)) is all it takes.

;; Subquery in FROM — detected automatically
(define active-users
  '((#:select id name) (#:from users) (#:where (#:= active #t))))

(sql->string
  `((#:select *)
    (#:from (#:as ,active-users u))
    (#:where (#:> u.age 18))))
⇒ ("SELECT * FROM (SELECT id, name FROM users WHERE active = $1) AS u WHERE u.age > $2" #t 18)

Use #:lift to force a value to be treated as a parameter even if it looks like a query. See lift-form.

Previous: Subquery detection, Up: Data Model   [Contents][Index]

5.4 Identifier conversion ¶

Scheme symbols use kebab-case; SQL identifiers use snake_case. The conversion is automatic:

Dots are split-points: each segment is converted independently, then rejoined with .. Segments containing characters outside [a-zA-Z0-9_] after conversion are automatically SQL double-quoted.

Use #:quoted to force double-quoting explicitly. See quoted-form.

Next: Comparison operators, Up: Expressions   [Contents][Index]

6.1 Atoms ¶

#:null produces the SQL NULL literal. It is never parameterized. When used as the right-hand side of #:= or #:!=, it automatically converts to IS NULL or IS NOT NULL. See Comparison operators.

The SQL datetime keyword literals are available as zero-argument expression forms:

Like #:null, these render verbatim (no parentheses, no parameter binding) and match the ‘literal-value’ production of both SQLite and PostgreSQL. Example:

(sql->string
  '((#:update users)
    (#:set (last-seen (#:current-timestamp)))
    (#:where (#:= id 42))))
⇒ ("UPDATE users SET last_seen = CURRENT_TIMESTAMP WHERE id = $1" 42)

Next: Logical operators, Previous: Atoms, Up: Expressions   [Contents][Index]

6.2 Comparison operators ¶

When #:= or #:!= encounters #:null as an operand it automatically converts to IS NULL or IS NOT NULL, preventing the classic SQL mistake of x = NULL (which is always unknown in SQL). Use (#:is-null x) or (#:is-not-null x) for the explicit forms. Use (#:raw "= NULL") if you genuinely need the broken SQL form.

Next: Arithmetic and string operators, Previous: Comparison operators, Up: Expressions   [Contents][Index]

6.3 Logical operators ¶

#:and does not add outer parentheses; #:or does. This reflects SQL precedence: AND binds tighter than OR, so OR expressions benefit from explicit grouping.

Operator parenthesization ¶

When an operator’s argument is itself an operator expression, it is automatically wrapped in parentheses to preserve the intended grouping. No manual parenthesization is needed:

(sql->string '((#:select (#:* (#:+ a b) c))))
⇒ ("SELECT (a + b) * c")

(sql->string '((#:select *) (#:from t)
               (#:where (#:not (#:and (#:= x 1) (#:= y 2))))))
⇒ ("SELECT * FROM t WHERE NOT ((x = $1) AND (y = $2))" 1 2)

Non-operator sub-expressions (function calls, identifiers, parameters) are never parenthesized.

In #:where and #:having clauses, sql-merge will automatically AND-combine conditions from multiple queries without creating nested #:and forms. See sql-merge.

Next: Set membership and range, Previous: Logical operators, Up: Expressions   [Contents][Index]

6.4 Arithmetic and string operators ¶

Scheme-friendly names are used for bitwise and modulo operators to avoid reader and shell headaches with the raw SQL tokens %, &, |, <<, >>. Bitwise XOR has no portable spelling (PostgreSQL uses #, SQLite has no SQL-level XOR), so #:bit-xor is registered in (sql pg) rather than in the core operator table. See Other PostgreSQL operators.

Next: Aliasing and ordering, Previous: Arithmetic and string operators, Up: Expressions   [Contents][Index]

6.5 Set membership and range ¶

#:in and #:not-in are variadic. All arguments after the first are treated as values. A single remaining argument that is a query is treated as a subquery.

(sql->string '((#:select *) (#:from t) (#:where (#:in x 1 2 3))))
⇒ ("SELECT * FROM t WHERE x IN ($1, $2, $3)" 1 2 3)

(sql->string '((#:select *) (#:from t) (#:where (#:in x "a" "b"))))
⇒ ("SELECT * FROM t WHERE x IN ($1, $2)" "a" "b")

(sql->string
  '((#:select *) (#:from t)
    (#:where (#:in x ((#:select id) (#:from categories))))))
⇒ ("SELECT * FROM t WHERE x IN (SELECT id FROM categories)")

#:composite produces a parenthesized, comma-separated tuple. This is useful for row-value comparisons:

(sql->string
  '((#:select *) (#:from t)
    (#:where (#:in (#:composite id name)
                   ((#:select id name) (#:from other))))))
⇒ ("SELECT * FROM t WHERE (id, name) IN (SELECT id, name FROM other)")

#:distinct is used inside aggregate function calls:

(sql->string '((#:select (count (#:distinct email)))))
⇒ ("SELECT COUNT(DISTINCT email)")

#:nest forces parenthesization around an expression:

(sql->string '((#:select (#:nest (#:+ a b)))))
⇒ ("SELECT (a + b)")

#:lateral wraps a subquery or expression with the LATERAL keyword, for use in join positions:

'(#:cross-join (#:lateral ((#:select *)
                           (#:from orders)
                           (#:where (#:= orders.user-id users.id)))))
⇒ CROSS JOIN LATERAL (SELECT * FROM orders
   WHERE orders.user_id = users.id)

Next: Conditionals, Previous: Set membership and range, Up: Expressions   [Contents][Index]

6.6 Aliasing and ordering ¶

Next: Function calls, Previous: Aliasing and ordering, Up: Expressions   [Contents][Index]

6.7 Conditionals ¶

#:case takes alternating condition/value pairs, with an optional #:else clause at the end (searched CASE):

(sql->string
  '((#:select
     (#:case
       (#:= status "active")  "Active"
       (#:= status "pending") "Pending"
       #:else                 "Unknown"))
    (#:from t)))
⇒ ("SELECT CASE WHEN status = $1 THEN $2 WHEN status = $3 THEN $4 ELSE $5 END FROM t" "active" "Active" "pending" "Pending" "Unknown")

#:case-expr is the simple CASE form where a single expression is tested against values:

(sql->string
  '((#:select (#:case-expr status
                "active" "A"
                "pending" "P"
                #:else   "?"))
    (#:from t)))
⇒ ("SELECT CASE status WHEN $1 THEN $2 WHEN $3 THEN $4 ELSE $5 END FROM t" "active" "A" "pending" "P" "?")

Next: Escape hatches, Previous: Conditionals, Up: Expressions   [Contents][Index]

6.8 Function calls ¶

Any list whose head is a plain symbol (not a Guile keyword) is treated as a SQL function call. The function name is uppercased:

(sql->string '((#:select (count *))))
⇒ ("SELECT COUNT(*)")

(sql->string '((#:select (now))))
⇒ ("SELECT NOW()")

(sql->string '((#:select (coalesce a b "default"))))
⇒ ("SELECT COALESCE(a, b, $1)" "default")

(sql->string '((#:select (sum (#:* price quantity)))))
⇒ ("SELECT SUM(price * quantity)")

The ‘*’ argument in (count *) is the plain Scheme symbol *. identifier->sql passes it through verbatim rather than quoting it, so any function that accepts * as an argument in SQL (COUNT, JSONB_AGG, ARRAY_AGG, …) works without an escape hatch:

(sql->string '((#:select (count *))))
⇒ ("SELECT COUNT(*)")

(sql->string '((#:select (count (#:distinct email)))))
⇒ ("SELECT COUNT(DISTINCT email)")

(sql->string '((#:select (count users.*))))
⇒ ("SELECT COUNT(users.*)")

A trailing (#:order-by …) in the argument list is treated as aggregate ORDER BY:

(sql->string
  '((#:select (array-agg name (#:order-by (#:asc name))))))
⇒ ("SELECT ARRAY_AGG(name ORDER BY name ASC)")

(sql->string
  '((#:select (string-agg name ", " (#:order-by (#:asc name))))))
⇒ ("SELECT STRING_AGG(name, $1 ORDER BY name ASC)" ", ")

Next: Window functions, Previous: Function calls, Up: Expressions   [Contents][Index]

6.9 Escape hatches ¶

(#:raw sql-string)

Splice sql-string verbatim into the output. No parameters are generated. Use with care and never with untrusted input.

(#:lift val)

Force val to be treated as a parameterized value, never as SQL structure. Useful when data happens to look like a query.

(define my-data '(some "list" 42))

(sql->string
  `((#:select *) (#:from t)
    (#:where (#:= metadata (#:lift ,my-data)))))
⇒ ("SELECT * FROM t WHERE metadata = $1" (some "list" 42))

(#:inline val)

Render val as a SQL literal directly in the output, without parameterization. Strings are single-quoted (with ' escaping), numbers render directly, #t/#f become TRUE/FALSE, and #:null becomes NULL. Use for values that must appear literally in SQL (e.g. date literals, interval strings).

(sql->string '((#:select (#:inline "2024-01-15"))))
⇒ ("SELECT '2024-01-15'")

(sql->string '((#:select (#:inline 42))))
⇒ ("SELECT 42")

(sql->string '((#:select (#:inline #t))))
⇒ ("SELECT TRUE")

Unlike #:raw, #:inline is type-aware and handles escaping. Unlike #:lift, the value is not parameterized.

Inside DDL column-spec contexts (see Inline context), scalars render as literals automatically — the scope propagates through function-call arguments, so (datetime "now") in a column default emits DATETIME('now') without any explicit #:inline wrap. Use #:inline explicitly when you want a SQL literal outside those contexts.

(#:quoted sym)

SQL double-quote every segment of the identifier sym. Use for reserved words or to preserve case.

(sql->string '((#:select (#:quoted user))))
⇒ ("SELECT \"user\"")

(sql->string '((#:select (#:quoted public.user))))
⇒ ("SELECT \"public\".\"user\"")

(#:cast expr type)

CAST(expr AS type). type may be a symbol or a string; string types are spliced verbatim.

(sql->string '((#:select (#:cast x integer))))
⇒ ("SELECT CAST(x AS integer)")

(sql->string '((#:select (#:cast name "varchar(255)"))))
⇒ ("SELECT CAST(name AS varchar(255))")

Previous: Escape hatches, Up: Expressions   [Contents][Index]

6.10 Window functions ¶

Window modifiers (#:over, #:filter, #:within-group) wrap the aggregate function expression, following the same pattern as #:as and #:cast:

(#:as expr alias)            ;; expr AS alias
(#:cast val type)            ;; CAST(val AS type)
(#:over fn-expr specs...)  ;; fn OVER (specs)
(#:filter fn-expr cond)      ;; fn FILTER (WHERE cond)

#:over ¶

#:over takes a function expression and one or more window specification elements:

(sql->string
  '((#:select (#:over (sum salary)
                      (#:partition-by department)
                      (#:order-by (#:asc hired-at)))))) 
⇒ ("SELECT SUM(salary) OVER (PARTITION BY department ORDER BY hired_at ASC)")

Frame clauses ¶

Frame clauses control which rows are included in the window. Specify a frame type and two bounds:

(sql->string
  '((#:select (#:over (sum salary)
                      (#:partition-by department)
                      (#:order-by (#:asc hired-at))
                      (#:rows-between #:unbounded-preceding #:current-row)))))
⇒ ("SELECT SUM(salary) OVER (PARTITION BY department ORDER BY hired_at ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)")

Frame types: #:rows-between, #:range-between, #:groups-between.

Frame bounds: #:unbounded-preceding, #:current-row, #:unbounded-following, (#:preceding N), (#:following N).

Named windows ¶

Instead of repeating a window spec in every expression, define it once with the #:window clause and reference it by name:

(sql->string
  '((#:select name salary
       (#:over (rank) #:w)
       (#:over (sum salary) #:w))
    (#:from employees)
    (#:window (w (#:partition-by department)
                 (#:order-by (#:desc salary))))))
⇒ ("SELECT name, salary, RANK() OVER w, SUM(salary) OVER w FROM employees WINDOW w AS (PARTITION BY department ORDER BY salary DESC)")

The #:window clause produces WINDOW w AS (…). Reference a named window by passing a keyword (#:w) as the sole window spec in #:over. See window-clause, for the clause reference.

#:filter ¶

#:filter wraps an aggregate with FILTER (WHERE …):

(sql->string
  '((#:select (#:filter (count *) (#:= status "active"))) (#:from t)))
⇒ ("SELECT COUNT(*) FILTER (WHERE status = $1) FROM t" "active")

#:within-group ¶

#:within-group is for ordered-set aggregates:

(sql->string
  '((#:select (#:within-group (percentile-cont 0.5)
                              (#:order-by (#:asc salary))))))
⇒ ("SELECT PERCENTILE_CONT($1) WITHIN GROUP (ORDER BY salary ASC)" 0.5)

Composing modifiers ¶

Modifiers compose by nesting — the inner modifier wraps the function, and the outer modifier wraps the result. PostgreSQL requires FILTER before OVER, so #:filter nests inside #:over:

(sql->string
  '((#:select (#:over (#:filter (count *) (#:= status "active"))
                      (#:partition-by department)))))
⇒ ("SELECT COUNT(*) FILTER (WHERE status = $1) OVER (PARTITION BY department)" "active")

Reading from inside out: (count *) is the base aggregate, #:filter adds the WHERE filter, and #:over adds the window. The nesting order ensures the SQL keywords appear in the correct sequence.

Next: JOIN clauses, Up: Clauses   [Contents][Index]

7.1 SELECT clauses ¶

#:select col …

SELECT col, …

Star and qualified-star are supported as bare symbols:

(sql->string '((#:select *) (#:from users)))
⇒ ("SELECT * FROM users")

(sql->string '((#:select users.*) (#:from users)))
⇒ ("SELECT users.* FROM users")

(sql->string '((#:select u.id u.*) (#:from (#:as users u))))
⇒ ("SELECT u.id, u.* FROM users AS u")

This works because identifier->sql passes ‘*’ segments through verbatim (see Identifier conversion). There is no separate #:* form; use the plain symbol.

#:select-distinct col …

SELECT DISTINCT col, …

#:select-distinct-on (col …) col …

SELECT DISTINCT ON (col, …) col, … (PostgreSQL).

The standard grammar treats DISTINCT and DISTINCT ON as decorators of the SELECT production, so the following shape is also supported (and preferred for new code):

(sql->string '((#:select (#:distinct) country) (#:from users)))
⇒ ("SELECT DISTINCT country FROM users")

(sql->string
  '((#:select (#:distinct-on (location)) location time) (#:from t)))
⇒ ("SELECT DISTINCT ON (location) location, time FROM t")

The standalone #:select-distinct and #:select-distinct-on clauses remain supported; either form produces identical SQL.

#:values-stmt (v …) …

VALUES (v, …), … as a top-level statement (not inside an INSERT). Each argument is a row. Composes with #:order-by, #:limit, and #:offset, and can appear as the body of a CTE.

(sql->string
  '((#:values-stmt (1 "a") (2 "b") (3 "c"))
    (#:order-by (#:desc column1))
    (#:limit 2)))
⇒ ("VALUES ($1, $2), ($3, $4), ($5, $6) ORDER BY column1 DESC LIMIT $7" 1 "a" 2 "b" 3 "c" 2)

#:from table …

FROM table, …. Each table may be a symbol, an (#:as …) alias form, or a query (subquery). A subquery in FROM position must be aliased with #:as; PostgreSQL requires it.

#:where expr

WHERE expr.

#:group-by col …

GROUP BY col, …

SQL:2003 grouping extensions (ROLLUP, CUBE, GROUPING SETS) are supported as expression forms that can appear as #:group-by arguments:

(sql->string '((#:select *) (#:from t) (#:group-by (#:rollup a b))))
⇒ ("SELECT * FROM t GROUP BY ROLLUP (a, b)")

(sql->string '((#:select *) (#:from t) (#:group-by (#:cube a b))))
⇒ ("SELECT * FROM t GROUP BY CUBE (a, b)")

(sql->string
  '((#:select *) (#:from t)
    (#:group-by (#:grouping-sets (a b) (a) ()))))
⇒ ("SELECT * FROM t GROUP BY GROUPING SETS ((a, b), (a), ())")

GROUPING SETS accepts bare column lists, empty lists ((), the empty grouping set), and nested (#:rollup …) or (#:cube …) forms.

#:having expr

HAVING expr.

#:window (name spec …) …

Named window definitions for #:over references.

(sql->string
  '((#:select *) (#:from t)
    (#:window (w (#:partition-by dept) (#:order-by (#:desc salary))))))
⇒ ("SELECT * FROM t WINDOW w AS (PARTITION BY dept ORDER BY salary DESC)")

#:order-by expr …

ORDER BY expr, …

#:limit n

LIMIT $N

#:offset n

OFFSET $N

#:for arg …

Row locking (PostgreSQL). The grammar is FOR <mode> [OF tbl, …] [NOWAIT | SKIP LOCKED].

Lock modes (required): #:update, #:no-key-update, #:share, #:key-share.

Wait policy (optional): #:nowait, #:skip-locked.

Per-table scope (optional): (#:of tbl …).

(#:for #:update)                      ; FOR UPDATE
(#:for #:no-key-update)               ; FOR NO KEY UPDATE
(#:for #:share (#:of users orders))   ; FOR SHARE OF users, orders
(#:for #:update (#:of users) #:nowait) ; FOR UPDATE OF users NOWAIT

Next: INSERT clauses, Previous: SELECT clauses, Up: Clauses   [Contents][Index]

7.2 JOIN clauses ¶

Join clauses use alternating table-expression / condition pairs within a single clause. #:on and #:using are required condition wrappers.

;; Single join with :on
(sql->string
  '((#:select u.*) (#:from (#:as users u))
    (#:left-join (#:as roles r) (#:on (#:= u.role-id r.id)))))
⇒ ("SELECT u.* FROM users AS u LEFT JOIN roles AS r ON u.role_id = r.id")

;; Multiple joins of the same type — one clause, alternating pairs
(sql->string
  '((#:select x.*) (#:from x)
    (#:left-join (#:as t1 a) (#:on (#:= x.id t1.x-id))
                 (#:as t2 b) (#:on (#:= x.id t2.x-id)))))
⇒ ("SELECT x.* FROM x LEFT JOIN t1 AS a ON x.id = t1.x_id LEFT JOIN t2 AS b ON x.id = t2.x_id")

;; USING with column names
(sql->string
  '((#:select *) (#:from orders)
    (#:left-join payments (#:using id created-at))))
⇒ ("SELECT * FROM orders LEFT JOIN payments USING (id, created_at)")

Supported conditional join types: #:join / #:inner-join, #:left-join, #:right-join, #:full-join. #:cross-join takes only table expressions (no ON/USING).

Natural joins match on common column names automatically and so also take only table expressions:

(sql->string '((#:select *) (#:from a) (#:natural-join b)))
⇒ ("SELECT * FROM a NATURAL JOIN b")

(sql->string '((#:select *) (#:from a) (#:natural-left-join b)))
⇒ ("SELECT * FROM a NATURAL LEFT JOIN b")

Available variants: #:natural-join, #:natural-inner-join, #:natural-left-join, #:natural-right-join, #:natural-full-join.

Next: UPDATE clauses, Previous: JOIN clauses, Up: Clauses   [Contents][Index]

7.3 INSERT clauses ¶

;; Single row
(sql->string
  '((#:insert-into users)
    (#:columns name email age)
    (#:values ("Alice" "alice@example.com" 30))))
⇒ ("INSERT INTO users (name, email, age) VALUES ($1, $2, $3)" "Alice" "alice@example.com" 30)

;; Multiple rows
(sql->string
  '((#:insert-into users)
    (#:columns name email)
    (#:values ("Alice" "alice@example.com")
              ("Bob"   "bob@example.com"))))
⇒ ("INSERT INTO users (name, email) VALUES ($1, $2), ($3, $4)" "Alice" "alice@example.com" "Bob" "bob@example.com")

Each row in #:values is a sub-list of expressions. Elements are processed as expressions, so function calls work (e.g., (now) produces NOW()).

For rows whose columns all take their DEFAULT values, use #:default-values instead of #:values:

(sql->string
  '((#:insert-into users) (#:default-values) (#:returning id)))
⇒ ("INSERT INTO users DEFAULT VALUES RETURNING id")

INSERT OR … (SQLite) ¶

SQLite accepts a conflict-action modifier directly on INSERT that pre-dates the SQL-standard ON CONFLICT clause. Pass the action as an optional keyword after the table name:

(use-modules (sql sqlite))

(sql->string
  '((#:insert-into users #:or-ignore)
    (#:columns email name)
    (#:values ("a@x" "Alice"))))
⇒ ("INSERT OR IGNORE INTO users (email, name) VALUES ($1, $2)" "a@x" "Alice")

PostgreSQL rejects this syntax; prefer #:on-conflict for cross-dialect code.

INSERT…SELECT works by combining INSERT and SELECT clauses in the same query. The SELECT portion can be built separately and spliced in:

(define archived-query
  '((#:select id name)
    (#:from users)
    (#:where (#:= deleted #t))))

(sql->string
  `((#:insert-into archived-users)
    (#:columns id name)
    ,@archived-query))
⇒ ("INSERT INTO archived_users (id, name) SELECT id, name FROM users WHERE deleted = $1" #t)

ON CONFLICT (PostgreSQL) ¶

;; DO NOTHING
(sql->string
  '((#:insert-into users) (#:columns email) (#:values ("a@x"))
    (#:on-conflict #:do-nothing)))
⇒ ("INSERT INTO users (email) VALUES ($1) ON CONFLICT DO NOTHING" "a@x")

;; DO NOTHING on specific column
(sql->string
  '((#:insert-into users) (#:columns email) (#:values ("a@x"))
    (#:on-conflict (email) #:do-nothing)))
⇒ ("INSERT INTO users (email) VALUES ($1) ON CONFLICT (email) DO NOTHING" "a@x")

;; DO UPDATE SET
(sql->string
  '((#:insert-into users) (#:columns email name)
    (#:values ("a@x" "Alice"))
    (#:on-conflict (email)
      (#:do-update-set (name excluded.name)
                       (updated-at (now))))))
⇒ ("INSERT INTO users (email, name) VALUES ($1, $2) ON CONFLICT (email) DO UPDATE SET name = excluded.name, updated_at = NOW()" "a@x" "Alice")

The EXCLUDED pseudo-table holds the row that would have been inserted, had there been no conflict. It is referenced like any other table: a dotted symbol such as excluded.name becomes the SQL identifier excluded.name after kebab→snake conversion. No special form is needed.

EXCLUDED references can appear in any expression position inside #:do-update-set, including on the right-hand side of assignments and inside the optional #:where predicate:

'((#:insert-into users)
  (#:columns email name login-count)
  (#:values ("a@x" "Alice" 1))
  (#:on-conflict (email)
    (#:do-update-set (name       excluded.name)
                     (login-count (#:+ users.login-count excluded.login-count))
                     (#:where (#:!= users.name excluded.name)))))
⇒
INSERT INTO users (email, name, login_count) VALUES ($1, $2, $3)
ON CONFLICT (email)
DO UPDATE SET name = excluded.name,
              login_count = users.login_count + excluded.login_count
              WHERE users.name != excluded.name

Use #:on-constraint for named constraint targets instead of column lists:

(sql->string
  '((#:insert-into users) (#:columns email) (#:values ("a@x"))
    (#:on-conflict (#:on-constraint uq-email) #:do-nothing)))
⇒ ("INSERT INTO users (email) VALUES ($1) ON CONFLICT ON CONSTRAINT uq_email DO NOTHING" "a@x")

A #:where clause at the end of #:do-update-set filters which conflicting rows are actually updated:

(sql->string
  '((#:insert-into users) (#:columns email name)
    (#:values ("a@x" "Alice"))
    (#:on-conflict (email)
      (#:do-update-set (name excluded.name)
                       (#:where (#:= users.active #t))))))
⇒ ("INSERT INTO users (email, name) VALUES ($1, $2) ON CONFLICT (email) DO UPDATE SET name = excluded.name WHERE users.active = $3" "a@x" "Alice" #t)

Next: DELETE clauses, Previous: INSERT clauses, Up: Clauses   [Contents][Index]

7.4 UPDATE clauses ¶

(sql->string
  '((#:update users)
    (#:set (name "Bob") (updated-at (now)))
    (#:where (#:= id 42))
    (#:returning id name updated-at)))
⇒ ("UPDATE users SET name = $1, updated_at = NOW() WHERE id = $2 RETURNING id, name, updated_at" "Bob" 42)

The #:set clause expects (column value-expr) pairs. (name "Bob") is not treated as a function call — the #:set handler expects pairs, not expressions. Subqueries work naturally in value position.

Next: CTE clauses (#:with and #:with-recursive), Previous: UPDATE clauses, Up: Clauses   [Contents][Index]

7.5 DELETE clauses ¶

(sql->string
  '((#:delete-from users)
    (#:where (#:and (#:= active #f) (#:< last-login "2020-01-01")))
    (#:returning id email)))
⇒ ("DELETE FROM users WHERE (active = $1) AND (last_login < $2) RETURNING id, email" #f "2020-01-01")

;; DELETE ... USING
(sql->string
  '((#:delete-from orders)
    (#:using users)
    (#:where (#:= orders.user-id users.id))))
⇒ ("DELETE FROM orders USING users WHERE orders.user_id = users.id")

Next: Set operations, Previous: DELETE clauses, Up: Clauses   [Contents][Index]

7.6 CTE clauses (#:with and #:with-recursive) ¶

Each CTE entry is a sub-list with 2 or 3 elements:

;; (name query)
(define active-users-q
  '((#:select id name) (#:from users) (#:where (#:= active #t))))

(sql->string
  `((#:with (active-users ,active-users-q))
    (#:select *)
    (#:from active-users)))
⇒ ("WITH active_users AS (SELECT id, name FROM users WHERE active = $1) SELECT * FROM active_users" #t)

;; (name (col ...) query) — with explicit column list
(define active-users-q
  '((#:select id name) (#:from users) (#:where (#:= active #t))))

(sql->string
  `((#:with (active-users (id name) ,active-users-q))
    (#:select *)
    (#:from active-users)))
⇒ ("WITH active_users(id, name) AS (SELECT id, name FROM users WHERE active = $1) SELECT * FROM active_users" #t)

;; Multiple CTEs
(define active-users-q
  '((#:select id name) (#:from users) (#:where (#:= active #t))))
(define recent-orders-q
  '((#:select user-id (#:as (sum total) total))
    (#:from orders)
    (#:group-by user-id)))

(sql->string
  `((#:with (active-users  ,active-users-q)
            (recent-orders ,recent-orders-q))
    (#:select active-users.name recent-orders.total)
    (#:from active-users)
    (#:join recent-orders (#:on (#:= active-users.id recent-orders.user-id)))))
⇒ ("WITH active_users AS (SELECT id, name FROM users WHERE active = $1), recent_orders AS (SELECT user_id, SUM(total) AS total FROM orders GROUP BY user_id) SELECT active_users.name, recent_orders.total FROM active_users INNER JOIN recent_orders ON active_users.id = recent_orders.user_id" #t)

Next: Canonical clause ordering, Previous: CTE clauses (#:with and #:with-recursive), Up: Clauses   [Contents][Index]

7.7 Set operations ¶

`((#:union-all ,query-a ,query-b)
  (#:order-by (#:desc created-at))
  (#:limit 100))
⇒ (SELECT ...) UNION ALL (SELECT ...)
         ORDER BY created_at DESC LIMIT $1

Supported: #:union, #:union-all, #:intersect, #:intersect-all, #:except, #:except-all.

Previous: Set operations, Up: Clauses   [Contents][Index]

7.8 Canonical clause ordering ¶

The formatter always sorts clauses into canonical SQL order regardless of how they appear in the input. This means clause order in your data never matters — write them in whatever order is convenient.

SELECT order: #:with, #:with-recursive, #:select, #:select-distinct, #:from, joins, #:where, #:group-by, #:having, #:window, #:order-by, #:limit, #:offset, #:for

INSERT order: #:insert-into, #:columns, #:values, #:on-conflict, #:returning

UPDATE order: #:update, #:set, #:from, #:where, #:returning

DELETE order: #:delete-from, #:using, #:where, #:returning

8.1 CREATE TABLE ¶

(sql->string
  '((#:create-table users)
    (#:with-columns
      (id integer (#:primary-key))
      (name text (#:not-null))
      (email text (#:not-null) (#:unique))
      (active boolean (#:default #t))
      (created-at timestamptz (#:not-null) (#:default (now))))))
⇒ ("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE, active BOOLEAN DEFAULT TRUE, created_at TIMESTAMPTZ NOT NULL DEFAULT (NOW()))")

Pass #:if-not-exists after the table name:

(sql->string '((#:create-table users #:if-not-exists)))
⇒ ("CREATE TABLE IF NOT EXISTS users")

Scalars inline; no bound parameters in DDL ¶

Column defaults, CHECK expressions, and table-level constraints inside #:with-columns are rendered as if #:inline were set: scalars (strings, numbers, booleans, #:null) appear as SQL literals rather than being pushed onto the parameter list. SQLite rejects parameterised DDL outright; PostgreSQL accepts it in a few places but migration files need to be re-runnable without a params vector, so we inline everywhere.

;; (#:default 0)          -> DEFAULT 0          (not $1 with 0 bound)
;; (#:default "")         -> DEFAULT ''
;; (#:default #t)         -> DEFAULT TRUE
;; (#:default #:null)     -> DEFAULT NULL
;; (#:check (#:> x 0))    -> CHECK (x > 0)      (0 inline, not bound)

Non-scalar defaults — function calls, bare identifiers, compound expressions — are wrapped in parentheses. SQLite’s grammar requires DEFAULT (expr) for anything other than a literal or signed number; PostgreSQL accepts the parenthesised form too, so the wrap is dialect-neutral:

;; (#:default (now))              -> DEFAULT (NOW())
;; (#:default (datetime "now"))   -> DEFAULT (DATETIME('now'))
;; (#:default CURRENT-TIMESTAMP)  -> DEFAULT (CURRENT_TIMESTAMP)

The inline scope extends into function-call arguments, so scalars inside the compound expression also render as literals — the "now" in the second line becomes 'now', not a bound parameter.

Column definitions ¶

Each entry in #:with-columns is (name type constraint …).

The type can be:

• a symbol — uppercased: integer ⇒ INTEGER
• a string — verbatim: "varchar(255)"
• a list — type constructor: (varchar 50) ⇒ VARCHAR(50), (numeric 10 2) ⇒ NUMERIC(10, 2)

STORED generated columns are supported by PostgreSQL 12+ and SQLite 3.31+. VIRTUAL is SQLite-only; PostgreSQL rejects it. IDENTITY is PostgreSQL 10+ only; SQLite has no SQL-level equivalent (use INTEGER PRIMARY KEY for implicit rowid aliasing).

The generator emits the SQL unconditionally — it has no way to know your target database. You are responsible for using constraints your database supports.

The #:constraint column-level prefix names an inner constraint so the DB can refer to it by name in error messages or ALTER TABLE ... DROP CONSTRAINT:

(sql->string
  '((#:create-table orders)
    (#:with-columns
      (price numeric (#:constraint ck-price (#:check (#:> price 0)))))))
⇒ ("CREATE TABLE orders (price NUMERIC CONSTRAINT ck_price CHECK (price > 0))")

Reference options: #:on-delete action and #:on-update action, where action is a keyword like #:cascade, #:restrict, #:set-null, or #:set-default.

String types like "varchar(255)" can be used directly as the type.

Table-level constraints ¶

Entries in #:with-columns that start with a list (not a symbol) are table-level constraints:

;; Composite primary key
((#:primary-key col1 col2))

;; Named constraint
((#:constraint my-pk) (#:primary-key col1 col2))

;; Foreign key with options
((#:foreign-key (user-id)) (#:references (users id)) (#:on-delete #:cascade))

;; Table-level check
((#:check (#:> end-date start-date)))

;; Unique constraint
((#:unique col1 col2))

8.2 DROP TABLE ¶

(sql->string '((#:drop-table users)))
⇒ ("DROP TABLE users")

(sql->string '((#:drop-table users #:if-exists)))
⇒ ("DROP TABLE IF EXISTS users")

(sql->string '((#:drop-table foo bar #:if-exists #:cascade)))
⇒ ("DROP TABLE IF EXISTS foo, bar CASCADE")

Options #:if-exists, #:cascade, and #:restrict may appear in any order after the table names.

8.3 ALTER TABLE ¶

#:alter-table introduces the table to be altered. Use separate clauses for a single operation:

(sql->string
  '((#:alter-table users) (#:add-column (email text (#:not-null)))))
⇒ ("ALTER TABLE users ADD COLUMN email TEXT NOT NULL")

Or pass multiple operations inline for a single comma-separated ALTER TABLE statement:

(sql->string
  '((#:alter-table fruit
      (#:add-column (id integer (#:not-null)))
      (#:drop-column ident)
      (#:alter-column (name #:set-data-type text)))))
⇒ ("ALTER TABLE fruit ADD COLUMN id INTEGER NOT NULL, DROP COLUMN ident, ALTER COLUMN name SET DATA TYPE TEXT")

ADD COLUMN ¶

Adds a column using the same column definition format as #:with-columns (see DDL — Data Definition Language). Scalars in defaults inline and compound expressions wrap, same as CREATE TABLE.

(sql->string
  '((#:alter-table t) (#:add-column (active boolean (#:default #t)))))
⇒ ("ALTER TABLE t ADD COLUMN active BOOLEAN DEFAULT TRUE")

(sql->string
  '((#:alter-table t) (#:add-column (email text) #:if-not-exists)))
⇒ ("ALTER TABLE t ADD COLUMN IF NOT EXISTS email TEXT")

DROP COLUMN ¶

(sql->string '((#:alter-table t) (#:drop-column email)))
⇒ ("ALTER TABLE t DROP COLUMN email")

(sql->string
  '((#:alter-table t) (#:drop-column email #:if-exists #:cascade)))
⇒ ("ALTER TABLE t DROP COLUMN IF EXISTS email CASCADE")

ALTER COLUMN ¶

Each #:alter-column takes a list (col action [arg]). Defaults passed to #:set-default follow the same inline / paren-wrap rules as CREATE TABLE.

(sql->string
  '((#:alter-table t) (#:alter-column (name #:set-data-type text))))
⇒ ("ALTER TABLE t ALTER COLUMN name SET DATA TYPE TEXT")

(sql->string
  '((#:alter-table t) (#:alter-column (active #:set-default #t))))
⇒ ("ALTER TABLE t ALTER COLUMN active SET DEFAULT TRUE")

(sql->string
  '((#:alter-table t)
    (#:alter-column (created-at #:set-default (now)))))
⇒ ("ALTER TABLE t ALTER COLUMN created_at SET DEFAULT (NOW())")

RENAME COLUMN / RENAME TABLE ¶

(sql->string
  '((#:alter-table t) (#:rename-column (old-name new-name))))
⇒ ("ALTER TABLE t RENAME COLUMN old_name TO new_name")

(sql->string '((#:alter-table users) (#:rename-table customers)))
⇒ ("ALTER TABLE users RENAME TO customers")

ADD CONSTRAINT / DROP CONSTRAINT ¶

;; UNIQUE
(sql->string
  '((#:alter-table t) (#:add-constraint (uq-email #:unique email))))
⇒ ("ALTER TABLE t ADD CONSTRAINT uq_email UNIQUE (email)")

;; PRIMARY KEY
(sql->string
  '((#:alter-table t) (#:add-constraint (pk-t #:primary-key id))))
⇒ ("ALTER TABLE t ADD CONSTRAINT pk_t PRIMARY KEY (id)")

;; CHECK
(sql->string
  '((#:alter-table t)
    (#:add-constraint (ck-price #:check (#:> price 0)))))
⇒ ("ALTER TABLE t ADD CONSTRAINT ck_price CHECK (price > 0)")

;; FOREIGN KEY
(sql->string
  '((#:alter-table orders)
    (#:add-constraint (fk-user #:foreign-key (user-id)
                                #:references (users id)
                                #:on-delete #:cascade))))
⇒ ("ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE")

;; DROP CONSTRAINT
(sql->string
  '((#:alter-table t)
    (#:drop-constraint uq-email #:if-exists #:cascade)))
⇒ ("ALTER TABLE t DROP CONSTRAINT IF EXISTS uq_email CASCADE")

8.4 CREATE INDEX / DROP INDEX ¶

(sql->string '((#:create-index idx-email users email)))
⇒ ("CREATE INDEX idx_email ON users (email)")

(sql->string
  '((#:create-index idx-name users first-name last-name)))
⇒ ("CREATE INDEX idx_name ON users (first_name, last_name)")

Options can appear anywhere in the argument list alongside the positional names (index-name, table, columns):

(use-modules (sql pg))

(sql->string '((#:create-index idx-data t data (#:using #:gin))))
⇒ ("CREATE INDEX idx_data ON t USING GIN (data)")

(sql->string
  '((#:create-index idx-data t location
                    #:unique #:concurrently #:if-not-exists
                    (#:using #:gist))))
⇒ ("CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS idx_data ON t USING GIST (location)")

Column specs can be symbols (bare column names), expressions (function calls), or ordering directives:

;; Expression index (functional index)
(sql->string '((#:create-index idx-lower-email users (lower email))))
⇒ ("CREATE INDEX idx_lower_email ON users (LOWER(email))")

;; Column with ordering
(sql->string '((#:create-index idx-date orders (#:desc created-at))))
⇒ ("CREATE INDEX idx_date ON orders (created_at DESC)")

#:drop-index uses the same option pattern as #:drop-table:

(sql->string '((#:drop-index idx-email)))
⇒ ("DROP INDEX idx_email")

(use-modules (sql pg))

(sql->string
  '((#:drop-index idx-email #:if-exists #:concurrently #:cascade)))
⇒ ("DROP INDEX CONCURRENTLY IF EXISTS idx_email CASCADE")

8.5 CREATE VIEW / DROP VIEW ¶

#:create-view and #:create-or-replace-view emit the AS keyword automatically. The view body is composed from standard DML clauses (#:select, #:from, etc.).

(sql->string
  '((#:create-view active-users)
    (#:select id name)
    (#:from users)
    (#:where (#:= active #t))))
⇒ ("CREATE VIEW active_users AS SELECT id, name FROM users WHERE active = $1" #t)

(sql->string
  '((#:create-view active-users #:if-not-exists)
    (#:select *) (#:from users)))
⇒ ("CREATE VIEW IF NOT EXISTS active_users AS SELECT * FROM users")

#:create-or-replace-view is PostgreSQL-specific. See PostgreSQL expression forms.

#:drop-view follows the standard drop pattern:

(sql->string '((#:drop-view active-users)))
⇒ ("DROP VIEW active_users")

(sql->string '((#:drop-view active-users #:if-exists #:cascade)))
⇒ ("DROP VIEW IF EXISTS active_users CASCADE")

8.6 CREATE TRIGGER / DROP TRIGGER ¶

The header is standard SQL; the body is dialect-specific and lives in the dialect module (see BEGIN … END trigger body for SQLite’s BEGIN … END form).

#:create-trigger’s args may appear in any order:

The trigger name (bare symbol).

Timing: #:before, #:after, or #:instead-of.

Event: #:insert, #:delete, (#:update), or

(#:update-of col …).

Target table: (on TABLE).

Optional modifiers: #:if-not-exists, #:for-each-row.

Optional (#:when EXPR) — WHEN predicate.

(sql->string '((#:create-trigger t #:after #:insert (on tbl))))
⇒ ("CREATE TRIGGER t AFTER INSERT ON tbl")

(sql->string
  '((#:create-trigger t #:after (#:update-of title desc) (on bookmarks)
                      #:for-each-row
                      (#:when (#:!= NEW.updated-at OLD.updated-at)))))
⇒ ("CREATE TRIGGER t AFTER UPDATE OF title, desc ON bookmarks FOR EACH ROW WHEN NEW.updated_at != OLD.updated_at")

The #:when expression and the trigger body (via dialect’s #:begin) inline scalars — triggers cannot carry bound parameters in any supported dialect.

#:drop-trigger follows the standard drop pattern:

(sql->string '((#:drop-trigger t)))               ⇒ ("DROP TRIGGER t")
(sql->string '((#:drop-trigger t #:if-exists)))   ⇒ ("DROP TRIGGER IF EXISTS t")

8.7 CREATE TABLE AS ¶

#:create-table-as creates a table from a query. Pair with #:with-data to control whether data is loaded:

(sql->string
  '((#:create-table-as archived-users)
    (#:select id name) (#:from users) (#:where (#:= active #f))))
⇒ ("CREATE TABLE archived_users AS SELECT id, name FROM users WHERE active = $1" #f)

(sql->string
  '((#:create-table-as t #:if-not-exists)
    (#:select *) (#:from src)))
⇒ ("CREATE TABLE IF NOT EXISTS t AS SELECT * FROM src")

(sql->string
  '((#:create-table-as t)
    (#:select *) (#:from src) (#:with-data #f)))
⇒ ("CREATE TABLE t AS SELECT * FROM src WITH NO DATA")

8.8 TRUNCATE ¶

#:truncate accepts one or more table names followed by options:

(sql->string '((#:truncate users)))
⇒ ("TRUNCATE TABLE users")

(sql->string
  '((#:truncate users orders #:restart-identity #:cascade)))
⇒ ("TRUNCATE TABLE users, orders RESTART IDENTITY CASCADE")

8.9 PostgreSQL-specific DDL ¶

Materialized views, extensions, COMMENT ON, and CREATE OR REPLACE VIEW are PostgreSQL-specific and require loading (sql pg). See PostgreSQL expression forms.

8.10 SQLite-specific DDL ¶

CREATE VIRTUAL TABLE (for FTS5, RTree, and other virtual-table modules) and the SQLite BEGIN … END trigger body are SQLite-specific and require loading (sql sqlite). See SQLite Extensions.

9.1 SQLite BEGIN modes ¶

With (sql sqlite) loaded, #:begin-transaction also accepts SQLite’s lock-acquisition keywords:

Any other form delegates to the standard handler, so plain (#:begin-transaction) and the ISOLATION LEVEL forms continue to work with (sql sqlite) loaded (even though SQLite itself will reject the latter at execution time).

9.2 Savepoints ¶

Nested transaction scopes are supported via three clauses:

Savepoints are SQL-standard and supported by both PostgreSQL and SQLite. Names go through the usual identifier conversion (kebab→snake), so (#:savepoint before-migration) becomes SAVEPOINT before_migration.

Next: replace-clause, Up: Composing Queries   [Contents][Index]

10.1 sql-merge ¶

sql-merge merges multiple queries clause-by-clause. Output is always in canonical clause order.

Note: sql-merge is designed for DML queries (SELECT, INSERT, UPDATE, DELETE). DDL statements (CREATE TABLE, ALTER TABLE, etc.) are not composable in the same way and should be built directly as clause lists.

Each clause’s merge strategy is declared in the clause registry via register-clause! (see Extending guile-sql). The built-in strategies are:

AND-combine flattening prevents nested #:and accumulation when merging multiple #:where conditions:

(sql-merge
  '((#:where (#:and (#:= a 1) (#:= b 2))))
  '((#:where (#:= c 3))))
⇒ ((#:where (#:and (#:= a 1) (#:= b 2) (#:= c 3))))

Because queries are just data, composable filter functions are plain Scheme procedures:

(define (active-only q)   (sql-merge q '((#:where (#:= active #t)))))
(define (admin-only q)    (sql-merge q '((#:where (#:= role "admin")))))
(define (paginate q p sz) (sql-merge q `((#:limit ,sz) (#:offset ,(* p sz)))))

;; Compose freely — the AND conditions are flattened automatically
(sql->string (paginate (admin-only (active-only base)) 2 20))
⇒ (... "WHERE (active = $1) AND (role = $2) LIMIT $3 OFFSET $4" ...)

Next: Writing clause lists, Previous: sql-merge, Up: Composing Queries   [Contents][Index]

10.2 replace-clause ¶

replace-clause replaces a single clause in a query, preserving clause order. If the clause does not exist it is appended.

;; Replace WHERE entirely
(replace-clause q '#:where '(#:= id 99))
⇒ ... (#:where (#:= id 99)) ...

;; Replace SELECT columns
(replace-clause q '#:select 'id 'name)
⇒ ... (#:select id name) ...

;; Add LIMIT (was absent)
(replace-clause q '#:limit 5)
⇒ ... (#:limit 5)

Next: Pretty-printing, Previous: replace-clause, Up: Composing Queries   [Contents][Index]

10.3 Writing clause lists ¶

guile-sql queries are alists of (keyword . args) pairs; write them as literals or with quasiquote:

;; Static literal
'((#:select a b c) (#:from users) (#:where (#:= id 42)))

;; Dynamic with quasiquote
`((#:select id name) (#:from users) (#:where (#:= user-id ,uid)))

;; Composing with sql-merge
(sql-merge base-query
  `((#:where (#:= user-id ,uid))
    (#:limit ,page-size)
    (#:offset ,(* page page-size))))
⇒ ((#:select id name) (#:from users) (#:where (#:= user-id 42)) (#:limit 10) (#:offset 20))

Earlier versions of (sql helpers) exported clause-constructor procedures like (select 'a 'b) that produced '((#:select a b)). These were removed because their names shadowed core Scheme bindings (select, from, where, columns, update, for …), so every program that imported (sql) triggered "overrides core binding" warnings. Literal and quasiquote forms are short enough that the sugar didn’t carry its weight. sql-merge plus replace-clause continue to do the composition work.

Previous: Writing clause lists, Up: Composing Queries   [Contents][Index]

10.4 Pretty-printing ¶

Pass #:pretty #t to sql->string for Mozilla SQL Style Guide formatting. Keywords are left-aligned on their own line; arguments are indented two spaces.

(sql->string
  '((#:select submission-date active)
    (#:from sample)
    (#:where (#:and (#:> submission-date "2018-01-01")
                  (#:= active #t)))
    (#:limit 10))
  #:pretty #t)

Produces:

SELECT
  submission_date,
  active
FROM
  sample
WHERE
  submission_date > $1
  AND active = $2
LIMIT
  $3

The AND/OR line-break rule only applies to the top-level condition in WHERE/HAVING. Nested boolean expressions stay inline.

Next: (sql core) — formatter, Up: API Reference   [Contents][Index]

11.1 (sql) — public module ¶

The (sql) module re-exports the public API from (sql core) and (sql helpers). Load (sql pg) or (sql sqlite) separately for dialect-specific features. Dialect authors should import (sql dialect) instead of (sql core) directly — it exposes a curated set of registries and formatter helpers intended for extension. See (sql dialect) — dialect-author API.

;; Bare import is fine: (sql) exports a small handful of names
;; (sql->string, placeholder-*, register-*, sql-merge,
;; replace-clause) and shadows no core bindings.
(use-modules (sql))

;; Prefix import is still an option if you prefer explicit scoping.
(use-modules ((sql)    #:prefix sql:)
             ((sql pg) #:prefix sql:))

Next: (sql dialect) — dialect-author API, Previous: (sql) — public module, Up: API Reference   [Contents][Index]

11.2 (sql core) — formatter ¶

Function: sql->string query [#:pretty #f] [#:placeholder placeholder-dollar] ¶

Format query (a clause list) into a list (sql-string param …). Parameters are raw Scheme values in the order they appear in the output.

(sql->string '((#:select a b) (#:from users) (#:where (#:= id 42))))
⇒ ("SELECT a, b FROM users WHERE id = $1" 42)

With #:pretty #t, produces Mozilla SQL Style Guide output. See Pretty-printing.

#:placeholder controls the placeholder style. It is a procedure that takes a 1-based parameter index and returns the placeholder string. Built-in options:

(sql->string query #:placeholder placeholder-dollar)    ; $1, $2, ... (default)
(sql->string query #:placeholder placeholder-question)  ; ?, ?, ...
(sql->string query #:placeholder placeholder-colon)     ; :1, :2, ...

;; Custom:
(sql->string query
  #:placeholder (lambda (n) (string-append "@p" (number->string n))))
;; @p1, @p2, ...

Function: format-expr expr state ¶

Low-level expression formatter. state is a parameter accumulator created with make-state. Returns (values sql-string new-state). Useful when implementing custom clause handlers. Available to dialect authors via (sql dialect). See Extending guile-sql.

Function: make-state [#:placeholder placeholder-dollar] [#:inline? #f] ¶

Create a fresh parameter accumulator state. #:placeholder is a procedure that takes a 1-based index and returns a placeholder string. #:inline?, when true, makes state-add-param render scalars as SQL literals via inline-sql-value rather than pushing them onto the parameter list; used by DDL contexts that don’t accept bound parameters (see Inline context).

Function: placeholder-dollar n ¶

PostgreSQL-style: $1, $2, …

Function: placeholder-question n ¶

MySQL/SQLite-style: ?, ?, …

Function: placeholder-colon n ¶

Oracle-style: :1, :2, …

Function: state-counter state ¶

Return the number of parameters accumulated in state so far.

Function: state-params state ¶

Return the accumulated parameter values of state in order.

Function: state-inline? state ¶

Return #t if state’s inline flag is set — scalars passed to state-add-param will render as SQL literals rather than being parameterised.

Function: state-with-inline state flag ¶

Return a copy of state with the inline flag set to flag (a boolean). Preserves the parameter counter, placeholder procedure, and accumulated params so that entering and exiting an inline scope composes cleanly.

Function: in-inline-scope state proc ¶

Call proc with state’s inline flag set to #t. proc must return (values sql state); the caller’s inline flag is restored on the returned state so nested inline blocks compose cleanly. Clause handlers for DDL column-spec contexts use this to push the flag for the duration of their formatting.

(register-clause! #:my-ddl
  #:handler
  (lambda (args state pretty next)
    (in-inline-scope state
      (lambda (st)
        (format-expr (car args) st)))))

See Inline context for the full set of clauses that push an inline scope by default.

Inline context ¶

Certain DDL contexts don’t accept bound parameters. SQLite rejects parameterised DDL outright, and migration files need to be re-runnable without a params vector. The following clauses push an inline scope for the extent of their sub-formatting, so scalars inside render as SQL literals:

• #:with-columns — column defaults, CHECK expressions, and table-level constraints.
• #:add-column (ALTER TABLE).
• #:alter-column — including #:set-default.
• #:add-constraint — including the CHECK form.
• #:create-trigger — header and any #:when clause.
• #:begin (SQLite trigger body) — all statements inside BEGIN … END.

The scope propagates through function-call arguments, so (datetime "now") inside a column default emits DATETIME('now') with 'now' inline.

Function: register-op! sym [#:type ’infix] [#:token #f] ¶

Register a new operator sym. type is one of infix (binary), infix* (variadic, with outer parens), infix-join (variadic, no parens), or prefix. token defaults to the uppercased name of sym without the leading :.

(register-op! #:ilike #:type 'infix #:token "ILIKE")
(register-op! #:~     #:type 'infix #:token "~")

See Extending guile-sql.

Function: register-form! sym handler ¶

Register a new keyword expression form sym. handler is (lambda (args state) …) returning (values sql-string new-state).

Form handlers are checked before operators in expression dispatch, so they can implement structural forms (like #:between or #:case) that need custom argument handling beyond what simple operator types support.

(register-form! #:current-date
  (lambda (args state)
    (values "CURRENT_DATE" state)))

See Extending guile-sql.

Function: register-clause! sym [#:handler #f] [#:statement-type #f] [#:after #f] [#:before #f] [#:merge-strategy #f] ¶

Register clause sym. handler is (lambda (args state pretty next) …) returning (values sql-string new-state). Calling next delegates to the previously-registered handler (raises if none).

statement-type is one of any (default), select, insert, update, delete, set-op, or ddl. A non-any type classifies queries containing the clause. When multiple typed clauses coexist, the most specific wins (ddl > insert/update/delete/set-op > select).

after / before splice the clause relative to an anchor keyword in the ordering (mutually exclusive; missing anchor errors at registration time). Without either, the clause is appended.

merge-strategy is one of concat (concatenate args), and-combine (AND-combine, like #:where), or last-write-wins (default, for sql-merge).

On re-registration, statement-type and merge-strategy inherit from the previous entry when not supplied.

See Extending guile-sql.

Function: clause-merge-strategy kw ¶

Return the merge strategy for a registered clause kw, or #f.

Function: clause-statement-type kw ¶

Return the statement type for a registered clause kw, or #f.

Next: (sql util) — utilities, Previous: (sql core) — formatter, Up: API Reference   [Contents][Index]

11.3 (sql dialect) — dialect-author API ¶

Curated extension surface for dialect authors. Re-exports:

• Registries: register-op!, register-form!, register-clause!, clause-merge-strategy, clause-statement-type.
• Parameter state: make-state, state-counter, state-params, state-inline?, state-with-inline, state-add-param, in-inline-scope.
• Expression formatters: format-expr, format-expr-list, format-list, format-subquery, query-alist?.
• Sub-formatters: format-sub-query (inner query, e.g. trigger body) and format-sub-clause (nested clause, e.g. inline ALTER).
• Clause-builder helpers: format-csv-clause, format-single-clause, format-table-csv-clause.
• Identifier utilities: identifier->sql, escape-double-quotes, escape-single-quotes, inline-sql-value.

(sql pg) and (sql sqlite) are built entirely on this module; no privileged core access is required to write a new dialect.

(use-modules (sql dialect))

;; Register a PostgreSQL-specific regex match operator.
(register-op! #:~  #:type 'infix #:token "~")

;; Register a new DDL clause.
(register-clause! #:create-schema
  #:statement-type 'ddl
  #:after '#:create-table
  #:handler
  (lambda (args state pretty next)
    (values (string-append "CREATE SCHEMA " (identifier->sql (car args)))
            state)))

Next: (sql helpers) — query composition, Previous: (sql dialect) — dialect-author API, Up: API Reference   [Contents][Index]

11.4 (sql util) — utilities ¶

Function: identifier->sql sym ¶

Convert symbol sym to a SQL identifier string. Splits on ., converts each segment from kebab-case to snake_case, and SQL double-quotes any segment containing characters outside [a-zA-Z0-9_]. Double-quote characters within segments are escaped per the SQL standard (""). See Identifier conversion.

Next: (sql tcl) — transaction control, Previous: (sql util) — utilities, Up: API Reference   [Contents][Index]

11.5 (sql helpers) — query composition ¶

Function: sql-merge query … ¶

Merge one or more queries into one using clause-aware strategies. Output is in canonical clause order. See sql-merge.

Function: replace-clause query clause-name arg … ¶

Replace or append clause clause-name in query. See replace-clause.

Next: (sql pg) — PostgreSQL, Previous: (sql helpers) — query composition, Up: API Reference   [Contents][Index]

11.6 (sql tcl) — transaction control ¶

Registers #:begin-transaction, #:commit, and #:rollback clauses. Loaded automatically by (sql); has no exports. See Transactions.

Next: (sql sqlite) — SQLite, Previous: (sql tcl) — transaction control, Up: API Reference   [Contents][Index]

11.7 (sql pg) — PostgreSQL ¶

Registers PostgreSQL-specific operators, expression forms, and DDL clause handlers. Has no exports — loading the module is sufficient. See PostgreSQL Extensions.

Previous: (sql pg) — PostgreSQL, Up: API Reference   [Contents][Index]

11.8 (sql sqlite) — SQLite ¶

Registers SQLite-specific operators (#:match) and DDL clause handlers (#:create-virtual-table, virtual-table #:using, #:begin trigger body, BEGIN lock-mode override). Has no exports — loading the module is sufficient. See SQLite Extensions.

Next: PostgreSQL expression forms, Up: PostgreSQL Extensions   [Contents][Index]

12.1 Loading (sql pg) ¶

(use-modules (sql pg))

Loading (sql pg) registers PostgreSQL-specific operators, expression forms, and DDL clauses into the (sql core) dispatch table.

Next: JSONB operators, Previous: Loading (sql pg), Up: PostgreSQL Extensions   [Contents][Index]

12.2 PostgreSQL expression forms ¶

ARRAY constructor ¶

#:array produces PostgreSQL ARRAY expressions:

(use-modules (sql pg))

(sql->string '((#:select (#:array 1 2 3))))
⇒ ("SELECT ARRAY[$1, $2, $3]" 1 2 3)

(sql->string '((#:select (#:array ((#:select id) (#:from users))))))
⇒ ("SELECT ARRAY(SELECT id FROM users)")

With multiple arguments, values are parameterized inside square brackets. With a single subquery argument, the subquery is wrapped in parentheses (the PostgreSQL ARRAY(subquery) form).

INTERVAL literal ¶

#:interval produces a PostgreSQL INTERVAL literal. The string value is always inlined (not parameterized) because PostgreSQL does not accept parameterized interval strings in all contexts:

(use-modules (sql pg))

(sql->string '((#:select (#:interval "30 days"))))
⇒ ("SELECT INTERVAL '30 days'")

(sql->string '((#:select (#:interval "1 hour"))))
⇒ ("SELECT INTERVAL '1 hour'")

AT TIME ZONE ¶

#:at-time-zone formats the SQL AT TIME ZONE expression:

(use-modules (sql pg))

(sql->string
  '((#:select (#:at-time-zone created-at "UTC")) (#:from t)))
⇒ ("SELECT created_at AT TIME ZONE $1 FROM t" "UTC")

(sql->string
  '((#:select (#:at-time-zone (now) "America/New_York"))))
⇒ ("SELECT NOW() AT TIME ZONE $1" "America/New_York")

PostgreSQL-specific DDL ¶

The following DDL clauses are PostgreSQL-specific and registered by (sql pg).

#:create-or-replace-view:

(use-modules (sql pg))

(sql->string
  '((#:create-or-replace-view active-users)
    (#:select *) (#:from users)))
⇒ ("CREATE OR REPLACE VIEW active_users AS SELECT * FROM users")

Materialized views (#:create-materialized-view, #:refresh-materialized-view, #:drop-materialized-view):

(use-modules (sql pg))

(sql->string
  '((#:create-materialized-view mv-users)
    (#:select *) (#:from users)))
⇒ ("CREATE MATERIALIZED VIEW mv_users AS SELECT * FROM users")

(sql->string '((#:refresh-materialized-view mv-users #:concurrently)))
⇒ ("REFRESH MATERIALIZED VIEW CONCURRENTLY mv_users")

(sql->string
  '((#:drop-materialized-view mv-users #:if-exists #:cascade)))
⇒ ("DROP MATERIALIZED VIEW IF EXISTS mv_users CASCADE")

Extensions (#:create-extension, #:drop-extension):

(use-modules (sql pg))

(sql->string '((#:create-extension pgcrypto #:if-not-exists)))
⇒ ("CREATE EXTENSION IF NOT EXISTS pgcrypto")

(sql->string '((#:drop-extension pgcrypto #:if-exists #:cascade)))
⇒ ("DROP EXTENSION IF EXISTS pgcrypto CASCADE")

#:comment-on takes a target specifier and a string (inlined with escaping):

(use-modules (sql pg))

(sql->string '((#:comment-on (#:table users) "The users table")))
⇒ ("COMMENT ON TABLE users IS 'The users table'")

(sql->string
  '((#:comment-on (#:column users.email) "User's email address")))
⇒ ("COMMENT ON COLUMN users.email IS 'User''s email address'")

#:concurrently and (#:using method) on #:create-index / #:drop-index also require (sql pg). See DDL — Data Definition Language.

Next: Other PostgreSQL operators, Previous: PostgreSQL expression forms, Up: PostgreSQL Extensions   [Contents][Index]

12.3 JSONB operators ¶

#:has-key?, #:has-any-key?, #:has-all-keys?, #:json-path, #:json-path-text, and #:jsonb-delete-path use descriptive names because the corresponding SQL operators (?, ?|, ?&, #>, #>>, #-) either cannot appear in a Guile keyword or trigger the reader’s vertical-bar symbol escape (|…|).

Previous: JSONB operators, Up: PostgreSQL Extensions   [Contents][Index]

12.4 Other PostgreSQL operators ¶

(#:ilike name "%foo%")

name ILIKE $1 — case-insensitive LIKE.

(#:not-ilike name "%foo%")

name NOT ILIKE $1

(#:regex-match col "^pat")

col ~ $1 — case-sensitive POSIX regex match.

(#:regex-imatch col "^pat")

col ~* $1 — case-insensitive POSIX regex match.

(#:regex-not-match col "^pat")

col !~ $1 — negated case-sensitive match.

(#:regex-not-imatch col "^pat")

col !~* $1 — negated case-insensitive match.

(#:&& a b)

a && b — array overlap.

(#:bit-xor a b)

a # b — bitwise XOR. SQLite has no SQL-level XOR operator; this is PostgreSQL-only.

(#:select-distinct-on (col …) col …)

SELECT DISTINCT ON (col, …) col, …

• LIKE / ILIKE with ESCAPE

(sql->string
  '((#:select *) (#:from t)
    (#:where (#:like-escape col "10\\%" "\\"))))
⇒ ("SELECT * FROM t WHERE col LIKE $1 ESCAPE $2" "10\\%" "\\")

(use-modules (sql pg))

(sql->string
  '((#:select *) (#:from t)
    (#:where (#:ilike-escape col "A\\_" "\\"))))
⇒ ("SELECT * FROM t WHERE col ILIKE $1 ESCAPE $2" "A\\_" "\\")

Next: MATCH operator, Up: SQLite Extensions   [Contents][Index]

13.1 Loading (sql sqlite) ¶

(use-modules (sql sqlite))

Loading (sql sqlite) registers SQLite-specific operators and DDL clauses into the (sql core) dispatch table.

Next: CREATE VIRTUAL TABLE, Previous: Loading (sql sqlite), Up: SQLite Extensions   [Contents][Index]

13.2 MATCH operator ¶

#:match is the SQLite infix MATCH operator, used for FTS5 full-text queries and RTree geometry tests:

(use-modules (sql sqlite))

(sql->string
  '((#:select *) (#:from bookmarks)
    (#:where (#:match title "bookmarks"))))
⇒ ("SELECT * FROM bookmarks WHERE title MATCH $1" "bookmarks")

(sql->string
  '((#:select rowid)
    (#:from bookmarks-fts)
    (#:where (#:match bookmarks-fts "guile"))))
⇒ ("SELECT rowid FROM bookmarks_fts WHERE bookmarks_fts MATCH $1" "guile")

In FTS5 the right-hand side is an FTS5 query string; in RTree it’s a coordinate tuple. Either way, the query is parameterized.

• GLOB and REGEXP

Next: BEGIN … END trigger body, Previous: MATCH operator, Up: SQLite Extensions   [Contents][Index]

13.3 CREATE VIRTUAL TABLE ¶

#:create-virtual-table introduces a SQLite virtual table; the #:using-module clause specifies the module name and its arguments. Module arguments take one of these shapes:

col-name

A bare symbol — a column (kebab → snake_case).

(col-name)

Also a column.

(key value)

A key/value option; the value is inlined. Strings are single-quoted with escaping, symbols are emitted as identifiers, numbers render directly.

FTS5 external-content table:

(use-modules (sql sqlite))

(sql->string
  '((#:create-virtual-table bookmarks-fts)
    (#:using-module (fts5 (title) (description) (url)
                          (content "bookmarks")
                          (content-rowid "id")
                          (tokenize "porter unicode61")))))
⇒ ("CREATE VIRTUAL TABLE bookmarks_fts USING fts5(title, description, url, content='bookmarks', content_rowid='id', tokenize='porter unicode61')")

RTree index:

(use-modules (sql sqlite))

(sql->string
  '((#:create-virtual-table demo-index)
    (#:using-module (rtree (id) (minX) (maxX) (minY) (maxY)))))
⇒ ("CREATE VIRTUAL TABLE demo_index USING rtree(id, minX, maxX, minY, maxY)")

#:if-not-exists is supported:

(use-modules (sql sqlite))

(sql->string
  '((#:create-virtual-table bookmarks-fts #:if-not-exists)
    (#:using-module (fts5 (title)))))
⇒ ("CREATE VIRTUAL TABLE IF NOT EXISTS bookmarks_fts USING fts5(title)")

Legacy #:using shape ¶

Earlier versions of guile-sql reused #:using for both CREATE VIRTUAL TABLE USING … and DELETE … USING …, dispatching on the argument shape. That shim is still in place, so existing code continues to work:

'((#:create-virtual-table demo)
  (#:using (rtree (id) (minX) (maxX))))  ; legacy; still valid

New code should prefer #:using-module for the virtual-table case. A future major release may drop the overloaded #:using handler.

Previous: CREATE VIRTUAL TABLE, Up: SQLite Extensions   [Contents][Index]

13.4 BEGIN … END trigger body ¶

The CREATE TRIGGER header and DROP TRIGGER live in (sql ddl) as standard DDL (see Triggers). The body is dialect-specific: SQLite uses a BEGIN … END block containing SQL statements, while PostgreSQL uses EXECUTE FUNCTION. (sql sqlite) registers the #:begin clause for SQLite’s form.

#:begin takes one or more DML queries and joins them with semicolons inside BEGIN … END. Inside the body, trigger-local identifiers like NEW.id and OLD.title are emitted as bare symbols — identifier->sql preserves uppercase and handles the dot as a segment separator.

The trigger body inlines scalars — SQLite doesn’t accept bound parameters inside CREATE TRIGGER, and migration files need to be re-runnable without a params vector. String and numeric values in VALUES clauses, SET expressions, and WHERE clauses therefore render as SQL literals.

'((#:create-trigger (t #:after #:insert (on tbl)))
  (#:begin
    ((#:delete-from other))))
⇒ CREATE TRIGGER t AFTER INSERT ON tbl BEGIN DELETE FROM other; END

FTS5 external-content sync trigger on UPDATE. FTS5 requires the 'delete' command with OLD values before re-inserting NEW values, or stale tokens stay indexed. The "delete" string is emitted as the SQL literal 'delete' thanks to the trigger-body inline context:

'((#:create-trigger (bookmarks-fts-au
                     #:after (#:update-of title description url)
                     (on bookmarks) #:for-each-row))
  (#:begin
    ((#:insert-into bookmarks-fts)
     (#:columns bookmarks-fts rowid title description url)
     (#:values ("delete" OLD.id OLD.title OLD.description OLD.url)))
    ((#:insert-into bookmarks-fts)
     (#:columns rowid title description url)
     (#:values (NEW.id NEW.title NEW.description NEW.url)))))

Next: guile-sqlite3 (SQLite), Up: Database Integration   [Contents][Index]

14.1 guile-squee (PostgreSQL) ¶

guile-squee’s exec-query expects a SQL string and a list of string parameters (or #f for SQL NULL). Values must be coerced from Scheme types to strings:

(use-modules (sql) (sql pg) (ice-9 match))

(define (value->squee-string v)
  "Coerce a Scheme value for guile-squee's exec-query."
  (cond
    ((string? v)    v)
    ((number? v)    (number->string v))
    ((eq? v #t)     "t")
    ((eq? v #f)     "f")
    ((eq? v #:null) #f)
    (else (error "unsupported param type" v))))

(define (exec-sql conn query)
  "Format QUERY and execute it via guile-squee."
  (match (sql->string query)
    ((sql . params)
     (exec-query conn sql (map value->squee-string params)))))

;; Usage:
(exec-sql conn
  '((#:select id name)
    (#:from users)
    (#:where (#:= active #t))))

Previous: guile-squee (PostgreSQL), Up: Database Integration   [Contents][Index]

14.2 guile-sqlite3 (SQLite) ¶

guile-sqlite3 uses ? placeholders and accepts most native Scheme types via sqlite-bind, but rejects raw booleans (“unexpected value”). Use placeholder-question to generate ? placeholders, and coerce booleans at the driver boundary:

(use-modules (sql) (sqlite3) (srfi srfi-1) (ice-9 match))

(define (value->sqlite v)
  "Coerce a Scheme value for guile-sqlite3's sqlite-bind."
  (cond
    ((eq? v #t) 1)
    ((eq? v #f) 0)
    (else v)))

(define (exec-sql db query)
  "Format QUERY and execute it via guile-sqlite3."
  (match (sql->string query #:placeholder placeholder-question)
    ((sql . params)
     (let ((stmt (sqlite-prepare db sql)))
       (for-each (lambda (i val)
                   (sqlite-bind stmt (+ i 1) (value->sqlite val)))
                 (iota (length params))
                 params)
       (let ((results (sqlite-map identity stmt)))
         (sqlite-finalize stmt)
         results)))))

;; Usage:
(exec-sql db
  '((#:select id name)
    (#:from users)
    (#:where (#:= active #t))))

#t in the #:where clause above becomes integer 1 via value->sqlite, matching how SQLite stores booleans. The coercion lives in caller code rather than in guile-sql to keep the library focused on pure SQL generation (see Features).

15.1 Custom operators ¶

register-op! adds a new operator to the global dispatch table:

;; PostgreSQL regex operators
(register-op! #:~   #:type 'infix #:token "~")
(register-op! #:~*  #:type 'infix #:token "~*")
(register-op! #:!~  #:type 'infix #:token "!~")

;; Full-text search
(register-op! #:@  #:type 'infix #:token "@")
(register-op! #:@ #:type 'infix #:token "@)̈

15.2 Custom expression forms ¶

register-form! adds a new keyword expression form. Form handlers are checked before operators, so they can implement structural forms that need custom argument handling:

(use-modules (srfi srfi-11) (sql dialect))

;; Add ARRAY[...] constructor
(register-form! #:pg-array
  (lambda (args state)
    (let-values (((sqls st) (format-expr-list args state)))
      (values (string-append "ARRAY[" (string-join sqls ", ") "]") st))))

(sql->string '((#:select (#:pg-array 1 2 3))))
⇒ ("SELECT ARRAY[$1, $2, $3]" 1 2 3)

Note: the handler receives (args state) and must return (values sql-string new-state). Use format-expr to format sub-expressions, threading the state.

15.3 Custom clauses ¶

register-clause! adds a new top-level clause. The handler receives (args state pretty next):

(use-modules (srfi srfi-11) (sql dialect))  ; let-values, register-clause!

(register-clause! #:fetch
  #:statement-type 'any
  #:handler
  (lambda (args state pretty next)
    (let-values (((n st) (format-expr (car args) state)))
      (values (string-append "FETCH FIRST " n " ROWS ONLY") st))))

;; Now usable in queries:
(sql->string '((#:select *) (#:from t) (#:fetch 10)))
⇒ ("SELECT * FROM t FETCH FIRST $1 ROWS ONLY" 10)

Statement type and ordering ¶

#:statement-type is one of 'any (default), 'select, 'insert, 'update, 'delete, 'set-op, or 'ddl. A non-'any type classifies the query containing the clause as that type.

#:after / #:before position a clause relative to an existing anchor in the ordering:

(register-clause! #:create-schema
  #:statement-type 'ddl
  #:after '#:create-table
  #:handler
  (lambda (args state pretty next)
    (values (string-append "CREATE SCHEMA " (identifier->sql (car args)))
            state)))

Overriding existing clauses ¶

Re-registering a keyword chains the new handler in front of the previous one. Call next to delegate; raises if the chain is exhausted. (sql pg) uses this for #:create-index to layer on CONCURRENTLY and USING method:

(register-clause! #:create-index
  #:handler
  (lambda (args state pretty next)
    (let-values (((idx-name tbl-name options columns)
                  (extract-index-parts (car args))))
      (if (memq #:concurrently options)
          (format-pg-concurrent-index ...)
          (next)))))

Merge strategies ¶

#:merge-strategy controls how sql-merge combines duplicate clauses. Available: concat (concatenate args), and-combine (AND-combine, like #:where), last-write-wins (default).

(register-clause! #:hints
  #:handler
  (lambda (args state pretty next)
    (values (string-append "/*+ " (string-join args ", ") " */") state))
  #:merge-strategy 'concat)

(sql pg) and (sql sqlite) are canonical examples.

Next: One clause entry per join type, Up: Pitfalls   [Contents][Index]

16.1 SQL NULL and #:null ¶

There are three distinct things that look “null-ish” in guile-sql:

#:null

The SQL NULL literal. Inlined verbatim; not parameterized. Use this when you mean SQL NULL.

#f

The Scheme false value. Treated as a parameterized value ($N). This is the Scheme boolean false, not SQL NULL. How #f is sent to the database depends on your driver (see Database Integration).

(#:= col #:null)

Auto-converts to col IS NULL. Using (#:= col #f) does not convert — it produces col = $1 with #f as the parameter value.

Next: Subqueries in FROM require an alias, Previous: SQL NULL and #:null, Up: Pitfalls   [Contents][Index]

16.2 One clause entry per join type ¶

Because queries are keyed lists, there can be only one entry per key. assq on a query with two #:left-join entries would only find the first one.

Use the alternating-pairs convention to express multiple joins of the same type within a single clause entry:

;; Correct: one :left-join clause with alternating pairs
'((#:left-join (#:as t1 a) (#:on (#:= x.id t1.x-id))
              (#:as t2 b) (#:on (#:= x.id t2.x-id))))

;; Wrong: two :left-join entries — second is invisible to assq
'((#:left-join (#:as t1 a) (#:on (#:= x.id t1.x-id)))
  (#:left-join (#:as t2 b) (#:on (#:= x.id t2.x-id))))  

sql-merge handles the alternating-pairs convention correctly when merging two queries that each have a #:left-join entry.

Previous: One clause entry per join type, Up: Pitfalls   [Contents][Index]

16.3 Subqueries in FROM require an alias ¶

PostgreSQL requires that every subquery in a FROM clause have an alias. Always wrap subqueries in FROM with #:as:

;; Correct
`((#:select *)
  (#:from (#:as ,active-users u))     (#:where (#:> u.age 18)))

;; Wrong: PostgreSQL will reject this
`((#:select *)
  (#:from ,active-users)             (#:where (#:> age 18)))

guile-sql itself will render both forms without error — the constraint is PostgreSQL’s, not guile-sql’s.

17.1 Why data-driven SQL? ¶

There are several ways to build SQL in a programming language, each with different tradeoffs.

The simplest is string concatenation: assemble the SQL text directly using format or string-append, interpolating values along the way. This is flexible and requires no library, but the resulting string cannot be decomposed, inspected, or combined with another query. And because values and SQL syntax live in the same string, any lapse in escaping is a potential injection vulnerability.

A step up is a macro-based SQL builder, where S-expressions expand into SQL strings at compile time. The syntax is pleasant to write, and the primary interface is convenient for static queries. Dynamic queries are possible — Common Lisp’s S-SQL, for example, provides a runtime compilation function and a template mechanism — but they require switching to a different API. Composing queries means manually assembling lists with append and quasiquote rather than working with an inherently data-oriented representation. The additional machinery works, but it is a workaround for the macro system rather than a natural consequence of the design.

ORMs and active record patterns take a different approach entirely, mapping database tables to objects and generating SQL internally. These are effective for routine CRUD operations, but complex queries — window functions, CTEs, set operations — often require dropping down to raw SQL.

guile-sql takes a fourth approach: queries are plain association lists that happen to describe SQL. The lists are not evaluated, expanded, or compiled until you call sql->string. Before that point they are ordinary Scheme data, subject to all the usual list operations. This means the full power of Scheme — assq, map, filter, cons, quasiquote, higher-order functions — is available for building, inspecting, and transforming queries. guile-sql operates at the layer below an ORM: it generates the SQL that an ORM or active record library might use internally.

17.2 Why Guile keywords for SQL structure markers? ¶

SQL structure markers use Guile keywords (#:select, #:where, #:=). This is inspired by S-SQL (Common Lisp) and HoneySQL (Clojure), where :keyword is the keyword type. In Guile the equivalent is #:keyword.

Using the proper keyword type has concrete benefits: keyword? is an unambiguous predicate, query-alist? reduces to a single (keyword? (caar x)) check, and there is no risk of a SQL structure marker being accidentally confused with an identifier.

The shorter :symbol notation is available via Guile’s prefix keyword read syntax (see Keyword Read Syntax in Guile Reference Manual).

17.3 Why plain lists, not records? ¶

Records would prevent assq/filter/map/quasiquote manipulation. Queries must be plain data so that all standard Scheme list operations apply without a special API. This also means queries can be serialized, inspected, and transformed with any Scheme tool.

17.4 Why #:= #:null → IS NULL? ¶

x = NULL is never true in SQL, not even when x is NULL. The correct test is x IS NULL. Any application code that writes (#:= col #:null) almost certainly means IS NULL, so guile-sql converts it automatically, matching the behavior of HoneySQL. Use (#:raw "= NULL") if you genuinely need the standard (broken) SQL form.

17.5 Why one join clause per type? ¶

Multiple entries with the same key break assq. See One clause entry per join type.

17.6 Why sql-merge instead of threading helpers? ¶

Guile has no -> threading macro. Implicit argument-type overloading is not idiomatic Scheme. Explicit sql-merge is clear about what is happening, composable with regular functions, and its merge strategies are documented and predictable.

17.7 Out of scope ¶

The following are intentionally out of scope:

• Type-checked queries
• Named parameters ((#:param name) with a #:params map)
• Multi-dialect support (quoting styles, clause ordering per dialect)
• GROUPING SETS, CUBE, ROLLUP
• TABLESAMPLE, EXPLAIN, EXPLAIN ANALYZE
• Procedural-language function and trigger bodies (PL/pgSQL, PL/Python, etc.). SQLite’s DML-body triggers are supported by (sql sqlite); what is out of scope is embedding PL source inside a CREATE FUNCTION or CREATE TRIGGER … EXECUTE FUNCTION body.
• Driver-specific value coercion (#t/#f to 1/0, Scheme numbers to driver-native bytes, etc.). Coercion is a driver-boundary concern and lives in caller code.