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
```python3
"""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!