Alembic with default schema + other schemas always recreates table when just adding a column

[NicolasDijols]

Hello, i'm very new to Alembic and i'm trying to test it with a very simple project.

Describe the bug

My database(Postgis) has 3 schemas: public, topology and tiger.
My own tables are in the public schema.
Running a first migration that adds a table 'users' is going well but when i try to make a new migration to add a single column, the migration script creates the whole users table again (with the new column) instead of just add the column to the users table.
I found theses issues: #1282 and #1416. Mine seems to be a combination of both.

Following answers from these issues, I've added this in my env.py:

# To not take into account postgis extra schemas
def include_name(name, type_, parent_names):
    if type_ == "schema":
        return False
    else:
        return True

# ...

context.configure(
            connection=connection,
            target_metadata=target_metadata,
            include_name=include_name,
            include_schemas=True
        )

This is ok for not touching topology and tiger schemas when creating a new migration, but as i said the users table is recreated entirely.

If i remove the include_schemas and include_name arguments when calling context.configure, now the column is added correctly, without recreating the whole users table, but the migration script removes all tables in topology and tiger schemas.

Expected behavior
I would like to be able to have non default schema alongside the default one, add tables into the default schema without removing any tables from other schemas and when add or remove column from my own table, not recreate the whole table each time.

To Reproduce

The env.py is the one by default with these additions:

...
from models.models import Base
target_metadata = Base.metadata
...
def include_name(name, type_, parent_names):
    if type_ == "schema":
        return False
    else:
        return True

# ...
# in def run_migrations_online()
context.configure(
            connection=connection,
            target_metadata=target_metadata,
            include_name=include_name,
            include_schemas=True
        )

# models/models.py
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True, index=True)
    username = Column(String, unique=True, index=True)
    email = Column(String, unique=True, index=True)
    full_name = Column(String)
    hashed_password = Column(String)
    #phone_number = Column(String)  # Uncomment for second migration

After running alembic revision --autogenerate -m "Initial migration"

"""Initial migration

Revision ID: 5db9a59cd29e
Revises: 
Create Date: 2024-04-28 14:10:27.869431

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = '5db9a59cd29e'
down_revision: Union[str, None] = None
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('users',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('username', sa.String(), nullable=True),
    sa.Column('email', sa.String(), nullable=True),
    sa.Column('full_name', sa.String(), nullable=True),
    sa.Column('hashed_password', sa.String(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    schema='public'
    )
    op.create_index(op.f('ix_public_users_email'), 'users', ['email'], unique=True, schema='public')
    op.create_index(op.f('ix_public_users_id'), 'users', ['id'], unique=False, schema='public')
    op.create_index(op.f('ix_public_users_username'), 'users', ['username'], unique=True, schema='public')
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f('ix_public_users_username'), table_name='users', schema='public')
    op.drop_index(op.f('ix_public_users_id'), table_name='users', schema='public')
    op.drop_index(op.f('ix_public_users_email'), table_name='users', schema='public')
    op.drop_table('users', schema='public')
    # ### end Alembic commands ###

Uncomment the phone_number field in User from models.py
After running alembic revision --autogenerate -m "add phone number"

"""add phone number

Revision ID: 7b7fd4d8ed8b
Revises: 5db9a59cd29e
Create Date: 2024-04-28 14:11:16.712480

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = '7b7fd4d8ed8b'
down_revision: Union[str, None] = '5db9a59cd29e'
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('users',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('username', sa.String(), nullable=True),
    sa.Column('email', sa.String(), nullable=True),
    sa.Column('full_name', sa.String(), nullable=True),
    sa.Column('hashed_password', sa.String(), nullable=True),
    sa.Column('phone_number', sa.String(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    schema='public'
    )
    op.create_index(op.f('ix_public_users_email'), 'users', ['email'], unique=True, schema='public')
    op.create_index(op.f('ix_public_users_id'), 'users', ['id'], unique=False, schema='public')
    op.create_index(op.f('ix_public_users_username'), 'users', ['username'], unique=True, schema='public')
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f('ix_public_users_username'), table_name='users', schema='public')
    op.drop_index(op.f('ix_public_users_id'), table_name='users', schema='public')
    op.drop_index(op.f('ix_public_users_email'), table_name='users', schema='public')
    op.drop_table('users', schema='public')
    # ### end Alembic commands ###

Error
Not an error per se but compare should not detect these when autogenerate a second migration

INFO  [alembic.autogenerate.compare] Detected added table 'users'
INFO  [alembic.autogenerate.compare] Detected added index ''ix_users_email'' on '('email',)'
INFO  [alembic.autogenerate.compare] Detected added index ''ix_users_id'' on '('id',)'
INFO  [alembic.autogenerate.compare] Detected added index ''ix_users_username'' on '('username',)'

Versions.

• OS: OSX sonoma
• Python: 3.10.2
• Alembic: 1.13.1
• SQLAlchemy: 2.0.29
• Database: postgres 15.5 - postgis 3.4
• DBAPI: psycopg 2.9.9

Additional context

Have a nice day!

[obdura]

I have a similar problem. Did you find a solution?

[CaselIT]

Hi,

I believe that the include_name you are using is telling alembic to ignore any scheme, so the database is always reflected as empty.

From your description shouldn't you tell alembic to include the public (probably reflected with name Nome) schema?

[erfannariman]

Not sure if I understand @CaselIT , can someone elaborate what needs to change regarding OP's example?

[CaselIT]

the ignore_name provided by the user ignored all schema, this prevents alembic from reflecting anything.