SQLAlchemy Bundle¶

Integrates Flask SQLAlchemy Unchained and Flask Migrate with Flask Unchained.

Dependencies¶

Flask SQLAlchemy
Flask Migrate
SQLAlchemy
Alembic
Depending on your database of choice, you might also need a specific driver library.

Installation¶

Install dependencies:

pip install "flask-unchained[sqlalchemy]"

And enable the bundle in your unchained_config.py:

# your_project_root/unchained_config.py

BUNDLES = [
    # ...
    'flask_unchained.bundles.sqlalchemy',
    'app',
]

Config¶

class flask_unchained.bundles.sqlalchemy.config.Config[source]

The default configuration options for the SQLAlchemy Bundle.

SQLALCHEMY_DATABASE_URI = 'sqlite:///db/development.sqlite': The database URI that should be used for the connection. Defaults to using SQLite with the database file stored at ROOT_PATH/db/<env>.sqlite. See the SQLAlchemy Dialects documentation for more info.

SQLALCHEMY_TRANSACTION_ISOLATION_LEVEL = None

Set the engine-wide transaction isolation level.

See the docs for more info.

SQLALCHEMY_ECHO = False: If set to True SQLAlchemy will log all the statements issued to stderr which can be useful for debugging.

SQLALCHEMY_TRACK_MODIFICATIONS = False: If set to True, Flask-SQLAlchemy will track modifications of objects and emit signals. The default is False. This requires extra memory and should be disabled if not needed.

SQLALCHEMY_RECORD_QUERIES = None: Can be used to explicitly disable or enable query recording. Query recording automatically happens in debug or testing mode. See get_debug_queries() for more information.

SQLALCHEMY_BINDS = None: A dictionary that maps bind keys to SQLAlchemy connection URIs.

SQLALCHEMY_NATIVE_UNICODE = None: Can be used to explicitly disable native unicode support. This is required for some database adapters (like PostgreSQL on some Ubuntu versions) when used with improper database defaults that specify encoding-less databases.

SQLALCHEMY_POOL_SIZE = None: The size of the database pool. Defaults to the engine’s default (usually 5).

SQLALCHEMY_POOL_TIMEOUT = None: Specifies the connection timeout in seconds for the pool.

SQLALCHEMY_POOL_RECYCLE = None

Number of seconds after which a connection is automatically recycled. This is required for MySQL, which removes connections after 8 hours idle by default. Note that Flask-SQLAlchemy automatically sets this to 2 hours if MySQL is used.

Certain database backends may impose different inactive connection timeouts, which interferes with Flask-SQLAlchemy’s connection pooling.

By default, MariaDB is configured to have a 600 second timeout. This often surfaces hard to debug, production environment only exceptions like 2013: Lost connection to MySQL server during query.

If you are using a backend (or a pre-configured database-as-a-service) with a lower connection timeout, it is recommended that you set SQLALCHEMY_POOL_RECYCLE to a value less than your backend’s timeout.

SQLALCHEMY_MAX_OVERFLOW = None: Controls the number of connections that can be created after the pool reached its maximum size. When those additional connections are returned to the pool, they are disconnected and discarded.

SQLALCHEMY_COMMIT_ON_TEARDOWN = False: Whether or not to automatically commit on app context teardown. Defaults to False.

ALEMBIC = {'script_location': 'db/migrations'}: Used to set the directory where migrations are stored. ALEMBIC should be set to a dictionary, using the key script_location to set the directory. Defaults to ROOT_PATH/db/migrations.

ALEMBIC_CONTEXT = {'render_item': <function render_migration_item>, 'template_args': {'migration_variables': []}}: Extra kwargs to pass to the constructor of the Flask-Migrate extension. If you need to change this, make sure to merge the defaults with your settings!

class flask_unchained.bundles.sqlalchemy.config.TestConfig[source]

Default configuration options for testing.

SQLALCHEMY_DATABASE_URI = 'sqlite://': The database URI to use for testing. Defaults to SQLite in memory.

Usage¶

Usage of the SQLAlchemy bundle starts with an import:

from flask_unchained.bundles.sqlalchemy import db

From there, usage is extremely similar to stock Flask SQLAlchemy, and aside from a few minor gotchyas, you should just be able to copy your models and they should work (please file a bug report if this doesn’t work!). The gotchyas you should be aware of are:

the automatic table naming has slightly different behavior if the model class name has sequential upper-case characters
any models defined in not-app-bundles must declare themselves as class Meta: lazy_mapped = True
you must use back_populates instead of backref in relationship declarations (it may be possible to implement support for backrefs, but honestly, using back_populates is more Pythonic anyway, because Zen of Python)
many-to-many join tables must be declared as model classes

Furthermore, models in SQLAlchemy Unchained include three columns by default:

column name	description
`id`	the primary key
`created_at`	a timestamp of when the model got inserted into the database
`updated_at`	a timestamp of the last time the model was updated in the database

These are customizable by declaring meta options. For example to disable timestamping and to rename the primary key column to pk, it would look like this:

from flask_unchained.bundles.sqlalchemy import db


class Foo(db.Model):
    class Meta:
        pk = 'pk'
        created_at = None
        updated_at = None

Models support the following meta options:

meta option name	default	description
table	snake_cased model class name	The database tablename to use for this model.
pk	`'id'`	The name of the primary key column.
created_at	`'created_at'`	The name of the row creation timestamp column.
updated_at	`'updated_at'`	The name of the most recent row update timestamp column.
repr	`('id',)`	Column attributes to include in the automatic `__repr__`
validation	`True`	Whether or not to enable validation on the model for CRUD operations.
mv_for	`None`	Used for specifying the name of the model a `MaterializedView` is for, as a string.
relationships	`{}`	This is an automatically determined meta option, and is used for determining whether or not a model has the same relationships as its base model. This is useful when you want to override a model from a bundle but change its relationships. The code that determines this is rather experimental, and may not do the right thing. Please report any bugs you come across!

