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