DEV Community

Cover image for SQLAlchemy v2 will break with the past... and most likely, your web app
Juan-Pablo Scaletti
Juan-Pablo Scaletti

Posted on

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()
Enter fullscreen mode Exit fullscreen mode

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()
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)))
Enter fullscreen mode Exit fullscreen mode

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()
Enter fullscreen mode Exit fullscreen mode

Join and filter by a related table:

# 1.x style (before)
session.query(User).join(Address).\
    filter(Address.email == 'e@sa.us').\
    all()

# 2.x style (now)
session.execute(
    select(User).join(Address).
    where(Address.email == 'e@sa.us')
).scalars().all()
Enter fullscreen mode Exit fullscreen mode

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/ 😉

Oldest comments (1)

Collapse
 
defman profile image
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.