Change your database safely

Add delete with undo, versioned updates and migrations that never lock a busy table, and watch for slow and repeated queries.

Building for 0.3

Status Status is where the work is: Planned (in a milestone, not started), Building (tickets in progress), Preview (usable, may change), then the stability it ships at.

Stability The promise an interface ships with.

Stable Follows semantic versioning; breaking changes need a new major, 12 months of overlap and a codemod.
Beta Usable in production; may change before it becomes stable.
Experimental May change or go away; needs an explicit opt-in in main.yaml.

Stability levels

View as Markdown Open in Claude Edit

Updated 2026-10-28

You will let people delete a record and undo it, stop two editors overwriting each other, find slow and repeated queries before your users do, and run a migration without locking a busy table. It takes about 25 minutes and needs a Postgres database for the last step.

You need

A project with main.yaml and a model from db (see the interface page for models).
For step 4: db.driver: postgres and the DATABASE_URL secret, or one secret: per region.

1. Delete with undo

Give the model a trash period in main.yaml. A deleted record waits that long before it is gone, and a label's retention still caps it.

main.yaml

db:
  models:
    invoices: { trash: 30d, versions: true }

lib/invoices.ts

import { model, models, s } from "mainsdk";

const { invoices } = models({
  invoices: model(s.object({ total: s.int() }), { trash: "30d", versions: true }),
});

export async function undoable(user: string, id: string) {
  const mine = invoices.for(user);
  await mine.delete(id, { ifVersion: "any" });   // hidden from get and list
  return mine.restore(id);                        // MAIN-DATA-0063 once the 30 days have passed
}

You should see get(id) fail as not found after the delete, and trashed().list() show it.

2. Stop silent overwrites

With versions: true every read returns version, and an update must say which version it saw. A stale one fails with MAIN-DATA-0060 and nothing is written.

app/api/invoices/[id]/route.ts

import { etag, ifMatch, model, models, preconditionFailed, s } from "mainsdk";

const { invoices } = models({
  invoices: model(s.object({ total: s.int() }), { trash: "30d", versions: true }),
});

export async function PATCH(req: Request, { params }: { params: Promise<{ id: string }> }) {
  const { id } = await params;
  const patch = (await req.json()) as { total: number };
  const row = await invoices
    .for("u1") // the signed-in user in a real route
    .update(id, patch, { ifVersion: ifMatch(req) })   // reads the If-Match header
    .catch((err: unknown) => { throw preconditionFailed(err); });   // 412 instead of 409
  return Response.json(row, { headers: { etag: etag(row.version) } });
}

A request without If-Match gets a 428 (MAIN-DATA-0066).

3. Find slow and repeated queries

Every statement is timed. In development a query slower than db.slow and a query run more than db.repeat times in one request are logged, with the file and line that made them. Check a handler in a test.

app/api/orders/route.test.ts

import { expectQueries } from "@mainsdk/testing";
declare const GET: (req: Request) => Promise<Response>; // the route handler under test

await expectQueries(() => GET(new Request("http://localhost/api/orders")), { repeat: 10 });   // fails with MAIN-DATA-0054 on an N+1

Fix the repeated query by loading the records with one where: { id: { in: ids } } call. mainsdk check also reports a query inside a loop over another query's results (MAIN-DATA-0055).

4. Run migrations without blocking

Write the migration with your tool as usual. Before you merge, mainsdk check reads the migrations that are new since your base branch and names anything that would lock a busy table, with the safe form.

bash

npx mainsdk check

text

MAIN-DATA-0040  high  prisma/migrations/20261027_add_index/migration.sql:1
  fix: CREATE INDEX CONCURRENTLY "orders_status_idx" ON "orders" ("status"), in a migration of its own

Then apply them with a lock timeout and retries, once per region:

bash

npx mainsdk db migrate --plan
npx mainsdk db migrate

If a statement can't get its lock after the last retry, the run stops with MAIN-DATA-0044 and applies nothing after it. Run it again when the lock is free.

Ask your agent