6.1 Extended DB API

6.1 Extended DB API🔗

The toolbox/db/base module improves and extends db/base. In addition to the bindings documented in this section, it re-exports all bindings from db/base (except those that have the same name as one of the bindings documented in this section).

The interface provided by toolbox/db/base is mostly drop-in compatible with that of db/base, with two major exceptions:

Most functions have been changed to no longer require an explicit database connection argument. Instead, the value of the current-db parameter is used.
Nested uses of call-with-transaction do not create nested transactions by default. The #:nested 'allow option must be supplied if true nested transactions are desired.

parameter
(current-db) → (or/c connection? #f)
(current-db db) → void?
db : (or/c connection? #f)
= #f

A parameter that determines the current database connection, which many functions use implicitly if a connection is not explicitly provided.

procedure
(get-db [who]) → connection?
who : symbol? = 'get-db

Obtains the current database connection. If (current-db) is #f, an exn:fail:contract exception is raised (with who inserted at the start of the error message).

procedure
(in-transaction? [#:db db]) → boolean?
db : connection? = (current-db)

Like in-transaction? from db/base, but uses the current database connection by default.

procedure
(call-with-transaction thunk
[ #:db db
#:isolation isolation-level
#:option option
#:nested nested-mode]) → any
  thunk : (-> any)
  db : connection? = (current-db)
   isolation-level :
(or/c 'serializable
      'repeatable-read
      'read-committed
      'read-uncommitted
      #f)
= #f
  option : any/c = #f
  nested-mode : (or/c 'allow 'omit 'fail) = 'omit

Like call-with-transaction from db/base, but uses the current database connection by default, and behavior when already inside a transaction differs depending on nested-mode:

If nested-mode is 'omit (the default), call-with-transaction has no effect when already inside a transaction: thunk is invoked directly, without starting a nested transaction.
If nested-mode is 'allow, call-with-transaction applies thunk within a nested transaction. (This is the behavior of call-with-transaction from db/base.)
If nested-mode is 'fail, call-with-transaction raises an exn:fail:contract exception if already inside a transaction.

The default value of 'omit makes call-with-transaction ensure that thunk is executed in the context of some transaction, but it does not allow the effects of thunk to be selectively rolled back. In practice, partial rollbacks are rarely useful, and creating savepoints to permit them can have significant performance overhead, so this is usually the right choice.

procedure
(call-with-transaction/retry thunk
[ #:db db
#:isolation isolation-level
#:option option
#:nested nested-mode
#:max-retries max-retries
#:retry-delay retry-delay-secs])
→ any
  thunk : (-> any)
  db : connection? = (current-db)
   isolation-level :
(or/c 'serializable
      'repeatable-read
      'read-committed
      'read-uncommitted
      #f)
= #f
  option : any/c = #f
  nested-mode : (or/c 'allow 'omit 'fail) = 'omit
   max-retries : (or/c exact-nonnegative-integer? +inf.0)
= (current-max-transaction-retries)
   retry-delay-secs : (>=/c 0)
= (current-transaction-retry-delay)

Like call-with-transaction, except that thunk may be retried if it raises an exn:fail:sql:busy? exception. Retrying is not possible if call-with-transaction/retry is called from within another transaction, so if a transaction is already started, call-with-transaction/retry behaves identically to call-with-transaction.

Assuming retrying is possible, thunk may be executed up to max-retries times before giving up. Before each retry attempt, call-with-transaction/retry sleeps for retry-delay-secs seconds.

When db is a SQLite connection and option is #f or 'deferred, retry attempts will automatically use 'immediate for option, instead. This is usually enough to ensure that thunk itself is only executed at most twice, though the retry limit may still be reached if call-with-transaction/retry is unable to successfully acquire a write transaction.

Note that the retry mechanism of call-with-transaction/retry is used in addition to the retry mechanism used for all SQLite operations, which is controlled separately via the #:busy-retry-limit and #:busy-retry-delay arguments to sqlite3-connect. For multi-statement transactions, the retry mechanism of call-with-transaction/retry is often substantially more useful, as a SQLITE_BUSY failure may indicate that the entire transaction must be restarted, in which case retrying the last statement will never succeed and serves no purpose. However, the intrinsic retry mechanism can be more useful in other situations, especially when the #:use-place argument to sqlite3-connect is 'os-thread, as it can use SQLite’s built-in busy handler.

parameter
(current-max-transaction-retries)
→ (or/c exact-nonnegative-integer? +inf.0)
(current-max-transaction-retries max-retries) → void?
max-retries : (or/c exact-nonnegative-integer? +inf.0)
= 10

A parameter that controls the number of times call-with-transaction/retry will attempt to retry a transaction that fails with a exn:fail:sql:busy? exception.

parameter
(current-transaction-retry-delay) → (>=/c 0)
(current-transaction-retry-delay retry-delay-secs) → void?
retry-delay-secs : (>=/c 0)
= 0.1

A parameter that controls the number of seconds call-with-transaction/retry will wait between attempts to retry a transaction that fails with a exn:fail:sql:busy? exception.

procedure
(query stmt
arg ...
[ #:db db
#:log? log?
#:explain? explain?
#:analyze? analyze?])
→ (or/c simple-result? rows-result?)
  stmt : (or/c string? virtual-statement? prepared-statement?)
  arg : any/c
  db : connection? = (current-db)
  log? : any/c = (current-log-db-queries?)
  explain? : any/c = (current-explain-db-queries?)
  analyze? : any/c = (current-analyze-db-queries?)

Like query from db/base, but uses the current database connection by default and supports automatic query logging and instrumentation. If enabled, all log messages are written to toolbox:db-logger on topic 'toolbox:db:query at level 'info.

If log? is not #f, the SQL text of stmt is logged before the query is executed, and the query’s (wall clock) execution time is logged after the execution completes.

If explain? is not #f, a textual representation of the database system’s query plan is logged before the query is executed. Currently, this option is only supported with SQLite; an exn:fail:unsupported exception will be raised with other database systems.

If analyze? is not #f, the query plan is logged in the same way as for explain?, but the plan is logged after executing the query, and it is annotated with performance information collected during the query’s execution. Like explain?, this option is currently only supported with SQLite, but analyze? additionally requires that SQLite was compiled with the SQLITE_ENABLE_STMT_SCANSTATUS compile-time option. The sqlite3-stmt-scanstatus-enabled? function can be used to check whether this is the case.

Examples:

> (current-db (sqlite3-connect #:database 'memory))
> (define can-analyze? (sqlite3-stmt-scanstatus-enabled?))
> (query
   #:log? #t
   #:explain? (not can-analyze?)
   #:analyze? can-analyze?
   (string-join
    '("WITH RECURSIVE"
      "  fib(i,a,b) AS"
      "  (SELECT 1, 0, 1"
      "   UNION ALL"
      "   SELECT i+1, b, a+b FROM fib"
      "   WHERE i <= 10)"
      "SELECT b FROM fib ORDER BY i")
    "\n"))
[2025-09-14 16:21:52] [toolbox:db:query/INFO]
WITH RECURSIVE
  fib(i,a,b) AS
  (SELECT 1, 0, 1
   UNION ALL
   SELECT i+1, b, a+b FROM fib
   WHERE i <= 10)
SELECT b FROM fib ORDER BY i
[2025-09-14 16:21:52] [toolbox:db:query/INFO] 0.2 ms
[2025-09-14 16:21:52] [toolbox:db:query/INFO] QUERY PLAN [cycles=255,578]
├╴CO-ROUTINE fib
│ ├╴SETUP
│ │ ╰╴SCAN CONSTANT ROW
│ ╰╴RECURSIVE STEP
│   ╰╴SCAN fib                   3.6% [est=983,040.0 actual=1.0 loops=11 rows=11 cycles=9,270]
├╴SCAN fib                            [est=122,880.0 actual=11.0 loops=1 rows=11]
╰╴USE TEMP B-TREE FOR ORDER BY   5.7% [est=1.0 actual=11.0 loops=1 rows=11 cycles=14,549]
(rows-result
'(((name . "b") (decltype . #f)))
'(#(1) #(1) #(2) #(3) #(5) #(8) #(13) #(21) #(34) #(55) #(89)))

procedure
(query-exec stmt
arg ...
[ #:db db
#:log? log?
#:explain? explain?
#:analyze? analyze?]) → void?
  stmt : (or/c string? virtual-statement? prepared-statement?)
  arg : any/c
  db : connection? = (current-db)
  log? : any/c = (current-log-db-queries?)
  explain? : any/c = (current-explain-db-queries?)
  analyze? : any/c = (current-analyze-db-queries?)

Like query-exec from db/base, but uses the current database connection by default and supports automatic query logging and instrumentation like query. See the documentation for query for information about the behavior of log?, explain?, and analyze?.

procedure
(query-rows stmt
arg ...
[ #:db db
#:group groupings
#:group-mode group-mode
#:log? log?
#:explain? explain?
#:analyze? analyze?]) → (listof vector?)
  stmt : (or/c string? virtual-statement? prepared-statement?)
  arg : any/c
  db : connection? = (current-db)
   groupings :
(let* ([field/c (or/c string? exact-nonnegative-integer?)]
       [grouping/c (or/c field/c (vectorof field/c))])
  (or/c grouping/c (listof grouping/c)))
= '()
  group-mode : (listof (or/c 'preserve-null 'list)) = '()
  log? : any/c = (current-log-db-queries?)
  explain? : any/c = (current-explain-db-queries?)
  analyze? : any/c = (current-analyze-db-queries?)

Like query-rows from db/base, but uses the current database connection by default and supports automatic query logging and instrumentation like query. See the documentation for query for information about the behavior of log?, explain?, and analyze?.

procedure
(query-list stmt
arg ...
[ #:db db
#:log? log?
#:explain? explain?
#:analyze? analyze?]) → list?
  stmt : (or/c string? virtual-statement? prepared-statement?)
  arg : any/c
  db : connection? = (current-db)
  log? : any/c = (current-log-db-queries?)
  explain? : any/c = (current-explain-db-queries?)
  analyze? : any/c = (current-analyze-db-queries?)

Like query-list from db/base, but uses the current database connection by default and supports automatic query logging and instrumentation like query. See the documentation for query for information about the behavior of log?, explain?, and analyze?.

procedure
(query-row stmt
arg ...
[ #:db db
#:log? log?
#:explain? explain?
#:analyze? analyze?]) → vector?
  stmt : (or/c string? virtual-statement? prepared-statement?)
  arg : any/c
  db : connection? = (current-db)
  log? : any/c = (current-log-db-queries?)
  explain? : any/c = (current-explain-db-queries?)
  analyze? : any/c = (current-analyze-db-queries?)

Like query-row from db/base, but uses the current database connection by default and supports automatic query logging and instrumentation like query. See the documentation for query for information about the behavior of log?, explain?, and analyze?.

procedure
(query-maybe-row stmt
arg ...
[ #:db db
#:log? log?
#:explain? explain?
#:analyze? analyze?]) → (or/c vector? #f)
  stmt : (or/c string? virtual-statement? prepared-statement?)
  arg : any/c
  db : connection? = (current-db)
  log? : any/c = (current-log-db-queries?)
  explain? : any/c = (current-explain-db-queries?)
  analyze? : any/c = (current-analyze-db-queries?)

Like query-maybe-row from db/base, but uses the current database connection by default and supports automatic query logging and instrumentation like query. See the documentation for query for information about the behavior of log?, explain?, and analyze?.

procedure
(query-value stmt
arg ...
[ #:db db
#:log? log?
#:explain? explain?
#:analyze? analyze?]) → any/c
  stmt : (or/c string? virtual-statement? prepared-statement?)
  arg : any/c
  db : connection? = (current-db)
  log? : any/c = (current-log-db-queries?)
  explain? : any/c = (current-explain-db-queries?)
  analyze? : any/c = (current-analyze-db-queries?)

Like query-value from db/base, but uses the current database connection by default and supports automatic query logging and instrumentation like query. See the documentation for query for information about the behavior of log?, explain?, and analyze?.

procedure
(query-maybe-value stmt
arg ...
[ #:db db
#:log? log?
#:explain? explain?
#:analyze? analyze?]) → any/c
  stmt : (or/c string? virtual-statement? prepared-statement?)
  arg : any/c
  db : connection? = (current-db)
  log? : any/c = (current-log-db-queries?)
  explain? : any/c = (current-explain-db-queries?)
  analyze? : any/c = (current-analyze-db-queries?)

Like query-maybe-value from db/base, but uses the current database connection by default and supports automatic query logging and instrumentation like query. See the documentation for query for information about the behavior of log?, explain?, and analyze?.

parameter
(current-log-db-queries?) → boolean?
(current-log-db-queries? log?) → void?
log? : any/c
= #f

A parameter that controls whether functions like query should log each query’s SQL text; see the documentation for query for details.

parameter
(current-explain-db-queries?) → boolean?
(current-explain-db-queries? explain?) → void?
explain? : any/c
= #f

A parameter that controls whether functions like query should log each query’s query plan before execution; see the documentation for query for details.

parameter
(current-analyze-db-queries?) → boolean?
(current-analyze-db-queries? analyze?) → void?
analyze? : any/c
= #f

A parameter that controls whether functions like query should log each query’s profiled query plan after execution; see the documentation for query for details.

procedure
(query-changes [#:db db]) → exact-nonnegative-integer?
db : connection? = (current-db)

Returns the number of database rows that were changed, inserted, or deleted by the most recently completed INSERT, DELETE, or UPDATE statement. Currently only supported with SQLite; an exn:fail:unsupported exception will be raised with other database systems.

procedure
(map-sql-nullable proc v) → any/c
proc : (-> any/c any/c)
v : any/c

If v is sql-null, returns sql-null, otherwise returns (proc v).

Examples:

> (map-sql-nullable add1 1)
2
> (map-sql-nullable add1 sql-null)
#<sql-null>

syntax
(lifted-statement expr)

expr : (or/c string? (-> dbsystem? string?))

Equivalent to (#%lift (virtual-statement expr)). That is, lifted-statement is like virtual-statement, except that it is implicitly lifted to the top of the enclosing module (so expr may not reference local variables). This allows a virtual statement to be declared inline, where it is used.

Also see ~stmt, which combines lifted-statement and ~sql.

value
toolbox:db-logger : logger?

A logger used by various functions in toolbox/db/base. Its parent logger is toolbox-logger.

procedure
(exn:fail:sql:busy? v) → boolean?
v : any/c

Returns #t if v is an exn:fail:sql exception and (exn:fail:sql-sqlstate v) is 'busy. Otherwise, returns #f.

procedure
(exn:fail:sql:constraint? v) → boolean?
v : any/c

Returns #t if v is an exn:fail:sql exception and (exn:fail:sql-sqlstate v) is 'constraint. Otherwise, returns #f.

contents ← prev up next →

1	Syntax
2	Data Structures
3	Logging
4	Formatting
5	Gregor
6	Database
7	Drawing
8	Web
	Index

6.1	Extended DB API
6.2	Formatting SQL
6.3	Defining SQL accessors
6.4	SQLite