Common Migration Patterns: 12 Real-World Schema Changes with MySQL

Part 8 of 18

May 03, 2026 Abhay 13 min read

Common Migration Patterns: 12 Real-World Schema Changes with MySQL

Most Liquibase guides explain change types by listing them. This article is different — it walks through twelve patterns you will encounter repeatedly in a real project, explains the MySQL-specific behaviour of each, and shows the rollback you need to write alongside every change.

All examples build on the ecommerce database established in Part 1. By the end, you will have a complete reference you can reach for on any working day.

Automatic Rollback: What You Get for Free

Before the patterns: some change types generate rollback SQL automatically. Others require you to write it explicitly. Knowing which is which saves time.

Change Type	Auto Rollback	Rollback Generated
`createTable`	Yes	`DROP TABLE`
`dropTable`	No	Must be explicit
`addColumn`	Yes	`DROP COLUMN`
`dropColumn`	No	Must be explicit
`renameColumn`	Yes	Rename back
`renameTable`	Yes	Rename back
`createIndex`	Yes	`DROP INDEX`
`dropIndex`	No	Must be explicit
`addForeignKeyConstraint`	Yes	`DROP FOREIGN KEY`
`dropForeignKeyConstraint`	No	Must be explicit
`addUniqueConstraint`	Yes	Drop constraint
`addNotNullConstraint`	Yes*	`DROP NOT NULL`
`modifyDataType`	No	Must be explicit
`insert`	No	Must be explicit
`update`	No	Must be explicit
`sql`	No	Must be explicit

*addNotNullConstraint auto-rollback has known bugs with named constraints in some Liquibase versions — always write it explicitly.

The rule of thumb: if you’re dropping or modifying something, write explicit rollback. If you’re creating something new (table, column, index), Liquibase can usually generate the drop.

Pattern 1: Add a Nullable Column

The simplest migration — adding a column that allows NULL. Existing rows get NULL for the new column automatically.

# 20260510-001-add-users-phone.yaml
databaseChangeLog:

  - changeSet:
      id: "20260510-001"
      author: abhay
      comment: Add optional phone number to users
      changes:
        - addColumn:
            tableName: users
            columns:
              - column:
                  name: phone
                  type: VARCHAR(20)
                  constraints:
                    nullable: true
      rollback:
        - dropColumn:
            tableName: users
            columnName: phone

addColumn has automatic rollback (dropColumn), but writing it explicitly is still good practice — it makes the intent visible in code review.

Pattern 2: Add a NOT NULL Column to an Existing Table

This is where developers get burned. Adding NOT NULL without a default fails on any table that already has rows, because MySQL cannot determine what value to assign to existing rows.

The three-step pattern:

# 20260510-002-add-users-verified.yaml
databaseChangeLog:

  # Step 1: Add column as nullable
  - changeSet:
      id: "20260510-002"
      author: abhay
      comment: Add email_verified column (nullable first)
      changes:
        - addColumn:
            tableName: users
            columns:
              - column:
                  name: email_verified
                  type: TINYINT(1)
                  defaultValueNumeric: 0
                  constraints:
                    nullable: true
      rollback:
        - dropColumn:
            tableName: users
            columnName: email_verified

  # Step 2: Backfill existing rows
  - changeSet:
      id: "20260510-003"
      author: abhay
      comment: Backfill email_verified = 0 for all existing users
      changes:
        - update:
            tableName: users
            columns:
              - column:
                  name: email_verified
                  valueNumeric: 0
      rollback:
        - update:
            tableName: users
            columns:
              - column:
                  name: email_verified
                  value: null

  # Step 3: Apply NOT NULL constraint
  - changeSet:
      id: "20260510-004"
      author: abhay
      comment: Apply NOT NULL constraint on email_verified
      changes:
        - addNotNullConstraint:
            tableName: users
            columnName: email_verified
            columnDataType: TINYINT(1)
            defaultNullValue: "0"
      rollback:
        - dropNotNullConstraint:
            tableName: users
            columnName: email_verified
            columnDataType: TINYINT(1)

