Testing with Lode

After you have successfully set up your database connection with lode (see Getting Started), using it from your tests needs a little more thought. In Elixir, this is where Ecto.Adapters.SQL.Sandbox comes in: a special pool that wraps every test in a transaction so tests can talk to the database concurrently without seeing each other’s data.

lode’s testing story is structurally different, and it comes in two layers:

1. Unit tests run against the in-memory adapter (lode/adapters/memory) — no database at all. This is how the entire core lode package tests itself.
2. Integration tests run against a real PostgreSQL, configured through the standard libpq environment variables. This is how the lode_sql package tests itself.

There is a transaction-rollback sandbox (sandbox.run, covered below), and the in-memory layer removes most of the need for one anyway: the majority of your tests never open a connection in the first place.

Test configuration is just a value

Elixir’s guide starts by writing a config/test.exs that points the repo at a myapp_test database and swaps the pool for the sandbox. lode has no application config and no globally registered repo process — a repo is a plain value you construct from an adapter (see DESIGN.md in the repository). So “configuring the repo for tests” is just calling a different constructor:

import lode/adapters/memory
import lode/repo

let r = repo.new(memory.new())

Production code that takes a Repo argument doesn’t know or care which adapter is behind it. Pass repo.new(postgres.new(conn)) in the application and repo.new(memory.new()) in tests — that is the whole dependency-injection story.

A third option sits between the two: the SQLite adapter (lode/adapters/sqlite) against an in-memory database, repo.new(sqlite.new(sqlight.open(":memory:"))). It needs no server (SQLite is embedded), yet exercises real SQL — actual WHERE/JOIN execution, real UNIQUE/NOT NULL constraints (surfaced as typed ConstraintErrors), and migrations through the migrator. Reach for it when a test depends on SQL semantics the in-memory adapter only approximates, without standing up Postgres.

Unit tests with the in-memory adapter

Every call to memory.new() creates a fresh, empty, ETS-backed store. Two tests can never see each other’s rows because they don’t share a store — which is exactly the isolation property the SQL Sandbox exists to provide, here for free, by construction.

A complete test module, in the same idiom the library’s own lode/test/repo_test.gleam uses (lode uses gleeunit rather than ExUnit — any public function ending in _test is a test):

import lode/adapters/memory
import lode/changeset
import lode/query
import lode/query/expr.{FieldRef}
import lode/repo
import lode/schema.{type Schema}
import lode/types/primitive
import lode/value.{VInt, VString}
import gleam/dict
import gleam/option.{Some}
import gleam/result

pub type User {
  User(id: Int, name: String, age: Int)
}

fn user_schema() -> Schema(User) {
  schema.new(
    source: "users",
    fields: [
      schema.primary_key("id", primitive.id()),
      schema.field("name", primitive.string()),
      schema.field("age", primitive.integer()),
    ],
    load: fn(row) {
      use id <- result.try(schema.require_int(row, "id"))
      use name <- result.try(schema.require_string(row, "name"))
      use age <- result.try(schema.require_int(row, "age"))
      Ok(User(id:, name:, age:))
    },
    dump: fn(u: User) {
      dict.from_list([
        #("id", VInt(u.id)),
        #("name", VString(u.name)),
        #("age", VInt(u.age)),
      ])
    },
  )
}

