#moondb

Moved on mooncakes from Lfan-ke/moondb to moonbitstack/moondb.

flowchart TD
    subgraph layers["query layers — build on moondb"]
      ORM["moonorm<br/><small>SQLAlchemy-style ORM / query builder</small>"]
      APP["your app / repository code"]
    end
    IFACE(["<b>@moondb</b><br/><small>Value · Row · ExecResult · DbError · Driver</small>"])
    subgraph drivers["drivers — implement moondb"]
      SQLITE["moonsqlite<br/><small>C-FFI, native</small>"]
      PG["moonpostgres<br/><small>pure-MoonBit wire</small>"]
      MYSQL["moonmysql<br/><small>pure-MoonBit wire</small>"]
      MOCK["MockDriver<br/><small>in-memory, ships here</small>"]
    end
    APP --> ORM --> IFACE
    IFACE --> SQLITE
    IFACE --> PG
    IFACE --> MYSQL
    IFACE --> MOCK

#Install

moon add moonbitstack/moondb

#The interface at a glance

#The binding contract

#Value cases

#Quickstart

test "round-trip through the interface" {
  // A query layer is written against the `Driver` trait, not a concrete backend.
  let db = @moondb.MockDriver::new()

  // Bind values as parameters — never string-interpolated into the SQL.
  db.execute("INSERT INTO hero (id, name) VALUES (?, ?)", [Int(1), Text("Nova")])
  |> ignore

  db.begin()
  db.execute("INSERT INTO hero (id, name) VALUES (?, ?)", [Int(2), Text("Iris")])
  |> ignore
  db.rollback() // Iris is undone; Nova remains.

  let rows = db.query("SELECT id, name FROM hero", [])
  assert_eq(rows.length(), 1)
  assert_eq(rows[0].int_by("c0"), 1)
  assert_eq(rows[0].text_by("c1"), "Nova")
}

#Your own types

test "read a nullable column as an option" {
  let row : @moondb.Row = { columns: ["id", "age"], values: [Int(1), Null] }
  let id : Int64 = row.read(0)
  let age : Int? = row.read_by("age")
  assert_eq((id, age), (1L, None))
}

struct Email(String)

impl @moondb.ToValue for Email with fn to_value(self) {
  let Email(s) = self
  Text(s)
}

impl @moondb.FromValue for Email with fn from_value(v) {
  match v {
    Text(s) if s.contains("@") => Email(s)
    other => raise TypeError(other.kind() + ", not Email")
  }
}

#One statement, many runs

test "one statement, two bindings" {
  let db = @moondb.MockDriver::new()
  let stmt = db.prepare("INSERT INTO hero (id, name) VALUES (?, ?)")
  stmt.bind([@moondb.Value::Int(1), @moondb.Value::Text("Nova")])
  stmt.execute() |> ignore
  stmt.bind([@moondb.Value::Int(2), @moondb.Value::Text("Iris")])
  stmt.execute() |> ignore
  stmt.close()
  assert_eq(db.row_count(), 2)
}

#Named parameters

test "the same named statement for every backend" {
  let sql = "SELECT * FROM hero WHERE id = :id AND realm = :realm"
  let pg = @moondb.rewrite_named(sql, @moondb.Dialect::Postgres)
  assert_eq(pg.sql, "SELECT * FROM hero WHERE id = $1 AND realm = $2")

  let lite = @moondb.rewrite_named(sql, @moondb.Dialect::Sqlite)
  assert_eq(lite.sql, "SELECT * FROM hero WHERE id = ? AND realm = ?")

  let args : Map[String, @moondb.Value] = Map([])
  args.set("id", @moondb.Value::Int(7))
  args.set("realm", @moondb.Value::Text("north"))
  assert_eq(lite.bind(args).length(), 2)
}

#Nested transactions

test "a nested body that fails keeps the outer work" {
  let db = @moondb.MockDriver::new()
  let tx = @moondb.Txn::new(db)
  tx.run(fn(tx) raise @moondb.DbError {
    tx.driver().execute("INSERT INTO hero VALUES (?, ?)", [
      @moondb.Value::Int(1), @moondb.Value::Text("Nova"),
    ])
    |> ignore
    (try {
      tx.run(fn(_tx) raise @moondb.DbError {
        raise @moondb.QueryError("inner failed")
      })
    } catch {
      _ => ()
    })
    |> ignore
  })
  assert_eq(db.row_count(), 1)
}

#Dialects and capabilities

test "ask the driver, not the caller" {
  let db = @moondb.MockDriver::new()
  assert_eq(db.dialect(), @moondb.Dialect::Sqlite)
  assert_eq(db.dialect().placeholder(1), "?")
  let caps = db.capabilities()
  assert_true(caps.savepoints)
  assert_false(caps.streaming_copy)
}

#The narrow view

test "a repository only needs to run SQL" {
  let db = @moondb.MockDriver::new()
  fn count(db : &@moondb.QueryExecutor) -> Int raise @moondb.DbError {
    db.query("SELECT 1", []).length()
  }
  assert_eq(count(@moondb.Executor::new(db)), 0)
}

