# sqlite.db

> A module of sqlite's API.

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`

```mojo
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__`

```mojo
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__`

```mojo
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`

```mojo
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`

```mojo
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__`

```mojo
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__`

```mojo
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).

```mojo
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`

```mojo
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__`

```mojo
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`

```mojo
def num_cols(self) -> Int
```

Return the number of columns in this row.

#### `int_val`

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

Return the integer value of column ``col``.

Args:

- `col`: 0-based column index.

#### `float_val`

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

Return the floating-point value of column ``col``.

Args:

- `col`: 0-based column index.

#### `text_val`

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

Return the text value of column ``col``.

Args:

- `col`: 0-based column index.

#### `is_null`

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

Check whether column ``col`` contains SQL NULL.

Args:

- `col`: 0-based column index.

### `Statement`

```mojo
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__`

```mojo
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__`

```mojo
def __deinit__(deinit self)
```

Finalize the statement and release its resources.

#### `reset`

```mojo
def reset(self)
```

Reset the statement so it can be re-executed.

Raises: Error: If the reset call fails.

#### `bind_int`

```mojo
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`

```mojo
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`

```mojo
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`

```mojo
def bind_null(self, idx: Int)
```

Bind SQL NULL to parameter ``idx``.

Args:

- `idx`: 1-based parameter index.

Raises: Error: On binding failure.

#### `step`

```mojo
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`

```mojo
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__`

```mojo
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__`

```mojo
def __deinit__(deinit self)
```

Close the database connection.

#### `execute`

```mojo
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`

```mojo
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`

```mojo
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`

```mojo
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`

```mojo
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`

```mojo
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`

```mojo
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.