Three separate changesets matter here: if step 3 fails (e.g., a row was inserted between step 2 and step 3 with a NULL value), steps 1 and 2 are already committed and tracked. You can fix the data and retry only the failing changeset.

MySQL shortcut for small tables: MySQL can add a NOT NULL column with a default in one statement without the backfill step, but only if the table is small and the operation is fast. For large tables (millions of rows), always use the three-step pattern to avoid locking the table during the backfill.

Pattern 3: Rename a Column

# 20260511-001-rename-users-fullname.yaml
databaseChangeLog:

  - changeSet:
      id: "20260511-001"
      author: abhay
      comment: Rename full_name to display_name for clarity
      changes:
        - renameColumn:
            tableName: users
            oldColumnName: full_name
            newColumnName: display_name
            columnDataType: VARCHAR(255)
      # Auto rollback available — renameColumn reverses automatically
      # Writing explicit rollback anyway for visibility
      rollback:
        - renameColumn:
            tableName: users
            oldColumnName: display_name
            newColumnName: full_name
            columnDataType: VARCHAR(255)

columnDataType is required by renameColumn in MySQL — Liquibase uses it to generate the correct CHANGE COLUMN statement.

Production warning: Renaming a column is a breaking change for any application code or query that references the old name. In zero-downtime deployments, you add the new column first, keep both columns in sync, migrate application code, then drop the old column. This is covered in Article 17 (zero-downtime deployments).

Pattern 4: Change a Column’s Data Type

# 20260512-001-widen-products-price.yaml
databaseChangeLog:

  - changeSet:
      id: "20260512-001"
      author: abhay
      comment: Change price from DECIMAL(8,2) to DECIMAL(12,2) to support larger values
      changes:
        - modifyDataType:
            tableName: products
            columnName: price
            newDataType: DECIMAL(12,2)
      rollback:
        - modifyDataType:
            tableName: products
            columnName: price
            newDataType: DECIMAL(8,2)

modifyDataType has no automatic rollback — always write it explicitly. The rollback reverts to the original type.

MySQL caveats:

Widening a type (e.g., VARCHAR(100) → VARCHAR(255)) is safe and typically instant via an in-place ALTER
Narrowing a type (e.g., DECIMAL(12,2) → DECIMAL(8,2)) can truncate data — MySQL does not error, it silently truncates
Changing between fundamentally different types (e.g., VARCHAR → INT) requires data to be compatible or the ALTER fails

For complex type changes where data transformation is needed, use a sql changeset instead:

  - changeSet:
      id: "20260512-002"
      author: abhay
      comment: Migrate status column from VARCHAR to ENUM
      changes:
        - sql:
            sql: >
              ALTER TABLE products
              MODIFY COLUMN status
              ENUM('draft', 'active', 'archived') NOT NULL DEFAULT 'draft';              
      rollback:
        - sql:
            sql: >
              ALTER TABLE products
              MODIFY COLUMN status VARCHAR(20) NOT NULL DEFAULT 'draft';

Pattern 5: Drop a Column

# 20260513-001-drop-users-legacy-field.yaml
databaseChangeLog:

  - changeSet:
      id: "20260513-001"
      author: abhay
      comment: Remove legacy referral_code column (unused since v0.9)
      changes:
        - dropColumn:
            tableName: users
            columnName: referral_code
      rollback:
        - addColumn:
            tableName: users
            columns:
              - column:
                  name: referral_code
                  type: VARCHAR(50)
                  constraints:
                    nullable: true

dropColumn has no automatic rollback. The explicit rollback recreates the column but cannot restore the data. This is why you should ensure data has been migrated or archived before dropping.

Before running dropColumn in production:

Deploy application code that no longer reads/writes the column
Verify no active queries reference the column (check slow query log, APM)
Then deploy the dropColumn changeset

Pattern 6: Add a Foreign Key Constraint

