Alembic detects existing changes if db username equals postgres schema name

[alexvas96]

Describe the bug

Alembic detects existing changes if db username equals postgres schema name.
Problem occurs only if parameter include_name is passed in env.py.

Expected behavior
Alembic generates empty migration.

To Reproduce

Project structure

.
├── alembic.ini
└── src
    ├── migrations
    │   ├── env.py
    │   ├── script.py.mako
    │   └── versions
    │       └── 20240318_2128_722179d9516c_initial.py
    └── models.py

File: src/models.py

from sqlalchemy import MetaData, Column, BigInteger, String, Identity
from sqlalchemy.orm import DeclarativeBase

metadata = MetaData(schema='example')


class Base(DeclarativeBase):
    metadata = metadata


class Customer(Base):
    __tablename__ = 'customers'

    id = Column(BigInteger, Identity(always=True), primary_key=True)
    name = Column(String)
    email = Column(String)

File: src/migrations/env.py

from logging.config import fileConfig

from sqlalchemy import engine_from_config
from sqlalchemy import pool

from alembic import context

from models import Base

config = context.config


if config.config_file_name is not None:
    fileConfig(config.config_file_name)

target_metadata = Base.metadata


def include_name(name, type_, parent_names):
    if type_ == 'schema':
        return name == target_metadata.schema

    return True


def run_migrations_offline() -> None:
    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url,
        target_metadata=target_metadata,
        literal_binds=True,
        include_name=include_name,
        include_schemas=True,
        dialect_opts={"paramstyle": "named"},
    )

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


def run_migrations_online() -> None:
    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,
            include_name=include_name,
            include_schemas=True,
        )

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


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

File: src/migrations/versions/20240318_2128_722179d9516c_initial.py

"""initial

Revision ID: 722179d9516c
Revises: 
Create Date: 2024-03-18 21:28:38.818940

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = '722179d9516c'
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:
    op.execute('CREATE SCHEMA example')
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('customers',
    sa.Column('id', sa.BigInteger(), sa.Identity(always=True), nullable=False),
    sa.Column('name', sa.String(), nullable=True),
    sa.Column('email', sa.String(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    schema='example'
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('customers', schema='example')
    # ### end Alembic commands ###
    op.execute('DROP SCHEMA example')

alembic.ini

[alembic]
script_location = src/migrations

file_template = %%(year)d%%(month).2d%%(day).2d_%%(hour).2d%%(minute).2d_%%(rev)s_%%(slug)s

prepend_sys_path = .

version_path_separator = os

sqlalchemy.url = postgresql://example:pass@localhost:5432/db

# Logging configuration
[loggers]
keys = root,sqlalchemy,alembic

[handlers]
keys = console

[formatters]
keys = generic

[logger_root]
level = WARN
handlers = console
qualname =

[logger_sqlalchemy]
level = WARN
handlers =
qualname = sqlalchemy.engine

[logger_alembic]
level = INFO
handlers =
qualname = alembic

[handler_console]
class = StreamHandler
args = (sys.stderr,)
level = NOTSET
formatter = generic

[formatter_generic]
format = %(levelname)-5.5s [%(name)s] %(message)s
datefmt = %H:%M:%S

sqlalchemy.url in alembic.ini: postgresql://example:pass@localhost:5432/db

alembic upgrade head
alembic revision --autogenerate -m "test"

Output:

INFO  [alembic.autogenerate.compare] Detected added table 'customers'

Autogenerated migration:

def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('customers',
    sa.Column('id', sa.BigInteger(), sa.Identity(always=True), nullable=False),
    sa.Column('name', sa.String(), nullable=True),
    sa.Column('email', sa.String(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    schema='example'
    )
    # ### end Alembic commands ###


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

If I change the username in DSN (postgresql://user:pass@localhost:5432/db) to something that is not equal to the schema name, I get an empty migration.

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 ###

Error
No errors.

Versions.

• OS: Ubuntu 22.04.4 LTS
• Python: Python 3.11.6
• Alembic: alembic 1.13.1
• SQLAlchemy: 2.0.28
• Database: PostgreSQL
• DBAPI: psycopg2-binary==2.9.9

Have a nice day!

[zzzeek]

hi -

log into your database with the working URL, then type this:

test=> select current_schema();
 current_schema 
----------------
 public
(1 row)

then log in to the database with a username that matches an existing schema name, try again, for me my username is "scott", so ill create a schema with that name (note that im not even disconnecting in my case):

test=> create schema scott;
CREATE SCHEMA
test=> select current_schema();
 current_schema 
----------------
 scott
(1 row)

it changed! this is because of a variable known as the search_path which defaults to this:

test=> show search_path;
   search_path   
-----------------
 "$user", public
(1 row)

the "public" name is the one we want, but note it also includes the name "$user", so that is in this case injecting the username you are logging in as as part of the search path. Because of that, the name "example" is officially your default schema and SQLAlchemy/alembic considers tables in the "example" schema to have no additional schema qualifier. your include_name function then has the default schema become ignored because you are only returning True for the explicit "example" schema which you will never see here.

solution: change search path to be "public" only, or use a different username from the schema name so that the "$user" variable is not being activated.

background in SQLAs docs are here:

https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path

Note that in all cases, the “default” schema is always reflected as None. The “default” schema on PostgreSQL is that which is returned by the PostgreSQL current_schema() function. On a typical PostgreSQL installation, this is the name public.

so that note is not entirely accurate as more recent PG versions include "$user", we should likely add a note for that.