# sqlite.ffi > A module of sqlite's API. Low-level FFI wrappers for the SQLite C library. All sqlite3 handles (``sqlite3*`` and ``sqlite3_stmt*``) are stored as ``Int`` (pointer address). Input C strings are passed as ``Int`` via ``unsafe_ptr() → Int`` cast. Output C-string return values are received as ``Pointer[UInt8, MutUntrackedOrigin]`` and immediately copied into owned ``String`` values via ``StringSlice``. The library is loaded at runtime via ``OwnedDLHandle`` so Mojo's JIT never needs to resolve SQLite symbols at compile time, eliminating the ``JIT session error: Symbols not found`` failure on Linux. The handle is opened, and every entry point resolved, **once per process** (``_FFI`` below): a ``dlopen``/``dlclose`` cycle of an already-resident library costs around 450 microseconds on macOS, so opening one per ``Database`` — let alone per ``Statement`` or ``Transaction``, as this binding used to — made the fixed cost of preparing a query dwarf the query itself. Do not call ``Sqlite3FFI`` methods from user code -- use ``db.mojo``. ## Structs ### `Sqlite3FFI` ```mojo struct Sqlite3FFI ``` Runtime-loaded SQLite FFI: ``dlopen`` + ``dlsym`` for all C entry-points. Loads ``libsqlite3`` at construction via ``OwnedDLHandle`` and resolves every function pointer once. All opaque pointer arguments (``sqlite3*``, ``sqlite3_stmt*``) are represented as ``Int`` (64-bit on all supported platforms), matching the C ABI on x86-64 and arm64 without requiring ``Pointer`` type annotations. **Construct this exactly once per process** -- reach it through ``sqlite_ffi()``, which returns a borrow of the ``_Global`` instance. ``RTLD.NODELETE`` additionally makes ``dlclose`` a no-op, so the library image stays resident (and its VFS state, WAL locks and shared-memory mappings intact) for the process lifetime. Because ``_dl_sym`` copies each symbol's address out into a plain function value, calls do not borrow the handle; the handle only has to outlive them, which the ``_Global`` guarantees. Example:: ref ffi = sqlite_ffi() var db = ffi.open(":memory:") ffi.exec(db, "CREATE TABLE t (x INTEGER)") _ = ffi.close(db) Implements: `Movable` #### `__init__` ```mojo def __init__(out self, lib_path: String = "") ``` Load ``libsqlite3`` and resolve all function pointers. Args: - `lib_path`: Explicit path to the library. If empty, the soname is looked up by ``_dlopen.open_library``: ``$CONDA_PREFIX/lib``, next to the executable and in its ``../lib``, then the dynamic linker's search path (the build environment's lib via the binary's rpath, then the system), so it works with or without ``$CONDA_PREFIX``. Raises: Error: If the library cannot be opened (the message names every place searched and says to set ``CONDA_PREFIX``) or a symbol is missing. #### `open` ```mojo def open(self, filename: String) -> Int ``` Open or create a SQLite database file. Uses a ``List[Int]`` as the output buffer for the ``sqlite3**`` argument (same pattern as simdjson FFI for output pointer args). Passes an explicit null-terminated copy of ``filename`` (same pattern as ``exec``) to avoid the Mojo ``String`` quirk where reused heap buffers may contain stale bytes after the logical string end. Args: - `filename`: File path or ``:memory:`` for an in-memory database. Raises: Error: If ``sqlite3_open`` returns a non-zero code. #### `close` ```mojo def close(self, db: Int) -> Int32 ``` Close the database connection. Args: - `db`: ``sqlite3`` handle. #### `errmsg` ```mojo def errmsg(self, db: Int) -> String ``` Return the most recent error message for the connection. Args: - `db`: ``sqlite3`` handle. #### `exec` ```mojo def exec(self, db: Int, sql: String) ``` Execute one or more SQL statements with no result rows. ``sqlite3_exec`` reads until a null byte, so we build an explicit ``List[UInt8]`` copy of *sql* and append a ``0`` byte before calling into C. ``String.unsafe_ptr()`` returns a read-only pointer in this Mojo version, so we cannot patch the null byte in-place. The copy eliminates the Mojo ``String`` quirk where reused heap buffers may contain stale bytes after the logical string end. Args: - `db`: ``sqlite3`` handle. - `sql`: Semicolon-separated SQL text (DDL, DML, PRAGMA, etc.). Raises: Error: If ``sqlite3_exec`` returns a non-zero code. #### `prepare_v2` ```mojo def prepare_v2(self, db: Int, sql: String) -> Int ``` Compile SQL text into a prepared statement. Uses a ``List[Int]`` as the output buffer for the ``sqlite3_stmt**`` argument. Passes ``len(sql)`` as the explicit byte count to ``sqlite3_prepare_v2`` (instead of ``-1``) so SQLite never reads beyond the valid bytes. This avoids a Mojo ``String`` quirk where reused heap buffers may contain stale bytes after the logical string end, which with ``nByte = -1`` causes spurious parse errors (e.g. ``near "NTEGER": syntax error``). Args: - `db`: ``sqlite3`` handle. - `sql`: A single SQL statement (without trailing semicolon). Raises: Error: If compilation fails. #### `step` ```mojo def step(self, stmt: Int) -> Int32 ``` Advance a prepared statement by one step. Args: - `stmt`: ``sqlite3_stmt`` handle. #### `reset` ```mojo def reset(self, stmt: Int) -> Int32 ``` Reset a prepared statement for re-execution. Args: - `stmt`: ``sqlite3_stmt`` handle. #### `finalize` ```mojo def finalize(self, stmt: Int) ``` Destroy a prepared statement and free its resources. Args: - `stmt`: ``sqlite3_stmt`` handle. #### `bind_int` ```mojo def bind_int(self, stmt: Int, idx: Int, val: Int) ``` Bind an integer value to a statement parameter (1-based index). Wraps ``sqlite3_bind_int64``. Args: - `stmt`: ``sqlite3_stmt`` handle. - `idx`: Parameter index (1-based). - `val`: Integer value. Raises: Error: On binding failure. #### `bind_double` ```mojo def bind_double(self, stmt: Int, idx: Int, val: Float64) ``` Bind a floating-point value to a statement parameter. Args: - `stmt`: ``sqlite3_stmt`` handle. - `idx`: Parameter index (1-based). - `val`: Float64 value. Raises: Error: On binding failure. #### `bind_text` ```mojo def bind_text(self, stmt: Int, idx: Int, val: String) ``` Bind a text value to a statement parameter (SQLITE_TRANSIENT copy). Passes the explicit byte length (``len(v)``) to ``sqlite3_bind_text`` instead of ``-1`` to avoid the Mojo ``String`` null-termination quirk where reused buffers may contain stale bytes after the logical end. Args: - `stmt`: ``sqlite3_stmt`` handle. - `idx`: Parameter index (1-based). - `val`: String value (copied by SQLite). Raises: Error: On binding failure. #### `bind_null` ```mojo def bind_null(self, stmt: Int, idx: Int) ``` Bind SQL NULL to a statement parameter. Args: - `stmt`: ``sqlite3_stmt`` handle. - `idx`: Parameter index (1-based). Raises: Error: On binding failure. #### `column_count` ```mojo def column_count(self, stmt: Int) -> Int ``` Return the number of columns in the current result row. Args: - `stmt`: ``sqlite3_stmt`` handle. #### `changes` ```mojo def changes(self, db: Int) -> Int ``` Return the rows inserted, updated or deleted by the most recent statement. Args: - `db`: ``sqlite3`` handle. #### `total_changes` ```mojo def total_changes(self, db: Int) -> Int ``` Return rows changed by all statements since the connection opened. Args: - `db`: ``sqlite3`` handle. #### `last_insert_rowid` ```mojo def last_insert_rowid(self, db: Int) -> Int ``` Return the rowid of the most recent successful INSERT. Args: - `db`: ``sqlite3`` handle. #### `column_type` ```mojo def column_type(self, stmt: Int, col: Int) -> Int ``` Return the SQLite type code of a column value (0-based index). Args: - `stmt`: ``sqlite3_stmt`` handle. - `col`: Column index (0-based). #### `column_int` ```mojo def column_int(self, stmt: Int, col: Int) -> Int ``` Read an integer column value (0-based index). Args: - `stmt`: ``sqlite3_stmt`` handle. - `col`: Column index (0-based). #### `column_double` ```mojo def column_double(self, stmt: Int, col: Int) -> Float64 ``` Read a floating-point column value (0-based index). Args: - `stmt`: ``sqlite3_stmt`` handle. - `col`: Column index (0-based). #### `column_text` ```mojo def column_text(self, stmt: Int, col: Int) -> String ``` Read a text column value (0-based index). Args: - `stmt`: ``sqlite3_stmt`` handle. - `col`: Column index (0-based). ## Functions ### `sqlite_ffi` ```mojo def sqlite_ffi() -> ref[MutUntrackedOrigin] Sqlite3FFI ``` The process-wide FFI table, borrowed. Never destroy the referent. Raises: Error: If libsqlite3 can't be loaded (see ``Sqlite3FFI.__init__``). ## Aliases ### `SQLITE_OK` ```mojo comptime SQLITE_OK = 0 ``` ### `SQLITE_ROW` ```mojo comptime SQLITE_ROW = 100 ``` ### `SQLITE_DONE` ```mojo comptime SQLITE_DONE = 101 ``` ### `SQLITE_INTEGER` ```mojo comptime SQLITE_INTEGER = 1 ``` ### `SQLITE_FLOAT` ```mojo comptime SQLITE_FLOAT = 2 ``` ### `SQLITE_TEXT` ```mojo comptime SQLITE_TEXT = 3 ``` ### `SQLITE_BLOB` ```mojo comptime SQLITE_BLOB = 4 ``` ### `SQLITE_NULL` ```mojo comptime SQLITE_NULL = 5 ```