Migrations

Sp00ky includes a built-in migration system for managing incremental, version-controlled changes to your SurrealDB schema. Instead of applying your full schema on every deploy, migrations let you evolve your database safely over time — locally, in CI, and on Sp00ky Cloud.

How It Works

You write your schema in .surql files
When you change the schema, spky migrate create diffs your source against the current migrations and generates a new migration file
spky migrate apply runs any pending migrations in order against your database
Each applied migration is tracked in a _00_migrations table with a SHA-256 checksum, so drift and re-application are detected automatically

Migrations are applied individually (SurrealDB DDL does not support transactions). All generated DEFINE statements include the OVERWRITE keyword for idempotency.

Migration Engine

Sp00ky supports two migration engines. Set migrationEngine in your sp00ky.yml to choose:

yaml

# sp00ky.yml
migrationEngine: surrealkit   # "legacy" (default) or "surrealkit"

surrealkit:
  binary: /path/to/surrealkit  # optional, defaults to "surrealkit" in PATH

Engine	Description
`legacy`	(default) Built-in engine with auto-diff, SHA-256 checksum tracking, and ephemeral SurrealDB for schema comparison. Described in detail below.
`surrealkit`	Delegates to the external SurrealKit CLI. Uses `surrealkit sync` (declarative push) during `spky dev` and `surrealkit rollout` (phased migrations) for production deploys.

Note

When using surrealkit, install it with cargo install surrealkit or set the binary path in sp00ky.yml. The CLI verifies the binary exists before running any migration command.

The rest of this page documents the legacy engine. If you’re using SurrealKit, refer to the SurrealKit documentation for engine-specific commands — all spky migrate subcommands (create, apply, status, fix) are routed through whichever engine you’ve selected.

Creating Migrations

Auto-Diff (Recommended)

The primary way to create a migration is auto-diff — Sp00ky compares your current source schema against the state defined by existing migrations and generates only the changes:

Bash

spky migrate create "add_user_avatar"

Under the hood this:

Builds the new schema from your source files (schema + backend method schemas)
Replays all existing migrations through an ephemeral SurrealDB to get the old schema
Diffs old vs new to find additions, removals, and modifications
Writes only the diff as a new migration file with OVERWRITE statements

If there are no changes, it prints a message and creates nothing.

Example output:

text

Created migration: 20240215093000_add_user_avatar.surql
  /path/to/migrations/20240215093000_add_user_avatar.surql
  +2 addition(s), -0 removal(s), ~0 modification(s)

Note

Auto-diff requires Docker to be running — it uses an ephemeral SurrealDB container for schema normalization.

Auto-Diff Against a Live Database

If you have a running SurrealDB, you can diff against it directly instead of replaying migrations:

Bash

spky migrate create "add_user_avatar" \\
  --url http://localhost:8000 \\
  --namespace main \\
  --database main

This extracts the live schema and diffs it against your source — useful when the database has drifted from what migrations expect.

Empty Migration

For manual control, create an empty migration template:

Bash

spky migrate create "manual_fix" --empty

This creates a file with a placeholder comment that you fill in yourself.

From Existing Schema

If you’re bootstrapping migrations for the first time, prepopulate from an existing schema file:

Bash

spky migrate create "bootstrap" --schema ./schema.surql

For the very first migration, the full schema is the incremental change from an empty database, so this is typically used as-is.

Migration File Format

Files follow a strict naming convention:

text

{YYYYMMDDHHmmss}_{name}.surql

text

migrations/
  20240101120000_bootstrap_schema.surql
  20240215093000_add_user_avatar.surql
  20240301140000_create_indexes.surql

Each file contains SurrealQL statements. Auto-generated migrations include OVERWRITE for idempotency:

sql

-- Migration: add_user_avatar
-- Auto-generated by spky migrate create

DEFINE FIELD OVERWRITE avatar ON TABLE user TYPE option<string>;
DEFINE FIELD OVERWRITE avatar_updated_at ON TABLE user TYPE option<datetime>;

The name is automatically sanitized — spaces and hyphens become underscores, uppercase becomes lowercase, and special characters are stripped.

Applying Migrations

Run all pending migrations against your database:

Bash

spky migrate apply

Migrations execute in timestamp order. Each applied migration is recorded in the _00_migrations tracking table with its version, name, timestamp, and SHA-256 checksum.

Connection Options

Bash

spky migrate apply \\
  --url http://localhost:8000 \\
  --namespace myapp \\
  --database production \\
  --username admin \\
  --password secret

All connection flags can also be set via environment variables:

Variable	Default	Description
`SURREAL_URL`	`http://localhost:8000`	SurrealDB endpoint
`SURREAL_NS`	`main`	Namespace
`SURREAL_DB`	`main`	Database
`SURREAL_USER`	`root`	Username
`SURREAL_PASS`	`root`	Password

CLI flags take precedence over environment variables.

Checking Status

Bash

spky migrate status

Example output:

text

