Explicit transaction boundaries or compensating actions after Ballerina schema change

Direct Answer: Decision Criteria

Use explicit transaction boundaries only when your target database natively supports transactional DDL (e.g., PostgreSQL, SQL Server, Oracle). Use compensating actions when the database performs implicit commits on DDL (e.g., MySQL, MariaDB) or when the schema change must coordinate across multiple services where a single database transaction cannot span all participants.

Why This Split Exists

Ballerina's sql:Client and transaction API (sql:beginTransaction, commit, rollback) delegate DDL execution to the underlying JDBC driver. The driver, in turn, obeys the database engine's rules:

• PostgreSQL: CREATE TABLE, ALTER TABLE, DROP TABLE run inside the transaction and roll back cleanly.
• MySQL / MariaDB: DDL statements trigger an implicit commit before and after execution. A subsequent rollback() cannot undo the DDL.
• SQL Server: Most DDL is transactional, but some operations (e.g., CREATE DATABASE) autocommit.

Ballerina 1.5+ does not add a language-level abstraction that normalizes this behavior. The release notes and ballerina/sql module documentation continue to state that transaction semantics follow the JDBC driver and database. No version-specific guidance overrides the engine-dependent reality.

How to Verify for Your Stack

1. Run a test migration inside a Ballerina transaction block:

   transaction {
       _ = check db->execute(`CREATE TABLE test_rollback (id INT)`);
       // force failure
       error? err = db->execute(`INVALID SQL`);
       check err;
   } else {
       // rollback executed automatically
   }

2. After the block, check whether test_rollback exists. If it does, your database/driver does not support transactional DDL for that statement.
3. Consult the JDBC driver changelog for your exact version (e.g., MySQL Connector/J 8.0.x, PostgreSQL JDBC 42.x) to confirm implicit-commit behavior.

Compensating-Action Checklist (When Transactions Are Insufficient)

If verification shows DDL is not transactional, define a compensating script before applying the forward migration:

• Pair every CREATE with a DROP IF EXISTS.
• Pair every ALTER TABLE … ADD COLUMN with ALTER TABLE … DROP COLUMN IF EXISTS.
• Store the compensating statements in version control alongside the forward migration.
• Execute compensations in reverse order of the forward steps.
• Log each step's success/failure to an audit table so a re-run can detect omitted or duplicated actions.

Caution: Compensating actions are not atomic. A failure halfway through leaves the schema in an intermediate state that may require manual intervention.

Transactional Outbox Interaction

The transactional outbox pattern writes domain events to an outbox table inside the same local transaction that commits the schema change (or the compensating action's final step). This guarantees:

• If the schema change commits, the event is durably stored.
• If the schema change rolls back (transactional DDL) or the compensation completes, no event is emitted.
• A separate publisher (CDC, polling, or Ballerina scheduled task) reads the outbox and emits messages with at-least-once semantics.

When eventual consistency is acceptable, the outbox decouples the schema-change safety decision from downstream notification reliability. You still must choose transactions vs. compensations for the schema change itself; the outbox only ensures that once the schema state is final, the event is not lost.

One Diagnostic Detail That Changes the Recommendation

Which database engine and major version are you targeting? If you confirm PostgreSQL ≥ 12 or SQL Server ≥ 2019, explicit transactions are safe for most DDL. If MySQL/MariaDB or an older engine, plan compensating actions.