guile-sql ¶

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

2.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   [Index]

2.2 Building from a release tarball ¶

./configure && make && make install

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

2.3 Building from Git ¶

./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   [Index]

3.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   [Index]

3.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   [Index]

3.3 Composable filter functions ¶

Plain functions that return partial alists 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 alists, Up: Data Model   [Index]

4.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, the q helper converts it at runtime. See q helper.

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   [Index]

4.2 Query alists ¶

A query is an association list of tagged clause lists. 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 alist. See Canonical clause ordering.

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

4.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 alist: ((#:select …) …) — first element is a list starting with a Guile keyword.
• Plain alist: ((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
`((#:select *)
  (#:from (#:as ,active-users u))
  (#:where (#:> u.age 18)))
⇒ SELECT * FROM (SELECT ...) AS u WHERE u.age > $1

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

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

4.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   [Index]

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

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

5.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   [Index]

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

'(#:* (#:+ a b) c)
⇒ (a + b) * c

'(#:not (#:and (#:= x 1) (#:= y 2)))
⇒ NOT ((x = $1) AND (y = $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   [Index]

5.4 Arithmetic and string operators ¶

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

5.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 alist is treated as a subquery.

'(#:in x 1 2 3)
⇒ x IN ($1, $2, $3)

'(#:in x "a" "b")
⇒ x IN ($1, $2)

'(#:in x ((#:select id) (#:from categories)))
⇒ x IN (SELECT id FROM categories)

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

'(#:in (#:composite id name) ((#:select id name) (#:from other)))
⇒ (id, name) IN (SELECT id, name FROM other)

#:distinct is used inside aggregate function calls:

'(count (#:distinct email))  ⇒ COUNT(DISTINCT email)

#:nest forces parenthesization around an expression:

'(#:nest (#:+ a b))  ⇒ (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   [Index]

5.6 Aliasing and ordering ¶

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

5.7 Conditionals ¶

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

'(#:case
   (#:= status "active")  "Active"
   (#:= status "pending") "Pending"
   #:else                 "Unknown")
⇒ CASE WHEN status = $1 THEN $2
     WHEN status = $3 THEN $4
     ELSE $5 END

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

'(#:case-expr status "active" "A" "pending" "P" #:else "?")
⇒ CASE status WHEN $1 THEN $2 WHEN $3 THEN $4 ELSE $5 END

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

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

'(count *)                     ⇒ COUNT(*)
'(now)                         ⇒ NOW()
'(coalesce a b "default")      ⇒ COALESCE(a, b, $1)
'(sum (#:* price quantity))     ⇒ SUM(price * quantity)

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

'(array-agg name (#:order-by (#:asc name)))
⇒ ARRAY_AGG(name ORDER BY name ASC)

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

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

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

`(#:= metadata (#:lift ,my-data))  ⇒ metadata = $1

(#: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).

'(#:inline "2024-01-15")  ⇒ '2024-01-15'
'(#:inline 42)            ⇒ 42
'(#:inline #t)            ⇒ TRUE

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

(#:quoted sym)

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

'(#:quoted user)         ⇒ "user"
'(#:quoted public.user)  ⇒ "public"."user"

(#:cast expr type)

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

'(#:cast x integer)            ⇒ CAST(x AS integer)
'(#:cast name "varchar(255)")  ⇒ CAST(name AS varchar(255))

Previous: Escape hatches, Up: Expressions   [Index]

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

'(#:over (sum salary)
        (#:partition-by department)
        (#:order-by (#:asc hired-at)))
⇒ 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:

'(#:over (sum salary)
        (#:partition-by department)
        (#:order-by (#:asc hired-at))
        (#:rows-between #:unbounded-preceding #:current-row))
⇒ 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 …):

'(#:filter (count *) (#:= status "active"))
⇒ COUNT(*) FILTER (WHERE status = $1)

#:within-group ¶

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

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

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:

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

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   [Index]

6.1 SELECT clauses ¶

#:select col …

SELECT col, …

#:select-distinct col …

SELECT DISTINCT col, …

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

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

#:from table …

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

#:where expr

WHERE expr.

#:group-by col …

GROUP BY col, …

#:having expr

HAVING expr.

#:window (name spec …) …

Named window definitions for #:over references.

'(#:window (w (#:partition-by dept) (#:order-by (#:desc salary))))
⇒ 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. Args: #:update, #:share, #:nowait, #:skip-locked.

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

6.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
'((#:left-join (#:as roles r) (#:on (#:= u.role-id r.id))))
⇒ LEFT JOIN roles AS r ON u.role_id = r.id

;; Multiple joins of the same type — one clause, alternating pairs
'((#:left-join (#:as t1 a) (#:on (#:= x.id t1.x-id))
              (#:as t2 b) (#:on (#:= x.id t2.x-id))))
⇒ 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
'((#:left-join payments (#:using id created-at)))
⇒ LEFT JOIN payments USING (id, created_at)

Supported join types: #:join / #:inner-join, #:left-join, #:right-join, #:full-join, #:cross-join.

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

6.3 INSERT clauses ¶

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

;; Multiple rows
'((#: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)

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

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
'((#:on-conflict #:do-nothing))
⇒ ON CONFLICT DO NOTHING

;; DO NOTHING on specific column
'((#:on-conflict (email) #:do-nothing))
⇒ ON CONFLICT (email) DO NOTHING

;; DO UPDATE SET
'((#:on-conflict (email)
    (#:do-update-set (name excluded.name)
                    (updated-at (now)))))
⇒ ON CONFLICT (email) DO UPDATE SET name = excluded.name, updated_at = NOW()

excluded.name is a plain dotted identifier; the EXCLUDED pseudo-table is referenced like any other table.

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

'((#:on-conflict (#:on-constraint uq-email) #:do-nothing))
⇒ ON CONFLICT ON CONSTRAINT uq_email DO NOTHING

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

'((#:on-conflict (email)
    (#:do-update-set (name excluded.name)
                     (#:where (#:= users.active #t)))))
⇒ ON CONFLICT (email)
   DO UPDATE SET name = excluded.name WHERE users.active = $1

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

6.4 UPDATE clauses ¶

'((#: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

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   [Index]

6.5 DELETE clauses ¶

'((#: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
'((#: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   [Index]

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

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

;; (name query)
`((#: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
`((#:with (active-users (id name) ,active-users-q))
  (#:select *)
  (#:from active-users))
⇒ WITH active_users(id, name) AS (SELECT ...)
         SELECT * FROM active_users

;; Multiple CTEs
`((#: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 ...), recent_orders AS (SELECT ...) \
SELECT active_users.name, recent_orders.total \
FROM active_users INNER JOIN recent_orders ON active_users.id = recent_orders.user_id" ...)

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

6.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   [Index]

6.8 Canonical clause ordering ¶

The formatter always sorts clauses into canonical SQL order regardless of how they appear in the input alist. 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

7.1 CREATE TABLE ¶

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

Use #:if-not-exists by wrapping the table name:

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

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)

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

7.2 DROP TABLE ¶

'(#:drop-table users)
⇒ DROP TABLE users

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

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

Options #:if-exists, #:cascade, and #:restrict can appear anywhere in the list alongside the table names.

7.3 ALTER TABLE ¶

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

'((#: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:

'((#: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).

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

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

DROP COLUMN ¶

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

'((#: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]).

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

'((#:alter-table t) (#:alter-column (active #:set-default #t)))
⇒ ALTER TABLE t ALTER COLUMN active SET DEFAULT $1

RENAME COLUMN / RENAME TABLE ¶

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

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

ADD CONSTRAINT / DROP CONSTRAINT ¶

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

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

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

;; FOREIGN KEY
'((#: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
'((#:alter-table t) (#:drop-constraint (uq-email #:if-exists #:cascade)))
⇒ ALTER TABLE t DROP CONSTRAINT IF EXISTS uq_email CASCADE

7.4 CREATE INDEX / DROP INDEX ¶

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

'(#: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):

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

'(#: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)
'(#:create-index (idx-lower-email users (lower email)))
⇒ CREATE INDEX idx_lower_email ON users (LOWER(email))

;; Column with ordering
'(#: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:

'(#:drop-index idx-email)
⇒ DROP INDEX idx_email

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

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

'((#: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

'((#: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:

'(#:drop-view active-users)
⇒ DROP VIEW active_users

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

7.6 CREATE TABLE AS ¶

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

'((#: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

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

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

7.7 TRUNCATE ¶

#:truncate can take a single table name or a list with multiple tables and options:

'(#:truncate users)
⇒ TRUNCATE TABLE users

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

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

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

8.1 sql-merge ¶

sql-merge merges multiple query alists 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 alists.

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: Clause constructor helpers, Previous: sql-merge, Up: Composing Queries   [Index]

8.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   [Index]

8.3 Clause constructor helpers ¶

Helpers are thin wrappers that produce single-clause alists. They reduce quoting noise when building queries dynamically with sql-merge:

;; With helpers
(sql-merge (select 'a 'b 'c) (from 'users) (where '(#:= id 42)))
⇒ ((#:select a b c) (#:from users) (#:where (#:= id 42)))

;; Equivalent without helpers
(sql-merge '((#:select a b c)) '((#:from users)) '((#:where (#:= id 42))))
⇒ ((#:select a b c) (#:from users) (#:where (#:= id 42)))

For static queries written as literals, helpers are unnecessary. For dynamic queries, quasiquote is idiomatic:

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

Helpers that match their clause keyword directly: select, select-distinct, from, where, order-by, limit, offset, group-by, having, join, inner-join, left-join, right-join, full-join, cross-join, insert-into, columns, on-conflict, returning, update, delete-from, with-recursive, union-all, intersect-all, except-all, window-def.

Some helpers are renamed to avoid shadowing Scheme core bindings:

Previous: Clause constructor helpers, Up: Composing Queries   [Index]

8.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   [Index]

9.1 (sql) — public module ¶

The (sql) module re-exports the public API from (sql core) and (sql helpers). Load (sql pg) separately for PostgreSQL features. Low-level state internals (make-state, state-counter, state-params) are available via (sql core) for custom handlers but are not re-exported here.

;; Recommended: prefix import to avoid namespace pollution
(use-modules ((sql)    #:prefix sql:)
             ((sql pg) #:prefix sql:))

;; Bare import also works at the REPL or in scripts
(use-modules (sql))

Next: (sql util) — utilities, Previous: (sql) — public module, Up: API Reference   [Index]

9.2 (sql core) — formatter ¶

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

Format query (a clause alist) 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. See Extending guile-sql.

Function: make-state [#:placeholder placeholder-dollar] ¶

Create a fresh parameter accumulator state. #:placeholder is a procedure that takes a 1-based index and returns a placeholder string.

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: 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] [#:merge-strategy ’last-write-wins] ¶

Register a new top-level clause sym. handler is (lambda (args state pretty) …) returning (values sql-string new-state).

merge-strategy controls how sql-merge combines duplicate clauses. One of:

concat

Concatenate the argument lists.

and-combine

AND-combine expressions (like #:where).

last-write-wins

The later clause replaces the earlier one (default).

See Extending guile-sql.

Next: (sql helpers) — clause constructors, Previous: (sql core) — formatter, Up: API Reference   [Index]

9.3 (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 pg) — PostgreSQL, Previous: (sql util) — utilities, Up: API Reference   [Index]

9.4 (sql helpers) — clause constructors ¶

Function: sql-merge query … ¶

Merge one or more query alists 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.

Function: q data ¶

Recursively convert :symbol SQL structure markers in data to Guile keywords (#:keyword). Provides a more concise notation for hand-written queries. See The q shorthand.

(q '((:select id) (:from users)))
⇒ '((#:select id) (#:from users))

All other exports are clause constructor helpers. See Clause constructor helpers.

Previous: (sql helpers) — clause constructors, Up: API Reference   [Index]

9.5 (sql pg) — PostgreSQL ¶

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

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

10.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   [Index]

10.2 PostgreSQL expression forms ¶

ARRAY constructor ¶

#:array produces PostgreSQL ARRAY expressions:

'(#:array 1 2 3)
⇒ ARRAY[$1, $2, $3]

'(#:array ((#:select id) (#:from users)))
⇒ 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:

'(#:interval "30 days")      ⇒ INTERVAL '30 days'
'(#:interval "1 hour")       ⇒ INTERVAL '1 hour'

AT TIME ZONE ¶

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

'(#:at-time-zone created-at "UTC")
⇒ created_at AT TIME ZONE $1

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

PostgreSQL-specific DDL ¶

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

#:create-or-replace-view:

'((#: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):

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

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

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

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

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

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

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

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

'(#: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   [Index]

10.3 JSONB operators ¶

#:json-path and #:json-path-text are preferred aliases for the :#> and :#>> operators, which are valid but harder to read in Scheme source.

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

10.4 Other PostgreSQL operators ¶

(#:ilike name "%foo%")

name ILIKE $1 — case-insensitive LIKE.

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

name NOT ILIKE $1

(#:&& a b)

a && b — array overlap.

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

SELECT DISTINCT ON (col, …) col, …

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

11.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   [Index]

11.2 guile-sqlite3 (SQLite) ¶

guile-sqlite3 uses ? placeholders and accepts native Scheme types via sqlite-bind. Use placeholder-question to generate ? placeholders:

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

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

Note: SQLite stores booleans as integers. You may need to coerce #t/#f to 1/0 depending on your schema.

12.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 "@)̈

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

;; 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 from (sql core) to format sub-expressions, threading the state.

12.3 Custom clauses ¶

register-clause! adds a new top-level clause:

(use-modules (srfi srfi-11))  ; for let-values

(register-clause! #:fetch
  #:handler
  (lambda (args state pretty)
    (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)

The optional #:merge-strategy keyword controls how sql-merge combines duplicate clauses when merging queries:

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

Available strategies are concat (concatenate args), and-combine (AND-combine like #:where), and last-write-wins (default).

The (sql pg) module itself is the canonical example of the extension mechanism.

Next: The q shorthand, Up: Pitfalls   [Index]

13.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: One clause entry per join type, Previous: SQL NULL and #:null, Up: Pitfalls   [Index]

13.2 The q shorthand ¶

guile-sql queries use Guile keywords (#:select, #:where, etc.). If you find the #: prefix verbose, the q helper converts :symbol notation to keywords at runtime:

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

The conversion is purely runtime and costs one pass over the data. q recurses into all pairs, leaving strings, numbers, booleans, existing #:keywords, and plain symbols unchanged.

;; These are identical:
(sql->string '((#:select id) (#:from users)))
⇒ ("SELECT id FROM users")

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

Whether to use q is a style preference. The canonical form uses #: keywords directly.

• Alternative: prefix keyword read syntax

(read-set! keywords 'prefix)

;; Now :foo is read as the keyword #:foo everywhere
'((:select id name) (:from users) (:where (:= active #t)))
⇒ ((#:select id name) (#:from users) (#:where (#:= active #t)))

Next: Subqueries in FROM require an alias, Previous: The q shorthand, Up: Pitfalls   [Index]

13.3 One clause entry per join type ¶

Because queries are alists, 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   [Index]

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

14.1 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 the q helper for users who prefer it. See The q shorthand.

14.2 Why alists, 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.

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

x = NULL is always unknown in SQL — never true, never false. Any application code that writes (#:= col #:null) almost certainly means IS NULL. guile-sql converts it automatically, matching the behavior of HoneySQL. Use (#:raw "= NULL") if you genuinely need the broken SQL form.

14.4 Why one join clause per type? ¶

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

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

14.6 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 bodies (CREATE FUNCTION, CREATE TRIGGER)