chickadee » sql-de-lite


sql-de-lite is an interface to SQLite 3 for CHICKEN.

sql-de-lite has the following features:


Starting with sql-de-lite 0.4.0, the SQLite 3 library is included with the egg. Chicken 5 will use this bundled copy by default. Chicken 4 will use the system sqlite3 if present and recent enough, or the bundled copy if not.

In most cases, the default works:

chicken-install sql-de-lite

The goal is to ensure you can depend on this extension without requiring your users to install sqlite.

Bundled library

SQLite 3.31.1 is included in the egg and will be linked in statically by default on Chicken 5, or if the system library is missing or older than 3.31.1 on Chicken 4. The bundled library is compiled with FTS3, FTS4, FTS5, JSON1 extensions and URI syntax enabled.

For convenience, the SQLite3 command shell is built and installed with your Chicken binaries as chicken-sqlite3.

On Chicken 4, to force the use of the bundled library, pass the sql-de-lite-internal-lib feature to chicken-install.

chicken-install -D sql-de-lite-internal-lib sql-de-lite

External library

On Chicken 4, a SQLite library installed in the default system library location will be detected and used instead of the bundled library, if present and recent enough.

On Chicken 5, you need to explicitly request to use the external library.

To force the use of an external library on either version, pass the sql-de-lite-external-lib feature to chicken-install.

chicken-install -D sql-de-lite-external-lib sql-de-lite

For convenience, a chicken-sqlite3 script will be installed; this is a small script that calls sqlite3.

Note: Forcing an external library could fail if it is too old. The absolute minimum is probably 3.6.x.

Non-standard library location

You can point the build process at a library in a non-standard location--for example, under your home directory--by setting CSC_OPTIONS appropriately.

export CSC_OPTIONS="-I$HOME/local/include -L$HOME/local/lib"
chicken-install sql-de-lite

Your own built-in library, static

If you want to use your own built-in library, retrieve the egg source, download the SQLite amalgamation zip file or tarball, then extract its files into sqlite3/ inside the egg source.

Currently, there is no way to override the compilation options to SQLite3 from chicken-install. If you need to do so, edit the sql-de-lite.setup or sql-de-lite.egg file.

Low-level interface

The low-level interface maps closely to the underlying library API. It may be used in tandem with or instead of the high-level API as desired.

Opening the database

open-database filenameprocedure

Opens filename, a sqlite3 database. If no database exists, one is created transparently. filename may also be one of the following symbols:

  • memory: a new database in memory unique to this connection
  • temp or temporary: a new temporary database on disk, visible only to this connection

Returns a #<sqlite-database> object.

Closing the database

close-database dbprocedure

Closes the database connection db. All prepared statements are finalized before closing the database.

Note. Prior to 0.5.0, we did finalize transient statements as well, by walking the library's list of open statements before closing. Unfortunately, this included any statements prepared and owned by SQLite itself--for example, when using FTS--resulting in a double finalize and crash.

Note. Prior to 0.7.0, transient (non-cached) statements had to be finalized manually before closing the database, typically with the query and exec high-level interface. Now all statements are finalized regardless of caching status.

database-closed? dbprocedure

Predicate that checks if database db is closed.

Preparing a SQL statement

prepare db sqlprocedure

Looks up a prepared statement in the statement cache. If not found, it prepares a new statement. Returns a statement object.

Preparing a SQL statement consisting entirely of whitespace or comment is an error as of 0.5.0.

Returns a #<sqlite-statement> object.

Note. Prior to 0.7.0, an exception was thrown if a statement we pulled from cache is currently running. Statements are no longer cached while running.

prepare-transient db sqlprocedure

Same as prepare, but bypasses the cache completely. This procedure is subject to removal.

prepared-cache-size nparameter

Sets the capacity of the prepared statement cache, in statements.

When the cache reaches capacity and a new statement is prepared, the least recently used statement is finalized and drops off the cache.

This setting takes effect only upon initiating a new connection, and the statement cache is unique per connection.

Set capacity to 0 to disable the cache.

Stepping the statement

step statementprocedure

Steps statement and returns one of the following:

  • 'row: a row was returned (SQLITE_ROW)
  • 'done: the statement is done executing (SQLITE_DONE)
  • #f: step failed due to error

