#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

#orm.table(name="heroes")
pub struct Hero {
  #orm.column(primary_key=true, autoincrement=true)
  id : Int64?
  name : String
  #orm.column(index=true)
  power : Double
}

#sql.select("SELECT id, name, power FROM heroes WHERE power > #{power}")
declare fn strong(db : &@moondb.Driver, power : Double) -> Array[Hero] raise

#The query builder

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()
}

#A connection string picks the driver and dialect

pub fn from_url(driver : &@moondb.Driver) -> @moonorm.Session raise @moonorm.DsnError {
  let engine = @moonorm.Engine::new("postgresql://user:pass@db:5432/app?sslmode=require")
  // engine.driver() == "postgresql"; engine.dialect() == Postgres
  engine.session(driver) // a Session that renders for Postgres
}

#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) } },
  )
}

pub fn handle_column() -> @moonorm.ColumnType {
  @moonorm.user_type(sqlite="TEXT", postgres="CITEXT", mysql="VARCHAR(255)")
}

#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.7 -C \"$mod_dir\" \"$input\" -o \"$output\"")

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

#Without annotations

pub struct Hero {
  id : Int64?
  name : String
  power : Double
}

pub fn hero_model() -> @moonorm.Model[Hero] {
  @moonorm.Model::from_fields(
    "heroes",
    [
      @moonorm.field("id", BigIntType, h => h.id, primary_key=true, autoincrement=true),
      @moonorm.field("name", TextType, h => h.name, nullable=false),
      @moonorm.field("power", RealType, h => h.power),
    ],
    (r) => { { id: r.read(0), name: r.read(1), power: r.read(2) } },
  )
}

pub struct Hero {
  id : Int64?
  name : String
  power : Double
}

pub fn hero_model() -> @moonorm.Model[Hero] {
  @moonorm.Model::new(
    "heroes",
    [
      @moonorm.column("id", BigIntType, primary_key=true, autoincrement=true),
      @moonorm.column("name", TextType, nullable=false),
      @moonorm.column("power", RealType),
    ],
    (r) => { { id: r.read(0), name: r.read(1), power: r.read(2) } },
    (h) => {
      let out : Array[(String, @moondb.Value)] = []
      if h.id is Some(v) {
        out.push(("id", @moondb.Int64(v)))
      }
      out.push(("name", @moondb.Text(h.name)))
      out.push(("power", @moondb.Double(h.power)))
      out
    },
  )
}

#SQL mappers

pub struct User {
  id : Int64
  name : String
  age : Int32?
}

#sql.select("SELECT id, name, age FROM user WHERE age > #{age}")
declare fn adults(db : &@moondb.Driver, age : Int32) -> Array[User] raise

#sql.select("SELECT id, name, age FROM user WHERE id = #{id}")
declare fn find(db : &@moondb.Driver, id : Int64) -> User? raise

#sql.insert("INSERT INTO user(name, age) VALUES(#{u.name}, #{u.age})")
declare fn add(db : &@moondb.Driver, u : User) -> Int64 raise

#sql.delete("DELETE FROM user WHERE id = #{id}")
declare fn remove(db : &@moondb.Driver, id : Int64) -> Unit raise

rule(name: "sql", command: "moonx moonbitstack/moonorm/cmd/sql@0.11.7 -C \"$mod_dir\" \"$input\" -o \"$output\"")
dev_build(rule: "sql", input: "models.mbt", output: "mapper.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
}

sess.load_batch(teams, children, strategy=JoinedLoad)

#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
}

#Event hooks

pub fn stamp_audit(sess : @moonorm.Session) -> Unit {
  sess.on(BeforeInsert, ctx => {
    // The row is mutable here: what the hook leaves behind is what gets written.
    ctx.set_column("updated_by", @moondb.Text("nightly-job"))
  })
  sess.on(BeforeInsert, ctx => {
    if ctx.table != "audit_log" {
      return
    }
    ctx.set_column("at", @moondb.Text("now"))
  })
  sess.on(AfterInsert, ctx => println(ctx.sql))
}

