Question

Configuration Keys

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

flask_sqlalchemy.config.SQLALCHEMY_DATABASE_URI

See SQLAlchemy’s documentation on Engine Configuration 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.

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.

Using custom MetaData and naming conventions

You can optionally construct the SQLAlchemy object with a custom MetaData object. This allows you to specify a custom constraint naming convention. This makes constraint names consistent and predictable, useful when using migrations, as described by Alembic.

from sqlalchemy import MetaData
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy(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"
}))

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 for more information.