Alembic Autogenerate Creates New Table Scripts Instead of Adding Column to Existing Table

[TheShubhendra]

Describe the bug
When using Alembic's revision --autogenerate command to generate a migration script after adding a new column to an existing table, Alembic incorrectly generates a script that attempts to create new tables for all the models defined in the SQLAlchemy models file, instead of generating a migration script that simply adds the new column to the specified table. This behavior occurs even though the tables already exist in the database, and the only change made was the addition of a single column to one table.

Expected behavior
The expected behavior is for Alembic to generate a migration script that includes an operation to add the newly defined column to the existing table in the database. There should be no operations in the migration script related to creating new tables, especially since these tables already exist in the database and no changes have been made to them. The autogenerated script should only reflect the addition of the new column.
To Reproduce

1. Model before Update

import os

import pytz
from sqlalchemy import (Boolean, Column, DateTime, Integer, MetaData,
                        PrimaryKeyConstraint, String, Enum)
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.sql import func
from app.core.enums import ExerciseDeliveryMode

SCHEMA_NAME = os.environ["SCHEMA_NAME"]

timezone = pytz.timezone("Asia/Kolkata")


class Base(DeclarativeBase):
    metadata = MetaData(schema=SCHEMA_NAME)


class TimestampMixin(object):
    created_at = Column(DateTime, server_default=func.now(), nullable=False)
    updated_at = Column(
        DateTime,
        nullable=False,
    )


class QuestionAttempt(Base):
    __tablename__ = "question_attempt"
    id = Column(Integer, primary_key=True)
    question_id = Column(Integer, nullable=False)
    exercise_id = Column(Integer, nullable=False)
    student_id = Column(Integer, nullable=False)
    is_correct = Column(Boolean, nullable=False)
    timestamp = Column(DateTime, nullable=False, server_default=func.now())
    duration = Column(Integer, nullable=True)
    answer = Column(String(100), nullable=True)


class ExerciseProgress(Base):
    __tablename__ = "exercise_progress"
    student_id = Column(Integer)
    exercise_id = Column(Integer)
    questions_attempted = Column(Integer)
    questions_correct = Column(Integer)
    mastery_level = Column(Integer, nullable=False, server_default="0")
    last_attempted = Column(DateTime, nullable=False, server_default=func.now())
    mastery_achieved = Column(Boolean, nullable=False, server_default="false")
    mastery_achieved_at = Column(DateTime, nullable=True)

    # create composite primary key
    __table_args__ = (
        PrimaryKeyConstraint("student_id", "exercise_id"),
        {},
    )

2. Models after update

import os

import pytz
from sqlalchemy import (Boolean, Column, DateTime, Integer, MetaData,
                        PrimaryKeyConstraint, String, Enum)
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.sql import func
from app.core.enums import ExerciseDeliveryMode

SCHEMA_NAME = os.environ["SCHEMA_NAME"]

timezone = pytz.timezone("Asia/Kolkata")


class Base(DeclarativeBase):
    metadata = MetaData(schema=SCHEMA_NAME)


class TimestampMixin(object):
    created_at = Column(DateTime, server_default=func.now(), nullable=False)
    updated_at = Column(
        DateTime,
        nullable=False,
    )


class QuestionAttempt(Base):
    __tablename__ = "question_attempt"
    id = Column(Integer, primary_key=True)
    question_id = Column(Integer, nullable=False)
    exercise_id = Column(Integer, nullable=False)
    student_id = Column(Integer, nullable=False)
    is_correct = Column(Boolean, nullable=False)
    timestamp = Column(DateTime, nullable=False, server_default=func.now())
    duration = Column(Integer, nullable=True)
    answer = Column(String(100), nullable=True)


class ExerciseProgress(Base):
    __tablename__ = "exercise_progress"
    student_id = Column(Integer)
    exercise_id = Column(Integer)
    questions_attempted = Column(Integer)
    questions_correct = Column(Integer)
    mastery_level = Column(Integer, nullable=False, server_default="0")
    last_attempted = Column(DateTime, nullable=False, server_default=func.now())
    mastery_achieved = Column(Boolean, nullable=False, server_default="false")
    mastery_achieved_at = Column(DateTime, nullable=True)
    # Enum
    delivery_mode = Column(
        Enum(ExerciseDeliveryMode, name="exercise_delivery_mode"),
        nullable=False,
        server_default=ExerciseDeliveryMode.SIMPLE.value,
    )
    # create composite primary key
    __table_args__ = (
        PrimaryKeyConstraint("student_id", "exercise_id"),
        {},
    )

3. Generated migration script

