PostgreSQL with std.orm

std.orm maps ordinary moon structs to PostgreSQL tables. Models describe the mapping explicitly, queries remain parameterized, and every database operation returns an asynchronous Result.

Use std.sql directly for dynamic result shapes or database-specific queries. Use std.orm when application code benefits from reusable typed models, predicates, relations, and migrations.

Define a model

use std.orm

struct User {
  id: Optional<int>,
  email: string,
  active: bool,
  version: int,
  deleted_at: Optional<DateTime>,
}

fn user_model() -> Model<User> {
  orm.model<User>("app.users", [
    orm.column("id").primary_key().generated(),
    orm.column("email").unique().indexed(),
    orm.column("active").default_sql("TRUE"),
    orm.column("version").version(),
    orm.column("deleted_at").soft_delete(),
  ])
}

Column descriptors can mark primary keys, generated or read-only fields, defaults, indexes, optimistic-lock versions, and soft deletes. Use column_as("field", "column") when the database and moon names differ.

Built-in mappings cover booleans, integer widths, floats, strings, byte arrays, DateTime, JSON, decimals, UUIDs, enums stored as text, and optional forms.

Query models

Build predicates from typed fields:

let users = user_model()
let query = orm.query(pool, users)
  .where(users.field("active").eq(true))
  .where(users.field("email").ilike("%@example.com"))
  .order_by(users.field("id"), "desc")
  .limit(50)

let page: [User] = (await query.all())?
let first: Optional<User> = (await query.first())?

Available predicates include comparisons, ranges, IN lists, LIKE and ILIKE, null tests, and boolean combinations. Queries support ordering, distinct, limit and offset, row locks, and soft-delete scopes.

Use to_query() when another API expects a SqlQuery. to_sql() is useful for inspection and logging; it does not include parameter values.

Stream large queries

all collects rows up to the configured materialization limit. Use stream when the result can be large:

let rows = (await orm.query(pool, users)
  .order_by(users.field("id"), "asc")
  .stream())?

while let some(user) = (await rows.next())? {
  println(user.email)
}
rows.close()

Close the stream when stopping early so its database connection can return to the pool.

Insert, update, and delete

Insert functions return mapped values, including generated fields:

let created = (await orm.insert(pool, users, User {
  id: none,
  email: "ada@example.com",
  active: true,
  version: 1,
  deleted_at: none,
}))?

Update selected rows with a typed change set:

let changes = orm.changes(users)
  .set(users.field("email"), "new@example.com")
  .set(users.field("active"), true)

let changed = (await orm.query(pool, users)
  .where(users.field("id").eq(user_id))
  .update(changes))?

A version field adds optimistic locking. A conflicting update returns StaleObject. With a soft-delete field, ordinary delete records a deletion time; use the explicit query scopes when deleted rows should be included.

Transactions

Use a std.sql transaction when application code needs several operations to succeed together. Model and query functions accept a transaction wherever they accept a connection or pool.

For a single callback, orm.transaction handles commit and rollback:

let result = await orm.transaction<User>(pool, |transaction| {
  create_user(transaction, input)
})

It commits an ok result and rolls back an err result. Use explicit SQL savepoints when part of a larger transaction may be retried or undone.

Relations and preloading

Relations describe how two models connect; reading a field never performs a hidden query:

let user_posts = orm.has_many(
  users,
  posts,
  users.field("id"),
  posts.field("user_id"),
)

let posts_for_user = (await user_posts.load(pool, user))?
let posts_by_user = (await user_posts.preload(pool, page_of_users))?

Use preload for a page of source records. It performs one batched query and groups the results, avoiding one query per record. has_one and belongs_to cover the other common relation shapes.

Migrations

Migrations are explicit ordered values kept with the application source:

let migrations = [
  orm.migration(
    "20260901_001_users",
    [orm.create_table(user_model())],
    [sql.raw("DROP TABLE app.users", [])],
  ),
]

let status = (await orm.migrate(pool, migrations))?

Applied migrations are tracked by ID and checksum. moon refuses changed or missing migration history rather than silently rewriting it.

Use migration_status before a deployment and migration_dry_run to review the SQL. Keep down migrations accurate, and mark a migration non_transactional() only for statements PostgreSQL does not permit inside a transaction.

Browse complete signatures in the std.orm API reference. See PostgreSQL with std.sql for connections, pools, transactions, and lower-level queries.