DEV Community

Feroz Shah
Feroz Shah

Posted on Originally published at migrax.org

Applying and undoing migrations: migrate, status, rollback

#java #database #postgres #devops

migrax generate writes migrations, and three commands handle the rest: status shows what the database has, migrate applies what is missing, and rollback undoes it. This article goes through each one on a real project. It also covers what a rollback can and can't bring back, and what happens when someone edits a migration that already ran.

Tested with: Migrax 0.17.1, Hibernate 6.6.13, PostgreSQL 17. Every output below was copied from a real run.

Where we start

The shop project from the earlier articles has four applied migrations: the first tables, a phone column, and the two renames from Renaming columns and tables without losing data. Now two more entity changes come in: a notes field on PurchaseOrder, and an index on its createdAt column.

@Entity
@Table(name = "purchase_orders",
    indexes = @Index(name = "ix_orders_created", columnList = "createdAt"))
public class PurchaseOrder {
  ...
  @Column(length = 500)
  String notes;
}
Enter fullscreen mode Exit fullscreen mode

Running migrax generate once per change gives two files: 0005_add_purchase_orders_notes.sql and 0006_index_purchase_orders.sql.

status: what has the database got? 

$ migrax status
Migrations in src/main/resources/db/migration for jdbc:postgresql://localhost:5432/shop
  [X] 0001_initial.sql                    applied 2026-10-25 16:04:44
  [X] 0002_add_customers_phone.sql        applied 2026-10-25 16:04:58
  [X] 0003_rename_customers_name.sql      applied 2026-10-25 16:06:06
  [X] 0004_rename_orders.sql              applied 2026-10-25 16:07:06
  [ ] 0005_add_purchase_orders_notes.sql  pending
  [ ] 0006_index_purchase_orders.sql      pending
4 applied, 2 pending.
Enter fullscreen mode Exit fullscreen mode

[X] is applied, [ ] is pending. Migrax keeps the history in a migrax_history table in the same database, with a checksum of every applied file. status only reads; it never changes anything. The alias showmigrations does the same.

Before applying: --dry-run and --sql

--dry-run lists what would run and checks it for risky SQL, without touching the database:

$ migrax migrate --dry-run
2 migration(s) would be applied:
  0005_add_purchase_orders_notes.sql
  0006_index_purchase_orders.sql
src/main/resources/db/migration/0006_index_purchase_orders.sql, statement 1: warning MX001
  CREATE INDEX ix_orders_created ON purchase_orders (createdAt)
  Building this index blocks writes to purchase_orders until it finishes.
  Fix: Use CREATE INDEX CONCURRENTLY in its own migration that starts with '-- migrax:no-transaction' ('migrax generate --safe' does this).
Enter fullscreen mode Exit fullscreen mode

On a small table that warning doesn't matter. On a table with millions of rows, it is the difference between a quick deploy and minutes of blocked writes. The lint rules page lists every check.

If a DBA has to run the changes, --sql prints one script for them. It applies each migration in its own transaction and records it in the history, so Migrax sees it as applied afterwards:

$ migrax migrate --sql
-- Migration script written by Migrax: 2 migration(s) to apply.
-- Review it, then run the whole file with the database's own tool; it must stop
-- at the first error:
--   psql -v ON_ERROR_STOP=1 -f <this file>
-- Each migration is recorded in migrax_history, so 'migrax status' and later
-- 'migrax migrate' runs see it as applied.

-- 0005_add_purchase_orders_notes.sql
BEGIN;
-- Generated by Migrax 0.17.1 for postgresql

ALTER TABLE purchase_orders ADD COLUMN notes varchar(500);
INSERT INTO migrax_history(version,checksum,applied_at) VALUES('0005_add_purchase_orders_notes.sql','5f26d7ff0f5e8502d49b57c00fd7449f0619bcf7d8f38441f006fcd261904837',(CURRENT_TIMESTAMP AT TIME ZONE 'Etc/UTC'));
COMMIT;

-- 0006_index_purchase_orders.sql
...
Enter fullscreen mode Exit fullscreen mode

migrate: all at once, or up to a point

migrax migrate applies every pending migration in order. --to stops after the one you name:

$ migrax migrate --to 0005_add_purchase_orders_notes.sql
Applying migration '0005_add_purchase_orders_notes.sql'...
Successfully applied migration '0005_add_purchase_orders_notes.sql'.
Applied 1 migration(s) up to 0005_add_purchase_orders_notes.sql.
1 migration(s) after it stay pending.
Enter fullscreen mode Exit fullscreen mode 
$ migrax migrate
Applying migration '0006_index_purchase_orders.sql'...
Successfully applied migration '0006_index_purchase_orders.sql'.
Applied 1 migration(s); 6 total in src/main/resources/db/migration.
Enter fullscreen mode Exit fullscreen mode

On PostgreSQL each migration runs in a transaction. If a statement fails, that migration is rolled back as a whole and migrate stops. Migrax also takes a lock on the database while it works, so two application instances that start at the same moment don't both apply the same migration.

rollback: undoing the newest migrations

Every generated migration has a rollback script next to it, in rollback/. migrax rollback runs the script of the newest applied migration and removes that migration from the history.

