Stores

A store is a collection of methods for accessing data. Stores can be backed by APIs, filesystems, caches, or other sources.

The most common store is the DatabaseStore, a collection of records with a schema and a common interface, backed by a relational or document database. This page focuses on database stores, the most widely used.

Mapping

Driver	Maps to	Examples
SQL	table	SQLite, MySQL, PostgreSQL, Oracle
Document/NoSQL	collection	JSONDB, MongoDB

Stores have:

Schema — field names and types (Pema)
API — type-safe CRUD and queries
Driver — the database connection to use

Define a store

Create a file under stores and export a store. Every store requires three fields: table, db, and schema.

Stores may also define migrate, which defaults to true. Set migrate: false for stores that should not participate in migration generation.

// stores/Post.ts
import p from "pema";
import store from "primate/store";
import db from "@/config/db";

export default store({
  table: "post",
  db,
  schema: {
    id: store.key.primary(p.u32),
    title: p.string.max(100),
    body: p.string,
    created: p.date.now(),
  },
});

Passing an incorrect table, db, or schema throws immediately at construction time.

Database stores require a primary key field. Pema types (string, number, boolean, date, etc.) enforce validation and map to database columns. See Pema and Stores for the complete Storable compatibility matrix, including wrappers and structured JSON fields.

Portability

Because table and db are declared explicitly in the store file, stores are fully self-contained modules. You can import and use them anywhere — migration scripts, test suites, REPLs, or any other context outside a running Primate app — without any framework initialisation.

// scripts/migrate.ts
import Post from "../stores/Post.ts";

await Post.drop();

Primary keys

Define primary keys with key.primary():

import store from "primate/store";
import p from "pema";

// auto-generated integer PK (default)
id: store.key.primary(p.u32)

// auto-generated UUID PK
id: store.key.primary(p.uuid)

// bigint PK
id: store.key.primary(p.u64)

// manual PK (no auto-generation)
id: store.key.primary(p.u32, { generate: false })

// manual string PK
id: store.key.primary(p.string, { generate: false })

Supported PK types:

Unsigned integers: u8, u16, u32, u64, u128
UUIDs: p.uuid, p.uuid.v4(), p.uuid.v7()
Strings: p.string with { generate: false }

Generated p.uuid.v4() keys use UUIDv4. Generated p.uuid.v7() keys use UUIDv7, as does the generic p.uuid form, which follows Primate's preferred UUID version. This is consistent across database adapters; an explicitly supplied key is validated and preserved instead of regenerated.

Foreign keys

Define foreign keys with key.foreign() to reference other stores:

import store from "primate/store";
import p from "pema";

export default store({
  table: "article",
  db,
  schema: {
    id: store.key.primary(p.u32),
    title: p.string,
    author_id: store.key.foreign(p.u32),  // references User.id
  },
});

Foreign keys should use the same scalar type as the referenced primary key, for example key.foreign(p.u32) or key.foreign(p.uuid).

Insertion type

Every store exposes the type accepted by insert as Store.Insert. Generated and defaulted fields remain optional.

import Post from "@/stores/Post";

type NewPost = typeof Post.Insert;

const draft: NewPost = {
  title: "Primate 0.41",
  body: "...",
};

await Post.insert(draft);

Pass an array to insert several independent records atomically. Primate applies schema validation and defaults to every record before writing, and returns the inserted records in input order with their generated keys.

const posts = await Post.insert([
  { title: "First post", body: "..." },
  { title: "Second post", body: "..." },
]);

An empty array returns an empty array without contacting the database. If validation or a database constraint fails, none of the records are inserted. Primate does not split oversized batches because doing so would weaken that atomicity guarantee. Database statement and parameter limits therefore bound a single batch; divide independent work explicitly when separate atomic batches are acceptable. Primate does not mutate the input array or its records.

MongoDB uses a transaction to provide the same all-or-nothing contract, so array insertion requires a replica-set member or mongos. See the MongoDB adapter notes.

Relations

Define relationships between stores inline in the schema using store.relation.

One-to-many

A user has many articles:

// stores/User.ts
import p from "pema";
import store from "primate/store";
import db from "@/config/db";

export default store({
  table: "user",
  db,
  schema: {
    id: store.key.primary(p.uuid),
    name: p.string,
    articles: store.relation.many({ table: "article", by: "author_id" }),
  },
});

One-to-one

A user has one profile:

// stores/User.ts
import p from "pema";
import store from "primate/store";
import db from "@/config/db";

export default store({
  table: "user",
  db,
  schema: {
    id: store.key.primary(p.uuid),
    name: p.string,
    profile: store.relation.one({ table: "profile", by: "user_id" }),
  },
});

