search

sqlalchemy / alembic

A database migrations tool for SQLAlchemy.
MIT License
2.91k stars 250 forks source link

Autogenerate Tables With Foreign Keys In Wrong Order #1059

Closed ibraheemalayan closed 2 years ago

ibraheemalayan commented 2 years ago

Describe the bug auto generating migration scripts returns a script that doesn't work. when upgrading the database I get undefined table error.

Expected behavior the autogenerated script should order the table creation commands in the correct dependency order

Code

I removed a lot of unrelated columns and tables from here because the code should not be public

models.py


class Store(db.Model):

    __tablename__ = "store"

    id: int = Column(Integer, primary_key=True)

    logo_image_id = Column(Integer, ForeignKey("image.id", name="store_logo_image"))
    logo_image: S3Image = relationship(
        S3Image,
        primaryjoin= logo_image_id == S3Image.id
    )

    main_category_id = Column(Integer, ForeignKey("category.id"))

    images: List[S3Image] = relationship(
        S3Image,
        lazy="select",
        backref="store",
        cascade="all, delete-orphan",
        primaryjoin = id == S3Image.store_id,
        post_update=True
    )

class S3Image(db.Model):
    __tablename__ = "image"

    id: int = Column(Integer, primary_key=True)

    alt: str = Column(String(64), nullable=True)

    extension: str = Column(String(6))  # TODO validate this

    is_uploaded: bool = Column(Boolean, default=False)

    date_uploaded: datetime = Column(DateTime(), default=datetime.utcnow, index=True)

    version_id: str = Column(String(40))

    store_id = Column(Integer, ForeignKey("store.id"), index=True) # TODO shared images (for template images)

class Category(db.Model):

    __tablename__ = "category"

    id: int = Column(Integer, primary_key=True)

    name: str = Column(String(32))

    image_id = Column(Integer, ForeignKey("image.id", name="category_image"))

    category_image: S3Image = relationship(
        S3Image,
        primaryjoin=image_id == S3Image.id
    )

    sub_categories = relationship(
        "Category",
        backref=backref(
            "parent_category", lazy="joined", remote_side=[id]
        ),  # TODO check if needed else make it select
        lazy="select",  # load on access
        cascade="all, delete-orphan",
    )  # TODO delete ? maybe move

    # must be 0 for main categories
    parent_category_id = Column(
        Integer, ForeignKey("category.id"), default=None
    )

migration_script.py

