Alembic drops and recreate index/Foreign Key everytime I create a new migration file

[louistwiice]

Hello Every One.
I am in a need of help about alembic migration file. I have a project setup as followed:

• Alembic 1.9
• SQLalchemy 1.4.36
• Psycop2 binary 2.9.5

My Model file

import pytz
from datetime import datetime
from sqlalchemy import Boolean, Column, String, TIMESTAMP, Integer, ForeignKey, UniqueConstraint, Index
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, scoped_session


SQLALCHEMY_DATABASE_URL = 'postgresql://test:password@localhost:5432/test'
engine = create_engine(SQLALCHEMY_DATABASE_URL)


Base = declarative_base()

class ControlOrgan(Base):
    """
        Model where to save "Control organ" and the owner
        PS: This table will be in public schema only
    """
    __tablename__ = 'control_organs'

    id = Column(UUID(as_uuid=True), primary_key=True, index=True, default=uuid.uuid4)
    name = Column(String, index=True, nullable=False)
    slug = Column(String)
    is_active = Column(Boolean, default=True)
    owner_id = Column(UUID(as_uuid=True), nullable=False)
    is_deleted = Column(Boolean, default=False)
    created_at = Column(TIMESTAMP(timezone=True), default=datetime.now(tz=pytz.UTC))
    updated_at = Column(TIMESTAMP(timezone=True), default=datetime.now(tz=pytz.UTC), onupdate=datetime.now(tz=pytz.UTC))

    roles = relationship('Role')

    __table_args__ = (
        Index('only_not_deleted_control_organ_index', 'name', 'is_deleted',
              unique=True, postgresql_where=(~is_deleted)),
        {'schema': 'public'}
    )


class Role(Base):
    """
        Model where to save every role created in a specific Control organ

        PS: This table will be in a specific schema for each control organ
    """
    __tablename__ = 'roles'

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    description = Column(String)
    control_organ_id = Column(UUID(as_uuid=True), ForeignKey("public.control_organs.id",  ondelete='CASCADE'), nullable=False)
    created_at = Column(TIMESTAMP(timezone=True), default=datetime.now(tz=pytz.UTC))
    updated_at = Column(TIMESTAMP(timezone=True), default=datetime.now(tz=pytz.UTC), onupdate=datetime.now(tz=pytz.UTC))

    permissions = relationship('RolePermissions')

    __table_args__ = (
        Index('unique_name_index', 'name', 'control_organ_id', unique=True),
    )

class RolePermissions(Base):

    __tablename__ = 'role_permissions'

    id = Column(Integer, primary_key=True)
    permission = Column(String, nullable=False)
    role_id = Column(Integer, ForeignKey("roles.id", ondelete="SET NULL"))
    created_at = Column(TIMESTAMP(timezone=True), default=datetime.now(tz=pytz.UTC))
    updated_at = Column(TIMESTAMP(timezone=True), default=datetime.now(tz=pytz.UTC), onupdate=datetime.now(tz=pytz.UTC))

    __table_args__ = (
        UniqueConstraint('permission', 'role_id', name='_role_permission_uc'),
        Index('role_permission_index', 'permission', 'role_id')
    )


class User(Base):

    __tablename__ = 'users'

    id = Column(UUID(as_uuid=True), primary_key=True, index=True, default=uuid.uuid4)
    username = Column(String, index=True, nullable=False)
    email = Column(String, index=True,nullable=False)
    password = Column(String, nullable=False)
    control_organ_id = Column(UUID(as_uuid=True), ForeignKey("public.control_organs.id", ondelete="CASCADE"), nullable=True)
    role_id = Column(Integer, nullable=True)
    last_login_at = Column(TIMESTAMP(timezone=True), nullable=True)
    is_deleted = Column(Boolean, default=False)
    created_at = Column(TIMESTAMP(timezone=True), default=datetime.now(tz=pytz.UTC))
    updated_at = Column(TIMESTAMP(timezone=True), default=datetime.now(tz=pytz.UTC), onupdate=datetime.now(tz=pytz.UTC))

    #entity = relationship('Entity')

    @validates("email")
    def validate_email(self, key, email):
        if bool(re.match('^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$', email)) is False:
            raise ValueError("Email is not valid")

        return email

    __table_args__ = (
        Index('only_not_deleted_user_username_index', 'username', 'is_deleted',
              unique=True, postgresql_where=(~is_deleted)),
        Index('only_not_deleted_user_email_index', 'email', 'is_deleted',
              unique=True, postgresql_where=(~is_deleted)),
        {'schema': 'public'}
    )

So my problem is, every time I generate a new migration, alembic drops and recreates, sometime indexes or Foreign keys. Foreign keys/Indexes are well created in the first time in the database, but Alembic regenerates it in the migration file

This is what I often got

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.ddl.postgresql] Detected sequence named 'roles_id_seq' as owned by integer column 'roles(id)', assuming SERIAL and omitting
INFO  [alembic.ddl.postgresql] Detected sequence named 'role_permissions_id_seq' as owned by integer column 'role_permissions(id)', assuming SERIAL and omitting
INFO  [alembic.autogenerate.compare] Detected removed foreign key (control_organ_id)(id) on table roles
INFO  [alembic.autogenerate.compare] Detected added foreign key (control_organ_id)(id) on table roles
INFO  [alembic.autogenerate.compare] Detected removed foreign key (control_organ_id)(id) on table users
INFO  [alembic.autogenerate.compare] Detected added foreign key (control_organ_id)(id) on table public.users

