Persistence

Persistence is a small Active Record-inspired TypeScript layer over Knex for PostgreSQL. The current MVP runtime includes generated schema consumption, hydrated reads, declared relations, relation-aware joins, lifecycle-aware saves, explicit transactions, commit callbacks, and declared-effect state machines.

State Machines With A Declared Effect Graph

The headline differentiator: an AASM-inspired state machine where every transition declares its entire effect graph — from/to, guard, typed zod payload, and callbacks in the three lifecycle tiers — in one visible block. No TypeScript equivalent exists integrated with a persistence lifecycle. Transitions compile to a compare-and-swap write through the full save lifecycle, so concurrent transitions throw StaleTransitionError instead of corrupting state, and the governed column narrows to its literal state union (order.status is "pending" | "confirmed" | ..., and order.confirm() / order.canConfirm() are injected and fully typed). See docs/state-machines.md.

Current Scope And Limits

Persistence aims for Rails Active Record ergonomics in TypeScript, but the current runtime is a focused MVP rather than a full ORM or a complete Active Record port.

• PostgreSQL is the only supported database, and a migrated database remains the source of truth.
• Generated schema artifacts provide runtime shape validation; models use database column names directly.
• The default query chain covers common typed reads, scalar terminals, declared relation joins, SQL inspection, safe comments, and PostgreSQL explain plans. Use query() for raw Knex joins, custom aggregates, vendor-specific SQL, and caller-shaped projections.
• Relations are manually declared with hasMany, hasOne, belongsTo, and hasManyThrough for explicit join-model reads. Unloaded association queries support common scalar reads, reloads, and direct hasMany/hasOne build and create helpers. Polymorphic relations, nested relation paths, relation inference, and through writes are not implemented yet.
• Lifecycle-aware writes are create, save, assignAndSave, findOrCreateBy, updateOrCreateBy, association create, touch, and reload. Models can opt into optimistic locking with a conventional lock_version column. Direct SQL insertAll, upsert, update, and delete skip lifecycle behavior, and lifecycle-aware destroy, bulk create, and nested relational writes are out of scope.
• State machines govern status-like columns with declared states, typed transition methods, advisory can* predicates, and compare-and-swap transition writes; non-transition writes stay possible and are documented as skipping transition effects.
• Test factories are available from @dannyfuf/persistence/factory for deterministic attrs, build, stub, create, lists, lifecycle hooks, and direct relation graph setup in tests.

See docs/mvp-boundaries.md for the canonical list of current limits.

Test Factories

Define factories as classes and import the test API from the ./factory subpath:

import {
  Factory,
  FactoryDefinition,
  Trait,
  type FactoryContext,
} from "@dannyfuf/persistence/factory";

class UserFactory extends FactoryDefinition<typeof UserRecord> {
  static model = UserRecord;

  override attrs(ctx: FactoryContext<typeof UserRecord>) {
    return {
      email: ctx.sequence("users.email", (value) => `user${value}@example.test`),
      external_id: ctx.fake.uuid(),
    };
  }

  @Trait()
  inactive() {
    return { active: false };
  }
}

const attrs = Factory.attrs(UserFactory, "inactive");
const user = await Factory.create(UserFactory, {
  overrides: { name: "Danny" },
});

Use Factory.reset() between tests to rewind deterministic sequences, fake data, and generated ids without clearing definitions. Use Factory.lint() in a setup test or preflight step to catch invalid factory classes and trait combinations; opt into strategies: ["create"] when a configured PostgreSQL test database is available and persisted graph linting should be rolled back automatically.

Vitest and Jest use the same explicit setup shape:

import { beforeEach } from "vitest";
import { Factory } from "@dannyfuf/persistence/factory";

beforeEach(() => {
  Factory.reset();
});

See docs/factory-api.md for the full contract, including belongsTo, hasOne, hasMany, lifecycle hook semantics, lint diagnostics, and runner-neutral setup guidance.

Installation

npm install @dannyfuf/persistence knex pg zod
pnpm add @dannyfuf/persistence knex pg zod
bun add @dannyfuf/persistence knex pg zod

Agent Skill

Install the persistence-orm agent skill into ~/.agents/skills with one command:

curl -fsSL https://raw.githubusercontent.com/dannyfuf/persistence/main/scripts/install-persistence-orm-skill.sh | sh

If curl is unavailable, use wget:

wget -qO- https://raw.githubusercontent.com/dannyfuf/persistence/main/scripts/install-persistence-orm-skill.sh | sh

From a local clone, the same installer is available as:

deno task skill:install

Restart opencode after installing so it discovers the external skill.

Development Setup

Deno is the runtime and package manager for the development workflow (publishing stays on the npm registry):

deno install
docker compose up -d postgres
deno task lint
deno task typecheck
deno task test
deno task test:integration
deno task schema:check
deno task build

If Docker is provided by OrbStack and the shim is not on your shell PATH, add ~/.orbstack/bin before running Docker commands.

Configuration

Create a project-level persistence.config.mjs:

export default {
  knex: {
    client: "pg",
    connection: process.env.DATABASE_URL,
  },
  schema: {
    outputPath: "src/generated/persistenceSchema.ts",
    databaseSchemas: ["public"],
  },
};

The knex field can also be a path to a module that exports a Knex config.

Schema Commands

Run migrations through Persistence to update the database and regenerate the schema artifact in one step:

deno task cli migrate:latest

The command runs knex.migrate.latest() from your Persistence config and only generates schemas after migrations succeed. If migrations fail, the generated schema file is left untouched.

You can also run schema generation commands directly for CI and debugging. The migrated PostgreSQL database is still the source of truth.

deno task cli schema:generate
deno task cli schema:check

schema:generate writes the generated artifact. schema:check regenerates it in memory and exits non-zero when the checked-in file is missing or stale.

See docs/schema-generation.md for the generated file contract and PostgreSQL support notes. See docs/mvp-boundaries.md for the deliberate MVP limits.

Verification

The local CI equivalent is:

deno task verify

deno task verify runs lint, type-checking, unit tests, PostgreSQL integration and e2e tests, schema check, and build. The repository-level deno task schema:check prepares the PostgreSQL fixture schema before checking test/fixtures/generated/phase1-schema.ts.

Models And Reads

Configure the runtime with your Knex connection and generated schema module:

import {
  Model,
  belongsTo,
  defineModel,
  hasMany,
  hasManyThrough,
  type ModelInstanceFor,
} from "@dannyfuf/persistence";
import { knex } from "./db.js";
import { persistenceSchema } from "./generated/persistenceSchema.js";

Model.configure({ connection: knex, schema: persistenceSchema });

class UserRecord extends Model<"users"> {
  static tableName = "users" as const;
  static optimisticLocking = true;
  static posts = hasMany(() => PostRecord, { foreignKey: "user_id" });
}

class PostRecord extends Model<"posts"> {
  static tableName = "posts" as const;
  static user = belongsTo(() => UserRecord, { foreignKey: "user_id" });
  static tags = hasManyThrough(() => TagRecord, {
    through: () => PostTagRecord,
    sourceForeignKey: "post_id",
    targetForeignKey: "tag_id",
  });
}

class TagRecord extends Model<"tags"> {
  static tableName = "tags" as const;
}

class PostTagRecord extends Model<"post_tags"> {
  static tableName = "post_tags" as const;
}

export const User = defineModel(UserRecord);
export const Post = defineModel(PostRecord);
export const Tag = defineModel(TagRecord);
export const PostTag = defineModel(PostTagRecord);
export type User = ModelInstanceFor<typeof UserRecord>;
export type Post = ModelInstanceFor<typeof PostRecord>;

Read APIs hydrate rows into model instances with direct database-column accessors:

const user = await User().find(1);
const activeUsers = await User().where({ active: true }).orderBy("email").all();
const page = await User().orderBy("created_at", "desc").limit(25).offset(50).all();
const activeCount = await User().where({ active: true }).count();
const activeEmails = await User().distinct("email").pluck("email");
const visibleIds = await User()
  .whereNot({ active: false })
  .orWhere({ email: "[email protected]" })
  .ids();

Query inspection is first-class for read-shaped chains:

const inspected = User().comment("report:active-users").where({ active: true }).toSQL();

console.log(inspected.sql, inspected.bindings);

const planRows = await User().where({ active: true }).explain();

toSQL() returns Knex's SQL inspection object and does not execute the query. explain() runs plain PostgreSQL EXPLAIN without ANALYZE and returns the raw plan rows, usually with a QUERY PLAN column. Comments must be non-empty static labels and cannot contain SQL comment delimiters, null bytes, or Knex binding markers; do not put secrets or personally identifiable information in comments.

Safe select and orderBy calls are typed to declared generated columns, *, and the base table wildcard such as users.*. pluck, ids, and distinct use declared base-table columns. count, exists, pluck, and ids return scalar values without hydrating model instances. Arbitrary SQL selectors belong in query().

Nullable reads return null; findOrThrow, findByOrThrow, and firstOrThrow throw RecordNotFoundError.

Model classes can declare relations with hasMany, belongsTo, hasOne, and hasManyThrough. Use relation joins when the relation is needed for filtering or ordering, and use with when returned model instances should contain hydrated relation values.

// Inner relation join. Returns User instances; posts is still an association query.
const usersWithPublishedPosts = await User()
  .join("posts")
  .where({ posts: { published: true } })
  .orderBy("posts.created_at", "desc")
  .all();

// Left relation join. Keeps users even when no post row matches.
const usersMaybeWithPosts = await User().leftJoin("posts").all();

// Explicit loading. Returns users whose posts property is Post[].
const usersWithPosts = await User().with("posts").all();
const loadedPosts = usersWithPosts[0].posts;

// Through relation loading. Returns posts whose tags property is Tag[].
const postsWithTags = await Post().with("tags").all();
const loadedTags = postsWithTags[0].tags;