#f is only ever returned if raising exceptions is disabled. Completion of execution is still considered a "success" and so the true value 'done is returned, rather than #f.

Upon database error, the statement is reset.

Resetting a statement

reset statementprocedure

Resets statement to the beginning of its program, returning the statement.

Finalizing a statement

finalize statementprocedure

Finalize statement. Usually, the statement is reset and added to the statement cache. If the statement is transient, or cannot be cached for some reason, it is finalized immediately.

Finalizing a finalized statement, or a statement on a closed database, is a no-op.

resurrect statementprocedure

Resurrects a previously finalized statement s by pulling it from the cache, or if it was not cached, by re-preparing the original SQL associated with the statement.

Returns s, which is also modified in place.

Binding parameters

bind statement index valueprocedure

Bind parameter at index of statement to value, and returns statement. The variable index may be an integer (the first parameter is 1, not 0) or a string for a named parameter --- for example, "$key", ":key" or "@key". For named parameters, the $, : or @ must be included in the string. A reference to an invalid index will throw an exception.

bind-parameters statement #!rest parametersprocedure

Convenience function which binds parameters to indices 1 .. n, in order. Keyword arguments are permitted; foo: 3 will bind 3 to parameter :foo.

The number of parameters must match the statement's bind-parameter-count, or an error will be signaled. Also, all keywords used must be valid parameter names.

Mixing named and anonymous arguments in the same statement is not recommended.

bind-parameter-count statementprocedure

Returns the number of bound parameter slots in this prepared statement. If numbered parameters are present, gaps may be left in the sequence. Named parameters count in the slot total as well.

bind-parameter-name statement iprocedure

Returns a string representing the name of the bound parameter at index i, or #f if the parameter is anonymous or is out of range.

The string includes the parameter name prefix; for example ":foo", "$foo", "@foo" or "?nnn".

Obtaining result data

In this section's examples, we assume a simple database has been created with the following commands:

CREATE TABLE cache(key text, val text);
INSERT INTO cache(key,val) VALUES('foo', 'bar');
INSERT INTO cache(key,val) VALUES('baz', 'quux');

and a SELECT statement has been prepared and stepped once:

(define s (prepare db "SELECT rowid, * from cache;"))
(step s)  ; => row

and s is implicitly reset between examples.

column-name statement indexprocedure

Return the name of the specified result set column as a symbol. The statement need not have been stepped to retrieve column names or column count.

(column-name s 1)
; => key
column-names statementprocedure

Convenience function which returns a list of all column names for the result set, in order.

(column-names s)
; => (rowid key val)
column-count statementprocedure

Return the number of columns in the result set returned by the prepared statement.

(column-count s)
; => 3
column-type statement indexprocedure

Returns the type of the indexed column in the current row. SQLite is dynamically typed and the column types are unique to each row.

SymbolDatabase type
(map (lambda (i) (column-type s i))
     (list 0 1 2))
; => (integer text text)
column-data statement indexprocedure

Returns the data from the indexed column in the current row.

Column typeScheme type
integerExact or inexact number
floatInexact number
(map (lambda (i) (column-data s i))
     (list 0 1 2))
; => (1 "foo" "bar")

integer values are retrieved with sqlite3_column_int64. On a 32-bit machine, values outside the signed 31-bit fixnum range are returned as inexact numbers. On a 64-bit machine, values outside the signed 63-bit fixnum range are returned as inexact numbers. Note that inexact numbers are 64-bit floating point values, and can only accurately represent 53 bits of an integer.

row-data statementprocedure

Retrieve a list of column data from the current row. If the last execution of step returned done, a NULL value will be returned for every column.

(row-data s)
; => (1 "foo" "bar")
row-alist statementprocedure

Retrieve an alist mapping column names to column data for the current row.

(row-alist s)
; => ((rowid . 1) (key . "foo") (val . "bar"))
change-count dbprocedure

Returns the number of database rows that were changed or inserted or deleted by the most recently completed SQL statement, not including triggers, as in sqlite3_changes.

total-change-count dbprocedure

Returns the number of row changes caused by INSERT, UPDATE or DELETE statements since the database connection was opened, including triggers, as in sqlite3_total_changes.

