How to make a double foreign key as a primary key in SQLModel resp. SQLAlchemy

Question

Currently, I have two tables, user and groups and I want to associate them in table group2user, where I specify who has which rights to a group table.

Hence, I need two foreign keys in group2user, which should be able to do cascading delete (if we delete the user or group item).

For this, I wrote down the following code with SQLModel and SQLAlchemy

import enum
from typing import Optional
from sqlmodel import SQLModel, Field, Relationship

class User(SQLModel, table=True):
    user_id: str = Field(primary_key=True, nullable=False)
    user_group: Optional["Group"] = Relationship(
        sa_relationship_kwargs={"uselist": False, "cascade": "save-update,merge,expunge,delete,delete-orphan"})

class Group(SQLModel, table=True):
    group_id: uuid.UUID = Field(default_factory=uuid.uuid4, primary_key=True, index=True, nullable=False)
    user_id: Optional[str] = Field(sa_column=Column(String, ForeignKey("user.user_id", ondelete="CASCADE")))
    user_list: List["Group2User"] = Relationship(
        sa_relationship_kwargs={"cascade": "save-update,merge,expunge,delete,delete-orphan"},
       )

class GroupRights(enum.Enum):
    READ = "read"
    WRITE = "write"
    ADMIN = "admin"


class Group2User(SQLModel):
    user_id: str = Field(sa_column=Column(String, ForeignKey("user.user_id", ondelete="CASCADE"), nullable=False, primary_key=True))
    group_id: uuid.UUID = Field(sa_column=Column(UUID, ForeignKey("group.group_id", ondelete="CASCADE"),
                                                 primary_key=True, nullable=False))
    rights: GroupRights = Field(default="READ")

When I have a look at the tables (see below), I see the cascading delete for group via foreign key user_id. However, the same does not apply for user_id and group_id in the table group2user, where it is a primary key, but not a foreign key with ON DELETE CASCADE.

CREATE TABLE "user" (
    user_id VARCHAR NOT NULL,
    PRIMARY KEY (user_id)
)


CREATE TABLE "group" (
    user_id VARCHAR,
    group_id UUID NOT NULL,
    PRIMARY KEY (group_id),
    FOREIGN KEY(user_id) REFERENCES "user" (user_id) ON DELETE CASCADE
)


CREATE TABLE group2user (
    user_id VARCHAR NOT NULL,
    group_id UUID NOT NULL,
    rights grouprights NOT NULL,
    PRIMARY KEY (user_id, group_id)
)

Do you know how to fix that?

Answer 1

1. If you want a many to many relationship, you should use the link_model option on Relationship members, like the following.

   class Group2User(SQLModel, table=True):
       ...
   
   class User(SQLModel, table=True):
       ...
       groups: List['Group'] = Relationship(back_populates='users', link_model=Group2User)
   
   class Group(SQLModel, table=True):
       ...
       users: List[User] = Relationship(back_populates='groups', link_model=Group2User)
   

   See the official tutorial for a detail.

2. If you want an association object relationship, you should define bidirectional Relationship members like the following.(I renamed Group2User to Acl for better readability. ACL means access control list.)

   class Acl(SQLModel, table=True):
       ...
       user: 'User' = Relationship(back_populates='acls')
       group: 'Group' = Relationship(back_populates='acls')
   
   class User(SQLModel, table=True):
       ...
       acls: List[Acl] = Relationship(back_populates='user')
   
   class Group(SQLModel, table=True):
       ...
       acls: List[Acl] = Relationship(back_populates='group')
   

   See the official tutorial for a detail.

3. If you want cascade deletions on the DBMS level, you should do like this.(I changed the name and type of the primary key columns for better readability.)

   class Acl(SQLModel, table=True):
       user_id: int = Field(sa_column=
           Column(Integer, ForeignKey('user.id', ondelete='CASCADE'),
               primary_key=True))
       group_id: int = Field(sa_column=
           Column(Integer, ForeignKey('group.id', ondelete='CASCADE'),
               primary_key=True))
       ...
       user: 'User' = Relationship(back_populates='acls')
       group: 'Group' = Relationship(back_populates='acls')
   
   class User(SQLModel, table=True):
       ...
       acls: List[Acl] = Relationship(back_populates='user',
           sa_relationship_kwargs = dict(cascade='all', passive_deletes=True))
   
   class Group(SQLModel, table=True):
       ...
       acls: List[Acl] = Relationship(back_populates='group',
           sa_relationship_kwargs = dict(cascade='all', passive_deletes=True))
   

   See the SQLAlchemy documentation for a detail.

4. As a side note, SQLModel project is in its pretty early stage at this time.(Even it has no API reference.) I don't recommend it if you are not ready to hack the source code.