KAHIBARO
Discord Login Register ☰
☰

12.4. Database Sessions

Table of Contents

Understanding Database Sessions

When you use an ORM, you almost never talk to the database directly. Instead, you work through a database session.

A session is the unit that:

If you understand sessions clearly, you avoid many confusing bugs like “my data did not save” or “why did this delete happen so late?”.

This chapter will focus on sessions in the context of a typical Python ORM, especially SQLAlchemy, but the concepts apply to most ORMs.

What Is a Database Session?

You can think of a database session as:

Typical flow:

  1. Open a session.
  2. Run queries and modify objects.
  3. Commit or roll back.
  4. Close the session.

A database session is not the same as a user session in web apps.
A database session is about communication with the database.
A user session is about tracking a logged in user.

Sessions vs Connections

A common confusion: session vs connection.

In SQLAlchemy terms:

You rarely handle connections directly when using an ORM. You create and use sessions.

Example with SQLAlchemy style code:

python 
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine("postgresql+psycopg2://user:password@localhost/dbname")
SessionLocal = sessionmaker(bind=engine, autoflush=False, autocommit=False)
# Create a session
db = SessionLocal()   # This is a session, not a raw DB connection

The session will ask the engine for a connection when it actually needs to talk to the database, for example when executing a query or a flush.

The Identity Map: One Row, One Object

A key feature of ORM sessions is the identity map.

Inside a session, for each database row, there is at most one Python object that represents it.

For example, consider a User table and a User ORM model:

python 
user1 = db.query(User).filter(User.id == 1).first()
user2 = db.query(User).filter(User.id == 1).first()
print(user1 is user2)  # True

Within the same session:

This identity map is maintained per session. If you use two different sessions, each can have its own copy of the same row:

python 
db1 = SessionLocal()
db2 = SessionLocal()
user1 = db1.query(User).get(1)
user2 = db2.query(User).get(1)
print(user1 is user2)  # False, different sessions, different objects

This is why it is important to understand session scope, which we will cover soon.

Session Lifecycle: From Creation to Close

A typical session lifecycle looks like this:

  1. Create a session (often per web request).
  2. Use it for queries and changes.
  3. Commit or rollback.
  4. Close it.

Example in pseudo SQLAlchemy code:

python 
db = SessionLocal()   # 1. create
try:
    # 2. use it
    user = db.query(User).filter(User.email == "test@example.com").first()
    user.name = "New Name"
    db.commit()       # 3. commit changes
except:
    db.rollback()     # rollback on error
    raise
finally:
    db.close()        # 4. close always

Important points:

Transactions and Sessions

In most ORMs, a session controls transactions.

A commit sends pending changes to the database permanently.
A rollback cancels all changes since the last commit.

Example:

python 
db = SessionLocal()
user = User(email="a@example.com")
db.add(user)          # not in the database yet, only in session
db.commit()           # INSERT is executed here, transaction ends
user.name = "Alice"
db.commit()           # UPDATE is executed here

If an error happens before the second commit:

python 
db = SessionLocal()
try:
    user = User(email="b@example.com")
    db.add(user)
    # Some error happens here
    raise ValueError("Oops")
    db.commit()
except:
    db.rollback()     # user is not inserted at all
finally:
    db.close()

Flushing vs Committing

Sessions have two related but different actions: flush and commit.

In SQLAlchemy:

You might explicitly call flush() if you need database generated values before committing, for example an auto increment id.

Example:

python 
user = User(email="c@example.com")
db.add(user)
print(user.id)   # None, not yet flushed or committed
db.flush()       # send INSERT to DB, but still in the same transaction
print(user.id)   # Now has the generated ID from the database
db.commit()      # transaction is now completed

A flush uses the current transaction. If you roll back after flushing, those changes are undone.

Object States Inside a Session

ORMs track the state of objects while they are attached to a session. Common states:

StateDescriptionExample
transientNew object, not attached to any session, no DB rowuser = User(email="x@y.com") before add()
pendingAttached to session, will be inserted on flushAfter session.add(user) but before flush / commit
persistentExists in database and attached to a sessionAfter commit, or after being loaded via a query
detachedWas persistent, but session is closed or object removedAfter session.close() or session.expunge(user)
deletedMarked for deletion in this session, will be deleted on flushAfter session.delete(user) before commit

You usually do not need to manage these states manually, but you should know they exist so behavior makes sense.

Example:

python 
user = User(email="x@y.com")  # transient
db.add(user)                  # pending
db.commit()                   # becomes persistent
db.close()                    # user object now detached

If you modify a detached object, the session will not see it automatically. You must reattach it if you want to persist changes, which you will see in the next section.

Adding, Updating, and Deleting with a Session

Adding new records

To add a new row:

  1. Create a model instance.
  2. Add it to the session.
  3. Commit.
python 
user = User(email="new@example.com")
db.add(user)
db.commit()
db.refresh(user)  # often used in SQLAlchemy to get updated values
print(user.id)    # has database generated ID

db.refresh(user) reloads the object from the database. Some frameworks do this for you, others require it explicitly.

Updating existing records

You usually update by:

  1. Querying the object.
  2. Changing attributes.
  3. Committing.
python 
user = db.query(User).filter(User.id == 1).first()
user.name = "Updated Name"
db.commit()   # ORM detects that 'name' changed and issues an UPDATE