Because a rollback can delete data, Migrax wants a confirmation. In a terminal it asks:

$ migrax rollback
Rolling back 1 migration(s), newest first:
  0006_index_purchase_orders.sql
Roll back now? Data written to removed objects is lost. [y/N] y
Rolling back migration '0006_index_purchase_orders.sql'...
Rolled back migration '0006_index_purchase_orders.sql'.
Rolled back 1 migration(s).
Enter fullscreen mode Exit fullscreen mode

In a script or CI job it stops instead, until you add --yes:

$ migrax rollback
Rolling back 1 migration(s), newest first:
  0006_index_purchase_orders.sql
error[MXE001]: Rollback needs confirmation.
Back up the database, then rerun with --yes.
Enter fullscreen mode Exit fullscreen mode

--steps n undoes the newest n migrations. --dry-run shows the list first:

$ migrax rollback --steps 2 --dry-run
Would roll back 2 migration(s), newest first:
  0006_index_purchase_orders.sql
  0005_add_purchase_orders_notes.sql
Enter fullscreen mode Exit fullscreen mode

--to keeps the migration you name and undoes everything after it:

$ migrax rollback --to 0003_rename_customers_name.sql --yes
Rolling back 3 migration(s), newest first:
  0006_index_purchase_orders.sql
  0005_add_purchase_orders_notes.sql
  0004_rename_orders.sql
Rolling back migration '0006_index_purchase_orders.sql'...
Rolled back migration '0006_index_purchase_orders.sql'.
Rolling back migration '0005_add_purchase_orders_notes.sql'...
Rolled back migration '0005_add_purchase_orders_notes.sql'.
Rolling back migration '0004_rename_orders.sql'...
Rolled back migration '0004_rename_orders.sql'.
Rolled back 3 migration(s).
Enter fullscreen mode Exit fullscreen mode

Rolled-back migrations are pending again, so the next migrax migrate applies them again. The files stay where they are.

What a rollback can't bring back

A rollback restores the structure, not the data. Say someone stores a note on an order:

UPDATE purchase_orders SET notes = 'Gift wrap, please' WHERE id = 2;
Enter fullscreen mode Exit fullscreen mode

Rolling back 0005 drops the notes column. Applying it again brings an empty column back, and the note is gone:

 id | notes
----+-------
  1 |
  2 |
  3 |
Enter fullscreen mode Exit fullscreen mode

The rollback script says so in its header, so it shows in review before anything runs:

db/migration/rollback/0005_add_purchase_orders_notes.sql:

-- Generated by Migrax 0.17.1 for postgresql
-- Rollback for 0005_add_purchase_orders_notes.sql. 'migrax rollback' runs it.
-- Note: deletes data written to purchase_orders.notes since the migration ran.

ALTER TABLE purchase_orders DROP COLUMN notes;
Enter fullscreen mode Exit fullscreen mode

The same applies the other way round: when a migration drops a column, its rollback can re-create the column but not its old values. To keep those, generate that migration with --keep-data. Migrax then copies the values into a backup table first, and the rollback puts them back (Rollbacks and recovery).

Don't edit a migration that already ran

Migrax stores a checksum of each applied file. If someone changes the file afterwards, even just by adding a comment, status shows it and exits with code 1:

$ migrax status
Migrations in src/main/resources/db/migration for jdbc:postgresql://localhost:5432/shop
  [X] 0001_initial.sql                    applied 2026-10-25 16:04:44
  [X] 0002_add_customers_phone.sql        applied 2026-10-25 16:04:58
  [X] 0003_rename_customers_name.sql      applied 2026-10-25 16:06:06
  [X] 0004_rename_orders.sql              applied 2026-10-25 16:08:46
  [!] 0005_add_purchase_orders_notes.sql  CHANGED: file changed after it was applied 2026-10-25 16:08:46
  [X] 0006_index_purchase_orders.sql      applied 2026-10-25 16:08:46
5 applied, 1 changed.
Some migrations need attention. See 'migrax help repair'.
Enter fullscreen mode Exit fullscreen mode

migrate refuses to run at all:

$ migrax migrate
error[MXE104]: Checksum changed for applied migration 0005_add_purchase_orders_notes.sql
Run again with --verbose for details.
Enter fullscreen mode Exit fullscreen mode

This protects you from a database that no longer matches its files: the edited SQL never ran on the databases that already applied the old version. Put the file back as it was (here, git checkout of the file) and write any new change as a new migration.

Summary

  • migrax status shows applied, pending and changed migrations, and never changes anything.
  • migrax migrate applies pending migrations in order. --dry-run previews and lints, --sql writes a script for a DBA, and --to stops at a given migration.
  • migrax rollback undoes the newest migrations (--steps, --to). It asks first, and needs --yes in scripts.
  • Rollbacks restore structure, not data. The rollback script's header says what gets lost, and --keep-data keeps it.
  • Never edit an applied migration. Write a new one.

More in the guides: Rollbacks and recovery and Production safety, which shows how migrax verify tests every rollback script before release.

Originally published at migrax.org.

Top comments (0)