Flask Integration¶
sqlspec.extensions.flask provides SQLSpecPlugin for Flask applications, managing synchronous (and portal-bridged asynchronous) connection pools across Flask's application factory lifecycle (init_app), caching request-scoped sessions on flask.g, and finalizing transactions in teardown_appcontext. A request connection is checked out the first time plugin.get_session() or plugin.get_connection() asks for it, so views that never touch the database open none.
Installation¶
Application Factory Setup¶
Pass a single database configuration, a sequence of configurations, or a SQLSpec registry to SQLSpecPlugin(...), initialize it inside your create_app factory with plugin.init_app(app) (or SQLSpecPlugin(config, app)), and retrieve the active request session via plugin.get_session():
from typing import Any
from flask import Flask
from sqlspec.adapters.sqlite import SqliteConfig, SqliteDriver
from sqlspec.extensions.flask import SQLSpecPlugin
def create_app(plugin: SQLSpecPlugin) -> Flask:
"""Create a Flask application initialized with SQLSpecPlugin."""
app = Flask(__name__)
plugin.init_app(app)
@app.get("/health")
def health() -> dict[str, Any]:
db: SqliteDriver = plugin.get_session()
return db.select_one("SELECT 1 AS ok")
return app
plugin = SQLSpecPlugin(SqliteConfig(connection_config={"database": ":memory:"}))
app = create_app(plugin)
Calling plugin.get_session() multiple times within the same HTTP request returns the same flask.g-cached driver session. With an async configuration, plugin.get_session() returns a blocking session whose calls (session.select(...), for row in session.select_stream(...), with session.transaction():) run on the plugin's portal event loop, so views use it without await.
Multi-Database Binds¶
Configure distinct session_key and optional connection_key values under extension_config["flask"] (FlaskConfig; when connection_key is omitted, SQLSpec derives an internal flask.g key automatically from session_key) and pass the key to plugin.get_session("<key>"):
from typing import Any
from flask import Flask
from sqlspec import SQLSpec
from sqlspec.adapters.sqlite import SqliteConfig, SqliteDriver
from sqlspec.extensions.flask import SQLSpecPlugin
def create_app(plugin: SQLSpecPlugin) -> Flask:
"""Create a Flask application exposing a multi-database report endpoint."""
app = Flask(__name__)
plugin.init_app(app)
@app.get("/report")
def report() -> dict[str, list[dict[str, Any]]]:
db: SqliteDriver = plugin.get_session("db")
etl_db: SqliteDriver = plugin.get_session("etl_db")
users = db.select("SELECT 1 AS id, 'Alice' AS name")
metrics = etl_db.select("SELECT 'metric1' AS name, 100 AS value")
return {"users": users, "metrics": metrics}
return app
spec = SQLSpec()
spec.add_config(
SqliteConfig(
connection_config={"database": ":memory:"},
extension_config={"flask": {"session_key": "db", "connection_key": "db_connection"}},
)
)
spec.add_config(
SqliteConfig(
connection_config={"database": ":memory:"},
extension_config={"flask": {"session_key": "etl_db", "connection_key": "etl_connection"}},
)
)
plugin = SQLSpecPlugin(spec)
app = create_app(plugin)
Tenant-Aware Requests¶
With driver_features={"tenant_strategy": "rls"} on a supported adapter, Flask defers checkout until a view or before_request hook verifies membership, calls set_request_tenant(org_id), and calls plugin.get_session(); a synchronous per-bind tenant_extractor can resolve the tenant from g.user instead. See Flask Multi-Tenancy for complete examples and the Tenant Request Contract for the rules every framework shares.
Configuration Options¶
Configure commit_mode inside extension_config["flask"] ("autocommit" by default, "autocommit_include_redirect" for HTML/HTMX/Inertia.js 302/303 redirects, or "manual" for explicit commit() with automatic rollback of uncommitted work):
| Key | Type | Default | Description |
|---|---|---|---|
tenant_extractor |
Callable[..., TenantExtractorValue] |
- | Return the authorized tenant payload, string, UUID, or None (no tenant from this source) for the request. Requires tenant_strategy on the same configuration; called on first tenant use, never at request start. Accepts zero arguments or the Flask request. |
connection_key |
str |
- | Key for storing the connection in Flask g once it is checked out on first access. Default: auto-generated from session_key. |
session_key |
str |
'db_session' |
Key for accessing session via plugin.get_session(). Default: 'db_session'. |
commit_mode |
Literal['manual', 'autocommit', 'autocommit_include_redirect'] |
'autocommit' |
Transaction commit mode. Default: 'autocommit'. manual: No automatic commit; uncommitted work rolls back on release; autocommit: Commits on 2xx status, rollback otherwise; autocommit_include_redirect: Commits on 2xx-3xx status, rollback otherwise |
extra_commit_statuses |
set[int] |
None |
Additional HTTP status codes that trigger commit. Default: None. |
extra_rollback_statuses |
set[int] |
None |
Additional HTTP status codes that trigger rollback. Default: None. |
disable_di |
bool |
False |
Disable built-in dependency injection. Default: False. When True, the Flask extension will not register request hooks for managing database connections and sessions. Users are responsible for managing the database lifecycle manually via their own DI solution. |
enable_sqlcommenter_middleware |
bool |
True |
Control automatic SQLCommenter context population. Default: True. When the driver's :class:~sqlspec.core.statement.StatementConfig has enable_sqlcommenter=True, request attributes are populated automatically. Set to False to explicitly disable this behavior. |
enable_correlation_middleware |
bool |
True |
Enable request correlation ID middleware. Default: True |
correlation_header |
str |
X-Request-ID |
HTTP header to read the request correlation ID from when middleware is enabled. Default: X-Request-ID |
correlation_headers |
tuple[str, ...] or list[str] |
- | Additional HTTP headers to read as correlation ID fallbacks. |
auto_trace_headers |
bool |
True |
Read standard trace context headers as correlation ID fallbacks. Default: True. |
replica_session_key |
str |
- | Request key for a replica session. |
replica_connection_key |
str |
- | Request key for a replica connection. |
routing_session_key |
str |
- | Request key for a routing session. |
default_routing_mode |
Literal['primary', 'auto'] |
'auto' |
Default request routing mode. Default: 'auto'. |
enable_routing_middleware |
bool |
True |
Install request-scoped routing middleware. Default: True. |
routing_header |
str or None |
None |
Trusted application routing header name when explicitly enabled behind a gateway. Default: None. |
primary_pin_cookie |
str or None |
None |
Optional application primary pin cookie name. Default: None. |
primary_pin_secret |
str or bytes or None |
None |
Optional HMAC secret verifying signed primary pin cookies. Default: None. |
Set enable_correlation_middleware=False to stop this plugin from reading request IDs or adding them to response headers.
Read Replica Sessions and Portal Context¶
db_ext.get_db_replica_session(key=...) uses the selected reader on first database use. get_replica_connection(key=...) exposes its physical connection after checkout. get_db_routing_session(key=...) supplies the opt-in routing facade. A bind key or config instance selects the database.
For an async adapter, Flask runs these calls through PortalProvider. Tenant and routing payloads copy into that work. Writes merge their primary pin back to the request; a portal timeout pins conservatively before it raises if completion is uncertain. Each request still owns cleanup. Use explicit worker scopes outside HTTP requests. See Replica Operations for lag and failure behavior.
Connection Lifetime¶
- Normal responses: The request connection is committed or rolled back and released when the request context tears down after the response.
- Streaming responses: A streamed response keeps the connection until its generator finishes, so the generator can use the request session.
- Background work: Threads or tasks that outlive the request run after the connection is released. Open a new session with
config.provide_session()in the worker.
A session or stream cannot be used after its connection is released; trying to raises ImproperConfigurationError.
Recap¶
- Initialize
SQLSpecPluginwithplugin.init_app(app)and access request-scoped sessions viaplugin.get_session(). - Configure
commit_modeand multi-databasesession_keynames inextension_config["flask"]. - See the Flask Extension Reference for
SQLSpecPluginandFlaskConfigsignatures.