agronholm / agronholm/sqlacodegen

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

Open
#426 7 comments 0 reactions 1 assignee Claimed by @sheinbergon View on GitHub
bug
Dominant language
Python
Stars
2.4k
Forks
284
Avg merge
2d 1h
Merged PRs (30d)
1

Description

### Things to check first

- [x] I have searched the existing issues and didn't find my bug already reported there

- [x] 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.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.