docs/sqlite.md — sigil-sqlite

1# SQLite

3> SQLite database bindings with high-level query helpers.

5```scheme

6(import (sigil sqlite))

7```

9## Opening and Closing

11```scheme

12;; Open a database file (created if it doesn't exist)

13(define db (sqlite-open "app.db"))

15;; In-memory database

16(define db (sqlite-open ":memory:"))

18;; Close when done

19(sqlite-close db)

21;; Resource-safe pattern: auto-closes on exit

22(call-with-database "app.db"

23 (lambda (db)

24 (sqlite-query db "SELECT * FROM users")))

25```

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

29## Type Mapping

31| SQLite | Scheme | Notes |

32|---------|--------------|--------------------------|

33| NULL | `#f` | Read and write |

34| INTEGER | integer | 64-bit signed |

35| REAL | flonum | IEEE 754 double |

36| TEXT | string | UTF-8 |

37| BLOB | bytevector | `#u8(...)` read and write |

39## High-Level API

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

43### sqlite-query

45Execute a query and return all rows as alists.

47```scheme

48(sqlite-query db "SELECT * FROM users")

49; => (((id . 1) (name . "Alice")) ((id . 2) (name . "Bob")))

51;; With parameter binding

52(sqlite-query db "SELECT * FROM users WHERE age > ?" 21)

53; => (((id . 1) (name . "Alice") (age . 30)))

55;; Multiple parameters

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

58;; No results returns empty list

59(sqlite-query db "SELECT * FROM users WHERE id = ?" 999)

60; => ()

61```

63Column names become symbols in the alist keys.

65### sqlite-query-row

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

69```scheme

70(define user (sqlite-query-row db "SELECT * FROM users WHERE id = ?" 1))

71; => ((id . 1) (name . "Alice") (email . "[email protected]"))

73(assoc-ref 'name user) ; => "Alice"

74(assoc-ref 'email user) ; => "[email protected]"

76(sqlite-query-row db "SELECT * FROM users WHERE id = ?" 999)

77; => #f

78```

80### sqlite-run

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

84```scheme

85(sqlite-run db "INSERT INTO users (name, email) VALUES (?, ?)"

86 "Alice" "[email protected]")

87; => #t

89(sqlite-run db "UPDATE users SET name = ? WHERE id = ?" "Bob" 1)

90; => #t

92(sqlite-run db "DELETE FROM users WHERE id = ?" 1)

93; => #t

94```

96## Database Info

98```scheme

99;; Row ID of last INSERT

100(sqlite-run db "INSERT INTO users (name) VALUES (?)" "Alice")

101(sqlite-last-insert-rowid db) ; => 1

102

103;; Number of rows changed by last INSERT/UPDATE/DELETE

104(sqlite-run db "UPDATE users SET name = 'Updated' WHERE id > ?" 0)

105(sqlite-changes db) ; => 3

106

107;; Error message from last operation

108(sqlite-errmsg db) ; => "not an error" (or description on failure)

109```

110

111## Schema Setup

112

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

114

115```scheme

116(sqlite-exec db "CREATE TABLE IF NOT EXISTS users (

117 id INTEGER PRIMARY KEY,

118 name TEXT NOT NULL,

119 email TEXT,

120 created_at TEXT DEFAULT CURRENT_TIMESTAMP)")

121; => #t

122

123(sqlite-exec db "CREATE INDEX IF NOT EXISTS idx_users_email ON users (email)")

124; => #t

125```

126

127## Low-Level API

128

129For fine-grained control over statement lifecycle.

130

131```scheme

132;; Prepare a statement

133(define stmt (sqlite-prepare db "SELECT * FROM users WHERE age > ?"))

134

135;; Bind parameters (1-based index)

136(sqlite-bind stmt 1 21)

137

138;; Step through results

139(let loop ()

140 (when (eq? (sqlite-step stmt) 'row)

141 (let ((id (sqlite-column stmt 0))

142 (name (sqlite-column stmt 1)))

143 (display (format "~a: ~a\n" id name))

144 (loop))))

145

146;; Clean up

147(sqlite-finalize stmt)

148```

149

150- `sqlite-step` returns `'row` (data available), `'done` (complete), or `#f` (error)

151- `sqlite-column` uses 0-based indexing

152- `sqlite-column-count` and `sqlite-column-name` inspect result shape

153- `sqlite-reset` reuses a statement with new bindings

154- `sqlite-db?` / `sqlite-stmt?` type predicates for handle checking

155

156## Common Patterns

157

158### CRUD Operations

159

160```scheme

161(import (sigil sqlite))

162

163(define db (sqlite-open "app.db"))

164(sqlite-exec db "CREATE TABLE IF NOT EXISTS notes (

165 id INTEGER PRIMARY KEY, title TEXT, body TEXT)")

166

167;; Create

168(sqlite-run db "INSERT INTO notes (title, body) VALUES (?, ?)"

169 "First Note" "Hello, world!")

170(define id (sqlite-last-insert-rowid db))

171

172;; Read

173(define note (sqlite-query-row db "SELECT * FROM notes WHERE id = ?" id))

174(assoc-ref 'title note) ; => "First Note"

175

176;; Update

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

178

179;; Delete

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

181

182(sqlite-close db)

183```

184

185### Resource Management

186

187```scheme

188(call-with-database ":memory:"

189 (lambda (db)

190 (sqlite-exec db "CREATE TABLE kv (key TEXT PRIMARY KEY, value TEXT)")

191 (sqlite-run db "INSERT INTO kv VALUES (?, ?)" "lang" "sigil")

192 (assoc-ref 'value

193 (sqlite-query-row db "SELECT value FROM kv WHERE key = ?" "lang"))))

194; => "sigil"

195```