magmalake sqlite

sqlite · API

sqlite.db

Safe Mojo API for SQLite -- Layer 2.

Provides four structs that wrap the raw FFI handles from ffi.mojo:

  • Database -- owns a SQLite connection; closes it on destruction.
  • Statement -- owns a prepared statement; finalizes it on destruction.
  • Row -- a snapshot of a single result row (value types, not a borrow).
  • Transaction -- context-manager / RAII transaction guard; commits on clean exit, rolls back automatically on exception.

Basic usage::

var db = Database(":memory:")
db.execute("CREATE TABLE t (id INTEGER, name TEXT)")
var stmt = db.prepare("INSERT INTO t VALUES (?, ?)")
stmt.bind_int(1, 1)
stmt.bind_text(2, "Alice")
_ = stmt.step()

var q = db.prepare("SELECT id, name FROM t")
while True:
    var row = q.step()
    if not row:
        break
    print(row.value().int_val(0), row.value().text_val(1))

Transaction usage -- context manager (recommended)::

with db.transaction():
    db.execute("INSERT INTO orders VALUES (1, 'Alice')")
    db.execute("INSERT INTO line_items VALUES (1, 42, 3)")
# -> COMMIT on clean exit; ROLLBACK if any statement raises

Transaction usage -- manual control::

var tx = db.transaction()       # issues BEGIN
db.execute("INSERT ...")
db.execute("INSERT ...")
tx.commit()                     # issues COMMIT; destructor becomes a no-op

Structs

Transaction

struct Transaction

RAII transaction guard -- with block commits on success, rolls back on error.

Obtain a Transaction via Database.transaction(), which issues BEGIN immediately.

Context-manager usage (recommended) -- identical to Python's with conn: pattern::

with db.transaction():
    db.execute("INSERT INTO orders VALUES (1, 'Alice')")
    db.execute("INSERT INTO line_items VALUES (1, 42, 3)")
# -> COMMIT issued automatically; both rows written atomically

If any statement inside raises, ROLLBACK is issued and the original exception propagates::

with db.transaction():
    db.execute("INSERT INTO t VALUES (1)")
    raise Error("oops")     # -> __exit__(err) -> ROLLBACK, re-raised

Manual usage (fine-grained control) -- use var tx when you need explicit guard access (conditional rollback, multiple commit points). Note: Mojo's with/__exit__ protocol requires a non-consuming __enter__, so with ... as tx: binds tx to None -- use var tx instead::

var tx = db.transaction()   # BEGIN
db.execute("INSERT ...")
if some_condition:
    tx.rollback()           # abort without raising
    return
tx.commit()                 # explicit COMMIT; destructor becomes no-op

To abandon without raising, consume the guard immediately::

var tx = db.transaction()
_ = tx^                     # guard destroyed -> immediate ROLLBACK

Note: Nested transactions are not supported via BEGIN/COMMIT. Use SAVEPOINT directly if you need nesting.

Implements Movable

__init__

def __init__(out self, handle: Int)

Begin a new transaction on handle.

Args
handle: Open sqlite3* connection handle.

Raises Error: If BEGIN fails (e.g. a transaction is already active).

__deinit__

def __deinit__(deinit self)

Issue ROLLBACK if the transaction was neither committed nor rolled back.

Errors from the implicit ROLLBACK are swallowed so destructors never raise.

commit

def commit(mut self)

Commit the transaction and make all changes permanent.

After this call the destructor becomes a no-op.

Raises Error: If COMMIT fails.

rollback

def rollback(mut self)

Explicitly roll back the transaction.

Useful when you detect a logical error and want to abort early without raising an exception. After this call the destructor becomes a no-op.

Raises Error: If ROLLBACK fails.

__enter__

def __enter__(mut self)

Enter the transaction context manager.

BEGIN was already issued in __init__, so nothing extra is needed here. Mojo's with statement calls __exit__ on this same object when the block finishes.

Note: Because __exit__ is defined, Mojo requires a non-consuming __enter__. Use with db.transaction(): (without as); for explicit guard access, use var tx = db.transaction().

__exit__

def __exit__(mut self)

Commit on clean exit.

Called automatically when the with block finishes without raising. Equivalent to calling commit() explicitly.

Raises Error: If COMMIT fails (e.g., disk full, constraint violation).

def __exit__(mut self, err: Error) -> Bool

Roll back on exception and re-raise.

Called automatically when an exception escapes the with block. Issues ROLLBACK to discard all pending changes, swallows any rollback error (so the original exception is not replaced), then returns False so the original exception continues to propagate.

Args
err: The exception raised inside the with block.

Row

struct Row

An immutable snapshot of a single result row.

All column values are copied out of the statement at construction time, so the Row outlives the Statement that produced it.

Column indices are 0-based throughout.

Implements Movable

__init__

def __init__(out self, ncols: Int, types: List[Int], ints: List[Int], floats: List[Float64], texts: List[String])