Reverse relations

Query from the many side back to the one side using reverse: true:

// stores/Article.ts
import p from "pema";
import store from "primate/store";
import db from "@/config/db";

export default store({
  table: "article",
  db,
  schema: {
    id: store.key.primary(p.u32),
    title: p.string,
    author_id: store.key.foreign(p.u32),
    author: store.relation.one({ table: "user", by: "author_id", reverse: true }),
  },
});

Loading relations

Use the with option to load related records. Pass the store directly:

import Article from "@/stores/Article";

const users = await User.find({
  with: { articles: Article },
});

Pass a query object with store plus any sub-query options:

import Article from "@/stores/Article";

const users = await User.find({
  where: { active: true },
  with: {
    articles: {
      store: Article,
      select: ["title", "created"],
      sort: { created: "desc" },
      limit: 5,
    },
  },
});

// result:
// [
//   {
//     id: "...",
//     name: "John",
//     articles: [
//       { title: "First Post", created: ... },
//       { title: "Second Post", created: ... },
//     ]
//   }
// ]

Each relation sub-query can specify:

store — the related store (required)
select — fields to include
sort — ordering
where — filter criteria
limit — max records per parent

Primate validates that the store passed in with matches the table declared in the relation. Passing the wrong store throws a relation_table_mismatch error.

Operators

The where option supports operators for non-equality comparisons.

Comparison operators

Available on numbers, bigints, and dates:

const posts = await Post.find({
  where: { views: { $gt: 100 } },
});

Operator	Meaning	Types
`$gt`	greater than	number, bigint
`$gte`	greater than or equal	number, bigint
`$lt`	less than	number, bigint
`$lte`	less than or equal	number, bigint
`$ne`	not equal	number, bigint
`$after`	after	date
`$before`	before	date

String operators

const users = await User.find({
  where: { name: { $like: "Ali%" } },
});

Operator	Meaning
`$like`	case-sensitive pattern match
`$ilike`	case-insensitive pattern match
`$ne`	not equal

Use % as a wildcard and _ as a single-character wildcard.

$in operator

Match records whose field value is in a given list. Works on strings, numbers, bigints, dates, and UUIDs:

const users = await User.find({
  where: { name: { $in: ["John", "Bob", "Larry"] } },
});

const posts = await Post.find({
  where: { status: { $in: ["draft", "published"] } },
});

Passing an empty array throws — an empty $in can never match anything and is almost always a programmer mistake.

Pagination

Limit

Cap the number of results with limit:

const posts = await Post.find({
  sort: { created: "desc" },
  limit: 20,
});

Offset

Skip a number of records with offset. Requires limit to be set:

const page = await Post.find({
  sort: { created: "desc" },
  limit: 20,
  offset: 40,
});

Using offset without limit throws. All six database drivers support offset pagination natively.

First matching record

Use first when a query needs at most one record. It accepts where, select, sort, offset, and with, applies a limit of one internally, and returns undefined when no record matches.

const latest = await Post.first({
  where: { published: true },
  sort: { created: "desc" },
  select: ["id", "title"],
});

Unlike find, first does not accept a limit option.

Configure a database

Install a Primate database driver and create a config/db directory with your database file.

SQLite PostgreSQL MySQL MongoDB

$ npm install @primate/sqlite

$ npm install @primate/postgresql

$ npm install @primate/mysql

$ npm install @primate/mongodb

SQLite PostgreSQL MySQL MongoDBconfig/db.tsconfig/db.tsconfig/db.tsconfig/db.ts

import sqlite from "@primate/sqlite";

export default sqlite({ database: "/tmp/primate.db" });

import postgresql from "@primate/postgresql";

export default postgresql({ database: "primate" });

import mysql from "@primate/mysql";

export default mysql({ database: "primate" });

import mongodb from "@primate/mongodb";

export default mongodb({ database: "primate" });

Name the file freely. For the main driver, use index.ts or default.ts. Import it into your stores as db from @/config/db.

Migrations

Primate migrations are one-time application state transitions. Generated migrations usually update store schemas, but a migration body may run any application upgrade code.

Enable it in config/app.ts by telling Primate which database should track applied migrations and which table to use:

import config from "primate/config";
import db from "@/config/db";

export default config({
  migrations: {
    table: "migration",
    db,
  },
});

The configured database stores the execution ledger; it does not restrict what migration code can do. JSONDB cannot be used as migrations.db because it cannot atomically prevent concurrent processes from executing the same body. Migration create, status, apply, upgrade and production startup reject that configuration before creating or reading a ledger or running a body. JSONDB remains available for ordinary Stores. If migrations is not configured, migrations are disabled.