# 20260514-001-create-orders-table.yaml
databaseChangeLog:

  - changeSet:
      id: "20260514-001"
      author: abhay
      comment: Create orders table
      changes:
        - createTable:
            tableName: orders
            columns:
              - column:
                  name: id
                  type: BIGINT UNSIGNED
                  autoIncrement: true
                  constraints:
                    primaryKey: true
                    nullable: false
              - column:
                  name: user_id
                  type: BIGINT UNSIGNED
                  constraints:
                    nullable: false
              - column:
                  name: status
                  type: ENUM('pending','confirmed','shipped','delivered','cancelled')
                  defaultValue: pending
                  constraints:
                    nullable: false
              - column:
                  name: total
                  type: DECIMAL(12,2)
                  constraints:
                    nullable: false
              - column:
                  name: created_at
                  type: DATETIME
                  defaultValueComputed: CURRENT_TIMESTAMP
                  constraints:
                    nullable: false
      rollback:
        - dropTable:
            tableName: orders

  - changeSet:
      id: "20260514-002"
      author: abhay
      comment: Add foreign key from orders.user_id to users.id
      changes:
        - addForeignKeyConstraint:
            constraintName: fk_orders_user_id
            baseTableName: orders
            baseColumnNames: user_id
            referencedTableName: users
            referencedColumnNames: id
            onDelete: RESTRICT
            onUpdate: CASCADE
      rollback:
        - dropForeignKeyConstraint:
            baseTableName: orders
            constraintName: fk_orders_user_id

MySQL foreign key behaviour options:

`onDelete` / `onUpdate`	Effect
`RESTRICT` (default)	Reject the operation if related rows exist
`CASCADE`	Propagate the delete/update to child rows
`SET NULL`	Set child column to NULL (column must allow NULL)
`NO ACTION`	Same as RESTRICT in MySQL

Always name your foreign key constraints (fk_orders_user_id). Auto-generated names (orders_ibfk_1) are impossible to reference cleanly in rollback or dropForeignKeyConstraint.

MySQL automatically creates an index on the foreign key column (user_id) if no index already exists. Verify with SHOW INDEX FROM orders after applying.

Pattern 7: Add an Index

# 20260514-003-index-orders-user-status.yaml
databaseChangeLog:

  - changeSet:
      id: "20260514-003"
      author: abhay
      comment: Composite index for orders lookup by user + status
      changes:
        - createIndex:
            indexName: idx_orders_user_status
            tableName: orders
            columns:
              - column:
                  name: user_id
              - column:
                  name: status
      rollback:
        - dropIndex:
            indexName: idx_orders_user_status
            tableName: orders

  - changeSet:
      id: "20260514-004"
      author: abhay
      comment: Unique constraint on products.slug
      changes:
        - addUniqueConstraint:
            constraintName: uq_products_slug
            tableName: products
            columnNames: slug
      rollback:
        - dropUniqueConstraint:
            constraintName: uq_products_slug
            tableName: products

Composite index column order matters. (user_id, status) serves queries that filter by user_id alone or by user_id + status together. It does not serve queries that filter by status alone. Put the highest-cardinality, most-selective column first.

MySQL index creation on large tables locks the table in older versions. MySQL 5.6+ supports online DDL for most index operations, but the lock behaviour depends on the index type and table engine. For very large tables (50M+ rows), consider the liquibase-percona extension which uses Percona’s pt-online-schema-change to add indexes without locking. This is covered in Article 14.

Pattern 8: Rename a Table

# 20260515-001-rename-product-images.yaml
databaseChangeLog:

  - changeSet:
      id: "20260515-001"
      author: abhay
      comment: Rename product_images to product_media to support future video support
      changes:
        - renameTable:
            oldTableName: product_images
            newTableName: product_media
      rollback:
        - renameTable:
            oldTableName: product_media
            newTableName: product_images

renameTable has automatic rollback but write it explicitly anyway. MySQL’s RENAME TABLE is atomic and near-instant — it does not copy data. Foreign keys referencing the old table name are automatically updated by MySQL.

Same production warning as renameColumn: rename is a breaking change for application queries. Use the expand-contract pattern for zero-downtime renames (Article 17).

Pattern 9: Insert Reference / Seed Data

