Bad foreign_keys parameter for self-referencing table

[amacfie]

DDL in postgres:

CREATE TABLE document(
  id                    UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
  ...
  image_id              UUID
);

ALTER TABLE document ADD CONSTRAINT image_fk
  FOREIGN KEY (image_id)
  REFERENCES document(id) ON DELETE CASCADE;

dataclass generated:

@mapper_registry.mapped
@dataclass
class Document:
    __tablename__ = 'document'
    __table_args__ = (
        ForeignKeyConstraint(['image_id'], ['document.id'], ondelete='CASCADE', name='image_fk'),
        PrimaryKeyConstraint('id', name='document_pkey'),
        ...
    )
    __sa_dataclass_metadata_key__ = 'sa'

    id: str = field(metadata={'sa': Column(UUID, server_default=text('gen_random_uuid()'))})
    image_id: Optional[str] = field(default=None, metadata={'sa': Column(UUID)})
    ...

    image: Optional[Document] = field(default=None, metadata={'sa': relationship('Document', remote_side=[id], foreign_keys=[image_id], back_populates='image_reverse')})
    image_reverse: List[Document] = field(default_factory=list, metadata={'sa': relationship('Document', remote_side=[image_id], foreign_keys=[image_id], back_populates='image')})
    ...

SQLAlchemy error:

sqlalchemy.exc.ArgumentError: Column expression expected for argument 'foreign_keys'; got Field(name='image_id',type='Optional[str]',default=None,default_factory=<dataclasses._MISSING_TYPE object at 0x7ffa9ebd1ab0>,init=True,repr=True,hash=None,compare=True,metadata=mappingproxy({'sa': Column('image_id', UUID(), ForeignKey('document.id'), table=<document>)}),kw_only=False,_field_type=_FIELD).

I think the argument should be "[Document.image_id]".

[agronholm]

IIRC this is fixed in master. Would you mind trying with that?

[amacfie]

On master, DDL:

CREATE TABLE document(
  id                    UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
  image_id              UUID
);

ALTER TABLE document ADD CONSTRAINT image_fk
  FOREIGN KEY (image_id)
  REFERENCES document(id) ON DELETE CASCADE;

generated:

@mapper_registry.mapped
@dataclass
class Document:
    __tablename__ = 'document'
    __table_args__ = (
        ForeignKeyConstraint(['image_id'], ['document.id'], ondelete='CASCADE', name='image_fk'),
        PrimaryKeyConstraint('id', name='document_pkey')
    )
    __sa_dataclass_metadata_key__ = 'sa'

    id: str = field(metadata={'sa': Column(UUID, server_default=text('gen_random_uuid()'))})
    image_id: Optional[str] = field(default=None, metadata={'sa': Column(UUID)})

    image: Optional[Document] = field(default=None, metadata={'sa': relationship('Document', remote_side=[id], back_populates='image_reverse')})
    image_reverse: List[Document] = field(default_factory=list, metadata={'sa': relationship('Document', remote_side=[image_id], back_populates='image')})

error:

sqlalchemy.exc.ArgumentError: Column expression expected for argument 'remote_side'; got Field(name='id',type='str',default=<dataclasses._MISSING_TYPE object at 0x7f6681115a50>,default_factory=<dataclasses._MISSING_TYPE object at 0x7f6681115a50>,init=True,repr=True,hash=None,compare=True,metadata=mappingproxy({'sa': Column('id', UUID(), table=<document>, primary_key=True, nullable=False, server_default=DefaultClause(<sqlalchemy.sql.elements.TextClause object at 0x7f667f65bd00>, for_update=False))}),kw_only=False,_field_type=_FIELD).

[amacfie]

I think in my original message I was using master too, but there were other fields and tables.

[amacfie]

Issue is gone at least for declarative mode with SQLAlchemy 2

[simkessy]

@agronholm I generated my models using version 3.0.0rc2 which I believe has the fix in for this issue but i'm getting and error with this model

@mapper_registry.mapped
@dataclass
class Request:
    __tablename__ = 'requests'
    __table_args__ = (
        ForeignKeyConstraint(
            ['owner_id'], ['users.id'], ondelete='RESTRICT', onupdate='RESTRICT', name='request_owner'
        ),
        Index('assignee_id_index', 'assignee_id'),
        Index('assignee_state_idx', 'assignee_id', 'state'),
        Index('created_at_index', 'created_at'),
        Index('last_updated_index', 'last_updated'),
        Index('processed_at_index', 'processed_at'),
        Index('request_id_and_state_idx', 'id', 'state'),
        Index('requests_agent_id_idx', 'agent_id'),
        Index('requests_owner_id_idx', 'owner_id'),
        Index('signed_name_idx', 'signed_name'),
        Index('state_index', 'state'),
        Index('user_composite_idx', 'owner_id', 'agent_id', 'created_at'),
        Index('zendesk_id_index', 'zendesk_id'),
    )
    __sa_dataclass_metadata_key__ = 'sa'
    
    request_references: List[RequestReference] = field(
        default_factory=list,
        metadata={
            'sa': relationship(
                'RequestReference', foreign_keys='[RequestReference.request_id_higher]', back_populates='request'
            )
        },
    )

    request_referencess_: List[RequestReference] = field(
        default_factory=list,
        metadata={
            'sa': relationship(
                'RequestReference', foreign_keys='[RequestReference.request_id_lower]', back_populates='request_'
            )
        },
    )

Error:

sqlalchemy.exc.ArgumentError: Column expression expected for argument 'foreign_keys'; got Field(name='request_id_higher',type='int',default=<dataclasses._MISSING_TYPE object at 0x7fd82dd572e0>,default_factory=<dataclasses._MISSING_TYPE object at 0x7fd82dd572e0>,init=True,repr=True,hash=None,compare=True,metadata=mappingproxy({'sa': Column('request_id_higher', Integer(), ForeignKey('requests.id'), table=<request_references>, primary_key=True, nullable=False)}),_field_type=_FIELD).

[agronholm]

Could you at least update to the latest pre-release first?

[simkessy]

The latest version drops support for SQLAlchemy 1.x which is what we're using.

[agronholm]

Well, then I'm afraid I can't help you. I have extremely little time to devote to this project in the first place, let alone for old versions of it.