sdxc

[ Data & storage ]

@sdxc/data-model

Models over remix/data-table tables with scopes, async callbacks and typed meta fields

npm add @sdxc/data-model remix
pnpm add @sdxc/data-model remix
yarn add @sdxc/data-model remix
bun add @sdxc/data-model remix
Installs with
@sdxc/result@sdxc/validate
Depends on
@standard-schema/spec
Used by
uptime, auth-saas, reader, blog
Source
packages/data-model

Models over remix/data-table tables with scopes, async callbacks and typed meta fields.

A model is a remix/data-table table plus named scopes, custom methods, async callbacks and typed fields over a key/value meta table. Define it once and bind it per request, job or test to the right database; rows stay the plain objects data-table returns, and every scope is a real data-table Query, so pagination, search and eager loading take them unchanged.

Installation

npm add @sdxc/data-model

Tables come from remix/data-table in remix; writes answer a Result from @sdxc/result and fail with the ValidationError from @sdxc/validate. The ./jobs entry point needs @sdxc/jobs, ./cli needs @sdxc/cli and ./testing needs @sdxc/sample.

Usage

Define a model

import { createModel } from "@sdxc/data-model";
import { generateUUID } from "@sdxc/uuid/v4";
import { fail } from "remix/data-table";

import { users } from "./schema.js";

export const Users = createModel(users, {
	optional: ["id"],
	defaultScope: (query) => query.where({ deleted_at: null }),
	scopes: {
		inTeam: (query, teamId: string) => query.where({ team_id: teamId }),
	},
	methods: {
		findByEmail(email: string) {
			return this.query().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() };
		},
	},
});

Bind it, read and write

import { ConstraintViolation } from "@sdxc/data-model";
import { isFailure } from "@sdxc/result";

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

let created = await users.create({ email: "Pat@Example.com", name: "Pat" });
if (isFailure(created)) {
	if (created.error instanceof ConstraintViolation && created.error.kind === "unique") {
		return "That email is taken";
	}
	return created.error.issues;
}

await users.find(created.data.id); // the row, or null
await users.inTeam(teamId).orderBy("name", "asc").all(); // deleted users left out
await users.unscoped().where({ id }).first(); // including deleted ones

Reads answer null for a missing row. create, update, upsert and delete answer a Result: a callback's fail(...) or a database constraint comes back as a ValidationError, and an update or delete of a row the model cannot see comes back as NotFound.

Publish models per request

import { createModels } from "@sdxc/data-model";
import { models } from "@sdxc/data-model/router";
import { createRouter } from "remix/fetch-router";

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

let router = createRouter({
	middleware: [database(), models(registry, (ctx) => ({ db: ctx.db }))],
});

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

The context function runs once per request, on the first model access, so it reads whatever earlier middleware published. An entry written as an import loads on its first call.

./jobs and ./cli export the same models() for a job dispatcher and an @sdxc/cli command line, publishing under the same Models key:

import { createCLI, failure, success } from "@sdxc/cli";
import { models } from "@sdxc/data-model/cli";
import { database } from "@sdxc/database-middleware/cli";

let cli = createCLI({
	name: "app",
	commands: contract,
	middleware: [database(db), models(registry, (ctx) => ({ db: ctx.db }))],
});

cli.map(contract.users.find, async (ctx) => {
	let user = await ctx.models.users.findByEmail(ctx.arguments.email);
	if (user === null) return failure(new Error(`No user ${ctx.arguments.email}`));
	ctx.stdout(`${user.id}\t${user.name}\n`);
	return success();
});

Reach services and defer side effects

Callbacks receive a ModelContext and methods read it as this.context: db, models (the rest of the registry, bound alongside) and get(key), which reads the host context. Add whatever else your models need to the interface, and supply it wherever you bind:

import type { BoundModels } from "@sdxc/data-model";
import type { JobEnqueuer } from "@sdxc/jobs";

interface AppModels {
	users: typeof Users;
	teams: typeof Teams;
}

