Schema not included in foreign keys in SQLAlchemy model #41
Description
In a case where there are multiple schemas, schema name should be included in the generated foreign key references.
As an an example, a really simple dbdiagrams.io "model":
The exported PostgreSQL DDL looks like this:
CREATE SCHEMA "schema1";
CREATE SCHEMA "schema2";
CREATE TABLE "schema1"."table1" (
"id" int PRIMARY KEY,
"reference_to_table_in_another_schema" int NOT NULL
);
CREATE TABLE "schema2"."table2" (
"id" int PRIMARY KEY
);
ALTER TABLE "schema1"."table1" ADD FOREIGN KEY ("reference_to_table_in_another_schema") REFERENCES "schema2"."table2" ("id");Generating sqlalchemy model from this with command
omm /tmp/sample_ddl.sql -m sqlalchemy --no-global-schemacreates model like this:
import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Table1(Base):
__tablename__ = "table1"
id = sa.Column(sa.Integer(), primary_key=True)
reference_to_table_in_another_schema = sa.Column(
sa.Integer(), sa.ForeignKey("table2.id"), nullable=False
)
__table_args__ = dict(schema="schema1")
class Table2(Base):
__tablename__ = "table2"
id = sa.Column(sa.Integer(), primary_key=True)
__table_args__ = dict(schema="schema2")Problem is that the schema name is missing from sa.ForeignKey("table2.id"). This will fail because this statement would mean that the table should be found under "public" schema in postgresql, whereas the table is in fact in schema2.
File "/root/.cache/pypoetry/virtualenvs/test-enkIQtTI-py3.9/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 2530, in _resolve_column
raise exc.NoReferencedTableError(
sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'table1.reference_to_table_in_another_schema' could not find table 'table2' with which to generate a foreign key to target column 'id'
Adding the schema reference like below fixes the issue:
reference_to_table_in_another_schema = sa.Column(
sa.Integer(), sa.ForeignKey("schema2.table2.id"), nullable=False