1

I have a project where one of the DB Schema ("alpha") was already existing, and it comprises of "user" table. The entire schema and its tables were being managed using go and goose, and contains all the auth related tables.

Now, my team wants to work in fastapi, we chose sqlalchemy+alembic for ORM & Migration. Now, the issue is I have a few tables where I want to create foreign keys on existing alpha.user table's id field. Let me explain:

class Forms(Base, UserMixin):
    __tablename__ = "forms"
    
    form_config = Column(String(8192), nullable=False)
    is_active = Column(SMALLINT, default = 0)

class UserMixin(object):
    @declared_attr
    def user_id(self):
        return Column(String(64), ForeignKey('alpha.user.id'), nullable=False)

On running alembic revision --autogenerate, it gives an error:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'forms.user_id' could not find table 'alpha.user' with which to generate a foreign key to target column 'id'

Note that I've used in my env.py the include_names as follows:

def include_name(name, type_, parent_names):
    if type_ == "schema":
        # note this will not include the default schema
        return name in ["beta", "public"]
    else:
        return True

    ...
    
    context.configure(
        include_schemas=True,
        include_name=include_name,
        ...
    )

So my requirement is to be able to have FKEYs on all tables in beta schema, where they are referring to alpha.user's id column, but without need to define alpha table's in my code, since they are already there.

How do I accomplish this, or if there's a better way?

Ouroboros
  • 1,432
  • 1
  • 19
  • 41

1 Answers1

0

You can probably just inject the schema tables into your metadata using reflection like this:

metadata_obj = MetaData()
#...
metadata_obj.reflect(someengine, schema="alpha")

Or maybe try just those tables:

metadata_obj = MetaData()
#...
metadata_obj.reflect(someengine, schema="alpha", only=["user"])

Then I think the way you are using include_name should ignore those tables other than checking the FK.

Ian Wilson
  • 6,223
  • 1
  • 16
  • 24