SQLAlchemy v2 will break with the past... and most likely, your web app

Most mature Python Libraries care a lot about backward compatibility, so it was a surprise to discover that the upcoming version of SQLAlchemy is making huge changes in its API, breaking with how web developers have been using it for years.

By far, the most, dramatic change is that session.query() is beign deprecated. This means the following code no longer works:

user = session.query(User).filter_by(username='peter').first()

Instead, you now have to use select to build a query and execute it in the session.

user = session.execute(
    select(User).filter_by(username='peter').limit(1)
).scalars().first()

Other query patterns have changed as well:

Get an object by primary key:

# 1.x style (before)
session.query(User).get(42)

# 2.x style (now)
session.get(User, 42)

Counting the number of objects:

# 1.x style (before)
session.query(User).count()

# 2.x style (now)
session.scalar(select(func.count()).select_from(User))
# or
session.scalar(select(func.count(User.id)))

Join a related table:

# 1.x style (before)
session.query(User).options(
    joinedload(User.addresses)
).all()

# 2.x style (now)
session.execute(
    select(User).
    options(joinedload(User.addresses))
).unique().all()

Join and filter by a related table:

# 1.x style (before)
session.query(User).join(Address).\
    filter(Address.email == '[e-mail]').\
    all()

# 2.x style (now)
session.execute(
    select(User).join(Address).
    where(Address.email == '[e-mail]')
).scalars().all()

The new versions are much more verbose, aren't they? Why the change?
In the words of SQLAlchemy devs:

The fact that SQLAlchemy has both a select() construct as well as a separate Query object that features an extremely similar but fundamentally incompatible interface is likely the greatest inconsistency in SQLAlchemy.

So now they are getting rid of it, and breaking most "wrapper" libraries, like Flask-SQLAlchemy, that will have to be rewritten entirely.

Before migrating to Peewee or PonyORM in anger, consider trying this lightweight wrapper I wrote to for the new syntax: https://sqla-wrapper.scaletti.dev/ 😉

Comments

[Sergey Kislyakov]

AFAIK counting the number of objects was explicitly removed so the programmer would see how ineffective it is (e.g. you have to do select count(*) from (%subquery%)).

Of course doing a major release would break every single library if it's not updated. That's the idea of the major realeses in SemVer.

I actually like the new style of doing queries, though.