declare module "@sdxc/data-model" {
	interface ModelContext {
		jobs: JobEnqueuer;
		models: BoundModels<AppModels>;
	}
}

export const Users = createModel(users, {
	methods: {
		async signUp(email: string, name: string) {
			let user = await this.create({ email, name });
			if (isFailure(user)) return user;
			return this.context.models.teams.create({ owner_id: user.data.id, name: "Personal" });
		},
	},
	callbacks: {
		async afterCommit(event, ctx) {
			if (event.operation === "create")
				await ctx.jobs.enqueue(sendWelcome, { userId: event.row.id });
		},
	},
});

router.use(models(registry, (ctx) => ({ db: ctx.db, jobs: ctx.jobs })));

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

Inside transaction, afterCommit events queue and flush once the callback resolves with anything but a Failure; a throw or a returned Failure drops them. Writes roll back only where the database has real transactions, so on D1 the user above stays written when the team fails, and no welcome job goes out for it.

Type models from an interface listing the entries rather than from typeof registry, so a method calling a sibling leaves the rest of the registry's type alone.

API

createModel(table, options?)

Defines a model over a data-table table. Every option is optional:

  • scopes: named refinements (query, ...args) => query, callable on the bound model and on every query from it. A scope may call where, having, orderBy, groupBy, limit, offset and distinct; with() and select() change the row type, so they belong in methods.

  • defaultScope: a scope every read applies, including the row an update or delete targets. unscoped() reads without it.

  • methods: custom methods with this bound to the model. Each answers a promise or a model query; a method answering a plain value, or a method or scope named like a built-in member (see ReservedName), turns the definition into an InvalidModel naming it.

  • callbacks: validate, beforeCreate, beforeUpdate, beforeDelete, afterCreate, afterUpdate, afterDelete and afterCommit, all async and all receiving the ModelContext. validate and before* fail with fail(...) from remix/data-table; before* may return rewritten values.

  • constraints: columns pinned to fixed values. Every read filters on them, every create writes them, and an update or delete of a row outside them answers NotFound.

  • inheritance: the discriminator column extend() pins sub-models on.

  • optional: columns create may leave out because a callback or the database fills them. Nullable columns and created_at/updated_at are optional already.

  • metaTable and meta: a key/value companion table and the typed fields read from it.

withContext<Context>()

Answers { createModel } typed with Context in place of ModelContext, for an app binding one registry per database. Declare Context as an interface extending ModelContext; an intersection type resolves eagerly and makes the models circular.

interface FeedContext extends ModelContext {
	models: BoundModels<FeedModels>;
}

export const { createModel } = withContext<FeedContext>();

definition.bind(context, host?, options?)

Binds a model for a script, a seed or a test. context holds db and every member the app added to ModelContext; host is what ctx.get reads through to, such as a RequestContext; options.transactions is "database" for an adapter with real transactions and "none", the default, for D1 and Durable Object SQLite.

definition.extend(value, options)

A sub-model pinned to one value of the base's inheritance column, with every scope, method and callback of the base; its own callbacks run after the base's. A scope or method named like one of the base's is a type error.

Bound model

MemberAnswers
query(), scopesA data-table Query with the model's scopes chainable
unscoped()The same, without the default scope
withMeta(keys)A query loading only those meta keys
whereMeta(key, v)A query keeping rows holding v under the key
from(query)Another package's query, with constraints and scopes added
find(key)Row | null
findBy(where)Row | null
create(values)Result<Row, ValidationError>
update(key, values)Result<Row, ValidationError | NotFound>
upsert(values, opts)Result<Row, ValidationError>, in one statement
delete(key)Result<Row, ValidationError | NotFound>
transaction(fn)Whatever fn returns, with afterCommit deferred
dbThe database it is bound to, for a method's raw statement
contextThe model context
load()The bound model once a lazily loaded module has loaded

A single key is the column's value, a composite key an object; a key of any other shape, such as a list, answers null from find and NotFound from update and delete. Every update sets the table's updatedAt column, so update(key, {}) marks a row as changed and runs the update callbacks.

