---
title: Model your tables
description: Wrap remix/data-table tables in models with scopes, callbacks and typed meta fields, bind them per request as ctx.models, and dispatch jobs only after a write commits.
section:
    title: Data & background work
    order: 6
order: 3
lastUpdated: 2026-10-09
---

Once an app has more than a few tables, the code around them settles into a pattern: a module
per table with `findBy…` and `listBy…` functions that all take the database first, plus some
place where a write enqueues a job or sends a mail. [`@sdxc/data-model`](/api/data-model)
is that layer, written once. A model is a `remix/data-table` table plus named scopes, custom
methods and async callbacks. It is bound to a database once per request, job or test, and every
member runs there.

This guide defines a model, publishes the app's models as `ctx.models`, dispatches side effects
only after a write lands, keeps several record types in one table, stores open-ended attributes
in a key/value table, and builds test data with factories. Your tables stay as you declared
them; the database setup is the one from
[Query D1 and Durable Object SQL](/docs/data-and-background-work/databases).

```bash
npm add @sdxc/data-model @sdxc/result @sdxc/validate remix
npm add -D @sdxc/sample
```

## Define a model

`createModel` takes a table and returns a definition. Scopes are named refinements of a query;
methods are built from the bound model, so they compose its scopes; callbacks run around every
write made through the model:

```typescript {% title="app/models/users.ts" %}
import { createModel } from "@sdxc/data-model";
import { generateUUID } from "@sdxc/uuid";
import { fail } from "remix/data-table";

import { users } from "~/database/schema";

export const Users = createModel(users, {
	optional: ["id"],
	scopes: {
		active: (query) => query.where({ deleted_at: null }),
		inTeam: (query, teamId: string) => query.where({ team_id: teamId }),
	},
	methods: (model) => ({
		findByEmail: (email: string) =>
			model.active().where({ email: email.toLowerCase() }).first(),
	}),
	callbacks: {
		async validate(values) {
			if (values.name === "") return fail("Name is required", ["name"]);
		},
		async beforeCreate(values) {
			return {
				...values,
				id: values.id ?? generateUUID(),
				email: values.email.toLowerCase(),
			};
		},
	},
});
```

`optional` lists the columns `create` may leave out because a callback or the database fills
them; every other non-null column is required by the type of `create`. A scope receives a query
it may refine with `where`, `orderBy`, `limit` and the other methods that keep the row type, and
answers it. `with()` and `select()` change the row, so they belong in a method.

Bind the definition to a database and use it:

```typescript
import { isFailure } from "@sdxc/result";

let users = Users.bind({ db });

let created = await users.create({ email: "Pat@Example.com", name: "Pat" });
if (isFailure(created)) return created.error.issues;

await users.find(created.data.id); // the row, or null
await users.active().inTeam(teamId).orderBy("name", "asc").all();
```

Reads answer `null` for a missing row. Writes answer a `Result`: a `ValidationError` from
[`@sdxc/validate`](/api/validate) when a callback, the table's own hooks or a database
constraint refuses the write, and `NotFound` when an update or delete names a key the model has
no row for. A unique index violation comes back as an issue at the indexed column, so a form
renders the duplicate email the same way it renders a failed schema.

Every scope returns a real data-table query, so [`@sdxc/pagination`](/api/pagination) pages it
unchanged:

```typescript
import { Pagination } from "@sdxc/pagination";

let page = await Pagination.byOffset(users.active().orderBy("name", "asc"), {
	page: 1,
	perPage: 25,
});
```

`Pagination.byKeyset` owns the ordering, so give it a scope that only filters; it refuses a
query that already orders.

## Publish the models on `ctx.models`

A registry lists the models under the names handlers read them by. An entry can be an import,
which loads the module on its first call in each request, so a request evaluates only the
models it touches:

```typescript {% title="app/models/index.ts" %}
import { createModels } from "@sdxc/data-model";

import { Users } from "./users";

export const models = createModels({
	users: Users,
	articles: () => import("./articles"),
});
```

The router middleware binds the registry per request. Its function builds the model context
from the request context, once, on the first model access, so it reads whatever earlier
middleware published, such as the database on `ctx.db`:

```typescript {% title="app/router.ts" %}
import { models as modelsMiddleware } from "@sdxc/data-model/router";
import { createRouter } from "remix/router";

import database from "~/http/middleware/database";
import { openDatabase } from "~/lib/database";
import { models } from "~/models";

export const router = createRouter({
	middleware: [
		database(openDatabase),
		modelsMiddleware(models, (ctx) => ({ db: ctx.db })),
	],
});

router.get(routes.users.show, async (ctx) => {
	let user = await ctx.models.users.findByEmail(ctx.params.email);
	if (user === null) return new Response(null, { status: 404 });
	return Response.json(user);
});
```