# 20260516-001-seed-product-categories.yaml
databaseChangeLog:

  - changeSet:
      id: "20260516-001"
      author: abhay
      context: default,dev,staging,prod
      comment: Seed initial product categories
      changes:
        - insert:
            tableName: product_categories
            columns:
              - column: {name: name, value: "Electronics"}
              - column: {name: slug, value: "electronics"}
        - insert:
            tableName: product_categories
            columns:
              - column: {name: name, value: "Clothing"}
              - column: {name: slug, value: "clothing"}
        - insert:
            tableName: product_categories
            columns:
              - column: {name: name, value: "Books"}
              - column: {name: slug, value: "books"}
      rollback:
        - delete:
            tableName: product_categories
            where: slug IN ('electronics', 'clothing', 'books')

Key decisions for data changesets:

Use context to control which environments run the changeset (Article 10 covers this in depth)
The rollback where clause must be deterministic — don’t delete by auto-increment id because IDs differ per environment
For large seed data sets, use loadData (Pattern 11) instead of individual insert statements

Pattern 10: Update Existing Data

# 20260517-001-normalize-user-status.yaml
databaseChangeLog:

  - changeSet:
      id: "20260517-001"
      author: abhay
      comment: Normalize legacy 'suspended' status to 'banned'
      changes:
        - update:
            tableName: users
            columns:
              - column:
                  name: status
                  value: banned
            where: status = 'suspended'
      rollback:
        - update:
            tableName: users
            columns:
              - column:
                  name: status
                  value: suspended
            where: status = 'banned'

Important: the rollback here is lossy — it sets all banned users back to suspended, including users who were banned before this migration ran. For production data migrations where the rollback must be lossless, you need to track which rows were changed:

  - changeSet:
      id: "20260517-002"
      author: abhay
      comment: Add migrated_from_suspended flag for safe rollback
      changes:
        - addColumn:
            tableName: users
            columns:
              - column:
                  name: migrated_from_suspended
                  type: TINYINT(1)
                  defaultValueNumeric: 0
                  constraints:
                    nullable: false
        - update:
            tableName: users
            columns:
              - column: {name: status, value: banned}
              - column: {name: migrated_from_suspended, valueNumeric: 1}
            where: status = 'suspended'
      rollback:
        - update:
            tableName: users
            columns:
              - column: {name: status, value: suspended}
            where: migrated_from_suspended = 1
        - dropColumn:
            tableName: users
            columnName: migrated_from_suspended

This pattern — tracking which rows were changed by a migration — is the correct approach for any data migration where the rollback must be exact.

Pattern 11: Load Data from CSV

For large reference data sets, loadData is cleaner than dozens of insert changesets:

# 20260518-001-load-countries.yaml
databaseChangeLog:

  - changeSet:
      id: "20260518-001"
      author: abhay
      comment: Load country reference data from CSV
      changes:
        - loadData:
            tableName: countries
            file: db/data/countries.csv
            separator: ","
            columns:
              - column: {name: code, type: STRING}
              - column: {name: name, type: STRING}
              - column: {name: calling_code, type: NUMERIC}
      rollback:
        - delete:
            tableName: countries

CSV file (src/main/resources/db/data/countries.csv):

code,name,calling_code
IN,India,91
US,United States,1
GB,United Kingdom,44
DE,Germany,49

loadData wraps all inserts in a single transaction. If any row fails, the entire changeset rolls back.

Use loadUpdateData instead of loadData when the table may already have rows and you want upsert semantics (insert if not exists, update if exists):

        - loadUpdateData:
            tableName: countries
            file: db/data/countries.csv
            primaryKey: code
            columns:
              - column: {name: code, type: STRING}
              - column: {name: name, type: STRING}
              - column: {name: calling_code, type: NUMERIC}

Pattern 12: Complex Backfill with a Computed Value

This pattern handles the case where a new column’s value must be derived from existing data — not just a static default.

Scenario: Add a display_price column to products that stores price formatted with currency symbol, derived from the existing price column.

