Map a data model#

This guide maps the SQLAlchemy models of an application to SCIM resources, case by case. It is for developers who have followed the Overview and map a model that it does not cover: an extension, a computed value, a custom resource type, or tables with other identifiers. It does not cover the choice of the schemas the server publishes: for this choice, read Publish the stored attributes.

The examples extend the intranet of the Overview.

Prepare the SQLAlchemy model#

Give each SQLAlchemy model the columns the storage needs:

  • a primary key, mapped to id;

  • a creation date and a modification date, mapped to meta.created and meta.lastModified;

  • an integer version counter, declared as the version_id_col of the mapper, and passed as the version of the mapping.

A ResourceMapping without one of them raises a ValueError. The project model of the intranet has these columns:

class ProjectRecord(Base):
    __tablename__ = "projects"

    id: Mapped[uuid.UUID] = mapped_column(primary_key=True, default=uuid.uuid4)
    code: Mapped[str] = mapped_column(unique=True)
    title: Mapped[str | None]
    created_at: Mapped[datetime.datetime]
    updated_at: Mapped[datetime.datetime]
    revision: Mapped[int] = mapped_column()
    owner_id: Mapped[uuid.UUID | None] = mapped_column(ForeignKey("accounts.id"))

    owner: Mapped[Account | None] = relationship()
    members: Mapped[list[Account]] = relationship(secondary=project_members)

    __mapper_args__ = {"version_id_col": revision}

Per RFC 7643 §3.1, two resources never share an id, even of different types. A search at the root of the server returns the resources of every type together, and clients tell them apart by their id. The integers of an autoincrement column start again in each table, so they break this rule as soon as the server serves two resource types. Use a UUID, with the Uuid type, or a sequence that every table shares.

The primary key may be an integer, a string or a UUID, and the storage returns it as a string. Give its column a default, such as default=uuid.uuid4, a server default, or an autoincrement, so that the database fills the key of each new resource.

Map a column#

Map an attribute to a column of the model. Map a complex attribute holding a single value, such as name, by its sub-attributes:

{
    "userName": Account.login,
    "name.givenName": Account.first_name,
    "name.familyName": Account.last_name,
}

A path to a complex attribute alone, such as name, raises a ValueError. Leave out the attributes the model has no column for: the storage refuses a value for them. To keep clients from sending such values, read Publish the stored attributes.

Map an extension#

Map the attribute of an extension by its full path, the URN of the schema then the attribute name. The mapping takes the model with its extensions, such as User[Workplace]:

class Workplace(Extension):
    __schema__ = URN("urn:example:params:scim:schemas:extension:intranet:2.0:User")
    office: str | None = None


ACCOUNTS = ResourceMapping(
    User[Workplace],
    Account,
    {
        "id": Account.id,
        "userName": Account.login,
        "urn:example:params:scim:schemas:extension:intranet:2.0:User:office": Account.office,
        "meta.created": Account.created_at,
        "meta.lastModified": Account.updated_at,
    },
    version=Account.revision,
)

Map a computed value#

Map an attribute to a hybrid_property when the model computes its value. The storage reads the property, and filters and sorts with its expression. Without a setter, the attribute is read-only: the storage never writes it, and refuses a different value with a 400 error. The displayName of the intranet joins the first and the last name:

@hybrid_property
def full_name(self):
    return f"{self.first_name or ''} {self.last_name or ''}".strip() or None

@full_name.expression
def full_name(cls):
    return func.nullif(
        func.trim(
            func.coalesce(cls.first_name, "")
            + " "
            + func.coalesce(cls.last_name, "")
        ),
        "",
    )

Convert a value#

Map an attribute to a hybrid_property with a setter and an expression when the column holds the value in another form. The getter and the setter convert the value in Python. The expression converts it in SQL, so that filters and sorts compare SCIM values. The following model stores active in a status column, which holds enabled or disabled:

class Account(Base):
    status: Mapped[str | None]

    @hybrid_property
    def active(self):
        return None if self.status is None else self.status == "enabled"

    @active.inplace.setter
    def _active_setter(self, value):
        self.status = None if value is None else ("enabled" if value else "disabled")

    @active.inplace.expression
    @classmethod
    def _active_expression(cls):
        return cls.status == "enabled"

