Transactions, Commits, and Rollbacks in SQLModel: A Mental Model That Actually Makes Sense

Learn how to effectively manage database transactions in Python using SQLModel through an intuitive Git-based mental model and proven optimization patterns.

2025-03-29

Every developer working with databases has experienced that frustrating moment when their application grinds to a halt because of connection issues or unexplained slowdowns.

If you've ever wondered why your SQLModel operations are crawling along or connections are being dropped, you're not alone.

The problem isn't your code's logic - it's how you're managing database sessions.

I learned this the hard way after creating bottlenecks by constantly opening new connections instead of properly managing and reusing sessions.

By the end of this post, you'll understand:

Why creating a new session for every operation kills performance

When exactly you should commit() or rollback()

A simple mental model that makes SQLModel transactions intuitive

Bulletproof patterns for managing database connections efficiently

The best part? You'll never again wonder why your application is suddenly crawling along due to connection pool exhaustion.

The Git Model: A Better Way to Think About Database Transactions

Database transactions can be confusing until you have the right mental model.

Here's the simplest way to understand what's happening:

Your SQLModel session works almost exactly like Git.

SQLModel Action	Git Equivalent	What's Happening
`session.add(user)`	`git add`	Stages changes but doesn't save them permanently
`session.commit()`	`git commit && git push`	Makes changes permanent and visible to everyone
`session.rollback()`	`git reset --hard`	Discards all uncommitted changes

Think of it like this: Just as you wouldn't create a new Git repo for every file change, you shouldn't create new database sessions for every operation.

Not only does this analogy make transactions clearer, it also explains why your app slows to a crawl when you keep creating new sessions instead of reusing them.

Sessions: Your Database Workspace

A session in SQLModel represents your connection to the database for a series of operations:

python

from sqlmodel import Session
from .database import engine

def create_user():
    with Session(engine) as session:
        # Your database operations here
        new_user = User(name="Alice")
        session.add(new_user)
        session.commit()  # Don't forget this!

This with block ensures:

A database connection is established

Your operations are grouped in a transaction

The connection is properly closed when you're done

But here's what most tutorials miss: without that commit() call, your changes exist only in memory - never reaching the actual database.

The Lifecycle of a Database Change

Understanding the journey your data takes clarifies when and why to commit:

1. Adding Objects: Just Staging Changes

When you create a new record:

python

new_user = User(name="Alice")
session.add(new_user)

You've only told SQLModel: "I want to insert this object."

This is equivalent to git add - you're staging the change, not saving it permanently.

Your data is not in the database yet.

2. Querying Without Committing: The Confusing Part

Here's where things get tricky.

Within the same session, you can query your new object even without committing:

python

session.add(new_user)

# This works - even though it's not committed!
found_user = session.exec(select(User).where(User.name == "Alice")).first()

This works because SQLModel maintains an identity map of objects in memory and flushes changes when needed.

But:

Other sessions can't see your changes

If your application crashes, those changes are lost

If you call rollback(), the changes disappear

3. Committing: Making It Real

python

session.commit()

This is where the magic happens - your changes are now permanently stored in the database.

Think of this as both git commit and git push combined:

Your changes are saved permanently

Other sessions/connections can now see those changes

If your application crashes, the data remains intact

4. Rollback: Your Safety Net

Before committing, if you realize you've made a mistake:

python

session.rollback()

This discards all uncommitted changes in the current transaction - like git reset --hard before pushing.

Important: Once you've committed changes, rollback() can't remove them.

You'd need a new transaction to revert that data.

Practical Patterns for Real Applications

Now that you understand the mental model, here are battle-tested patterns for common scenarios:

Pattern 1: The Basic Transaction

Use this when you want several operations to succeed or fail together:

python

with Session(engine) as session:
    try:
        # Multiple operations in one transaction
        user = User(name="Alice")
        session.add(user)
        
        post = Post(title="First Post", user_id=user.id)
        session.add(post)
        
        # Commit only after all operations succeed
        session.commit()
    except Exception as e:
        session.rollback()  # Revert partial changes
        # Log error, handle exception, etc.

Pattern 2: Multiple Transactions in One Session

Sometimes you want to save progress incrementally:

python

with Session(engine) as session:
    # Transaction 1
    alice = User(name="Alice")
    session.add(alice)
    session.commit()  # Alice is now safely in the DB
    
    try:
        # Transaction 2
        bob = User(name="Bob")
        session.add(bob)
        # Potentially risky operation
        result = risky_operation()
        session.commit()  # Bob is saved
    except Exception:
        session.rollback()  # Only Bob is rolled back, Alice remains

This approach saves partial progress, ensuring some data persists even if later operations fail.

Pattern 3: High-Performance Batch Operations

For bulk imports or analytics tasks:

python

BATCH_SIZE = 1000
with Session(engine) as session:
    for i, item in enumerate(large_dataset):
        user = User(name=f"User {item['name']}")
        session.add(user)
        
        # Commit in batches to avoid memory issues
        if i % BATCH_SIZE == 0:
            session.commit()
    
    # Commit any remaining items
    session.commit()

This pattern prevents memory bloat when processing large datasets and saves progress incrementally.

Common Pitfalls and Their Solutions

Pitfall 1: The Detached Object

python

with Session(engine) as session:
    user = User(name="Alice")
    session.add(user)
    session.commit()

# Session is now closed
user.name = "Alicia"  # This change won't be saved!

Solution: Reattach the object to a new session before modifying:

python

with Session(engine) as session:
    # Reattach the user to this session
    session.add(user)
    user.name = "Alicia"
    session.commit()

Pitfall 2: Forgetting to Close Sessions

python

# Bad practice - session never closed
session = Session(engine)
user = session.exec(select(User).first())

Solution: Always use context managers (with statements) to ensure proper cleanup:

python

with Session(engine) as session:
    user = session.exec(select(User)).first()

Pitfall 3: Not Handling Errors Properly

python

# Risky approach
with Session(engine) as session:
    # If this raises an exception, we never reach commit
    user = process_data()
    session.add(user)
    session.commit()

Solution: Use try/except blocks to handle errors and ensure rollback:

python

with Session(engine) as session:
    try:
        user = process_data()
        session.add(user)
        session.commit()
    except Exception as e:
        session.rollback()
        # Handle or log the error

Final Thoughts: When to Commit?

The most common question is: "How often should I commit?"

Rule of thumb: Commit after each logical unit of work.

Too frequent:

Committing after every single add() creates unnecessary database overhead.

Too rare:

Waiting too long between commits risks losing work and creates lock contention.

Think of a commit as drawing a line under a completed task.

When one logical operation is done (creating a user and their initial posts, for example), commit those changes together.

Summary: The SQLModel Transaction Cheatsheet

Action	What It Does	Best Practice
`session.add(obj)`	Stages an object for insertion/update	Use for all new or modified objects
`session.flush()`	Sends pending changes to DB (automatically called by SQLModel when needed)	Rarely needed explicitly
`session.commit()`	Permanently saves all changes	Call after completing a logical unit of work
`session.rollback()`	Discards uncommitted changes	Call when something goes wrong
`with Session() as session:`	Creates and automatically closes session	Always use this pattern

Remember the Git analogy: add, commit, push.

In SQLModel, it's add, commit - two steps that keep your data safe and your mental model clear.

Effective database management in Python requires understanding both the SQLModel API and fundamental transaction principles.

By following best practices around session management, error handling, and strategic commits, you can build robust and reliable database interactions.

Subscribe to My Newsletter

Want to stay updated on more content like this? Subscribe to my newsletter below.