#moonorm

Imports. Bound-value constructors (Int, Text, Null, …) are moondb's: the Value type is re-exported by moonorm, but you construct values as @moondb.Int / @moondb.Text (add moon add moonbitstack/moondb). A model field needs none of them: field binds the attribute itself, and Row::read reads a cell as the field's type. The examples that touch a real database import the SQLite driver under a short alias, "moonbitstack/moonsqlite" @sqlite in moon.pkg.

#Packages

#Quickstart

pub fn quickstart() -> Unit {
  let (sql, params) = @moonorm.select("users")
    .column("id")
    .column("name")
    .eq("age", @moondb.Int(18))
    .where_("name", "LIKE", @moondb.Text("bob%"))
    .order_by("name", Asc)
    .limit(10)
    .build()
  // SELECT id, name FROM users WHERE age = ? AND name LIKE ? ORDER BY name ASC LIMIT 10
  // params: [Int(18), Text("bob%")]
  ignore((sql, params))
}

pub fn joins() -> Unit {
  let orders : @moonorm.Table = { name: "orders", columns: [] }
  let (sql, params) = orders
    .select()
    .raw("users.name")
    .count()
    .join("users", "users.id = orders.user_id")
    .eq("orders.status", @moondb.Text("paid"))
    .group_by("users.name")
    .having("COUNT(*)", ">", @moondb.Int(3))
    .build()
  // SELECT users.name, COUNT(*) FROM orders JOIN users ON users.id = orders.user_id
  //   WHERE orders.status = ? GROUP BY users.name HAVING COUNT(*) > ?
  // params: [Text("paid"), Int(3)]
  ignore((sql, params))
}

#Running against a real database

// native target only — moonsqlite links the vendored amalgamation.
pub fn demo() -> Unit raise @moondb.DbError {
  let sess = @moonorm.Session::new(@sqlite.SqliteDriver::open(":memory:"))
  sess.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)", [])
  |> ignore

  // INSERT through the builder — values are bound, never spliced.
  sess.add(
    @moonorm.insert("users").set("name", @moondb.Text("alice")).set("age", @moondb.Int(30)),
  )
  |> ignore

  // SELECT through the builder — real rows come back, typed.
  let rows = sess.fetch(
    @moonorm.select("users").column("name").column("age").where_("age", ">", @moondb.Int(18)),
  )
  let _ = rows[0].text(0) // "alice"  (raises TypeError if the column is not Text)
  let _ = rows[0].int(1) // 30
  sess.close()
}

#Models & relationships

pub struct Team {
  id : Int
  name : String
}

pub struct Hero {
  id : Int
  name : String
  team_id : Int
}

pub fn models() -> (Array[@moonorm.Model[Team]], Array[@moonorm.Model[Hero]]) {
  let teams : @moonorm.Model[Team] = @moonorm.Model::new(
    "teams",
    [
      @moonorm.column("id", IntType, primary_key=true),
      @moonorm.column("name", TextType, nullable=false),
    ],
    (r) => { { id: r.int(0), name: r.text(1) } },
    (t) => [("id", @moondb.Int(t.id)), ("name", @moondb.Text(t.name))],
  )
  let heroes : @moonorm.Model[Hero] = @moonorm.Model::new(
    "heroes",
    [
      @moonorm.column("id", IntType, primary_key=true),
      @moonorm.column("name", TextType, nullable=false),
      @moonorm.column("team_id", IntType, references=("teams", "id")),
    ],
    (r) => { { id: r.int(0), name: r.text(1), team_id: r.int(2) } },
    (h) => [
      ("id", @moondb.Int(h.id)),
      ("name", @moondb.Text(h.name)),
      ("team_id", @moondb.Int(h.team_id)),
    ],
  )
  ([teams], [heroes])
}

pub struct Team {
  id : Int
  name : String
}

pub struct Hero {
  id : Int
  name : String
  team_id : Int
}

pub fn mapping(sess : @moonorm.Session, teams : @moonorm.Model[Team], heroes : @moonorm.Model[Hero]) -> Unit raise @moondb.DbError {
  sess.create_table(teams) |> ignore // CREATE TABLE teams (id INTEGER PRIMARY KEY, name TEXT NOT NULL)
  sess.create_table(heroes) |> ignore
  sess.insert_record(teams, { id: 1, name: "avengers" }) |> ignore
  sess.insert_record(heroes, { id: 10, name: "iron-man", team_id: 1 }) |> ignore

  // 1:N — a team's heroes, eagerly loaded as mapped records.
  let children = @moonorm.has_many(heroes, "team_id", (t : Team) => @moondb.Int(t.id))
  let kids = sess.load({ id: 1, name: "avengers" }, children)

  // N:1 — a hero's team.
  let parent = @moonorm.belongs_to(teams, "id", (h : Hero) => @moondb.Int(h.team_id))
  let team = sess.load_one({ id: 10, name: "iron-man", team_id: 1 }, parent)
  ignore((kids, team))
}

#Declarative models from a field list

pub struct Hero {
  id : Int
  name : String
  team_id : Int
}

pub fn declarative() -> @moonorm.Model[Hero] {
  @moonorm.Model::from_fields(
    "heroes",
    [
      @moonorm.field("id", IntType, h => h.id, primary_key=true),
      @moonorm.field("name", TextType, h => h.name, nullable=false),
      @moonorm.field("team_id", IntType, h => h.team_id, references=("teams", "id")),
    ],
    (r) => { { id: r.read(0), name: r.read(1), team_id: r.read(2) } },
  )
}

