flask-sqlalchemy
Flask-SQLAlchemy wires SQLAlchemy 2 into Flask by creating engines from app configuration, providing a declarative model base, and scoping a session to the current Flask application context. It adds convenient 404 and pagination helpers while leaving queries, relationships, transactions, and database behavior to SQLAlchemy itself. It is integration glue, not a separate ORM, and its documentation expects you to learn SQLAlchemy from SQLAlchemy's own tutorial.
This remains the sensible adapter when both Flask and SQLAlchemy are settled choices. Skip it for non-Flask code, and budget separately for SQLAlchemy knowledge, a database driver, and real migrations.
Use it if
- You already chose Flask and SQLAlchemy and want request-scoped session cleanup with little glue code
- You use an application factory and want one extension object initialized across multiple Flask apps
- Your views benefit from built-in get-or-404, one-or-404, and pagination helpers
- You need named binds for a small number of databases configured through Flask
- You are not using Flask: plain SQLAlchemy avoids application-context coupling and gives the same ORM and SQL features
- You expect this package to teach or simplify SQLAlchemy queries; its quickstart explicitly sends readers to the SQLAlchemy tutorial and uses the SQLAlchemy 2 select API
- You want automatic schema migrations: `create_all()` only creates missing tables and the docs direct changed schemas to Alembic, Flask-Alembic, or Flask-Migrate
- You depend on old `Model.query` examples: the current docs call that interface legacy and prefer `db.session.execute(db.select(...))`
- You need active feature development from the core Pallets team: the README identifies this as a community-maintained Pallets-Eco extension and asks for maintainers
Setup reality
Install `Flask-SQLAlchemy`, but also install the database driver required by your URL, such as psycopg for PostgreSQL or PyMySQL for MySQL. Version 3.1.1 requires Flask 2.2.5 or newer and SQLAlchemy 2.0.16 or newer. Create one `SQLAlchemy` object, preferably with a SQLAlchemy 2 `DeclarativeBase`, configure at least `SQLALCHEMY_DATABASE_URI` or `SQLALCHEMY_BINDS`, then call `db.init_app(app)`. Configuration is read when initialization creates the engines, so changing Flask config later does not rebuild them. Relative SQLite paths resolve under Flask's instance folder, a surprise if you look beside the source file. Outside a request or CLI command, every access to `db.session`, `db.engine`, and helpers needs `app.app_context()`. Import every model before `create_all()` or its table is invisible, and do not mistake `create_all()` for migrations because it never alters existing columns. Writes require an explicit commit and error paths should roll back. The extension removes the session after each request, but that does not commit it. `Model.query` still appears in older tutorials but is legacy. Multiple binds create separate metadata collections and do not give you atomic cross-database transactions. Finally, turn on `SQLALCHEMY_TRACK_MODIFICATIONS` only if you need its signals because the config reference calls out significant per-session overhead.
Patterns
Initialize the extension in an app factoryinitialize-extension
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import DeclarativeBase
class Base(DeclarativeBase):
pass
db = SQLAlchemy(model_class=Base)
def create_app():
app = Flask(__name__, instance_relative_config=True)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///app.sqlite'
db.init_app(app)
return appSet engine configuration before `init_app`; the extension reads it while creating engines and does not track later config changes.
Define a typed SQLAlchemy 2 modeldefine-model
from sqlalchemy import String
from sqlalchemy.orm import Mapped, mapped_column
class User(db.Model):
id: Mapped[int] = mapped_column(primary_key=True)
username: Mapped[str] = mapped_column(String(80), unique=True, index=True)
email: Mapped[str] = mapped_column(String(255))The generated table name is `user`; define `__tablename__` explicitly if a stable database naming convention matters.
Create tables during local setupcreate-tables
app = create_app()
with app.app_context():
from myapp import models
db.create_all()Import models first, and use this only for fresh local databases; `create_all` does not alter an existing schema.
Query models with the SQLAlchemy 2 APIselect-models
stmt = db.select(User).where(User.email.endswith('@example.com')).order_by(User.username)
users = db.session.execute(stmt).scalars().all()Prefer `db.session.execute(db.select(...))`; the current docs label `Model.query` and `session.query` as legacy.
Load a row or return HTTP 404get-or-404
from flask import render_template
@app.get('/users/<int:user_id>')
def user_detail(user_id):
user = db.get_or_404(User, user_id, description='User not found')
return render_template('user.html', user=user)This helper belongs in an HTTP view; service-layer code should usually return or raise a domain-specific result instead.
Paginate a select statementpaginate-results
from flask import request
stmt = db.select(User).order_by(User.username)
page = db.paginate(
stmt,
page=request.args.get('page', 1, type=int),
per_page=20,
max_per_page=100,
)
return {'items': [u.username for u in page.items], 'pages': page.pages}Pass an explicit `max_per_page` when request data can influence page size, or one request can ask the database for an excessive result set.
Insert a row and handle commit failurecreate-and-commit
from sqlalchemy.exc import IntegrityError
user = User(username='ada', email='ada@example.com')
db.session.add(user)
try:
db.session.commit()
except IntegrityError:
db.session.rollback()
raiseThe request teardown removes the scoped session but does not commit it; after a failed flush or commit, roll back before reusing the session.
Update and delete ORM objectsupdate-and-delete
user = db.get_or_404(User, user_id)
user.email = 'new@example.com'
db.session.commit()
db.session.delete(user)
db.session.commit()Each commit is its own transaction here; group changes in one transaction when they must succeed or fail together.
Eager-load a relationshipload-relationship
from sqlalchemy.orm import selectinload
stmt = db.select(User).options(selectinload(User.posts)).order_by(User.id)
users = db.session.execute(stmt).scalars().all()Eager loading prevents an N+1 query loop and avoids trying to lazy-load after the app-scoped session has been removed.
Set production engine optionsconfigure-pool
app.config.update(
SQLALCHEMY_DATABASE_URI=os.environ['DATABASE_URL'],
SQLALCHEMY_ENGINE_OPTIONS={
'pool_pre_ping': True,
'pool_recycle': 300,
},
)Install the dialect driver named by the URL separately; Flask-SQLAlchemy does not supply PostgreSQL or MySQL drivers.
Route a model to a named database bindconfigure-binds
app.config['SQLALCHEMY_BINDS'] = {
'auth': 'sqlite:///auth.sqlite',
}
class LoginEvent(db.Model):
__bind_key__ = 'auth'
id: Mapped[int] = mapped_column(primary_key=True)Each bind has separate metadata and engine state; do not assume one atomic transaction spans multiple databases.
Use the session in a test fixturetest-with-context
import pytest
@pytest.fixture()
def app():
app = create_app()
app.config['TESTING'] = True
with app.app_context():
db.create_all()
yield app
db.session.remove()
db.drop_all()An active app context is required even outside requests; for a large suite, transaction rollbacks are faster than rebuilding every table.
Alternatives
| Package | Registry | Pick it when |
|---|---|---|
| SQLAlchemy | PyPI | You want the same ORM without Flask context coupling or extension-specific helpers |
| peewee | PyPI | A small Flask service benefits more from a compact active-record-style ORM |
| Flask-Migrate | PyPI | You already use Flask-SQLAlchemy and specifically need Alembic schema migrations |