Migrations

Manage TailorDB schema changes safely with automated migration generation, TypeScript migration scripts, and transaction-wrapped data transformations

The migration system detects field-level schema differences between your local type definitions and the previous snapshot, then generates migration files to safely apply those changes with data transformations.

Overview

Key Features:

• Local snapshot-based diff detection between current types and previous migration snapshot
• Transaction-wrapped data migrations for atomicity - all changes commit or rollback together
• Automatic execution during apply - pending migrations run as part of tailor-sdk apply
• TypeScript migration scripts - type-safe data transformations using Kysely

Quick Start

1. Configure Migrations

Enable migrations in your tailor.config.ts:

tailor.config.ts

import { defineConfig } from "@tailor-platform/sdk";

export default defineConfig({
  name: "my-app",
  db: {
    tailordb: {
      files: ["./tailordb/*.ts"],
      migration: {
        directory: "./migrations",
        // Optional: specify machine user for migration script execution
        // If not specified, the first machine user from auth.machineUsers is used
        machineUser: "admin-machine-user",
      },
    },
  },
});

2. Modify Your Schema

tailordb/user.ts

import { db } from "@tailor-platform/sdk";

export const user = db.type("User", {
  name: db.string(),
  email: db.string(), // ← New required field
  ...db.fields.timestamps(),
});

3. Generate Migration

tailor-sdk tailordb migration generate

Output:

Generated migration 0001
  Diff file: ./migrations/0001/diff.json
  Migration script: ./migrations/0001/migrate.ts
  DB types: ./migrations/0001/db.ts

4. Edit Migration Script

The generated migration script provides a template for data transformations:

migrations/0001/migrate.ts

import type { Transaction } from "./db";

export async function main(trx: Transaction): Promise<void> {
  // Populate the new required field with default values
  await trx
    .updateTable("User")
    .set({ email: "[e-mail]" })
    .where("email", "is", null)
    .execute();
}

The db.ts file contains Kysely Transaction types that reflect the schema state before the migration runs. This ensures type-safe data transformations based on the actual database structure at that point in time.

5. Apply Migration

tailor-sdk apply

Migrations are automatically executed during apply. You’ll see output like:

ℹ Found 1 pending migration(s) to execute.
ℹ Using machine user: admin-machine-user

✔ Migration tailordb/0001 completed successfully

✔ All migrations completed successfully.
✔ Successfully applied changes.

CLI Commands

Generate Migration

# Generate migration for all schema changes
tailor-sdk tailordb migration generate

# Generate with a description
tailor-sdk tailordb migration generate --name "add email field to user"

# Generate without confirmation prompts
tailor-sdk tailordb migration generate --yes

# Delete existing migrations and start fresh
tailor-sdk tailordb migration generate --init --yes

Check Migration Status

# Show status for all namespaces
tailor-sdk tailordb migration status

# Show status for specific namespace
tailor-sdk tailordb migration status --namespace tailordb

Example output:

Namespace: tailordb
  Current migration: 0001
  Pending migrations:
    - 0002: Add email field to user
    - 0003: Remove deprecated status field

Set Migration Checkpoint

# Set migration checkpoint to 0001
tailor-sdk tailordb migration set 1 --yes

# Set for specific namespace
tailor-sdk tailordb migration set 2 --namespace tailordb

# Reset to initial state (all migrations become pending)
tailor-sdk tailordb migration set 0 --yes

Setting the migration checkpoint changes which migrations will be executed on next apply:

• Forward (e.g., 0001 → 0003): Skips migrations 0002 and 0003
• Backward (e.g., 0003 → 0001): Migrations 0002 and 0003 become pending and will re-execute

Migration Directory Structure

Migrations use a directory-based structure with 4-digit sequential numbering:

migrations/
├── 0000/                    # Initial schema (number 0)
│   └── schema.json          # Full schema snapshot
├── 0001/                    # First change
│   ├── diff.json            # Schema diff
│   ├── migrate.ts           # Migration script (if breaking changes)
│   └── db.ts                # Kysely types (if breaking changes)
├── 0002/                    # Second change
│   └── diff.json
└── ...

• 0000 - Initial schema snapshot (always starts at 0)
• 0001 - First schema change
• 0002 - Second schema change, etc.

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	No	Schema change only - data is preserved
Change optional→required	Yes	Yes	Script sets defaults for null values
Change required→optional	No	No	Schema change only
Add index	No	No	Schema change only
Remove index	No	No	Schema change only
Add unique constraint	Yes	Yes	Script must resolve duplicate values
Remove unique constraint	No	No	Schema change only
Add enum value	No	No	Schema change only
Remove enum value	Yes	Yes	Script migrates records with removed values
Add type	No	No	Schema change only
Remove type	No	No	Schema change only - data is preserved
Change field type	-	-	Not supported - requires 3-step migration
Change array to single value	-	-	Not supported - requires 3-step migration
Change single value to array	-	-	Not supported - requires 3-step migration
Change foreign key relationship	Yes	Yes	Script updates existing references

Unsupported Schema Changes

The following changes require a 3-step migration process:

Field Type Change

Field type changes (e.g., string → integer) are not directly supported. Use this migration strategy:

1. Migration 1: Add a new field with the desired type (e.g., fieldName_new) and migrate data
2. Migration 2: Remove the old field
3. Migration 3: Add the field with the original name and new type, migrate data from temporary field, then remove temporary field

Array Property Change

Changing between single value and array (e.g., array: false → array: true) is not directly supported. Use the same 3-step migration strategy as field type changes.

Migration Script Examples

