Flask-SQLAlchemy Patterns — Core Concepts

How Flask-SQLAlchemy manages sessions

The most important thing Flask-SQLAlchemy does is manage database sessions automatically. A session tracks changes to your objects and writes them to the database when you commit.

Flask-SQLAlchemy creates a scoped session tied to the request lifecycle. When a request starts, you get a fresh session. When the request ends, the session is automatically removed — committing pending changes or rolling back on errors.

This means you rarely create sessions manually. You use db.session and trust the framework to handle lifecycle. But understanding what happens underneath prevents a whole category of bugs.

The N+1 query problem

The most common performance killer in ORM code. Consider this:

users = User.query.all()        # 1 query: SELECT * FROM users
for user in users:
    print(user.posts)           # N queries: SELECT * FROM posts WHERE user_id = ?

For 100 users, that’s 101 database queries. The fix is eager loading — telling SQLAlchemy to fetch related data upfront:

users = User.query.options(joinedload(User.posts)).all()  # 1 query with JOIN

Three loading strategies exist:

• joinedload — Uses a SQL JOIN, fetching everything in one query. Best for one-to-one or small one-to-many relationships.
• subqueryload — Runs a second query with a subquery. Better for large collections where JOINs would create too many duplicated rows.
• selectinload — Runs a second query with WHERE id IN (...). Usually the best default for collections.

Query composition

Instead of writing complex queries inline, compose them from reusable pieces:

class User(db.Model):
    # ...
    
    @classmethod
    def active(cls):
        return cls.query.filter_by(is_active=True)
    
    @classmethod
    def created_after(cls, date):
        return cls.query.filter(cls.created_at > date)

These methods return query objects that can be chained further. The database only executes when you call .all(), .first(), or iterate.

Session patterns that prevent bugs

Always use db.session.add() explicitly for new objects

user = User(name='Alice')
db.session.add(user)
db.session.commit()

Relying on cascade adds from relationships works but makes code harder to follow. Explicit adds make the intent clear.

Handle rollback on errors

try:
    db.session.add(user)
    db.session.commit()
except IntegrityError:
    db.session.rollback()
    raise

If an error occurs during commit and you don’t roll back, the session enters a broken state. All subsequent queries in that request will fail.

Use db.session.get() for primary key lookups

# Good: uses identity map, skips DB if already loaded
user = db.session.get(User, 42)

# Slower: always generates a query
user = User.query.filter_by(id=42).first()

db.session.get() checks the session’s identity map first. If the object was already loaded in this request, it returns it from memory.

Relationship configuration

Relationships define how models connect. The key decisions are loading strategy and cascade behavior:

class User(db.Model):
    posts = db.relationship('Post', 
        back_populates='author',
        lazy='select',           # Default: load on access
        cascade='all, delete-orphan'  # Delete posts when user is deleted
    )

The lazy parameter sets the default loading strategy. Override it per-query with .options() rather than changing the default, because different views need different strategies.

Common misconception

“The ORM is slow, raw SQL is always faster.” The ORM adds overhead per query (typically microseconds), but the real performance difference comes from which queries run. An ORM with proper eager loading runs 2 queries where naive code runs 101. The ORM’s overhead per query is irrelevant compared to eliminating 99 unnecessary queries.

When you do need raw SQL for truly complex queries, SQLAlchemy supports it: db.session.execute(text("SELECT ...")).

Pagination

Flask-SQLAlchemy provides built-in pagination:

page = request.args.get('page', 1, type=int)
users = User.query.paginate(page=page, per_page=20)

The paginate object contains .items (the results), .pages (total page count), .has_next, and .has_prev. This generates proper LIMIT/OFFSET SQL.

For large tables, offset-based pagination gets slow. Keyset pagination (filtering by the last seen ID) performs better but requires ordered results.

One thing to remember: Most Flask-SQLAlchemy performance problems come from invisible queries — the N+1 problem, unnecessary lazy loads, and missing indexes. Use Flask-SQLAlchemy’s query recording (SQLALCHEMY_RECORD_QUERIES) during development to see every query each request generates.