last-insert-rowid dbprocedure

Get the ROWID of the last successful INSERT, as in sqlite3_last_insert_rowid.

High-level interface

Opening and closing the database

call-with-database filename procprocedure

Opens a database, calls proc with the database object and then closes the database on return. If an error occurs in proc, the database is closed immediately.


sql db sql-strprocedure

Creates a statement object associated with the database connection db and the SQL sql-str. Preparation of the statement is deferred until needed. This is a normal statement in every respect except that it must be resurrected before it can be used.

sql is recommended over prepare when using the query / exec interface so that you can declare your statements without compiling them until and unless they are actually used.

sql/transient db sql-strprocedure

Equivalent to (sql db sql-str), but the statement will never be cached; it is prepared anew every time it is resurrected.


query proc s #!rest argsprocedure

Resurrects statement s, binds args to s using bind-parameters, performs a query* and finalizes the statement.

query is the usual way to perform a query unless you need to bind arguments manually, need more control or are using the low-level interface, in which case you can use query* if desired.

You typically call query or query* with one of the provided result fetching procedures; you can also pass your own procedure to perform whichever operations you would like.

query* proc sprocedure

Calls (proc s) and resets the statement s immediately afterward, to avoid locking the database. If an exception occurs during proc, the statement will still be reset. The statement is not reset before execution.

The entire purpose of query* is to ensure a statement is reset after it is executed. If a statement were left in a running state --- for example, if an uncaught exception occurs during proc, or you simply do not exhaust its result set --- then the database will be locked for writing until the statement is finalized.

Query procedures
fetch sprocedure
fetch-row sprocedure

Fetch the next row of the result set. This is the equivalent to performing a step followed by a row-data call, and works with both the high- and low-level interfaces. If the statement has finished executing, fetch returns '(). These query procedures do not reset the statement before or afterward; one may do so using reset or query.

fetch and fetch-row are aliases.

(fetch s)
; => (1 "foo" "bar")
(fetch s)
; => (2 "baz" "quux")
(fetch s)
; => ()
(fetch s)
; => error
(query fetch s)
; => (1 "foo" "bar")
(query fetch s)
; => (1 "foo" "bar")
(fetch s)
; => (1 "foo" "bar")
fetch-all sprocedure
fetch-rows sprocedure

Calls fetch until it returns '(), and collects the result into a list. fetch-all and fetch-rows are aliases.

(query fetch-all s)
; => ((1 "foo" "bar")
      (2 "baz" "quux"))
fetch-alist sprocedure

Fetch the next row of the result set and return an alist mapping symbolic row names to values. Equivalent to step followed by row-alist.

(query fetch-alist s)
; ((rowid . 1) (key . "foo") (val . "bar"))
fetch-alists sprocedure

Fetches all rows and returns a list of alists, one per row.

(query fetch-alists s)
; (((rowid . 1) (key . "foo") (val . "bar"))
;  ((rowid . 2) (key . "baz") (val . "quux")))
fetch-value sprocedure

Fetches and returns only the first value (first column) of the next row, or #f if the row contained no column data. Equivalent to using first-column on the result of a fetch, but does not materialize the entire row.

(query fetch-value
       (sql db "select key from cache"))
; => "foo"
(query fetch-value
       (sql db "select key from cache where key=?") "nosuchkey")
; => #f
fetch-column sprocedure

Fetches all rows and returns a list containing the first column of each, or '() if there was no column data.

(query fetch-column
       (sql db "select key from cache"))
; => ("foo" "bar")
for-each-row procprocedure
for-each-row* procprocedure

Returns a procedure suitable for passing to query, taking one argument, a statement object.

The procedure will call fetch once for each row and call your callback as (proc row), discarding the results.

