#moonorm

Moved on mooncakes from Lfan-ke/moonorm to moonbitstack/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). The examples below import the SQLite driver under a short alias, "moonbitstack/moonsqlite" @sqlite in moon.pkg.

#Packages

#Quickstart

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()
// sql    = "SELECT id, name FROM users WHERE age = ? AND name LIKE ? ORDER BY name ASC LIMIT 10"
// params = [Int(18), Text("bob%")]

let (isql, ivals) = @moonorm.insert("users")
  .set("name", @moondb.Text("bob")).set("age", @moondb.Int(30)).build()
// "INSERT INTO users (name, age) VALUES (?, ?)"

let (usql, uvals) = @moonorm.update("users").set("age", @moondb.Int(31)).where_("id", "=", @moondb.Int(1)).build()
let (dsql, dvals) = @moonorm.delete("users").where_("id", "=", @moondb.Int(9)).build()

// JOIN + GROUP BY + HAVING, with aggregates and a Table descriptor:
let orders : @moonorm.Table = { name: "orders", columns: [] }
let (jsql, jparams) = 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)]

#Running against a real database

// native target only — moonsqlite links the vendored amalgamation.
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 isn't Text)
  let _ = rows[0].int(1)   // 30
  sess.close()
}

#Models & relationships

// A parent model and a child model with a foreign key back to it.
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))],
)

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, eager-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)   // [Hero{...}, ...]

// 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)  // Some(Team{...})

#Declarative models from a field list

let heroes : @moonorm.Model[Hero] = @moonorm.Model::from_fields(
  "heroes",
  [
    @moonorm.field("id", IntType, (h : Hero) => @moondb.Int(h.id), primary_key=true),
    @moonorm.field("name", TextType, (h : Hero) => @moondb.Text(h.name), nullable=false),
    @moonorm.field("team_id", IntType, (h : Hero) => @moondb.Int(h.team_id),
                   references=("teams", "id")),
  ],
  (r) => { id: r.int(0), name: r.text(1), team_id: r.int(2) },   // the one closure that stays
)

#Batch loading (avoiding N+1)

let children = @moonorm.has_many(heroes, "team_id", (t : Team) => @moondb.Int(t.id))
let teams : Array[Team] = [{ id: 1, name: "avengers" }, { id: 2, name: "x-men" }]
let grouped = sess.load_batch(teams, children)   // one query; grouped[0] = avengers' heroes, …

#Nested transactions & isolation

let sp = sess.begin_nested()          // SAVEPOINT moonorm_sp_1; sess.savepoint_depth() == 1
sess.add(insert_stmt) |> ignore
sp.rollback()                         // undo just this savepoint's work; depth back to 0

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

#Design & boundaries (honest)

• Explicit, not magic. SQLAlchemy issues SQL implicitly when you touch a mapped attribute; MoonBit has no attribute interception, so every statement is issued explicitly via Session — the same faithful trade Diesel and GORM make.
• Pure, zero C. moonorm is written entirely against the @moondb seam, so the whole library compiles on every backend (wasm / wasm-gc / js / native) and drags no backend behind it. The C lives in one isolated place — moonsqlite — not here. A Postgres wire-protocol backend and a JS node:sqlite backend are next.
• Tested against a real driver. The Session/Model/relationship layer is covered on every backend against @moondb.MockDriver; real SQL execution is proven end to end in moonsqlite's native integration tests, which open an actual SQLite database and are mutation-verified (neutering the C bind path turns them red).

#Subqueries, CTEs & optimistic locking

let big = @moonorm.select("orders").column("user_id").where_("total", ">", @moondb.Int(100))
let (sql, params) = @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)]   ← CTE value first, then the WHERE value

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

let migrations : 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, migrations) |> ignore          // applies 1 then 2; returns how many ran
let _ = m.current_version(sess)            // 2
m.down_to(sess, migrations, 1) |> ignore   // rolls back 2, leaving 1

#Injection safety

let evil = "'; DROP TABLE users; --"
let (sql, params) = @moonorm.select("users").eq("name", @moondb.Text(evil)).build()
// sql    = "SELECT * FROM users WHERE name = ?"   ← the attack string is NOT in the SQL
// params = [Text("'; DROP TABLE users; --")]      ← it's a bound parameter

#Roadmap (transliterating SQLAlchemy)

#License