FireDAC schema changes: wrap DDL in one transaction or go forward-only with compensating scripts?

Question

I'm designing a schema migration strategy for a RAD Studio (Delphi) application that uses FireDAC against more than one target DBMS. The goal is a rollback-safe upgrade path when a schema change fails partway through.

Two documented approaches seem viable. First, wrap the DDL in an explicit transaction using TFDConnection.StartTransaction / Commit / Rollback, which works cleanly on Firebird and InterBase where DDL is transactional. Second, use forward-only migrations via TFDScript, pairing each change with a tested compensating script, which is the pattern recommended for DBMSs like Oracle or MySQL where DDL triggers an implicit commit and Rollback cannot undo it.

The tension: transactional DDL gives atomic rollback but only on DBMSs that support it, while forward-only scripts are portable but demand disciplined, tested compensating changes that can be destructive. I'm also unsure how much TxOptions and auto-commit settings affect whether my DDL actually runs inside the explicit transaction at all.

Should I standardize on one strategy across all targets, or branch per DBMS? How can I verify at runtime that a given DDL statement really executed inside my explicit transaction rather than auto-committing? Are there practical limits (e.g., schema locks on Firebird) that argue against long DDL transactions?

Zoha · Answer

AnswerBranch per DBMS. Do not standardize on one strategy. Use transactional DDL with explicit TFDConnection.StartTransaction/Commit/Rollback on engines that support transactional DDL, and use forward-only migrations with tested compensating scripts on engines where DDL forces an implicit commit. Branching is the only rollback-safe way to cover Firebird/InterBase/PostgreSQL/SQL Server and Oracle/MySQL/SQLite in the same codebase.Confirmed factsFireDAC will execute DDL inside an explicit transaction when the driver and DBMS allow it. Whether Rollback actually undoes the DDL is a DBMS property, not a FireDAC property. Firebird and InterBase support transactional DDL. PostgreSQL and SQL Server support transactional DDL for most schema changes. Oracle and MySQL issue an implicit commit on many DDL statements, so Rollback cannot undo them. SQLite auto-commits DDL outside a transaction.TFDConnection.AutoCommit controls whether FireDAC auto-commits each statement. For manual control set AutoCommit to False and use StartTransaction. TxOptions.AutoCommit and the driver-specific options affect this. TFDScript executes statements sequentially in the current transaction context when AutoCommit is False.Likely practical considerationsLong DDL transactions hold schema locks. On Firebird, a DDL transaction acquires exclusive locks on system tables and the affected objects, blocking concurrent DDL and sometimes DML until Commit/Rollback. For large tables or online systems, keep the transactional batch small and short.Forward-only with compensating scripts is portable but requires idempotent, tested reverse scripts and careful ordering. Compensating scripts must be safe to run only after a known failure point.Steps for this caseList target DBMS and engine versions. Map each to transactional DDL support.For transactional targets: set Connection.AutoCommit to False, StartTransaction, run DDL via TFDScript or individual commands, Commit on success, Rollback on exception.For non-transactional targets: disable explicit transaction wrapping, execute DDL statements one by one, log each applied step, and on failure run the pre-written compensating script for the last successful step.Verify runtime behavior with a test: begin transaction, execute CREATE TABLE test_tx, Rollback, then query for the table. Absence confirms transactional DDL. Presence confirms implicit commit.To change the recommendation I need the exact DBMS and engine versions you must support. Which DBMS versions are in scope for this release?

FireDAC schema changes: wrap DDL in one transaction or go forward-only with compensating scripts?

1 answer

Answer

Confirmed facts

Likely practical considerations

Steps for this case

Your perspective belongs here.

0 question comments