LuaSQL transaction limits regarding DDL schema changes

Question

When managing database migrations via LuaSQL, there is a discrepancy in how Data Definition Language (DDL) statements interact with transaction boundaries compared to Data Manipulation Language (DML) statements.

In many database drivers, executing a schema change—such as CREATE TABLE or ALTER TABLE—triggers an implicit commit. This behavior potentially bypasses the rollback mechanism, leaving the schema modified even if the surrounding transaction is aborted. This creates a risk of inconsistent states when DDL and DML are combined in a single logical unit of work.

Because the LuaSQL API provides a generic interface across multiple drivers (SQLite, MySQL, PostgreSQL), the level of atomicity for schema changes is not standardized.

Which LuaSQL drivers support true transactional DDL that can be reverted via a rollback?
  Is there a programmatic way within the LuaSQL API to detect if a driver will trigger an implicit commit upon executing a schema change?

Tasadduq Burney · Answer

Answer the exact question first
Transactional DDL support in LuaSQL

luasql.postgres – DDL (CREATE, ALTER, DROP, etc.) is fully transactional and can be rolled back.
  luasql.sqlite3 – SQLite’s DDL is also transactional; a ROLLBACK undoes schema changes.
  luasql.mysql – Most DDL statements trigger an implicit commit (InnoDB, MyISAM, and the default autocommit=1 setting). They cannot be rolled back.
  luasql.odbc / luasql.oracle – These drivers typically issue an implicit commit on DDL, so rollback does not revert the change.

So, only PostgreSQL and SQLite provide true transactional DDL in LuaSQL.
Is there a programmatic way to detect implicit commits?
LuaSQL does not expose a flag or method that tells you whether a driver will commit automatically on DDL. The standard way to discover this is:

Begin a transaction: conn:execute("BEGIN") (or rely on autocommit being off).
  Execute a harmless DDL statement, e.g., conn:execute("CREATE TABLE test_ddl (id INTEGER)").
  Execute conn:execute("ROLLBACK").
  Query the catalog to see if the table still exists. If it does, the driver performed an implicit commit.

Alternatively, you can infer from the driver name and version:

DriverTransactional DDL?Notes
  
    luasql.postgresYesRequires BEGIN if autocommit is on.
    luasql.sqlite3YesSQLite always treats DDL as part of the transaction.
    luasql.mysqlNoImplicit commit on most DDL; temporary tables may differ.
    luasql.odbc / luasql.oracleNoImplicit commit on DDL; check driver docs for exceptions.

Practical steps for your migration code

Wrap the entire migration in a transaction: conn:execute("BEGIN").
  Run all DDL and DML statements.
  If the driver supports transactional DDL, a conn:execute("ROLLBACK") will undo everything.
  For drivers that don’t, separate the schema changes into a dedicated migration step that is run independently of the transactional block.

Missing diagnostic detail
Could you let me know which LuaSQL driver and version you are using? That will help confirm whether your environment behaves as described above.

LuaSQL transaction limits regarding DDL schema changes

1 answer

Answer the exact question first

Is there a programmatic way to detect implicit commits?

Practical steps for your migration code

Missing diagnostic detail

Your perspective belongs here.

0 question comments

Driver	Transactional DDL?	Notes
luasql.postgres	Yes	Requires `BEGIN` if autocommit is on.
luasql.sqlite3	Yes	SQLite always treats DDL as part of the transaction.
luasql.mysql	No	Implicit commit on most DDL; temporary tables may differ.
luasql.odbc / luasql.oracle	No	Implicit commit on DDL; check driver docs for exceptions.