#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.
• Common SQLite result code constants exported for matching and diagnostics.
• Ships with sqlite3.c and sqlite3.h, so it does not rely on a system-installed SQLite.

#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
      #|);
    ),
  )
  create.step_once()
  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)
  insert.step_once()
  insert.finalize()

  let query = conn.prepare(
    (
      #|SELECT id, name, score FROM users;
    ),
  )
  assert_true(query.step())
  let id : Int = query.column(index=0)
  let name : String = query.column(index=1)
  let score : Double = query.column(index=2)
  assert_eq(id, 1)
  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, or a file path such as "app.db" for a persistent one.
2. Connection::prepare creates a prepared statement.
3. For statements that do not return rows, call Statement::step_once().
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.
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
      #|);
    ),
  )
  create.step_once()
  create.finalize()

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

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

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

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

  assert_true(query.step())
  let second_id : Int = query.column(index=0)
  let second_payload : Bytes = query.column(index=1)
  assert_eq(second_id, 2)
  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:")

  @test.assert_raise(() => conn.prepare("SELECT FROM"))

  assert_true(conn.get_errmsg().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.
• Connection::get_errmsg(): read the most recent error message from the connection.

#Statement

• Statement::bind(index, value): bind a parameter. Parameter indexes start at 1, matching the SQLite C API.
• Statement::step(): execute one step. Query statements return true when a row is available and false when iteration is complete.
• Statement::step_once(): execute once and require that the statement produces no row. This is suitable for CREATE, INSERT, UPDATE, and DELETE. If the statement does return a row, it raises SQLITE_ROW.
• Statement::column(index): read a column value from the current row. Column indexes start at 0.
• Statement::finalize(): destroy the prepared statement and release its native resources.

#Bind and Column

#Constraints and Notes

• This is a manual resource management API. Every Statement should be explicitly finalize()d, and every Connection should be explicitly close()d.
• The current public API does not expose reset, so a statement that has already been executed should generally be treated as a one-shot object. If you want to run it again, preparing a new statement is the simplest path.
• Parameter indexes start at 1, while column indexes start at 0. It is easy to mix these up.
• String values are encoded as UTF-8 when bound. String reads use lossy UTF-8 decoding. If you need lossless raw byte handling, use Bytes instead.
• There is currently no public API for binding or decoding NULL, and no public column-type inspection API. If you need to distinguish NULL precisely, you will need to extend the library.
• The package is intentionally focused on SQLite basics and does not add transaction wrappers, batch helpers, or named-parameter support.

#Result Code Constants

• SQLITE_OK
• SQLITE_ROW
• SQLITE_DONE
• SQLITE_ERROR
• SQLITE_BUSY
• SQLITE_MISUSE
• SQLITE_CONSTRAINT
• SQLITE_CANTOPEN

#Development and Verification

moon check
moon test
moon info
moon fmt