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

Juan-Pablo Scaletti — Tue, 09 Nov 2021 22:43:03 +0000

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@sa.us').\
    all()

# 2.x style (now)
session.execute(
    select(User).join(Address).
    where(Address.email == 'e@sa.us')
).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/ 😉

DEV Community: Juan-Pablo Scaletti

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