SQLModel does not generate many-to-many classes correctly

[darktempla]

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

3.0.0

SQLAlchemy version

2.0.38

RDBMS vendor

PostgreSQL

What happened?

Ran the command

sqlacodegen postgresql://postgres:postgres@localhost:5432/sentimental --generator sqlmodels --outfile data_pipelines/lib/models/pg_ext_sqlm.py

All tables generated correctly but the many-to-many class was not correct. The base metaclass was missing after the first parmeter property_lead_link.

t_property_lead_link = Table(
    'property_lead_link', ,
    Column('property_lead_id', Integer, primary_key=True, nullable=False),
    Column('lead_link_id', Integer, primary_key=True, nullable=False),
    ForeignKeyConstraint(['lead_link_id'], ['property_link.id'], name='fk_property_lead_link_link'),
    ForeignKeyConstraint(['property_lead_id'], ['property_lead.id'], name='fk_property_lead_link_lead'),
    PrimaryKeyConstraint('property_lead_id', 'lead_link_id', name='pk_property_lead_link')
)

Database schema for reproducing the bug

CREATE TABLE public.property_lead (
  id serial NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
);

ALTER TABLE public.property_lead ADD CONSTRAINT pk_property_leads PRIMARY KEY (id);

CREATE TABLE public.property_link (
  id serial NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
);

ALTER TABLE public.property_link ADD CONSTRAINT pk_lead_links PRIMARY KEY (id);

CREATE TABLE public.property_lead_link (
  property_lead_id integer NOT NULL,
  lead_link_id integer NOT NULL
);

ALTER TABLE public.property_lead_link ADD CONSTRAINT pk_property_lead_link PRIMARY KEY (property_lead_id, lead_link_id);
ALTER TABLE public.property_lead_link ADD CONSTRAINT fk_property_lead_link_lead FOREIGN KEY (property_lead_id) REFERENCES public.property_lead(id);
ALTER TABLE public.property_lead_link ADD CONSTRAINT fk_property_lead_link_link FOREIGN KEY (lead_link_id) REFERENCES public.property_link(id);

[darktempla]

When I generate the same without --generator sqlmodels then the sqlalchemy Base.metadata reference is used in the table definition.

t_property_lead_link = Table(
    "property_lead_link", Base.metadata,
    Column("property_lead_id", Integer, primary_key=True, nullable=False),
    Column("lead_link_id", Integer, primary_key=True, nullable=False),
    ForeignKeyConstraint(["lead_link_id"], ["property_link.id"], name="fk_property_lead_link_link"),
    ForeignKeyConstraint(["property_lead_id"], ["property_lead.id"], name="fk_property_lead_link_lead"),
    PrimaryKeyConstraint("property_lead_id", "lead_link_id", name="pk_property_lead_link")
)

[agronholm]

Looks like a duplicate of #392?

[sheinbergon]

@agronholm I'm not sure. PKs are defined, so this should not happen

[devly-tal]

The problem seems to stem from the following code in generators:

if len(fk_constraints) == 2 and all(
                col.foreign_keys for col in table.columns
            ):
                model = models_by_table_name[qualified_name] = Model(table)
                tablename = fk_constraints[0].elements[0].column.table.name
                links[tablename].append(model)
                continue

This causes the table to be linked as a Model instead of a class model.
A model requires a metadata ref, which is given from self.base.metadata_ref, in SQLModelGenerator, it is set to be an empty string:

def generate_base(self) -> None:
        self.base = Base(
            literal_imports=[],
            declarations=[],
            metadata_ref="",
        )

I'm not sure what the general solution should be, I solved the issue with a workaround (through a custom generator inheriting from SQLModelGenerator) that works in the context of my code.

[sheinbergon]

@devly-tal thank you for putting in the time to diagnose this issue. Would you mind opening a PR, and we'll work on that?

[sheinbergon]

@agronholm your recent work fixed the missing metadata ref issue. However, this PR uncovers an issue where perfectly SQLModel eligible link tables get generated as tables. So I'mma leave this issue open until this is addressed

[agronholm]

SQLModel has its own way of specifying link tables, and I don't think we ever supported that.

[sheinbergon]

SQLModel has its own way of specifying link tables, and I don't think we ever supported that.

I concur. After reviewing SQLModel documentation, I'm working on adding that support exactly