Migration Status:

  [applied]  20240101120000_bootstrap_schema       (applied 2024-01-01T12:00:00Z)
  [applied]  20240215093000_add_user_avatar        (applied 2024-02-15T09:30:00Z)
  [pending]  20240301140000_create_indexes
  [DRIFT]    20240215093000_add_user_avatar        (checksum mismatch!)

Status detects:

Pending — migration exists on disk but hasn’t been applied yet
Drift — a migration file was modified after it was applied (checksum mismatch)
Missing — a migration was applied but the file no longer exists on disk

Fixing Drift

When checksums don’t match (e.g., you intentionally edited an applied migration), use the fix command:

Bash

spky migrate fix --fix-checksums

This does two things:

1. Updates checksums — For each applied migration where the on-disk file has changed, the stored checksum is updated to match the current file content.

2. Detects schema drift — Replays all migrations through an ephemeral SurrealDB, then compares the expected schema against your live database. If they differ, it generates a corrective migration:

text

Schema drift detected:
  +1 addition(s), -0 removal(s), ~1 modification(s)

Generated corrective migration:
  migrations/20240301150000_corrective_fix.surql

Review the file and run: spky migrate apply

Warning

Always review generated corrective migrations before applying — they may contain destructive changes like REMOVE FIELD statements.

Migrations During `spky dev`

When you run spky dev, the CLI handles migrations automatically:

1. Schema drift check — Before starting infrastructure, Sp00ky diffs your source schema against existing migrations. If changes are detected, you’re prompted to generate a new migration:

text

Schema changes detected:
  +2 addition(s)

  > Generate migration
    Continue anyway
    Abort

2. Apply pending migrations — After infrastructure starts and SurrealDB is healthy, pending migrations are detected. You’re prompted to apply them:

text

2 pending migration(s):
  20240215093000_add_user_avatar
  20240301140000_create_indexes

  > Apply migrations
    Skip migrations
    Quit

If migration application fails (e.g., syntax error in a migration), you can choose to reset the database and retry.

Flags

Flag	Effect
`--skip-migrations`	Skip the entire migration phase
`--apply-migrations`	Auto-apply pending migrations without prompting
`--fix-checksums`	Auto-fix checksum mismatches before applying

Bash

spky dev --apply-migrations --fix-checksums

3. Internal schema — After user migrations, Sp00ky automatically applies its internal schema (meta tables, per-table events for record versioning, and remote functions). This is idempotent and not tracked in _00_migrations.

Migrations During Cloud Deploy

When you run spky deploy, migrations are applied automatically as part of the deployment flow:

Infrastructure is provisioned (SurrealDB, SSP, Scheduler VMs)
The CLI waits for SurrealDB to become reachable on its public URL
Migrations are applied from your local migrations/ directory against the cloud database
Internal schema and remote functions are applied to the cloud SSP/Scheduler
Deployment is finalized and app containers start

No user interaction is needed — migrations run automatically. If your migrations/ directory doesn’t exist, the migration step is skipped.

Note

Cloud deploy applies migrations from your local machine to the cloud database. Make sure your local migrations directory is up to date before deploying.

The same migration flow also runs after spky backup reset — after the database is wiped, migrations are re-applied to rebuild the schema.

Safety Features

SHA-256 checksums — Every migration is hashed when applied. Modifications after application are detected and block further applies until resolved.
Drift detection — status and fix detect when applied migrations have been modified or when the live database has diverged from what migrations define.
Ordered execution — Migrations always run in chronological order by their timestamp prefix.
Idempotent generation — Auto-generated migrations use DEFINE ... OVERWRITE so they can be safely re-applied if needed.
Unique tracking — The _00_migrations table has a unique index on version, preventing duplicate application.

Warning

SurrealDB DDL does not support transactions. If a migration fails partway through, the database may be in a partial state. Use spky migrate fix to detect and correct drift.

CLI Reference

Bash

spky migrate <COMMAND>

Commands:
  create <name>      Generate a new migration file (auto-diff by default)
    --empty              Create empty template instead of auto-diff
    --schema <path>      Pre-populate from existing schema file
    --migrations-dir     Override migrations directory
    --url/--namespace/--database/--username/--password
                         Diff against live DB instead of ephemeral

  apply                Apply all pending migrations
    --url/--namespace/--database/--username/--password
                         Connection options (or use SURREAL_* env vars)
    --migrations-dir     Override migrations directory

  status               Show applied/pending/drift status
    --url/--namespace/--database/--username/--password
    --migrations-dir

  fix                  Detect and fix schema drift
    --fix-checksums      Update stored checksums to match disk
    --url/--namespace/--database/--username/--password
    --migrations-dir

Migrations

How It Works

Migration Engine

Creating Migrations

Auto-Diff (Recommended)

Auto-Diff Against a Live Database

Empty Migration

From Existing Schema

Migration File Format

Applying Migrations

Connection Options

Checking Status

Fixing Drift

Migrations During spky dev

Flags

Migrations During Cloud Deploy

Safety Features

CLI Reference

Migrations During `spky dev`