SQLite

SQLite database bindings with high-level query helpers.

(import (sigil sqlite))

Opening and Closing

;; Open a database file (created if it doesn't exist)
(define db (sqlite-open "app.db"))

;; In-memory database
(define db (sqlite-open ":memory:"))

;; Close when done
(sqlite-close db)

;; Resource-safe pattern: auto-closes on exit
(call-with-database "app.db"
  (lambda (db)
    (sqlite-query db "SELECT * FROM users")))

sqlite-open returns #f on failure. sqlite-close is idempotent.

Type Mapping

High-Level API

These three procedures handle statement preparation, parameter binding, and cleanup automatically.

sqlite-query

Execute a query and return all rows as alists.

(sqlite-query db "SELECT * FROM users")
; => (((id . 1) (name . "Alice")) ((id . 2) (name . "Bob")))

;; With parameter binding
(sqlite-query db "SELECT * FROM users WHERE age > ?" 21)
; => (((id . 1) (name . "Alice") (age . 30)))

;; Multiple parameters
(sqlite-query db "SELECT * FROM users WHERE name = ? AND age > ?" "Alice" 21)

;; No results returns empty list
(sqlite-query db "SELECT * FROM users WHERE id = ?" 999)
; => ()

Column names become symbols in the alist keys.

sqlite-query-row

Execute a query and return just the first row, or #f if no match.

(define user (sqlite-query-row db "SELECT * FROM users WHERE id = ?" 1))
; => ((id . 1) (name . "Alice") (email . "alice@example.com"))

(assoc-ref 'name user)   ; => "Alice"
(assoc-ref 'email user)  ; => "alice@example.com"

(sqlite-query-row db "SELECT * FROM users WHERE id = ?" 999)
; => #f

sqlite-run

Execute a statement that doesn't return rows (INSERT, UPDATE, DELETE). Returns #t on success, #f on failure.

(sqlite-run db "INSERT INTO users (name, email) VALUES (?, ?)"
            "Alice" "alice@example.com")
; => #t

(sqlite-run db "UPDATE users SET name = ? WHERE id = ?" "Bob" 1)
; => #t

(sqlite-run db "DELETE FROM users WHERE id = ?" 1)
; => #t

Database Info

;; Row ID of last INSERT
(sqlite-run db "INSERT INTO users (name) VALUES (?)" "Alice")
(sqlite-last-insert-rowid db)  ; => 1

;; Number of rows changed by last INSERT/UPDATE/DELETE
(sqlite-run db "UPDATE users SET name = 'Updated' WHERE id > ?" 0)
(sqlite-changes db)  ; => 3

;; Error message from last operation
(sqlite-errmsg db)  ; => "not an error" (or description on failure)

Schema Setup

sqlite-exec executes SQL that doesn't use parameters or return rows.

(sqlite-exec db "CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT,
  created_at TEXT DEFAULT CURRENT_TIMESTAMP)")
; => #t

(sqlite-exec db "CREATE INDEX IF NOT EXISTS idx_users_email ON users (email)")
; => #t

Low-Level API

For fine-grained control over statement lifecycle.

;; Prepare a statement
(define stmt (sqlite-prepare db "SELECT * FROM users WHERE age > ?"))

;; Bind parameters (1-based index)
(sqlite-bind stmt 1 21)

;; Step through results
(let loop ()
  (when (eq? (sqlite-step stmt) 'row)
    (let ((id (sqlite-column stmt 0))
          (name (sqlite-column stmt 1)))
      (display (format "~a: ~a\n" id name))
      (loop))))

;; Clean up
(sqlite-finalize stmt)

• sqlite-step returns 'row (data available), 'done (complete), or #f (error)
• sqlite-column uses 0-based indexing
• sqlite-column-count and sqlite-column-name inspect result shape
• sqlite-reset reuses a statement with new bindings
• sqlite-db? / sqlite-stmt? type predicates for handle checking

Common Patterns

CRUD Operations

(import (sigil sqlite))

(define db (sqlite-open "app.db"))
(sqlite-exec db "CREATE TABLE IF NOT EXISTS notes (
  id INTEGER PRIMARY KEY, title TEXT, body TEXT)")

;; Create
(sqlite-run db "INSERT INTO notes (title, body) VALUES (?, ?)"
            "First Note" "Hello, world!")
(define id (sqlite-last-insert-rowid db))

;; Read
(define note (sqlite-query-row db "SELECT * FROM notes WHERE id = ?" id))
(assoc-ref 'title note)  ; => "First Note"

;; Update
(sqlite-run db "UPDATE notes SET title = ? WHERE id = ?" "Updated Title" id)

;; Delete
(sqlite-run db "DELETE FROM notes WHERE id = ?" id)

(sqlite-close db)

Resource Management

(call-with-database ":memory:"
  (lambda (db)
    (sqlite-exec db "CREATE TABLE kv (key TEXT PRIMARY KEY, value TEXT)")
    (sqlite-run db "INSERT INTO kv VALUES (?, ?)" "lang" "sigil")
    (assoc-ref 'value
      (sqlite-query-row db "SELECT value FROM kv WHERE key = ?" "lang"))))
; => "sigil"