Flask-SQLAlchemy

Flask-SQLAlchemy is an extension for Flask that adds support for SQLAlchemy. It simplifies the integration by setting up common objects and patterns, such as a session tied to each web request, models, and engines.

Note that Flask-SQLAlchemy does not change how SQLAlchemy works; it is a wrapper that facilitates its use within the Flask ecosystem. For in-depth ORM usage, refer to the official SQLAlchemy documentation.

When testing database models or logic directly (without making HTTP requests via the Flask test client), you must manually push an application context.

Best Practice: Only push a context for the specific duration needed for each test. Avoid pushing a global application context for all tests, as this can interfere with proper session cleanup.

For repetitive testing patterns, you can use a pytest fixture to manage the context lifecycle.

# Manual context in a test
def test_user_model(app):
    user = User()
    with app.app_context():
        db.session.add(user)
        db.session.commit()

# Using a pytest fixture for context management
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()

Before reporting an issue, ensure it is a bug in Flask-SQLAlchemy and not SQLAlchemy by checking the traceback. When reporting, include:

• A description of expected vs. actual behavior.
• A minimal reproducible example.
• The full traceback if an exception occurred.
• Your Python, Flask-SQLAlchemy, and SQLAlchemy versions.

Flask-SQLAlchemy adds a query object to each model as a shortcut for db.session.query(Model).

Warning: The query interface is considered legacy in SQLAlchemy. It is recommended to use session.execute(select(...)) instead for modern SQLAlchemy usage.

# get the user with id 5
user = User.query.get(5)

# get a user by username
user = User.query.filter_by(username=username).one()

The Flaskr example is a basic blog application based on the official Flask tutorial, modified to use Flask-SQLAlchemy instead of plain SQL. To set it up, clone the repository, navigate to the example directory, and checkout the appropriate version tag.

Note: Ensure you use the same version of the code as the documentation you are following. If you are using the main branch, you must install Flask-SQLAlchemy from source before installing Flaskr.

# clone the repository
$ git clone https://github.com/pallets/flask-sqlalchemy
$ cd flask-sqlalchemy/examples/flaskr
# checkout the correct version
$ git checkout correct-version-tag

# If using the main branch, install Flask-SQLAlchemy from source first:
$ pip install -e ../..
$ pip install -e .

Connect the extension to your Flask application using db.init_app(app). You must provide the SQLALCHEMY_DATABASE_URI configuration key, which is the connection string for your database.

# 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)

Install or update the latest version of Flask-SQLAlchemy using pip.

$ pip install -U Flask-SQLAlchemy

To run the Flaskr application, set the FLASK_APP and FLASK_ENV environment variables, initialize the database using the flask init-db command, and then start the server with flask run.

Once running, the application is accessible at http://127.0.0.1:5000.

# On Linux/macOS
$ export FLASK_APP=flaskr
$ export FLASK_ENV=development
$ flask init-db
$ flask run

# On Windows cmd
> set FLASK_APP=flaskr
> set FLASK_ENV=development
> flask init-db
> flask run

Build the project documentation using Sphinx located in the docs directory.

$ cd docs
$ make html
# Open _build/html/index.html in your browser

Do not use the issue tracker for general support questions. Instead, use:

• Discord: The #get-help channel.
• Mailing List: flask@python.org for long-term or large-scale discussions.
• Stack Overflow: Search using site:stackoverflow.com flask-sqlalchemy {search term}.

When using multiple engines, configuration follows these precedence rules:

1. engine_options passed to the SQLAlchemy constructor sets defaults for all engines.
2. SQLALCHEMY_ECHO sets the default for both echo and echo_pool for all engines.
3. Options defined in SQLALCHEMY_BINDS override the defaults from step 1.
4. SQLALCHEMY_ENGINE_OPTIONS overrides the None key in SQLALCHEMY_BINDS.
5. SQLALCHEMY_DATABASE_URI overrides the url key in the default engine's options.