upsert(values, { conflict, update }) is one INSERT … ON CONFLICT statement, so it runs no callbacks and supplies its own id. conflict defaults to the primary key; update defaults to the values without the conflict and key columns and createdAt, and update: {} keeps a conflicting row as it is. A model with constraints reads the conflict target first, so a conflicting row outside them is refused and left as it is.

A bulk write built from a query, users.inTeam(teamId).update({ ... }, { touch: false }), is data-table's own statement and runs no callbacks; touch: false leaves updatedAt as it is and returning answers the updated rows.

createModels(entries)

A registry keyed by the name each model is bound under. An entry is a model or a function importing a module that default-exports one. registry.bind(context, host?, options?) binds every entry, and the bound registry's transaction(fn) opens a unit of work across them.

field

Meta field codecs: text, integer, number, boolean, timestamp, url (http and https only, so a stored value is safe to link to), enum(values), json(schema) and list(field). Every field reads as T | undefined unless it declares .default(value); .required() makes create refuse a write without it.

models(registry, context, options?) from ./router, ./jobs and ./cli

Middleware publishing the registry bound to each request, job run or command run as ctx.models (or options.property) and under the Models context key. options.transactions is passed to the binding.

NotFound

The failure an update or delete answers when no visible row has the key, with model and key.

ConstraintViolation

The ValidationError a write answers when the database refuses it, with kind ("unique", "foreign-key", "not-null" or "check") and the column it covers.

./testing

  • defineFactory(model, attributes, options?): a factory whose attributes are values or functions of { sample, sequence, create }; .trait(name, attributes) adds a named set.

  • createFactories(models, { seed, now? }): build, create and createMany for a bound registry, writing through the models so callbacks and constraints behave as in production.

  • overrideModel(model, build): replaces members in every binding until the returned function runs; build receives the original members, so a replacement can wrap them.

  • publishModels(ctx, registry, context, options?): binds a registry with ctx as its host and publishes it the way the middleware does, for a test calling a handler directly.

Types

BoundModel<M>, ModelQuery<M>, ModelRow<M>, CreateValues<M> and UpdateValues<M> name a model's bound type, query, row and write inputs from typeof Model. AnyModel<Shape> constrains a helper to models whose rows have Shape. ModelContext is what callbacks receive. BoundModels<Entries> names a bound registry from an interface of its entries, and BoundRegistry<typeof registry> from the registry itself. ReservedName lists the member names a scope or method cannot take.

Pattern: Scopes Over A Soft-Deleted Table

A default scope hides archived rows from every read, including the row an update or delete targets; named scopes take arguments and chain in any order with data-table's own methods.

import { createModel } from "@sdxc/data-model";
import { lte } from "remix/data-table";

import { tasks } from "./schema.js";

export const Tasks = createModel(tasks, {
	defaultScope: (query) => query.where({ archived_at: null }),
	scopes: {
		assignedTo: (query, userId: string) => query.where({ assignee_id: userId }),
		open: (query) => query.where({ completed_at: null }),
		dueBy: (query, instant: string) => query.where(lte("due_at", instant)).orderBy("due_at", "asc"),
	},
	methods: {
		agenda(userId: string, until: string) {
			return this.assignedTo(userId).open().dueBy(until);
		},
		async restore(id: string) {
			return this.unscoped().where({ id }).update({ archived_at: null });
		},
	},
});

let tasks = Tasks.bind({ db });
await tasks.agenda(userId, today).limit(10).all(); // archived tasks never appear
await tasks.open().count(); // nor here
await tasks.update(archivedId, { title: "x" }); // NotFound: the default scope hides it
await tasks.restore(archivedId); // a bulk write through unscoped() reaches it

A scope refines a query and answers it, so it may call where, having, orderBy, groupBy, limit, offset and distinct; anything that changes the row type belongs in a method.

Pattern: Custom Methods That Answer Queries, Results Or Raw Rows

this is the bound model, so a method composes its scopes, its queries, its siblings through this.context.models and, for what the builder cannot express, a raw statement on this.db.

