Java.DBMigrationTools.Can database changes be rolled back?

✅ Short Answer

Yes, database changes can be rolled back — but only under specific conditions:

• ✅ In a transaction → changes can be rolled back automatically on failure.
• ❌ Most schema changes (DDL) like CREATE TABLE are not transactional in many databases.
• ✅ Migration tools like Liquibase can rollback if you explicitly define rollback logic.
• ❌ Tools like Flyway do not rollback — you must undo changes manually.

---

🔎 1. Database Transaction Rollback

✅ If you’re performing DML operations (insert, update, delete) in a transaction:

BEGIN;
UPDATE users SET email = 'test@example.com' WHERE id = 1;
ROLLBACK; -- Undoes the update

✅ Works in most RDBMS (PostgreSQL, MySQL with InnoDB, etc.)

❌ But many DDL statements (like ALTER TABLE, CREATE TABLE) are auto-committed:

• PostgreSQL supports transactional DDL
• MySQL/MariaDB does not (DDL can’t be rolled back)
• Oracle supports transactional DDL in some cases
• SQLite does support it

---

🔎 2. Liquibase Rollback

✅ You can define explicit rollback in changesets:

<changeSet id="3" author="alice">
  <addColumn tableName="users">
    <column name="phone" type="varchar(20)"/>
  </addColumn>
  <rollback>
    <dropColumn tableName="users" columnName="phone"/>
  </rollback>
</changeSet>

Then run

liquibase rollbackCount 1

✅ Liquibase will run the rollback logic to revert changes.

❌ But this is manual — rollback only happens when you run it.
❌ There’s no automatic rollback on error during a migration.

🔎 3. Flyway Rollback

❌ Flyway has no rollback feature.

✅ If a migration fails:

• The script is marked as failed
• You must fix manually
• Optionally write an “undo” migration (U1__undo_users.sql), but Flyway won’t run it automatically

---

📌 Key Takeaways

Context	Rollback possible?	How?
SQL transactions	✅ For DML (and some DDL)	Use BEGIN; ... ROLLBACK;
Liquibase	✅ If defined	Use <rollback> blocks + rollback commands
Flyway	❌ Not supported natively	Manual repair or versioning
DDL (e.g. CREATE)	❌ In most DBs	Use tool-level rollback or backups