DEV Community

Feroz Shah
Feroz Shah

Posted on Originally published at migrax.org

Renaming columns and tables without losing data

#java #database #postgres #hibernate

Renames are where generated migrations usually go wrong. When a field changes from name to fullName, the database only sees one column disappear and another appear. A tool that just compares the two schemas drops the old column, and every value in it, then adds an empty new one.

Migrax notices when a change looks like a rename. In a terminal it asks you. In CI, where no one can answer, it refuses to write a migration that would lose the data. This article shows both cases for a column and for a table, and how to undo a rename.

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

Where we start

This picks up where Generate SQL migrations from your JPA entities ended: a customers table and an orders table, two migrations applied, and a few rows of data:

      email       | id |     name     |      phone
------------------+----+--------------+------------------
 ada@example.com  |  1 | Ada Lovelace | +44 20 7946 0001
 alan@example.com |  2 | Alan Turing  |
Enter fullscreen mode Exit fullscreen mode

Each order points at its customer through a foreign key.

Renaming a column

Rename the field in Customer:

  @Column(name = "full_name", nullable = false, length = 100)
  String fullName;
Enter fullscreen mode Exit fullscreen mode

In CI: Migrax refuses

First, run generate where nobody can answer a question, such as a CI job or a script:

$ migrax generate
Possible rename: customers.name to customers.full_name. To keep the data, rerun with --rename customers.name=full_name.
2 change(s) for postgresql:
  1. drop column customers.name  [DESTRUCTIVE]
     ALTER TABLE customers DROP COLUMN name;
  2. add column customers.full_name
     ALTER TABLE customers ADD COLUMN full_name varchar(100) NOT NULL;
error[MXE108]: 1 destructive change(s) detected (marked [DESTRUCTIVE] above).
Review them, then run 'migrax generate --allow-destructive'. If a column or table was renamed, pass --rename table.old=new or --rename-table old=new instead.
Enter fullscreen mode Exit fullscreen mode

It exits with code 1 and writes no file. Here, writing the plain drop and add would have been doubly wrong. It would have deleted both names. Then ADD COLUMN ... NOT NULL would have failed anyway, because the existing rows have no value for the new column.

The first line gives the option that keeps the data.

In a terminal: Migrax asks

Run the same command in a terminal and Migrax asks instead:

$ migrax generate
Did you rename customers.name to customers.full_name? [y/N] y
Created src/main/resources/db/migration/0003_rename_customers_name.sql:
  - rename column customers.name to full_name
Rollback script written to src/main/resources/db/migration/rollback.
src/main/resources/db/migration/0003_rename_customers_name.sql, statement 1: warning MX008
  ALTER TABLE customers RENAME COLUMN name TO full_name
  Application instances still running the old code use the old name and will fail.
  Fix: Rename in steps: add the new name, write to both, switch reads, then remove the old name ('migrax generate --zero-downtime' does this). Or deploy during a maintenance window.
Review the SQL, then run 'migrax migrate'.
Enter fullscreen mode Exit fullscreen mode

The default is "no". If you just press Enter, Migrax treats the change as a drop and an add, and refuses as in CI.

The same answer as an option

In a script, give the answer up front with --rename table.old=new:

$ migrax generate --rename customers.name=full_name
Created src/main/resources/db/migration/0003_rename_customers_name.sql:
  - rename column customers.name to full_name
Enter fullscreen mode Exit fullscreen mode

It writes exactly the same files as the "y" answer. Both runs above created byte-identical migrations. Separate several column renames with commas.

The migration

db/migration/0003_rename_customers_name.sql:

-- Generated by Migrax 0.17.1 for postgresql

ALTER TABLE customers RENAME COLUMN name TO full_name;
Enter fullscreen mode Exit fullscreen mode

db/migration/rollback/0003_rename_customers_name.sql:

-- Generated by Migrax 0.17.1 for postgresql
-- Rollback for 0003_rename_customers_name.sql. 'migrax rollback' runs it.

ALTER TABLE customers RENAME COLUMN full_name TO name;
Enter fullscreen mode Exit fullscreen mode

A rename moves no data, so it is fast even on a large table. Apply it, and the names are still there:

$ migrax migrate
Applying migration '0003_rename_customers_name.sql'...
Successfully applied migration '0003_rename_customers_name.sql'.
Applied 1 migration(s); 3 total in src/main/resources/db/migration.
Enter fullscreen mode Exit fullscreen mode 
 id |  full_name   |      email
----+--------------+------------------
  1 | Ada Lovelace | ada@example.com
  2 | Alan Turing  | alan@example.com
Enter fullscreen mode Exit fullscreen mode

How Migrax spots a rename

It treats a dropped column and an added column in the same table as a possible rename when:

  • they are the same name in another style, like created_at and createdAt (even when the type changed too), or
  • the new column has the same type as the old one.

A rename that also changes the type becomes a RENAME followed by an ALTER.

Renaming a table

Now rename the orders table:

