The Query Interface
Every model gets a .query attribute that returns a Query object you chain methods onto: User.query.filter_by(username='alice').first() finds a single row by exact keyword match, while User.query.filter(User.age > 18).all() uses the more flexible expression syntax that supports comparisons, and_(), or_(), and like() for pattern matching. .first() returns None if nothing matches, .one() raises an exception unless exactly one row matches, and .get(id) (or the newer db.session.get(Model, id)) does a fast primary-key lookup that can be served from the identity map without hitting the database if the object is already loaded in the session.
Cricket analogy: .filter_by(username='alice') is like searching a scorecard for the exact player 'Virat Kohli' by name, while .filter(User.age > 18) is like the more flexible query 'find all players with a strike rate above 150' using a comparison.
# Exact match
user = User.query.filter_by(username='alice').first()
# Expression-based filtering
adults = User.query.filter(User.age >= 18).order_by(User.username).all()
# Primary key lookup
user = db.session.get(User, 42)
# Joins with eager loading to avoid N+1 queries
from sqlalchemy.orm import joinedload
posts = Post.query.options(joinedload(Post.author)).all()
# Pagination
page = User.query.order_by(User.id).paginate(page=1, per_page=20)Avoiding the N+1 Query Problem
By default, relationships are lazily loaded, meaning accessing post.author after fetching a list of posts triggers a separate query per post — fine for one row, disastrous for a page listing 100 posts, since that's 101 total queries. joinedload() fixes this by folding the related rows into the original query via a SQL JOIN, while selectinload() issues one additional bulk query using IN (...) to fetch all related rows at once — generally preferred over joinedload when the relationship returns many rows, since it avoids the exponential row duplication a JOIN causes on multiple relationships.
Cricket analogy: The N+1 problem is like a scorer walking to the pavilion to check each of the eleven batters' individual season averages one at a time, instead of requesting the entire team's stats sheet in one trip.
You can spot N+1 problems in development by enabling SQLALCHEMY_ECHO = True (or a tool like Flask-DebugToolbar) to log every SQL statement — seeing dozens of near-identical SELECTs for a single page load is the telltale sign that a relationship needs eager loading.
Aggregations and Raw Expressions
For counting, summing, and grouping, Flask-SQLAlchemy exposes SQLAlchemy's function namespace via db.func, so db.session.query(db.func.count(User.id)).scalar() returns a single count, and db.session.query(Post.user_id, db.func.count(Post.id)).group_by(Post.user_id).all() gives per-user post counts directly from the database rather than pulling every row into Python and counting there. When a query's logic genuinely can't be expressed cleanly through the ORM's chainable API, db.session.execute(db.text("SELECT ...")) drops down to raw SQL while still going through the managed session and connection pool.
Cricket analogy: Using db.func.count() grouped by team is like asking the scoring system for 'total wickets per bowler this series' computed server-side, rather than downloading every ball-by-ball delivery and tallying manually.
When dropping to raw SQL with db.text(), always use bound parameters (e.g., db.text("SELECT * FROM users WHERE id = :id").bindparams(id=user_id)) rather than Python string formatting — interpolating user input directly into SQL strings opens the door to SQL injection.
Model.queryreturns a chainable Query object;.filter_by()handles exact matches,.filter()handles expressions..first()returns None on no match,.one()raises unless exactly one row matches,.get()/session.get()is a fast primary-key lookup.- Lazy-loaded relationships trigger the N+1 query problem when iterating over a collection.
joinedload()uses a SQL JOIN;selectinload()uses a second bulk query — prefer selectinload for large related collections.SQLALCHEMY_ECHO = Truehelps surface N+1 problems during development by logging every SQL statement.db.funcexposes SQL aggregate functions like COUNT, SUM, and GROUP BY without pulling all rows into Python.- Raw SQL via
db.text()should always use bound parameters to avoid SQL injection.
Practice what you learned
1. What is the key difference between `.filter_by()` and `.filter()` on a Flask-SQLAlchemy query?
2. What causes the N+1 query problem when working with relationships?
3. When is `selectinload()` generally preferred over `joinedload()`?
4. Why should bound parameters be used when executing raw SQL with db.text()?
5. What does db.func provide in Flask-SQLAlchemy?
Was this page helpful?
You May Also Like
Flask-SQLAlchemy Basics
Learn how Flask-SQLAlchemy wires the SQLAlchemy ORM into a Flask app, from configuration to sessions.
Defining Models in Flask
Learn how to declare Flask-SQLAlchemy models with columns, types, constraints, and relationships.
Database Migrations with Flask-Migrate
Manage evolving database schemas safely using Flask-Migrate's Alembic-backed migration workflow.
Related Reading
Related Study Notes in Web Development
Browse all study notesWebSockets Study Notes
Web Development · 30 topics
Web DevelopmentWebAssembly Study Notes
WebAssembly · 30 topics
Web DevelopmentgRPC Study Notes
Protocol Buffers · 30 topics
Web DevelopmentSpring Boot Study Notes
Java · 30 topics
Web DevelopmentDjango Study Notes
Python · 30 topics
Web DevelopmentNext.js Study Notes
JavaScript · 30 topics