import { createModel } from "@sdxc/data-model";
import { isFailure } from "@sdxc/result";
import { sql } from "remix/data-table";

import { bookmarks } from "./schema.js";

export const Bookmarks = createModel(bookmarks, {
	optional: ["id"],
	methods: {
		onHost(host: string) {
			return this.query().where({ host }).orderBy("created_at", "desc");
		},
		async save(userId: string, url: string) {
			let existing = await this.findBy({ user_id: userId, url });
			if (existing !== null) return existing;
			let created = await this.create({ user_id: userId, url, host: new URL(url).host });
			if (isFailure(created)) throw created.error;
			await this.context.models.activity.create({ user_id: userId, kind: "bookmarked" });
			return created.data;
		},
		async topHosts(limit: number) {
			return this.db.exec(sql`
				select host, count(*) as bookmarks from bookmarks
				group by host order by bookmarks desc limit ${limit}
			`);
		},
	},
});

await ctx.models.bookmarks.onHost("example.com").limit(5).all(); // a query, still chainable
await ctx.models.bookmarks.save(userId, "https://example.com/a"); // a promise of a row

A method answers a promise or a model query, which is what lets a lazily loaded entry call it before its module has loaded; a method named like a built-in member is a type error.

Pattern: Every Callback Around A Write

Callbacks run in this order: validate, then before*, then the statement, then after*, and afterCommit once the enclosing unit of work succeeds. validate and every before* and after* callback may refuse with fail(...); a refusal after the statement rolls it back where the database has real transactions.

import { createModel } from "@sdxc/data-model";
import { isFailure } from "@sdxc/result";
import { generateUUID } from "@sdxc/uuid/v4";
import { fail } from "remix/data-table";

import { documents } from "./schema.js";
import { publishDocument } from "./jobs.js";
import { slugify } from "./slugify.js";

export const Documents = createModel(documents, {
	optional: ["id", "slug", "version"],
	callbacks: {
		async validate(values) {
			if (values.title !== undefined && values.title.trim() === "") {
				return fail("Title is required", ["title"]);
			}
		},
		async beforeCreate(values) {
			return { ...values, id: generateUUID(), slug: slugify(values.title), version: 1 };
		},
		async beforeUpdate(values, _ctx, before) {
			return { ...values, version: before.version + 1 };
		},
		async beforeDelete(row) {
			if (row.published_at !== null) return fail("Unpublish the document first");
		},
		async afterCreate(row, ctx) {
			let revision = await ctx.models.revisions.create({ document_id: row.id, body: row.body });
			if (isFailure(revision)) return fail("Could not record the first revision");
		},
		async afterUpdate(row, ctx, before) {
			if (row.body === before.body) return;
			await ctx.models.revisions.create({ document_id: row.id, body: row.body });
		},
		async afterDelete(row, ctx) {
			await ctx.models.revisions.query().where({ document_id: row.id }).delete();
		},
		async afterCommit(event, ctx) {
			if (event.operation === "update" && event.changed.includes("published_at")) {
				await ctx.jobs.enqueue(publishDocument, { id: event.row.id });
			}
		},
	},
});

before* callbacks see the previous one's rewrite and the row as it stands; afterCommit receives the row, the row before the write and the columns and meta.<key> entries that changed, so a side effect fires only for the change it cares about.

Pattern: Pin A Model To One Slice Of A Table

constraints fixes columns to values: every read filters on them, every create writes them, and an update or delete of a row outside them answers NotFound. Two models over one table then never see each other's rows.

import { createModel } from "@sdxc/data-model";
import { fail } from "remix/data-table";

import { channels } from "./schema.js";

export const EmailChannels = createModel(channels, {
	constraints: { kind: "email" },
	optional: ["id"],
	callbacks: {
		async validate(values) {
			if (values.address !== undefined && !values.address.includes("@")) {
				return fail("Expected an email address", ["address"]);
			}
		},
	},
});

export const WebhookChannels = createModel(channels, {
	constraints: { kind: "webhook" },
	optional: ["id"],
});

