# TailorDB Migrations > **Beta:** The `tailordb migration` command and the migration runtime are beta features. They may introduce breaking changes in future releases. The CLI emits a beta warning on every invocation. The migration system tracks changes to your TailorDB table definitions over time and applies them to deployed workspaces with optional data transformation scripts. For the CLI command reference, see [`tailordb migration`](../cli/tailordb.md#tailordb-migration). This document covers concepts, workflows, and operational guidance. ## Overview **Key Properties** - **Local snapshot–based diff detection** — each migration is generated by diffing your current table definitions against the previous snapshot stored in `migrations//`. - **Transaction-wrapped data migrations** — each `migrate.ts` script runs inside a database transaction on the platform; if the script throws, all changes in that migration roll back. - **Automatic execution during `apply`** — `tailor deploy` detects pending migrations, runs the two-stage table update (pre-migration → script → post-migration), and updates the migration checkpoint label. - **Type-safe scripts** — the generated `db.ts` provides Kysely types that reflect the schema state **before** the migration runs, so transformations are written against the actual data shape. **Files in `migrations/`** ``` migrations/ ├── 0000/ # Initial schema snapshot │ └── schema.json ├── 0001/ # First change │ ├── diff.json # Field-level diff from 0000 │ ├── migrate.ts # Data migration script (auto-generated for breaking changes; can be added manually via `migration script`) │ ├── db.ts # Kysely types for the script (pre-migration shape) │ └── db.pglite.ts # CREATE TABLE script of that shape, for PGlite tests ├── 0002/ │ └── diff.json # No script — non-breaking changes only └── ... ``` `0000` always contains a full snapshot. `0001` and onward contain a diff plus, optionally, a script and its types (auto-generated for breaking changes, or added manually via `tailordb migration script` for warning-tier changes). **Commit the entire `migrations/` directory to version control.** ## Initial Setup ### New project When you start with no `migrations/` directory: 1. Add the `migration` block to `tailor.config.ts` (see [Configuration](#configuration)). 2. Define your initial tables in `tailordb/`. 3. Generate the initial migration: ```bash tailor tailordb migration generate ``` This creates `migrations/0000/schema.json` from your current tables. 4. Run `tailor deploy`. The migration label is set to `0000` on the deployed namespace. ### Adding migrations to an existing project If you already have a deployed workspace whose schema matches your local table definitions: 1. Add the `migration` block to `tailor.config.ts`. 2. Run `tailor tailordb migration generate` to create `0000/schema.json` from current local tables. 3. Run `tailor deploy`. Because remote schema already matches, no script runs; only the migration label is set. If your local tables and remote schema have **diverged**, reconcile them before introducing migrations — either update local tables to match remote, or accept that the first non-`0000` migration will reflect that gap. ### Resetting `tailor tailordb migration generate --init` deletes the existing `migrations/` directory and creates `0000` from the current local tables. Use it only before the project is deployed. For a deployed migration history, use [`migration rebaseline`](#re-baselining-a-deployed-migration-history), which verifies the history and connected workspace before replacing any files. ## Migration Workflow A typical change cycle: 1. **Modify a table definition.** ```typescript // tailordb/user.ts export const user = db.table("User", { name: db.string(), email: db.string(), // ← new required field ...db.fields.timestamps(), }); ``` 2. **Generate the migration.** ```bash tailor tailordb migration generate --name "add email to user" ``` Output: ``` Generated migration 0001 Diff file: ./migrations/0001/diff.json Migration script: ./migrations/0001/migrate.ts DB types: ./migrations/0001/db.ts ``` If `EDITOR` or `VISUAL` is set, `migrate.ts` opens automatically. 3. **Edit `migrate.ts`** to populate data for the new required field: ```typescript import type { Transaction } from "./db"; export async function main(trx: Transaction): Promise { await trx .updateTable("User") .set({ email: "default@example.com" }) .where("email", "is", null) .execute(); } ``` 4. **Apply.** ```bash tailor deploy ``` The pre-migration phase relaxes the new field to optional, the script runs and populates values, then the post-migration phase enforces `required: true`. ### Warnings and optional migration scripts Some non-breaking changes can still cause data loss — most notably removing a field (`field_removed`), removing a table (`table_removed`), or removing a member inside a nested field (reported on the nested field's `field_modified` change). `migration generate` reports these as **warnings**: ``` Warning: data loss possible: - User.legacyParentId: Field removed (existing data will no longer be accessible through the schema after the post-migration phase) ``` No `migrate.ts` is generated automatically because the schema change itself is non-breaking, but the existing data is no longer accessible through the active schema after the post-migration phase. The platform may retain a removed field's underlying stored value, so do not rely on removal to clear data before reusing the same field name. If you need to preserve, transform, or clear that data first, add a script with: ```bash tailor tailordb migration script 0002 ``` This writes `migrations/0002/migrate.ts`, `migrations/0002/db.ts`, and `migrations/0002/db.pglite.ts` next to the existing `diff.json` (add `--with-test` to also scaffold the tests — see [Testing Migrations Locally](#testing-migrations-locally)). The removed field stays readable inside `migrate.ts` because the pre-migration phase keeps it on the table until the script finishes (see [Per-migration phases](#per-migration-phases)). The next `tailor deploy` runs the script automatically — `migrate.ts` is executed whenever the file exists on disk, regardless of whether the diff itself required it. If the data loss is intentional and no script is needed, record that decision the same way as for breaking changes (see [Breaking changes without a script](#breaking-changes-without-a-script)): ```bash tailor tailordb migration script 0002 --no-script --reason "column no longer needed, data can be dropped" ``` In an interactive session, `migration generate` offers to record the reason on the spot when it detects warnings. The acknowledgment is stored in `diff.json`, so it is reviewable in the PR, and it satisfies `migration validate --strict` — useful for enforcing in CI that destructive changes are explicitly acknowledged before merge (see [Schema verification](#schema-verification)). ### Renaming a field Renaming a field in a table definition looks like a removal plus an addition to the diff engine. Left as-is, that combination silently drops the old field's data: the removal is only a warning, so nothing forces a data copy. To prevent that, when `migration generate` finds a removed field and an added field in the same table whose stored values can be copied without changing their meaning, it asks whether the change is a rename. Serial fields are never rename candidates, and an enum field only qualifies when it keeps every value of the removed field: ``` ? User.fullName was removed and displayName was added with a compatible type. Was it renamed to displayName? (Y/n) ``` In non-interactive environments (or with `--yes`), no prompt is shown and the command fails while a rename candidate is left unresolved — writing it as remove + add would silently drop the field's data at deploy. Resolve every candidate explicitly: pass the rename, or confirm a genuine removal with `--drop`: ```bash tailor tailordb migration generate --rename "User.fullName:displayName" tailor tailordb migration generate --drop "User.fullName" ``` Repeat `--rename` and `--drop` for multiple fields. A `--rename` that does not match a compatible removed + added pair, or a `--drop` that does not match a removed field, fails with an error. A confirmed rename is recorded as a single `field_renamed` change and treated as **breaking**, so a migration script is required. The generated `migrate.ts` copies the old field into the new one with a single set-based update, and the generated `db.ts` exposes both the old field (readable) and the new field (writable). The copy intentionally overwrites every row without checking for existing values: values of removed fields are retained in storage, so a stale value could otherwise resurface under the new name later. If the new field adds a unique constraint, the script also includes a duplicate-resolution block to run before the constraint is enforced. During deploy, the pre-migration phase keeps the old field and adds the new field with its constraints relaxed, the script copies the data, and the post-migration phase drops the old field and enforces the new field's constraints — all within a single `tailor deploy`. If you decline the prompt (or confirm the removal with `--drop`), the change stays a plain removal + addition with the usual data-loss warning. #### Renaming a member inside a nested field Members inside a **nested field** (`db.object(...)`) are detected the same way: when `migration generate` finds a member removed from a nested field and a compatible member added under the same parent, it asks whether the member was renamed. Two members qualify only when copying the value preserves it exactly, because nested member constraints other than the new member's requiredness and unique constraint are not relaxed: the type, array-ness, requiredness, foreign key target, decimal scale, hooks, and validations must match (index, unique, and vector may differ, as for a top-level rename), enum values may be added but not removed, an object-typed member must keep the same members recursively, and serial members never qualify. ``` ? User.address.zip was removed and zipCode was added with a compatible type. Was it renamed to zipCode? (Y/n) ``` In non-interactive environments the command fails while a candidate is left unresolved, exactly like field renames. Resolve it with the nested member forms of the same flags (a value with two or more dots before the `:` targets a member; deeper members use their dotted path, and the new name is a single segment under the same parent): ```bash tailor tailordb migration generate --rename "User.address.zip:zipCode" tailor tailordb migration generate --rename "User.address.geo.lat:latitude" tailor tailordb migration generate --drop "User.address.zip" ``` A confirmed rename is recorded on the nested field's `field_modified` change as `memberRenames` and treated as **breaking**, so a migration script is required. The generated `migrate.ts` reads every row's nested value, stores each renamed member under its new name (descending into arrays at every level), and writes the value back; the old member is kept in the written value because it stays on the schema until the post-migration phase drops it. During deploy, the pre-migration phase keeps the old member on the nested field and adds the new member as optional, the script copies the values, and the post-migration phase drops the old member and enforces the new member's requiredness. A member removed without a confirmed rename stays a data-loss warning, which `migration validate --strict` picks up like any other warning; when a compatible sibling was added, the warning names it and the `--rename` value that confirms the rename: ``` Warning: data loss possible: - User.address.zip: Nested member removed (existing values will no longer be accessible through the schema). Possibly renamed to zipCode: confirm it with --rename "User.address.zip:" to scaffold a copy script, or keep the removal and copy the values yourself ``` The pre-migration phase keeps a removed member on the nested field until the script finishes, exactly like a removed top-level field, so a custom script can still read it. Nested fields reach the script as objects. Renaming a nested member cannot be combined in one migration with renaming its table or the nested field itself, and an object-typed member cannot be renamed in the same migration as one of its own members; split such changes into separate migrations (rename the object first, then its member). ### Renaming a table Renaming a whole table is detected the same way: when `migration generate` finds a removed table and an added table with a matching shape, it asks whether the change is a rename: ``` ? User was removed and Person was added with a compatible schema. Was it renamed to Person? (Y/n) ``` In non-interactive environments the command fails while a candidate is left unresolved, exactly like field renames. Resolve it with the table forms of the same flags (a value without a `.` targets a table): ```bash tailor tailordb migration generate --rename "User:Person" tailor tailordb migration generate --drop "User" ``` Two tables qualify as a rename pair only when copying every row preserves the data: every field must keep its name, type, array-ness, required/unique constraints, foreign key target, and decimal scale; enum fields may gain values but not lose them; indexes must match. A self-referential foreign key is compared against the new table name and must be optional. Tables with serial fields (their values cannot be written by a script) or file fields (file contents are not copied) are never candidates. Name-derived and data-independent settings — `pluralForm`, description, table settings, permissions, hooks, and validations — may differ. A confirmed rename is recorded as a single `table_renamed` change and treated as **breaking** for two reasons: existing records must be copied by the migration script, and the table's GraphQL API names (derived from the table name and `pluralForm`) change, which breaks API clients. The generated `migrate.ts` copies every row from the old table into the new one in id-ordered batches, preserving ids so stored foreign key references stay valid, and the generated `db.ts` exposes both the old table (readable) and the new table (writable). Self-referential foreign keys are inserted as null and backfilled after every row exists, so a reference to a row in a later batch cannot fail the copy. Two caveats apply to the copy. The old table is not write-protected: rows written to it after the script's transaction commits — and before post-migration cleanup drops it — are not carried over, so pause writers to the renamed table for the duration of the deploy. And platform-managed record metadata (creation/update timestamps and actors) cannot be written by the script, so the new table's records carry the migration run's metadata instead of the original values. Fields on other tables that reference the renamed table via `foreignKeyType` must be retargeted at the new name in the same change. That retarget is recognized as part of the rename: it is not flagged as a breaking foreign-key change and needs no reference fixup, because record ids are preserved by the copy. During deploy, the pre-migration phase creates the new table with its full constraints while the old table stays on the namespace, the script copies the rows, and the old table is dropped in post-migration cleanup after the checkpoint advances — all within a single `tailor deploy`. ### Breaking changes without a script Breaking changes require `migrate.ts`. If it is missing at deploy time (for example, the generated script was deleted), `tailor deploy` fails before applying the migration or anything after it. When there is genuinely nothing to migrate — say, the affected table holds no data yet — record an explicit acknowledgment instead of keeping an empty script: ```bash tailor tailordb migration script 0002 --no-script --reason "no data yet, safe to skip" ``` This stores the reason in `migrations/0002/diff.json` (commit the change). The next `tailor deploy` applies the schema change as usual, skips only the script step, and logs the recorded reason. The command refuses to record a skip while `migrate.ts` exists — delete the script first. If `migrate.ts` is added back later, `tailor deploy` fails rather than choosing between the script and the acknowledgment; run `tailor tailordb migration script 0002` again to clear the now-stale acknowledgment from `diff.json` (the script then runs on the next deploy), or delete `migrate.ts` to keep the skip. ### Data-only migrations Sometimes existing data must be transformed without any schema change — fixing values written by an application bug, or a one-off normalization. Create a migration that carries no schema diff and exists only to run its script: ```bash tailor tailordb migration generate --data-only --name "normalize legacy phone numbers" ``` This writes a numbered migration with an empty `diff.json`, a `migrate.ts` skeleton, and `db.ts` typed against the current schema. Edit `migrate.ts` to implement the transformation; the next `tailor deploy` runs it like any other migration script — in a single transaction, advancing the migration checkpoint (see [Performance and Large Tables](#performance-and-large-tables) for batching patterns). Because the entry is part of the migration history, the fix is versioned, ordered relative to schema changes, and applied once per workspace. The command requires a clean state: if the namespace has schema changes that are not yet in migration files, generate the schema migration first. With multiple namespaces, pass `--namespace` to name the target. `--data-only` cannot be combined with `--init`, `--rename`, `--drop`, or `--expand-contract`. A data-only migration runs in **every** workspace the history is applied to, including freshly created ones. Write the script so it is safe against tables with no matching rows (a set-based `UPDATE` with a `WHERE` clause is naturally a no-op on an empty table). For a fix that should run in a single environment only, or that is too large for one transaction, run it outside the migration history instead. ## Configuration ```typescript // tailor.config.ts export default defineConfig({ name: "my-app", db: { tailordb: { files: ["./tailordb/*.ts"], migration: { directory: "./migrations", // Optional. Defaults to the first machine user in auth.machineUsers. machineUser: "admin-machine-user", }, }, }, }); ``` | Option | Type | Description | | ----------------------- | ------ | --------------------------------------------------------------------------------------------------------- | | `migration.directory` | string | Directory path for migration files. Required when migrations are enabled for the namespace. | | `migration.machineUser` | string | Machine user used to run migration scripts. Optional; defaults to the first entry in `auth.machineUsers`. | ## Generated Files | File | When generated | Description | | ----------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------ | | `0000/schema.json` | First `migration generate` | Full snapshot of all tables in the namespace. | | `XXXX/diff.json` | Every subsequent migration | Field-level diff against the previous snapshot. | | `XXXX/migrate.ts` | Auto-generated for breaking changes and `--data-only` migrations; added manually via `tailordb migration script` for warning-tier changes | Data transformation script. The `main` export receives a Kysely `Transaction`. | | `XXXX/db.ts` | Generated once when `migrate.ts` is created | Kysely types reflecting the schema **before** this migration. Exports `Database`, `Transaction`, and `MigrationContext`. | | `XXXX/db.pglite.ts` | Generated with `db.ts` | `CREATE TABLE` script of the same schema, for running `migrate.ts` on PGlite. Never deployed. | | `XXXX/migrate.test.ts` | Added via `tailordb migration script --with-test` | Unit-test scaffold for `migrate.ts` (see [Testing Migrations Locally](#testing-migrations-locally)). Never deployed. | | `XXXX/migrate.pglite.test.ts` | Added via `tailordb migration script --with-test` when `@electric-sql/pglite` is installed | PGlite test scaffold for `migrate.ts`. Never deployed. | `db.ts` reflects the pre-migration schema because the script runs after the pre-migration phase has temporarily relaxed breaking constraints (e.g., a new `required` field is added as `optional` first), so the data being read still matches the previous shape. ### Migration file format compatibility Migration files are versioned independently of the SDK package. This SDK writes format version `6` and reads versions `1` through `6`. It normalizes supported older formats in memory; it never rewrites applied migration files on disk. Format version `6` records renames of members inside nested fields (`memberRenames`); older SDK versions refuse to read it rather than deploying such a migration without the copy step. Supported histories also preserve the behavior of field hooks and validators saved by older SDKs, including access to the record and boolean validators with a separate error message. Legacy update hooks retain existing values for omitted fields; explicitly supplied values, including `null`, take precedence. This applies to both snapshots and diffs, including nested fields. Your existing migration files can remain as generated. If a future SDK can no longer replay an old migration format, re-baseline while using an SDK version that still supports the complete history, commit the new baseline, deploy it to every environment, and then upgrade the SDK. A file from a newer unsupported format instead requires upgrading the SDK first. The CLI rejects both cases with guidance rather than attempting a best-effort replay. The SDK used for this transition must read the old history and write a baseline format that the target SDK accepts. Keep that SDK version pinned until every environment has adopted the new baseline. File-format support does not guarantee compatibility for arbitrary imports in a custom `migrate.ts`; keep its dependencies pinned and test customized scripts when upgrading. There is no migration-file conversion command. Keeping applied files unchanged preserves the record of what ran, while `migration rebaseline` provides the escape hatch when the supported replay window changes. ## Migration Script Anatomy ```typescript import type { Transaction } from "./db"; export async function main(trx: Transaction): Promise { // SELECT is supported. const users = await trx.selectFrom("User").select(["id", "name"]).execute(); // Loop and transform. for (const u of users) { await trx .updateTable("User") .set({ displayName: u.name.toUpperCase() }) .where("id", "=", u.id) .execute(); } } ``` **Worked example: backfilling a new required enum field** Adding a required field is a breaking change, so `migration generate` scaffolds `migrate.ts`. Given this table change: ```typescript // tailordb/user.ts export const user = db.table("User", { name: db.string(), email: db.string(), role: db.enum(["MANAGER", "STAFF"]), // ← new required field ...db.fields.timestamps(), }); ``` existing `User` rows have no `role` value yet, so the script assigns one before the post-migration phase enforces the constraint: ```typescript import type { Transaction } from "./db"; export async function main(trx: Transaction): Promise { await trx.updateTable("User").set({ role: "MANAGER" }).where("role", "is", null).execute(); } ``` The `where("role", "is", null)` guard keeps the script idempotent — rows that already have a value are untouched if the script re-runs. Reference scripts for other breaking-change patterns live in the repository's [migration fixture templates](https://github.com/tailor-platform/sdk/tree/main/example/tests/migration-fixtures/templates): backfilling fields that become required and migrating rows off a removed enum value ([0005](https://github.com/tailor-platform/sdk/blob/main/example/tests/migration-fixtures/templates/0005/migrate.ts)), and de-duplicating values before a unique constraint is added ([0006](https://github.com/tailor-platform/sdk/blob/main/example/tests/migration-fixtures/templates/0006/migrate.ts)). They show the shape of each migration, not drop-in logic — adapt them to your data (the suffix strategy in `0006`, for example, assumes the suffixed names are not already taken). **Accessing environment variables** The migration `main` receives an optional second argument exposing the variables defined in `defineConfig({ env })` — the same values available via `context.env` in resolvers. The `MigrationContext` type is exported from the generated `./db`: ```typescript import type { Transaction, MigrationContext } from "./db"; export async function main(trx: Transaction, { env }: MigrationContext): Promise { // Branch on environment-specific config resolved at deploy time if (env.SKIP_BACKFILL) return; await trx.updateTable("User").set({ stage: env.ENVIRONMENT }).execute(); } ``` The `env` values are injected at bundle time (the same mechanism as resolvers/executors/workflow jobs); `process.env` and other Node-side environment access remain unavailable at runtime. The second argument is optional — existing `main(trx)` scripts continue to work unchanged. **Rules** - Always use the `trx` argument for database access. Anything that bypasses `trx` is not part of the transaction and will not roll back on failure. - Do not import resolvers, executors, or other SDK runtime services. The migration script runs as a standalone bundle on the platform with only Kysely access; SDK service helpers are not available. - Standard Node-compatible packages are bundled. Keep dependencies minimal — every import is shipped to the platform. - `console.log` / `console.error` output is captured and surfaced under `Logs:` in the apply output. Use it sparingly for progress markers on long-running migrations. - The script is idempotency-friendly by default because it runs inside a transaction, but **plan for re-execution**: if a later migration in the same `apply` fails, the platform may retry the apply, and you may want your script to tolerate already-migrated rows (e.g., `where("email", "is", null)` instead of unconditional updates). ## Supported Schema Changes | Change Type | Breaking? | Migration Script? | Notes | | --------------------------------- | --------- | ----------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Add optional field | No | No | Schema change only | | Add required field | Yes | Yes | Script populates default values | | Remove field | No | Optional | Warning tier — no script is auto-generated, but you can add one with `tailordb migration script` to preserve or clear data before the field leaves the active schema. The field stays readable from `migrate.ts` during Pre-migration. | | Rename field | Yes | Yes | Confirmed interactively at generate time or via `--rename "Table.oldField:newField"` — see [Renaming a field](#renaming-a-field). Auto-generated script copies values from the old field to the new one; both fields coexist during Pre-migration. | | Remove nested member | No | Optional | Warning tier — see [Renaming a member inside a nested field](#renaming-a-member-inside-a-nested-field). The member stays readable from `migrate.ts` during Pre-migration. | | Rename nested member | Yes | Yes | Confirmed interactively at generate time or via `--rename "Table.field.oldMember:newMember"`. Auto-generated script rewrites each row's nested value; the old member stays readable and the new member is optional during Pre-migration. | | Change optional → required | Yes | Yes | Script sets defaults for null values | | Change required → optional | No | No | Schema change only | | Add index (non-unique) | No | No | Schema change only | | Add unique index | Yes | Yes | Script must resolve duplicate value combinations across the index fields | | Change unique index fields | Yes | Yes | Treated like adding a new unique constraint over the new field set | | Remove index | No | No | Schema change only (removing the unique constraint from an index is also non-breaking) | | Add unique constraint | Yes | Yes | Script must resolve duplicate values | | Remove unique constraint | No | No | Schema change only | | Change decimal scale | Yes | Yes | Auto-generated script re-saves existing rows under the new scale. Decreasing scale rounds values half-up and can lose precision. If the same change adds a unique constraint, duplicate handling runs after re-saving. | | Add enum value | No | No | Schema change only | | Remove enum value | Yes | Yes | Script migrates records with removed values | | Add table | No | No | Schema change only | | Remove table | No | Optional | Warning tier — no script is auto-generated, but you can add one with `tailordb migration script` to preserve data before the table leaves the active schema. The table stays readable from `migrate.ts` during Pre-migration. | | Rename table | Yes | Yes | Confirmed interactively at generate time or via `--rename "OldTable:NewTable"` — see [Renaming a table](#renaming-a-table). Auto-generated script copies all rows preserving ids; both tables coexist until post-migration cleanup. | | Change foreign key target table | Yes | Yes | Script updates references to the new target | | Change field type (verified pair) | Yes | Yes | In-place for the pairs listed under [Field type changes](#field-type-changes); review the generated normalization scaffold and customize it only when existing values need transformation | | Change field type (other pair) | Yes | Yes | Two migrations, generated together after you confirm — see [Converting a field type](#converting-a-field-type). Edit the conversion in the first; the second needs no changes. | | Change array → single value | - | - | **Not supported** — see [Converting a field type](#converting-a-field-type) | | Change single value → array | Yes | Yes | Two migrations, generated together after you confirm — see [Converting a field type](#converting-a-field-type). Each stored value becomes a one-element array, so the conversion needs no edits. | ### Field type changes These pairs change in place, in a single migration: | From | To | | --------- | ---------------------------- | | `integer` | `string`, `float`, `decimal` | | `float` | `string`, `decimal` | | `decimal` | `string`, `float` | | `boolean` | `string` | | `uuid` | `string` | | `enum` | `string` | Every pair here accepts every value its source type allows, which is what lets the change happen in one migration: the field keeps its previous type until the migration finishes, so your application can keep writing to it throughout. Every other scalar pair needs a temporary field. Eligible fields can use the generated [expand-contract pair](#converting-a-field-type); fields the generator rejects still need the manual three-step sequence described below. Three groups are worth calling out: - Converting to a narrower type — `string` → `integer`, `string` → `uuid`, `integer` → `boolean` and similar — is excluded because values the source type still accepts, such as `"abc"` in a `string` field, cannot be cast. Your script could clean up the rows it sees, but the field goes on accepting new uncastable values until the migration completes. - `boolean` → `integer`, `float` → `integer`, and `string` → `date` are excluded because the stored values cannot be cast to the new type. - `date`, `datetime`, and `time` fields are not stored in the textual form you wrote them in, so converting them to or from another type reads back as a different instant. Convert them through a temporary field where your script controls the formatting. Converting to `decimal` reformats values to the field's scale, so `42` reads back as `42.000000`. Values with more decimal places than the scale allows are rounded half-up, so `1.1234567` becomes `1.123457` at the default scale of 6. A `float` field that is already unique cannot convert to `decimal` in place, because rounding can turn two distinct values into the same one and the existing constraint leaves no room to resolve the collision. Use the 3-step migration for those. ### Generated normalization script for field type changes For example, changing `User.age` from `integer` to `float` generates a `migrate.ts` that scans non-null values in batches of 100: ```typescript import type { Transaction } from "./db"; export async function main(trx: Transaction): Promise { // Normalize User.age from integer to float while the previous type is still active { let lastId: string | undefined; while (true) { let query = trx .selectFrom("User") .select(["id", "age"]) .where("age", "is not", null) .orderBy("id", "asc") .limit(100); if (lastId) { query = query.where("id", ">", lastId); } const rows = await query.execute(); if (rows.length === 0) break; for (const row of rows) { // TODO(tailor-migration-review): Remove this marker and the `never` annotation after reviewing the normalization. // Keep the value accepted by the active integer type and castable to float. const sourceValue = row.age; if (sourceValue === null) continue; const normalizedValue: never = sourceValue; if (Object.is(normalizedValue, sourceValue)) continue; await trx .updateTable("User") .set({ ["age"]: normalizedValue }) .where("id", "=", row.id) .execute(); } lastId = rows[rows.length - 1]!.id; } } } ``` The generated `never` annotation intentionally causes a TypeScript error until you review the normalization. If the existing values are already suitable for the target type, remove the annotation and review marker to accept the identity transformation; it does not write any rows. If values need application-specific normalization, replace the expression and remove the annotation and marker while keeping the result valid for both the active source type and the target type. The source field contract remains active until the script finishes; for example, an `integer` → `float` script cannot write fractional values during this phase. ### Converting a field type A field type change outside the verified in-place pairs — `string` → `integer`, for example — cannot be applied in one step, because the field would have to hold both shapes at once. The same holds when a single value becomes an array (`string` → `string[]`): the stored values must be rewritten as arrays before the field can take the new shape. `migration generate` offers to carry the values through a temporary field instead: ``` User.price changes from string to integer, which cannot be applied in one step. ? Generate two migrations to convert User.price through a temporary field? (Y/n) ``` Confirming writes two migrations: 1. **The conversion.** Adds a temporary field (`priceMigrate`), converts each stored value into it, and clears and removes the original field. Edit the conversion expression before deploying: the generated `never` annotation fails your typecheck, and `tailordb migration validate` rejects the migration while the review marker is still there. When only the array-ness changes (`string` → `string[]`), the conversion stores each value as a one-element array and carries no review marker; when the element type changes as well (`integer` → `string[]`), you convert the element and the script wraps it. 2. **The rename.** Renames the temporary field back to `price`. Its copy script is complete, but this migration also carries every other schema change the same run picked up, so review it as you would any generated migration. If converting to an array also reduces a decimal field's `scale` or removes enum values, the conversion keeps the review marker. Edit the element conversion to satisfy the target field before deploying. `tailor deploy` applies both. Because the conversion only touches rows whose original value is still set, a re-run resumes where it stopped rather than converting a row twice. The original field is removed in the first migration rather than the second, because the rename needs its name free. Your script can still read it while the conversion runs. In a non-interactive run — `--yes`, or CI — name each field explicitly: ```bash tailor tailordb migration generate --yes --expand-contract "User.price" ``` Without the flag the command fails rather than converting anything, so a scripted run cannot start a two-migration change by accident. > **Why the conversion clears the original field.** Removing a field from the schema does not necessarily remove its stored value. Reusing the name for an incompatible type while a stale value remains can deploy successfully and then make subsequent reads fail. Clearing the original in the same update is what prevents that, which is why the generated script writes both fields at once. > **Writes that land while the conversion runs are not carried across.** The original field keeps its old type until the conversion finishes, so an application can still write to it after the script has read the last row — and that value is dropped with the field rather than converted. Stop writes to the field, or accept the loss, before deploying a conversion on a live workspace. Some changes are still rejected and need a temporary field you add yourself — add the new field, write a script that fills it and clears the old one, then remove the old field and rename the temporary one in a later migration: - A field that is already an array: collapsing it into a single value has no answer the generated script could choose for you, and changing its element type (`string[]` → `integer[]`) is not generated either. - A field that is unique, or that an index, relationship, permission, or table-level script names. Those keep pointing at the original name, which the conversion removes. ## Testing Pending Migrations `tailor tailordb migration test` runs every migration pending in the source workspace against an isolated workspace. The source workspace is selected by `--workspace-id` or the active profile and is never modified. The command performs the following sequence: 1. Reads each migration-enabled namespace's `sdk-migration` checkpoint from the source workspace and reconstructs that exact snapshot from local migration history. 2. Creates a temporary workspace in the same region, organization, and folder as the source, unless `--target-workspace-id` names an existing throwaway workspace. 3. Deploys the checkpoint snapshots and writes their checkpoint labels. 4. Loads fixture data or clones source records. 5. Runs the normal deployment pipeline, including every pending pre-migration, `migrate.ts`, and post-migration phase. 6. Optionally runs an assertion script against the migrated data. 7. Deletes an automatically-created workspace after success or failure. Both the pre-migration and final TailorDB schemas come from committed migration snapshots. Ungenerated changes in the current table definitions are not included in the rehearsal. Executors are omitted from the baseline deployment so loading fixture or cloned records cannot trigger current event handlers against the older schema. Auth user profiles are also deferred until the pending migrations finish, while configured machine users remain available to run seed and migration scripts. The final deployment restores the configured executors and user profiles. Static websites are deployed so configuration references to their URLs resolve, but their workspace-bound custom domains are omitted from migration-test deployments. ### Seed mode Seed mode is the default and uses the JSONL files produced by the configured `seedPlugin`. Run `tailor generate` after adding the plugin or changing seed tables, then populate its `data/*.jsonl` files: ```bash tailor tailordb migration test --data seed ``` Rows are loaded only for tables present in the deployed pre-migration snapshots (and current schemas without migrations), in foreign-key dependency order. Fields introduced by pending migrations, including timestamp and nested fields, are removed before insertion so current fixtures can be loaded into the baseline schema. Missing table files are treated as empty. IdP `_User` fixtures are not loaded by this command. Use `--machine-user` to override the seed plugin's `machineUserName`, the namespace migration setting, and the first configured Auth machine user for seed and assertion execution. ### Clone mode Clone mode copies TailorDB records from the source workspace after the identical application, namespace names, and pre-migration schemas exist in the target. For namespaces without migration history, the command reproduces the deployed source schema rather than uncommitted local table changes: ```bash tailor tailordb migration test --data clone ``` The platform clone API is feature-gated and requires editor access to both same-region workspaces. It copies TailorDB records only: IdP users, file blobs, and metadata labels are not copied. File fields therefore retain references whose blobs are absent. The command polls the asynchronous operation and reports platform failures; if clone is unavailable, use seed mode. The source schema is re-verified immediately before cloning; if the source workspace was deployed or otherwise changed after the test started, the command aborts instead of cloning data that no longer matches the deployed baseline. ### Assertions and retained targets Pass a TypeScript file with `--assert`. Its exported `main` function uses the same Kysely transaction signature as `migrate.ts`, runs after all pending migrations, and must throw when an invariant fails: ```bash tailor tailordb migration test \ --data seed \ --assert ./tests/assert-customer-email.ts \ --assert-namespace tailordb ``` `--assert-namespace` is inferred when only one namespace has pending migrations and is required otherwise. To inspect the result after a run, pass `--keep` so the automatically created workspace survives instead of being deleted, on success and on failure: ```bash tailor tailordb migration test --keep ``` Alternatively, provide an empty designated throwaway workspace. This mode never deletes the target and requires explicit acknowledgment: ```bash tailor tailordb migration test \ --target-workspace-id 00000000-0000-4000-8000-000000000000 \ --yes ``` Do not target a shared development or production workspace: baseline deployment reconciles its managed resources and schemas before the migration test runs. ## Automatic Migration Execution When you run `tailor deploy`, the SDK detects pending migrations (anything past the current `sdk-migration` label on the deployed namespace) and runs them in order before continuing with the rest of the apply. ### Per-migration phases For each pending migration: 1. **Pre-migration**: Schema changes that would be breaking are applied in a relaxed form first. A verified in-place field type change keeps its complete previous field contract until Post-migration, including field and table-level hooks or validators changed by the same migration. Newly-required fields are added as optional; fields whose `optional → required` transition is breaking are temporarily kept optional. Fields that are being removed in this migration are temporarily kept on the table so that `migrate.ts` can still read them (for example, to `innerJoin` through a foreign key that is about to be dropped); members removed from a nested field are kept the same way, and the new member of a confirmed nested rename is added as optional and non-unique. For a renamed field, the old field is kept and the new field is added with its constraints relaxed, so the script can read the old field and write the new one. For a renamed table, the new table is created with its full constraints while the old table stays on the namespace until post-migration cleanup, so the script can copy rows between them. Breaking table-level index changes are relaxed the same way: a newly-added unique index is withheld, and an index gaining a unique constraint (or a unique index changing its field set) keeps its previous definition, so `migrate.ts` can resolve duplicates first. Non-breaking changes that are part of the same migration are also applied here. 2. **Script execution**: If `migrate.ts` exists on disk for this migration, it is bundled and sent to the platform via the script execution API and runs as the configured machine user inside a transaction. The script is hard-required for breaking changes (`diff.requiresMigrationScript`) — deploy fails if the file is missing, unless a `--no-script` acknowledgment was recorded (see [Breaking changes without a script](#breaking-changes-without-a-script)). It is also executed when present for warning-tier diffs — see [Warnings and optional migration scripts](#warnings-and-optional-migration-scripts). 3. **Post-migration schema**: Required constraints and the target field definitions are applied. Do not assume that removing a field clears its underlying stored JSON value. 4. **Checkpoint and cleanup**: The `sdk-migration` label is bumped to this migration's number, then removed GQL permissions and tables — including the old table left behind by a rename — are deleted. Advancing the checkpoint first prevents a failed checkpoint write from requiring the SDK to recreate irreversibly deleted records. This split is what allows existing rows to be backfilled before the database starts rejecting nulls, and what lets `migrate.ts` traverse foreign-key fields that the same migration removes. ### Schema verification Before running migrations, `apply` performs two checks: 1. **Local schema check** — your current table definitions must match the latest snapshot in `migrations/`. If they don't, you forgot to run `migration generate`. 2. **Remote schema check** — the deployed schema is reconstructed from migration history; the actual remote schema must match. Drift here means someone applied a different set of migrations or edited the schema out-of-band. On drift you'll see something like: ``` ✖ Remote schema drift detected: Namespace: tailordb Remote migration: 0007 Differences: Table 'User': - Field 'email': required: remote=false, expected=true ``` The error also points you at `migration status`, `migration generate`, `migration sync`, and `migration set` — see [Remote schema drift detected](#remote-schema-drift-detected) for which one applies. To run the same checks without deploying — plus migration file integrity (numbering, parseable contents, a `migrate.ts` or a recorded `--no-script` acknowledgment for every migration that requires a script, and no unresolved generated normalization review markers): ```bash tailor tailordb migration validate ``` It reports issues per namespace, exits with a non-zero code when any check fails, and supports `--json` for machine-readable output. With `--strict`, validation additionally fails when a migration not yet applied to the remote has data-loss warnings (see [Warnings and optional migration scripts](#warnings-and-optional-migration-scripts)) but neither a `migrate.ts` nor a recorded `--no-script` acknowledgment. The failure names the affected table and field and prints the exact command to record the acknowledgment. To bypass both checks during deploy (not recommended outside of recovery scenarios): ```bash tailor deploy --no-schema-check ``` ### Example output ``` ℹ Found 2 pending migration(s) to execute. ℹ Executing 2 pending migration(s)... ℹ Using machine user: admin-machine-user for namespace 'tailordb' ✔ Migration tailordb/0002 completed successfully ✔ Migration tailordb/0003 completed successfully ✔ All migrations completed successfully. ✔ Successfully applied changes. ``` ## Re-baselining a deployed migration history `tailor tailordb migration rebaseline` collapses the complete history into a new `0000/schema.json` in the current migration format. It does not modify the deployed schema or data. Before running it: 1. Apply the latest migration to every environment. The CLI verifies the connected workspace, but it cannot inspect other workspaces. 2. Commit or otherwise preserve the existing migration history. Files after `0000`, including `migrate.ts` and `db.ts`, disappear from the working tree; Git history retains committed files. 3. Make sure local table changes have been captured with `tailor tailordb migration generate`. Then re-baseline one namespace: ```bash tailor tailordb migration rebaseline --namespace tailordb ``` The command validates the migration files, verifies that replaying the latest migration exactly reproduces the local table definitions, and checks that the connected workspace is at that latest migration with no schema drift. After confirmation, it replaces the local history with the reconstructed baseline, records a new migration history ID in both `0000/schema.json` and remote metadata, and resets the connected workspace's `sdk-migration` label to `0000`. Use `--yes` only after arranging the same operational preconditions in non-interactive automation. Commit the resulting `migrations/` change before generating any new migrations. For another environment still carrying the exact checkpoint and history ID that the new baseline replaced, the next `tailor deploy` checks whether its remote schema exactly matches the new `0000`. If it does, deploy offers to reset the checkpoint to `0000` and move the environment to the new history ID before applying any later local migrations. A markerless history is eligible only for the first rebaseline, at the exact migration recorded as replaced. Any other checkpoint or history ID is rejected without changing remote metadata, even if its schema happens to match the baseline. Partial squashing is not supported: re-baselining always replaces the full history for one namespace. ## `migration set` Semantics `tailor tailordb migration set ` updates the `sdk-migration` label on the deployed namespace's metadata. **It does not modify any data or schema.** It only changes which migrations the next `apply` will consider pending. The command also aligns the remote migration history ID with the local baseline, removing a stale ID when the local history predates re-baselining. The migration number is validated before anything is sent to the remote: it must be a 4-digit value (e.g. `0001`) or a bare integer (e.g. `1`) within 0–9999, and must exist in the working tree's migration history, which is itself validated (a gapped history is rejected). `0` is always accepted as the baseline (even when no migrations directory exists yet), provided the history passes validation. | Movement | Effect on next `apply` | Effect on data | | -------------------------------- | ------------------------------------------------------------------------- | ------------------------------------------------------------- | | Forward (e.g., `0001` → `0003`) | Migrations `0002` and `0003` are skipped — they will not run. | None. | | Backward (e.g., `0003` → `0001`) | Migrations `0002` and `0003` become pending and will re-execute on apply. | None directly — but the re-executed scripts may rewrite data. | Use cases: - **Recovery from drift** — you investigated, manually fixed the remote, and want the SDK's bookkeeping to reflect reality. - **Re-running a faulty migration** in a development workspace — set backward, fix `migrate.ts`, apply. - **Skipping a migration** that you know was already applied out-of-band. `migration set` does not perform a true rollback. To undo a schema/data change in production, write a new forward migration that reverses it (see [Rollback Strategy](#rollback-strategy)). ## `migration sync` Semantics `tailor tailordb migration sync ` reconstructs the schema snapshot at migration `N` from the working tree's migration history and **overwrites the remote schema to match it**, then sets the `sdk-migration` label to `N` and aligns the remote migration history ID with the local baseline. Unlike `migration set`, it changes the remote schema as well as the bookkeeping. Like `set`, it never runs `migrate.ts` scripts itself — it only changes what the next `apply` considers pending: | Movement | Effect on next `apply` | Effect on data | | -------------------------------- | ----------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------- | | Backward (e.g., `0003` → `0001`) | Migrations `0002` and `0003` become pending and re-execute, including their `migrate.ts`. | Tables absent from snapshot `0001` are deleted along with their data; re-executed scripts may rewrite data. | | Forward (e.g., `0001` → `0003`) | Migrations `0002` and `0003` are skipped — their `migrate.ts` scripts will not run. | Data the skipped scripts would have migrated stays as-is. | Before anything is sent to the remote, `sync` verifies that replaying the full migration history reproduces the current local table definitions. If it does not — because migration files were edited and no longer match, or because a schema change has not been recorded with `migration generate` yet — the command fails without touching the remote. This means a rewritten migration history is validated before it can overwrite the deployed schema. Because syncing backward causes already-applied scripts to re-execute on the next deploy, **write `migrate.ts` scripts to be idempotent** (see [Performance and Large Tables](#performance-and-large-tables) for resumable `where` clauses). The main use case is recovering from drift after a `deploy --no-schema-check` from an older revision: instead of checking out that revision, run `migration sync ` to restore the remote to a known snapshot, then `tailor deploy` to apply the remaining migrations from the working tree. ## Team Workflow and CI/CD ### Branch coordination Migration numbers are assigned sequentially, so two developers branching off the same point and each generating `0005` will collide. Conventions that work: - **Don't generate migrations on long-lived feature branches.** Generate them just before merge, after rebasing onto main. - **Resolve collisions by re-generating.** If your branch has `0005` but main now has `0005` from another PR, regenerate yours as `0006` — see [Resolving a migration number conflict](#resolving-a-migration-number-conflict). - **Treat migration files as merge-conflict-prone.** They are committed JSON and TypeScript, so review them in PRs. The `diff.json` is the source of truth — if review focuses there, regenerating after rebase is straightforward. ### Resolving a migration number conflict When your branch and main each generated the same number, merging or rebasing stops with an add/add conflict on `migrations/0005/diff.json`. Resolve it by re-generating your migration on top of main's: 1. **Save your script edits aside.** If you customized `0005/migrate.ts`, keep a copy before touching the directory — during a rebase, `git show ORIG_HEAD:migrations/0005/migrate.ts` prints the version from your pre-rebase branch tip. 2. **Take main's `0005/` directory in full.** Accept main's version of every conflicting file. Then check for files only your side added: if your migration has a `migrate.ts` and main's does not, that file never conflicts — it silently stays next to main's `diff.json`. Delete such leftovers explicitly. 3. **Finish the rebase or merge, then re-run `migration generate`.** With main's migration now part of local history, the diff is computed against the correct base — including main's changes — and your migration lands as the next number (`0006`). 4. **Port your script.** Copy the logic saved in step 1 into the newly scaffolded `0006/migrate.ts`. For a warning-tier change, `migration generate` does not scaffold a script — recreate it first with `tailor tailordb migration script 0006`. **When a plain rename is enough.** If the two migrations touch disjoint tables and fields, renaming your directory to the next free number (keeping main's `0005/`) can be acceptable. Run `tailor tailordb migration validate` after the rename: if it reports a mismatch, the migrations were not disjoint — discard the rename and re-generate as above. A passing check covers only the schema history, not your script: `migrate.ts` now runs after main's migration, so confirm it does not read or write tables that migration touches — when in doubt, re-generate. ### CI / CD - For non-interactive environments, pass `--yes` to `migration generate` and `--yes` to `apply`. `apply` runs migrations automatically when the `migrations/` directory is configured. - Run `tailor tailordb migration validate` in CI to catch uncommitted migrations, broken migration files, unreviewed generated normalization logic, and remote schema drift before deploying. It exits with a non-zero code when validation fails and supports `--json`. Add `--strict` to also require an explicit acknowledgment (a `migrate.ts` or a recorded `--no-script` reason) for every pending migration that can drop data, so destructive changes cannot merge unnoticed. - `tailor tailordb migration status` validates file-format compatibility across the full local history, compares its history ID with the deployed namespace, and shows applied and pending migrations for a human-readable comparison. Its exit code is non-zero on incompatible files, migration history mismatches, and remote read errors, so check the output. - Avoid running migrations in parallel against the same workspace — there is no locking. Serialize deploys per environment. ### Resetting a deployed project Use `tailor tailordb migration rebaseline` rather than combining `migration generate --init` with a manual checkpoint change. See [Re-baselining a deployed migration history](#re-baselining-a-deployed-migration-history) for the required cross-environment coordination and verification. ## Failure Recovery If the pre-migration phase or `migrate.ts` fails: - **The transaction rolls back** for that migration's script. Database changes the script made are undone. - **The pre-migration schema changes are rolled back** to the prior checkpoint: tables that already existed are restored to their previous shape, and tables the migration newly introduced are dropped. The workspace is left at its prior checkpoint and prior schema — not half-applied. - The whole `apply` aborts and the checkpoint label is not bumped. Subsequent migrations in the same run do not execute. The rollback is best-effort per table; if reverting a table fails, a warning is logged and the original migration error is still reported. After a failure: 1. Read the `Logs:` block in the apply output to find the cause. 2. Fix `migrate.ts` (or the data it depends on). 3. Re-run `tailor deploy`. The same migration runs again because its label was never bumped, and the prior-checkpoint schema is a clean baseline to retry against. If a migration **succeeds in script** but its reversible **post-migration schema update** fails (rare; usually a constraint violation the script should have prevented), the SDK makes the same best-effort restoration to the prior-checkpoint schema. The script's committed data changes remain, so write migration scripts to tolerate re-execution. The checkpoint is advanced only after the reversible post-migration schema updates succeed. If the checkpoint write reports an error, the SDK reads it back: a matching value is treated as committed. Any other observed value leaves the post-migration schema unchanged rather than risk rolling back a concurrent deployment; a value beyond the current migration confirms a concurrent deploy, while an older or missing value means the checkpoint must be repaired before retrying. If read-back also fails, the SDK likewise leaves the post-migration schema unchanged; verify the remote checkpoint before retrying. Removed tables are deleted only after the checkpoint is committed. If that cleanup fails, the checkpoint remains at the new migration and the SDK fails closed: the leftover table is reported as remote schema drift on the next deploy. Remove the leftover GQL permission and table manually, verify the remote schema, and then retry. The SDK does not automatically ignore or delete a same-named remote table because it cannot distinguish failed cleanup from a table recreated after cleanup completed. ## Rollback Strategy There is no automatic down-migration. To roll back a schema/data change in production, write a new forward migration that reverses the previous one. For example, to undo a `0005` that added a required `email` field: 1. Edit your table definitions to remove the field. 2. `migration generate --name "rollback 0005 email"` produces `0006` with a removal diff. 3. Apply. In **development workspaces**, a quicker option is to fix `0005/migrate.ts` in place, run `migration set ` to re-mark it pending, and apply. Do not do this on production — it confuses migration history across environments. ## Machine User and Permissions Migration scripts execute server-side under a machine user identity. The CLI selects the user in this priority order: 1. `db..migration.machineUser` if set in `tailor.config.ts`. 2. The first entry in `auth.machineUsers` otherwise. The CLI logs the selected user before running scripts (`Using machine user: ...`). **Permissions required** The machine user needs read/write access to every table the migration script touches. If your migrations alter data across multiple tables, the simplest path is to give the migration user broad access (e.g., an `ADMIN` role) and restrict day-to-day machine users separately. If the user lacks permission, the script fails with a permission error in `Logs:`. If you see `No machine user available for migration execution`, either: - Add `machineUsers: { ... }` to your auth config and `tailor deploy` it, or - Set `migration.machineUser` to an existing machine user name in the db config. ## Multi-Namespace Coordination If your project defines multiple TailorDB namespaces (`db: { ns1: { ... }, ns2: { ... } }`), each has its own `migrations/` directory and its own migration label. During `apply`: - Migrations are grouped by namespace and executed namespace by namespace. - Within a namespace, migrations run sequentially in number order. - There is no cross-namespace ordering guarantee. Do not write a migration in `ns1` that depends on data produced by a migration in `ns2` running first. - Each namespace can specify its own `migration.machineUser`. ## Performance and Large Tables The migration script runs in a single transaction. For tables with many rows: - Prefer set-based SQL (`updateTable(...).set(...).where(...)`) over per-row loops. - If a per-row loop is unavoidable, batch by primary key range. Avoid `OFFSET`-based pagination — it scans previously-seen rows on every page. - Long-running transactions can hit platform timeouts and hold locks. For very large backfills, consider splitting the work across multiple migrations, each operating on a subset. - Add `LIMIT` and resumability (idempotent `where` clauses) so a re-run after a transient failure converges. ### Migrations that take longer than a minute Migration scripts run as workflow jobs, so they are not bound by the 60-second limit that applies to synchronous script execution. `tailor deploy` waits for the migration and reports its logs as usual. Execution time is still bounded — a workflow job has its own, far longer, execution-time limit. The script still runs in a single transaction, so the guidance above about locks and transaction size continues to apply — prefer splitting a very large backfill across several migrations over holding one transaction open for a long time. ## Testing Migrations Locally ### Unit-testing migrate.ts `main` is a plain function, so you can unit-test it with Vitest before the first deploy ever runs it. `createKyselyMock` from `@tailor-platform/sdk/vitest` compiles queries to the same SQL as the deployed migration, so a test verifies the exact statements the script issues — SQL, parameters, and order. Type the mock with the `Database` interface exported from the generated `db.ts`. Scaffold a ready-to-fill test next to the script with: ```bash tailor tailordb migration script 0005 --with-test ``` When `migrate.ts` already exists (the usual case for breaking changes, where `migration generate` creates it), the command adds only the tests that do not exist yet, plus a missing `db.pglite.ts`. Or write the test by hand: ```typescript // migrations/0005/migrate.test.ts import { createKyselyMock } from "@tailor-platform/sdk/vitest"; import { describe, expect, test } from "vitest"; import type { Database } from "./db"; import { main } from "./migrate"; describe("0005 add required email", () => { test("backfills null emails", async () => { const mock = createKyselyMock(); await mock.withTx((trx) => main(trx)); expect(mock.updates).toHaveLength(1); expect(mock.updates[0]?.updateValues()).toEqual({ email: "unknown@example.com" }); expect(mock.updates[0]?.sql).toContain('where "email" is null'); }); }); ``` Stage the rows each query returns with `mock.enqueueResult(...)` or `mock.setQueryResolver(...)` when the script reads before writing; call `main(trx, { env: { ... } })` when the script takes a `MigrationContext`. See [Kysely-layer mock](../testing.md#kysely-layer-mock-createkyselymock) for the full mock API. These tests need no platform connection and no `tailor-runtime` environment — they run in a plain Vitest setup. Vitest's default `include` pattern already picks up `migrations/**/migrate.test.ts`; if your config narrows `include`, add the migrations directory. The test file is ignored by `tailor deploy` and never ships to the platform. ### Executing migrate.ts against a local Postgres (PGlite) A statement-level test verifies what the script issues, not what it does to data (e.g., whether a `where` clause matches the rows you intended). To run `main` against real rows locally, back Kysely with [`@electric-sql/pglite`](https://pglite.dev/) — an in-memory PostgreSQL — via `createKyselyPGlite` from `@tailor-platform/sdk/vitest`: ```bash npm install -D @electric-sql/pglite ``` The generated `db.pglite.ts` exports the `CREATE TABLE` script for the same schema `db.ts` types — the tables as the pre-migration phase leaves them while `migrate.ts` runs, including relaxed constraints, renamed fields under both names, and retained removed fields. Run it once on the PGlite instance, stage rows, then run the script in a transaction. `tailor tailordb migration script --with-test` scaffolds this test too when `@electric-sql/pglite` is installed. Type the instance with `Unmigrated` rather than `Database`: `db.ts` types a column the migration makes required as `T | null` on read but `T` on write (and an enum it narrows as the old values on read but the new ones on write), so that `migrate.ts` cannot write what the migration is removing — which would also stop the test from staging the rows the script has to convert. `Unmigrated` lets every column be written with whatever it can still be read as; `main` still receives a `Transaction`. ```typescript // migrations/0005/migrate.pglite.test.ts import { PGlite } from "@electric-sql/pglite"; import { createKyselyPGlite, type Unmigrated } from "@tailor-platform/sdk/vitest"; import { afterAll, beforeAll, describe, expect, test } from "vitest"; import type { Database } from "./db"; import { pgliteSchema } from "./db.pglite"; import { main } from "./migrate"; const pglite = new PGlite(); const db = createKyselyPGlite>(pglite); // PGlite loads Postgres on first use, which can take longer than the default hook timeout. beforeAll(async () => { await pglite.exec(pgliteSchema.tailordb); }, 60_000); afterAll(async () => { await db.destroy(); }); describe("0005 add required email", () => { test("backfills null emails and keeps existing ones", async () => { const now = new Date(); await db .insertInto("User") .values([ { name: "a", email: null, createdAt: now, updatedAt: now }, { name: "b", email: "b@example.com", createdAt: now, updatedAt: now }, ]) .execute(); await db.transaction().execute((trx) => main(trx)); const rows = await db.selectFrom("User").select(["name", "email"]).orderBy("name").execute(); expect(rows).toEqual([ { name: "a", email: "unknown@example.com" }, { name: "b", email: "b@example.com" }, ]); }); }); ``` Pass nested field values as JavaScript objects or arrays of objects, without `JSON.stringify`. Generated migration types use `Record` for each nested object so scripts can work with both old and new members during a migration; narrow member values before using them. Two caveats keep this from replacing a scratch workspace: - PGlite runs full PostgreSQL, while TailorDB supports [a subset of it](https://docs.tailor.tech/guides/function/accessing-tailordb#supported-sql-queries) — a statement that passes here can still be rejected on deploy. - `db.pglite.ts` mirrors the column shape, not the platform: hooks, validations, and permissions do not run, and the limits listed under [Real SQL execution with PGlite](../testing.md#real-sql-execution-with-pglite-mocktailordbwithpglite) apply. ### Beyond unit tests A unit test verifies which statements the script issues; a PGlite test verifies what they do to the rows you staged. Neither runs against your actual data. To cover that: - Run `migration generate` on a clean working copy first, review `diff.json`, then run again after editing tables to ensure the diff matches what you intended. - For non-trivial migrations, apply against a scratch workspace before promoting to staging or production. ## Environment-Specific Strategies A migration script is a function — branching on environment (e.g., to skip a backfill in dev) is just normal TypeScript. For environment awareness, use the `env` values defined in `defineConfig({ env })`, exposed via the optional second argument `{ env }: MigrationContext` (see [Migration Script Anatomy](#migration-script-anatomy)). These are resolved at deploy time and inlined into the bundle. Do not read `process.env` inside `migrate.ts` — Node-side environment access is unavailable at runtime; only the injected `env` (and the data itself) reflect the target environment. For genuinely different schemas across environments, prefer separate workspaces with the same migration history rather than divergent `migrations/` directories. ## Troubleshooting ### Remote schema drift detected **Cause:** Remote schema doesn't match what the migration history says it should be. **Resolution:** 1. `tailor tailordb migration status` to see local vs remote. 2. Compare with teammates — has someone applied different migrations? 3. If remote was changed manually, decide whether to update local migrations to match or to use `migration set ` to align bookkeeping. 4. To force the remote schema back to a known snapshot, use `migration sync ` (see [`migration sync` Semantics](#migration-sync-semantics)). 5. As a last resort in non-production environments, `--no-schema-check` skips both checks. Do not use this as a routine workaround. ### "Remote migration checkpoint is not in the local migration history" error **Cause:** The deployed namespace's checkpoint refers to a migration number, or a migration history ID, that the local `migrations/` directory no longer has a record of. This is a different code path from schema drift above: it fires before any schema comparison, because the CLI cannot even locate the remote's recorded position in the local history. One specific cause is a `migration rebaseline` run on a different environment while this one had not yet caught up to the latest pre-rebaseline migration. `migration rebaseline` requires every other environment to already be at the latest migration (see [Re-baselining a deployed migration history](#re-baselining-a-deployed-migration-history)), but the CLI only verifies the one workspace it runs against. If this environment was behind, the error message names the migration this environment must reach (the migration every environment was required to be at before the rebaseline) alongside the migration it is actually at. **Resolution (fell behind before a rebaseline):** 1. Restore the pre-rebaseline `migrations/` directory from git history — check out the commit before `migration rebaseline` ran. Re-baselining removes migration files after `0000` from the working tree but Git history retains everything that was committed. 2. Deploy this environment against that restored history until its checkpoint reaches the migration named in the error, running any `migrate.ts` scripts it still needs. 3. Switch back to the current (rebaselined) migration files and deploy again. The remote schema now matches the new baseline, so this deploy offers the automatic checkpoint reset to `0000` under the new history ID. **Resolution (any other cause):** Run `tailor tailordb migration status` to compare local and remote, or pull the latest migration files if your checkout is stale. ### "Invalid schema snapshot" or "Invalid migration diff" error **Cause:** A `schema.json` or `diff.json` file in the `migrations/` directory is corrupted or does not match the expected structure. Merge conflicts left in these files are a common cause. **Resolution:** 1. Read the error message — it includes the file path and the offending field. 2. Restore the file from version control (`git checkout -- `), or regenerate migration files with `migration generate` / `migration script`. 3. Do not hand-edit `schema.json` or `diff.json`; they are managed by the CLI. ### "Unsupported migration file format version" error **Cause:** A `schema.json` or `diff.json` file is older or newer than the format versions supported by the installed SDK. **Resolution:** Follow the ordering in the error message. For an older history, restore an SDK version that can read every file, run `migration rebaseline`, commit and deploy the new baseline everywhere, and then upgrade. For a file produced by a newer SDK, upgrade the SDK that is reading it. Do not hand-edit the version field. ### "No machine user available for migration execution" **Cause:** Neither `migration.machineUser` is set nor are there any machine users in `auth.machineUsers`. **Resolution:** Add a machine user to auth, apply auth changes, then re-run. ### "Machine user not found" **Cause:** `migration.machineUser` references a name that doesn't exist in the deployed auth config. **Resolution:** Either add the machine user to `auth.machineUsers` and apply, or change `migration.machineUser` to a valid name. ### Migration script execution fails **Cause:** Runtime error in your `migrate.ts`, a permission error from the machine user, or a constraint violation when post-migration tightens tables. **Resolution:** Read the `Logs:` block. Fix the script or the data assumption it relies on, and re-run `tailor deploy`. The label is not bumped on failure, so the same migration retries. ### `migrate.ts` not found for a migration that needs one **Cause:** `diff.requiresMigrationScript` is true but `migrate.ts` is missing from the migration directory. **Resolution:** Restore the file from version control, or create it with `tailor tailordb migration script --namespace `. If the migration intentionally needs no data transformation, record that decision with `tailor tailordb migration script --namespace --no-script --reason ""` instead.