lode

# lode

> **lode** *n.* — a rich vein of ore; an abundant store.

A database toolkit for [Gleam](https://gleam.run) — schemas, changesets, a typed
query builder, a repo, `Multi`, association preloading, and PostgreSQL + SQLite
adapters. Ported from Elixir's [Ecto](https://github.com/elixir-ecto/ecto).

Because Gleam has no macros, no runtime reflection, and no behaviours, this is a
*functional re-expression* of Ecto rather than a literal translation. The design
rationale and a full Ecto-feature mapping live in [`DESIGN.md`](DESIGN.md).

## Packages

Mirroring Elixir's split, lode ships as two sibling packages in two repos:

- **`lode`** (this repo) — the database-agnostic core: types, schema, changeset,
  query builder, repo, `Multi`, associations, the adapter interface, and the
  in-memory adapter. Depends only on `gleam_stdlib`. Compiles on both the
  Erlang and JavaScript targets.
- **`lode_sql`** — the SQL renderer (dialect-parameterized), the PostgreSQL and
  SQLite adapters, migrations, codegen, and the drift checker. Depends on
  `lode` + `pog` + `sqlight`. Lives in its own repository alongside this one
  (checked out as a sibling directory, `../lode_sql`), together with the
  runnable examples (`examples/codegen_demo`, `examples/lustre_form`).

Modules in both packages live under the `lode/` namespace (so you still
`import lode/migrator`), just as Elixir namespaces `Ecto.Migration` under `Ecto.`
despite it shipping in `ecto_sql`.

## Status — v0.1

All six build phases are complete and tested (419 tests across both packages,
including live PostgreSQL round-trips):

| Area | Module(s) | Notes |
|------|-----------|-------|
| Value boundary & errors | `lode/value`, `lode/error` | tagged DB `Value`; `Result`-based errors (no exceptions) |
| Type system | `lode/type_`, `lode/types/*` | int/float/bool/string/binary/uuid/enum/decimal/date-time/array/map; type-erased `FieldType` |
| Schema | `lode/schema` | explicit `Schema(row)` metadata + `load`/`dump` codecs + field builders |
| Changeset | `lode/changeset` | `cast`, validations, constraints, `optimistic_lock`, `apply_action` |
| Query | `lode/query`, `lode/query/expr`, `lode/query/sql` | typed field accessors; builders; parameterized SQL renderer |
| Repo | `lode/repo` | `all/one/get/get_by/insert(_prefixed)/insert_all(_returning)/upsert(_all)(_returning)/insert_or_update/update(_prefixed)/delete(_changeset)/*_all/count/exists/aggregate/stream_fold/stream_for_each/query_raw(_as)/transaction` (transactions nest via savepoints) |
| Multi | `lode/multi` | Ecto's core step set: `insert`/`update`/`delete`/`run`, bulk (`insert_all`/`update_all`/`delete_all`), query (`one`/`all`/`exists`), `put`; `merge`/`append`/`prepend` composition with duplicate-step-name rejection (no `insert_or_update`/`error`/`inspect` steps — a `run` step covers each) |
| Associations | `lode/association`, `lode/preload`, `lode/repo` | `has_many`/`has_one`/`belongs_to`/`has_many_through`/`many_to_many` declared by name; by-name `repo.preload` with nesting (batched, N+1-free); `preload.join` loads a `has_many`/`has_one`/`belongs_to`/`many_to_many` in the parent query via a `LEFT JOIN` (with the association's `where`/`preload_order`); `association.join` derives a `JOIN` from a declared association (Ecto's `assoc(..)`); per-assoc `where`/`preload_order`; write-side `put_assoc`/`cast_assoc` with `on_replace`/`on_delete`/`defaults` (has_many/has_one); invalid staged children fail the write with errors keyed `"<assoc>.<field>"` (+ `association`/`child_index` metadata); declared join-table constraints map violations to field errors (`association.join_constraint`) |
| Adapters | `lode/adapter`, `lode/adapters/memory`, `lode/adapters/postgres`, `lode/adapters/sqlite` | in-memory (tests) + Postgres via `pog` + SQLite via `sqlight` (serverless `:memory:` or file); the adapter carries an `engine` tag so the migrator picks the DDL dialect |
| Migrations | `lode/migration`, `lode/migration/ddl`, `lode/migrator` | typed DDL builder w/ auto-reversible up/down; dialect-aware (Postgres + SQLite, chosen from the adapter's `engine`); `migrate`/`rollback`/`status` over `schema_migrations` |
| Schema spec | `lode/schema/spec` | declarative, pure-data schema spec — the single source of truth; lossless type intent (enums, embeds, decimal, temporal, uuid, jsonb), stored/virtual field kinds, `source`/`as`/`redact` overrides, all five association kinds, `manual_schema` opt-out |
| Codegen | `lode/codegen` | `generate_from_spec`: spec → records (with association fields), `Schema`s with associations registered, typed `FieldRef` accessors, typed preload constructors, enum/embed definitions — **no live DB**; grouped by association connected component to keep mutually-recursive code intra-module. Introspection retained as `bootstrap_spec` (one-shot DB → spec importer) and `generate` (DB → modules via the same emitters), engine-aware (`information_schema` on Postgres; `sqlite_master` + `PRAGMA` on SQLite) |
| Drift check | `lode/drift` | verify a live database against the spec's DDL projection: missing/extra tables & columns, type/nullability/PK mismatches, association-implied foreign keys; virtual fields invisible by construction; engine-aware (Postgres + SQLite — SQLite compares at type-affinity level, see `lode/drift` docs) |

> Note: migrations live in Elixir's separate `ecto_sql` package, not in `ecto`
> itself; `lode_sql` is the equivalent layer for lode.

## A taste

```gleam
import lode/changeset
import lode/query
import lode/query/expr
import lode/repo
import lode/adapters/postgres
import lode/types/primitive
import lode/value.{VInt, VString}
import gleam/dict
import gleam/option.{Some}

// 1. Your own record + a Schema value describing it (see test/ for the full schema).
pub type User { User(id: Int, name: String, age: Int) }

// 2. Typed field accessors make query expressions compile-checked:
fn user_age() {
  expr.FieldRef(binding: 0, name: "age", lode_type: primitive.integer())
}

pub fn example(conn) {
  let r = repo.new(postgres.new(conn))   // or adapters/memory.new() for tests
  let s = user_schema()

  // INSERT ... RETURNING * (the DB assigns the serial id).
  // Public functions take labelled arguments (positional still works too).
  let changeset =
    changeset.cast(
      data: User(0, "", 0),
      schema: s,
      params: dict.from_list([#("name", VString("Alice")), #("age", VInt(30))]),
      permitted: ["name", "age"],
    )
  let assert Ok(alice) = repo.insert(repo: r, schema: s, changeset: changeset)

  // SELECT with a typed WHERE — expr.gte(field: user_age(), to: "thirty") would
  // not compile.
  let adults =
    query.from(source: "users", alias: "u")
    |> query.where(expr.gte(field: user_age(), to: 18))
  let assert Ok(found) = repo.all(repo: r, schema: s, query: adults)

  let assert Ok(Some(_)) = repo.get(repo: r, schema: s, id: VInt(alice.id))
}
```

## Associations & preloading

Declare associations **by name on the schema** (Gleam has no reflection, so you
supply how to read the join keys and how to attach loaded rows), then preload
them by name — Ecto's `Repo.preload(posts, [comments: :author])` style. Each
association loads in one batched query (no N+1); nesting composes to any depth.

```gleam
fn post_schema() -> Schema(Post) {
  post_base()
  |> association.has_many(
    name: "comments",
    related: comment_schema(),      // may itself declare `author`, etc.
    foreign_key: "post_id",
    owner_key: fn(p: Post) { VInt(p.id) },
    child_key: fn(c: Comment) { VInt(c.post_id) },
    put: fn(p, cs) { Post(..p, comments: cs) },
  )
}

// preload by name:
let assert Ok(posts) =
  repo.preload(repo: r, schema: post_schema(), parents: posts, preloads: [
    preload.one("comments"),
  ])

// nested — posts -> comments -> author (like [comments: :author]):
let assert Ok(posts) =
  repo.preload(repo: r, schema: post_schema(), parents: posts, preloads: [
    preload.nest("comments", [preload.one("author")]),
  ])
```

There is no lazy loading — like Ecto, associations are loaded explicitly.

### Association options

Every association takes an `opts:` built from `association.options()`:

```gleam
|> association.has_many(
  name: "comments",
  // ...keys + put...
  opts: association.options()
    |> association.where(expr.neq(field: comment_body(), to: "spam"))   // extra preload filter
    |> association.preload_order([query.desc(expr.col(comment_body()))]) // order children
    |> association.on_replace(association.ReplaceDelete)  // write-side (below)
    |> association.on_delete(association.DeleteAll)       // cascade on repo.delete
    |> association.defaults([#("flagged", value.VBool(False))]),
)
```

### through & many_to_many

```gleam
// has_many :comment_authors, through: [:comments, :author]
|> association.has_many_through(
  name: "comment_authors",
  via: comment_schema, to: user_schema,
  parent_key: fn(p: Post) { value.VInt(p.id) }, via_foreign_key: "post_id",
  mid_owner_key: fn(c: Comment) { value.VInt(c.post_id) },
  mid_key: fn(c: Comment) { value.VInt(c.user_id) },
  to_foreign_key: "id", leaf_owner_key: fn(u: User) { value.VInt(u.id) },
  put: fn(p, us) { Post(..p, authors: us) }, opts: association.options(),
)

// many_to_many :tags, join_through: "posts_tags"
|> association.many_to_many(
  name: "tags", related: tag_schema,
  join_through: "posts_tags", join_owner_key: "post_id", join_related_key: "tag_id",
  owner_key: fn(p: Post) { value.VInt(p.id) }, child_key: fn(t: Tag) { value.VInt(t.id) },
  put: fn(p, ts) { Post(..p, tags: ts) }, opts: association.options(),
)
```

### Writing associations

For `has_many`/`has_one`, stage child rows on the parent changeset and they are
written in one transaction when you `repo.insert`/`repo.update` — honoring the
association's `on_replace` policy, and cascaded on `repo.delete` per `on_delete`:

```gleam
let cs =
  changeset.change(author, author_schema())
  |> changeset.put_assoc("books", [           // typed child changesets
    changeset.change(Book(0, 0, "Dune"), book_schema()),
  ])
let assert Ok(saved) = repo.insert(repo: r, schema: author_schema(), changeset: cs)
// saved.books are inserted with the foreign key set and attached to `saved`.

// or build children from params (Ecto's cast_assoc):
changeset.change(author, author_schema())
|> changeset.cast_assoc(
  name: "books",
  data: Book(0, 0, ""),
  params: book_param_maps,
  with: fn(b, p) { changeset.cast(b, book_schema(), p, ["title"]) },
)
```

`through` is **read/preload-only** by design; `belongs_to` and `many_to_many`
are writable via `put_assoc`/`cast_assoc` (see "Known limitations" for the
details).

`lode/codegen` generates all of this from the **schema spec**
(`lode/schema/spec`) — a hand-authored, pure-data description of your tables
that is the single source of truth (see [`DESIGN.md`](DESIGN.md) §14). With no
live database, `codegen.generate_from_spec(tables)` emits the records
(association fields included), `schema()` functions with associations
registered, typed `FieldRef` accessors, and typed preload-name constructors so
`post_comments([...])` is compile-checked (a typo'd name won't compile). The
spec also carries **virtual fields** (on the record and cast in changesets,
never written to the database), column renames (`source`), custom type
overrides (`as_custom`), enums, and embedded schemas. To keep the
mutually-recursive records and registrations legal under Gleam's
no-circular-imports rule, codegen groups tables by association connected
component — isolated tables get their own clean module, and a cluster of
associated tables shares one module with record-prefixed names.

Migrations stay hand-authored, and `lode/drift` keeps the two honest:
`drift.check(repo:, schema:, tables:)` compares the spec's stored columns
against the engine's catalog (`information_schema` on Postgres;
`sqlite_master` + `PRAGMA` on SQLite) and reports missing/extra tables and columns,
type/nullability/primary-key mismatches, and missing association-implied
foreign keys. To adopt the workflow on an existing database, run
`codegen.bootstrap_spec(repo:, schema:)` once: it introspects and emits a spec
module you refine by hand (the database can't express enums, embeds, or
virtuals).

## Running the tests

The core's tests use the in-memory adapter and need nothing extra:

```sh
gleam test
```

The `lode_sql` repo's Postgres and migration tests need a live database — see
its README. The workflow in
[`.github/workflows/test.yml`](.github/workflows/test.yml) runs this package's
tests and format check on the Erlang target and builds the JavaScript target.

## Publishing

Both packages carry Hex-ready metadata (v0.1.0, descriptions, Apache-2.0 —
the licence Elixir's Ecto uses), and the names `lode` and `lode_sql` are free
on Hex.

Releases are driven by [version_bump](https://hex.pm/packages/version_bump)
(a dev dependency): `gleam run -m version_bump -- --dry-run` previews the next
version and release notes from the conventional commits; the real run (needs
`HEXPM_API_KEY`) tags `v${version}`, publishes to Hex, and commits the version
bump. Config lives under `[tools.version_bump]` in `gleam.toml`.

Publish `lode` before `lode_sql` (whose path dependency on `../lode` must
become a Hex version dependency at its own release time). There is also no
GitHub remote yet — add one and uncomment the `repository` line in
`gleam.toml` so Hexdocs links back to the source.

## Known limitations & not-yet-implemented

What works today is listed in the status table above. The items below are
genuine gaps versus Elixir's Ecto — tracked here so they're visible rather than
tribal knowledge. (Features Ecto has that this port *intentionally* drops —
process-dictionary repo, `__schema__` reflection, bang/exception variants,
telemetry, macro keyword syntax — are recorded in [`DESIGN.md`](DESIGN.md) §11.)

### Types & values

- **`decimal` and the date/time types are real.** `decimal` is backed by
  [`dee`](https://hexdocs.pm/dee/) (a port of Elixir's `Decimal`, so equality
  and rounding match Ecto); the temporal types are backed by `gleam/time`
  (`calendar.Date`, `calendar.TimeOfDay`, `timestamp.Timestamp`). They compare,
  order, and round-trip natively through `pog`. Reads are **lossless**: the
  Postgres adapter installs a custom `numeric` decoder (`lode_pg_numeric`) that
  returns the wire digits as a decimal string instead of letting `pgo` decode
  `numeric` to a 64-bit `Float`, so high-precision values keep every digit on
  read (writes were already lossless — `decimal` is sent as text).
- **JSON / `jsonb` columns are supported** via a `json` type
  (`lode/types/json`) backed by a `Value`↔JSON codec, round-tripping through a
  Postgres `jsonb` column (sent as text, parsed back on load). `WHERE` clauses
  can be operator-aware: `expr.json_get`/`json_get_text` render `->`/`->>`
  member access and `expr.json_contains` renders `@>` containment (all built
  on `expr.fragment`).

### Schema & changeset

- **Embedded schemas are supported** — `embeds_one`/`embeds_many`
  (`lode/embed`) store a nested schema inline as `jsonb`, with
  `cast_embed_one`/`cast_embed_many` mirroring `cast_assoc`. Embedded `decimal`/
  temporal fields round-trip (serialized as strings, parsed by the field's
  lenient load).
- **Validator coverage matches Ecto's common set** — `required`, `length`,
  `number`, `inclusion`/`exclusion`/`subset`, `format` (via `gleam_regexp`),
  `confirmation`, `acceptance`, and custom `validate_change`. Confirmation and
  acceptance fields are typically declared as `schema.virtual_field`s so they
  cast and validate without ever being stored.
- **Constraint violations map to changeset errors** — a `unique_constraint` /
  `foreign_key_constraint` / `check_constraint` declared on the changeset turns
  a matching database violation into `Error(ChangesetInvalid(..))` with the
  field error (e.g. `"has already been taken"`), instead of a raw
  `ConstraintError`. An undeclared violation still surfaces as `ConstraintError`.

### Associations

- **Write-side is supported for `has_many`/`has_one`, `belongs_to`, and
  `many_to_many`** via `put_assoc`/`cast_assoc` (with `on_replace`/`on_delete`).
  `belongs_to` inserts the referenced row first and sets the owner's foreign key;
  `many_to_many` upserts children and reconciles join rows. `has_many :through`
  is **read/preload-only by design** (write the underlying association instead).
- **Composite primary keys** are honored in association writes (child identity
  for `on_replace` matching and updates uses the full key). Note: insert-vs-update
  for *natural* composite keys is still inferred from key presence — pre-existing
  rows are matched, but brand-new natural-key children supplied to `put_assoc`
  are treated as updates. Relationship links (foreign keys, join keys) remain
  single-column.

### Query

- **The in-memory adapter** evaluates single-source queries, aggregate selects
  (`count`/`sum`/`avg`/`min`/`max`), and subquery sources / `IN (subquery)`; it
  does **not** do joins, group-by, or fragments — those go through the SQL
  renderer + Postgres.
- **Subqueries** are supported as a `FROM` source (`query.from_subquery`) and as
  `WHERE x IN/NOT IN (subquery)` (`query.where_in_subquery`), with the
  subquery's parameters threaded into the outer `$n` sequence.
- **Window functions** — `expr.over(call, partition_by:, order_by:)` in `select`
  (ranking `expr.row_number`/`rank`/`dense_rank` or any aggregate), plus named
  windows via `query.window` + `expr.over_named`; identical standard SQL on
  Postgres and SQLite. Frames (`ROWS BETWEEN ...`) drop to `repo.query_raw`.
- **CTEs** — `query.with_cte(q, name:, as_:, recursive:)` renders
  `WITH [RECURSIVE] "name" AS (...)`; reference the CTE by name as an ordinary
  `from`/join source, build recursive bodies with `query.union_all`, and the
  CTE's parameters thread first into the `$n`/`?n` sequence. Plain and
  recursive CTEs work on Postgres and SQLite; CTEs on `update_all`/`delete_all`
  drop to `repo.query_raw`.
- **Row locks, `first`/`last`, and set combinations** — `query.lock(q,
  "FOR UPDATE")`, `query.first`/`query.last` (order + `limit 1`), and
  `query.union`/`query.union_all` (compose with `from_subquery` to order a
  union).
- **Schema prefixes** for Postgres-schema multi-tenancy — `query.prefix(q,
  schema)` qualifies reads (and `update_all`/`delete_all`), and
  `changeset.put_prefix` (plus `repo.insert_prefixed`/`update_prefixed`)
  qualifies writes, so tables render as `"schema"."table"`.
- **`expr.fragment(sql, args)` is the raw-SQL splice** (Ecto's `fragment`):
  each `?` is replaced by an expression, literals still become `$n` parameters.
  It covers most one-off SQL (json operators ship as fragment helpers).

### Repo

- **Bulk insert is supported** — `repo.insert_all(repo:, schema:, rows:)`
  inserts typed rows in a single statement (count back), and
  `insert_all_returning` loads the stored rows with generated keys filled in.
  It is the raw bulk path (no changesets, no association writes); autogenerated
  and virtual columns are omitted automatically. One statement means one
  parameter per column per row, so chunk very large batches yourself (Postgres
  caps a statement at 65535 parameters).
- **Upsert is supported** (Ecto's `:on_conflict`/`:conflict_target`) —
  `repo.upsert(repo:, schema:, changeset:, on_conflict:)` and
  `upsert_all`/`upsert_all_returning` take a `lode/on_conflict` policy:
  `Nothing` (`DO NOTHING`; a skipped insert comes back as `Ok(None)`) or
  `Update` (`DO UPDATE SET`) with a `Columns`/`Constraint` target and a
  `Replace(cols)`/`ReplaceAll`/`ReplaceAllExcept(cols)`/`Set(values)` action.
  `DO UPDATE` results are read back with `RETURNING`, so the returned struct
  reflects the stored row. The in-memory adapter detects conflicts only for
  `Columns` targets (it has no constraint catalog). Upserts don't run staged
  association writes (`put_assoc`/`cast_assoc`) — under a conflict the parent
  row's identity is ambiguous, so that combination is rejected.
- **Aggregates** — `repo.count` pushes `SELECT count(*)` to the database
  (falling back to materializing only when a `limit`/`offset`/`group_by`/
  `distinct` would make `count(*)` wrong), and `repo.aggregate(query:, by:)`
  computes `sum`/`avg`/`min`/`max`/`count` server-side, returning the scalar.
- **Transactions nest** — a `repo.transaction` opened on the transaction-scoped
  repo inside another runs as a `SAVEPOINT` (Postgres) or snapshot (memory), so
  an inner rollback is independent of the outer.
- **Test sandbox** — `lode/sandbox`'s `sandbox.run(repo, body)` isolates a test
  in a transaction that always rolls back (no truncation); nested transactions
  inside become savepoints. There is no process-ownership registry by design —
  concurrency is own-`Repo`-per-async-test (see `DESIGN.md` §11).
- **Insert-or-update** — `repo.insert_or_update(repo:, schema:, changeset:)`
  inserts or updates depending on whether the changeset's data already has a
  primary key (lode has no struct `__meta__.state`, so PK presence is the
  signal — same rule as `cast_assoc`). See the "Divergences" guide for the
  natural-key caveat.
- **Streaming reads** — `repo.stream_fold`/`stream_for_each` walk a result set
  in `batch_size` chunks rather than materializing it like `all`. On Postgres
  this is a real server-side cursor (`DECLARE`/`FETCH`/`CLOSE`) in a transaction
  the adapter manages: constant memory and one MVCC snapshot. The shape differs
  from Ecto — a fold, not a lazy `Enumerable`, since Gleam has no lazy stream
  type (see the "Divergences" guide).
- **Raw-SQL escape hatch** — `repo.query_raw(sql, params)` runs arbitrary
  parameterized SQL (positional `$1`, `$2`, ... like Ecto's `Repo.query/3`),
  returning name-keyed `Dict(String, Value)` rows: order-independent and
  projection-tolerant (load by name with `schema.load_field`, absent columns as
  `VNull`). `repo.query_raw_as(schema:, sql:, params:)` loads each row into a
  typed struct via `schema.load` in one call.
- **Query-integrated preload is supported** — attach preloads to the query
  with `query.preload(q, [preload.nest("comments", [...])])` and
  `repo.all`/`repo.one` load them onto the results (batched per association,
  same machinery as the separate `repo.preload` step, which remains for
  already-loaded rows).

### Migrations & tooling

- **Migration CLI** — `migrator.run(repo, migrations, args)` dispatches
  `migrate` / `rollback [n]` / `status`. Wire it into a `gleam run` entrypoint
  (see `examples/codegen_demo/src/migrate.gleam`):
  `gleam run -m migrate migrate|rollback [n]|status`. Each migration runs in its
  own transaction, so a failed migration rolls back cleanly.
- **Foreign keys and timestamps have builders** — `ddl.references(column,
  ddl.reference("table"))` (with `ddl.on_delete`/`ddl.on_update` taking a
  `NoAction`/`Restrict`/`Cascade`/`SetNull` action, and `ddl.references_column`
  for a non-`id` target), and `ddl.timestamps()` for the conventional
  `inserted_at`/`updated_at` pair. Still partial elsewhere: no
  `migration.create_schema` (CREATE SCHEMA), alter-column-type, or richer index
  options — use the `migration.execute` escape hatch.

### Codegen & spec

- **Schema-first generation is implemented** (DESIGN §14): the spec is the
  source of truth, `generate_from_spec` needs no database, introspection is
  demoted to a drift verifier (`lode/drift`) plus a one-shot importer
  (`bootstrap_spec`). Association options live in the spec too:
  `assoc_on_replace`/`assoc_on_delete` as plain data, and
  `assoc_where`/`assoc_preload_order`/`assoc_defaults` as source snippets (the
  same code-as-data convention as `as_custom`), each rendered onto the
  generated registration as `association.options()` builder pipes. Residual
  gaps:
  - **`references` assumes a single-column key** on the one side (composite
    relationship links remain single-column, matching the association layer).
  - **Spec `as_custom`/`virtual` defaults/assoc-option snippets are
    source-text expressions** — they are compile-checked only once the
    generated module is compiled.
- Type-name singular/pluralization is **naive** — but it only applies to the
  introspection bridge (`generate`/`bootstrap_spec`); authored specs name their
  record types explicitly.

### Adapters & targets

- **Postgres** (via `pog`), **SQLite** (via `sqlight`), and the in-memory adapter
  exist; no MySQL. The SQL/DDL renderer is dialect-parameterized, so the query
  builder, changesets, repo, and migrations work unchanged across Postgres and
  SQLite — the adapter is the only line that names the engine (`postgres.new` /
  `sqlite.new` / `memory.new`). SQLite is embedded, so its tests need no server
  (`sqlight.open(":memory:")`), and the adapter serializes its single
  connection across processes — concurrent transactions queue rather than
  interleave.
- **Erlang/BEAM target only**; the JavaScript target is untested.