Flask-SQLAlchemy

Flask-SQLAlchemy is the Pallets Community Ecosystem’s extension for wiring SQLAlchemy into a Flask application. Rather than requiring developers to hand-roll engine creation, session lifecycle management, and per-request cleanup, it wraps SQLAlchemy’s Engine, Session, and declarative Model classes behind a single SQLAlchemy extension object that Flask app factories can configure once and reuse everywhere.

The extension owns the parts of SQLAlchemy setup that are genuinely Flask-specific: a session scoped to the current Flask application context (so it is created lazily and torn down automatically when the context ends), automatic __tablename__ generation from CamelCase model names, and a get_bind() implementation that picks the right engine when an app uses multiple databases via __bind_key__. Everything else — querying, relationships, migrations — is plain SQLAlchemy, exposed directly through db.Column, db.relationship, and friends so developers are never locked into a parallel API.

Version 3.x tracks SQLAlchemy 2.x’s declarative-style DeclarativeBase and typed Mapped/mapped_column API while still supporting the legacy 1.x style for existing codebases, and adds first-class support for pagination (db.paginate), Flask CLI integration, and per-request query-count tracking for debugging N+1 issues.

What You Get

• A SQLAlchemy extension object configurable once via init_app() and reused across an app factory
• A Session subclass scoped to the Flask application context with automatic teardown on context exit
• Multi-database support via __bind_key__, with per-bind MetaData and automatic engine selection in get_bind()
• A declarative Model base with automatic __tablename__ generation from CamelCase class names
• Built-in db.paginate() / SelectPagination helpers for paginating SQLAlchemy select() queries
• Optional per-request query-count tracking (record_queries) for diagnosing N+1 query problems
• A flask shell integration that auto-imports the db instance and registered models

Common Use Cases

• Adding a relational database layer to a Flask app without hand-managing engine and session lifecycle
• Splitting reads/writes across multiple databases in one app using bind keys
• Migrating an existing SQLAlchemy 1.x codebase to 2.x-style declarative models incrementally
• Paginating large query results in API or template-rendered list views
• Debugging slow request handlers by inspecting per-request executed-query counts
• Sharing model definitions across multiple Flask applications via a single extension instance

Under The Hood

Architecture The library is organized around a central SQLAlchemy class (src/flask_sqlalchemy/extension.py) that acts as a factory and registry: on construction it builds a scoped Session (session.py), a Table subclass that auto-selects MetaData by bind key (table.py), and a declarative Model base (model.py) whose metaclasses (BindMetaMixin, NameMixin) inject automatic table naming and bind-aware metadata assignment at class-creation time rather than at query time. init_app() defers engine creation until a Flask app is actually attached, storing the extension under app.extensions["sqlalchemy"] so multiple apps can share one extension instance with independent per-app engines. The custom Session.get_bind() in session.py is the crux of multi-database support: it inspects the mapper or clause being executed, walks up to find the associated MetaData’s bind_key, and resolves the correct engine from self._db.engines. Changing this core abstraction (context-scoped session + bind resolution) would ripple through every model and query in an app using multiple binds.

Tech Stack Pure Python (no other languages in the repo), targeting flask>=2.2.5 and sqlalchemy>=2.0.16 as its only runtime dependencies, declared in pyproject.toml with a flit_core build backend. Development tooling is comprehensive: ruff for linting (with flake8-bugbear, pyflakes, isort, pyupgrade rule sets), mypy in strict mode plus pyright for a second type-checking pass, and tox (via tox-uv) driving a matrix across Python 3.8 through 3.12 plus dedicated style, typing, and docs environments. Documentation builds with Sphinx using the Pallets Sphinx theme, and uv.lock pins the full dev/test/typing/docs dependency graph.

Code Quality The tests/ directory contains 16 focused test modules (test_extension_object.py, test_model_bind.py, test_session.py, test_pagination.py, test_table_bind.py, and more) covering bind-key resolution, model naming, legacy query compatibility, and CLI integration, run under pytest with filterwarnings = ["error"] in pyproject.toml so any unexpected warning fails the suite. Type coverage is enforced doubly, via mypy --strict and pyright in standard mode, both scoped to src and tests. CI (.github/workflows/tests.yaml, pre-commit.yaml) runs the pre-commit suite and full tox matrix on every push and pull request. Code is consistently typed with explicit TypeVar bounds and from __future__ import annotations, and docstrings throughout use Sphinx cross-reference syntax with per-version versionchanged notes.

What Makes It Unique What distinguishes Flask-SQLAlchemy from wiring SQLAlchemy into Flask by hand is its bind-key system: a single extension instance can maintain separate MetaData and Engine objects per named bind, with model classes routed to the correct one automatically via metaclass-level inspection rather than requiring the developer to manually select a session or engine per query. Combined with declarative-class-creation-time table naming, this lets teams add multi-database support to an existing single-database Flask app with minimal code changes.