@Entity
@Table(name = "purchase_orders")
public class PurchaseOrder {
Enter fullscreen mode Exit fullscreen mode

A table rename has one more catch. Hibernate puts the table name into the names of the foreign keys it creates, so renaming the table changes the key's name too. Without an answer, Migrax again sees a drop and refuses:

$ migrax generate
Possible rename: table orders to purchase_orders. To keep the data, rerun with --rename-table orders=purchase_orders.
4 change(s) for postgresql:
  1. drop foreign key fk_orders_customer_id_54d4cc52 on orders  [DESTRUCTIVE]
     ALTER TABLE orders DROP CONSTRAINT fk_orders_customer_id_54d4cc52;
  2. create table purchase_orders
     CREATE TABLE purchase_orders (createdAt timestamp(6) with time zone, customer_id bigint NOT NULL, id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL, total numeric(12,2) NOT NULL, CONSTRAINT purchase_orders_pkey PRIMARY KEY (id));
  3. drop table orders  [DESTRUCTIVE]
     DROP TABLE orders;
  4. add foreign key fk_purchase_orders_cu_1ba47d9c on purchase_orders
     ALTER TABLE purchase_orders ADD CONSTRAINT fk_purchase_orders_cu_1ba47d9c FOREIGN KEY (customer_id) REFERENCES customers (id);
error[MXE108]: 2 destructive change(s) detected (marked [DESTRUCTIVE] above).
Review them, then run 'migrax generate --allow-destructive'. If a column or table was renamed, pass --rename table.old=new or --rename-table old=new instead.
Enter fullscreen mode Exit fullscreen mode

Answer "y" in a terminal, or pass --rename-table orders=purchase_orders (both write the same files again):

$ migrax generate
Did you rename table orders to purchase_orders? [y/N] y
Created src/main/resources/db/migration/0004_rename_orders.sql:
  - rename table orders to purchase_orders
  - rename foreign key fk_orders_customer_id_54d4cc52 to fk_purchase_orders_cu_1ba47d9c on purchase_orders
Rollback script written to src/main/resources/db/migration/rollback.
Enter fullscreen mode Exit fullscreen mode

(The MX008 warning from before appears here too.)

db/migration/0004_rename_orders.sql:

-- Generated by Migrax 0.17.1 for postgresql

ALTER TABLE orders RENAME TO purchase_orders;
ALTER TABLE purchase_orders RENAME CONSTRAINT fk_orders_customer_id_54d4cc52 TO fk_purchase_orders_cu_1ba47d9c;
Enter fullscreen mode Exit fullscreen mode

The foreign key keeps its columns and its target and only gets its new name. On PostgreSQL, CockroachDB, YugabyteDB and H2 that is a RENAME CONSTRAINT, which is instant. Other databases can't rename a foreign key, so there Migrax drops the key and adds it again under the new name. That makes the database check every row once. Before Migrax 0.17.1, a table rename needed --allow-destructive because of this key. Now the answer to the rename question is enough.

After migrax migrate, the orders still belong to the same customers:

 id |  full_name   | total
----+--------------+--------
  1 | Ada Lovelace |  49.90
  2 | Ada Lovelace | 120.00
  3 | Alan Turing  |  15.50
Enter fullscreen mode Exit fullscreen mode

migrax drift agrees that the database matches the entities:

$ migrax drift
No drift: the database matches the snapshot.
Enter fullscreen mode Exit fullscreen mode

Undoing a rename

The rollback script renames everything back, foreign key included:

db/migration/rollback/0004_rename_orders.sql:

ALTER TABLE purchase_orders RENAME CONSTRAINT fk_purchase_orders_cu_1ba47d9c TO fk_orders_customer_id_54d4cc52;
ALTER TABLE purchase_orders RENAME TO orders;
Enter fullscreen mode Exit fullscreen mode 
$ migrax rollback --yes
Rolling back 1 migration(s), newest first:
  0004_rename_orders.sql
Rolling back migration '0004_rename_orders.sql'...
Rolled back migration '0004_rename_orders.sql'.
Rolled back 1 migration(s).
Enter fullscreen mode Exit fullscreen mode

A rename moves no data, so undoing it loses none either. migrax migrate applies it again.

A rename while the old code still runs

The MX008 warning is about deployment, not data. During a rolling deploy, instances of the old version keep running for a while. They still query customers.name and fail as soon as the column is renamed. You have two options:

  • deploy during a short maintenance window, so no old instance is left when the migration runs, or
  • rename in steps: add the new column, write to both, switch reads, then drop the old one. migrax generate --zero-downtime writes these steps for you (Zero-downtime changes).

Summary

  • Migrax spots likely renames and asks about them. It never drops data on a guess.
  • In CI it refuses with MXE108 and prints the exact --rename or --rename-table option to use.
  • A table rename also renames its foreign keys, in place where the database supports it.
  • Every rename has a rollback script that renames it back.

More in the guide: Changing entities.

Originally published at migrax.org.

Top comments (0)