Guide

From a MoonBit struct to a table.

moonorm maps MoonBit types onto SQL. This guide runs end to end, and every snippet on it was compiled against this repository — copy them into a project that depends on moonbitstack/moonorm and moonbitstack/moondb.

1 · Install

Add the toolkit and its driver contract:

moon add moonbitstack/moonorm
moon add moonbitstack/moondb

moonorm builds the query and schema layer; moondb is the contract a backend (SQLite, PostgreSQL, MySQL) implements. Values cross that boundary as Value, never as SQL text.

2 · Declare a model

A model is a MoonBit struct, the columns that store it, and the two directions of the mapping. Declare the columns once and keep them — the migration section reuses them.

struct Account {
  id : Int64?
  email : String
  balance : Double
  team_id : Int64
  status : String
  level : Int
}

fn account_columns() -> Array[@moonorm.Column] {
  [
    @moonorm.column("id", BigIntType, primary_key=true, autoincrement=true),
    @moonorm.column("email", TextType, nullable=false, unique=true),
    @moonorm.column("balance", RealType),
    @moonorm.column("team_id", BigIntType, references=("teams", "id"), on_delete="SET NULL"),
    @moonorm.column("status", TextType, nullable=false, default="'active'"),
    @moonorm.column("level", IntType, checks=["level >= 0"]),
  ]
}

fn account() -> @moonorm.Model[Account] {
  @moonorm.Model::new(
    "accounts",
    account_columns(),
    (r) => {
      {
        id: r.read(0),
        email: r.read(1),
        balance: r.read(2),
        team_id: r.read(3),
        status: r.read(4),
        level: r.read(5),
      }
    },
    (a) => {
      [
        (
          "id",
          match a.id {
            Some(v) => @moondb.Int64(v)
            None => @moondb.Null
          },
        ),
        ("email", @moondb.Text(a.email)),
        ("balance", @moondb.Double(a.balance)),
        ("team_id", @moondb.Int64(a.team_id)),
        ("status", @moondb.Text(a.status)),
        ("level", @moondb.Int(a.level)),
      ]
    },
  )
}

id is Int64? because the database assigns it: None binds as NULL, and the server fills the key.

3 · Generate DDL

One model, three dialects. Each of these is a single statement, printed verbatim:

fn print_ddl() -> Unit {
  println(account().create_table_sql_for(Sqlite))
  println(account().create_table_sql_for(Postgres))
  println(account().create_table_sql_for(Mysql))
}
// CREATE TABLE accounts (id INTEGER PRIMARY KEY AUTOINCREMENT, email TEXT NOT NULL UNIQUE, balance REAL, team_id INTEGER REFERENCES teams(id) ON DELETE SET NULL, status TEXT NOT NULL DEFAULT 'active', level INTEGER CHECK (level >= 0))
// CREATE TABLE accounts (id BIGSERIAL PRIMARY KEY, email TEXT NOT NULL UNIQUE, balance DOUBLE PRECISION, team_id BIGINT REFERENCES teams(id) ON DELETE SET NULL, status TEXT NOT NULL DEFAULT 'active', level INTEGER CHECK (level >= 0))
// CREATE TABLE accounts (id BIGINT AUTO_INCREMENT PRIMARY KEY, email TEXT NOT NULL UNIQUE, balance DOUBLE, team_id BIGINT REFERENCES teams(id) ON DELETE SET NULL, status TEXT NOT NULL DEFAULT 'active', level INT CHECK (level >= 0)) ENGINE=InnoDB

The differences are the ones that matter: INTEGER PRIMARY KEY AUTOINCREMENT, BIGSERIAL, BIGINT AUTO_INCREMENT, and MySQL's ENGINE=InnoDB suffix. Pass if_not_exists=true to prefix CREATE TABLE IF NOT EXISTS on every dialect.

4 · Build a query

A query is a MoonBit expression that renders to dialect SQL. Every value in the predicate is bound; the statement keeps a placeholder.

fn build_query() -> (String, Array[@moondb.Value]) {
  account()
    .select()
    .where_("email", "=", @moondb.Text("ada@x.io"))
    .order_by("level", Asc)
    .limit(10)
    .build()
}
// SELECT id, email, balance, team_id, status, level FROM accounts
//   WHERE email = ? ORDER BY level ASC LIMIT 10
// params: [Text("ada@x.io")]

The value travels in params as a Value, so no quoting is possible. limit takes an integer, not a value, and is written into the statement as a literal.

5 · Insert a row

fn build_insert() -> (String, Array[@moondb.Value]) {
  let row : Account = {
    id: None,
    email: "ada@x.io",
    balance: 0.0,
    team_id: 2,
    status: "active",
    level: 1,
  }
  account().insert_of(row).build_for(Postgres)
}
// INSERT INTO accounts (id, email, balance, team_id, status, level)
//   VALUES (?, ?, ?, ?, ?, ?)
// params: [Null, Text("ada@x.io"), Double(0), Int64(2), Text("active"), Int(1)]

The key is bound as NULL rather than omitted, so one statement serves both an assigned key and a generated one.

6 · Migrate

Read what the database actually has, diff it against the columns you declared, and let each difference become a step. Reflected-only columns are Removed; declared-only ones are Added.

pub fn plan_for(db : &@moondb.Driver) -> Array[@moonorm.ColumnDiff] raise @moondb.DbError {
  let reflected = @moonorm.reflect_table(db, "accounts")
  @moonorm.diff_schema(account_columns(), reflected, dialect=Postgres)
}

Against a table that holds only id and email, the plan is four additions:

fn report(diffs : Array[@moonorm.ColumnDiff]) -> Unit {
  for d in diffs {
    match d {
      Added(name~, col_type~) =>
        println("+ " + name + " " + col_type.sql_for(Postgres))
      Removed(name) => println("- " + name)
      TypeChanged(name~, from~, to~) =>
        println("~ " + name + " " + from.sql_for(Postgres) + " -> " + to.sql_for(Postgres))
    }
  }
}
// + balance DOUBLE PRECISION
// + team_id BIGINT
// + status TEXT
// + level INTEGER

Migrator then applies a list of Migration records — each a version, a name, and the up/down statements — and records what it ran.

Dialects

The Dialect enum fixes the spelling of types, keys and placeholders. Choose it once per session; the seam renders the rest.