Flushing vs Committing

7/7/25

I’ve been using a lot of sqlalchemy lately and keep doing this pattern:

• Make a bunch of changes to the database
• Log those changes
• Depending on whether I want to save the changes, commit or not

But sometimes I run in to an issue where when I test, things are all ok. However, once I finally am ready to run in prod, but then call .commit(), I get a ton of PK errors or other issues. The thing I was missing was .flush()

Flushing will:

• create primary keys or default values
• check constraints (unique, FK)
• Flushing inside a session without calling .commit() will NOT save the changes in the database

Whereas committing actually executes the changes without the ability to rollback.

That’s very useful, because now I can do something like this:

from sqlalchemy import Session
from models import Book, Publisher

def summarize_changes(session) -> dict:
    changes = {
        'inserts': [...],
        'updates': [...],
        'deletes': [...]
    }
    with open('changes.json') as f:
        f.write(changes)

def make_database_changes(dev=True) -> None:
    with Session(autoflush=False) as session:
        new_book = Book(title='Moby Dick', publisher=3)
        session.add(new_book)
        summarize_changes(session)
        if dev:
            # Flush changes, make sure we don't have any PK issues
            # For example, if publisher ID = 3 doesn't exist, an exception
            # is raised HERE on .flush()
            session.flush()
        else:
            # Otherwise, we commit our changes
            session.commit()

Also worth noting that its important to turn autoflush off when the session object is created.