Alembic: Alembic dropped several tables that does not belong to my application

[Lindsey1986]

I have a database with existing tables that are not used by my Python code. I generated a migration using Flask-Migrate and ran it, and it deleted my existing tables while creating the user table. How can I run migrations without removing any existing tables?

I read about include_object and from what I understand I could whitelist tables that should not be dropped, but this is not sustainable in my project as new tables are added all the time by other team members.

How can I tell Alembic to stop dropping tables that it doesn't know about? I only want it to make changes to tables that are within my model definition in my Python app.

[zzzeek]

I have a database with existing tables that are not used by my Python code. I generated a migration using Flask-Migrate and ran it, and it deleted my existing tables while creating the user table. How can I run migrations without removing any existing tables?

You should never run the migrations generated by autogenerate without reviewing them first and modifying them to fit your needs. autogenerate is not intended to create perfect migrations, it is only meant to save the trouble of the bulk of writing them.

That said, the include_object hook that you've found is the correct way to affect what tables Alembic considers.

I read about include_object and from what I understand I could whitelist tables that should not be dropped, but this is not sustainable in my project as new tables are added all the time by other team members.

has your organization considered using separate schemas or databases to isolate tables that belong to different applications? if multiple applications are all storing their tables in the same schema / database and I would assume with no naming conventions that can separate them either, what if two different applications both want to have a table named "user" for example?

How can I tell Alembic to stop dropping tables that it doesn't know about? I only want it to make changes to tables that are within my model definition in my Python app.

Alembic doesn't "know" or "not know" about certain tables, it only "knows" what you give it, and for the autogenerate task, you give it a database connection from which it will get the list of table names and your MetaData() object and it compares those two things.

include_object is a callable function for which you can define any kind of logic you'd like, not just a hardcoded list of table names.

for example, if you have a particular MetaData() object that has all your tables for your models that you care about, you can use that:

def include_object(object, name, type_, reflected, compare_to):
    if type_ == "table":
        if name in my_metadata.tables:
            return True
        else:
            return False
    else:
        return False

this of course will prevent autogenerate from detecting that you removed a table from your own model, but it will otherwise prevent all drop table directives from being generated.

More simply however, include_object gives you enough information to know if a particular table being looked at is only in the database and not in your MetaData collection. If the "reflected" argument is True, that means the Table is from the database, not your MetaData, and if compare_to is None, that means your MetaData has no corresponding table. So you can use that:

def include_object(object, name, type_, reflected, compare_to):
    if type_ == "table" and reflected and compare_to is None:
        return False
    else:
        return True

cookbook section in 5 minutes.

[zzzeek]

forgot to add the ticket on the change that's in (i typed the wrong number see next comment)

[zzzeek]

oops, i mean cf751243372044e33aa2a5450a9996d642

[zzzeek]

recipe is added, closing this