Active Record vs Unit of Work
If you've spent years building PHP 8.3 applications on Laravel 12, Eloquent feels like second nature. You pull a record with User::find(1), modify standard properties, and hit $user->save(). The model represents both the row in PostgreSQL or MySQL and the logic required to query and persist itself. That's classic Active Record.
SQLAlchemy 2.0 doesn't work like that. It uses the Data Mapper pattern driven by a Unit of Work. Your Python class is a pure data structure; it has no built-in knowledge of how to persist itself to PostgreSQL. That responsibility belongs entirely to the Session. If you try calling a hypothetical user.save() method in SQLAlchemy, you'll hit an AttributeError instantly.
Understanding the Session Lifecycle
In Eloquent, every database interaction is immediate unless wrapped in a manual transaction via DB::transaction(). When you run User::create([...]), an INSERT query runs immediately. In SQLAlchemy 2.0, adding an object to a session merely registers it with the Unit of Work. The actual SQL execution delays until a flush or commit occurs.
An object in SQLAlchemy moves through four distinct states during its lifecycle:
- Transient: The object exists in Python memory but isn't tied to a session or database primary key yet.
- Pending: You've passed the object to
session.add(), but SQLAlchemy hasn't issued anINSERTquery to the database yet. - Persistent: The object is linked to an active session and has a corresponding row in the database.
- Detached: The underlying session closed or expired, but the Python object still exists in memory. Accessing unloaded attributes on a detached object raises a runtime error.
Here is how a standard write operation looks when moving from PHP 8.3 Eloquent to SQLAlchemy 2.0 in Python 3.12:
# PHP / Laravel 12 Eloquent equivalent:
# $user = User::findOrFail(42);
# $user->email = 'dev@example.com';
# $user->save();
from sqlalchemy import select
from sqlalchemy.orm import Session
def update_user_email(engine, user_id: int, new_email: str) -> None:
with Session(engine) as session:
# User is Persistent after execution
stmt = select(User).where(User.id == user_id)
user = session.scalars(stmt).one()
# Modifying attribute marks user as 'dirty' in the session
user.email = new_email
# Unit of Work automatically generates UPDATE query on commit
session.commit()Notice that we never called an update method on user. The session tracks mutations on persistent objects. When session.commit() runs, SQLAlchemy inspects its internal state, generates the precise SQL statement (UPDATE users SET email = :email WHERE id = :id), and executes it inside a single transaction. If an exception occurs, the transaction rolls back cleanly.
Relationship Loading: Goodbye Implicit Lazy Queries
In Eloquent, accessing an unloaded relationship triggers an implicit database query. If you load a user model and reference $user->posts in Blade or a resource transformer, Eloquent silently executes SELECT * FROM posts WHERE user_id = ? behind the scenes. While convenient during prototyping, this behavior frequently leads to hidden N+1 query bugs in production API endpoints.
SQLAlchemy 2.0 forces you to think about relationship loading upfront, especially if you work with async database engines like asyncpg. Calling an unloaded relationship on an async SQLAlchemy session won't just run a slow query—it raises an AioSqlalchemyError because synchronous I/O cannot occur inside an event loop.
To write clean, predictable queries with sqlalchemy for eloquent developers, you need to select explicit loading options:
- selectinload: Emits a separate
SELECT ... WHERE id IN (...)query. This is the exact equivalent of Laravel's default eager loading withUser::with('posts'). Use this for one-to-many and many-to-many relationships. - joinedload: Generates a SQL
LEFT OUTER JOINto fetch parent and child records in a single query result set. Best for one-to-one or many-to-one relationships. - contains_eager: Tells SQLAlchemy that you manually wrote a
JOINor filtered child tables in theselect()construct, so it should map those already-fetched rows directly into the model attributes.
Here is how eager loading looks in practice using SQLAlchemy 2.0's select() constructs:
from sqlalchemy import select
from sqlalchemy.orm import Session, selectinload, joinedload
def get_user_dashboard_data(session: Session, user_id: int) -> User:
# Fetch user, eager-load profile via JOIN and posts via second SELECT
stmt = (
select(User)
.where(User.id == user_id)
.options(
joinedload(User.profile),
selectinload(User.posts)
)
)
# scalars() unpacks the single-element tuple returned by select()
user = session.scalars(stmt).one()
return userIn Eloquent, you'd write this as User::with(['profile', 'posts'])->findOrFail($user_id). In SQLAlchemy, using selectinload for collections prevents duplicate parent rows from bloating memory, reducing query response times from 120ms down to around 15ms on large datasets.
Gotchas That Catch Eloquent Developers Off Guard
1. The DetachedInstanceError
This is the most common bug PHP developers hit in Python web frameworks. When a SQLAlchemy session context closes (like at the end of an HTTP request), objects fetched during that session become detached. If your JSON serializer or template engine tries to read an unloaded relationship after session closure, SQLAlchemy throws an error:
sqlalchemy.orm.exc.DetachedInstanceError: Parent instance <User at 0x7f8...> is not bound to a Session; lazy load operation of attribute 'posts' cannot proceedTo avoid this, either explicitly eager-load every required relationship during the initial query or configure your session context manager to keep sessions alive across the request lifecycle.
2. Session Flush vs Commit
Eloquent doesn't distinguish between flushing and committing because every active record write commits instantly unless bounded by DB::beginTransaction(). In SQLAlchemy, session.flush() sends pending changes to the database buffer so primary keys generate, but it leaves the database transaction open. session.commit() flushes pending changes and finalizes the transaction.
If you need to fetch an auto-incrementing integer primary key on a newly instantiated model before saving related records, call session.flush() instead of committing early.
3. The Identity Map
If you execute User::find(1) twice in Laravel within a single request without caching, Eloquent queries the database twice and creates two separate model instances in PHP memory. Modifying one won't update the other.
SQLAlchemy maintains an Identity Map within each session. Querying primary key 1 multiple times within the same session returns the exact same Python object instance in memory. If you updated user.name earlier in the session, the subsequent query returns that modified object without re-fetching stale data from SQL.
Switching from Eloquent to SQLAlchemy requires shifting focus from model instances to unit-of-work scope. Once you treat sessions as explicit transaction boundaries and specify relationship loading strategies upfront, SQLAlchemy gives you total control over query counts and memory usage.
