Skip to content

SQLite

Jump to bottom
Ori Pekelman edited this page May 11, 2026 · 1 revision

Tep::SQLite

A thin libsqlite3 binding wired through spinel's FFI. Same shape as sqlite3-ruby's low-level API: open / exec / prepare / bind / step / col / finalize. No ActiveRecord, no statement caching, no connection pool.

When to reach for it

Anywhere you'd otherwise reach for a small Ruby sqlite3-ruby client. The compiled binary links against libsqlite3 at runtime, so the system needs libsqlite3-dev (Debian/Ubuntu) installed at build time.

Open / close

db = Tep::SQLite.new
if db.open("/var/lib/app/data.db")   # or ":memory:"
  # ... use db ...
  db.close
end
  • open(path) returns true on success, false on failure.
  • close is idempotent and tracks its own handle; safe to call twice.
  • path may be ":memory:" for an anonymous in-memory database.
  • Opening on an already-open instance leaks the prior handle; close first.

Exec (no rows back)

db.exec("CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT)")
db.exec("INSERT INTO notes (body) VALUES ('hello')")

Returns true on success. Use for DDL and constant SQL; for user input go through prepare + bind.

Prepare + bind + step + finalize

db.prepare("INSERT INTO notes (body) VALUES (?)")
db.bind_str(1, body_from_user)
db.step          # 1 = row available, 0 = done, -1 = error
db.finalize      # always pair with prepare

Bind parameters are 1-indexed.

Method Purpose
db.prepare(sql) Opens a cursor. Returns true on success.
db.bind_str(idx, value) Bind a TEXT parameter.
db.bind_int(idx, value) Bind an INTEGER parameter.
db.step Advance the cursor. 1 row, 0 done, -1 error.
db.col_str(i) Read the i-th column as a String (i 0-indexed).
db.col_int(i) Read the i-th column as an Integer.
db.col_count Number of columns in the current row.
db.finalize Close the cursor (always pair with prepare).
db.reset Reset the cursor for re-execution.
db.last_rowid Last inserted rowid.

A NULL column reads back as "" (str) or 0 (int) — these aren't distinguished from empty values. col_str caps a single row's string column at 64 KiB (large blobs would truncate).

Conveniences

first_str and first_int cover the common "single-row, single- column" pattern (e.g. count queries, by-id lookups):

n      = db.first_int("SELECT COUNT(*) FROM notes", "")
body   = db.first_str("SELECT body FROM notes WHERE id = ?", "42")

The second argument is an optional :str bind for ? parameters (pass "" for "no param"). Always finalises the cursor before returning.

For multi-row reads, use the explicit prepare/step loop:

db.prepare("SELECT id, body FROM notes ORDER BY id DESC LIMIT 10")
while db.step == 1
  id   = db.col_int(0)
  body = db.col_str(1)
  # ... append to a result string ...
end
db.finalize

Pitfalls

  • One cursor per instance. prepare / step / finalize all operate on the most recent prepare. Don't nest queries inside another's loop — the inner prepare will overwrite the parent cursor.
  • No transactions sugar. Use db.exec("BEGIN") / db.exec("COMMIT") / db.exec("ROLLBACK") directly.
  • Errors are silent. Most methods return a boolean or status int — there's no exception. Check the return values; on -1 from step, the most likely cause is a prepare that failed (bad SQL).
  • :dbh not :handle. The internal handle ivar is named :dbh because adding attr_accessor :handle on Tep::SQLite collides with Tep::Handler#handle in spinel's same-name imeth unifier. Treat db.dbh as an opaque integer.

Why no ORM?

The framework's value-add is being small and AOT-compilable. ORMs (ActiveRecord, Sequel) lean heavily on method_missing / dynamic class generation that spinel can't lower. If you want something higher-level than raw SQL, you wrap Tep::SQLite in your own data-access layer — the indirection stays type-checkable.

Clone this wiki locally