Alembic自动生成迁移未识别字段默认值变更问题求助

问题复现 #

1. 初始创建Users模型，执行alembic revision --autogenerate可正常生成迁移文件：

from main import Base
from sqlalchemy import Column, BigInteger, SmallInteger, String, Sequence, ForeignKey


class Users(Base):
    __tablename__ = "users"

    id = Column(BigInteger, Sequence("user_id_seq"), primary_key=True)
    first_name = Column(String(50))
    last_name = Column(String(50))
    email = Column(String(255))
    password = Column(String(60), nullable=True)

2. 添加UserTypes表及Users表的user_type外键字段后，自动生成迁移文件也正常：

from main import Base
from sqlalchemy import Column, BigInteger, SmallInteger, String, Sequence, ForeignKey


class Users(Base):
    __tablename__ = "users"

    id = Column(BigInteger, Sequence("user_id_seq"), primary_key=True)
    first_name = Column(String(50))
    last_name = Column(String(50))
    email = Column(String(255))
    password = Column(String(60), nullable=True)
    user_type = Column(SmallInteger, ForeignKey("user_types.id", name="fk_user_type"))


class UserTypes(Base):
    __tablename__ = "user_types"

    id = Column(SmallInteger, Sequence("user_types_id_seq"), primary_key=True)
    type = Column(String(20))

3. 为Users表的user_type字段添加default=1后，自动生成的迁移文件为空：

# 修改后的字段定义
user_type = Column(SmallInteger, ForeignKey("user_types.id", name="fk_user_type"), default=1)

生成的空迁移文件：

"""Made Default Value Of user_type 1

Revision ID: 054b79123431
Revises: 84bc1adb3e66
Create Date: 2022-12-28 17:20:06.757224

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '054b79123431'
down_revision = '84bc1adb3e66'
branch_labels = None
depends_on = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    pass
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    pass
    # ### end Alembic commands ###

已尝试在env.py的离线/在线迁移函数的context.configure中添加compare_server_default=True，但问题未解决。

解决方案 #

1. 区分ORM默认值与数据库默认值 #

Alembic的自动迁移只会检测数据库层面的默认值（server_default），你设置的default是Python ORM层面的默认值，仅在代码创建对象时生效，不会同步到数据库表结构中，因此Alembic无法识别该变化。

2. 修改模型字段定义 #

将default替换为server_default，并使用SQLAlchemy的sa.text()包裹默认值（确保数据库能解析该表达式）：

user_type = Column(SmallInteger, ForeignKey("user_types.id", name="fk_user_type"), server_default=sa.text("1"))

3. 确认env.py配置 #

确保env.py中的迁移配置已开启compare_server_default=True：

# 在run_migrations_online函数中
def run_migrations_online():
    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,
            compare_server_default=True  # 确保该参数存在
        )

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

# 在run_migrations_offline函数中
def run_migrations_offline():
    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url,
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
        compare_server_default=True  # 确保该参数存在
    )

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

4. 重新生成迁移文件 #

执行命令：

alembic revision --autogenerate -m "Add server default for user_type"

此时会生成包含字段默认值修改的迁移文件，示例：

def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('users', 'user_type',
               existing_type=sa.SMALLINT(),
               server_default=sa.text('1'),
               existing_nullable=True)
    # ### end Alembic commands ###

def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('users', 'user_type',
               existing_type=sa.SMALLINT(),
               server_default=None,
               existing_nullable=True)
    # ### end Alembic commands ###

内容的提问来源于stack exchange，提问作者Tushar Vaswani