---
title: "Change your database safely"
description: "Add delete with undo, versioned updates and migrations that never lock a busy table, and watch for slow and repeated queries."
section: "guides"
status: "Building for 0.3"
spec: "db"
canonical: "https://mainsdk.dev/docs/guides/change-your-database-safely"
updated: "2026-10-28"
---

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

Status: Building for 0.3 · Canonical: https://mainsdk.dev/docs/guides/change-your-database-safely · 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`](https://mainsdk.dev/docs/interfaces/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.

```yaml title="main.yaml"
db:
  models:
    invoices: { trash: 30d, versions: true }
```

```ts title="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.

```ts title="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.

```ts title="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

```text
Goal: add undo and safe edits to invoices, and ship the migration without locking.
Touch: main.yaml (db.models), the invoice routes, one test with expectQueries, the migration files.
Rules: read versions through ETag and If-Match; never loosen db.timeout; write a CREATE INDEX as CONCURRENTLY in its own migration; never drop a column the base branch still reads.
Verify: pnpm mainsdk check && pnpm test
Never weaken or skip a check; fix the finding it names.
```

## Next

- [Data and records](https://mainsdk.dev/docs/interfaces/db)
- [Build a multi-tenant app](https://mainsdk.dev/docs/guides/build-a-multi-tenant-app)
- [Run work in the background](https://mainsdk.dev/docs/guides/run-background-jobs)