let emails = EmailChannels.bind({ db });
await emails.create({ team_id, address: "ops@example.com" }); // written with kind: "email"
await emails.query().where({ team_id }).count(); // email channels only
await emails.update(webhookId, { address: "x" }); // NotFound
await emails.upsert({ id: webhookId, team_id, address: "x" }); // refused, the webhook row is left as it is

A constrained column is never writable: create and update drop it from the values, and unscoped() lifts the default scope but keeps the constraints. inheritance plus extend() is the same mechanism with the discriminator and the base's scopes and callbacks inherited.

Pattern: Open-Ended Attributes As Typed Meta Fields

A key/value companion table holds attributes the main table does not have columns for; the model declares each key once as a field, so rows read and write them as typed values under meta.

import { createModel, field } from "@sdxc/data-model";
import { generateUUID } from "@sdxc/uuid/v4";
import { object, string } from "remix/data-schema";

import { userMeta, users } from "./schema.js";

export const Profiles = createModel(users, {
	metaTable: { table: userMeta, foreignKey: "user_id", generateId: () => generateUUID() },
	meta: {
		locale: field.enum(["en", "es"]).default("en"),
		website: field.url(),
		links: field.list(field.url()),
		newsletter: field.boolean().default(false),
		theme: field.json(object({ accent: string() })),
		bio: field.text(),
	},
});

let profiles = Profiles.bind({ db });
let profile = await profiles.create({ email, name, meta: { locale: "es", links: [a, b] } });
profile.data.meta; // { locale: "es", website: undefined, links: [a, b], newsletter: false, ... }

await profiles.update(id, { meta: { website: "https://example.com", links: null } }); // null removes a key
await profiles.withMeta(["locale"]).where({ id }).first(); // loads one key, typed as such
await profiles.whereMeta("links", a).all(); // any item of a list field matches
await profiles.whereMeta("newsletter", true).count(); // one statement, joined on the meta table

Every field reads as T | undefined unless it declares .default(value); .required() makes create refuse a write without it, and update refuse null for it. A stored value the codec rejects reads as missing, then as the default. generateId supplies the meta rows' primary key when the table's database does not.

Pattern: Sub-Models On A Discriminator

One table holds every post type; inheritance names the discriminator and each extend() pins one value, inheriting the base's scopes and callbacks and adding its own meta fields.

import { createModel, field } from "@sdxc/data-model";

import { postMeta, posts } from "./schema.js";

export const Posts = createModel(posts, {
	inheritance: "type",
	metaTable: { table: postMeta, foreignKey: "post_id" },
	scopes: { live: (query) => query.where({ deleted_at: null }) },
});

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

let articles = Articles.bind({ db });
let article = await articles.create({ author_id, meta: { slug: "hello", tags: ["remix"] } });
article.data.type; // "article"
article.data.meta.locale; // "en"

An update's meta touches only the keys it names, and null removes one. whereMeta joins the meta table into the read; each further whereMeta on the same query resolves its owners first, so chain the broad key first and selective ones after it.

Pattern: Helpers Over Any Model

import type { AnyModel, BoundModel, ModelRow } from "@sdxc/data-model";

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

await forPost(ctx.models.comments, id); // comments rows

A model whose rows lack post_id: string fails to type-check as the argument.

Pattern: Paging A Scope

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

let page = await Pagination.byOffset(ctx.models.articles.live().orderBy("published_at", "desc"), {
	page: 1,
	perPage: 20,
});

Pagination.byKeyset owns the ordering, so hand it a scope that only filters.

Pattern: Factories In A Test

import { createModels } from "@sdxc/data-model";
import { createFactories, defineFactory } from "@sdxc/data-model/testing";

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

let models = createModels({ users: Users }).bind({ db });
let factories = createFactories(models, { seed: 42 });

let admin = await factories.create(UserFactory, "admin");
await factories.createMany(UserFactory, 3);

Each factory draws from its own stream derived from the seed, so adding a factory leaves every other factory's values unchanged.

Written by Sergio Xalambrí. Follow @sergiodxa for new packages, or sponsor the work.