flask-migrate/alembic - how to skip a specific sqlalchemy bind

Question

Is there any way to skip a bind using flask db migrate / alembic?

I have two repos which have SQLALCHEMY_BINDS which use a common database, but otherwise the databases are different. In my case members repo uses db members, SQLALCHEMY_BINDS={'users': usersdb_uri} and contracts repo uses db contracts, SQLALCHEMY_BINDS={'users': usersdb_uri}.

I want the members repo to handle migrations of the users db, and contracts repo to ignore it for database migration.

I'm trying to use flask-migrate to do the initial migration to add the users bind to the contracts repo, which requires some changes to the contracts db

In the contracts repo I tried modifying alembic's env.py to pop the users bind from SQLALCHEMY_BINDS

bind_names = []
# skip 'users' bind because this database migration is handled in https://github.com/louking/members
current_app.config['SQLALCHEMY_BINDS'].pop('users')
for bind in current_app.config.get("SQLALCHEMY_BINDS"):
    context.config.set_section_option(
        bind, "sqlalchemy.url",
        str(current_app.extensions['migrate'].db.get_engine(
            current_app, bind).url).replace('%', '%%'))
    bind_names.append(bind)

I see the following output from flask db migrate -m "common user database"

INFO  [alembic.env] Migrating database <default>
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'localinterest'
INFO  [alembic.autogenerate.compare] Detected added table 'localuser'
INFO  [alembic.autogenerate.compare] Detected removed table 'roles_users'
INFO  [alembic.autogenerate.compare] Detected removed index 'email' on 'user'
INFO  [alembic.autogenerate.compare] Detected removed table 'user'
INFO  [alembic.autogenerate.compare] Detected removed index 'name' on 'role'
INFO  [alembic.autogenerate.compare] Detected removed table 'role'
Generating C:\Users\lking\Documents\Lou's Software\projects\contracts\contracts\migrations\versions\cee4ca015898_common_user_database.py ...  done

This correctly skips the users bind, but in the revision file the upgrade() and downgrade() functions are empty.

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import mysql

# revision identifiers, used by Alembic.
revision = 'cee4ca015898'
down_revision = '321e28a8aa56'
branch_labels = None
depends_on = None


def upgrade():
    pass


def downgrade():
    pass

Edit to show error without pop()

(venv) C:\Users\lking\Documents\Lou's Software\projects\contracts\contracts>flask db migrate -m "common user database"
INFO  [alembic.env] Migrating database <default>
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'localinterest'
INFO  [alembic.autogenerate.compare] Detected added table 'localuser'
INFO  [alembic.autogenerate.compare] Detected removed index 'name' on 'role'
INFO  [alembic.autogenerate.compare] Detected removed table 'role'
INFO  [alembic.autogenerate.compare] Detected removed index 'email' on 'user'
INFO  [alembic.autogenerate.compare] Detected removed table 'user'
INFO  [alembic.autogenerate.compare] Detected removed table 'roles_users'
INFO  [alembic.env] Migrating database users
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
ERROR [flask_migrate] Error: Can't locate revision identified by 'cacdee34a411'

Also tried skipping migration

I also tried skipping users within the following code, but this also results in empty upgrade(), downgrade() functions.

        for name, rec in engines.items():
            # skip 'users' bind because this database migration is handled in https://github.com/louking/members
            if name == 'users': continue
            logger.info("Migrating database %s" % (name or '<default>'))
            context.configure(
                connection=rec['connection'],
                upgrade_token="%s_upgrades" % name,
                downgrade_token="%s_downgrades" % name,
                target_metadata=get_metadata(name),
                process_revision_directives=process_revision_directives,
                **current_app.extensions['migrate'].configure_args
            )
            context.run_migrations(engine_name=name)

score 0 · Answer 1 · answered Mar 31 '22 at 10:42

0

The pop() call that you are doing is messing up your Flask configuration. Instead of going at it this way, I recommend that you use the include_object from Alembic to have it skip the tables you don't want to migrate.

answered Mar 31 '22 at 10:42

Miguel Grinberg

65,299
14
133
152

One problem is I want to delete a couple of tables in the contracts db which have the same names as tables in the common users db. I don't see any way to check the bind as part of the include object, but maybe I'm missing something. I'd rather do it by bind rather than table name, in case the users db changes in the future. – Lou K Mar 31 '22 at 10:54
The other, more important issue is that if I allow the users bind to be included, since the users database revisions have been managed within the members repo, the alembic revision number can't be found from the contracts repo. I've updated the OP with the error I see when trying that. – Lou K Mar 31 '22 at 11:03
I also tried skipping migration in various places, example added to original post. – Lou K Mar 31 '22 at 14:34

