SQLite 3.28: Should Python’s sqlite3 expose a rollback‑allowed flag for DDL?

Answer to the Question

Short answer: No, Python’s sqlite3 module does not need an additional rollback_allowed flag for DDL. The existing isolation_level attribute already gives developers full control over whether DDL statements are wrapped in an implicit transaction.

Why the Flag Is Unnecessary

• SQLite 3.28 and newer support fully transactional DDL for all operations that are implemented. Unsupported ALTER TABLE forms (e.g., DROP COLUMN) raise OperationalError and automatically abort the transaction, so a rollback is guaranteed.
• The Python sqlite3 module wraps every statement in a transaction unless the connection is in autocommit mode (i.e., conn.isolation_level = None). This is the documented way to opt out of the default wrapping.
• Because the isolation level already expresses the desired behaviour, adding another flag would be redundant and could confuse developers who already know how to enable or disable autocommit.

Confirming SQLite’s Capability

Before executing schema changes you can verify that the underlying SQLite build supports transactional DDL:

import sqlite3
conn = sqlite3.connect(':memory:')
print('SQLite version:', conn.execute('SELECT sqlite_version();').fetchone()[0])
print('Compile options:', conn.execute('PRAGMA compile_options;').fetchall())

Look for the ENABLE_FTS5 and ENABLE_JSON1 options – their presence indicates a modern build that includes transactional DDL support.

Testing Rollback Behaviour

1. Transactional test:

   conn.execute('BEGIN')
   conn.execute('CREATE TABLE t(id INTEGER)')
   conn.execute('ROLLBACK')
   print('Tables after rollback:', conn.execute("SELECT name FROM sqlite_master WHERE type='table'").fetchall())
   

   The table should not exist after the rollback.

2. Unsupported DDL test:

   try:
       conn.execute('BEGIN')
       conn.execute('ALTER TABLE t DROP COLUMN id')
   except sqlite3.OperationalError as e:
       print('Error:', e)
       conn.execute('ROLLBACK')
   

   Even though the operation fails, the transaction is automatically rolled back.

3. Autocommit test:

   conn.isolation_level = None
   conn.execute('CREATE TABLE a(id INTEGER)')
   print('Tables after autocommit:', conn.execute("SELECT name FROM sqlite_master WHERE type='table'").fetchall())
   

   The table is committed immediately, and there is no surrounding transaction to roll back.

Best‑Practice Patterns for Safe Schema Changes

• Always use conn.isolation_level = None only when you intentionally want the DDL to commit immediately and are confident the operation is safe.
• Wrap schema changes in an explicit BEGIN … COMMIT block if you want full control and to be able to roll back on error.
• For third‑party adapters (e.g., APSW, pysqlite), consult their documentation to confirm they respect SQLite’s transactional DDL semantics.
• When targeting older SQLite versions (<3.24), be aware that transactional DDL is not guaranteed; in those cases, consider performing schema changes in a separate process or using a migration framework that records applied changes.

Missing Diagnostic Detail

If you are using a SQLite build older than 3.28 or a third‑party adapter that may behave differently, please let us know the exact SQLite version and adapter name. This will help refine the recommendation for that environment.