# 20260519-001-add-display-price.yaml
databaseChangeLog:

  # Step 1: Add nullable column
  - changeSet:
      id: "20260519-001"
      author: abhay
      comment: Add display_price column (nullable during backfill)
      changes:
        - addColumn:
            tableName: products
            columns:
              - column:
                  name: display_price
                  type: VARCHAR(20)
                  constraints:
                    nullable: true
      rollback:
        - dropColumn:
            tableName: products
            columnName: display_price

  # Step 2: Backfill with computed value — requires sql changeset
  - changeSet:
      id: "20260519-002"
      author: abhay
      comment: Backfill display_price from price column
      changes:
        - sql:
            sql: >
              UPDATE products
              SET display_price = CONCAT('$', FORMAT(price, 2))
              WHERE display_price IS NULL;              
      rollback:
        - sql:
            sql: >
              UPDATE products SET display_price = NULL;              

  # Step 3: Apply NOT NULL
  - changeSet:
      id: "20260519-003"
      author: abhay
      comment: Apply NOT NULL on display_price after backfill
      changes:
        - addNotNullConstraint:
            tableName: products
            columnName: display_price
            columnDataType: VARCHAR(20)
            defaultNullValue: "$0.00"
      rollback:
        - dropNotNullConstraint:
            tableName: products
            columnName: display_price
            columnDataType: VARCHAR(20)

The middle changeset uses sql because update change type cannot express CONCAT or FORMAT — computed expressions require raw SQL.

Rollback Reference for Every Pattern

Pattern	Change Type	Explicit Rollback Required?
Add nullable column	`addColumn`	No (auto), but recommended
Add NOT NULL column	`addColumn` + `update` + `addNotNullConstraint`	Yes for `update`; no for others
Rename column	`renameColumn`	No (auto), but recommended
Change data type	`modifyDataType`	Yes
Drop column	`dropColumn`	Yes
Add foreign key	`addForeignKeyConstraint`	No (auto), but recommended
Add index	`createIndex`	No (auto), but recommended
Add unique constraint	`addUniqueConstraint`	No (auto), but recommended
Rename table	`renameTable`	No (auto), but recommended
Insert data	`insert`	Yes
Update data	`update`	Yes (and may be lossy — track changed rows)
Load CSV	`loadData`	Yes (delete all)
Computed backfill	`sql`	Yes

Common Mistakes

Adding NOT NULL without the three-step pattern: Running ALTER TABLE users ADD COLUMN verified TINYINT(1) NOT NULL on a table with existing rows throws ERROR 1364: Field 'verified' doesn't have a default value (or silently assigns 0 depending on MySQL’s strict mode). Always add nullable first, backfill, then constrain.

Writing lossy rollback for data migrations: An update changeset that changes status = 'suspended' to 'banned' cannot be rolled back cleanly unless you track which rows were changed. The rollback where: status = 'banned' will also revert rows that were already banned before the migration. For non-trivial data migrations, track changed rows.

Not naming constraints: Using addForeignKeyConstraint without constraintName lets MySQL generate a name like orders_ibfk_1. The rollback changeset needs to reference this name exactly — and it differs across MySQL instances. Always name constraints explicitly.

Best Practices

Three-step pattern for NOT NULL columns — add nullable, backfill, constrain; never in one step on a populated table
Always name constraints — foreign keys, unique constraints, and indexes with explicit names make rollback, diff, and schema documentation reliable
Data migrations need careful rollback — track which rows were changed if the rollback must be lossless
Use sql changeset for computed values — update change type cannot express SQL functions; reach for sql when you need CONCAT, IFNULL, DATE_FORMAT, etc.
Separate DDL from DML changesets — table changes and data changes in separate changesets give you precise rollback granularity
loadData for bulk reference data — cleaner than dozens of insert changesets, wraps everything in one transaction

What You’ve Learned

Which change types have automatic rollback and which require explicit rollback — know the table
Adding NOT NULL to a populated table requires three changesets: add nullable → backfill → constrain
renameColumn and renameTable are breaking changes for application code — plan for zero-downtime patterns
modifyDataType has no automatic rollback; always write the reverse explicitly
Data migrations (insert, update) need careful rollback that tracks changed rows
loadData and loadUpdateData handle bulk CSV imports; rollback deletes all loaded rows
Computed backfills require sql changeset because the update change type cannot express SQL functions

Next: Article 9 — Rollback Strategies: Automatic, Custom, Tag-Based, and Count-Based — a deep dive into planning rollback from the start, handling change types that can’t auto-rollback, and validating your rollback strategy in CI before it matters in production.