#MoonBit SQLite3 Binding

#Installation

moon add myfreess/sqlite3

{
  "import": [
    "myfreess/sqlite3"
  ]
}

#Quick Start

fn main {
  // Open database connection
  let conn = @sqlite3.Connection::open("example.db")

  // Create table
  let stmt = conn.prepare("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")
  stmt.step_once()
  stmt.finalize()

  // Insert data
  let stmt = conn.prepare("INSERT INTO users (name, age) VALUES (?, ?)")
  stmt.bind_string_as_blob(index=1, val="Alice")
  stmt.bind(index=2, val=25)
  stmt.step_once()
  stmt.finalize()

  // Query data
  let stmt = conn.prepare("SELECT id, name, age FROM users WHERE age > ?")
  stmt.bind(index=1, val=20)

  while stmt.step() {
    let id : Int = stmt.column(index=0)
    let name : String = stmt.column_blob_as_string(index=1)
    let age : Int = stmt.column(index=2)
    println("User: id=\{id}, name=\{name}, age=\{age}")
  }

  stmt.finalize()
  conn.close()
}

#API Reference

#Connection

#Connection::open(filename: String) -> Connection

let conn = @sqlite3.Connection::open("database.db")
// For in-memory database:
let conn = @sqlite3.Connection::open(":memory:")

#Connection::close(self) -> Unit

#Connection::prepare(self, sql: String) -> Statement

#Connection::get_errmsg(self) -> String

#Statement

#Statement::bind(self, index: Int, val: T) -> Unit

• Int
• Int64
• Double
• Bytes

stmt.bind(index=1, val=42)        // Bind integer
stmt.bind(index=2, val=3.14)      // Bind double
stmt.bind(index=3, val=some_bytes) // Bind bytes

#Statement::bind_string_as_blob(self, index: Int, val: String) -> Unit

stmt.bind_string_as_blob(index=1, val="Hello, World!")

#Statement::step(self) -> Bool

#Statement::step_once(self) -> Unit

#Statement::column(self, index: Int) -> T

• Int
• Int64
• Double
• Bytes

let id : Int = stmt.column(index=0)
let price : Double = stmt.column(index=1)
let data : Bytes = stmt.column(index=2)

#Statement::column_blob_as_string(self, index: Int) -> String

#Statement::finalize(self) -> Unit

#Error Handling

fn example() -> Unit raise SqliteError {
  let conn = @sqlite3.Connection::open("test.db")
  // Database operations that might throw SqliteError
  conn.close()
}

// Handle errors with try-catch
try {
  example()
} catch {
  SqliteError(code, loc) => println("SQLite error \{code} at \{loc}")
}

#Type Mapping

#Complete Example

fn database_example() -> Unit raise SqliteError {
  // Open database
  let conn = @sqlite3.Connection::open(":memory:")

  // Create table
  let create_stmt = conn.prepare(
    "CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL, data BLOB)"
  )
  create_stmt.step_once()
  create_stmt.finalize()

  // Insert data
  let insert_stmt = conn.prepare(
    "INSERT INTO products (name, price, data) VALUES (?, ?, ?)"
  )
  insert_stmt.bind_string_as_blob(index=1, val="Widget")
  insert_stmt.bind(index=2, val=19.99)
  insert_stmt.bind(index=3, val=b"\x01\x02\x03")
  insert_stmt.step_once()
  insert_stmt.finalize()

  // Query data
  let select_stmt = conn.prepare("SELECT * FROM products WHERE price > ?")
  select_stmt.bind(index=1, val=10.0)

  while select_stmt.step() {
    let id : Int = select_stmt.column(index=0)
    let name = select_stmt.column_blob_as_string(index=1)
    let price : Double = select_stmt.column(index=2)
    let data : Bytes = select_stmt.column(index=3)

    println("Product: \{id}, \{name}, $\{price}")
  }

  select_stmt.finalize()
  conn.close()
}

#SQLite Constants

• SQLITE_OK (0) - Success
• SQLITE_ERROR (1) - SQL error or missing database
• SQLITE_ROW (100) - Step has returned a row
• SQLITE_DONE (101) - Step has finished executing
• And many more...

#Platform Support

#Contributing

• Additional utility functions
• Better error messages
• Documentation improvements
• Test coverage

#License