Construct a Row from pre-read column snapshots.

Args
ncols: Number of columns.
types: Per-column SQLite type codes.
ints: Per-column integer values (0 for non-integer columns).
floats: Per-column float values (0.0 for non-float columns).
texts: Per-column text values (empty for non-text columns).

num_cols

def num_cols(self) -> Int

Return the number of columns in this row.

int_val

def int_val(self, col: Int) -> Int

Return the integer value of column col.

Args
col: 0-based column index.

float_val

def float_val(self, col: Int) -> Float64

Return the floating-point value of column col.

Args
col: 0-based column index.

text_val

def text_val(self, col: Int) -> String

Return the text value of column col.

Args
col: 0-based column index.

is_null

def is_null(self, col: Int) -> Bool

Check whether column col contains SQL NULL.

Args
col: 0-based column index.

Statement

struct Statement

A compiled, prepared SQLite statement.

Finalizes the underlying sqlite3_stmt on destruction. Parameters use 1-based indexing (as in the SQLite C API).

Implements Movable

__init__

def __init__(out self, db: Int, sql: String)

Compile a SQL statement.

Args
db: sqlite3 handle of the owning connection.
sql: A single SQL statement (no trailing semicolon needed).

Raises Error: If the SQL fails to compile.

__deinit__

def __deinit__(deinit self)

Finalize the statement and release its resources.

reset

def reset(self)

Reset the statement so it can be re-executed.

Raises Error: If the reset call fails.

bind_int

def bind_int(self, idx: Int, val: Int)

Bind an integer value to parameter idx.

Args
idx: 1-based parameter index.
val: Integer value.

Raises Error: On binding failure.

bind_float

def bind_float(self, idx: Int, val: Float64)

Bind a floating-point value to parameter idx.

Args
idx: 1-based parameter index.
val: Float64 value.

Raises Error: On binding failure.

bind_text

def bind_text(self, idx: Int, val: String)

Bind a text value to parameter idx.

Args
idx: 1-based parameter index.
val: String value (copied by SQLite).

Raises Error: On binding failure.

bind_null

def bind_null(self, idx: Int)

Bind SQL NULL to parameter idx.

Args
idx: 1-based parameter index.

Raises Error: On binding failure.

step

def step(self) -> Optional[Row]

Advance the statement by one step.

Reads all column values into a Row snapshot when a row is available, so the caller does not need to keep the statement alive.

Raises Error: If sqlite3_step returns an error code.

Database

struct Database

A SQLite database connection.

Opens (or creates) the database file on construction and closes the connection on destruction. Use :memory: for a transient in-memory database.

Example::

var db = Database(":memory:")
db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")

Implements Movable

__init__

def __init__(out self, path: String)

Open or create a SQLite database.

Args
path: File-system path to the database, or :memory:.

Raises Error: If sqlite3_open fails.

__deinit__

def __deinit__(deinit self)

Close the database connection.

execute

def execute(self, sql: String)

Execute one or more SQL statements with no result rows.

Suitable for DDL (CREATE TABLE, DROP TABLE) and DML without a return value (INSERT ... VALUES ..., DELETE ...).

Args
sql: Semicolon-separated SQL text.

Raises Error: If sqlite3_exec fails.

prepare

def prepare(self, sql: String) -> Statement

Compile a SQL statement for repeated execution.

Args
sql: A single SQL statement.

Raises Error: If the SQL fails to compile.

transaction

def transaction(self) -> Transaction

Begin a new transaction and return an RAII guard.

The guard issues BEGIN immediately. Call commit() on the returned Transaction to persist changes. If the guard is destroyed before commit() (exception, early return, or explicit rollback()), a ROLLBACK is issued automatically.

Example::

var tx = db.transaction()
db.execute("INSERT INTO orders VALUES (1, 'Alice')")
db.execute("INSERT INTO line_items VALUES (1, 42, 3)")
tx.commit()   # both rows committed atomically.

Raises Error: If BEGIN fails (e.g. a transaction is already open).

changes

def changes(self) -> Int

Return rows inserted, updated or deleted by the most recent statement.

The value a guarded write checks: an UPDATE ... WHERE whose predicate matched nothing reports 0, which is how optimistic concurrency detects that it lost the race rather than silently succeeding.

Raises Error: Only if libsqlite3 could not be loaded, which cannot happen once a Database exists.

total_changes

def total_changes(self) -> Int

Return rows changed by every statement since this connection opened.

Raises Error: Only if libsqlite3 could not be loaded.

last_insert_rowid

def last_insert_rowid(self) -> Int

Return the rowid of the most recent successful INSERT.

Raises Error: Only if libsqlite3 could not be loaded.

last_error

def last_error(self) -> String

Return the most recent error message for this connection.

Raises Error: Only if libsqlite3 could not be loaded, which cannot happen once a Database exists.