Duplicate Table(Base) created if foreign key exists between two separate schema's in one database

[jhenderson-moshipco]

Things to check first

• I have searched the existing issues and didn't find my bug already reported there

• I have checked that my bug is still present in the latest release

Sqlacodegen version

sqlacodegen==3.0.0

SQLAlchemy version

sqlalchemy==2.0.42

RDBMS vendor

PostgreSQL

What happened?

We've got a couple separate schema's in a postgres database that are split for separation of concern and organization. Our system has over 120 tables across all schema's. After testing we found that it has something to do with the public default schema.

When running codegen on two schema's (let's say public, SchemaB) where a foreign key exists on a public table that references a SchemaB table, the codegen output creates two separate python classes for the same object. Let's say the table name is User. We end up with a User & User_ class that are 99% the same except for a schema tag and one different foreign key relationship. To make things worse, if the table that is referenced has any additional foreign keys pointing to other tables, it will do the same for every referenced table. We end up with duplicate classes for every referenced column.

I can't share our actual schema, but I'll show the command and what to do to reproduce.

Database schema for reproducing the bug

sqlacodegen --schema="public,schema_b" --outfile="database/schema.py" --generator=declarative_alpha "postgresql+pg8000://${DB_USER}:${DB_PASSWORD}@0.0.0.0:3306/postgres"

To reproduce, create a table in a public schema and then in a separate schema and make a cross schema foreign key then run the command.

[alessandromoraes]

I'm using version 3.1.0, and I'm having the same problem in the Postgres database using multiple schemas. I had to create a script that generates the models and, at the end, manipulates the duplicate classes and merges them after file generation.

[agronholm]

Please provide the schema and the resulting generated code.

[agronholm]

@jhenderson-moshipco Also what's declarative_alpha? There's no such generator in this code base.

[sheinbergon]

@jhenderson-moshipco @alessandromoraes PRs are more than welcome

[jhenderson-moshipco]

@agronholm, declarative_alpha is our own custom plugin.

Gimme a few minutes and I'll spin up a test db and give the schema and command to reproduce.

from __future__ import annotations

from textwrap import indent

from sqlacodegen.models import ModelClass

from sqlacodegen.generators import DeclarativeGenerator


class DeclarativeAlphaGenerator(DeclarativeGenerator):
    def render_class(self, model: ModelClass) -> str:
        sections: list[str] = []

        # Render class variables / special declarations
        class_vars: str = self.render_class_variables(model)
        if class_vars:
            sections.append(class_vars)

        # Render column attributes
        rendered_column_attributes: list[str] = []
        for nullable in (False, True):
            for column_attr in model.columns:
                if column_attr.column.nullable is nullable:
                    rendered_column_attributes.append(
                        self.render_column_attribute(column_attr)
                    )

        if rendered_column_attributes:
            rendered_column_attributes.sort()
            sections.append("\n".join(rendered_column_attributes))

        # Render relationship attributes
        rendered_relationship_attributes: list[str] = [
            self.render_relationship(relationship)
            for relationship in model.relationships
        ]

        if rendered_relationship_attributes:
            rendered_relationship_attributes.sort()
            sections.append("\n".join(rendered_relationship_attributes))

        declaration = self.render_class_declaration(model)
        rendered_sections = "\n\n".join(
            indent(section, self.indentation) for section in sections
        )
        return f"{declaration}\n{rendered_sections}"

[sheinbergon]

@jhenderson-moshipco @alessandromoraes just to make sure I got my head around this. Is this a regression you're encountering for the 1st time in 3.1.0, and didn't happen in 3.0.0, or is it an issue you encounter as newcomers to this library?

[sheinbergon]

@jhenderson-moshipco @alessandromoraes if you want to have this issue solved, I'll need more co-operation from you end