#SQLite3.mbt

#Features

• Thin wrapper design with a small API surface that stays close to SQLite's native workflow.
• Support for prepared statements, positional parameter binding, and row-by-row result reading.
• Structured primary and extended error codes with the SQLite diagnostic message captured at the failure site.
• The native backend ships with sqlite3.c and sqlite3.h, so it does not rely on a system-installed SQLite.
• The Wasm backend keeps SQLite pointers inside the host and represents connections and statements with opaque handles.

#Installation

moon add moonbit-community/sqlite3

#Quick Start

///|
test "quick start" {
  let conn = @sqlite3.Connection::open(":memory:")

  let create = conn.prepare(
    (
      #|CREATE TABLE users (
      #|  id INTEGER PRIMARY KEY,
      #|  name TEXT NOT NULL,
      #|  score REAL NOT NULL
      #|);
    ),
  )
  assert_eq(create.step(), false)
  create.finalize()

  let insert = conn.prepare(
    (
      #|INSERT INTO users (id, name, score) VALUES (?, ?, ?);
    ),
  )
  insert.bind(index=1, 1)
  insert.bind(index=2, "alice")
  insert.bind(index=3, 98.5)
  assert_eq(insert.step(), false)
  insert.finalize()

  let query = conn.prepare(
    (
      #|SELECT id, name, score FROM users;
    ),
  )
  assert_true(query.step())
  let id : Int64 = query.column(index=0)
  let name : String = query.column(index=1)
  let score : Double = query.column(index=2)
  assert_eq(id, 1L)
  assert_eq(name, "alice")
  assert_eq(score.to_int(), 98)
  assert_eq(query.step(), false)
  query.finalize()

  conn.close()
}

#Workflow Summary

1. Connection::open opens the database. Pass ":memory:" for an in-memory database. File paths such as "app.db" work when the selected backend and runtime grant filesystem access.
2. Connection::prepare creates a prepared statement.
3. Call Statement::step() to advance the statement. Statements that do not return rows normally return false on the first call, meaning execution is complete.
4. For queries, call Statement::step() repeatedly. It returns true when a row is available and false when the result set is exhausted.
5. Use Statement::column(index=...) to read column values from the current row. It raises SqliteError with code=Misuse unless the preceding step() returned true.
6. Call Statement::finalize() to release the statement.
7. Call Connection::close() when you are done with the connection.

#Multi-Row Queries and BLOB Values

///|
test "blob round trip" {
  let conn = @sqlite3.Connection::open(":memory:")

  let create = conn.prepare(
    (
      #|CREATE TABLE files (
      #|  id INTEGER PRIMARY KEY,
      #|  payload BLOB NOT NULL
      #|);
    ),
  )
  assert_eq(create.step(), false)
  create.finalize()

  let insert1 = conn.prepare(
    (
      #|INSERT INTO files (id, payload) VALUES (?, ?);
    ),
  )
  insert1.bind(index=1, 1)
  insert1.bind(index=2, b"abc")
  assert_eq(insert1.step(), false)
  insert1.finalize()

  let insert2 = conn.prepare(
    (
      #|INSERT INTO files (id, payload) VALUES (?, ?);
    ),
  )
  insert2.bind(index=1, 2)
  insert2.bind(index=2, b"xyz")
  assert_eq(insert2.step(), false)
  insert2.finalize()

  let query = conn.prepare(
    (
      #|SELECT id, payload FROM files ORDER BY id;
    ),
  )

  assert_true(query.step())
  let first_id : Int64 = query.column(index=0)
  let first_payload : Bytes = query.column(index=1)
  assert_eq(first_id, 1L)
  assert_eq(first_payload, b"abc")

  assert_true(query.step())
  let second_id : Int64 = query.column(index=0)
  let second_payload : Bytes = query.column(index=1)
  assert_eq(second_id, 2L)
  assert_eq(second_payload, b"xyz")

  assert_eq(query.step(), false)
  query.finalize()

  conn.close()
}

#Error Handling

///|
test "error handling" {
  let conn = @sqlite3.Connection::open(":memory:")

  let error = @test.expect_error(() => conn.prepare("SELECT FROM"))
  match error {
    @sqlite3.SqliteError(code~, extended~, msg~) => {
      assert_eq(code, @sqlite3.ErrorCode::Error)
      assert_eq(extended, None)
      assert_true(msg.length() > 0)
    }
  }
  conn.close()
}

#Public API Overview

#Connection

• Connection::open(filename): open a database connection.
• Connection::prepare(sql): create a prepared statement.
• Connection::close(): close the database connection.

#Statement

• Statement::bind(index, value): bind a parameter. Parameter indexes start at 1, matching the SQLite C API.
• Statement::step(): advance the statement once. It returns true when a row is available and false when execution is complete. CREATE, INSERT, UPDATE, and DELETE statements without a RETURNING clause normally return false on the first call.
• Statement::column(index): read a column value from the current row. Column indexes start at 0; calling it before step() yields a row, after step() returns false, or after finalization raises an error with code=Misuse.
• Statement::finalize(): destroy the prepared statement and release its native resources.

#Bind and Column

///|
test "dynamic value" {
  let conn = @sqlite3.Connection::open(":memory:")
  let stmt = conn.prepare("SELECT ?")
  stmt.bind(index=1, @sqlite3.Value::Null)
  assert_true(stmt.step())
  let value : @sqlite3.Value = stmt.column(index=0)
  assert_eq(value, @sqlite3.Value::Null)
  stmt.finalize()
  conn.close()
}

#Constraints and Notes

• This is a manual resource management API. Every Statement must be explicitly finalize()d, and every Connection must be explicitly close()d. Dropping these values does not release SQLite resources on any backend.
• The Wasm backend requires a runtime that provides the moonbitlang/sqlite imports. Filesystem access and SQL policy are enforced by the host runtime.
• The bundled native SQLite 3.49.1 build and the pinned moonrun SQLite 3.53.2 build both use SQLite's automatic reset behavior: calling step() again after it returns false reruns the statement with its existing bindings. The public API does not expose reset, so prepare a new statement when you need to change bindings between executions.
• Connection::prepare accepts exactly one SQL statement. Empty input, comment-only input, and additional statements after the first one raise an error with code=Misuse; trailing whitespace, comments, and empty semicolons are allowed.
• Parameter indexes start at 1, while column indexes start at 0. It is easy to mix these up.
• SQL and String values cross both backend boundaries as UTF-16 code units. Native targets require little-endian UTF-16, while WebAssembly memory is little-endian by definition. SQLite converts text when the database file uses a different encoding. Use Bytes when the value is raw binary data rather than text.
• Connection::open uses sqlite3_open_v2, so a new database defaults to UTF-8. To select UTF-16LE or UTF-16BE storage, run PRAGMA encoding before creating any schema objects; this choice is independent of the native string API.
• The package does not expose declared column types or result-column names. Value reports the initial runtime storage class of a value in the current row. That class is cached before typed coercion, so reading the same cell through a typed decoder first does not change the later Value variant.
• The package is intentionally focused on SQLite basics and does not add transaction wrappers, batch helpers, or named-parameter support.

#Error Codes

#Development and Verification

moon check
moon test
moon info
moon fmt