"""initial migration

Revision ID: 14ad395059c3
Revises: 
Create Date: 2022-07-07 16:10:09.109942

"""
from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision = '14ad395059c3'
down_revision = None
branch_labels = None
depends_on = None

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###

    # removed unrelated tables and columns from this issue on github only (nothing was manually changed in the migration script)

    #.
    #.
    #.
    #.
    #.
    #.

    op.create_table('category',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=32), nullable=True),
    sa.Column('image_id', sa.Integer(), nullable=True),
    sa.Column('parent_category_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['image_id'], ['image.id'], name='category_image'),
    sa.ForeignKeyConstraint(['parent_category_id'], ['category.id'], ),
    sa.PrimaryKeyConstraint('id')
    )

    #.
    #.
    #.
    #.
    #.
    #.

    op.create_table('image',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('alt', sa.String(length=64), nullable=True),
    sa.Column('extension', sa.String(length=6), nullable=True),
    sa.Column('is_uploaded', sa.Boolean(), nullable=True),
    sa.Column('date_uploaded', sa.DateTime(), nullable=True),
    sa.Column('version_id', sa.String(length=40), nullable=True),
    sa.Column('silal_owned', sa.Boolean(), nullable=True),
    sa.Column('store_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['store_id'], ['store.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_image_date_uploaded'), 'image', ['date_uploaded'], unique=False)
    op.create_index(op.f('ix_image_store_id'), 'image', ['store_id'], unique=False)

    #.
    #.
    #.
    #.
    #.
    #.

    op.create_table('store',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=64), nullable=True),
    sa.Column('logo_image_id', sa.Integer(), nullable=True),
    sa.Column('main_category_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['logo_image_id'], ['image.id'], name='store_logo_image'),
    sa.ForeignKeyConstraint(['main_category_id'], ['category.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('name')
    )

    #.
    #.
    #.
    #.
    #.
    #.

Error

└──╼ $ flask db upgrade
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 14ad395059c3, initial migration
Traceback (most recent call last):
  File "path/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
    self.dialect.do_execute(
  File "path/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.UndefinedTable: relation "image" does not exist

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "path/venv/bin/flask", line 8, in <module>
    sys.exit(main())
  File "path/venv/lib/python3.9/site-packages/flask/cli.py", line 995, in main
    cli.main(args=sys.argv[1:])
  File "path/venv/lib/python3.9/site-packages/flask/cli.py", line 601, in main
    return super().main(*args, **kwargs)
  File "path/venv/lib/python3.9/site-packages/click/core.py", line 1053, in main
    rv = self.invoke(ctx)
  File "path/venv/lib/python3.9/site-packages/click/core.py", line 1659, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "path/venv/lib/python3.9/site-packages/click/core.py", line 1659, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "path/venv/lib/python3.9/site-packages/click/core.py", line 1395, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "path/venv/lib/python3.9/site-packages/click/core.py", line 754, in invoke
    return __callback(*args, **kwargs)
  File "path/venv/lib/python3.9/site-packages/click/decorators.py", line 26, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "path/venv/lib/python3.9/site-packages/flask/cli.py", line 445, in decorator
    return __ctx.invoke(f, *args, **kwargs)
  File "path/venv/lib/python3.9/site-packages/click/core.py", line 754, in invoke
    return __callback(*args, **kwargs)
  File "path/venv/lib/python3.9/site-packages/flask_migrate/cli.py", line 149, in upgrade
    _upgrade(directory, revision, sql, tag, x_arg)
  File "path/venv/lib/python3.9/site-packages/flask_migrate/__init__.py", line 98, in wrapped
    f(*args, **kwargs)
  File "path/venv/lib/python3.9/site-packages/flask_migrate/__init__.py", line 185, in upgrade
    command.upgrade(config, revision, sql=sql, tag=tag)
  File "path/venv/lib/python3.9/site-packages/alembic/command.py", line 320, in upgrade
    script.run_env()
  File "path/venv/lib/python3.9/site-packages/alembic/script/base.py", line 563, in run_env
    util.load_python_file(self.dir, "env.py")
  File "path/venv/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 92, in load_python_file
    module = load_module_py(module_id, path)
  File "path/venv/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 108, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
  File "<frozen importlib._bootstrap_external>", line 850, in exec_module
  File "<frozen importlib._bootstrap>", line 228, in _call_with_frames_removed
  File "migrations/env.py", line 91, in <module>
    run_migrations_online()
  File "migrations/env.py", line 85, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "path/venv/lib/python3.9/site-packages/alembic/runtime/environment.py", line 851, in run_migrations
    self.get_context().run_migrations(**kw)
  File "path/venv/lib/python3.9/site-packages/alembic/runtime/migration.py", line 620, in run_migrations
    step.migration_fn(**kw)
  File "/Users/ibraheemalyan/Desktop/ws/silal/web_backend/src/migrations/versions/14ad395059c3_initial_migration.py", line 31, in upgrade
    op.create_table('category',
  File "<string>", line 8, in create_table
  File "<string>", line 3, in create_table
  File "path/venv/lib/python3.9/site-packages/alembic/operations/ops.py", line 1254, in create_table
    return operations.invoke(op)
  File "path/venv/lib/python3.9/site-packages/alembic/operations/base.py", line 392, in invoke
    return fn(self, operation)
  File "path/venv/lib/python3.9/site-packages/alembic/operations/toimpl.py", line 114, in create_table
    operations.impl.create_table(table)
  File "path/venv/lib/python3.9/site-packages/alembic/ddl/impl.py", line 352, in create_table
    self._exec(schema.CreateTable(table))
  File "path/venv/lib/python3.9/site-packages/alembic/ddl/impl.py", line 193, in _exec
    return conn.execute(construct, multiparams)
  File "path/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1289, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "path/venv/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py", line 80, in _execute_on_connection
    return connection._execute_ddl(
  File "path/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1381, in _execute_ddl
    ret = self._execute_context(
  File "path/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1845, in _execute_context
    self._handle_dbapi_exception(
  File "path/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2026, in _handle_dbapi_exception
    util.raise_(
  File "path/venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "path/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
    self.dialect.do_execute(
  File "path/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) relation "image" does not exist

[SQL:
CREATE TABLE category (
    id SERIAL NOT NULL,
    name VARCHAR(32),
    image_id INTEGER,
    parent_category_id INTEGER,
    PRIMARY KEY (id),
    CONSTRAINT category_image FOREIGN KEY(image_id) REFERENCES image (id),
    FOREIGN KEY(parent_category_id) REFERENCES category (id)
)

]
(Background on this error at: https://sqlalche.me/e/14/f405)

You can notice that the table image has to be created before the table category to resolve the issue

Versions.

zzzeek commented 2 years ago

hi this is a known issue in #326 . There's no fix planned at this time so you will need to manually alter the CREATE TABLE statements to add the foreign key constraints afterwards using op.create_foreign_key() in your migration scripts.