How can I add a property to a SQL Model that is not a SQLAlchemy field?

[SergioPereo]

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

from sqlmodel import Field, SQLModel, Relationship
from datetime import datetime

class PermissionHierarchy(str, Enum):
    RESTRICTED = "Restricted"
    READ = "Read"
    EDITION = "Edition"

class Permissions(BaseModel):
    example_config: PermissionHierarchy = PermissionHierarchy.RESTRICTED

class ProfileRoleManifest(SQLModel, table=True):
    profile_id: int | None = Field(default=None, foreign_key="profile.id", primary_key=True)
    role_id: int | None = Field(default=None, foreign_key="role.id", primary_key=True)

class Role(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)

    permissions: Permissions | None

    profiles: list["Profile"] = Relationship(back_populates="roles", link_model=ProfileRoleManifest)

class Profile(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)

    roles: list[Role] = Relationship(back_populates="profiles", link_model=ProfileRoleManifest)

def save_role(tenant_id: int, role: schemas.Role, session: Session) -> models.Role:
    """
    Save a role in the SQL database.
    
    Args:
        tenant_id: The tenant identifier
        role: The role to be saved
        session: The SQL database session
    """
    role = models.Role(**role.model_dump())
    session.add(role)
    session.commit()

    crud.create_role_permissions(tenant_id, role.id, role.permissions)
    
    return role

Description

Problem Description

I have a Role object that includes a permissions property. The structure of these permissions changes frequently, and I want to avoid using a static structure like an Enum or Value Object pattern. Instead, I plan to store the permissions as JSON in a MongoDB collection, which will be dynamically retrieved and saved based on the role's ID.

I’m using SQLModel because I like how it integrates schemas and models, avoiding the need for separate definitions. However, I'm facing a challenge with storing and retrieving the permissions attribute. Since the permissions are stored in MongoDB, I get the following error when trying to include the Permissions schema as a property of the SQLModel:

ValueError: <class 'app.template.schemas.Permissions'> has no matching SQLAlchemy type

Question

Is there a recommended way to integrate MongoDB storage with SQLModel for this kind of use case? Or should I separate the Role between saves into distinct models, managing them independently?

Operating System

Linux

Operating System Details

I'm using WSL2

Distributor ID: Ubuntu
Description: Ubuntu 22.04.4 LTS
Release: 22.04
Codename: jammy

SQLModel Version

0.0.22

Python Version

3.12

Additional Context

No response

[monchin]

Have you ever tried this?

[SergioPereo]

I think that would create a JSON field on the database (not sure tho) and I don't really like them being that way. For what I saw in my lurking when posting this something like:
full_name: str = Field(sa_column=False)
Should theoretically work for that. I didn't try it yet (that's why I didn't post an answer) cause I was in a rush and I solved it using an schema. Probably later I will post a complete answer for this use case.