FIXME: Polymorphic Models

Commands¶

flask db¶

Perform database migrations.

flask db [OPTIONS] COMMAND [ARGS]...

branches¶

Show current branch points

flask db branches [OPTIONS]

Options

-d, --directory <directory>¶: Migration script directory (default is “migrations”)

-v, --verbose¶: Use more verbose output

current¶

Display the current revision for each database.

flask db current [OPTIONS]

Options

-d, --directory <directory>¶: Migration script directory (default is “migrations”)

-v, --verbose¶: Use more verbose output

downgrade¶

Revert to a previous version

flask db downgrade [OPTIONS] [REVISION]

Options

-d, --directory <directory>¶: Migration script directory (default is “migrations”)

--sql¶: Don’t emit SQL to database - dump to standard output instead

--tag <tag>¶: Arbitrary “tag” name - can be used by custom env.py scripts

-x, --x-arg <x_arg>¶: Additional arguments consumed by custom env.py scripts

Arguments

REVISION¶: Optional argument

drop¶

Drop database tables.

flask db drop [OPTIONS]

Options

--force¶

edit¶

Edit a revision file

flask db edit [OPTIONS] [REVISION]

Options

-d, --directory <directory>¶: Migration script directory (default is “migrations”)

Arguments

REVISION¶: Optional argument

heads¶

Show current available heads in the script directory

flask db heads [OPTIONS]

Options

-d, --directory <directory>¶: Migration script directory (default is “migrations”)

-v, --verbose¶: Use more verbose output

--resolve-dependencies¶: Treat dependency versions as down revisions

history¶

List changeset scripts in chronological order.

flask db history [OPTIONS]

Options

-d, --directory <directory>¶: Migration script directory (default is “migrations”)

-r, --rev-range <rev_range>¶: Specify a revision range; format is [start]:[end]

-v, --verbose¶: Use more verbose output

-i, --indicate-current¶: Indicate current version (Alembic 0.9.9 or greater is required)

init¶

Creates a new migration repository.

flask db init [OPTIONS]

Options

-d, --directory <directory>¶: Migration script directory (default is “migrations”)

--multidb¶: Support multiple databases

merge¶

Merge two revisions together, creating a new revision file

flask db merge [OPTIONS] [REVISIONS]...

Options

-d, --directory <directory>¶: Migration script directory (default is “migrations”)

-m, --message <message>¶: Merge revision message

--branch-label <branch_label>¶: Specify a branch label to apply to the new revision

--rev-id <rev_id>¶: Specify a hardcoded revision id instead of generating one

Arguments

REVISIONS¶: Optional argument(s)

migrate¶

Autogenerate a new revision file (Alias for ‘revision –autogenerate’)

flask db migrate [OPTIONS]

Options

-d, --directory <directory>¶: Migration script directory (default is “migrations”)

-m, --message <message>¶: Revision message

--sql¶: Don’t emit SQL to database - dump to standard output instead

--head <head>¶: Specify head revision or <branchname>@head to base new revision on

--splice¶: Allow a non-head revision as the “head” to splice onto

--branch-label <branch_label>¶: Specify a branch label to apply to the new revision

--version-path <version_path>¶: Specify specific path from config for version file

--rev-id <rev_id>¶: Specify a hardcoded revision id instead of generating one

-x, --x-arg <x_arg>¶: Additional arguments consumed by custom env.py scripts

reset¶

Drop database tables and run migrations.

flask db reset [OPTIONS]

Options

--force¶

revision¶

Create a new revision file.

flask db revision [OPTIONS]

Options

-d, --directory <directory>¶: Migration script directory (default is “migrations”)

-m, --message <message>¶: Revision message

--autogenerate¶: Populate revision script with candidate migration operations, based on comparison of database to model

--sql¶: Don’t emit SQL to database - dump to standard output instead

--head <head>¶: Specify head revision or <branchname>@head to base new revision on

--splice¶: Allow a non-head revision as the “head” to splice onto

--branch-label <branch_label>¶: Specify a branch label to apply to the new revision

--version-path <version_path>¶: Specify specific path from config for version file

--rev-id <rev_id>¶: Specify a hardcoded revision id instead of generating one

show¶

Show the revision denoted by the given symbol.

flask db show [OPTIONS] [REVISION]

Options

-d, --directory <directory>¶: Migration script directory (default is “migrations”)

Arguments

REVISION¶: Optional argument

stamp¶

‘stamp’ the revision table with the given revision; don’t run any migrations

flask db stamp [OPTIONS] [REVISION]

Options

-d, --directory <directory>¶: Migration script directory (default is “migrations”)

--sql¶: Don’t emit SQL to database - dump to standard output instead

--tag <tag>¶: Arbitrary “tag” name - can be used by custom env.py scripts

Arguments

REVISION¶: Optional argument

upgrade¶

Upgrade to a later version

flask db upgrade [OPTIONS] [REVISION]

Options

-d, --directory <directory>¶: Migration script directory (default is “migrations”)

--sql¶: Don’t emit SQL to database - dump to standard output instead

--tag <tag>¶: Arbitrary “tag” name - can be used by custom env.py scripts

-x, --x-arg <x_arg>¶: Additional arguments consumed by custom env.py scripts

Arguments

REVISION¶: Optional argument

API Docs¶

See SQLAlchemy Bundle API