How do you use SQLAlchemy with Flask (Flask-SQLAlchemy 3.x)?

Summary

Create db = SQLAlchemy(model_class=Base) in an extensions module, call db.init_app(app) in the factory, define models with SQLAlchemy 2.0 typed Mapped columns, and query with db.session.execute(db.select(...)).

Longer Answer

Flask-SQLAlchemy scopes db.session to the app context and removes it at teardown. Legacy Model.query still works but the 2.0 style (db.select, db.get_or_404, db.paginate) is recommended. Use Flask-Migrate (Alembic) for schema migrations. Watch for N+1 queries with relationships; use selectinload.

Code Example

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from flask_sqlalchemy import SQLAlchemy

class Base(DeclarativeBase):
    pass

db = SQLAlchemy(model_class=Base)

class User(db.Model):
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(unique=True)

@bp.get("/users/<int:uid>")
def get_user(uid: int):
    user = db.get_or_404(User, uid)
    return {"id": user.id, "email": user.email}

@bp.get("/users")
def list_users():
    page = db.paginate(db.select(User).order_by(User.id), per_page=20)
    return {"items": [u.email for u in page.items], "total": page.total}

Key Points

• db.init_app(app) in the factory
• 2.0-style db.select() queries
• Session scoped to app context
• Flask-Migrate for migrations