Populate Required Field

migrations/0003/migrate.ts

import type { Transaction } from "./db";

export async function main(trx: Transaction): Promise<void> {
  // Populate role for existing User records
  await trx
    .updateTable("User")
    .set({
      role: "MANAGER",
    })
    .where("role", "is", null)
    .execute();
}

Populate Multiple Fields

migrations/0005/migrate.ts

import type { Transaction } from "./db";

export async function main(trx: Transaction): Promise<void> {
  // Set default name for Supplier records where it is null
  await trx
    .updateTable("Supplier")
    .set({
      name: "Unknown Supplier",
    })
    .where("name", "is", null)
    .execute();

  // Set default country for Supplier records where it is null
  await trx
    .updateTable("Supplier")
    .set({
      country: "Unknown",
    })
    .where("country", "is", null)
    .execute();
}

Resolve Duplicate Values

migrations/0006/migrate.ts

import type { Transaction } from "./db";

export async function main(trx: Transaction): Promise<void> {
  // Ensure name values are unique before adding constraint
  const duplicates = await trx
    .selectFrom("User")
    .select(["name"])
    .groupBy("name")
    .having((eb) => eb.fn.count("id"), ">", 1)
    .execute();

  // For each duplicate name, add suffix to make them unique
  for (const dup of duplicates) {
    const records = await trx
      .selectFrom("User")
      .select(["id", "name"])
      .where("name", "=", dup.name)
      .execute();

    // Keep first record as-is, add suffix to others
    for (let i = 1; i < records.length; i++) {
      await trx
        .updateTable("User")
        .set({ name: `${records[i].name}_${i}` })
        .where("id", "=", records[i].id)
        .execute();
    }
  }
}

Automatic Migration Execution

When running tailor-sdk apply, pending migration scripts are automatically executed as part of the deployment process.

How It Works

1. Pending Migration Detection: Identifies migration scripts that haven’t been executed yet
2. Two-Stage Type Update: For breaking changes (required fields, type changes):
   • Pre-Migration: New fields are added as optional first
   • Script Execution: Migration scripts run to populate data
   • Post-Migration: Fields are changed to required, deletions are applied

Execution Flow

tailor-sdk apply
    │
    ├── Detect Pending Migrations
    │
    ├── [If pending migrations exist]
    │   ├── Pre-Migration: Add fields as optional
    │   ├── Execute Migration Scripts (TestExecScript API)
    │   └── Post-Migration: Apply required, deletions
    │
    └── Continue with normal apply flow

Requirements for Automatic Migration

1. Migrations configured: migration.directory set in db config
2. Auth configured: Auth service with machine users
3. Kysely generator/plugin: @tailor-platform/kysely-type configured

Machine User Selection

When executing migration scripts, the system selects a machine user in the following priority:

1. Explicit configuration: migration.machineUser in db config
2. Auto-selection: First machine user from auth.machineUsers

The machine user being used is logged during migration execution.

Schema Verification

By default, tailor-sdk apply performs two schema verifications:

1. Local schema check: Ensures local type definitions match the migration snapshot
2. Remote schema check: Ensures remote schema matches the expected state based on migration history

If remote schema drift is detected, you’ll see an error like:

✖ Remote schema drift detected:
Namespace: tailordb
  Remote migration: 0007
  Differences:
  Type 'User':
    - Field 'email': required: remote=false, expected=true

ℹ This may indicate:
  - Another developer applied different migrations
  - Manual schema changes were made directly
  - Migration history is out of sync

ℹ Use '--no-schema-check' to skip this check (not recommended).

Skipping Schema Check

To skip both local and remote schema verification (not recommended for production):

tailor-sdk apply --no-schema-check

Skipping schema checks may result in applying migrations to an inconsistent state.

Editor Integration

If the EDITOR environment variable is set, the generated script file will automatically open in your preferred editor:

export EDITOR=vim
tailor-sdk tailordb migration generate
# → After generation, vim opens XXXX/migrate.ts

Troubleshooting

Remote schema drift detected

Cause: The remote schema doesn’t match the expected state based on migration history. This can happen when:

• Another developer applied different migrations
• Schema was changed manually outside of migrations
• Migration history is out of sync

Solution:

1. Check migration status: Run tailor-sdk tailordb migration status to see current state
2. Sync with team: Ensure all team members have the same migration files
3. Reset if needed: Use tailor-sdk tailordb migration set <number> to reset migration checkpoint
4. Force apply (use with caution): Use --no-schema-check to skip verification

”Machine user not found”

Cause: Specified machine user doesn’t exist in auth configuration.

Solution:

1. Check available users: The error message lists available machine users
2. Add machine user to your auth config:
   tailor.config.ts

   machineUsers: {
     "your-user-name": {
       attributes: { role: "ADMIN" },
     },
   }
   
3. Run tailor-sdk apply to deploy the auth config
4. Retry migration

Migration script execution fails

Cause: Runtime error in your data migration logic.

Solution:

1. Check the error message for the failing query/operation
2. Test your script logic locally if possible
3. Fix the script
4. Apply again: tailor-sdk apply

Best Practices

Always Review Generated Scripts

Don’t blindly apply migrations. Review and test migration scripts before deploying to production.

Use Transactions

All operations in migration scripts use the transaction object (trx) to ensure atomicity.

Test Migrations Locally

Test migration scripts in a development environment before applying to production.

Version Control

Commit migration files to version control and ensure team members are in sync.

Next Steps

• Learn about TailorDB for database schema definition
• Explore Generators for code generation from your schema
• Understand Testing strategies for your application