An MCP tool mounted on the router runs on the same request context, so it reads
`ctx.get(Models)` the same way. A job dispatcher takes the counterpart from
`@sdxc/data-model/jobs`, given the database its own middleware published:

```typescript {% title="app/jobs/dispatcher.ts" %}
import { models as modelsMiddleware } from "@sdxc/data-model/jobs";
import { createJobDispatcher } from "@sdxc/jobs";

export const dispatcher = createJobDispatcher({
	middleware: [
		database(),
		modelsMiddleware(models, (ctx) => ({ db: ctx.database })),
	],
});
```

## Dispatch side effects after commit

Callbacks receive a model context. Its `db` is the database the model is bound to, `models` is
the rest of the registry bound to the same request, and `get` and `require` read whatever the
host context published, which is how a callback reaches the job queue:

```typescript {% title="app/models/users.ts" %}
import { createModel } from "@sdxc/data-model";
import { Jobs } from "@sdxc/jobs/router";

import jobs from "~/jobs";

export const Users = createModel(users, {
	callbacks: {
		async afterCommit(event, ctx) {
			if (event.operation !== "create") return;
			await ctx
				.require(Jobs)
				.enqueue(jobs.sendWelcome, { userId: event.row.id });
		},
	},
});
```

`afterCommit` runs right after a write made on its own. Inside `ctx.models.transaction(...)` it
waits for the whole callback, and runs only when the callback resolves with something other than
a `Failure`:

```typescript
import { isFailure } from "@sdxc/result";

let result = await ctx.models.transaction(async (models) => {
	let user = await models.users.create({ email, name });
	if (isFailure(user)) return user;

	return models.teams.create({ owner_id: user.data.id, name: "Personal" });
});
```

If the team fails, the returned `Failure` drops the queued welcome job. What it cannot do on D1
is take the user back: D1 commits every statement as it runs, so the user row stays and the
caller decides on the compensating delete. Bind with `{ transactions: "database" }` on an
adapter with real transactions, such as SQLite in tests, and the user rolls back too.

`afterCreate`, `afterUpdate` and `afterDelete` run before the write is reported, and can fail it.
On D1 their database work has already committed, so keep them to work the model can repeat, and
put anything with consequences outside the database in `afterCommit`.

An update or delete built from a query, `ctx.models.users.active().update({ ... })`, is one
statement over every matching row and runs no model callbacks. It is the escape hatch for
set-based writes; iterate and call `update(key, values)` per row when each row needs its
callbacks.

## Keep several record types in one table

A model can pin columns to fixed values with `constraints`, and a base model that names a
discriminator column can be extended once per value. Every query of a sub-model filters on its
value, every create writes it, and a key belonging to another type answers `NotFound`:

```typescript {% title="app/models/posts.ts" %}
import { createModel } from "@sdxc/data-model";
import { fail } from "remix/data-table";

export const Posts = createModel(posts, {
	inheritance: "type",
	scopes: { live: (query) => query.where({ deleted_at: null }) },
	callbacks: {
		async beforeDelete(row) {
			if (row.federated_at !== null)
				return fail("Federated posts are retracted");
		},
	},
});

export const Articles = Posts.extend("article", {});
export const Likes = Posts.extend("like", {});
```

A sub-model has every scope, method and callback of its base, and its own callbacks run after
the base's. Its rows type `type` as `"article"`, and its `create` input leaves `type` out. The
base reads every row with `type` as the full union, and writing through the base runs only the
base's callbacks.

## Store open-ended attributes in a meta table

Attributes that differ per record type, or that change too often to deserve a migration, often
live in a key/value table beside the main one. A model names that table once and declares typed
fields over it; rows then carry a decoded `meta` object:

```typescript {% title="app/models/articles.ts" %}
import { field } from "@sdxc/data-model";
import { generateUUID } from "@sdxc/uuid";

import { postMeta } from "~/database/schema";

export const Posts = createModel(posts, {
	inheritance: "type",
	metaTable: { table: postMeta, foreignKey: "post_id", generateId: generateUUID },
});

export const Articles = Posts.extend("article", {
	meta: {
		slug: field.text().required(),
		title: field.text(),
		locale: field.enum(["en", "es"]).default("en"),
		tags: field.list(field.text()),
	},
	methods: (model) => ({
		findBySlug: (slug: string) => model.live().whereMeta("slug", slug).first(),
	}),
});

export default Articles;
```

