Including database name in schema

[EVKoene]

To allow cross-database querying I want tot add a database name to the schema in table_args. However, this makes alembic see the schema as a new schema when I try to autogenerate a revision. Some example code I tested in a database called example_db:

from sqlalchemy import Column, String
from sqlalchemy.orm import declarative_base

Base = declarative_base()


class ExampleModel(Base):
    __tablename__ = "example_model"
    pk = Column(String(2), primary_key=True)
    __table_args__ = {"schema": "raw"}

for the first revision. When I add the database name to the schema like this:

from sqlalchemy import Column, String
from sqlalchemy.orm import declarative_base

Base = declarative_base()


class ExampleModel(Base):
    __tablename__ = "example_model"
    pk = Column(String(2), primary_key=True)
    __table_args__ = {"schema": "example_db.raw"}

a new revision will detect an added and a removed table, even though they are the same. I'm also unable to upgrade as alembic will tell me there already is an object called example_model in the database. Interestingly enough the other way around works fine: alembic will detect no differences if you first include the database name in the schema and remove it after.

Should I even attempt to include database names in the schema or should I look for another solution? If including them is fine, how should I do it?

[zzzeek]

the only thing I can see doing here is keeping that schema as "raw" but when you do your queries set up the engine with a schema translate map: https://docs.sqlalchemy.org/en/20/core/connections.html#translation-of-schema-names

[EVKoene]

This works perfectly, thanks!

[EVKoene]

@zzzeek , I was a bit too hasty in concluding everything worked as I hoped. Seems like this works for different schemas, but not for the combination of database + schema. So I'm back to trying to add the database name to the table_args.

Is there a way to change my alembic config to make alembic recognise the database name from the table_args attribute as the database name? I presume I need to edit the alembic.ini or overwrite the from alembic.config import Config object somehow, but could not find a matching example in the cookbook or docs. I am using a standard single-database alembic.ini at the moment. Also, let me know if you prefer I make a new discussion for this question as it's slightly different.

[zzzeek]

first off why do you need the database name at all in the __table_args__ when running the application? Secondly, translate_schema should be working for this and there should be nothing different about a db+schema combination, if that's the case then that's a bug in SQLAlchemy.

Is there a way to change my alembic config to make alembic recognise the database name from the table_args attribute as the database name?

well this points to why do you need DB name there at all, if Alembic is able to see these tables in "raw" without having to look at "database.raw", I dont see what "database.raw" is getting us.

you can do translate_schema on the alembic side as well, converting "database.raw" to "raw", but I think I need to understand the actual problem here, this seems too complicated for what's going on.

[EVKoene]

I get what you're saying, but I do think we need to include the DB name for what we're trying to do. To make sure what we're trying to accomplish and the problems are clear:

• We want a cross database join between tables from two different databases
• When originally configuring the tables, we defined them with only the schema name in the table_args and default options.
• Now, in order to allow cross database querying we want to add the database to the table_args (this works).
• When we autogenerate an alembic revision, alembic doesn't see the tables already exist, so it generates a drop and a create table

The alternative would be to always write revisions by hand, but it would be easier if we get alembic to understand the database names as part of the schema.

[zzzeek]

With these "two different databases", is it the case that when you are running alembic migrations, you are actually using distinct create_engine() calls to have independent connections to each database, and then on the query side in the app, you are using just a single engine to do cross DB calls, is that the use case?

this remains an issue of a MetaData collection that needs to look two different ways depending on how engine connectivity is set up in relation to that collection.

So there's one other way that I was holding off on because it's a little more blunt, but this probably will work. The "schema_translate_map" feature gives us this way that we can "translate" the schema names in the Table objects on the fly, when we build statements, that's fine. Alembic is not really very good at interpreting that map and I would not use it on the alembic side.

however there's another way to get a Table that has a different "schema" than the one it had originally and that is to copy it using the to_metadata method.

so the proposal would be:

1. use the "dbname.schema" in your table metadata, have your application run that way, that's great

2. in your alembic env.py, where you set up "target_metadata", let's point that to a brand new, blank MetaData() collection:

   target_metadata = MetaData()
   

3. now lets take your model metadata and copy all the tables into that new one:

     
     def get_schema_for_migrations(schema_name_w_database):
           dbname, schemaname = schema_name_w_database.split(".")
           return schemaname

    def get_schema_for_fk_reference(table, target_schema, fkc, existing_target_schema):
        """Given the schema inside of a ForeignKey, return the schema that should
             be applied."""

        return get_schema_for_migrations(target_schema)

     target_metadata = MetaData()
     for table in mymodel.Base.metadata.tables.values():
          table.to_metadata(
              target_metadata, 
              schema=get_schema_for_migrations(table.schema),
              referred_schema_fn=get_schema_for_fk_reference
         )

take a look, using print statements or pdb, for what is getting placed into target_metadata as far as schema etc. to make sure it's what you want Alembic to be comparing. You should be able to control precisely the schemaname that is in each Table object as well as within each ForeignKey reference. My only concern is that the to_metadata() routine is able to copy some more esoteric constructs like PG ExcludeConstraints and things if you have stuff like that in use but things should be working.

[EVKoene]

Thanks again for your help, I'll try this out and get back to you

[EVKoene]

To update: We tried this with a small table and I understand what you're doing here. This will be enough for us to continue, so this can be considered answered. Thanks again for your help!