fn new_user(name: String, age: Int) {
  changeset.cast(
    data: User(id: 0, name: "", age: 0),
    schema: user_schema(),
    params: dict.from_list([#("name", VString(name)), #("age", VInt(age))]),
    permitted: field.fields(["name", "age"]),
  )
}

pub fn insert_and_query_test() {
  // A brand-new store for this test alone.
  let r = repo.new(memory.new())
  let s = user_schema()

  let assert Ok(alice) = repo.insert(repo: r, schema: s, changeset: new_user("Alice", 30))
  assert alice.id == 1

  let assert Ok(_) = repo.insert(repo: r, schema: s, changeset: new_user("Bob", 17))

  let adults =
    query.from(source: "users", alias: "u")
    |> query.where(expr.gte(
      field: FieldRef(binding: 0, name: "age", lode_type: primitive.integer()),
      to: 18,
    ))
  let assert Ok([only_alice]) = repo.all(repo: r, schema: s, query: adults)
  assert only_alice.name == "Alice"

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

The in-memory adapter assigns auto-incrementing integer ids on insert, evaluates where/order_by/limit/offset, supports update_all, delete_all, and even repo.transaction with snapshot/rollback semantics — so transactional logic (including lode/multi pipelines) is unit-testable without Postgres.

Divergence — in-memory query coverage. Ecto’s sandbox runs your real queries against a real database, so everything a query can express is testable in isolation. lode’s in-memory adapter evaluates single-source queries, aggregate selects (count/sum/avg/min/max), group_by with a projected aggregate select, and subquery sources / IN (subquery) — but not joins, HAVING, correlated subqueries, DISTINCT, window functions, CTEs, or fragments, and repo.query_raw returns an AdapterError (there is no SQL engine inside it). Upserts detect conflicts only for an on_conflict.Columns target, since the store has no constraint catalog. Anything in that list must be tested against live Postgres, as described next. TASK:in-memory-query-coverage

Integration tests against a real PostgreSQL

Where Elixir configures config/test.exs, lode’s own SQL test suite keeps one small shared module, lode_sql/test/test_db.gleam, that builds a pog connection from the standard libpq environment variables — PGHOST, PGPORT, PGDATABASE, PGUSER, PGPASSWORD — with local-development defaults (localhost:5432, database lode_test, authenticating as the OS user). The shape of it:

import gleam/erlang/process
import pog

pub fn connect(name name: String) -> pog.Connection {
  let config =
    pog.default_config(process.new_name(name))
    |> pog.host("localhost")        // or read PGHOST etc. from the env
    |> pog.database("lode_test")
    |> pog.rows_as_map(True)
    |> pog.pool_size(1)
  let assert Ok(started) = pog.start(config)
  started.data
}

Two practical notes from the library’s own suite:

• One named pool per test module (test_db.connect(name: "lode_pg"), name: "lode_pg_mig"), …). Pools are never closed, so keep pool_size(1) — many test modules each holding a large pool can exceed Postgres max_connections.
• rows_as_map(True) is required — the Postgres adapter reads rows as name-keyed maps.

No case templates, no setup callbacks

ExUnit gives Ecto a RepoCase template whose setup block checks out a sandbox connection before every test. gleeunit has neither case templates nor setup/teardown hooks, so the lode idiom is plainer: each test calls ordinary setup functions at the top. Table setup and teardown happen by executing DDL directly, exactly as lode_sql/test/postgres_test.gleam does:

import lode/adapters/postgres
import lode/repo.{type Repo}
import pog
import test_db

fn connect() -> pog.Connection {
  test_db.connect(name: "my_app_pg")
}

fn reset_schema(conn: pog.Connection) -> Nil {
  let assert Ok(_) =
    pog.query("DROP TABLE IF EXISTS users") |> pog.execute(conn)
  let assert Ok(_) =
    pog.query(
      "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, age INT NOT NULL)",
    )
    |> pog.execute(conn)
  Nil
}

pub fn round_trip_test() {
  let conn = connect()
  reset_schema(conn)
  let r: Repo = repo.new(postgres.new(conn))
  // ...exactly the same test body as the in-memory version...
}

Because the body of an integration test is identical to the body of a unit test (only the adapter differs), a common pattern is to extract the assertions into a function taking a Repo and call it from both suites. For building the rows themselves, see Test factories.

If you prefer migrations over hand-written DDL, run your real migration list at the top of the suite instead — migrator.migrate(r, migrations) is idempotent over the schema_migrations table, and r.adapter.execute_ddl("DROP TABLE IF EXISTS ...") gives you a clean slate first (this is what lode_sql/test/migration_test.gleam does).

The test sandbox

sandbox.run wraps a test body in a transaction that is always rolled back, so the database is left untouched between tests — per-test isolation without truncation or ordering coupling:

import lode/sandbox

pub fn create_user_test() {
  let r = repo.new(postgres.new(conn))
  use tx <- sandbox.run(r)
  let assert Ok(alice) = repo.insert(tx, user_schema(), new_user("Alice", 30))
  let assert Ok([_]) = repo.all(tx, user_schema(), query.from("users", "u"))
  assert alice.name == "Alice"
}
// ROLLBACK on the way out — the rows never persist.

The body runs against the transaction-scoped tx repo; on exit the transaction rolls back. Because it is built on repo.transaction, a transaction the test itself opens (on tx) becomes a savepoint nested inside the sandbox — so you can sandbox tests of transactional code and their inner rollbacks behave correctly. sandbox.run works on the in-memory adapter too (rollback via its snapshot).

Divergence — sandbox concurrency & ownership. sandbox.run covers the rollback-based isolation that keeps a serial suite clean; it does not replicate Ecto’s process-ownership registry (allow/3, shared mode), because lode has no process-dictionary repo by design (see DESIGN.md). For concurrency, give each async test its own Repo (own connection); to share the sandboxed connection with a spawned process, pass the tx repo to it. One caveat: a failing assert panics before the explicit rollback, so isolation on the failure path comes from the connection being reclaimed, not the clean forced rollback.

Two habits still help keep a Postgres-backed suite tidy: push everything you can down to the in-memory adapter (reserve Postgres for the SQL renderer’s blind spots — joins, aggregates, fragments, constraints, migrations), and give each test module its own tables so modules can’t trample each other regardless of execution order.

Creating and migrating the test database

The upstream guide closes by aliasing mix test to ecto.create --quiet, ecto.migrate, test. There is no Mix here, and no ecto.create equivalent:

Storage up/down. storage.create(repo, database:) / storage.drop(repo, database:) create and drop the database (Ecto’s storage_up/storage_down), run against a Repo connected to another database. There’s no mix-style CLI, so wire them into a gleam run entrypoint or create the test database out of band. TASK:storage-up-down

In practice that means:

createdb lode_test     # once, locally
cd lode && gleam test        # core: in-memory, no database needed
cd lode_sql && gleam test    # SQL: needs the live Postgres

Migrating is in-band: call migrator.migrate(r, migrations) from test setup (or wire migrator.run into a gleam run -m migrate entrypoint and invoke it before the suite). On CI, export the PG* variables to point at a service container — the repository’s own .github/workflows/test.yml runs the full suite against a postgres:16 service this way.

For the complete list of differences from Elixir’s Ecto, see Divergences from Ecto.