// Unloaded relation properties are query objects.
const user = await User().findOrThrow(1);
const queriedPosts = await user.posts.where({ published: true }).all();
const postCount = await user.posts.count();
const hasDrafts = await user.posts.where({ published: false }).exists();
const newPost = user.posts.build({ title: "Draft" });
const savedPost = await user.posts.create({ title: "Published", published: true });
const reloadedPosts = await user.posts.reload();

Relation support is deliberately one-hop. Use raw query() for SQL shapes that need polymorphic relations, nested paths, repeated aliases, relation-aware direct writes, custom projections with hydrated relation reads, or many-to-many writes. Association build and create are supported for direct hasMany and hasOne relations; belongsTo and hasManyThrough writes are intentionally rejected.

Use query() for raw Knex joins, custom aggregates, and custom projections:

const rows = await User()
  .query()
  .join("posts", "posts.user_id", "users.id")
  .select({ userId: "users.id", postTitle: "posts.title" })
  .rows<{ userId: number; postTitle: string }>();

Direct SQL operations execute without lifecycle behavior:

const inserted = await User().insertAll([
  {
    email: "[email protected]",
    external_id: "00000000-0000-4000-8000-000000000001",
  },
]);

const rows = await User().insertAll(
  [
    {
      email: "[email protected]",
      external_id: "00000000-0000-4000-8000-000000000002",
    },
  ],
  { returning: ["id", "email"] },
);

await User().where({ active: false }).update({ active: true });
await User().where({ id }).delete();

const upserted = await User().upsert(
  {
    email: "[email protected]",
    external_id: "00000000-0000-4000-8000-000000000003",
  },
  { conflict: "email", update: { name: "Direct Upsert" } },
);

insertAll validates every payload with the generated insert schema and returns the inserted row count by default. { returning: "*" } returns plain database rows, and { returning: ["id", "email"] } returns selected-column objects. Direct inserts do not hydrate model instances and do not apply Persistence-managed timestamps; database defaults still apply.

upsert validates the insert shape, validates the explicit or derived update shape, executes PostgreSQL ON CONFLICT, and returns the RETURNING * row as a hydrated model instance. Direct SQL writes skip model validation, callbacks, commit callbacks, optimistic locking, and automatic timestamp mutation.

See docs/models-and-queries.md for callable repositories, build, custom repository methods, transaction-bound repositories, relation queries, explicit with loading, association queries, and advanced query() examples. See docs/relations.md for the full relation and join guide. See docs/api.md for the supported public exports.

Saves And Transactions

Use create, assign, save, assignAndSave, findOrCreateBy, and updateOrCreateBy for lifecycle-aware writes:

const user = await User().create({
  email: "[email protected]",
  external_id: "00000000-0000-4000-8000-000000000001",
});

await user.assignAndSave({ name: "Alex" });
await user.reload();
await user.touch();

user.changedAttributes();
user.changes();
user.savedChanges();
user.previousChanges();

const existingOrCreated = await User().findOrCreateBy(
  { email: "[email protected]" },
  { external_id: "00000000-0000-4000-8000-000000000001" },
);

const updatedOrCreated = await User().updateOrCreateBy(
  { email: "[email protected]" },
  { name: "Alex" },
  { external_id: "00000000-0000-4000-8000-000000000001" },
);

To protect instance saves from lost updates, add an integer lock_version column to the table and set static optimisticLocking = true on the model. Creates set lock_version to 0 when omitted. Updates include the original lock_version in the WHERE predicate, increment it on success, and throw StaleRecordError when another writer changed or deleted the row first. Clean no-op saves do not increment the lock version.

Use Model.transaction and pass tx explicitly to repositories that should participate in the same transaction:

await Model.transaction(async (tx) => {
  const user = await User(tx).findOrThrow(1);
  await user.assignAndSave({ active: false });
});

See docs/lifecycle.md for validation, timestamps, dirty tracking, and callback order. See docs/transactions.md for transaction-bound instances, nested transaction squashing, and commit callback failure behavior.

Callbacks

Persistence ships eight lifecycle hooks in three tiers: synchronous before-hooks for row shaping, BeforeCommit for atomic dependent writes, and the AfterCommit family for external effects (see docs/lifecycle.md). Callbacks are static methods decorated on the record class:

import { BeforeSave, AfterCommit } from "@dannyfuf/persistence";

class UserRecord extends Model<"users"> {
  static tableName = "users" as const;

  @BeforeSave
  static normalizeEmail(user: User) {
    user.email = user.email.toLowerCase();
  }

  @AfterCommit
  static publishChange(user: User) {
    console.log("committed", user.id);
  }
}

Example

See examples/basic for a minimal PostgreSQL project with a Knex migration, Persistence config, generated-schema command, model definition, and modular examples for reads, relation joins, explicit relation loading, association queries, raw projections, lifecycle saves, transactions, and direct bulk operations.