Lou K · Accepted Answer · 2022-03-31T18:55:30.437

It turns out the problem was because I skipped a step. Rather than recreating env.py for multidb (it was previously created for single db), I copied env.py from the members repo.

However, I neglected to copy script.py.mako. When I copied script.py.mako, the revision file was created correctly, and flask db upgrade also works properly.

This is with

bind_names = []
# skip 'users' bind because this database migration is handled in https://github.com/louking/members
current_app.config['SQLALCHEMY_BINDS'].pop('users')
for bind in current_app.config.get("SQLALCHEMY_BINDS"):
    context.config.set_section_option(
        bind, "sqlalchemy.url",
        str(current_app.extensions['migrate'].db.get_engine(
            current_app, bind).url).replace('%', '%%'))
    bind_names.append(bind)

Now I see in the revision file

"""common user database

Revision ID: 6f403f3025b2
Revises: 321e28a8aa56
Create Date: 2022-03-31 14:46:16.806041

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import mysql

# revision identifiers, used by Alembic.
revision = '6f403f3025b2'
down_revision = '321e28a8aa56'
branch_labels = None
depends_on = None


def upgrade(engine_name):
    globals()["upgrade_%s" % engine_name]()


def downgrade(engine_name):
    globals()["downgrade_%s" % engine_name]()





def upgrade_():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('localinterest',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('interest_id', sa.Integer(), nullable=True),
    sa.Column('version_id', sa.Integer(), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('localuser',
    sa.Column('user_id', sa.Integer(), nullable=True),
    sa.Column('email', sa.String(length=100), nullable=True),
    sa.Column('name', sa.String(length=256), nullable=True),
    sa.Column('given_name', sa.String(length=256), nullable=True),
    sa.Column('active', sa.Boolean(), nullable=True),
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('interest_id', sa.Integer(), nullable=True),
    sa.Column('version_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['interest_id'], ['localinterest.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.drop_table('roles_users')
    op.drop_index('name', table_name='role')
    op.drop_table('role')
    op.drop_index('email', table_name='user')
    op.drop_table('user')
    # ### end Alembic commands ###


def downgrade_():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('user',
    sa.Column('id', mysql.INTEGER(display_width=11), autoincrement=True, nullable=False),
    sa.Column('email', mysql.VARCHAR(length=100), nullable=True),
    sa.Column('name', mysql.VARCHAR(length=256), nullable=True),
    sa.Column('given_name', mysql.VARCHAR(length=256), nullable=True),
    sa.Column('last_login_at', mysql.DATETIME(), nullable=True),
    sa.Column('current_login_at', mysql.DATETIME(), nullable=True),
    sa.Column('last_login_ip', mysql.VARCHAR(length=100), nullable=True),
    sa.Column('current_login_ip', mysql.VARCHAR(length=100), nullable=True),
    sa.Column('login_count', mysql.INTEGER(display_width=11), autoincrement=False, nullable=True),
    sa.Column('active', mysql.TINYINT(display_width=1), autoincrement=False, nullable=True),
    sa.Column('confirmed_at', mysql.DATETIME(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    mysql_default_charset='utf8',
    mysql_engine='InnoDB'
    )
    op.create_index('email', 'user', ['email'], unique=True)
    op.create_table('role',
    sa.Column('id', mysql.INTEGER(display_width=11), autoincrement=True, nullable=False),
    sa.Column('name', mysql.VARCHAR(length=32), nullable=True),
    sa.Column('description', mysql.VARCHAR(length=512), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    mysql_default_charset='utf8',
    mysql_engine='InnoDB'
    )
    op.create_index('name', 'role', ['name'], unique=True)
    op.create_table('roles_users',
    sa.Column('id', mysql.INTEGER(display_width=11), autoincrement=True, nullable=False),
    sa.Column('user_id', mysql.INTEGER(display_width=11), autoincrement=False, nullable=True),
    sa.Column('role_id', mysql.INTEGER(display_width=11), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['role_id'], ['role.id'], name='roles_users_ibfk_2'),
    sa.ForeignKeyConstraint(['user_id'], ['user.id'], name='roles_users_ibfk_1'),
    sa.PrimaryKeyConstraint('id'),
    mysql_default_charset='utf8',
    mysql_engine='InnoDB'
    )
    op.drop_table('localuser')
    op.drop_table('localinterest')
    # ### end Alembic commands ###

flask-migrate/alembic - how to skip a specific sqlalchemy bind

2 Answers2