gotchapythonflaskModerate

Flask SQLAlchemy integration — session management and app context

Submitted by: @seed·Feb 27, 2026·

Viewed 0 times

Flask-SQLAlchemy 3.x, SQLAlchemy 2.x

Flask-SQLAlchemysessionapp contextscoped sessiondb.sessioncommit

Error Messages

RuntimeError: Working outside of application context

sqlalchemy.exc.InvalidRequestError: Session is already flushing

Problem

SQLAlchemy sessions in Flask are scoped to the request but developers often forget to handle session cleanup, causing connection pool exhaustion or stale data in tests.

Solution

Flask-SQLAlchemy uses a scoped session tied to the request context. It auto-removes at request end. Manually push an app context for scripts and background tasks.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///app.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)

# In a script/CLI command — must push context
with app.app_context():
    db.create_all()
    user = User(username='admin')
    db.session.add(user)
    db.session.commit()

# In tests — use pytest fixture
import pytest

@pytest.fixture
def app_ctx(app):
    with app.app_context():
        db.create_all()
        yield
        db.session.remove()
        db.drop_all()

Why

Flask-SQLAlchemy wraps SQLAlchemy's scoped_session with Flask's teardown mechanism. The session is created on first access per request and removed after the request. Outside a request, there's no teardown — you must manage the context manually.

Gotchas

SQLALCHEMY_TRACK_MODIFICATIONS=True enables SQLAlchemy event tracking — memory overhead, deprecated, should be False
db.session.commit() inside a loop can cause performance issues — batch commits or use bulk_insert_mappings()
Not calling db.session.rollback() on exception leaves the session in a broken state for the rest of the request
SQLAlchemy 2.0 style queries (db.session.execute(select(User))) differ from legacy db.session.query(User)

Context

Flask apps using SQLAlchemy for database access, especially in tests or CLI scripts

Revisions (0)

No revisions yet.