Writes take a `meta` object, and an update touches only the keys it names, with `null` removing
one:

```typescript
let article = await ctx.models.articles.create({
	author_id: ctx.user.id,
	meta: { slug: "hello", title: "Hello", tags: ["remix"] },
});

await ctx.models.articles.update(id, { meta: { title: "Hello, world", tags: null } });
```

Every field reads as possibly missing unless it declares a default, since any key can be
absent for any row; `required()` makes `create` refuse a write without it, with the issue at
`["meta", "slug"]`. A value the field's codec rejects reads as missing. `withMeta(["title"])`
loads only the keys a list shows, and `whereMeta(key, value)` keeps the rows holding a value.
`whereMeta` looks the matching rows up in the meta table first, so use it for selective keys
such as a slug; a value a listing filters or sorts on by the hundreds belongs in a column.

A write inserts the new meta rows before deleting the old ones, and reads take each key's
latest row, so a failure between the two statements on D1 still reads the new value.

## Write helpers over any model

`BoundModel<typeof Users>` names a bound model's type, and `ModelRow`, `CreateValues` and
`UpdateValues` name its row and write inputs. `AnyModel` is the constraint for a helper written
once for every model, and its optional shape narrows it to models whose rows have those columns:

```typescript {% title="app/models/helpers.ts" %}
import type { AnyModel, BoundModel, ModelRow } from "@sdxc/data-model";

export async function findOr404<M extends AnyModel>(
	model: BoundModel<M>,
	id: string,
): Promise<ModelRow<M>> {
	let row = await model.find(id);
	if (row === null) throw new Response(null, { status: 404 });
	return row;
}

export async function forPost<M extends AnyModel<{ post_id: string }>>(
	model: BoundModel<M>,
	postId: string,
): Promise<ModelRow<M>[]> {
	return model.query().where({ post_id: postId }).all();
}
```

`findOr404(ctx.models.articles, id)` answers an article row with its decoded meta, and
`forPost(ctx.models.users, id)` fails to type-check, because a user row has no `post_id`.

## Build test data with factories

`@sdxc/data-model/testing` defines factories whose attributes draw from
[`@sdxc/sample`](/api/sample) and write through the model, so callbacks, constraints and meta
run in a fixture exactly as in production:

```typescript {% title="app/test/factories.ts" %}
import { defineFactory } from "@sdxc/data-model/testing";

export const UserFactory = defineFactory(Users, {
	name: ({ sample }) => sample.person.fullName(),
	email: ({ sequence }) => `user${sequence}@example.com`,
}).trait("admin", { role: "admin" });

export const ArticleFactory = defineFactory(Articles, {
	author_id: async ({ create }) => (await create(UserFactory)).id,
	meta: ({ sample }) => ({
		slug: sample.lorem.slug(),
		title: sample.lorem.sentence(),
	}),
}).trait("draft", { published_at: null });
```

```typescript {% title="app/models/articles.test.ts" %}
import { createFactories } from "@sdxc/data-model/testing";

test("keeps drafts out of the published list", async () => {
	let models = createModels({ users: Users, articles: Articles }).bind({ db });
	let factories = createFactories(models, { seed: 42 });

	let author = await factories.create(UserFactory, "admin");
	await factories.createMany(ArticleFactory, 3, { author_id: author.id });
	await factories.create(ArticleFactory, "draft", { author_id: author.id });

	expect(await models.articles.query().where({ published_at: null }).count()).toBe(
		1,
	);
});
```

An attribute's function runs only when the call does not override it, so the articles above
create no extra users. Each factory draws from a stream derived from the seed by its name, so
adding a factory leaves every other factory's values where they were, and a failing run
reproduces from its seed. A write that fails throws, with the `ValidationError` as its cause.

## Where to go next

- [Query D1 and Durable Object SQL](/docs/data-and-background-work/databases) — open the
  database the models bind to.
- [Background jobs and cron](/docs/data-and-background-work/jobs-and-cron) — define the jobs an
  `afterCommit` callback enqueues.
- [Full-text search over SQLite](/docs/data-and-background-work/full-text-search) — wrap a search
  query with `from()` so a model's scopes apply to it.
- [`@sdxc/data-model`](/api/data-model) — every option, member and type.