"""Add delivery_mode column

Revision ID: a0db00ab8187
Revises: 3e9083e86083
Create Date: 2024-02-06 10:37:43.797500

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = 'a0db00ab8187'
down_revision: Union[str, None] = '3e9083e86083'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('exercise_progress',
    sa.Column('student_id', sa.Integer(), nullable=False),
    sa.Column('exercise_id', sa.Integer(), nullable=False),
    sa.Column('questions_attempted', sa.Integer(), nullable=True),
    sa.Column('questions_correct', sa.Integer(), nullable=True),
    sa.Column('mastery_level', sa.Integer(), server_default='0', nullable=False),
    sa.Column('last_attempted', sa.DateTime(), server_default=sa.text('now()'), nullable=False),
    sa.Column('mastery_achieved', sa.Boolean(), server_default='false', nullable=False),
    sa.Column('mastery_achieved_at', sa.DateTime(), nullable=True),
    sa.Column('delivery_mode', sa.Enum('SIMPLE', 'ADAPTIVE', name='exercise_delivery_mode'), server_default='simple', nullable=False),
    sa.PrimaryKeyConstraint('student_id', 'exercise_id'),
    schema='exercise'
    )
    op.create_table('question_attempt',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('question_id', sa.Integer(), nullable=False),
    sa.Column('exercise_id', sa.Integer(), nullable=False),
    sa.Column('student_id', sa.Integer(), nullable=False),
    sa.Column('is_correct', sa.Boolean(), nullable=False),
    sa.Column('timestamp', sa.DateTime(), server_default=sa.text('now()'), nullable=False),
    sa.Column('duration', sa.Integer(), nullable=True),
    sa.Column('answer', sa.String(length=100), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    schema='exercise'
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('question_attempt', schema='exercise')
    op.drop_table('exercise_progress', schema='exercise')
    # ### end Alembic commands ###

Versions.

• OS: MacOs Sonoma 14.0
• Python: 3.11.5
• Alembic: 1.12.0
• SQLAlchemy: sqlalchemy 2.0.21
• Database: PostgreSQL 16.1
• DBAPI: pyscopg2 66.0.2

Have a nice day!

[CaselIT]

Hi,

The most likely reason is that the scheme name in your env variable has changed.
If not can you provide an example your env.py too?

[TheShubhendra]

This is my env.py. I have defined DATABASE_URL and SCHEMA_NAME in alembic.ini and also passing as environment variable when running the command SCHEMA_NAME=schema alembic revision --autogenerate -m "Add delivery_mode column" for the models.py

from logging.config import fileConfig

from sqlalchemy import engine_from_config, pool

from alembic import context
from app.core.models import Base

# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config
# Interpret the config file for Python logging.
# This line sets up loggers basically.
if config.config_file_name is not None:
    fileConfig(config.config_file_name)

target_metadata = Base.metadata


# other values from the config, defined by the needs of env.py,
# can be acquired:
# my_important_option = config.get_main_option("my_important_option")
# ... etc.


def run_migrations_offline() -> None:
    """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,
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
    )

    with context.begin_transaction():
        context.run_migrations()


def run_migrations_online() -> None:
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
    connectable = engine_from_config(
        config.get_section(config.config_ini_section, {}),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            version_table_schema=config.get_main_option("schema_name"),
            include_schemas=True,
            include_name=lambda name, _type, _p: name
            == config.get_main_option("schema_name")
            or _type != "schema",
        )

        with context.begin_transaction():
            context.run_migrations()


if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

[CaselIT]

I don't see anything that jumps out. Did your first migration were you added the tables have the same schema schema='exercise' in the op.create_table call?

[TheShubhendra]

I don't see anything that jumps out. Did your first migration were you added the tables have the same schema schema='exercise' in the op.create_table call?

Yes

[TheShubhendra]

Today again , in another project first i created an initial migration script to create 3 tables then modified a string column in a table and now when i autogenerated the alembic generated the script to create all 3 tables again

[CaselIT]

That's definitely a strange behaviour. Can you provide a complete example maybe in a github repo?

[TheShubhendra]

Ok I will setup a repo to reproduce this.

[TheShubhendra]

https://github.com/TheShubhendra/alembic-migration-test-for-pgsql
@CaselIT You may reproduce it here. Thanks !

[CaselIT]

the env.py file is missing from that repository, but I'm assuming it's the default one generated by alembic.

You have to add include_schemas=True to context.configure since you are using schemas

@zzzeek it seems strange though that if include schema is not set the compare is not happening correctly

[kartikarcot]

I am also having the same problem. In my case i first create an empty revision file and stamp it as head (have an existing database). Then when I add a column and then autogenerate it detects all the tables in the schema and creates a script that wants to create all tables. However like you suggested include_schemas=True solves that problem for me. So I am confused why that is?

[killvont]

has anybody found a fix im also having the same issue I have been removing the migration folder and it seems to return back to the directory which keeps me from using these commands flask db migrate -m "Initial migration.", flask db upgrade in admin cmd it always returns with this error code. ERROR [flask_migrate] Error: Can't locate revision identified by '9ebf91bbcd22'

[douglaschalegre]

This info is store in the database on alembic_version.version_num