Migrations

Migrations are numbered .sql files kept in your repository. The CLI, the Node.js SDK and the MCP server each apply them in order, inside a transaction, and record what ran in a migrations table in your project database. All three use the same file format and the same table, so you can mix them.

Where to run them

Studio doesn't run migrations. SQL Editor → Migrations shows the registry table read-only, with counts of total, applied and failed migrations. It shows "No migrations yet" until a tool has created the table.

Migration files

• Files live in one directory, migrations by default, resolved from the current working directory. Change it with --dir in the CLI, the migrationsDir option in the Node.js SDK, or the migrationsDir argument or ORBITNEST_MIGRATIONS_DIR environment variable in the MCP server.
• A file name is a numeric id, then _ or -, then a name, ending in .sql: 001_create_posts.sql, 002-add-tags.sql. The id is the numeric prefix (001). Files are applied in numeric order, so 10 comes after 9. Ids must be unique. .sql files without a numeric prefix are ignored.
• A line that is exactly -- migrate:down, -- down or --- DOWN --- (any case, extra dashes allowed) splits the file. Everything above it is the forward (up) section, everything below it is the rollback (down) section. The down section is optional. A comment that only starts with "down", such as -- Down-sampled metrics, is not a marker. This whole-line rule applies from Node.js SDK 0.13.0, CLI 0.6.0 and MCP server 1.4.0; older versions split on any comment line starting with down.
• The checksum is a SHA-256 hash of the whole file. Don't edit a migration after it has been applied: the next run stops with a checksum-mismatch error. Add a new migration instead.
• Each section is sent as one script wrapped in BEGIN; and COMMIT;. If any statement fails, the whole section rolls back. Statements that can't run inside a transaction, such as CREATE INDEX CONCURRENTLY, aren't supported.
• A section must pass the API's SQL checks and be under 50,000 characters. Write plain statements separated by semicolons; no DO block is needed.
• Migrations can't drop, truncate, alter or DELETE FROM the auth_* tables. Foreign keys to auth_users(id) and triggers on it are fine.

-- Migration 001: create posts
CREATE TABLE posts (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id    uuid REFERENCES auth_users(id) ON DELETE CASCADE,
  title      text NOT NULL,
  published  boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX posts_user_created_idx ON posts (user_id, created_at DESC);
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- migrate:down
DROP TABLE IF EXISTS posts;

The registry table

The first run creates this table if it doesn't exist:

CREATE TABLE IF NOT EXISTS migrations (
  migration_id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  checksum TEXT NOT NULL,
  executed_at TIMESTAMP DEFAULT NOW(),
  status TEXT NOT NULL
);

status is success or failed. A migration with no row is pending.

If your database already has a migrations table from another tool, such as TypeORM or Knex, the Node.js SDK stops with A "migrations" table already exists but was not created by OrbitNest. Rename or drop that table before running OrbitNest migrations.

Create a migration

Both commands create the next numbered file, padded to three digits, with an up section and a -- migrate:down section stubbed out. Neither overwrites an existing file.

orbitnest migration new "create posts"
# Created migration 001: migrations/001_create_posts.sql

const { migrationId, path } = client.migrations.createMigration('create posts');

Apply with the CLI

orbitnest login
orbitnest link my-app

orbitnest db push            # apply every pending migration
orbitnest db status          # applied, pending or modified
orbitnest db rollback        # run the down section of the latest applied migration
orbitnest db rollback 002    # or of a specific one

See CLI → Migrations for every flag.

Apply with the Node.js SDK

Migrations run DDL, so create the client with a service-role key on a trusted machine, such as a CI job or a deploy script.

import { createClient, MigrationError } from '@orbitneststudio/node';

const admin = createClient({
  apiKey: process.env.ORBITNEST_SERVICE_KEY!,
  migrationsDir: 'migrations',
});

try {
  const result = await admin.migrations.runAll();
  console.log('ran', result.ran, 'skipped', result.skipped);
} catch (err) {
  if (err instanceof MigrationError) {
    console.error(err.message, err.result?.failed);
    process.exit(1);
  }
  throw err;
}

Failures throw MigrationError. When a run stops partway, error.result holds the partial { ran, skipped, failed }.

Reset with the CLI

orbitnest db reset requires --yes. From CLI 0.6.0 it then does a dry run first: it sends the drops and every migration's up section inside one transaction that is rolled back. If any migration fails to apply to the empty schema, nothing is dropped and the command stops with Reset aborted — nothing was dropped. Only after the dry run succeeds does it drop the tables and run db push. The dry run is one request, so the drops and all up sections together must fit in 50,000 characters.

orbitnest db reset --yes

Apply with the MCP server

Ask your AI assistant to run or inspect migrations. The tools read files from the directory where the MCP server runs.

The MCP server has no rollback tool or reset tool. Use the CLI or the Node.js SDK for those.

How runs behave

• Migrations run one at a time. A run stops at the first failure, records that migration as failed, and the next run retries it.
• A migration recorded as success is never run again.
• If an applied file's checksum no longer matches, the run stops before applying anything new. The CLI's db status shows that file as modified.
• Rollback runs the down section in a transaction, then removes the migration's row so it can be applied again.
• The Node.js SDK refuses to start a second run in the same process while one is in progress. Nothing locks across processes, so run migrations from a single place, such as one CI step.

Detect schema drift

To catch changes made outside migrations, save a snapshot of the live schema and compare against it later. orbitnest db diff exits with code 1 when the schema has drifted, so you can use it as a CI check.

orbitnest db diff --save   # write .orbitnest/schema.snapshot.json
orbitnest db diff          # show added, removed and changed tables and columns

See CLI → Schema drift.