Existing Table Recreated - Is my only option include_schemas and include_object?

[totalhack]

Describe your question

I need to manage multiple, different schemas in a single application with a MySQL database. So all table models will exist on one MetaData object and there will ideally be a single alembic_version table.

I thought I would be able to get by just setting the schema in the __table_args__ of each table, as that seems to auto-generate the proper migration on first pass with the schema set appropriately per table. But then I tried to run it again (after applying the migration), and it acted like the table did not exist. It seems like alembic doesn't use that schema information to check for the existence of the table?

I have come across the include_schema and include_object options. I tried include_schema at first actually but it started off by trying to delete all of the default mysql tables. I know I could use include_object to prevent that, or to only look for tables included in my MetaData (which you provided a great example of another ticket), but the latter loses the ability to detect table removal.

Is my best bet here to just use both include_schema and include_object and perhaps write code that ignores schemas outside of my custom ones? Just wondering if I'm missing something and this should be working.

Thanks for this library and sqlalchemy, I use them both a ton!

Have a nice day!

Hey, you too.

[zzzeek]

so MySQL "schemas" are actually databases, so immediately there's an awkwardness to using multiple databases for one application with this backend. I can see that it is going to do things like include the "mysql" database, "information_schema" and "performance_schema" and all that if used in this way.

Since MySQL doesn't have a first class "schema" concept distinct from databases, Alembic does need to be instructed which schemas it should be looking at.

There is a plan to provide an additional "schema selection" option, that's in another ticket somewhere, but for the moment, include_object can be used to deselect schemas directly without impacting Alembic's ability to see tables removed from the schemas you care about. the only requirement is that you have the list of schema names that you do want, then you'd do include_object as:

schemas_i_want = ["schema_a", "schema_b"]

def include_object(object, name, type_, reflected, compare_to):
    if reflected and type_ == 'table" and object.schema not in schemas_i_want:
       return False
    else:
       return True

and that's it! the only downside is that all those tables in those schemas get reflected unnecessarily but for MySQL this is not terribly non-performant. again there is a plan for a way to do the same thing above before it actually reflects the tables but we dont have that yet.

[totalhack]

Thanks for the quick reply and the specific example. That solution should be fine for now. I'll keep an eye out for the feature you mentioned.

[totalhack]

Hey @zzzeek, this may or may not be related to the solution above but I haven't found a clear answer yet so I'll ask here...

Alembic is still creating empty revisions if I run autogenerate and there is nothing to actually update. Is there a way to prevent this? Wondering if it's related to the fact that include_schemas is causing these extra tables to be reflected (but ignored in include_object).

I was hoping it would just be a no-op and require no manual intervention to delete the empty revision file, but I can work around this too. I don't intend to be running and applying --autogenerate revisions automatically, but there are situations where I might run it manually a second time to make sure everything is in sync.

[zzzeek]

Hey @zzzeek, this may or may not be related to the solution above but I haven't found a clear answer yet so I'll ask here...

Alembic is still creating empty revisions if I run autogenerate and there is nothing to actually update. Is there a way to prevent this? Wondering if it's related to the fact that include_schemas is causing these extra tables to be reflected (but ignored in include_object).

that's alembic's default behavior. If you dont want it to create one, use the recipe at https://alembic.sqlalchemy.org/en/latest/cookbook.html#don-t-generate-empty-migrations-with-autogenerate

I was hoping it would just be a no-op and require no manual intervention to delete the empty revision file, but I can work around this too. I don't intend to be running and applying --autogenerate revisions automatically, but there are situations where I might run it manually a second time to make sure everything is in sync.

there's also a desire to create a command that does this, see #724 which also includes recipes. there's also a 3rd party tool that does this right now , see https://pypi.org/project/alembic-autogen-check/ .