I have quite a convoluted database schema that I'm hoping to version using Alembic. I am using a declarative_base to create my models, and my env.py looks like:
from logging.config import fileConfig
import os
import sys
from alembic import context
from sqlalchemy import engine_from_config, pool
sys.path.append(os.getcwd())
from app import db, create_app, Base
config = context.config
fileConfig(config.config_file_name)
app = create_app()
config.set_main_option("sqlalchemy.url", app.config["SQLALCHEMY_DATABASE_URI"])
target_metadata = Base.metadata
def run_migrations_offline():
"""Run migrations in 'offline' mode.
This configures the context with just a URL
and not an Engine, though an Engine is acceptable
here as well. By skipping the Engine creation
we don't even need a DBAPI to be available.
Calls to context.execute() here emit the given string to the
script output.
"""
url = config.get_main_option("sqlalchemy.url")
context.configure(url=url)
with context.begin_transaction():
context.run_migrations()
def run_migrations_online():
"""Run migrations in 'online' mode.
In this scenario we need to create an Engine
and associate a connection with the context.
"""
engine = engine_from_config(
config.get_section(config.config_ini_section),
prefix='sqlalchemy.',
poolclass=pool.NullPool
)
connection = engine.connect()
context.configure(connection=connection, target_metadata=target_metadata)
try:
with context.begin_transaction():
context.run_migrations()
finally:
connection.close()
if context.is_offline_mode():
run_migrations_offline()
else:
run_migrations_online()
When I run the following:
alembic revision --autogenerate -m ''
To autogenerate the schema: It detects the correct tables:
/limbus/venv/lib/python3.6/site-packages/sqlalchemy/orm/relationships.py:1917: SAWarning: Setting backref / back_populates on relationship UserAccountVersion.version_parent to refer to viewonly relationship UserAccount.versions will be deprecated in SQLAlchemy 1.4, and will be disallowed in a future release. viewonly relationships should not be mutated (this warning may be suppressed after 10 occurrences)
(self, other),
/limbus/venv/lib/python3.6/site-packages/sqlalchemy/orm/relationships.py:1917: SAWarning: Setting backref / back_populates on relationship UserAccount.versions to refer to viewonly relationship UserAccountVersion.version_parent will be deprecated in SQLAlchemy 1.4, and will be disallowed in a future release. viewonly relationships should not be mutated (this warning may be suppressed after 10 occurrences)
(self, other),
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
INFO [alembic.autogenerate.compare] Detected added table 'address'
INFO [alembic.autogenerate.compare] Detected added table 'siteinformation'
INFO [alembic.autogenerate.compare] Detected added table 'useraccount'
INFO [alembic.autogenerate.compare] Detected added table 'useraccount_version'
INFO [alembic.autogenerate.compare] Detected added index 'ix_useraccount_version_end_transaction_id' on '['end_transaction_id']'
INFO [alembic.autogenerate.compare] Detected added index 'ix_useraccount_version_operation_type' on '['operation_type']'
INFO [alembic.autogenerate.compare] Detected added index 'ix_useraccount_version_transaction_id' on '['transaction_id']'
INFO [alembic.autogenerate.compare] Detected added table 'useraccounttoken'
Generating /limbus/migrations/versions/9aa6e8b6781c_.py ... done
However, when I attempt to upgrade - it complains about the useraccount table not existing. If I take a look at the autogenerated version file:
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('address',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('created_on', sa.DateTime(), server_default=sa.text('now()'), nullable=False),
sa.Column('updated_on', sa.DateTime(), server_default=sa.text('now()'), nullable=False),
sa.Column('street_address_one', sa.String(length=256), nullable=False),
sa.Column('street_address_two', sa.String(length=256), nullable=True),
sa.Column('city', sa.String(length=128), nullable=False),
sa.Column('county', sa.String(length=128), nullable=True),
sa.Column('post_code', sa.String(length=20), nullable=False),
sa.Column('country', sa.String(length=2), nullable=False),
sa.Column('author_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['author_id'], ['useraccount.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_table('siteinformation',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('created_on', sa.DateTime(), server_default=sa.text('now()'), nullable=False),
sa.Column('updated_on', sa.DateTime(), server_default=sa.text('now()'), nullable=False),
sa.Column('miabis_id', sa.String(length=128), nullable=True),
sa.Column('acronym', sa.String(length=64), nullable=True),
sa.Column('name', sa.String(length=128), nullable=True),
sa.Column('description', sa.String(length=128), nullable=True),
sa.Column('url', sa.String(length=128), nullable=True),
sa.Column('address_id', sa.Integer(), nullable=False),
sa.Column('author_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['address_id'], ['address.id'], ),
sa.ForeignKeyConstraint(['author_id'], ['useraccount.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_table('useraccount',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('created_on', sa.DateTime(), server_default=sa.text('now()'), nullable=False),
sa.Column('updated_on', sa.DateTime(), server_default=sa.text('now()'), nullable=False),
sa.Column('email', sa.String(length=320), nullable=False),
sa.Column('password_hash', sa.String(length=256), nullable=False),
sa.Column('title', sa.Enum('MRS', 'MISS', 'MS', 'MR', 'MX', 'PROF', 'DR', name='title'), nullable=False),
sa.Column('first_name', sa.String(length=128), nullable=False),
sa.Column('middle_name', sa.String(length=128), nullable=True),
sa.Column('last_name', sa.String(length=128), nullable=False),
sa.Column('account_type', sa.Enum('ADM', 'BIO', 'PRO', 'BOT', name='accounttype'), nullable=False),
sa.Column('access_control', sa.Enum('ADM', 'MOD', 'PRI', 'VIE', 'BOT', name='accesscontrol'), nullable=True),
sa.Column('is_locked', sa.Boolean(), nullable=False),
sa.Column('site_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['site_id'], ['siteinformation.id'], ),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('email')
)
op.create_table('useraccount_version',
sa.Column('id', sa.Integer(), autoincrement=False, nullable=False),
sa.Column('created_on', sa.DateTime(), server_default=sa.text('now()'), autoincrement=False, nullable=True),
sa.Column('updated_on', sa.DateTime(), server_default=sa.text('now()'), autoincrement=False, nullable=True),
sa.Column('email', sa.String(length=320), autoincrement=False, nullable=True),
sa.Column('password_hash', sa.String(length=256), autoincrement=False, nullable=True),
sa.Column('title', sa.Enum('MRS', 'MISS', 'MS', 'MR', 'MX', 'PROF', 'DR', name='title'), autoincrement=False, nullable=True),
sa.Column('first_name', sa.String(length=128), autoincrement=False, nullable=True),
sa.Column('middle_name', sa.String(length=128), autoincrement=False, nullable=True),
sa.Column('last_name', sa.String(length=128), autoincrement=False, nullable=True),
sa.Column('account_type', sa.Enum('ADM', 'BIO', 'PRO', 'BOT', name='accounttype'), autoincrement=False, nullable=True),
sa.Column('access_control', sa.Enum('ADM', 'MOD', 'PRI', 'VIE', 'BOT', name='accesscontrol'), autoincrement=False, nullable=True),
sa.Column('is_locked', sa.Boolean(), autoincrement=False, nullable=True),
sa.Column('site_id', sa.Integer(), autoincrement=False, nullable=True),
sa.Column('transaction_id', sa.BigInteger(), autoincrement=False, nullable=False),
sa.Column('end_transaction_id', sa.BigInteger(), nullable=True),
sa.Column('operation_type', sa.SmallInteger(), nullable=False),
sa.Column('created_on_mod', sa.Boolean(), server_default=sa.text('false'), nullable=False),
sa.Column('updated_on_mod', sa.Boolean(), server_default=sa.text('false'), nullable=False),
sa.Column('email_mod', sa.Boolean(), server_default=sa.text('false'), nullable=False),
sa.Column('password_hash_mod', sa.Boolean(), server_default=sa.text('false'), nullable=False),
sa.Column('title_mod', sa.Boolean(), server_default=sa.text('false'), nullable=False),
sa.Column('first_name_mod', sa.Boolean(), server_default=sa.text('false'), nullable=False),
sa.Column('middle_name_mod', sa.Boolean(), server_default=sa.text('false'), nullable=False),
sa.Column('last_name_mod', sa.Boolean(), server_default=sa.text('false'), nullable=False),
sa.Column('account_type_mod', sa.Boolean(), server_default=sa.text('false'), nullable=False),
sa.Column('access_control_mod', sa.Boolean(), server_default=sa.text('false'), nullable=False),
sa.Column('is_locked_mod', sa.Boolean(), server_default=sa.text('false'), nullable=False),
sa.Column('site_id_mod', sa.Boolean(), server_default=sa.text('false'), nullable=False),
sa.PrimaryKeyConstraint('id', 'transaction_id')
)
op.create_index(op.f('ix_useraccount_version_end_transaction_id'), 'useraccount_version', ['end_transaction_id'], unique=False)
op.create_index(op.f('ix_useraccount_version_operation_type'), 'useraccount_version', ['operation_type'], unique=False)
op.create_index(op.f('ix_useraccount_version_transaction_id'), 'useraccount_version', ['transaction_id'], unique=False)
op.create_table('useraccounttoken',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('created_on', sa.DateTime(), server_default=sa.text('now()'), nullable=False),
sa.Column('updated_on', sa.DateTime(), server_default=sa.text('now()'), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('token_hash', sa.String(length=256), nullable=False),
sa.ForeignKeyConstraint(['user_id'], ['useraccount.id'], ),
sa.PrimaryKeyConstraint('id')
)
# ### end Alembic commands ###
It looks like useraccounts is being generated AFTER the FK dependency is being made between other classes.
I've searched high and low, but I don't know how I'd go about fixing this. Any help would be greatly appreciated.
Best wishes.