Map the attribute to the property, with "active": Account.active. The filter active eq true then returns the accounts whose status is enabled.

Keep the expression NULL when the column is NULL, as status == "enabled" does. The filter active pr then holds only for the accounts with a status. A request removing the attribute passes None to the setter.

Map a write-only or a read-only attribute#

Wrap a column or a property in an Attribute to limit what the storage does with it:

  • with readable=False, the attribute is write-only: the storage never returns it, nor filters or sorts on it.

  • with writable=False, the attribute is read-only, even when its column or its property can be written.

The intranet hashes the password in the setter of a property:

@hybrid_property
def password(self):
    return self.password_hash

@password.inplace.setter
def _password_setter(self, value):
    self.password_hash = None if value is None else hash_password(value)

The mapping makes the password write-only:

{"password": Attribute(Account.password, readable=False)}

A write-only attribute keeps its stored value when a request does not carry it, since a resource never carries it back.

Map entries of a child table#

Map a multi-valued attribute whose entries belong to the resource, such as emails, with Many. Give it the relationship() to the child model, and the column of each sub-attribute:

{
    "emails": Many(
        Account.emails,
        {
            "value": EmailAddress.address,
            "type": EmailAddress.kind,
            "primary": EmailAddress.preferred,
        },
    )
}

Declare the relationship with cascade="all, delete-orphan": writing the resource replaces its entries, and the delete-orphan cascade deletes the former ones. A relationship without this cascade raises a ValueError. The order_by of the relationship gives the order of the entries. A sort on the attribute uses the entry marked primary, or else the first entry.

Map a custom resource type#

Map a resource type of the application the same way as a standard one. Declare its SCIM model first: for the steps, read Define custom SCIM models. The intranet serves its projects, whose owner and members are users:

class UserReference(ComplexAttribute):
    value: Annotated[str | None, Mutability.immutable] = None
    ref: Annotated[Reference["User"] | None, Mutability.immutable] = Field(
        None, serialization_alias="$ref", validation_alias="$ref"
    )
    display: str | None = None


class Project(Resource):
    __schema__ = URN("urn:example:params:scim:schemas:intranet:2.0:Project")
    code: Annotated[str | None, Required.true, Uniqueness.server] = None
    title: str | None = None
    owner: UserReference | None = None
    members: list[UserReference] | None = None

Map it to the project model:

PROJECTS = ResourceMapping(
    Project,
    ProjectRecord,
    {
        "id": ProjectRecord.id,
        "code": ProjectRecord.code,
        "title": ProjectRecord.title,
        "owner": Link(ProjectRecord.owner, "User"),
        "members": Link(ProjectRecord.members, "User"),
        "meta.created": ProjectRecord.created_at,
        "meta.lastModified": ProjectRecord.updated_at,
    },
    version=ProjectRecord.revision,
)

Serve it next to the users. The mappings of the storage are keyed by the name of each resource type:

def create_storage(session, provider):
    return SqlAlchemyStorage(
        {"User": ACCOUNTS, "Project": PROJECTS}, lambda: session, provider=provider
    )

Create a project owned by a user:

>>> from scim2_server.applications.wsgi import WSGIApplication
>>> from sqlalchemy import create_engine
>>> from sqlalchemy.orm import Session
>>> from werkzeug.test import Client
>>> from intranet.models import Base
>>> from intranet.projects import create_provider
>>> from intranet.projects import create_storage

>>> engine = create_engine("sqlite://")
>>> Base.metadata.create_all(engine)
>>> session = Session(engine)
>>> provider = create_provider()
>>> client = Client(WSGIApplication(create_storage(session, provider), provider))
>>> user = client.post("/v2/Users", json={"userName": "bjensen"}).json
>>> project = client.post(
...     "/v2/Projects", json={"code": "APOLLO", "owner": {"value": user["id"]}}
... ).json
>>> project["owner"]
{'value': '...', '$ref': 'http://localhost/v2/Users/...', 'display': 'bjensen'}
>>> session.close()
>>> engine.dispose()

For every parameter of the mapping and of the storage, read Mapping and Storages.