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

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.

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:

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:

1. A database connection is established
2. Your operations are grouped in a transaction
3. 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:

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:

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¶

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:

1. Your changes are saved permanently
2. Other sessions/connections can now see those changes
3. If your application crashes, the data remains intact

4. Rollback: Your Safety Net¶

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

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:

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:

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:

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¶

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:

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¶

# 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:

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

Pitfall 3: Not Handling Errors Properly¶

# 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:

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¶

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.

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