Once enabled, the workflow is:

npx primate migration create --name "add posts"
npx primate migration status
npx primate migration apply

migration create compares your current stores to the live database and writes a descriptively named file into migrations/. Each migration declares an immutable random revision ID and the revisions on which it depends.

import app from "@/app";

export default app.migrate({
  id: "23f24df1",
  parents: ["a81f3c20"],

  async run(db) {
    await db.schema.alter("post", {
      add: { published: "boolean" },
      drop: [],
      rename: [],
    });
  },
});

IDs are eight lowercase hexadecimal characters generated randomly when the migration is created. They are not hashes and must never be changed after a migration is shared or applied. Filenames are descriptive only.

Concurrent branches and merge migrations

Migrations form a directed acyclic graph rather than one global numbered list. There is normally one root with parents: []. A migration created on a branch records every current graph head as its parents, so two branches can add revisions independently without renaming either file.

After those branches merge, the graph temporarily has two heads. Run migration create after applying both branches. If there is no schema diff, Primate creates a merge-only migration automatically:

import app from "@/app";

export default app.migrate({
  id: "cf57a103",
  parents: ["23f24df1", "9b127ed0"],
});

Pending revisions run in deterministic topological order: every parent is applied before its children, with revision ID ordering used when independent nodes are simultaneously ready. Primate rejects malformed or duplicate IDs, missing parents, cycles, multiple roots, disconnected histories, unknown ledger IDs, and an applied child whose parent is absent.

migration status shows applied heads and pending revisions. migration apply runs each pending declaration once and records its revision ID in the ledger. Before reading or replacing the ledger, status, apply, and the confirmed 0.42 upgrade claim an apply row in a companion <table>__primate_lock table in the configured migration database. An overlapping apply or upgrade fails before running a migration body, snapshotting or replacing the ledger. Ordinary success and errors release the claim, so a failed command does not require lock cleanup. Inspect and address any partial changes made by a failed migration body or conversion before retrying it.

If the process is forcibly terminated, the claim remains to prevent an unsafe automatic retry. First confirm that no migration process is still running and inspect or restore any partially applied changes. For an interrupted upgrade, also retain and inspect .primate-042-upgrade.json and its saved ledger snapshot. Only then remove the apply row from <table>__primate_lock and rerun the same command. Never remove a claim held by a live process.

For production, primate build emits a self-contained migration entrypoint alongside the server. Run it before starting the server:

node build/migrate.js status --json
node build/migrate.js apply --json
node build/server.js

The entrypoint contains the exact configuration, database adapter, and migration graph bundled with that server build. It does not need the source tree or the Primate CLI. status exits with code 0 when clean, 2 when migrations are pending, and 1 for an invalid graph, inconsistent ledger, invalid command, or operational error. apply exits with code 0 on success and 1 on failure.

Explicitly applying migrations before startup is the recommended workflow. Primate does not apply migrations automatically when the server starts.

Do not start the new server if migration application fails. Running apply again is safe when the database is already current. A built app validates its exact bundled graph and exits before listening if revisions are pending or its ledger is inconsistent.

Upgrading from 0.42

0.43 moves configuration from db.migrations to top-level migrations, changes the ledger ID from a number to a string, and requires every migration to export an app.migrate() declaration. Existing numbered filenames may remain as they are; do not rename them or rewrite the state transition in their bodies.

There is no automatic 0.43 upgrade or transfer path for a 0.42 migration ledger stored in JSONDB. Remove the migration configuration to continue using JSONDB without migrations, or remain on 0.42 while adopting a separately verified ledger strategy. Do not point 0.43 at an empty ledger in another database: without the completed revision history, previously executed bodies appear pending and may run again.

First, back up the application database and move the configuration:

export default config({
  migrations: { table: "migration", db },
});

Then prepare a stable conversion plan:

npx primate migration upgrade

Primate writes migrations/.primate-042-upgrade.json and prints each legacy filename, its generated revision ID, and its predecessor ID. Re-running preparation reuses this file; it never silently generates a different mapping. The entries mapping is stable and may be carried through development, staging and production. Snapshot, recovery and completion fields describe the most recent target only; the command never treats them as proof that another database or restored backup has been converted.

Wrap each existing migration body with the corresponding declaration. For example, convert:

export default async db => {
  // existing body
};

to:

import app from "@/app";

export default app.migrate({
  id: "23f24df1",
  parents: ["a81f3c20"],
  async run(db) {
    // unchanged existing body
  },
});

Run npx primate migration upgrade again. It validates every edited declaration against the saved map without changing the ledger. After taking a fresh backup, perform the confirmed conversion:

npx primate migration upgrade --confirm

