How to declare symmetric self-referential many-to-many relationship in SQLAlchemy for storing directed graph

Question

from sqlalchemy import DateTime, Table, Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

__all__ = [ "GraphNode" ]

Base = declarative_base()

graph_edges = Table(
    'graph_edges', Base.metadata,
    Column('from_node_id', Integer, ForeignKey('graph_nodes.id'), primary_key=True),
    Column('to_node_id', Integer, ForeignKey('graph_nodes.id'), primary_key=True)
)


class GraphNode(Base):
    __tablename__ = 'graph_nodes'
    id = Column(Integer, primary_key=True, autoincrement=False)
    node_data = Column(String(50), nullable=False)

    from_me = relationship("GraphNode", secondary=graph_edges,
                           primaryjoin=id==graph_edges.c.from_node_id,
                           secondaryjoin=id==graph_edges.c.to_node_id)
    to_me = relationship("GraphNode", secondary=graph_edges,
                         primaryjoin=id==graph_edges.c.to_node_id,
                         secondaryjoin=id==graph_edges.c.from_node_id)

When I do this, I get this error from SQLAlchemy:

SAWarning: relationship 'GraphNode.to_me' will copy column graph_nodes.id to column graph_edges.from_node_id, which conflicts with relationship(s): 'GraphNode.from_me' (copies graph_nodes.id to graph_edges.from_node_id). If this is not the intention, consider if these relationships should be linked with back_populates, or if viewonly=True should be applied to one or more if they are read-only. For the less common case that foreign key constraints are partially overlapping, the orm.foreign() annotation can be used to isolate the columns that should be written towards. To silence this warning, add the parameter 'overlaps="from_me"' to the 'GraphNode.to_me' relationship. (Background on this error at: https://sqlalche.me/e/14/qzyx)

It sounds like SQLAlchemy is noticing that putting something in one node's from_me list will result in a change to some other node's to_me list. This is the desired behavior. I want to be able to traverse the links backwards as well as forwards.

Is there any way to do this properly without SQLAlchemy complaining at me?

Answer 1

Here is what solved the problem. The solution was sort of in the error message, and I'm not completely sure why it worked. But, here it is:

from sqlalchemy import DateTime, Table, Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

__all__ = [ "GraphNode" ]

Base = declarative_base()

graph_edges = Table(
    'graph_edges', Base.metadata,
    Column('from_node_id', Integer, ForeignKey('graph_nodes.id'), primary_key=True),
    Column('to_node_id', Integer, ForeignKey('graph_nodes.id'), primary_key=True)
)


class GraphNode(Base):
    __tablename__ = 'graph_nodes'
    id = Column(Integer, primary_key=True, autoincrement=False)
    node_data = Column(String(50), nullable=False)

    from_me = relationship("GraphNode", secondary=graph_edges,
                           primaryjoin=id==graph_edges.c.from_node_id,
                           secondaryjoin=id==graph_edges.c.to_node_id,
                           back_populates="to_me")
    to_me = relationship("GraphNode", secondary=graph_edges,
                         primaryjoin=id==graph_edges.c.to_node_id,
                         secondaryjoin=id==graph_edges.c.from_node_id,
                         back_populates="from_me")

Basically, I added the back_populates parameter to each relationship. It had the effect I desired.

I really wish I understood what was going on better. I feel more like I'm following a cookbook than starting with ingredients and deciding what sort of meal I want to make with them. I always hate doing that when I'm programming because I'll not understand what's critical and what isn't and make a tiny change that turns out to be massively important.