The ORM tracks dirty objects (objects with changed attributes). On flush or commit, it generates UPDATE only for those objects and fields that changed.

Deleting records

To delete:

  1. Query the object.
  2. Mark it for deletion.
  3. Commit.
python 
user = db.query(User).filter(User.id == 1).first()
db.delete(user)
db.commit()   # ORM issues DELETE

Before commit, the object is in the deleted state.

Merging Detached Objects

Sometimes you have an object that is not attached to a session anymore (detached). For example:

To apply changes from a detached object, you should merge it into a new session.

Example:

python 
# Suppose we got this user object from somewhere, but it is detached.
user = User(id=1, name="New Name")
db = SessionLocal()
managed_user = db.merge(user)   # managed_user is the persistent version
db.commit()

What merge() does conceptually:

Do not modify detached objects and expect changes to be saved automatically.
You must attach them to a session again, for example with session.merge().

Session Scope: How Long Should a Session Live?

A crucial design choice is the scope of a session: how long you keep a single session open.

Typical scopes:

ScopeDescriptionCommon use case
Per operationCreate and close inside each functionSimple scripts, batch tools
Per requestOne session for each HTTP requestWeb APIs and web applications
Global / long livedOne session reused across many requestsUsually a bad idea in web backends

Why “per request“ is recommended for web backends

For a web API, a common pattern is:

  1. At the start of a request, create a session.
  2. Use it to handle that request only.
  3. Commit or roll back and close it at the end of the request.

This isolates database work per request and makes it:

Example pattern in FastAPI style code:

python 
# Usually in some db.py
from sqlalchemy.orm import sessionmaker
from .database import engine
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

Then in endpoints:

python 
from fastapi import Depends
@app.get("/users/{user_id}")
def read_user(user_id: int, db: Session = Depends(get_db)):
    user = db.query(User).get(user_id)
    return user

Here:

Why global sessions are dangerous

If you keep one global session for the whole application:

In multi threaded servers, one global session can also cause thread safety problems if it is not configured correctly.

Session Configuration: autoflush and autocommit

In SQLAlchemy and similar ORMs, sessions have some important configuration options. Two common ones:

autoflush

Example:

python 
SessionLocal = sessionmaker(bind=engine, autoflush=True, autocommit=False)
db = SessionLocal()
user = User(email="auto@example.com")
db.add(user)
# With autoflush=True, this query triggers a flush first
count = db.query(User).count()

If autoflush=False, you might have to call session.flush() yourself when needed.

autocommit

For web backends, use sessions with autocommit=False.
Control commit() and rollback() explicitly.

Handling Sessions in Errors and Exceptions

It is very important to handle exceptions correctly with sessions.

Pattern:

python 
db = SessionLocal()
try:
    # perform DB work
    db.commit()
except:
    db.rollback()
    raise
finally:
    db.close()

Why this matters:

Example of a bug:

python 
db = SessionLocal()
# First transaction
user = User(email="bad@example.com")
db.add(user)
# This will raise an exception, for example due to a DB constraint
db.commit()
# Now the transaction is in a failed state.
# If you keep using `db` without rollback, you will get confusing errors.

Correct approach:

python 
db = SessionLocal()
try:
    user = User(email="bad@example.com")
    db.add(user)
    db.commit()
except:
    db.rollback()
    raise
finally:
    db.close()

In a framework like FastAPI, you often handle this in:

Example: Using Sessions in a Short Script

Here is a complete example of using sessions in a small standalone script with SQLAlchemy style code.

Assume you have a User model and engine.

python 
from sqlalchemy.orm import sessionmaker
from models import User
from database import engine
SessionLocal = sessionmaker(bind=engine, autoflush=False, autocommit=False)
def create_user(email: str, name: str):
    db = SessionLocal()
    try:
        user = User(email=email, name=name)
        db.add(user)
        db.commit()
        db.refresh(user)
        return user
    except:
        db.rollback()
        raise
    finally:
        db.close()
def rename_user(user_id: int, new_name: str):
    db = SessionLocal()
    try:
        user = db.query(User).get(user_id)
        if not user:
            return None
        user.name = new_name
        db.commit()
        return user
    except:
        db.rollback()
        raise
    finally:
        db.close()
if __name__ == "__main__":
    u = create_user("demo@example.com", "Demo")
    print("Created:", u.id, u.name)
    u2 = rename_user(u.id, "New Demo")
    print("Renamed:", u2.id, u2.name)

Patterns shown:

Common Pitfalls with Sessions

Here are some issues you are likely to run into as a beginner and how to think about them.

“My data did not save”

Possible reasons:

Check:

“I see stale data”

Possible reasons:

Fix:

“Weird thread errors” in web apps

Possible reason:

Fix:

Summary

You have seen that a database session in an ORM is:

Key ideas to remember:

  • Use one session per web request or per unit of work.
  • Always commit or rollback, then close the session.
  • Sessions manage transactions, do not rely on autocommit.
  • Detached objects do not save themselves, you must attach or merge them.

With a solid understanding of sessions, working with models, queries, and transactions in your backend becomes much more predictable and less error prone.

Views: 5

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3701 / 24h
Impressum / Imprint
Datenschutz / Data Policy