Does dbRollback() undo an ALTER TABLE executed inside a DBI transaction?

Answer

Short answer: No, DBI does not guarantee it. dbRollback() will undo an ALTER TABLE with SQLite and PostgreSQL, but not with MariaDB/MySQL via RMariaDB. Rollback safety for DDL is backend-dependent, not a DBI guarantee.

Confirmed facts by backend

• SQLite via RSQLite: Supports transactional DDL in default and WAL modes. An ALTER TABLE executed inside dbBegin() can be reverted by dbRollback().
• PostgreSQL via RPostgres: Fully supports transactional DDL. ALTER TABLE inside a dbBegin()/dbRollback() block is undone by dbRollback().
• MariaDB/MySQL via RMariaDB: Issues an implicit commit before DDL. Any active transaction is committed by ALTER TABLE, so dbRollback() afterwards cannot undo the schema change.

DBI role

DBI defines dbBegin, dbCommit and dbRollback as an API. It does not specify how a DBMS handles DDL inside a transaction. The SQL ROLLBACK issued by dbRollback() is subject to the underlying DBMS's transactional DDL rules and driver implementation.

Portable pattern for schema migrations

Portability across RSQLite, RMariaDB and RPostgres requires per-backend handling:

1. Isolate DDL from DML. Run schema changes in their own short transactions.
2. Do not rely on dbRollback() to protect ALTER TABLE on MariaDB/MySQL. Treat DDL as non-rollbackable there.
3. For SQLite and PostgreSQL you can wrap DDL in dbBegin()/dbRollback() for failure safety; for MariaDB/MySQL use application-level checks and forward-only migration state.

Per-backend branching on dbGetInfo(con)$dbname or driver class is the accepted approach today.

Verification steps

Confirm behavior in your environment by checking the catalog after rollback:

# SQLite example
con <- dbConnect(RSQLite::SQLite(), "file.db")
dbBegin(con)
dbExecute(con, "ALTER TABLE test ADD COLUMN x INTEGER")
dbRollback(con)
# query sqlite_master to verify column not present
dbDisconnect(con)

Repeat with information_schema for PostgreSQL and MariaDB. For SQLite, test with a file-backed database rather than :memory: for reliable results.

Assumption: current CRAN DBI, RSQLite, RPostgres and RMariaDB with stable transactional DDL semantics. Driver updates can change behavior, so verify with your versions.