Migrations

A migration is a recorded change to your schema. Changes you save on the console’s Schema page are recorded as migrations, and you can also keep SQL migration files in your repository and apply them with the CLI.

From the console

Make the change on the Schema page and save it. The page lists the project’s migrations. Open one to see what it changed, or to roll it back.

From SQL files with the CLI

Keep numbered .sql files in a folder in your repository, such as ./migrations:

-- migrations/0002_add_comments.sql
CREATE TABLE comments (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  post_id uuid REFERENCES posts(id) ON DELETE CASCADE,
  body text NOT NULL,
  created_at timestamptz DEFAULT now()
);

CREATE INDEX idx_comments_post ON comments (post_id);

vaif db push --dir ./migrations --dry-run   # show what each file would change
vaif db push --dir ./migrations             # apply the files, in order

For each file, the Schema Engine applies its table changes (tables, columns, indexes and keys, never a drop), then runs its data statements (INSERT, UPDATE, DELETE). Each file runs in a single transaction: if any statement fails, the whole file is rolled back, and the push stops there, because later files usually depend on it.

A file that was already applied unchanged is skipped; pass --force to apply it again. --env picks the environment, and pushing to a production environment also needs --allow-production, which only project owners and admins can use.

A SQL file can also create row rules with the -- vaif:owner and -- vaif:public-read lines.

History and rollback over the API

With a console access token:

• GET /schema-engine/migrations/project/{projectId} lists the project’s migrations.
• GET /schema-engine/migrations/{projectId}/{migrationId}/diff shows one migration’s changes.
• POST /schema-engine/migrations/{projectId}/{migrationId}/rollback rolls one back.
• POST /schema-engine/preview shows what a schema definition would change before you apply it.

Good practice

• Keep migrations small, and never edit one that has been applied.
• Review migration SQL as you review code.
• Try a migration on a staging environment before production.
• Take extra care with changes that drop or rewrite columns.