(query (for-each-row
         (lambda (x) (print "row: " x)))
; row: (1 foo bar)
; row: (2 baz quux)
; => undefined

for-each-row* behaves like for-each-row, but your callback is invoked with one argument for each column value. For example, these produce equivalent results:

(query (for-each-row (match-lambda ((name sql)
                        (print "table: " name " sql: " sql ";"))))
       (sql db "select name, sql from sqlite_master;"))
(query (for-each-row* (lambda (name sql)
                        (print "table: " name " sql: " sql ";")))
       (sql db "select name, sql from sqlite_master;"))
map-rows procprocedure
map-rows* procprocedure

Return a procedure suitable for passing to query, taking one argument, a statement object.

The procedure will call fetch once for each row and call (proc row), collecting the results into a list, in order.

(query (map-rows car) s)
; => (1 2)

Another example; these two produce equivalent results:

(query (map-rows car) (sql db "select name, sql from sqlite_master;"))
(map car (query fetch-all (sql db "select name, sql from sqlite_master;")))

map-rows* behaves like map-rows, but your callback is invoked with one argument for each column value.

fold-rows kons knilprocedure
fold-rows* kons knilprocedure

Calls (kons x xs) once for each row, where x is the current row data and xs is the seed (previous return value from kons). The initial seed is knil.

(query (fold-rows cons '()) s)
; => ((2 "baz" "quux") (1 "foo" "bar"))
;; sum the returned rowids
(query (fold-rows (lambda (x xs) (+ (car x) xs))
; => 3
;; that was contrived, you should actually do the sum in the database
(car (query fetch (sql db "select sum(rowid) from mytable;")))
; => 3

fold-rows* behaves like fold-rows, but the kons callback is invoked with one column for each argument value, plus the seed as the last argument -- for example, as (kons x y z seed). This turns out to be quite inefficient and makes little sense, so fold-rows* is deprecated as of 0.4.2.

first-column rowprocedure

Returns the first column of row, or #f if the row is '().

(first-column (query fetch (sql db "select sum(rowid) from mytable;")))
; => 3

You can also use fetch-value here instead:

(query fetch-value (sql db "select sum(rowid) from mytable;"))
; => 3


exec s #!rest argsprocedure

Resurrects statement s, binds args to s using bind-parameters, performs an exec*, and finalizes the statement.

exec* sprocedure

Executes statement sql, returning the number of changes (if the result set has no columns as in INSERT, DELETE, UPDATE) or the first row (if column data is returned as in SELECT). In the latter case, it is like performing a (query* fetch s), but is more efficient.

Resurrection is omitted, as it would wipe out any bindings. Reset is NOT done beforehand; it is cheap, but the user must reset before a bind anyway.

The statement is always reset afterward, even if an exception occurs, to avoid locking the database. Note however that an internal error when retrieving column data (such as a string > 16MB) will leave the statement open -- this is a flaw in the current implementation.

(exec (sql db "INSERT INTO cache(key, val) values(?, ?);")
      "chicken" 4)
; => 1
(exec (sql db "SELECT * FROM cache WHERE key = ?;")
; => ("chicken" "4")
(first-column (exec (sql db "SELECT val FROM cache;")))
; => "bar"
(first-column (exec (sql db "SELECT val FROM cache;")))
; => "bar"


with-transaction db thunk #!optional (type 'deferred)procedure
with-deferred-transaction db thunkprocedure
with-immediate-transaction db thunkprocedure
with-exclusive-transaction db thunkprocedure

Executes thunk within a BEGIN TRANSACTION block, and returns the value of thunk. The optional type may be one of the symbols deferred, immediate, or exclusive. You may also use the named convenience functions instead of the optional parameter.

The transaction is committed with (commit db) if thunk returns a true value. Escaping or re-entering the dynamic extent of thunk will not commit or rollback the in-progress transaction. However, if an exception occurs during thunk, or thunk returns #f, or the commit fails, the transaction will be rolled back with (rollback db). If this rollback fails, that is a critical error and you should likely abort.

rollback dbprocedure

Rollback current transaction. Unconditionally resets running queries before doing so, as rollback would fail if read or read/write queries are running. Successful rollback returns a true value. Rolling back in autocommit mode also returns a true value.

commit dbprocedure

Commit current transaction. This does not rollback running queries, because running read queries are acceptable, and the behavior in the presence of pending write statements is unclear. If the commit fails, you can always rollback, which will reset the pending queries.

Successful commit, or commit in autocommit mode, returns a true value.

autocommit? dbprocedure

Returns #t if the database is in autocommit mode, or #f if within a transaction.

Error handling

When a database error occurs, an exception of type (exn sqlite) is raised, containing the database error code and message. This information is also available from the database using the error-code and error-message interface.

sqlite-exception? eprocedure

Is e an exception raised by the database?

sqlite-exception-status eprocedure

Get the database error code as a symbol. See error-code for details.

sqlite-exception-message eprocedure

Get the database error message as a string.

error-code dbprocedure

Returns the last database error code as a symbol.

Symbol C error code
permission SQLITE_PERM
no-memory SQLITE_NOMEM
authorization SQLITE_AUTH
not-a-database SQLITE_NOTADB
error-message dbprocedure

Returns the last database error message as a string.

raise-database-errors BOOLEANparameter

Set to #t to raise an exception on database error, #f to return a false value. Note that certain critical errors, such as "misuse of interface" and arity mismatches of bound parameters will raise exceptions regardless. Procedures in this extension that utilize the low-level interface are written to work correctly with both #f return values and errors.

Disabling raising of database errors is intended for experts and this option may be removed.

Busy handling

Busy handling is done outside of the library, instead of inside the library busy handler, because with SRFI-18 threads it is not legal to yield within a callback. The backoff algorithm of sqlite3_busy_timeout is reimplemented.

SQLite can deadlock in certain situations and to avoid this will return SQLITE_BUSY immediately rather than invoking the busy handler. However if there is no busy handler, we cannot tell a retryable SQLITE_BUSY from a deadlock one. To gain deadlock protection we register a simple busy handler which sets a flag indicating this BUSY is retryable. This is done without invoking a callback into Scheme.

set-busy-handler! db procprocedure

Register the busy handler proc on the open connection db; the handler will be called repeatedly when a prepare or step operation returns SQLITE_BUSY. It is passed the two arguments (db count), which are the associated database connection and the number of times this busy handler has been invoked so far for this operation. The procedure should return #f to stop retrying and have the operation return a BUSY error to the caller, or #t if the busy operation should be retried.

By default, no busy handler is registered. Busy handlers are unique to each connection and must be registered after the connection is open.

 (lambda (db)
  (set-busy-handler! db (busy-timeout 10000)) ; 10 second timeout
busy-timeout msprocedure

Return a procedure suitable for use in set-busy-handler!, implementing a spinning busy timeout using the SQLite3 busy wait algorithm. This handler will wait up to ms milliseconds total before giving up. Other threads may be scheduled while this one is busy-waiting.

User-defined functions

You may define your own scalar and aggregate functions in Scheme.

Currently, a significant performance penalty is imposed on all calls to step once the first user-defined function is registered, due to a limitation on callbacks in Chicken. This penalty is on the order of 10 times, and is imposed on all statements regardless of whether a user function is actually invoked. However, if no functions are registered, there is no penalty.

register-scalar-function! db name nargs procprocedure

Register a user-defined scalar function name of arity nargs. nargs may range from 0 to 127, or -1 to define a function taking any number of arguments. You may define multiple functions with differing numbers of arguments. Defining a function with the same nargs as an existing function will redefine it, even built-in functions.

proc should be a function taking nargs arguments; to delete an existing function, set proc to #f. The return value is used as the value of the scalar function. If an error occurs during the function, it is signaled as a database error.

Functions must be defined anew for every database connection.

Be very careful when combining user-defined functions and SRFI-18 threads.

register-aggregate-function! db name nargs pstep #!optional (seed 0) (pfinal identity)procedure

Register a user-defined aggregate function name of arity nargs. nargs may range from 0 to 127, or -1 to define a function taking any number of arguments. You may define multiple functions with differing numbers of arguments. Defining a function with the same nargs as an existing function will redefine it, even built-in functions.

seed is the initial seed passed to this particular invocation of the aggregate function. At every step, pstep is invoked as (pstep seed arg1 ... argn) and its return value becomes the next seed. Finally, (pfinal seed) is invoked to do any final transformation necessary on the seed. (For example, if seed is a record, you may need to pull out and return the relevant data.) The return value of pfinal is used as the value of the aggregate function. If an error occurs during pstep or pfinal, it is signaled as a database error.

pstep should be a function taking nargs arguments. To delete an existing aggregate function, set pstep to #f. In this case the values of seed and pfinal are ignored.

Functions must be defined anew for every database connection.

Be very careful when combining user-defined functions and SRFI-18 threads.

Callbacks and SRFI-18

Warning. Callbacks are inherently unsafe in combination with SRFI-18 threads; callbacks must always be exited in the order they were entered, which can be violated with thread-switching or call/cc. sql-de-lite takes care to disable thread scheduling inside user-defined function callbacks. It also traps and safely signals errors via the library.

However, you must not:

unless you are a wizard, or can guarantee no other thread can ever invoke a callback (even one created with another egg).

User-defined functions have not been heavily stress-tested in the presence of multiple threads, so caution is advised.


load-extension! db filename #!optional entry-pointprocedure

Loads a SQLite extension library from filename and initializes it by calling entry-point, a string containing the name of a C function. If filename is not found, it will try, filename.dylib or filename.dll as appropriate for the platform. If entry-point is omitted or #f, SQLite guesses the entry point name from the filename. See the documentation for sqlite3_load_extension for further details.

As an example, see tests/run.scm and tests/rot13.c in the egg source, which define and call a rot13 function.

Warning. This allows arbitrary code to be loaded and executed from disk at runtime, and could conceivably cause the extension's state to get out of sync. Please proceed with extreme caution.


schema dbprocedure

Returns a list of SQL statements making up the database schema.

print-schema dbprocedure

Displays the database schema to the current output port; the result is similar to using .schema at the sqlite3 command prompt.

flush-cache! dbprocedure

Flush the prepared statement cache. All cached statements will be finalized (in the underlying library sense).

finalized? statementprocedure

Returns #t if the statement is finalized or has never been prepared.

(finalized? (sql db "select 1;"))     ; => #t
(finalized? (prepare db "select 1;")) ; => #f

A string representing the SQLite3 library version (e.g. "3.6.11").


SRFI-18 threads

If you are operating on the same database in multiple threads, you must open a new connection per thread. Reusing the same connection will result in corruption.

Be very careful when combining user-defined functions and SRFI-18 threads.

About this egg


Version history

Add load-extension! by CMB.
Add savepoint transaction support (with-savepoint-transaction) courtesy of Andy Bennett (@andyjpb).
Upgrade to SQLite 3.31.1. Enable FTS4, FTS5, JSON1 extensions and URI syntax. Add build support for Chicken 5.2 and require explicit request to use external lib on C5. Minimum version for Chicken 5 is 5.1 due to build changes.
Chicken 5.0 support, officially. Chicken 4 is still supported.
Move to github; initial Chicken 5 support from Peter Bex
Upgrade to SQLite 3.17.0
Defer caching statements until they are finalized, so the cache never contains running statements. Fixes a cache-expiration bug found by andyjpb; also transient statements no longer need to be finalized manually, and duplicate SQL may be prepared. Remove deprecated milliseconds->time identifier (wasamasa).
Move to bitbucket; dummy update for henrietta-cache
Upgrade to SQLite 3.8.9
Fix for test on 64bit machine. kon lovett
Remove pathname expansion from open-database. Use the pathname-expand egg instead.
Protect against access to busy handler after handler object freed
Reverse seed and pstep args to register-aggregate-function!, make seed optional
Scalar and aggregate user-defined functions. Upgrade internal library to 3.7.11.
Support named parameters.
Add database-closed?, sql/transient. Ensure transient statements are finalized in QUERY and EXEC. Disable last-resort finalization of open statements in close-database. Warn if database is not closed after call-with-database. Ensure statement column count and column names are correct if the database schema changes. Make preparing whitespace/comment SQL illegal (for now). Ensure resurrected transient statements are still transient.
Add fetch-value, -column, -alists, -row, and -rows. Reset execed stmts that return 0 rows, preventing spurious reserved lock after ROLLBACK. Bind exact numbers to int64, not int.
Remove deprecated milliseconds->time call
byte-vector and pointer types upgraded for compat with Chicken 4.7
Upgrade to SQLite 3.7.3; fix fold-rows* (and deprecate it)
Drop dependency on easyffi
Add integrated SQLite3 library
Initial release


Jim Ursetto


The egg is BSD-licensed. The SQLite 3 library is public domain.

Contents »