#Hybrid attributes

pub fn full_name() -> @moonorm.Hybrid[Person] {
  @moonorm.hybrid(
    "full_name",
    "{t}.first || ' ' || {t}.last",
    p => @moondb.Text(p.first + " " + p.last),
  )
}

pub fn adults(people : @moonorm.Model[Person]) -> @moonorm.Select raise @moondb.DbError {
  people.select().where_hybrid(full_name(), "=", @moondb.Text("Ada Lovelace"))
  // SELECT id, first, last FROM people WHERE people.first || ' ' || people.last = ?
}

#Inheritance mapping

pub fn engineer_model() -> @moonorm.Model[Engineer] {
  @moonorm.Model::from_fields(
    "employee",
    [
      @moonorm.field("id", IntType, e => e.id, primary_key=true),
      @moonorm.field("name", TextType, e => e.name, nullable=false),
      @moonorm.field("engineer_name", TextType, e => e.engineer_name),
    ],
    engineer_row,
  )
  .discriminate(column="kind", identity="engineer")
  // SELECT id, name, engineer_name, kind FROM employee WHERE kind = ?
}

pub fn joined_engineer_model() -> @moonorm.Model[JoinedEngineer] {
  @moonorm.Model::from_fields("engineer", engineer_fields, joined_engineer_row)
  .joined(parent_table="person", parent_columns=["id", "name"])
  // SELECT person.id, person.name, engineer.engineer_name
  //   FROM engineer JOIN person ON person.id = engineer.id
}

pub fn everyone(engineers : @moonorm.Select, managers : @moonorm.Select) -> @moonorm.Select {
  engineers.union_all(managers)
}

#Migrations

#Versioned steps

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
}

#Revisions

pub fn revisions() -> Array[@moonorm.Revision] {
  [
    @moonorm.Revision::new(
      id="0001",
      name="create_heroes",
      up=[
        @moonorm.create_table("heroes", [
          @moonorm.column("id", IntType, primary_key=true, autoincrement=true),
          @moonorm.column("name", TextType, nullable=false),
        ]),
        @moonorm.create_index("ix_heroes_name", "heroes", ["name"]),
      ],
      down=[
        @moonorm.drop_index("ix_heroes_name"),
        @moonorm.drop_table("heroes"),
      ],
    ),
    @moonorm.Revision::new(
      id="0002",
      parents=["0001"],
      name="create_teams",
      up=[@moonorm.create_table("teams", teams_columns)],
      down=[@moonorm.drop_table("teams")],
    ),
  ]
}

pub fn apply(sess : @moonorm.Session, plan : Array[@moonorm.Revision]) -> Unit raise @moondb.DbError {
  let m = @moonorm.Migrator::new()
  m.upgrade(sess, plan) |> ignore        // to the single head; "heads" for every tip
  m.downgrade(sess, plan, target="0001") |> ignore
  m.stamp(sess, plan, target="0002") |> ignore  // record it without running it
  let _ = m.heads(sess, plan)            // where the database sits
  let _ = m.history(sess, plan)          // newest first, each marked applied
}

pub fn plan_sql(plan : Array[@moonorm.Revision]) -> Array[@moonorm.Step] raise @moondb.DbError {
  @moonorm.Migrator::new().upgrade_sql(plan, [], target="heads", dialect=Postgres)
}

#Autogenerate

pub fn drift(declared : Array[@moonorm.TableSpec], live : Array[@moonorm.TableSpec]) -> Array[@moonorm.Change] {
  @moonorm.autogenerate_changes(declared, live, dialect=Postgres)
  // up:   ALTER TABLE heroes ADD COLUMN power INTEGER
  // down: ALTER TABLE heroes DROP COLUMN power
}

pub fn next_revision(declared : Array[@moonorm.TableSpec], live : Array[@moonorm.TableSpec]) -> @moonorm.Revision {
  @moonorm.autogenerate_revision(id="0005", parents=["0004"], name="hero powers", declared, live)
}

#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