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?