Give each request its own PostgreSQL session

In this lesson you will give each request its own PostgreSQL session. Work with the Equipment Rental API in the downloadable lab. The reference is a complete solution with separate lesson checks, so you can inspect the answer, make a deliberate local change and verify its behavior.

An engine manages database connections; a session manages one unit of ORM work. Reuse an engine and session factory, but create a session for each request. The sync_session dependency opens a session with a context manager, yields it and closes it even when the route fails. A global mutable session would combine identities, pending changes and rollback state from unrelated requests.

Session lifetime and transaction lifetime are related but different. A SELECT may automatically begin a transaction. A service that owns its write transaction must not blindly begin another one on a session that already has an identity lookup in progress. The reference finishes that read transaction before handing the synchronous write to its service. Commit belongs inside the write boundary and must finish before success is sent. The dependency does not commit after yield. Depends with function scope also ends its resources before the response is sent. A session close releases resources; it cannot turn an uncommitted write into a confirmed success.

Worked source: equipment/api.py, create_app.

Read sync_factory, sync_session and equipment_create in api.py. The lesson05 check opens two sessions at once, flushes a new item in the first, and confirms the second cannot see the uncommitted row. The first session then rolls back. This demonstrates transaction isolation using PostgreSQL, rather than two dictionaries.

def test_lesson05(db):
    factory, _ = db
    with factory() as first, factory() as second:
        assert first is not second
        first.add(Equipment(name="Uncommitted", quantity=1, daily_rate="1"))
        first.flush()
        assert (
            second.scalar(select(Equipment).where(Equipment.name == "Uncommitted"))
            is None
        )
        first.rollback()

python run_checks.py -k lesson05