Please find below, description of 2 of our tables

test=# \d control_organs
                     Table "public.control_organs"
   Column   |           Type           | Collation | Nullable | Default 
------------+--------------------------+-----------+----------+---------
 id         | uuid                     |           | not null | 
 name       | character varying        |           | not null | 
 slug       | character varying        |           |          | 
 is_active  | boolean                  |           |          | 
 owner_id   | uuid                     |           | not null | 
 is_deleted | boolean                  |           |          | 
 created_at | timestamp with time zone |           |          | 
 updated_at | timestamp with time zone |           |          | 
Indexes:
    "control_organs_pkey" PRIMARY KEY, btree (id)
    "ix_public_control_organs_id" btree (id)
    "ix_public_control_organs_name" btree (name)
    "only_not_deleted_control_organ_index" UNIQUE, btree (name, is_deleted) WHERE NOT is_deleted
Referenced by:
    TABLE "roles" CONSTRAINT "roles_control_organ_id_fkey" FOREIGN KEY (control_organ_id) REFERENCES control_organs(id) ON DELETE CASCADE
    TABLE "users" CONSTRAINT "users_control_organ_id_fkey" FOREIGN KEY (control_organ_id) REFERENCES control_organs(id) ON DELETE CASCADE

test=# \d roles
                                          Table "public.roles"
      Column      |           Type           | Collation | Nullable |              Default              
------------------+--------------------------+-----------+----------+-----------------------------------
 id               | integer                  |           | not null | nextval('roles_id_seq'::regclass)
 name             | character varying        |           | not null | 
 description      | character varying        |           |          | 
 control_organ_id | uuid                     |           | not null | 
 created_at       | timestamp with time zone |           |          | 
 updated_at       | timestamp with time zone |           |          | 
Indexes:
    "roles_pkey" PRIMARY KEY, btree (id)
    "unique_name_index" UNIQUE, btree (name, control_organ_id)
Foreign-key constraints:
    "roles_control_organ_id_fkey" FOREIGN KEY (control_organ_id) REFERENCES control_organs(id) ON DELETE CASCADE
Referenced by:
    TABLE "role_permissions" CONSTRAINT "role_permissions_role_id_fkey" FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE SET NULL

Do you guys know what can I set to prevent it from happen.

Thanks a lot for your help

[zzzeek]

your use of the "public" schema explicitly is the likely source of the issue, as this schema is already in the search path and it confuses SQLAlchemy when it it stated explicitly. see https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path for background. just remove the use of "public." and leave it off, it's implicit assuming you haven't changed the search path.

[louistwiice]

Thank you very much

Indeed, it works. There is no foreign Key regeneraton.
I added {"schema": "public"} in some of tables because I don't want alembic to create it another schema.

My application creates schema dynamically. There are tables that need to be in Public schema (ControlOrgan, and users) and others need to be created in every new schema (Roles and RolePermissions).

With the new setting you suggested, I struggle to remove ControlOrgan, and users from every new schema. And now it seems like my function include_object doesn't work anymore

def include_object(object, name, type_, reflected, compare_to):

    if type_ == "table" and name in ['control_organs', 'users']:
            return False
    return True


def run_migrations_online()  -> None:
    ...
    ...
    # We get the list of avalaible schema
    statement = text("""
                        SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema', 
    'pg_catalog', 'pg_toast') ORDER BY schema_name ASC;
                    """)
    result = DB.execute(statement)
    list_of_schemas = [value[0] for value in result ]

    # Update every table schema
    for schema_name in list_of_schemas:
        with connectable.connect() as connection:
        connection.execute(text('set search_path to "%s"' % schema_name))

        if schema_name == 'public':
            context.configure(
                connection=connection, target_metadata=target_metadata,
                #include_schemas=True
            )
        else:
            context.configure(
                connection=connection, target_metadata=target_metadata,
                include_schemas=True, include_object=include_object
            )

Do you have a hint about what should i adjust please ?

Thanks again for your help

[zzzeek]

OK, now you have a really hard problem, because Alembic doesn't have first class support for this pattern. I see you are using the recipe at https://alembic.sqlalchemy.org/en/latest/cookbook.html#rudimental-schema-level-multi-tenancy-for-postgresql-databases but that recipe was tested for an application where all tables are in a tenant schema.

I would say go back to how you had it, where "public" tables include the explicit schema name of "public", but make sure public is not in the search_path. The main thing is, if a Table has a non-None schema, that non-None schema should not be in the search_path.

is your application normally using schema_translate_map to function?

[zzzeek]

you also might want to try adding postgresql_ignore_search_path to the __table_args__ for those tables that are part of public, where you list out public explicitly:

 __table_args__ = (
        Index('only_not_deleted_control_organ_index', 'name', 'is_deleted',
              unique=True, postgresql_where=(~is_deleted)),
        {'schema': 'public', 'postgresql_ignore_search_path': True}
    )

this is all stuff that is confusing and not well tested though

[louistwiice]

Ok i'll try and let you know. I'll come back ASAP

but make sure public is not in the search_path. The main thing is, if a Table has a non-None schema, that non-None schema should not be in the search_path

I am not sure I understand this part well.
How you you set it given the 4th models given above?
Sould I change the default search path in the env.py file ?

[zzzeek]

yes you are changing the search_path in your env.py already, that's what

connection.execute(text('set search_path to "%s"' % schema_name))

does. you have control over this, it's just confusing