// Generate CockroachDB SQL and migrations for schema changes. Use when creating migrations, updating the database schema, or when the user mentions migrations, schema changes, or dbinit.sql.
| name | crdb-change |
| description | Generate CockroachDB SQL and migrations for schema changes. Use when creating migrations, updating the database schema, or when the user mentions migrations, schema changes, or dbinit.sql. |
Generate database changes for this repository. This includes changes to:
schema/crdb/dbinit.sqlFollow these steps in order. Do not skip ahead.
You MUST read schema/crdb/README.adoc first. Pay attention to important instructions and limitations, such as the requirement for idempotency and the inability to rename columns.
If not already provided, prompt the user whether they'd like to:
dbinit.sql.Check if .jj exists in the repository to determine whether to use jj or git commands.
Prompt the user to ask where the schema changes are:
Uncommitted changes: Changes not yet committed.
git diff -- schema/crdb/dbinit.sql (unstaged) or git diff --cached -- schema/crdb/dbinit.sql (staged)jj diff -- schema/crdb/dbinit.sqlThis commit only (stacked diff workflow): Changes are in the current commit only.
git diff HEAD^ -- schema/crdb/dbinit.sqljj diff --from @-- -- schema/crdb/dbinit.sqlThis branch (feature branch workflow): Changes span the entire branch.
git diff $(git merge-base HEAD main) -- schema/crdb/dbinit.sqljj diff --from 'fork_point(trunk() | @)' -- schema/crdb/dbinit.sqlIf the diff doesn't show anything, ask the user which ref to diff from.
Create a new folder under schema/crdb/ using the provided name or a short descriptive name derived from the schema changes.
Use existing folder names in schema/crdb/ as examples for naming conventions.
NOTE: The numbered folders, e.g. 1.0.0, are for legacy support only. No additional numbered directories should be added.
Based on the diff from step 1, write migration files in order:
up01.sql, up02.sql etc. (zero-padded) if you have more than 10 files.up1.sql, up2.sql etc. if you have 10 or fewer files.ALTER TABLE with multiple columns, you can add them all in one statement.NOT NULL columns to existing tables, add temporary defaults, then remove them in later migration files.IF NOT EXISTS for idempotency where supported.up.sql files are executed within a transaction (this always happens), and should be idempotent (this is an expectation that the migration author must uphold, with, e.g. IF NOT EXISTS).Bump the version number at the end of schema/crdb/dbinit.sql.
In nexus/db-model/src/schema_versions.rs, bump SCHEMA_VERSION.
In nexus/db-model/src/schema_versions.rs, add the new version to the KNOWN_VERSIONS list.
Run EXPECTORATE=overwrite cargo nextest run -p nexus-db-model 'test_migration_verification_files' to auto-generate .verify.sql files for any migration steps that contain backfill-prone DDL (CREATE INDEX, ADD CONSTRAINT, ALTER COLUMN SET NOT NULL, or ADD COLUMN with NOT NULL / non-null DEFAULT / STORED computed columns). Check in any generated files alongside the migration. If your migration doesn't contain backfill-prone DDL, no files are generated and this step is a no-op.
Run cargo nextest run -p omicron-nexus schema to verify that the migration is correct.
IF NOT EXISTS for idempotency.When creating a migration that affects existing data (like adding columns to existing tables), also add a data migration test in nexus/tests/integration_tests/schema.rs:
before_X_0_0 function to create test data in the old format.after_X_0_0 function to verify the migration worked correctly.get_migration_checks() map.This ensures old rows can be migrated smoothly in production.
Add a new Nexus background task. Use when the user wants to create a periodic background task in Nexus that runs on a timer.
Add a new version to a Dropshot HTTP API. Use when the user wants to make versioned changes to a Dropshot-managed HTTP API, such as adding, making changes to, or removing an API endpoint.