Moving to SQLAlchemy 2.0: Replacing session.query() with select()

The Problem: The Legacy Query API Debt

For years, SQLAlchemy developers relied on session.query(User) as the primary way to fetch data. While intuitive, this API blurred the line between the ORM (Object Relational Mapper) and the underlying SQL Core. This led to inconsistent behaviors when mixing raw SQL expressions with ORM models and made static type checking nearly impossible.

SQLAlchemy 2.0 solves this by unifying query construction. The legacy Query object is deprecated in favor of the select() function. The core takeaway is a shift in responsibility: select() now handles the construction of the query, while session.execute() handles the execution.

The New Execution Pattern

In the 1.x style, the query object was both the definition and the executor. In 2.0, these are decoupled. A select() statement returns a Select object, which is essentially a blueprint of the SQL you want to run.

To get your data back, you pass that blueprint to the session. Because session.execute() is designed to work for both raw tables and ORM classes, it returns a Result object—a collection of rows. Since ORM queries usually want the objects themselves rather than a row tuple containing the object, you use .scalars() to "unwrap" the results.

Comparison: 1.x vs 2.0 Syntax

Feature	Legacy (1.x)	Modern (2.0)
Basic Fetch	session.query(User).all()	session.execute(select(User)).scalars().all()
Filtering	.filter(User.name == 'Alice')	.where(User.name == 'Alice')
Single Result	.first()	session.execute(stmt).scalars().first()
Counting	.count()	select(func.count()).select_from(User)

Worked Example: Migrating a Filtered Query

Assume you have a User model using the 2.0 Mapped[] type annotations. You need to find a user by their email and return a single instance.

from sqlalchemy import select, create_engine
from sqlalchemy.orm import Session, Mapped, mapped_column
from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(unique=True)

# Setup (Run this in your application context)
engine = create_engine("sqlite:///")
Base.metadata.create_all(engine)

with Session(engine) as session:
    # 1. Construct the statement (The Blueprint)
    stmt = select(User).where(User.email == "[contact removed]")
    
    # 2. Execute and unwrap (The Execution)
    # .scalars() converts Row(User(...),) -> User(...)
    user = session.execute(stmt).scalars().first()
    
    if user:
        print(f"Found user: {user.email}")

Verification and Risks

To verify this is working as intended, enable SQL logging by setting echo=True in create_engine(). You should see a standard SELECT ... FROM users WHERE ... statement.

Risk: If you are migrating a large codebase, be aware that session.execute() does not trigger an implicit autoflush by default in 2.0. If you have pending changes in the session that the query needs to see, you must call session.flush() manually before executing the select, or pass execution_options={'autoflush': True} to the execute method.

Trade-offs and Limitations

The primary trade-off is verbosity. Writing session.execute(select(User)).scalars().all() is undeniably longer than session.query(User).all(). However, this verbosity buys you two critical engineering advantages:

• Static Analysis: When combined with Mapped[] and a type-checker like Mypy, the scalars() return type is known. You no longer get Any or generic Query types that hide the actual model being returned.
• Composability: Because select() is a standalone function, you can build complex queries across different modules by passing the Select object around and adding .where() or .join() clauses before finally executing it.

One limitation is the removal of the 'select' lazy loading strategy. If you relied on the old default lazy loading, you should now explicitly use selectinload or joinedload to avoid the N+1 query problem, as the 2.0 style is more strict about how relationships are emitted.

Actionable Closing

If you are upgrading from 1.4 to 2.0, don't try to rewrite every query at once. Start by enabling DeprecationWarning in your test suite to identify where session.query() is still being used. Prioritize migrating the most complex queries first, as the select() API makes debugging CTEs and window functions significantly easier than the legacy ORM wrapper.