#Implementing a driver

pub impl @moondb.Driver for MyConn with fn dialect(_self) { @moondb.Dialect::Sqlite }

pub impl @moondb.Driver for MyConn with fn execute(self, sql, params) {
  // ... bind `params` positionally, run `sql`, then:
  { rows_affected: n, last_insert_id: id }
}
// query / begin / commit / rollback / close likewise.

#Design notes

• Why raise, not Result. Typed accessors and driver calls raise DbError rather than returning Result[_, DbError], so a decode bug or a dropped connection surfaces at the call site instead of being silently swallowed. A caller opts into recovery with try/catch.
• Typed accessors are strict. row.int(i) raises TypeError if the cell is not an integer — including when it is NULL. Read a nullable column as T? with read, or guard it with is_null first. Integer→integer and integer→double conversions are allowed and lossless; int narrows an Int64 and says so.
• DbError is pub(all). A plain pub suberror can be caught from another package but not constructed — which would stop out-of-tree drivers from raising it. pub(all) opens the constructors.
• DbError carries the server's own classification. Besides ConnectError / QueryError / TypeError / Closed, a Server case carries a ServerDetail: the five-character SQLSTATE, the vendor error number, and the server's message, detail and hint. DbError::kind() maps any failure onto a coarse ErrorKind (Integrity, Programming, Operational, Data, Internal, NotSupported, …) and sqlstate() / code() expose the raw fields, so a caller can branch on the class instead of parsing a message.
• There are two driver traits, on purpose. Driver is synchronous, which is right for a backend whose calls block in C (moonsqlite steps a prepared statement and returns). A backend reached over TCP cannot be written that way: MoonBit's only socket stack is async-only, and an async fn cannot be called from a synchronous one. A wire driver forced to conform to Driver can do nothing but raise from every method — and because ping is a defaulted method built on query, it swallows that raise into false, so a Pool with pre_ping judges every one of its connections permanently unhealthy. AsyncDriver is the seam for those backends: the same eight operations, each awaited. It is a peer of Driver, not a replacement — a synchronous backend keeps implementing Driver and never becomes async. Declaring async methods pulls in no async runtime, so moondb stays dependency-free and still compiles on every backend; only a driver that implements the trait, and a caller that runs it in an event loop, need one.

• The pool is synchronous, and its counting is moonpool's. @pool.Pool[D] reuses idle connections under a size ceiling, evicts a connection that fails its pre_ping probe or outlives max_lifetime (age measured by an injected clock, the way database/sql swaps nowFunc in tests), and offers a non-blocking try_acquire. Because moondb's base contract is sync and the pure backends have no threads, an exhausted acquire fails immediately rather than blocking — the acquire_timeout is the budget an async driver layers real waiting on top of. ping is a default Driver method (SELECT 1), so every driver gets a health probe for free. The bookkeeping underneath — how many are live, which are idle, when each was born — is moonpool's Pool, and so is the optional ward in front of the database: a backoff after failed connects (acquire then raises ConnectError until the delay has passed), a token bucket spacing reconnections, a breaker that stops connecting to a failing backend, a shedder. This package adds only opening, closing and probing. It is a separate package because that dependency is the pool's alone: import { "moonbitstack/moondb/pool" } where a pool is wanted, and the interface stays dependency-free where it is not.

• The async pool is where an exhausted acquire actually waits. @apool.AsyncPool[D] is the counterpart for an AsyncDriver, and it exists because the synchronous pool cannot do the one thing a full pool is supposed to do. In @pool.Pool nothing can return a connection while a caller sits inside acquire, so a full pool fails immediately and acquire_timeout is only a number in the message. In an event loop, other tasks keep running while one waits — so AsyncPool puts the wait on a Semaphore with one permit per allowed checkout (max_size + overflow) and parks the caller there. release returns the permit and wakes exactly one waiter; acquire_timeout becomes a real deadline. The bookkeeping is still moonpool's, and overflow, pre_ping, max_lifetime and the reset hook mean what they mean in the sync pool — a returned connection is reset before it is reusable, and a connection that came from the overflow allowance is closed rather than idled so the pool settles back to max_size. It lives in its own package because it is the one part of moondb that imports moonbitlang/async: no synchronous driver reaches it, and the interface package itself still imports nothing. (The module declares the runtime so that package can resolve; a consumer that only wants the interface pays for a download, not for an async dependency in its own build.)

• The PostgreSQL protocol state machine is testable without a machine. PgConn speaks over a PgWire, which is either a socket (plain or TLS-wrapped) or one end of an in-memory pipe pair. That seam is what lets the parts that used to be unreachable — statement sequencing, the streaming guard that refuses a second statement while a PgRowStream is open, draining the rest of a result after an ErrorResponse, the COPY sub-protocol, notifications, the statement cache — run against a scripted peer in a unit test, byte for byte, with no server and no network. The live integration suites still exist and still run against real TLS-enabled servers; the offline tests are what catch a regression on a laptop with no database.

#What each driver supports

#Tests

moon test --target all

#License