If you use Flask-SQLAlchemy 3.x and still write Post.query.filter(…).all(), you are using the old SQLAlchemy 1.x query interface. The current standard is db.select() combined with db.session.execute(), plus db.session.get() to fetch a single record by primary key. This post converts one sample query to the new style, so your code matches the SQLAlchemy 2.0 docs and is easier to extend later.

The problem

The old style reads compactly, but it belongs to the SQLAlchemy 1.x era. You query through each model’s query attribute, with a separate access pattern per model, and as soon as you need joins, grouping, or aggregate functions, the old syntax quickly turns patchy. Worse, Post.query.get(id) is the legacy way to fetch a record: it goes through an intermediate Query layer instead of calling the session directly, so when you upgrade or read current docs, every example looks different from your code. The bigger the project, the harder two parallel query styles are to maintain: newcomers must learn both the old and the new way just to read one file.

from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    views = db.Column(db.Integer, default=0)
popular = Post.query.filter(Post.views >= 30).order_by(Post.views.desc()).all()
one = Post.query.get(1)

The solution

SQLAlchemy 2.0 style writes a query as an explicit statement: db.select(Post) says what to fetch, .where() filters, .order_by() sorts, then db.session.execute() runs it. The result is rows, so call .scalars().all() to get a plain list of Post objects with no intermediate wrapper. To fetch one record by id, use db.session.get(Post, 1): shorter, clearer, and the session’s standard API. To count all posts, use db.select(db.func.count()).select_from(Post) followed by .scalar_one(), instead of len() over a fully loaded list.

popular = db.session.execute(
    db.select(Post)
    .where(Post.views >= 30)
    .order_by(Post.views.desc())
).scalars().all()
one = db.session.get(Post, 1)
total = db.session.execute(
    db.select(db.func.count()).select_from(Post)
).scalar_one()

There is another benefit: because the query is a standalone select object, you can build it piece by piece — start with stmt = db.select(Post), then add .where() only when the user actually passes a filter parameter.

Applying it to your project

Do not rewrite the whole codebase in one go. Set one rule: new code uses db.select(), old code migrates gradually whenever you touch that file. Start with the three most common spots: list-fetching functions with filters and sorting, places using Model.query.get(), and places counting with len(query.all()). Each time you convert, rerun that route’s tests — the returned results must be identical to before, because this is a syntax change, not a logic change. Once the whole team is used to it, query code reads consistently from model to model, and new SQLAlchemy docs apply directly wherever you read them.

Related posts

  • Flask: use the Application Factory (create_app) so your app is easy to test and extend from day one
  • Flask: fix N+1 queries in Flask-SQLAlchemy with joinedload and selectinload
  • Flask: paginate query results with db.paginate(), no manual offset/limit

FAQ

Does Model.query still work on Flask-SQLAlchemy 3.x?

Yes, but it is a backwards-compatibility interface from the SQLAlchemy 1.x era. Current docs write every example with select(), so new code should use the new style to avoid learning two syntax systems.

What is .scalars() for — can I drop it?

You should not drop it. execute() returns result rows, while .scalars() unwraps the object in the first column of each row. Without it you get Row objects instead of Post objects, and accessing post.title will fail.

How is session.get() different from filter_by(id=…).first()?

session.get() looks up directly by primary key and can reuse an object already present in the session, so it is usually shorter and faster. filter_by() fits conditions that are not the primary key, such as filtering by slug or email.

💬 Have you tried this in your own project? If you hit an error or know a cleaner way, leave a comment below — I read them all and will fold good ideas into the post.

Sources