#Moon-Typed-ORM

mooncakes.io

///|
let age : Column[Int] = Column::new("users", "age")

///|
let stmt = Select::from("users")
  .where_(age.gte(18))
  .order_by(age.desc())
  .limit(10)
  .build()
// stmt.to_sql()    == SELECT * FROM "users" WHERE "users"."age" >= ? ORDER BY "users"."age" DESC LIMIT 10
// stmt.to_params() == [Value::int(18)]

#What's here

• ast.mbt — the DSL: Column, Condition, Order, Assignment.
• query.mbt — Select / Insert / Update / Delete builders and Statement, plus aggregates (COUNT / SUM / AVG / MIN / MAX), GROUP BY / HAVING, INNER / LEFT / RIGHT / FULL JOIN, and subqueries (IN / EXISTS, scalar comparison, and correlated).
• conn.mbt — Row, ExecResult, and the Connection / Transactional traits.
• memory.mbt — an in-memory Connection implementation (no FFI, no deps).
• error.mbt — OrmError.
• sqlite/ — a native SQLite Connection over a vendored SQLite amalgamation.
• cmd/main / cmd/sqlite_demo — runnable demos of the same builders against the in-memory backend and against SQLite.

#Running

moon test                    # 48 tests on wasm-gc: SQL rendering, errors, CRUD, joins, subqueries
moon test --target native    # + 3 SQLite tests (CRUD, null/float, transactions)
moon run cmd/main            # in-memory demo: build -> execute -> rows
moon run cmd/sqlite_demo --target native  # the same demo against SQLite

#The DSL

///|
let id : Column[Int] = Column::new("users", "id")

///|
let email : Column[String] = Column::new("users", "email")

// where id = 1 AND (email LIKE '%@example.com' OR email IS NULL)

///|
let cond = id.eq(1).and_(email.like("%@example.com").or_(email.is_null()))

///|
let stmt = Select::from("orders")
  .group_by(Column::new("orders", "status"))
  .count()
  .build()
// SELECT "orders"."status", COUNT(*) FROM "orders" GROUP BY "orders"."status"

///|
let user_id : Column[Int] = Column::new("orders", "user_id")

///|
let stmt = Select::from("orders")
  .left_join("users", user_id.eq(Column::new("users", "id")))
  .build()
// SELECT * FROM "orders" LEFT JOIN "users" ON "orders"."user_id" = "users"."id"
// `.join` is the inner join; `.left_join`, `.right_join`, and `.full_join`
// are the outer joins.

///|
let user_id : Column[Int] = Column::new("orders", "user_id")

///|
let stmt = Select::from("users")
  .where_(
    Column::new("users", "id").in_select(
      Select::from("orders").columns([user_id.to_ref()]),
    ),
  )
  .build()
// SELECT * FROM "users" WHERE "users"."id" IN (SELECT "orders"."user_id" FROM "orders")
// `Condition::exists(sub)` gives EXISTS; wrap in `.not_()` for NOT EXISTS.

///|
let uid : Column[Int] = Column::new("users", "id")

///|
let ouid : Column[Int] = Column::new("orders", "user_id")

///|
let stmt = Select::from("users")
  .where_(Condition::exists(Select::from("orders").where_(ouid.eq_col(uid))))
  .build()
// SELECT * FROM "users" WHERE EXISTS (SELECT * FROM "orders" WHERE "orders"."user_id" = "users"."id")

///|
let age : Column[Int] = Column::new("users", "age")
// users whose age is above the average:

///|
let stmt2 = Select::from("users")
  .where_(age.gt_sub(Select::from("users").avg(age)))
  .build()
// SELECT * FROM "users" WHERE "users"."age" > (SELECT AVG("users"."age") FROM "users")

#The Connection boundary

///|
pub(open) trait Connection {
  fn select(Self, Select) -> Array[Row] raise OrmError
  fn insert(Self, Insert) -> ExecResult raise OrmError
  fn update(Self, Update) -> ExecResult raise OrmError
  fn delete(Self, Delete) -> ExecResult raise OrmError
}

let db = Memory::new()
db.create_table("users", ["id", "name", "age"])

db.insert(Insert::into("users").set(id.set(1)).set(name.set("alice")).set(age.set(30)))

let rows = db.select(Select::from("users").where_(age.gte(18)))

let db = Sqlite::open(":memory:")
db.exec("CREATE TABLE users (id INTEGER, name TEXT, age INTEGER)")

db.insert(Insert::into("users").set(id.set(1)).set(name.set("alice")).set(age.set(30)))

let rows = db.select(Select::from("users").where_(age.gte(18)))

///|
pub(open) trait Transactional {
  fn begin(Self) -> Unit raise OrmError
  fn commit(Self) -> Unit raise OrmError
  fn rollback(Self) -> Unit raise OrmError
}

#Design notes

• Parameterized SQL. Values are never interpolated into the text. The builder emits ? placeholders and a parallel params array.
• Phantom type columns. Column[T]'s type parameter never appears in the SQL; it exists only so Column[Int]::eq takes an Int. This is a compile-time marker, not a runtime value.
• No string SQL in the hot path. Connection takes the builder types, so an in-memory backend doesn't have to parse SQL back.
• Value smart constructors. Enum variants are read-only outside the package (a MoonBit interface rule), so construct results with Value::int(1), Value::text("x"), etc.

#Limits

• One join per SELECT (INNER / LEFT / RIGHT / FULL); the in-memory backend evaluates all four join kinds, padding the missing side with nulls.
• Subqueries support IN, EXISTS, and scalar comparison, and may be correlated — they can reference the outer row. Correlation is single-level: a subquery sees its immediate outer query's columns, not a grand-outer.
• Transactions are opt-in via Transactional: Sqlite implements it, Memory does not.
• Condition::raw works for SQL rendering but is rejected by Memory.
• The SQLite driver is native-only (not available on wasm-gc).

#License