Rolling Back a Database Schema Change
For the person who has just deployed a schema change and has to decide whether it can be reversed; the DDL transaction behavior of each engine is explained where it appears.
On this page
The ALTER TABLE in the release you just deployed is holding a lock that every other query is queued behind, and the change has to come out. Whether one ROLLBACK is enough depends on the engine. PostgreSQL 17 and SQL Server 2022 undo a catalog change made inside an explicit transaction. MySQL 8.4 and Oracle AI Database 26ai commit each DDL statement on its own, so taking a change out there means running a script that reverses it.
Which engines can roll back DDL in a transaction
In PostgreSQL 17, the statements that create and alter schema objects run inside the surrounding transaction. Data Definition Language (DDL) is transactional there: BEGIN initiates a transaction block, so every statement after it executes in a single transaction until an explicit COMMIT or ROLLBACK is given[1]. Abort the transaction and the catalog changes go with it. ALTER TABLE acquires an ACCESS EXCLUSIVE lock unless a subform is explicitly noted otherwise[2], and a lock is normally held until the end of the transaction[3], so the ROLLBACK that undoes the change is also what ends the blocking. A few statements cannot run inside a transaction block at all, CREATE DATABASE among them[4]. Writing the change so that the lock is held briefly in the first place is a separate job, covered in deploying PostgreSQL schema changes safely.
Microsoft SQL Server 2022 gives you the same option. BEGIN TRANSACTION represents a point at which the data referenced by a session has a certain state of consistency, and all modifications made after it can be rolled back to that known state[5]. An ALTER TABLE that adds, alters, or drops a column or a constraint[6] is undone by ROLLBACK TRANSACTION under the default read-committed isolation level. Snapshot isolation is the exception, because the Database Engine does not support versioning of metadata. ALTER TABLE, CREATE INDEX, ALTER INDEX and DROP INDEX are among the statements not permitted after a BEGIN TRANSACTION statement running under snapshot isolation[7]. Leave the session on the default level and the rollback is available.
Oracle AI Database 26ai takes the decision out of your hands: it issues an implicit COMMIT before and after every DDL statement, and a transaction that already holds DML is committed first[8]. MySQL 8.4 ends the open transaction the same way.
| Engine | DDL inside an explicit transaction | What a later ROLLBACK reverses |
|---|---|---|
| PostgreSQL 17 | Runs inside the transaction | Tables, columns, indexes, constraints |
| SQL Server 2022 | Runs inside the transaction | Tables, columns, constraints |
| MySQL 8.4 | Commits implicitly | Only the statements after the DDL |
| Oracle AI Database 26ai | Commits implicitly | Only the statements after the DDL |
Why MySQL atomic DDL cannot be rolled back
MySQL 8.4 implements atomic DDL, and only the InnoDB storage engine supports it. An atomic DDL statement combines the data dictionary update, the storage engine operation, and the binary log write into a single atomic operation: either the changes persist to all three, or the statement is rolled back, even if the server halts partway through[9].
Atomic DDL is not transactional DDL. A DDL statement, atomic or otherwise, implicitly ends any transaction that is active in the current session, as if you had run COMMIT before it. DDL therefore cannot be performed inside another transaction, or inside transaction control statements such as START TRANSACTION and COMMIT[9]. Run the block below and the ROLLBACK arrives after the schema change has already been committed:
START TRANSACTION;
ALTER TABLE customer_orders ADD COLUMN processing_status VARCHAR(32) NOT NULL DEFAULT 'pending';
ROLLBACK;
Ask the catalog what the table looks like afterwards:
SELECT column_name, column_type
FROM information_schema.columns
WHERE table_name = 'customer_orders' AND column_name = 'processing_status';
| column_name | column_type |
|---|---|
| processing_status | varchar(32) |
The column outlived the rollback, which only ever applied to statements issued after the ALTER TABLE. Taking that column back off a MySQL database is a second migration that drops it.
Why a dropped column does not come back
A reverse migration script restores definitions, not rows. When an ALTER TABLE DROP COLUMN or a DROP TABLE commits, the engine marks the storage as free, and running the opposite statement afterwards creates an empty column or an empty table. Add a dropped column back and every existing row shows NULL, or the default you gave the new column, in place of the value it used to hold.
Type narrowing destroys data in the same way without looking destructive. Converting a column from VARCHAR(255) to VARCHAR(50), or from BIGINT to INT, truncates or rejects the values that no longer fit, and widening the type again leaves the shortened values as they are.
So a destructive statement has to be planned for before the forward migration runs, not after. The values have to exist somewhere the rollback can reach: a backup, or a table you copied them into as the first statement of the same release. If a destructive migration has already committed, the reverse script still earns its place, because it puts the table back into the shape the previous release queries and gets the application answering again. Backfilling is then a separate job that reads from the snapshot.
Generating the reverse script from two model versions
The rollback starts in the repository, not in the database. When the schema design is versioned in Git as a DbSchema model file, the Git history already holds the shape the database had before the release, which is what schema versioning for PostgreSQL buys you. The .dbs model file is plain XML, holding the tables, data types, indexes, virtual foreign keys, and comments, so checking out an older version of it is an ordinary Git operation:
git show HEAD~1:schema/production_model.dbs > schema/previous_model.dbs
Open both files in DbSchema, the model of the release you deployed and the one you just checked out, and synchronize between them. The Synchronization Dialog lists every object that differs, so nothing in the deployment is left out of the reverse script by accident, and it generates the SQL that turns one model into the other. The direction follows from which of the two models you treat as the source. Swap them and the comparison that produced the deployment script produces the script that reverses it, dropping the columns the release added and rebuilding the index it removed. Turning that output into something safe to run is covered in turning a schema diff into a safe migration script.
Nothing so far has touched the database, because DbSchema has been reading two model files. To measure how far the live database has drifted from either of them, connect and choose Compare Model with Database from the Schema menu, which puts the model in front of you next to the catalog. The database changes only when you review the generated statements and click Execute.
A worked before-and-after of a reversed schema change
A release adds two columns for the parts of a customer name, drops a unique constraint that no longer fits the data, and widens a column, on PostgreSQL 17:
ALTER TABLE accounts ADD COLUMN first_name VARCHAR(100);
ALTER TABLE accounts ADD COLUMN last_name VARCHAR(100);
ALTER TABLE accounts DROP CONSTRAINT uq_account_composite_code;
ALTER TABLE accounts ALTER COLUMN status TYPE VARCHAR(20);
Comparing the deployed model against the one from the previous commit produces the inverse, with the statements in the opposite order:
ALTER TABLE accounts ALTER COLUMN status TYPE VARCHAR(10);
ALTER TABLE accounts ADD CONSTRAINT uq_account_composite_code UNIQUE (account_code, department_id);
ALTER TABLE accounts DROP COLUMN first_name;
ALTER TABLE accounts DROP COLUMN last_name;
Two of those four statements can fail on the data that arrived while the release was live. ADD CONSTRAINT is rejected if two rows now share the same account_code and department_id pair, which they were free to do for as long as the constraint was gone. Narrowing status back to VARCHAR(10) fails on any row that picked up a longer value in the meantime:
ERROR: value too long for type character varying(10)
Both failures come from the rows rather than from the script, so inspect the table for conflicting values before you run the statements that restore the constraint and the narrower type.
The common failure when reversing multiple changes
Partial rollback is what actually goes wrong during an incident. A release renames a column on a parent table and updates a foreign key on a child table in two separate steps. The script written by hand under pressure drops the new foreign key and leaves the rename in place. The application reconnects and fails on a column that no longer answers to that name, because the catalog now matches neither the old release nor the new one, and nobody has tested it in that shape.
The order matters as well as the coverage. A foreign key has to go before the key it references, and a renamed column has to come back before anything that points at it. When the deployment was split across several migration files, reversing them file by file is where a statement gets missed. Compare the two database schemas instead: one comparison against the model of the release you are going back to covers every object in the deployment at once, whatever file it arrived in.
What to verify once the reversal has run
Once the reverse script finishes, four checks stand between it and production traffic.
- Compare the live database in DbSchema against the model file of the release you are going back to, and confirm that the tables, columns, data types, and default expressions all match it.
- Ask the catalog for the constraints and indexes the script recreated, using
pg_constraintandpg_indexesin PostgreSQL, orinformation_schema.table_constraintsin MySQL. - Restart or flush the application connection pools, so that no cached prepared statement still refers to a column position the rollback moved.
- Record the reversal in the migration ledger table, or remove the failed forward entry, so the next pipeline run does not treat the corrected version as already applied.
The constraint check is one query per table in PostgreSQL:
SELECT conname, contype FROM pg_constraint WHERE conrelid = 'accounts'::regclass;
A rollback you can generate is a rollback you can rehearse before the release rather than improvise after it. Download DbSchema at https://dbschema.com/download.html, commit the .dbs model file alongside the migration that changes it, and generate the reverse script by comparing the two versions. Saving the model to a file and schema synchronization are in the Pro edition.
Frequently asked questions
Can you roll back a schema migration in MySQL?
MySQL 8.4 commits a DDL statement as it runs, so a later ROLLBACK cannot reach it. Atomic DDL means the statement either finishes completely or leaves nothing behind, which is not the same as being reversible by the transaction around it. Undoing a MySQL schema change takes a compensating script that states the opposite change.
Can you roll back an ALTER TABLE in PostgreSQL?
PostgreSQL 17 runs DDL inside the surrounding transaction, so an ALTER TABLE issued after BEGIN is undone by ROLLBACK. The table goes back to the definition it had when the transaction started, and the ACCESS EXCLUSIVE lock the statement took is released with it.
Does reversing a schema change restore deleted data?
A reverse script restores definitions only. A column added back after a DROP COLUMN holds NULL or its default in every existing row, and a table recreated after a DROP TABLE is empty. The values come back from a backup or a snapshot taken before the migration ran.
How do you generate a reverse script in DbSchema Pro Edition?
DbSchema stores the design as an XML .dbs model file, so the version matching the previous release is already in your Git history. Open that file and the current model in DbSchema and synchronize between them: the Synchronization Dialog lists the differences and generates the migration script that reverses them.
Why did the rollback script run cleanly but the application still breaks?
A partial rollback runs cleanly. Reversing some of the changes in a deployment and not the others leaves the catalog in a shape neither the old release nor the new one was tested against, and the application fails on whichever part was left in place.
What do you check after a rollback?
Compare the live database against the model of the release you are going back to, and confirm the object list matches. Check that dependent views and foreign keys are back, and that the connection pools hold no stale prepared statements. Record the reversal in the migration ledger table.
Sources
Generate the reverse script for your next rollback
DbSchema compares two versions of your schema model and generates the migration script to reverse a change, so the rollback is ready before you need it.
