Migrations

Migrations are flat SQL files in application/settings/config/database/, executed in numeric order. The CLI tracks which files have run and skips them on subsequent invocations.

File naming

NNN-<slug>.sql

• NNN: zero-padded three-digit number for sort order.
• <slug>: kebab-case description (add-account-email, create-acl-data).

Example progression:

001-acl.sql
002-account.sql
003-api_registry.sql
004-timeanddate.sql
005-user.sql
006-user_to_account.sql
007-timeanddate_to_account.sql
008-user_to_acl.sql
009-account_to_api_registry.sql
010-timeanddate_to_user.sql
011-acl-data.sql
012-add-unique-key-user.sql
013-add-account-email.sql

Running migrations

./nibiru -mi local                              # APPLICATION_ENV=development
APPLICATION_ENV=staging   ./nibiru -mi staging
APPLICATION_ENV=production ./nibiru -mi production

The {env} argument and APPLICATION_ENV should agree. Migrations target the database configured in settings.<env>.ini under [DATABASE].

What the runner does

For each *.sql file in numeric order:

1. Looks up its filename in the _migrations table.
2. If absent, opens a transaction (where the driver supports DDL transactions), executes the file, and inserts a record on success.
3. If a statement fails, rolls back and exits non-zero.

The _migrations table is created automatically on first run.

Idempotent SQL

Always write migrations that can re-run without errors after a partial failure:

CREATE TABLE IF NOT EXISTS api_registry (
    api_registry_id    INT(11) NOT NULL AUTO_INCREMENT,
    api_registry_name  VARCHAR(255) NOT NULL,
    PRIMARY KEY (api_registry_id),
    UNIQUE KEY api_registry_name_uk (api_registry_name)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

For PostgreSQL:

CREATE TABLE IF NOT EXISTS api_registry (
    api_registry_id    SERIAL PRIMARY KEY,
    api_registry_name  TEXT NOT NULL UNIQUE
);

For ALTER statements, prefer IF NOT EXISTS clauses on supported engines:

ALTER TABLE "user" ADD COLUMN IF NOT EXISTS user_email VARCHAR(255);

If your engine doesn’t support IF NOT EXISTS on alter, wrap the statement in a guard query that no-ops when the change already exists.

Reset commands

Caution

The reset commands clear the migrations audit table — they don’t drop your data tables. After a reset, the next -mi will re-run every migration. Make sure your SQL is idempotent before you reset.

./nibiru -mi-reset local                       # forget all applied migrations
./nibiru -mi-reset-file 005-user.sql local     # forget a single file

The single-file form is useful when you’ve fixed a bug in a previously-applied migration and want to re-run only that file.

Branch hygiene

Two engineers working on parallel branches can both add 015-… and collide. Conventions that help:

• Reserve numbers in pull-request titles before writing the SQL.
• Use a generous gap for hotfixes (e.g., reserve 099, 199, 299 for emergency cherry-picks).
• Prefer additive migrations (new tables, new columns) over destructive ones (drops). They merge more cleanly.

Squashing

For long-lived projects, periodically collapse old migrations into a single seed file representing the current schema. Move the originals to archive/ so the audit trail still exists, and create a new 000-baseline-2026.sql that creates everything at once. Update the migrations runner with a manual INSERT INTO _migrations to mark old files as applied.

Schema-first models

When [GENERATOR] database = true, models are regenerated from the live schema after each migration. So a typical deploy is:

./nibiru -mi production
# Generator regenerates models on the next request.
./nibiru -cache-clear

For zero-downtime deploys: turn off the generator (database = false) and check in the regenerated models alongside the migrations they depend on.