Before replacing the numeric ledger table, Primate records its complete mapped contents in the plan file. It recreates the ledger with string revision IDs, restores the applied timestamps, and verifies the result without running any migration body. If the process is interrupted, keep the plan file and rerun the confirmed command; use its ledger snapshot together with the database backup for recovery. Do not delete the plan until the upgraded application has been verified.

While this recovery is unresolved, development and production status, apply, and application startup reject the partial ledger. They do not report it as pending or execute migration bodies. Only a confirmed upgrade whose plan matches the database recovery marker may resume. After it restores and verifies the complete ledger, Primate clears the marker and ordinary status, apply, and startup checks can proceed.

Run confirmed conversion separately against each environment and take a fresh backup there. Primate validates the live schema and ledger every time. A live numeric ledger replaces stale environment-specific snapshot state while keeping the assigned revision IDs, so different applied prefixes and timestamps remain local to each database. An absent or empty ledger is not populated from a plan's old snapshot unless that database contains the matching durable recovery marker written before replacement. Restoring a numeric backup is therefore detected and converted again rather than being hidden by completed: true in the file.

Stop every application and 0.42 migration process that can write the legacy ledger before confirmed conversion, and keep them stopped until the upgraded application is ready. Primate 0.42 does not know about the 0.43 companion lock, so the lock can serialize 0.43 apply and upgrade commands but cannot exclude a legacy writer. Run only one confirmed conversion at a time. If conversion is forcibly interrupted, follow the lock-recovery procedure above before retrying.

Lifecycle

Create tables or collections at app startup, e.g. in a route file.

import Post from "@/stores/Post";

await Post.create();

// ... later (e.g., tests/teardown)
// await Post.drop();

You can safely call create() multiple times; drivers treat it as idempotent. Good for prototyping. In production, prefer migrations to manage schema changes ahead of time.

Usage in routes

A typical route that reads (or creates) a record, then renders a route page:

// routes/posts/index.ts
import Post from "@/stores/Post";
import p from "pema";
import response from "primate/response";
import route from "primate/route";

export default route({
  async get() {
      const posts = await Post.find({
        where: {},
        sort: { created: "desc" },
        select: ["id", "title", "created"],
        limit: 20,
      });

      return response.page({ posts });
  },
  async post(request) {
      const body = p({
        title: p.string.max(100),
        body: p.string,
      }).parse(await request.body.form());

      const created = await Post.insert(body);

      return response.page({ post: created });
  },
});

Extending stores

Add custom methods to stores with .extend(). Extensions can be defined inline or in separate files for modularity.

// stores/User.ts
import p from "pema";
import store from "primate/store";
import db from "@/config/db";

export default store({
  table: "user",
  db,
  schema: {
    id: store.key.primary(p.u32),
    name: p.string,
    age: p.u8.range(0, 120),
    lastname: p.string.optional(),
  },
}).extend(User => ({
  findByAge(age: typeof User.Schema.age) {
    return User.find({ where: { age } });
  },
  async getAverageAge() {
    const users = await User.find({ where: {} });
    return users.reduce((sum, u) => sum + u.age, 0) / users.length;
  },
}));

Extended stores are excluded from migration generation automatically; migrations operate on the base store definitions.

Modular extensions

Create a base store:

// stores/User.ts
import p from "pema";
import store from "primate/store";
import db from "@/config/db";

export default store({
  table: "user",
  db,
  schema: {
    id: store.key.primary(p.u32),
    name: p.string,
    age: p.u8.range(0, 120),
    lastname: p.string.optional(),
  },
});

Create an extended version:

import Base from "@/stores/User";

export default Base.extend(User => {
  const Schema = User.Schema;

  return {
    findByAge(age: typeof Schema.age) {
      return User.find({ where: { age } });
    },
    findByNamePrefix(prefix: typeof Schema.name) {
      return User.find({ where: { name: { $like: `${prefix}%` } } });
    },
    async updateAge(id: typeof Schema.id, age: typeof Schema.age) {
      return User.update(id, { set: { age } });
    },
    async getAverageAge() {
      const users = await User.find({ where: {} });
      return users.reduce((sum, u) => sum + u.age, 0) / users.length;
    },
  };
});

Bespoke SQL

Use db.sql when an operation is clearer as raw SQL than as a Store query:

import db from "@/config/db";
import p from "pema";

const results = await db.sql({
  query: "SELECT name FROM users WHERE age > :age",
  params: { age: 18 },
  output: p.array({ name: p.string }),
});

TypeScript checks named placeholders against params, and the optional output schema validates and types the returned rows.

See the databases page for the full db.sql API.

Sessions

I18N