String length change not being detected by Alembic (even with compare_type=True)

[blamcodes]

Version:

• alembic: 1.11.1
• sqlalchemy: 2.0.15

Am having issues with alembic not detecting String length changes even with compare_type set to True in the env.py file in both the run_migration_* sections.

Changing this:

code: Mapped[str] = mapped_column(nullable=False)

to:

    code: Mapped[str] = mapped_column(String(6), nullable=False)

Does not bring about any changes when running alembic revision --autogenerate

❯ dc exec backend alembic revision --autogenerate -m "test2"
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
  Generating /app/alembic/versions/2023-06-06_test2.py ...  done

The env.py sections:

def run_migrations_offline() -> None:
    """Run migrations in 'offline' mode.

    This configures the context with just a URL
    and not an Engine, though an Engine is acceptable
    here as well.  By skipping the Engine creation
    we don't even need a DBAPI to be available.

    Calls to context.execute() here emit the given string to the
    script output.

    """
    url = get_url()
    context.configure(
        url=url,
        target_metadata=target_metadata,
        literal_binds=True,
        compare_type=True,
        dialect_opts={"paramstyle": "named"},
    )

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


def run_migrations_online() -> None:
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
    configuration = config.get_section(config.config_ini_section)
    configuration["sqlalchemy.url"] = get_url()
    connectable = engine_from_config(
        configuration,
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

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

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

Based on this thread, the change should detect string length changes. (#612)

Please let me know if I'm missing anything.

[zzzeek]

it's likely this is expected because going from unlengthed to lengthed might have some guards against false positives going on.

that said, if you can please share:

1. database in use, + version
2. what the column in question looks like before and after, with for example a SHOW CREATE TABLE or similar

[blamcodes]

Hey @zzzeek,

1 . Am using Postgres 14.6

2. Please let me know if I'm not providing what you need. Here are the DDL statements.

This is the original table with the code column set to code: Mapped[str] = mapped_column(nullable=False)

CREATE TABLE sector_code (
	parent_id uuid NULL,
	sector_code_system_id uuid NOT NULL,
	id uuid NOT NULL DEFAULT gen_random_uuid(),
	created_at timestamp NOT NULL DEFAULT timezone('utc'::text, now()),
	updated_at timestamp NOT NULL DEFAULT timezone('utc'::text, now()),
	deleted_at timestamp NULL,
	code varchar NOT NULL,
	CONSTRAINT sector_code_pkey PRIMARY KEY (id)
);

Here's the original create table in an older revision.

    op.create_table(
        "sector",
        sa.Column("name", sa.String(), nullable=False),
        sa.Column("id", sa.UUID(), server_default=sa.text("gen_random_uuid ()"), nullable=False),
        sa.PrimaryKeyConstraint("id", name=op.f("sector_pkey")),
        sa.UniqueConstraint("name", name=op.f("sector_name_key")),
    )

I commented out the model and created a alembic revision removing the table. Then un-commented the model along with the change to the String(6) so alembic generated a new table:

CREATE TABLE sector_code (
	code varchar(6) NOT NULL,
	parent_id uuid NULL,
	sector_code_system_id uuid NOT NULL,
	id uuid NOT NULL DEFAULT gen_random_uuid(),
	created_at timestamp NOT NULL DEFAULT timezone('utc'::text, now()),
	updated_at timestamp NOT NULL DEFAULT timezone('utc'::text, now()),
	deleted_at timestamp NULL,
	CONSTRAINT sector_code_pkey PRIMARY KEY (id)
);

 op.create_table('sector_code',
    sa.Column('code', sa.String(length=6), nullable=False),
    sa.Column('parent_id', sa.UUID(), nullable=True),
    sa.Column('sector_code_system_id', sa.UUID(), nullable=False),
    sa.Column('id', sa.UUID(), server_default=sa.text('gen_random_uuid ()'), nullable=False),
    sa.Column('created_at', sa.DateTime(), server_default=sa.text("timezone('utc', now())"), nullable=False),
    sa.Column('updated_at', sa.DateTime(), server_default=sa.text("timezone('utc', now())"), nullable=False),
    sa.Column('deleted_at', sa.DateTime(), nullable=True),
    sa.ForeignKeyConstraint(['parent_id'], ['sector_code.id'], name=op.f('sector_code_parent_id_fkey')),
    sa.ForeignKeyConstraint(['sector_code_system_id'], ['sector_code_system.id'], name=op.f('sector_code_sector_code_system_id_fkey')),
    sa.PrimaryKeyConstraint('id', name=op.f('sector_code_pkey'))
    )

[zzzeek]

PG allows for unlengthed VARCHAR. the alembic type comparison rules are set so that they only report a "positive" if two parameters don't match. if the number of parameters are different, it does not attempt to guess, so in this case VARCHAR and VARCHAR(6) have a different number of parameters; it's a negative. the overarching priority is to not produce false positives, since a false negative means editing a single migration file to fix for the miss, but a false positive means editing every migration file.

[blamcodes]

Thanks for confirmation of the behaviour @zzzeek! Will alter those columns manually.