FLASK-SQLALCHEMY(1)

📦 version 3.1.1, in arch rolling

🦋 NAME 🦋

flask-sqlalchemy — it's the manual page name bestie, and the thing itself is Flask-SQLAlchemy 3.1.1 [image] 💅

So. Flask-SQLAlchemy is an extension for Flask (https://flask.palletsprojects.com/) that adds support for SQLAlchemy (https://www.sqlalchemy.org/) to your application. It simplifies using SQLAlchemy with Flask by setting up common objects and patterns for using those objects, like a session tied to each web request, models, and engines. Iconic behavior honestly ✨

Flask-SQLAlchemy does not change how SQLAlchemy works or is used — no cap, it just wraps it. See the SQLAlchemy documentation (https://docs.sqlalchemy.org/) to learn how to work with the ORM in depth. The docs here only cover setting up the extension, not how to use SQLAlchemy. 🫶

🌈 USER GUIDE 🌈

🌸 Quick Start 🌸

Flask-SQLAlchemy simplifies using SQLAlchemy by automatically handling creating, using, and cleaning up the SQLAlchemy objects you'd normally work with. It adds a few useful features, but it still works like SQLAlchemy. Lowkey the whole vibe 💖

This page walks you through the basic use of Flask-SQLAlchemy. For full capabilities and customization, see the rest of these docs, including the API docs for the SQLAlchemy object (#flask_sqlalchemy.SQLAlchemy). 👑

🦋 Check the SQLAlchemy Documentation 🦋

Flask-SQLAlchemy is a wrapper around SQLAlchemy. So — you should follow the SQLAlchemy Tutorial (https://docs.sqlalchemy.org/en/20/tutorial/index.html) to learn how to use it, and consult its documentation for detailed info about its features. These docs show how to set up Flask-SQLAlchemy itself, not how to use SQLAlchemy. The good news? Flask-SQLAlchemy sets up the engine and scoped session automatically, so you can skip those parts of the SQLAlchemy tutorial. Slay, less work for you 💅

This guide assumes you're using SQLAlchemy 2.x, which has a new API for defining models plus better support for Python type hints and data classes. If you're on SQLAlchemy 1.x, see Legacy Quickstart <>. 🧚

🎀 Installation 🎀

Flask-SQLAlchemy is available on PyPI (https://pypi.org/project/Flask-SQLAlchemy/) and can be installed with various Python tools. For example, to install or update the latest version using pip:

$ pip install -U Flask-SQLAlchemy

💜 Initialize the Extension 💜

First, create the db object using the SQLAlchemy constructor.

Pass a subclass of either DeclarativeBase (https://docs.sqlalchemy.org/en/20/orm/mapping_api.html#sqlalchemy.orm.DeclarativeBase) or DeclarativeBaseNoMeta (https://docs.sqlalchemy.org/en/20/orm/mapping_api.html#sqlalchemy.orm.DeclarativeBaseNoMeta) to the constructor.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
  pass

db = SQLAlchemy(model_class=Base)

Learn more about customizing the base model class in Models and Tables <>. ⭐

🍓 About the SQLAlchemy object 🍓

Once constructed, the db object gives you access to the db.Model class (#flask_sqlalchemy.SQLAlchemy.Model) to define models, and the db.session (#flask_sqlalchemy.SQLAlchemy.session) to execute queries.

The SQLAlchemy object (#flask_sqlalchemy.SQLAlchemy) also takes additional arguments to customize the objects it manages. 🫶

✨ Configure the Extension ✨

Next step: connect the extension to your Flask app. The only required Flask app config is the SQLALCHEMY_DATABASE_URI key (#flask_sqlalchemy.config.SQLALCHEMY_DATABASE_URI). That's a connection string that tells SQLAlchemy what database to connect to. Fr fr it's the one thing you can't skip 🙈

Create your Flask application object, load any config, and then initialize the SQLAlchemy extension class (#flask_sqlalchemy.SQLAlchemy) with the application by calling db.init_app (#flask_sqlalchemy.SQLAlchemy.init_app). This example connects to a SQLite database, which is stored in the app's instance folder:

# create the app
app = Flask(__name__)
# configure the SQLite database, relative to the app instance folder
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///project.db"
# initialize the app with the extension
db.init_app(app)

See Configuration <> for an explanation of connection strings and what other configuration keys are used. 💖

🌟 Define Models 🌟

Subclass db.Model to define a model class. The model will generate a table name by converting the CamelCase class name to snake_case. Automatic, main character energy 👑

from sqlalchemy import Integer, String
from sqlalchemy.orm import Mapped, mapped_column

class User(db.Model):
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    username: Mapped[str] = mapped_column(String, unique=True, nullable=False)
    email: Mapped[str] = mapped_column(String)

See Models and Tables <> for more info about defining and creating models and tables. 🦋

🌸 Create the Tables 🌸

After all models and tables are defined, call SQLAlchemy.create_all() (#flask_sqlalchemy.SQLAlchemy.create_all) to create the table schema in the database. This requires an application context. Since you're not in a request at this point, create one manually:

with app.app_context():
    db.create_all()
⚠️ heads up bestie: if you define models in other modules, you must import them before calling create_all, otherwise SQLAlchemy will not know about them. 😭

create_all does not update tables if they're already in the database. So if you change a model's columns, use a migration library like Alembic (https://alembic.sqlalchemy.org/) with Flask-Alembic (https://flask-alembic.readthedocs.io/) or Flask-Migrate (https://flask-migrate.readthedocs.io/) to generate migrations that update the database schema. Don't try to brute force it, it's giving chaos 💀

💅 Query the Data 💅

Within a Flask view or CLI command, you can use db.session to execute queries and modify model data. This is the fun part ✨

SQLAlchemy automatically defines an __init__ method for each model that assigns any keyword arguments to corresponding database columns and other attributes.

  • db.session.add(obj) adds an object to the session, to be inserted.
  • Modifying an object's attributes updates the object.
  • db.session.delete(obj) deletes an object.

Remember to call db.session.commit() after modifying, adding, or deleting any data. Not me forgetting to commit… 🙈

db.session.execute(db.select(...)) constructs a query to select data from the database. Building queries is the main feature of SQLAlchemy, so you'll want to read its tutorial on select (https://docs.sqlalchemy.org/tutorial/data_select.html) to learn all about it. You'll usually use the Result.scalars() method to get a list of results, or the Result.scalar() method to get a single result. 👑

@app.route("/users")
def user_list():
    users = db.session.execute(db.select(User).order_by(User.username)).scalars()
    return render_template("user/list.html", users=users)

@app.route("/users/create", methods=["GET", "POST"])
def user_create():
    if request.method == "POST":
        user = User(
            username=request.form["username"],
            email=request.form["email"],
        )
        db.session.add(user)
        db.session.commit()
        return redirect(url_for("user_detail", id=user.id))

    return render_template("user/create.html")

@app.route("/user/<int:id>")
def user_detail(id):
    user = db.get_or_404(User, id)
    return render_template("user/detail.html", user=user)

@app.route("/user/<int:id>/delete", methods=["GET", "POST"])
def user_delete(id):
    user = db.get_or_404(User, id)

    if request.method == "POST":
        db.session.delete(user)
        db.session.commit()
        return redirect(url_for("user_list"))

    return render_template("user/delete.html", user=user)
👀 you may see uses of Model.query to build queries. That's an older interface for queries that's considered legacy in SQLAlchemy. Prefer using db.session.execute(db.select(...)) instead. We're moving on bestie.

See Modifying and Querying Data <> for more info about queries. 💜

🧚 What to Remember 🧚

For the most part, you should use SQLAlchemy as usual. The SQLAlchemy extension instance (#flask_sqlalchemy.SQLAlchemy) creates, configures, and gives access to the following things:

  • SQLAlchemy.Model (#flask_sqlalchemy.SQLAlchemy.Model) declarative model base class. It sets the table name automatically instead of needing __tablename__. ✨
  • SQLAlchemy.session (#flask_sqlalchemy.SQLAlchemy.session) is a session that is scoped to the current Flask application context. It is cleaned up after every request.
  • SQLAlchemy.metadata (#flask_sqlalchemy.SQLAlchemy.metadata) and SQLAlchemy.metadatas (#flask_sqlalchemy.SQLAlchemy.metadatas) gives access to each metadata defined in the config.
  • SQLAlchemy.engine (#flask_sqlalchemy.SQLAlchemy.engine) and SQLAlchemy.engines (#flask_sqlalchemy.SQLAlchemy.engines) gives access to each engine defined in the config.
  • SQLAlchemy.create_all() (#flask_sqlalchemy.SQLAlchemy.create_all) creates all tables.
  • You must be in an active Flask application context to execute queries and to access the session and engine. No context = no vibes 💀

👑 Configuration 👑

🎀 Configuration Keys 🎀

Configuration is loaded from the Flask app.config when SQLAlchemy.init_app() (#flask_sqlalchemy.SQLAlchemy.init_app) is called. The configuration is not read again after that. Therefore, all configuration must happen before initializing the application. Order matters, bestie 😭

flask_sqlalchemy.config.SQLALCHEMY_DATABASE_URI

The database connection URI used for the default engine. It can be either a string or a SQLAlchemy URL instance. See below and Engine Configuration (https://docs.sqlalchemy.org/en/20/core/engines.html) for examples.

At least one of this and SQLALCHEMY_BINDS must be set.

Changed in version 3.0: No longer defaults to an in-memory SQLite database if not set. So yeah, you gotta actually tell it now 🫠

flask_sqlalchemy.config.SQLALCHEMY_ENGINE_OPTIONS

A dict of arguments to pass to sqlalchemy.create_engine() (https://docs.sqlalchemy.org/en/20/core/engines.html#sqlalchemy.create_engine) for the default engine.

This takes precedence over the engine_options argument to SQLAlchemy (#flask_sqlalchemy.SQLAlchemy), which can be used to set default options for all engines.

Changed in version 3.0: Only applies to the default bind.
Added in version 2.4.

flask_sqlalchemy.config.SQLALCHEMY_BINDS

A dict mapping bind keys to engine options. The value can be a string or a SQLAlchemy URL instance. Or it can be a dict of arguments, including the url key, that will be passed to sqlalchemy.create_engine() (https://docs.sqlalchemy.org/en/20/core/engines.html#sqlalchemy.create_engine). The None key can be used to configure the default bind, but SQLALCHEMY_ENGINE_OPTIONS and SQLALCHEMY_DATABASE_URI take precedence. 💖

At least one of this and SQLALCHEMY_DATABASE_URI must be set. ✨ Added in version 0.12.

  • flask_sqlalchemy.config.SQLALCHEMY_ECHO — The default value for echo and echo_pool for every engine. This is literally so useful for quickly debugging the connections and queries that SQLAlchemy is issuing. Changed in version 3.0: Sets echo_pool in addition to echo.
  • flask_sqlalchemy.config.SQLALCHEMY_RECORD_QUERIES — If enabled, information about each query during a request will be recorded. Use get_recorded_queries() (cross-ref target: #flask_sqlalchemy.record_queries.get_recorded_queries) to get a list of queries that were issued during the request. Changed in version 3.0: Not enabled automatically in debug or testing mode.
  • flask_sqlalchemy.config.SQLALCHEMY_TRACK_MODIFICATIONS — If enabled, all insert, update, and delete operations on models are recorded, then sent in models_committed (#flask_sqlalchemy.track_modifications.models_committed) and before_models_committed (#flask_sqlalchemy.track_modifications.before_models_committed) signals when session.commit() is called.
⚠️ This adds a significant amount of overhead to every session. Prefer using SQLAlchemy’s ORM Events directly at https://docs.sqlalchemy.org/en/20/orm/events.html for the exact information you need.

Changed in version 3.0: Disabled by default.

Added in version 2.0.

Changed in version 3.1: Removed SQLALCHEMY_COMMIT_ON_TEARDOWN.

Changed in version 3.0: Removed SQLALCHEMY_NATIVE_UNICODE, SQLALCHEMY_POOL_SIZE, SQLALCHEMY_POOL_TIMEOUT, SQLALCHEMY_POOL_RECYCLE, and SQLALCHEMY_MAX_OVERFLOW.

💖 Connection URL Format 💖

See SQLAlchemy’s documentation on Engine Configuration at https://docs.sqlalchemy.org/en/20/core/engines.html for a complete description of syntax, dialects, and options.

A basic database connection URL uses the following format. Username, password, host, and port are optional depending on the database type and configuration.

dialect://username:password@host:port/database

Here are some example connection strings:

# SQLite, relative to Flask instance path
sqlite:///project.db

# PostgreSQL
postgresql://scott:tiger@localhost/project

# MySQL / MariaDB
mysql://scott:tiger@localhost/project

SQLite does not use a user or host, so its URLs always start with three slashes instead of two. The dbname value is a file path. Absolute paths start with a fourth slash (on Linux or Mac). Relative paths are relative to the Flask application’s instance_path (https://flask.palletsprojects.com/en/stable/api/#flask.Flask.instance_path).

🌸 Default Driver Options 🌸

Some default options are set for SQLite and MySQL engines to make them more usable by default in web applications.

SQLite relative file paths are relative to the Flask instance path instead of the current working directory. In-memory databases use a static pool and check_same_thread to work across requests.

MySQL (and MariaDB) servers are configured to drop connections that have been idle for 8 hours, which can result in an error like 2013: Lost connection to MySQL server during query. A default pool_recycle value of 2 hours (7200 seconds) is used to recreate connections before that timeout.

🦋 Engine Configuration Precedence 🦋

Because Flask-SQLAlchemy has support for multiple engines, there are rules for which config overrides other config. Most applications will only have a single database and only need to use SQLALCHEMY_DATABASE_URI and SQLALCHEMY_ENGINE_OPTIONS.

  • If the engine_options argument is given to SQLAlchemy, it sets default options for all engines. SQLALCHEMY_ECHO sets the default value for both echo and echo_pool for all engines.
  • The options for each engine in SQLALCHEMY_BINDS override those defaults.
  • SQLALCHEMY_ENGINE_OPTIONS overrides the None key in SQLALCHEMY_BINDS, and SQLALCHEMY_DATABASE_URI overrides the url key in that engine’s options.

🎀 Timeouts 🎀

Certain databases may be configured to close inactive connections after a period of time. MySQL and MariaDB are configured for this by default, but database services may also configure this type of limit. This can result in an error like 2013: Lost connection to MySQL server during query.

If you encounter this error, try setting pool_recycle in the engine options to a value less than the database’s timeout.

Alternatively, you can try setting pool_pre_ping if you expect the database to close connections often, such as if it’s running in a container that may restart.

See SQAlchemy’s docs on dealing with disconnects at https://docs.sqlalchemy.org/core/pooling.html#dealing-with-disconnects for more information.

👑 Models and Tables 👑

Use the db.Model class to define models, or the db.Table class to create tables. Both handle Flask-SQLAlchemy’s bind keys to associate with a specific engine.

✨ Initializing the Base Class ✨

SQLAlchemy 2.x offers several possible base classes for your models: DeclarativeBase (https://docs.sqlalchemy.org/en/20/orm/mapping_api.html#sqlalchemy.orm.DeclarativeBase) or DeclarativeBaseNoMeta (https://docs.sqlalchemy.org/en/20/orm/mapping_api.html#sqlalchemy.orm.DeclarativeBaseNoMeta).

Create a subclass of one of those classes:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
  pass

If desired, you can enable SQLAlchemy’s native support for data classes (https://docs.sqlalchemy.org/en/20/changelog/whatsnew_20.html#native-support-for-dataclasses-mapped-as-orm-models) by adding MappedAsDataclass as an additional parent class.

from sqlalchemy.orm import DeclarativeBase, MappedAsDataclass

class Base(DeclarativeBase, MappedAsDataclass):
  pass

You can optionally construct the SQLAlchemy object with a custom MetaData object (https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.MetaData). This allows you to specify a custom constraint naming convention (https://docs.sqlalchemy.org/core/constraints.html#constraint-naming-conventions). This makes constraint names consistent and predictable, useful when using migrations, as described by Alembic (https://alembic.sqlalchemy.org/en/latest/naming.html).

from sqlalchemy import MetaData

class Base(DeclarativeBase):
    metadata = MetaData(naming_convention={
        "ix": 'ix_%(column_0_label)s',
        "uq": "uq_%(table_name)s_%(column_0_name)s",
        "ck": "ck_%(table_name)s_%(constraint_name)s",
        "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
        "pk": "pk_%(table_name)s"
    })

💜 Initialize the Extension 💜

Once you’ve defined a base class, create the db object using the SQLAlchemy constructor.

db = SQLAlchemy(model_class=Base)

🌈 Defining Models 🌈

See SQLAlchemy’s declarative documentation at https://docs.sqlalchemy.org/en/20/orm/declarative_tables.html for full information about defining model classes declaratively.

Subclass db.Model to create a model class. Unlike plain SQLAlchemy, Flask-SQLAlchemy’s model will automatically generate a table name if __tablename__ is not set and a primary key column is defined.

from sqlalchemy.orm import Mapped, mapped_column

class User(db.Model):
    id: Mapped[int] = mapped_column(db.Integer, primary_key=True)
    username: Mapped[str] = mapped_column(db.String, unique=True, nullable=False)
    email: Mapped[str] = mapped_column(db.String)

Defining a model does not create it in the database. Use create_all() to create the models and tables after defining them. If you define models in submodules, you must import them so that SQLAlchemy knows about them before calling create_all.

with app.app_context():
    db.create_all()

🍓 Defining Tables 🍓

See SQLAlchemy’s table documentation at https://docs.sqlalchemy.org/en/20/core/metadata.html for full information about defining table objects.

Create instances of db.Table to define tables. The class takes a table name, then any columns and other table parts such as columns and constraints. Unlike plain SQLAlchemy, the metadata argument is not required. A metadata will be chosen based on the bind_key argument, or the default will be used.

A common reason to create a table directly is when defining many to many relationships. The association table doesn’t need its own model class, as it will be accessed through the relevant relationship attributes on the related models.

import sqlalchemy as sa

user_book_m2m = db.Table(
    "user_book",
    sa.Column("user_id", sa.ForeignKey(User.id), primary_key=True),
    sa.Column("book_id", sa.ForeignKey(Book.id), primary_key=True),
)

🧚 Reflecting Tables 🧚

If you are connecting to a database that already has tables, SQLAlchemy can detect that schema and create tables with columns automatically. This is called reflection. Those tables can also be assigned to model classes with the __table__ attribute instead of defining the full model.

Call the reflect() method on the extension. It will reflect all the tables for each bind key. Each metadata’s tables attribute will contain the detected table objects.

with app.app_context():
    db.reflect()

class User:
    __table__ = db.metadatas["auth"].tables["user"]

In most cases, it will be more maintainable to define the model classes yourself. You only need to define the models and columns you will actually use, even if you’re connecting to a broader schema. IDEs will know the available attributes, and migration tools like Alembic can detect changes and generate schema migrations.

🔥 Modifying and Querying Data 🔥

💅 Insert, Update, Delete 💅

See SQLAlchemy’s ORM tutorial at https://docs.sqlalchemy.org/tutorial/orm_data_manipulation.html and other SQLAlchemy documentation for more information about modifying data with the ORM.

To insert data, pass the model object to db.session.add():

user = User()
db.session.add(user)
db.session.commit()

To update data, modify attributes on the model objects:

user.verified = True
db.session.commit()

To delete data, pass the model object to db.session.delete():

db.session.delete(user)
db.session.commit()

After modifying data, you must call db.session.commit() to commit the changes to the database. Otherwise, they will be discarded at the end of the request.

🎀 Select

Okay so querying with the ORM — the whole vibe is covered in SQLAlchemy's Querying Guide and the rest of the SQLAlchemy docs, so go read those for the deep lore. ✨

Here's the tea though: queries run through db.session.execute(). You build them with select(). When you execute a select you get back a Result object, and that thing is stacked with methods for working with the rows it gives you. 💅

user = db.session.execute(db.select(User).filter_by(username=username)).scalar_one()

users = db.session.execute(db.select(User).order_by(User.username)).scalars()

🌸 Queries for Views

So if you're writing a Flask view function, you'll very often want to return a 404 Not Found when the entry someone asked for isn't there. Flask-SQLAlchemy has your back with some extra query methods, no cap:

  • SQLAlchemy.get_or_404() — raises a 404 if the row with the given id doesn't exist, otherwise it hands you back the instance. 🫶
  • SQLAlchemy.first_or_404() — raises a 404 if the query returns zero results, otherwise it gives you the first result.
  • SQLAlchemy.one_or_404() — raises a 404 if the query doesn't return exactly one result, otherwise it gives you that result. Periodt.
@app.route("/user-by-id/<int:id>")
def user_by_id(id):
    user = db.get_or_404(User, id)
    return render_template("show_user.html", user=user)

@app.route("/user-by-username/<username>")
def user_by_username(username):
    user = db.one_or_404(db.select(User).filter_by(username=username))
    return render_template("show_user.html", user=user)

And you can add a custom message to the 404 error, which is lowkey iconic for user experience:

user = db.one_or_404(
    db.select(User).filter_by(username=username),
    description=f"No user named '{username}'."
)

💀 Legacy Query Interface

You might see Model.query or session.query out in the wild being used to build queries. Bestie, that query interface is considered legacy in SQLAlchemy. Please prefer using session.execute(select(...)) instead. 🙈

See Legacy Query Interface for the documentation.

🦋 Paging Query Results

Say you've got a lot of results. You probably only wanna show a certain number at a time and let the user click next and previous links to see pages of data. That's called pagination, and the verb is paginate. Slay. ✨

Call SQLAlchemy.paginate() on a select statement and you get yourself a Pagination object.

During a request, this will grab the page and per_page arguments from the query string request.args. Pass max_per_page to prevent users from requesting too many results on a single page (protect the servers, protect the vibes 💅). If you don't give them, the defaults are page 1 with 20 items per page.

page = db.paginate(db.select(User).order_by(User.join_date))
return render_template("user/list.html", page=page)

🎀 Showing the Items

The Pagination object's Pagination.items attribute is the list of items for the current page. The object can also be iterated over directly, which honestly? Chef's kiss.

<ul>
  {% for user in page %}
    <li>{{ user.username }}
  {% endfor %}
</ul>

🌈 Page Selection Widget

The Pagination object has attributes you can use to build a page selection widget — iterate over page numbers and check the current page. iter_pages() will produce up to three groups of numbers, separated by None. By default it shows 2 page numbers at either edge, 2 numbers before the current, the current, and 4 numbers after the current. 💖

So like, if there are 20 pages and the current page is 7, here's what gets yielded:

users.iter_pages()
[1, 2, None, 5, 6, 7, 8, 9, 10, 11, None, 19, 20]

You can use the total attribute to show the total number of results, and first and last to show the range of items on the current page. Obsessed with a clean range display, ngl.

The following Jinja macro renders a simple pagination widget:

{% macro render_pagination(pagination, endpoint) %}
  <div class=page-items>
    {{ pagination.first }} - {{ pagination.last }} of {{ pagination.total }}
  </div>
  <div class=pagination>
    {% for page in pagination.iter_pages() %}
      {% if page %}
        {% if page != pagination.page %}
          <a href="{{ url_for(endpoint, page=page) }}">{{ page }}</a>
        {% else %}
          <strong>{{ page }}</strong>
        {% endif %}
      {% else %}
        <span class=ellipsis>…</span>
      {% endif %}
    {% endfor %}
  </div>
{% endmacro %}

🌸 Flask Application Context

An active Flask application context is required to make queries and to access db.engine and db.session. Why? Because the session is scoped to the context so it gets cleaned up properly after every request or CLI command. It's giving responsible adult energy. 🫶

And listen — no matter how the application gets initialized with the extension, it is not stored for later use. Instead, the extension uses Flask's current_app proxy to get the active application, which requires an active application context. Iykyk.

Automatic Context

When Flask is handling a request or a CLI command, an application context gets pushed automatically. So you literally don't need to do anything special to use the database during requests or CLI commands. Bestie, it's handled. ✨

Manual Context

If you try to use the database when an application context is not active, you will see this error, and she is loud:

RuntimeError: Working outside of application context.

This typically means that you attempted to use functionality that needed
the current application. To solve this, set up an application context
with app.app_context(). See the documentation for more information.

If you're in a situation where you need the database and don't have a context, you can push one with app_context. This is super common when calling db.create_all to create the tables, for example:

def create_app():
    app = Flask(__name__)
    app.config.from_object("project.config")

    import project.models

    with app.app_context():
        db.create_all()

    return app

Tests

If you're testing your app with the Flask test client to make requests to your endpoints, the context will be available as part of the request. But if you need to test something about your database or models directly, rather than going through a request, you gotta push a context manually. 💅

Only push a context exactly where and for how long it's needed for each test. Do not push an application context globally for every test, because that can interfere with how the session is cleaned up. Big deal, fr fr. 💀
def test_user_model(app):
    user = User()

    with app.app_context():
        db.session.add(user)
        db.session.commit()

And if you find yourself writing many tests like that, you can use a pytest fixture to push a context for a specific test — so much cleaner:

import pytest

@pytest.fixture
def app_ctx(app):
    with app.app_context():
        yield

@pytest.mark.usefixtures("app_ctx")
def test_user_model():
    user = User()
    db.session.add(user)
    db.session.commit()

👑 Multiple Databases with Binds

SQLAlchemy can connect to more than one database at a time. It calls the different engines "binds". 🌈

Flask-SQLAlchemy simplifies how binds work by associating each engine with a short string — a "bind key" — and then associating each model and table with a bind key. The session picks which engine to use for a query based on the bind key of the thing being queried. If no bind key is given, the default engine is used. Simple as that. ✨

Configuring Binds

The default bind is still configured by setting SQLALCHEMY_DATABASE_URI, and SQLALCHEMY_ENGINE_OPTIONS for any engine options. Additional binds go in SQLALCHEMY_BINDS — a dict mapping bind keys to engine URLs. 🎀

To specify engine options for a bind, the value can be a dict of engine options with the "url" key instead of only a URL string:

SQLALCHEMY_DATABASE_URI = "postgresql:///main"
SQLALCHEMY_BINDS = {
    "meta": "sqlite:////path/to/meta.db",
    "auth": {
        "url": "mysql://localhost/users",
        "pool_recycle": 3600,
    },
}

Defining Models and Tables with Binds

Flask-SQLAlchemy will create a metadata and engine for each configured bind. Models and tables with a bind key get registered with the corresponding metadata, and the session will query them using the corresponding engine. Matching, but make it fashion. 💅

To set the bind for a model, set the __bind_key__ class attribute. Not setting a bind key is equivalent to setting it to None, the default key:

class User(db.Model):
    __bind_key__ = "auth"
    id = db.Column(db.Integer, primary_key=True)

Models that inherit from this model will share the same bind key, or can override it — their choice, main character energy. ✨

To set the bind for a table, pass the bind_key keyword argument:

user_table = db.Table(
    "user",
    db.Column("id", db.Integer, primary_key=True),
    bind_key="auth",
)
Ultimately, the session looks up the bind key on the metadata associated with the model or table. That association happens during creation. So changing the bind key after creating a model or table will have no effect. Not me warning you twice about this. 😭

Accessing Metadata and Engines

You may need to inspect the metadata or engine for a bind. Quick note though: you should execute queries through the session, not directly on the engine. 🫶

The default engine is SQLAlchemy.engine, and the default metadata is SQLAlchemy.metadata. SQLAlchemy.engines and SQLAlchemy.metadatas are dicts mapping all bind keys. Go off, dicts. 👑

🗂️ Creating and Dropping Tables 🗂️

okay so create_all() and drop_all() — these two operate on all binds by default, no cap. The bind_key argument is your little remote control here: give it a string or None and it does that single bind; give it a list of strings or None and it does that subset of binds. 💅

⚠️ Heads up bestie: because these methods reach right into the engines, they must be called inside an application context. No context, no table party. 😭
# create tables for all binds
db.create_all()

# create tables for the default and "auth" binds
db.create_all(bind_key=[None, "auth"])

# create tables for the "meta" bind
db.create_all(bind_key="meta")

# drop tables for the default bind
db.drop_all(bind_key=None)

📊 Recording Query Information 📊

💀 Warning: This feature is intended for debugging only. Read that again. It's a debug toy, not a production accessory.

So Flask-SQLAlchemy can literally record info about every query that runs during a request, and you can pull that info back out later to debug your performance. It's giving detective energy 🔍 — like it can expose that a relationship went and did way too many individual selects, or point at a query that took forever and a day.

To turn it on, set SQLALCHEMY_RECORD_QUERIES to True in the Flask app config. Then hit up get_recorded_queries() to get a list of query info objects. Each object is serving these attributes:

  • statement — the string of SQL that SQLAlchemy generated, with parameter placeholders. ✨
  • parameters — the parameters that got sent along with the SQL statement.
  • start_time / end_time — the timing tea ☕ about when the query started executing and when the results came back. Accuracy and value depend on the operating system, so like, your mileage may vary.
  • duration — how long the query took, in seconds.
  • location — a string description of where in your app code the query ran. This may be unknown in certain cases. 🙈

🔄 Tracking Modifications 🔄

💀 Warning: Tracking changes adds significant overhead. In most cases, you'll be better served by using SQLAlchemy events directly. fr fr, just use the events.

So this feature sets up your session to track inserts, updates, and deletes for models, then sends a Blinker signal with a list of those changes — either before or during calls to session.flush() and session.commit(). 🌸

Wanna enable it? Set SQLALCHEMY_TRACK_MODIFICATIONS in the Flask app config. Then add a listener to models_committed (this one's emitted after the commit) or before_models_committed (this one's emitted before the commit). 🎀

from flask_sqlalchemy.track_modifications import models_committed

def get_modifications(sender: Flask, changes: list[tuple[t.Any, str]]) -> None:
    ...

models_committed.connect(get_modifications)

🎨 Advanced Customization 🎨

The various objects the extension manages? All customizable, bestie — you just pass arguments to the SQLAlchemy constructor. It's giving build-your-own-adventure. 🦋

🏗️ Model Class 🏗️

SQLAlchemy models all inherit from a declarative base class. Flask-SQLAlchemy exposes that as db.Model, and every model extends it. Want your own? Subclass the default and hand your custom class over to model_class. 💖

The example below gives every model an integer primary key — or a foreign key for joined-table inheritance.

📝 Note: Integer primary keys for everything is not necessarily the best database design (that's up to your project's requirements, bestie). This is only an example. Don't come for me.
from sqlalchemy import Integer, String, ForeignKey
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, declared_attr

class Base(DeclarativeBase):
    @declared_attr.cascading
    @classmethod
    def id(cls):
        for base in cls.__mro__[1:-1]:
            if getattr(base, "__table__", None) is not None:
                    return mapped_column(ForeignKey(base.id), primary_key=True)
            else:
                return mapped_column(Integer, primary_key=True)

db = SQLAlchemy(app, model_class=Base)

class User(db.Model):
    name: Mapped[str] = mapped_column(String)

class Employee(User):
    title: Mapped[str] = mapped_column(String)

🧬 Abstract Models and Mixins 🧬

If the behavior is only needed on some models rather than all of them, use an abstract model base class to customize just those models. Iconic move. For example — what if some models should track when they're created or updated? 👑

from datetime import datetime
from sqlalchemy import DateTime, Integer, String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, declared_attr

class TimestampModel(db.Model):
    __abstract__ = True
    created: Mapped[datetime] = mapped_column(DateTime, nullable=False, default=datetime.utcnow)
    updated: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

class Author(db.Model):
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    username: Mapped[str] = mapped_column(String, unique=True, nullable=False)

class Post(TimestampModel):
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    title: Mapped[str] = mapped_column(String, nullable=False)

You can also do this with a mixin class, inheriting from db.Model separately. Same vibe, different technique. ✨

class TimestampMixin:
    created: Mapped[datetime] = mapped_column(DateTime, nullable=False, default=datetime.utcnow)
    updated: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

class Post(TimestampMixin, db.Model):
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    title: Mapped[str] = mapped_column(String, nullable=False)

🚫 Disabling Table Name Generation 🚫

Some projects are like "no thank you, I'll set each model's __tablename__ myself" instead of letting Flask-SQLAlchemy detect and generate it. The simple way: set each __tablename__ and just don't touch the base class. But if you truly want the generation off, set disable_autonaming=True in the SQLAlchemy constructor. 💅

class Base(sa_orm.DeclarativeBase):
    pass

db = SQLAlchemy(app, model_class=Base, disable_autonaming=True)

🔌 Session Class 🔌

Flask-SQLAlchemy's Session class picks which engine to query based on the bind key associated with the model or table. Cute, right? But there are other strategies out there — horizontal sharding, for example — and those can be implemented with a different session class. Use the class_ key in the session_options argument to the extension to swap it out. 🌀

Flask-SQLAlchemy will always pass the extension instance as the db argument to the session, so your custom session has to accept that to keep working. Which is actually useful — you can use it to get at db.engines. 🫶

from sqlalchemy.ext.horizontal_shard import ShardedSession
from flask_sqlalchemy.session import Session

class CustomSession(ShardedSession, Session):
    ...

db = SQLAlchemy(session_options={"class_": CustomSession})

🔍 Query Class 🔍

💀 Warning: The query interface is considered legacy in SQLAlchemy. Yes, that includes session.query, Model.query, db.Query, and lazy="dynamic" relationships. Prefer using session.execute(select(...)) instead. That's the move, bestie.

You can still customize the query interface that the session, models, and relationships use — handy for adding extra query methods. Example: a get_or method that gets a row, or returns a default. 🎀

from flask_sqlalchemy.query import Query

class GetOrQuery(Query):
    def get_or(self, ident, default=None):
        out = self.get(ident)

        if out is None:
            return default

        return out

db = SQLAlchemy(query_class=GetOrQuery)

user = User.query.get_or(user_id, anonymous_user)

Passing the query_class argument customizes db.Query, db.session.query, Model.query, and db.relationship(lazy="dynamic") relationships. And yep — you can also customize these on a per-object basis. ✨

To customize a specific model's query property, set the query_class attribute on the model class:

class User(db.Model):
    query_class = GetOrQuery

To customize a specific dynamic relationship, pass the query_class argument to the relationship:

db.relationship(User, lazy="dynamic", query_class=GetOrQuery)

And to customize only session.query, pass the query_cls key to the session_options argument of the constructor:

db = SQLAlchemy(session_options={"query_cls": GetOrQuery})

🧾 API REFERENCE 🧾

📚 API 📚

🧩 Extension 🧩

class flask_sqlalchemy.SQLAlchemy(app=None, *, metadata=None,
session_options=None, query_class=<class
'flask_sqlalchemy.query.Query'>, model_class=<class
'flask_sqlalchemy.model.Model'>, engine_options=None,
add_models_to_shell=True, disable_autonaming=False)

Okay so this class integrates SQLAlchemy with Flask — the whole glow-up 💖. It handles setting up one or more engines, associating tables and models with specific engines, and cleaning up connections and sessions after each request. Main character energy, honestly.

Only the engine configuration is specific to each application. Everything else — the model, table, metadata, and session — is shared for all applications using that extension instance. Call init_app() to configure the extension on an application.

After creating the extension, you create model classes by subclassing Model, and table classes with Table. And plot twist: these can be accessed before init_app() is called, which makes it possible to define your models separately from the application. Obsessed. 🌈

Accessing session and engine requires an active Flask application context — and yep, that includes methods like create_all() which use the engine. No context, no access. 😭

This class also gives you access to names in SQLAlchemy's sqlalchemy and sqlalchemy.orm modules. Like, you can use db.Column and db.relationship instead of importing sqlalchemy.Column and sqlalchemy.orm.relationship. So convenient when you're defining models. 🦋

Parameters

  • app (Flask | None) — Call init_app() on this Flask application now.
  • metadata (sa.MetaData | None) — Use this as the default sqlalchemy.schema.MetaData. Useful for setting a naming convention. 🎀
  • session_options (dict[str, t.Any] | None) — the lil' argument bag that session uses to build every session instance 🫶. Heads up: a scopefunc key gets passed to the scoped session, not to the session instance itself. Wanna see the full menu? Peep sqlalchemy.orm.sessionmaker for the list of arguments.
  • query_class (type[Query]) — use this as the default query class for models and dynamic relationships 💅. Real talk though: the query interface is considered legacy in SQLAlchemy. Just so you know.
  • model_class (_FSA_MCT) — use this as the model base class when the declarative model class Model gets created ✨. You can also hand over a fully created declarative model class if you want to customize it further.
  • engine_options (dict[str, t.Any] | None) — default arguments used when creating every engine 🔥. These are lower precedence than application config (so config wins, periodt). See sqlalchemy.create_engine() for the list of arguments.
  • add_models_to_shell (bool) — adds the db instance and all model classes to the flask shell 🐚. Convenient? Iconic.
  • disable_autonaming (bool) — that's it, that's the parameter 🎀.

⏳ Changed in version — the deep lore 💫

  • Changed in version 3.1.0: the metadata parameter can still be used with SQLAlchemy 1.x classes, but it's ignored when you're using the SQLAlchemy 2.x style of declarative classes. Instead, specify metadata on your Base class. No cap.
  • Changed in version 3.1.0: added the disable_autonaming parameter.
  • Changed in version 3.1.0: changed model_class to accept a SQLAlchemy 2.x declarative base subclass.
  • Changed in version 3.0: an active Flask application context is always required to access session and engine.
  • Changed in version 3.0: separate metadata are used for each bind key.
  • Changed in version 3.0: the engine_options parameter is applied as defaults before per-engine configuration.
  • Changed in version 3.0: the session class can be customized in session_options.
  • Changed in version 3.0: added the add_models_to_shell parameter.
  • Changed in version 3.0: engines are created when you call init_app, rather than the first time they're accessed.
  • Changed in version 3.0: all parameters except app are keyword-only.
  • Changed in version 3.0: the extension instance is stored directly as app.extensions["sqlalchemy"].
  • Changed in version 3.0: setup methods are renamed with a leading underscore. They're considered internal interfaces which may change at any time 🙈.
  • Changed in version 3.0: removed the use_native_unicode parameter and config.
  • Changed in version 2.4: added the engine_options parameter.
  • Changed in version 2.1: added the metadata, query_class, and model_class parameters.
  • Changed in version 2.1: use the same query class across session, Model.query and Query.
  • Changed in version 0.16: scopefunc is accepted in session_options.
  • Changed in version 0.10: added the session_options parameter.

🌸 Model 🌸

A SQLAlchemy declarative model class 💜. Subclass this bad boy to define your database models.

If a model doesn't set __tablename__, it gets generated for you by converting the class name from CamelCase to snake_case ✨. It will not be generated if the model looks like it uses single-table inheritance (iykyk).

If a model or parent class sets __bind_key__, it'll use that metadata and database engine. Otherwise, it uses the default metadata and engine. This is ignored if the model sets metadata or __table__.

For code using the SQLAlchemy 1.x API, customize this model by subclassing Model and passing the model_class parameter to the extension 🫶. A fully created declarative model class can be passed as well, to use a custom metaclass.

For code using the SQLAlchemy 2.x API, customize this model by subclassing sqlalchemy.orm.DeclarativeBase or sqlalchemy.orm.DeclarativeBaseNoMeta and passing the model_class parameter to the extension 💅.

🌈 Query 🌈

The default query class used by Model.query and lazy="dynamic" relationships.

Warning: the query interface is considered legacy in SQLAlchemy ⚠️.

Customize this by passing the query_class parameter to the extension.

🦋 Table 🦋

A sqlalchemy.schema.Table class that chooses a metadata automatically ✨.

Unlike the base Table, the metadata argument is not required. If it's not given, it's selected based on the bind_key argument.

Parameters

  • bind_key — used to select a different metadata.
  • args — arguments passed to the base class. These are typically the table's name, columns, and constraints.
  • kwargs — arguments passed to the base class.

Changed in version 3.0: this is a subclass of SQLAlchemy's Table rather than a function.

👑 create_all(bind_key='__all__') 👑

Create tables that don't exist in the database by calling metadata.create_all() for all or some bind keys 🎀. Real talk: this does not update existing tables — use a migration library for that, bestie.

This requires that a Flask application context is active ⭐.

Parameters

  • bind_key (str | None | list[str | None]) — a bind key or list of keys to create the tables for. Defaults to all binds.

Return type

None

Changed in version 3.0: renamed the bind parameter to bind_key. Removed the app parameter.

Changed in version 0.12: added the bind and app parameters.

💀 drop_all(bind_key='__all__') 💀

Drop tables by calling metadata.drop_all() for all or some bind keys 🫶.

This requires that a Flask application context is active ⭐.

Parameters

  • bind_key (str | None | list[str | None]) — a bind key or list of keys to drop the tables from. Defaults to all binds.

Return type

None

Changed in version 3.0: renamed the bind parameter to bind_key. Removed the app parameter.

Changed in version 0.12: added the bind and app parameters.

🧚 dynamic_loader(argument, **kwargs) 🧚

A sqlalchemy.orm.dynamic_loader() that applies this extension's Query class for relationships and backrefs 💫.

Changed in version 3.0: the Query class is set on backref.

Parameters

  • argument (Any)
  • kwargs (Any)

Return type

RelationshipProperty[Any]

🔥 property engine: Engine 🔥

The default Engine for the current application, used by session if the Model or Table being queried does not set a bind key 🎀.

To customize, set the SQLALCHEMY_ENGINE_OPTIONS config, and set defaults by passing the engine_options parameter to the extension.

This requires that a Flask application context is active ⭐.

engines 🫶 (property)

Type: Mapping[str | None, Engine] — like, a map of bind keys to sqlalchemy.engine.Engine instances for whatever application you're in right now, bestie.

The None key is the default engine, and you can also grab it as engine. iykyk 💅

Wanna customize it? Set the SQLALCHEMY_BINDS config, and set your defaults by passing the engine_options parameter to the extension. That's the whole vibe.

Heads up: this literally requires that a Flask application context is active. No context, no engines, no nothing 😭

Added in version 3.0.

first_or_404(statement, *, description=None) 💖

Basically Result.scalar(), but instead of returning None it aborts with a 404 Not Found error. So dramatic, so correct 🎀

Parameters

  • statement (Select) — the select statement to execute.
  • description (str | None) — a custom message to show on the error page.

Return type: Any

Added in version 3.0.

get_engine(bind_key=None, **kwargs) 🦋

Get the engine for the given bind key for the current application. Same deal as before: this requires that a Flask application context is active, no cap.

Parameters

  • bind_key (str | None) — the name of the engine.
  • kwargs (Any)

Return type: Engine

💀 Deprecated since version 3.0: this will be removed in Flask-SQLAlchemy 3.2. Use engines[key] instead, bestie, it's the moment.

Changed in version 3.0: renamed the bind parameter to bind_key, and removed the app parameter.

get_or_404(entity, ident, *, description=None, **kwargs) ✨

Like session.get() but aborts with a 404 Not Found error instead of returning None. Same energy as first_or_404, different plumbing 🧚

Parameters

  • entity (type[_O]) — the model class to query.
  • ident (Any) — the primary key to query.
  • description (str | None) — a custom message to show on the error page.
  • kwargs (Any) — extra arguments passed to session.get().

Return type: _O

Changed in version 3.1: extra keyword arguments get passed to session.get(). Added in version 3.0.

init_app(app) 👑

Initialize a Flask application for use with this extension instance. You must call this before accessing the database engine or session with the app — it's giving prerequisites, honestly.

What it does: sets default configuration values, then configures the extension on the application and creates the engines for each bind key. Which means you have to call it after the application has been configured. Changes to application config after this call will not be reflected. Not a single one. 🙈

The following keys from app.config are used:

  • SQLALCHEMY_DATABASE_URI
  • SQLALCHEMY_ENGINE_OPTIONS
  • SQLALCHEMY_ECHO
  • SQLALCHEMY_BINDS
  • SQLALCHEMY_RECORD_QUERIES
  • SQLALCHEMY_TRACK_MODIFICATIONS

Parameters

  • app (Flask) — the Flask application to initialize.

Return type: None

metadata 🎀 (property)

Type: MetaData

The default metadata used by Model and Table if no bind key is set. The main character metadata, basically 👑

metadatas 🌸

Type: dict[str | None, MetaData]

Map of bind keys to sqlalchemy.schema.MetaData instances. The None key refers to the default metadata, and is available as metadata — yes, same one from up there, they're literally the same thing 💅

Customize the default metadata by passing the metadata parameter to the extension. This can be used to set a naming convention (so organized, so slay). When metadata for another bind key is created, it copies the default's naming convention. Copying the vibe fr fr.

Added in version 3.0.

one_or_404(statement, *, description=None) ⭐

Like Result.scalar_one(), but aborts with a 404 Not Found error instead of raising NoResultFound or MultipleResultsFound. We do not do exceptions here, we do 404s 🔥

Parameters

  • statement (Select) — the select statement to execute.
  • description (str | None) — a custom message to show on the error page.

Return type: Any

Added in version 3.0.

paginate(select, *, page=None, per_page=None, max_per_page=None, error_out=True, count=True) 🦋

Apply an offset and limit to a select statement based on the current page and number of items per page, returning a Pagination object. It's the "give me page 3 of the search results" of the SQLAlchemy world 🌈

The statement should select a model class, like select(User). This applies unique() and scalars() modifiers to the result, so compound selects will not return the expected results — that's a real caveat, not a vibe check, don't say I didn't warn you 😭

Parameters

  • select (Select) — the select statement to paginate.
  • page (int | None) — the current page, used to calculate the offset. Defaults to the page query arg during a request, or 1 otherwise.
  • per_page (int | None) — the maximum number of items on a page, used to calculate the offset and limit. Defaults to the per_page query arg during a request, or 20 otherwise.
  • max_per_page (int | None) — the maximum allowed value for per_page, to limit a user-provided value. Use None for no limit. Defaults to 100.
  • error_out (bool) — abort with a 404 Not Found error if no items are returned and page is not 1, or if page or per_page is less than 1, or if either are not ints.
  • count (bool) — calculate the total number of values by issuing an extra count query. For very complex queries this may be inaccurate or slow, so it can be disabled and set manually if necessary.

Return type: Pagination

Changed in version 3.0: the count query is more efficient. Added in version 3.0.

reflect(bind_key='__all__') 🧚

Load table definitions from the database by calling metadata.reflect() for all or some bind keys. Very "let the database tell me about itself" of it ✨

Requires that a Flask application context is active. Again. It's always the application context 🫶

okay so continuing right where we left off — here come the params for that one 🫶

Parameters

  • bind_key (str | None | list[str | None]) — a bind key, or a whole list of keys, telling it which tables to reflect. leave it out and it defaults to all binds. yes bestie, all of them 💅
  • Return type: None

Changed in version 3.0: renamed the bind parameter to bind_key, and removed the app parameter entirely. she's gone 💀

Changed in version 0.12: added the bind and app parameters.

relationship(*args, **kwargs)

It's a sqlalchemy.orm.relationship() but it slaps this extension's Query class on dynamic relationships and backrefs. so your relationships get the good query class, no cap ✨

Changed in version 3.0: the Query class is now set on backref too.

Parameters

  • args (Any)
  • kwargs (Any)

Return type: RelationshipProperty[Any]

session

A sqlalchemy.orm.scoping.scoped_session that creates Session instances scoped to the current Flask application context. when the app context exits, the session gets removed and the engine connection goes right back to the pool. so clean, so polite 🧚

Customize it by passing session_options to the extension.

this one requires an active Flask application context. no context = no session, bestie, that's just how it is 😭

Changed in version 3.0: the session is scoped to the current app context now.

🌸 Model 🌸

class flask_sqlalchemy.model.Model

The base class of the SQLAlchemy.Model declarative model class.

To define models, subclass db.Model — not this. To customize db.Model, you subclass this and pass it as model_class to SQLAlchemy. And if you wanna customize db.Model at the metaclass level, pass an already created declarative model class as model_class. ikyk what you're doing at that point 💜

  • __bind_key__ — the bind key that selects which metadata and engine get associated with this model's table. it gets ignored if metadata or __table__ is set. and if you don't give one, it uses the default key, None.
  • __tablename__ — the name of the table in the database. SQLAlchemy requires this, but Flask-SQLAlchemy will set it automatically if a model has a primary key defined. if __table__ or __tablename__ is set explicitly, then that's what gets used instead. she respects your choices 👑
  • query: t.ClassVar[Query] — a SQLAlchemy query for a model. equivalent to db.session.query(Model). you can customize it per-model by overriding query_class.
Warning: the query interface is considered legacy in SQLAlchemy. prefer using session.execute(select()) instead, bestie, that's the vibe now 🙈
  • query_class — the Query class used by query. defaults to SQLAlchemy.Query, which defaults to Query.

alias of Query

🦋 Metaclass mixins (SQLAlchemy 1.x) 🦋

If your code uses the SQLAlchemy 1.x API (which is the default for code that doesn't specify a model_class), then these mixins get automatically applied to the Model class. automatic. iconic. ⭐

class flask_sqlalchemy.model.DefaultMeta(name, bases, d, **kwargs)

SQLAlchemy declarative metaclass that provides __bind_key__ and __tablename__ support.

Parameters

  • name (str)
  • bases (tuple[type, ...])
  • d (dict[str, t.Any])
  • kwargs (t.Any)

class flask_sqlalchemy.model.BindMetaMixin(name, bases, d, **kwargs)

Metaclass mixin that sets a model's metadata based on its __bind_key__.

If the model sets metadata or __table__ directly, __bind_key__ gets ignored. and if the metadata is the same as the parent model's, it won't be set directly on the child model. sharing is caring, but only sometimes 💅

Parameters

  • name (str)
  • bases (tuple[type, ...])
  • d (dict[str, t.Any])
  • kwargs (t.Any)

class flask_sqlalchemy.model.NameMetaMixin(name, bases, d, **kwargs)

Metaclass mixin that sets a model's __tablename__ by converting the CamelCase class name into snake_case. a name gets set for non-abstract models that don't otherwise define __tablename__. but if a model doesn't define a primary key, it won't generate a name or __table__ — that's for single-table inheritance, so don't come for her 🌈

Parameters

  • name (str)
  • bases (tuple[type, ...])
  • d (dict[str, t.Any])
  • kwargs (t.Any)

👑 Session 👑

class flask_sqlalchemy.session.Session(db, **kwargs)

A SQLAlchemy Session class that picks which engine to use based on the bind key associated with the metadata associated with the thing you're querying. say that five times fast 😭

To customize db.session, subclass this and pass it as the class_ key in the session_options to SQLAlchemy.

Changed in version 3.0: renamed from SignallingSession. new name, same energy.

Parameters

  • db (SQLAlchemy)
  • kwargs (t.Any)

get_bind(mapper=None, clause=None, bind=None, **kwargs)

Selects an engine based on the bind_key of the metadata associated with the model or table being queried. if no bind key is set, it uses the default bind. easy 🌸

Changed in version 3.0.3: fixed finding the bind for a joined inheritance model.

Changed in version 3.0: the implementation now more closely matches the base SQLAlchemy implementation.

Changed in version 2.1: supports joining an external transaction.

Parameters

  • mapper (Any | None)
  • clause (Any | None)
  • bind (Engine | Connection | None)
  • kwargs (Any)

Return type: Engine | Connection

🎀 Pagination 🎀

class flask_sqlalchemy.pagination.Pagination

A slice of the total items in a query, obtained by applying an offset and limit based on the current page and the number of items per page. literally just a page of your stuff, bestie 🍓

don't create pagination objects manually! they're created by SQLAlchemy.paginate() and Query.paginate(). hands off ✋

Changed in version 3.0: iterating over a pagination object iterates over its items.

Changed in version 3.0: creating instances manually is not a public API.

  • page: int — the current page.
  • per_page: int — the maximum number of items on a page.
  • items: list[Any] — the items on the current page. iterating over the pagination object is equivalent to iterating over the items. same thing, different font 💅
  • total: int | None — the total number of items across all pages.
  • property first: int — the number of the first item on the page, starting from 1, or 0 if there are no items.

Added in version 3.0.

  • property last: int — the number of the last item on the page, starting from 1, inclusive, or 0 if there are no items.

Added in version 3.0.

  • pages (int) — the total number of pages, bestie. that's it. that's the number. 💅
  • has_prev (bool) — True if this is not the first page. basically "is there a before?" 🦋
  • prev_num (int | None) — the previous page number, or None if this is the first page. no before, no number, periodt.
  • prev(*, error_out=False) — go grab the previous page from the Pagination object. that's the whole vibe.

Parameters:

  • error_out (bool) — Abort with a 404 Not Found error if no items are returned and page is not 1, or if page or per_page is less than 1, or if either are not ints.

Return type: Pagination

  • has_next (bool) — True if this is not the last page. the "is there more??" check ✨
  • next_num (int | None) — the next page number, or None if this is the last page.
  • next(*, error_out=False) — same energy as prev but forwards. Query the Pagination object for the next page.

Parameters:

  • error_out (bool) — Abort with a 404 Not Found error if no items are returned and page is not 1, or if page or per_page is less than 1, or if either are not ints.

Return type: Pagination

  • iter_pages(*, left_edge=2, left_current=2, right_current=4, right_edge=2) — Yield page numbers for a pagination widget. Skipped pages between the edges and middle are represented by a None. (yes literally a None in the stream, that's the whole trick, don't panic 🙈)

For example, if there are 20 pages and the current page is 7, the following values are yielded.

1, 2, None, 5, 6, 7, 8, 9, 10, 11, None, 19, 20

Parameters:

  • left_edge (int) — How many pages to show from the first page.
  • left_current (int) — How many pages to show left of the current page.
  • right_current (int) — How many pages to show right of the current page.
  • right_edge (int) — How many pages to show from the last page.

Return type: Iterator[int | None]

Changed in version 3.0: Improved efficiency of calculating what to yield.

Changed in version 3.0: right_current boundary is inclusive.

Changed in version 3.0: All parameters are keyword-only.

🦋 Query 🦋

class flask_sqlalchemy.query.Query(entities, session=None)

okay so this is the SQLAlchemy Query subclass with some extra methods that are lowkey so useful for querying in a web application. it's giving "everything you already know plus the good stuff." 💖

This is the default query class for Model.query.

Changed in version 3.0: Renamed to Query from BaseQuery.

Parameters:

  • entities (Union[_ColumnsClauseArgument[Any], Sequence[_ColumnsClauseArgument[Any]]])
  • session (Optional[Session])

✨ the methods ✨

  • first_or_404(description=None) — Like first() but aborts with a 404 Not Found error instead of returning None. so you get a cute lil error page instead of a silent None haunting your template 😭 iconic behavior honestly.

Parameters:

  • description (str | None) — A custom message to show on the error page.

Return type: Any

  • get_or_404(ident, description=None) — Like get() but aborts with a 404 Not Found error instead of returning None. same slay, different lookup.

Parameters:

  • ident (Any) — The primary key to query.
  • description (str | None) — A custom message to show on the error page.

Return type: Any

  • one_or_404(description=None) — Like one() but aborts with a 404 Not Found error instead of raising NoResultFound or MultipleResultsFound. no cap, this one is the move — no exceptions sneaking out, just a clean 404. 🫶

Parameters:

  • description (str | None) — A custom message to show on the error page.

Return type: Any

Added in version 3.0.

  • paginate(*, page=None, per_page=None, max_per_page=None, error_out=True, count=True) — Apply an offset and limit to the query based on the current page and number of items per page, returning a Pagination object. the main character of pagination fr fr 👑

Parameters:

  • page (int | None) — The current page, used to calculate the offset. Defaults to the page query arg during a request, or 1 otherwise.
  • per_page (int | None) — The maximum number of items on a page, used to calculate the offset and limit. Defaults to the per_page query arg during a request, or 20 otherwise.
  • max_per_page (int | None) — The maximum allowed value for per_page, to limit a user-provided value. Use None for no limit. Defaults to 100.
  • error_out (bool) — Abort with a 404 Not Found error if no items are returned and page is not 1, or if page or per_page is less than 1, or if either are not ints.
  • count (bool) — Calculate the total number of values by issuing an extra count query. For very complex queries this may be inaccurate or slow, so it can be disabled and set manually if necessary. (heads up bestie, "inaccurate or slow" is not a joke, that's a real caveat 💀)

Return type: Pagination

Changed in version 3.0: All parameters are keyword-only.

Changed in version 3.0: The count query is more efficient.

Changed in version 3.0: max_per_page defaults to 100.

🌸 Record Queries 🌸

flask_sqlalchemy.record_queries.get_recorded_queries()

Get the list of recorded query information for the current session. Queries are recorded if the config SQLALCHEMY_RECORD_QUERIES is enabled. it's giving "receipts for every query you ran" 🧾✨

Each query info object has the following attributes:

  • statement — The string of SQL generated by SQLAlchemy with parameter placeholders.
  • parameters — The parameters sent with the SQL statement.
  • start_time / end_time — Timing info about when the query started execution and when the results where returned. Accuracy and value depends on the operating system. (yes, where — that's the original's typo, we keep the facts 💅)
  • duration — The time the query took in seconds.
  • location — A string description of where in your application code the query was executed. This may not be possible to calculate, and the format is not stable. (don't build your whole app on parsing this string, i'm begging 🙈)

Changed in version 3.0: Renamed from get_debug_queries.

Changed in version 3.0: The info object is a dataclass instead of a tuple.

Changed in version 3.0: The info object attribute context is renamed to location.

Changed in version 3.0: Not enabled automatically in debug or testing mode.

Return type: list[_QueryInfo]

🎀 Track Modifications 🎀

flask_sqlalchemy.track_modifications.models_committed

This Blinker signal is sent after the session is committed if there were changed models in the session. so like… the "okay we actually saved stuff" ping 🔥 only fires when something actually changed, no fake-outs.

The sender is the application that emitted the changes 👑, and the receiver gets passed the changes argument — a list of tuples shaped exactly like (instance, operation). The operations are "insert", "update", and "delete". Periodt, that's the whole vocabulary 🎀.

  • flask_sqlalchemy.track_modifications.before_models_committed — literally the same vibe as models_committed, except it fires before the commit takes place. Same signal, earlier timestamp 💅✨.

🦋 ADDITIONAL INFORMATION 🦋

🌸 BSD-3-Clause License 🌸

Copyright 2010 Pallets. Bestie, this is the boring-but-load-bearing part, so here's the whole thing verbatim — no edits, no vibes, just facts 🙈:

Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions are
met:

1. Redistributions of source code must retain the above copyright
   notice, this list of conditions and the following disclaimer.

2. Redistributions in binary form must reproduce the above copyright
   notice, this list of conditions and the following disclaimer in the
   documentation and/or other materials provided with the distribution.

3. Neither the name of the copyright holder nor the names of its
   contributors may be used to endorse or promote products derived from
   this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS
IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A
PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED
TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR
PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

📜 Changes 📜

💖 Version 3.1.1 💖

Released 2023-09-11

⭐ Version 3.1.0 ⭐

Released 2023-09-11

🎀 Version 3.0.5 🎀

Released 2023-06-21

🌈 Version 3.0.4 🌈

Released 2023-06-19

🧚 Version 3.0.3 🧚

Released 2023-01-31

💜 Version 3.0.2 💜

Released 2022-10-14

🍓 Version 3.0.1 🍓

Released 2022-10-11

👑 Version 3.0.0 👑

Released 2022-10-04

This one's the main character release, ngl — huge glow-up, tons of breaking changes, read carefully 😭

Version 2.5.1

Released 2021-03-18

  • Fix compatibility with Python 2.7. 🐍

Version 2.5.0

Released 2021-03-18

Version 2.4.4

Released 2020-07-14

Version 2.4.3

Released 2020-05-26

Version 2.4.2

Released 2020-05-25

Version 2.4.1

Released 2019-09-24

Version 2.4.0

Released 2019-04-24

Version 2.3.2

Released 2017-10-11

Version 2.3.1

Released 2017-10-05

Version 2.3.0

Released 2017-09-28

Version 2.2

Released 2017-02-27, codename Dubnium

Version 2.1

Released 2015-10-23, codename Caesium

  • Table names are automatically generated in more cases, including subclassing mixins and abstract models. 🦋
  • Allow using a custom MetaData object.
  • Add support for binds parameter to session.

Version 2.0

Released 2014-08-29, codename Bohrium

  • Changed how the builtin signals are subscribed to skip non-Flask-SQLAlchemy sessions. This will also fix the AttributeError about model changes not existing. ⭐
  • Added a way to control how signals for model modifications are tracked.
  • Made the SignallingSession a public interface and added a hook for customizing session creation.
  • If the bind parameter is given to the signalling session it will no longer cause an error that a parameter is given twice.
  • Added working table reflection support.
  • Enabled autoflush by default.
  • Consider SQLALCHEMY_COMMIT_ON_TEARDOWN harmful and remove from docs.

Version 1.0

Released 2013-07-20, codename Aurum

  • Added Python 3.3 support. 👑
  • Dropped Python 2.5 compatibility.
  • Various bugfixes.
  • Changed versioning format to do major releases for each update now.

Version 0.16

  • New distribution format (flask_sqlalchemy).
  • Added support for Flask 0.9 specifics.

Version 0.15

  • Added session support for multiple databases.

Version 0.14

  • Make relative sqlite paths relative to the application root.

Version 0.13

  • Fixed an issue with Flask-SQLAlchemy not selecting the correct binds.

Version 0.12

  • Added support for multiple databases.
  • Expose BaseQuery as db.Query.
  • Set default query_class for db.relation, db.relationship, and db.dynamic_loader to BaseQuery.
  • Improved compatibility with Flask 0.7.

Version 0.11

  • Fixed a bug introduced in 0.10 with alternative table constructors.

Version 0.10

  • Added support for signals.
  • Table names are now automatically set from the class name unless overridden.
  • Model.query now always works for applications directly passed to the SQLAlchemy constructor. Furthermore the property now raises a RuntimeError instead of being None.
  • Added session options to constructor.
  • Fixed a broken __repr__.
  • db.Table is now a factory function that creates table objects. This makes it possible to omit the metadata. ✨

Version 0.9

  • Applied changes to pass the Flask extension approval process.

Version 0.8

  • Added a few configuration keys for creating connections.
  • Automatically activate connection recycling for MySQL connections.
  • Added support for the Flask testing mode.

Version 0.7

  • Initial public release. 🎉 literally where it all began, baby!!

👑 Author

Pallets

2010 Pallets

and that's the whole page, bestie — you ate that entire read 💅 this was FLASK-SQLALCHEMY(1), living at version 3.1.x and dated Dec 15, 2025. Now go commit your session properly, mwah 🫶💖

📦 where this page comes from