Linking model with foreign data (from an external database)

[mijofa]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

"""
Sample/test code for sqlmodel/LDAP relationship.

I have hero users in LDAP.
I have team groups in LDAP.
Hero <-> Team relationship is handled by LDAP.

I have headquarters in SQL.
Access control for the headquarters should be based on what team(s) the hero is part of.
I don't want to copy LDAP data into SQL where it might get out of sync with the LDAP data.
"""
import typing

import sqlmodel
# import ldap3


# # External functions for getting a group from LDAP by name
# ldap_conn = ldap3.Connection(server='ldapi:///var/run/slapd/ldapi',
#                         authentication=ldap3.SASL,
#                         sasl_mechanism=ldap3.EXTERNAL,
#                         sasl_credentials='',
#                         auto_bind=True,
#                         # FIXME: once python3-ldap3 (>= 2.9.1), do
#                         #        client_strategy=ldap3.SAFE_RESTARTABLE,
#                         raise_exceptions=True)
# def _ldap_group(cn: str, attributes=ldap3.ALL_ATTRIBUTES) -> typing.Mapping[str, typing.List[typing.Any]]:
#     assert isinstance(cn, str), 'type mismatch — maybe got bytes?'
#     ldap_conn.search(search_base=both_base,
#                 search_scope='SUBTREE',
#                 search_filter='(&(objectClass=posixGroup)(cn={}))'.format(cn),
#                 attributes=attributes)
#     if 0 == len(ldap_conn.response):
#         raise KeyError('No such LDAP group', cn)
#     assert 1 == len(ldap_conn.response)
#     return ldap_conn.response[0]['attributes']
# To avoid the LDAP dependency for this example, I'll just hack this in like so:
def _ldap_group(cn: str, attributes = None):
    if cn == 'preventers':
        return {'memberUid': ['Deadpond', 'Rusty Man', 'Spider-Boy'], 'objectClass': ['posixGroup'], 'gidNumber': 1024, 'description': ['Preventers'], 'cn': ['preventers']}
    elif cn == 'z.force':
        return {'memberUid': ['Deadpond', 'Knife-hands'], 'objectClass': ['posixGroup'], 'gidNumber': 1025, 'description': ['Z-Force'], 'cn': ['z.force']}


class TeamHeadquartersGroupLink(sqlmodel.SQLModel, table=True):
    """
    Linking table for Team Headquarters to LDAP groups.
    I expect this table will always be required
    """

    hq_id: int = sqlmodel.Field(foreign_key='teamheadquarters.id', primary_key=True)
    # FIXME: I want to somehow link this to the LDAP group
    group_id: str = sqlmodel.Field(foreign_key='teamgroup.id', primary_key=True, description="The LDAP group name")


class TeamGroup(sqlmodel.SQLModel, table=True):
    """
    An SQL representation of the LDAP group.
    This table shouldn't need to exist in the SQL database.
    """

    id: str = sqlmodel.Field(primary_key=True)

    @property
    def _ldap_data(self):
        # FIXME: Add some cache/memoization here
        return _ldap_group(cn=self.id)
    @property
    def description(self):
        return self._ldap_data['description']
    @property
    def memberUid(self):
        return self._ldap_data['memberUid']


class TeamHeadquarters(sqlmodel.SQLModel, table=True):
    """
    Team Headquarters.
    Exists only in SQL, and will stay here
    """

    id: int | None = sqlmodel.Field(default=None, primary_key=True)
    name: str = sqlmodel.Field(description="Cool name given to the headquarters")
    address: typing.Optional[str] = sqlmodel.Field(description="Street address or GPS co-ordinates")
    # FIXME: I want this list to be a list of LDAP group objects, not SQL objects
    allowed_groups: typing.List[TeamGroup] = sqlmodel.Relationship(link_model=TeamHeadquartersGroupLink)


def create_headquarters():
    mansion = TeamHeadquarters(name='Z-Force Mansion', address='Australia', allowed_groups=[TeamGroup(id='z.force')])
    moon = TeamHeadquarters(name='The Moon', address='SPACE!!', allowed_groups=[TeamGroup(id='preventers')])

    with sqlmodel.Session(engine) as session:
        session.add(mansion)
        session.add(moon)
        session.commit()


def update_headquarters():
    with sqlmodel.Session(engine) as session:
        # Z-Force Mansion has been compromised, revoke all access
        mansion: TeamHeadquarters = session.exec(
            sqlmodel.select(TeamHeadquarters).where(TeamHeadquarters.name == "Z-Force Mansion")
        ).one()
        mansion.allowed_groups.clear()
        session.add(mansion)

        # Z-Force can hang with The Preventers for a bit
        moon: TeamHeadquarters = session.exec(
            sqlmodel.select(TeamHeadquarters).where(TeamHeadquarters.name == "The Moon")
        ).one()
        z_force: TeamGroup = session.exec(sqlmodel.select(TeamGroup).where(TeamGroup.id == "z.force")).one()
        moon.allowed_groups.append(z_force)
        session.add(moon)

        session.commit()

        session.refresh(mansion)
        session.refresh(moon)
        print(mansion, 'allowed heroes:', [g.memberUid for g in mansion.allowed_groups])
        print(moon, 'allowed heroes:', [g.memberUid for g in moon.allowed_groups])


def main():
    global engine
    engine = sqlmodel.create_engine('sqlite://', echo=False)
    sqlmodel.SQLModel.metadata.create_all(engine)
    create_headquarters()
    update_headquarters()


if __name__ == "__main__":
    main()

Description

So basically I want a linking table where only one side of the link is an actual SQL table while the other side is a separate Python object.
I've put most of my notes into comments in the code, so not really much to add in the description here.
The sample code "works", but is not really what I want, as you'll see in those comments.

• I have hero users in LDAP.
• I have team groups in LDAP.
• Hero <-> Team relationship is handled by LDAP.
• I have headquarters in SQL.
• Access control for the headquarters should be based on what team(s) the hero is part of.
• I don't want to copy LDAP data into SQL where it might get out of sync with the LDAP data.

I've tried things like having the link table just be a basic relationship, but then the link object gets handed around as is and can be updated directly, which I don't want.
I feel like I should be able to make a pydantic object representing the LDAP group, and the linking model would be able to match the group_id in the table to that pydantic object object so then TeamHeadquarters.allowed_groups would be a list of those objects. I haven't been able to figure this out, and I feel like there's something simple I'm missing here.

An option that I haven't completely taken off the table is PostgreSQL's foreign data wrappers, but at least the LDAP ones seem rather abandoned, and I think they're kind of overkill in this case.

Operating System

Linux

Operating System Details

Latest Debian, don't think this is really relevant

SQLModel Version

0.0.8

Python Version

Python 3.11.2

Additional Context

No response