Alembic attempts to recreate all tables in the Base class on every migration

Question

In my env.py I have set my target_metadata to Base.metadata which I import from models.py. I have a fresh database with a schema named basic that I want to use to create the tables and setup my models.py like this:

from datetime import datetime
from sqlalchemy import Column, DateTime, Integer, MetaData, String
from sqlalchemy.orm import declarative_base

Base = declarative_base(metadata=MetaData(schema='basic'))

class User(Base):
    __tablename__ = 'user'

    id =  Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    created_on = Column(DateTime, default=datetime.utcnow)

I run alembic revision --autogenerate -m"Create user model" and run alembic upgrade heads. Everything works as expected and I have table user in my database under the schema basic.

Now I want to add a table country. I add it to my models.py which now looks like this:

from datetime import datetime
from sqlalchemy import Column, DateTime, Integer, MetaData, String
from sqlalchemy.orm import declarative_base

Base = declarative_base(metadata=MetaData(schema='basic'))

class User(Base):
    __tablename__ = 'user'

    id =  Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    created_on = Column(DateTime, default=datetime.utcnow)

class Country(Base): 
    __tablename__ = 'country'

    id =  Column(Integer, primary_key=True)
    country = Column(String, nullable=False)
    created_on = Column(DateTime, default=datetime.utcnow)

I run alembic revision --autogenerate -m"Create country model" which creates a new versions file that looks like this:

"""Create country model

Revision ID: 0eef32919b0d
Revises: 2da4668d1069
Create Date: 2023-01-19 15:39:08.778274

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '0eef32919b0d'
down_revision = '2da4668d1069'
branch_labels = None
depends_on = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('country',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('country', sa.String(), nullable=False),
    sa.Column('created_on', sa.DateTime(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    schema='basic'
    )
    op.create_table('user',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(), nullable=False),
    sa.Column('created_on', sa.DateTime(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    schema='basic'
    )
    # ### end Alembic commands ###


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

Why does it also try to create the table user again? Running this will give an error that the object basic.user already exists. How can I fix this so that it looks at the current state of the db and only wants to create the table country?

Setting the option include_schemas=True (which is suggested in this thread: Alembic - sqlalchemy does not detect existing tables) helps but then includes all schemas and I only want it to be aware of this single schema.

Answer 1

I only want it to be aware of this single schema.

Then you also need to use include_name=, like so:

def run_migrations_online():

    # …

    def include_name(name, type_, parent_names):
        if type_ == "schema":
            # note this will not include the default schema
            return name in ["basic"]
        else:
            return True

    with connectable.connect() as connection:
        context.configure(
            connection=connection, target_metadata=target_metadata,
            include_schemas=True,
            include_name=include_name
        )