#Annotations

#orm.table(name="heroes")
pub struct Hero {
  #orm.column(primary_key=true, autoincrement=true)
  id : Int64?
  name : String
  #orm.column(name="secret_name", unique=true)
  secret : String
  #orm.column(check="age >= 0")
  age : Int?
  #orm.column(references="teams.id", on_delete="SET NULL")
  team_id : Int64?
  #orm.skip
  visits : Int
}

let hero = Hero::model()   // @moonorm.Model[Hero]

rule(name: "orm", command: "moonx moonbitstack/moonorm/cmd/orm@0.11.3 -C \"$mod_dir\" \"$input\" -o \"$output\"")

dev_build(rule: "orm", input: "models.mbt", output: "models.g.mbt")
formatter(ignore: [ "models.g.mbt" ])

#Batch loading

pub struct Team {
  id : Int
  name : String
}

pub struct Hero {
  id : Int
  name : String
  team_id : Int
}

pub fn batch(
  sess : @moonorm.Session,
  heroes : @moonorm.Model[Hero],
  teams : Array[Team],
) -> Array[Array[Hero]] raise @moondb.DbError {
  let children = @moonorm.has_many(heroes, "team_id", (t : Team) => @moondb.Int(t.id))
  sess.load_batch(teams, children) // one query; result[0] is the first team's heroes
}

#Nested transactions & isolation

pub fn tx(sess : @moonorm.Session, stmt : @query.Insert, dialect : @query.Dialect) -> Unit raise @moondb.DbError {
  let sp = sess.begin_nested() // SAVEPOINT moonorm_sp_1; sess.savepoint_depth() == 1
  sess.add(stmt) |> ignore
  sp.rollback() // undo just this savepoint's work; depth back to 0

  sess.begin_with({ isolation: Some(Serializable), read_only: false }, dialect~)
  // BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
}

#Design and boundaries

• Explicit, not magic. Every statement is issued explicitly through a Session. MoonBit has no attribute interception, so a mapped attribute cannot quietly fire SQL; reading a relationship is a call you can see.
• Pure, zero C. The library is written entirely against the @moondb seam, so it compiles on every backend (wasm / wasm-gc / js / native) and drags no backend behind it. The C lives where a driver needs it — moonsqlite — never here.
• Errors raise. A decode mismatch or a backend rejection is a @moondb.DbError at the call site, not a zero value.

#Subqueries, CTEs & optimistic locking

pub fn cte() -> (String, Array[@moondb.Value]) {
  let big = @moonorm.select("orders").column("user_id").where_("total", ">", @moondb.Int(100))
  @moonorm.select("users")
  .column("id")
  .column("name")
  .with_cte("big_spenders", big)
  .where_("active", "=", @moondb.Bool(true))
  .where_in("id", @moonorm.select("big_spenders").column("user_id"))
  .build()
  // WITH big_spenders AS (SELECT user_id FROM orders WHERE total > ?)
  //   SELECT id, name FROM users WHERE active = ? AND id IN (SELECT user_id FROM big_spenders)
  // params: [Int(100), Bool(true)]   <- the CTE value first, then the WHERE value
}

pub fn optimistic(sess : @moonorm.Session, current : Int) -> Unit raise {
  let stmt = @moonorm.update("account")
    .set("balance", @moondb.Int(50))
    .set("version", @moondb.Int(current + 1))
    .where_("id", "=", @moondb.Int(1))
    .where_("version", "=", @moondb.Int(current)) // the guard
  sess.modify_versioned(stmt, what="account") |> ignore // raises LostUpdate on a stale version
}

#Migrations

pub fn migrations(sess : @moonorm.Session) -> Unit raise @moondb.DbError {
  let plan : Array[@moonorm.Migration] = [
    {
      version: 1,
      name: "create_users",
      up: ["CREATE TABLE users (id INTEGER PRIMARY KEY)"],
      down: ["DROP TABLE users"],
    },
    {
      version: 2,
      name: "add_email",
      up: ["ALTER TABLE users ADD COLUMN email TEXT"],
      down: ["ALTER TABLE users DROP COLUMN email"],
    },
  ]
  let m = @moonorm.Migrator::new()
  m.up(sess, plan) |> ignore // applies 1 then 2; returns how many ran
  let _ = m.current_version(sess) // 2
  m.down_to(sess, plan, 1) |> ignore // rolls back 2, leaving 1
}

pub fn plan_for(db : &@moondb.Driver, declared : Array[@moonorm.Column]) -> Array[@moonorm.ColumnDiff] raise @moondb.DbError {
  let reflected = @moonorm.reflect_table(db, "accounts")
  @moonorm.diff_schema(declared, reflected, dialect=Postgres)
  // Added(name~, col_type~) for a column only the model declares,
  // Removed(name) for one only the database has,
  // TypeChanged(name~, from~, to~) when the two disagree on the type.
}

#Injection safety

pub fn injection() -> (String, Array[@moondb.Value]) {
  let evil = "'; DROP TABLE users; --"
  @moonorm.select("users").eq("name", @moondb.Text(evil)).build()
  // SELECT * FROM users WHERE name = ?   <- the attack string is not in the SQL
  // params: [Text("'; DROP TABLE users; --")]   <- it is a bound parameter
}

#License