Model your tables

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 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.

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:

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:

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 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 pages it unchanged:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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 and write through the model, so callbacks, constraints and meta run in a fixture exactly as in production:

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 });

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 — open the database the models bind to.

• Background jobs and cron — define the jobs an afterCommit callback enqueues.

• Full-text search over SQLite — wrap a search query with from() so a model's scopes apply to it.

• @sdxc/data-model — every option, member and type.