Support for nested transactions (using savepoints) - with db.atomic()

For v4 I want to get our transactions story straight.

We have some surprising behavior there already. The table.transform() method starts its own transaction in a way that abruptly commits and existing transactions. There are a few other surprises tucked around too.

SQLite supports nested transactions using savepoints. We should support those too.

I think with db.atomic(): is good syntax for this, as also seen in Django and Peewee.

It would be very cool if this enabled us to provide a robust dry run option for migrations from #752 by running the entire thing in a nested transaction and then failing to commit it.

One sticky point is this in the transform() method:

sqlite-utils/sqlite_utils/db.py

Lines 1911 to 1923 in 3cc27d6

    
           try: 
        
               if pragma_foreign_keys_was_on: 
        
                   self.db.execute("PRAGMA foreign_keys=0;") 
        
               with self.db.conn: 
        
                   for sql in sqls: 
        
                       self.db.execute(sql) 
        
                   # Run the foreign_key_check before we commit 
        
                   if pragma_foreign_keys_was_on: 
        
                       self.db.execute("PRAGMA foreign_key_check;") 
        
           finally: 
        
               if pragma_foreign_keys_was_on: 
        
                   self.db.execute("PRAGMA foreign_keys=1;") 
        
           return self

Changing PRAGMA foreign_keys=0; is only allowed outside a transaction: https://sqlite.org/foreignkeys.html#fk_enable

It is not possible to enable or disable foreign key constraints in the middle of a multi-statement transaction (when SQLite is not in autocommit mode). Attempting to do so does not return an error; it simply has no effect.

GPT-5.5 xhigh experiment:

import sqlite3

sqls = [
    'CREATE TABLE authors_new(id INTEGER PRIMARY KEY, name TEXT)',
    'INSERT INTO authors_new(rowid, id, name) SELECT rowid, id, name FROM authors',
    'DROP TABLE authors',
    'ALTER TABLE authors_new RENAME TO authors',
]
for outer in [False, True]:
    con=sqlite3.connect(':memory:')
    con.execute('PRAGMA foreign_keys=ON')
    con.execute('CREATE TABLE authors(id INTEGER PRIMARY KEY, name TEXT)')
    con.execute('CREATE TABLE books(id INTEGER PRIMARY KEY, author_id INTEGER REFERENCES authors(id))')
    con.execute('INSERT INTO authors VALUES(1,"a")')
    con.execute('INSERT INTO books VALUES(1,1)')
    con.commit()
    print('\nouter', outer)
    try:
        con.execute('BEGIN')
        if outer:
            con.execute('SAVEPOINT sp')
        con.execute('PRAGMA defer_foreign_keys=ON')
        for s in sqls: con.execute(s)
        print('fk_check rows', con.execute('PRAGMA foreign_key_check').fetchall())
        con.execute('PRAGMA defer_foreign_keys=OFF')
        if outer:
            con.execute('RELEASE sp')
            print('released sp')
        con.commit()
        print('commit ok')
    except Exception as e:
        print(type(e).__name__, e, 'in_tx', con.in_transaction)
        try: print('fk_check rows after err', con.execute('PRAGMA foreign_key_check').fetchall())
        except Exception as ee: print('fkcheck err', ee)

Outputs:

outer False
fk_check rows []
commit ok

outer True
fk_check rows []
released sp
commit ok

This shows that there is a path forward here: we can run PRAGMA defer_foreign_keys=ON inside a savepoint to tell SQLite to delay those FK checks until the end of the whole transaction (not just the current save point). Then we apply the changes - any foreign key problems should be resolved by the final table rename before we run PRAGMA foreign_key_check to confirm.

The Python executescript() method is a bit tricky here: https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.executescript

Execute the SQL statements in sql_script. If the autocommit is LEGACY_TRANSACTION_CONTROL and there is a pending transaction, an implicit COMMIT statement is executed first. No other implicit transaction control is performed; any transaction control must be added to sql_script.

This could mess with our own transaction handling.

Looks like autocommit=sqlite3.LEGACY_TRANSACTION_CONTROL is a default that was introduced in Python 3.12: https://docs.python.org/3/whatsnew/3.12.html#sqlite3

Older Python versions don't give you any way of opting out. 3.12+ default to that behavior but have a configuration to do something different.

db.atomic() method

Release 4.0rc1

Navigation Menu

Repository navigation

Support for nested transactions (using savepoints) - with db.atomic() #755

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

	try:
	if pragma_foreign_keys_was_on:
	self.db.execute("PRAGMA foreign_keys=0;")
	with self.db.conn:
	for sql in sqls:
	self.db.execute(sql)
	# Run the foreign_key_check before we commit
	if pragma_foreign_keys_was_on:
	self.db.execute("PRAGMA foreign_key_check;")
	finally:
	if pragma_foreign_keys_was_on:
	self.db.execute("PRAGMA foreign_keys=1;")
	return self

Support for nested transactions (using savepoints) - with db.atomic() #755

Description

Activity

simonw commented on Jun 21, 2026

simonw commented on Jun 21, 2026

simonw commented on Jun 21, 2026

simonw commented on Jun 21, 2026

simonw commented on Jun 21, 2026

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions