Shared databases

Use the same migration process for any database that is shared, long-lived, or contains data you care about.

That includes:

• PostgreSQL used by a backend or API
• shared SQLite files used by multiple app instances or long-lived environments
• staging and production databases

Recommended flow

1. Edit schema.prisma.
2. Run check and generate.
3. Create the next migration locally with migrate dev.
4. Review migration.sql, warnings.txt, before.prisma, after.prisma, and metadata.txt.
5. Commit schema changes, generated client, and migration artifacts together.
6. Run migrate status against the target database.
7. Apply with migrate deploy.
8. Verify with migrate status.

Commands

dart run comon_orm check
dart run comon_orm generate

dart run comon_orm migrate dev --name 20260315_add_user_role

dart run comon_orm migrate status

dart run comon_orm migrate deploy

What to review in the drafted artifact

migration.sql

This is the human-readable DDL summary.

Typical shape:

ALTER TABLE "posts" ADD COLUMN "status" TEXT NOT NULL DEFAULT 'draft';
CREATE INDEX "posts_author_id_status_idx" ON "posts" ("author_id", "status");

warnings.txt

This is the safety gate. If the planner sees risky transitions, the warnings appear here and apply stops by default.

Typical warnings include:

• dropping a column with live data
• shrinking an enum while live rows still use removed values
• lossy type changes
• SQLite rebuild scenarios

Example:

Potential data loss: dropping column users.legacy_code.
Potential rebuild: SQLite table posts must be recreated to apply relation or type changes.

before.prisma and after.prisma

These files capture the reviewed schema transition.

They matter for:

• auditability
• migrate status
• migrate resolve
• recovery when a local artifact has to be reconstructed from history

metadata.txt

This file stores migration metadata such as statement counts and whether a rebuild was required.

It matters for:

• deploy planning
• drift/status diagnostics
• provider-specific execution details

Important nuance

dev and deploy are not the same step.

• dev creates the next local migration artifact, applies it to the current database, and refreshes the client
• deploy applies already-created local migrations to another database
• checked-in migration artifacts remain important for review, status, rollback, and audit

History table

Applied migrations are recorded in _comon_orm_migrations with metadata such as:

• migration name
• provider
• checksum
• warnings
• whether a rebuild was required
• before and after schema snapshots

That metadata is what makes drift detection, status checks, and rollback support possible.

If you need to repair that history, use migrate resolve --applied ... or migrate resolve --rolled-back ....

Warnings and risk handling

dev, deploy, and other mutation commands stop by default when the planner detects risky changes such as destructive drops, lossy type changes, enum shrinkage, or SQLite rebuild scenarios.

Use --allow-warnings only after explicit review.

Practical review rules

1. Never edit old checked-in migration directories after they were applied anywhere important.
2. Run migrate status before migrate deploy on environments that may have drifted.
3. Treat warnings as a review step, not as noise to bypass.
4. Prefer a new forward migration over rewriting history.