Modeling a set of enum values in sqlalchemy

Question

The setting

I want to include role-based access control in an application I am currently working on.

In the domain a user may have any subset of a fixed set of roles, which are represented by an enum, similar to the following:

from enum import Enum

class Role(Enum):
    ADMIN = "admin"
    AUTHOR = "author"
    EDITOR = "editor"

(That is, a user may have none of this roles, some, or all of them.)

We are using "classical" mapping, due to architectural choices regarding clean architecture (I ommitted some irrelevant fields):

user_table = db.Table(
    "users",
    metadata,
    Column("id", db.Integer(), primary_key=True, autoincrement=True),
    Column("name", db.String(255), nullable=False),
)
mapper(domain.User, user_table)

roles_table = db.Table(
    "role_associations",
    metadata,
    Column("user_id",
        db.ForeignKey("users.id", ondelete="CASCADE", onupdate="CASCADE"),
        primary_key=True
    ),
    Column("role", db.Enum(Role), primary_key=True),
)

This schema – using a composite primary key for the association table – was chosen over

having a table for roles, since the roles are static
boolean attributes for every role, since there are many roles and users, but users usually only have a handful of roles.

The problem

I want to achieve an association between a User class and a set of Role enumeration members within the framework described above.

What I tried

In the SQLAlchemy documentation I found association proxies, which seemed to be applicable in my situation.

I defined a mapping for the association table (role_assiciations) and installed the association proxy on the User class:

User.roles = association_proxy("_role_associations", "role")

This helps insofar that user.roles does provide me with a list of roles associated to that user.

However, adding a role to a user is not possible. I added a creator:

User.roles = association_proxy("_role_associations", "role", creator=lambda role: role)

I thought that this would be sufficient for adding a role using a command such as:

user.roles.append(Role.ADMIN)

However, this produces the following traceback:

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/project/pip_packages/lib/python3.8/site-packages/sqlalchemy/ext/associationproxy.py", line 1080, in append
    col.append(item)
  File "/project/pip_packages/lib/python3.8/site-packages/sqlalchemy/orm/collections.py", line 1116, in append
    item = __set(self, item, _sa_initiator)
  File "/project/pip_packages/lib/python3.8/site-packages/sqlalchemy/orm/collections.py", line 1081, in __set
    item = executor.fire_append_event(item, _sa_initiator)
  File "/project/pip_packages/lib/python3.8/site-packages/sqlalchemy/orm/collections.py", line 717, in fire_append_event
    return self.attr.fire_append_event(
  File "/project/pip_packages/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line 1183, in fire_append_event
    value = fn(state, value, initiator or self._append_token)
  File "/project/pip_packages/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line 1492, in emit_backref_from_collection_append_event
    child_state, child_dict = instance_state(child), instance_dict(child)
AttributeError: 'Role' object has no attribute '_sa_instance_state'

So I guess, this would only work if Role was a SQLAlchemy mapped class.

Is this the correct approach?
If so, how can I get it to work and can the proxy be modified to be a set?

Is there a better way to achieve the association between users and sets of enum values described above?

zvone · Accepted Answer · 2021-01-02 02:54:42Z

1

Since there may be multiple roles per user, you need a many-to-many relation between users and roles.

That means there should be a table with users, a table with roles and a table with the relation between them. See https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html

user_role_table = Table('user_role', Base.metadata,
    Column('user_id', Integer, ForeignKey('user.id')),
    Column('role_id', Integer, ForeignKey('role.id'))
)

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    # other user data here
    roles = relationship("Role", secondary=user_role_table)

class Role(Base):
    __tablename__ = 'role'
    id = Column(Integer, primary_key=True)
    role = Column(db.Enum(RoleEnum), nullable=False),

What you did is you added a user_id in the roles table, which means that a role can belong to only one user, so you made it a one-to-many relation, instead of many-to-many.

answered Jan 2, 2021 at 2:54

zvone

19.5k5 gold badges53 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

D. A. Over a year ago

I know that this can be modeled as a M-to-N relation, but as I stated in the question, I would prefer not to do that in this case.

zvone Over a year ago

@D.A. The data model dictates the behaviour. You cannot have an M-to-N behaviour with an M-to-1 data model.

D. A. Over a year ago

As it is modeled, it is a one-to-many relationship between pairs of a user (id) as well as a role and a user. For every pair, there is just a single user, but for every user, there may be multiple such pairs. Please note that the key of the association table is made up of both columns therein.

zvone Over a year ago

@D.A. In your code, the enum is the primary key. So there can be only 3 rows in that table. Each of them has a user_id foreign key, so there can be only 3 users connected to those roles. The model is completely wrong.

D. A. Over a year ago

No, both columns make up the primary key. Please look at the whole line; the primary_key parameter for the user I'd is at the end of the line. Thus, there may be no identical combinations of users and roles.

|

D. A. · Accepted Answer · 2021-01-02 14:35:48Z

My solution

The approach described in the question is almost working as-is.

The main problem was that I misread what the "intermediary" class used by the creator of the association_proxy is. This refers to the class of which the association proxy accesses the attributes.

Thus, a solution which works on the schema described and provides a modifiable set of roles, is the following:

db.Table(
    "users",
    metadata,
    Column("id", db.Integer(), primary_key=True, autoincrement=True),
    Column("name", db.String(255), nullable=False),
)

mapper(
    domain.User,
    user_table,
    properties={
        "_role_associations": relationship(
            _UserRoleAssociation,
            collection_class=set,
        ),
    },
)

roles_table = db.Table(
    "roles",
    metadata,
    Column(
        "user_id",
        db.ForeignKey("users.id", ondelete="CASCADE", onupdate="CASCADE"),
        primary_key=True,
    ),
    Column("role", db.Enum(UserRole), primary_key=True),
)

mapper(
    _UserRoleAssociation,
    roles_table,
)

domain.User.roles = association_proxy(
    "_role_associations",
    "role",
    creator=lambda role: _UserRoleAssociation(role),
    cascade_scalar_deletes=True,
)

Here, I also changed the collection_type for the relation between users and the user-role-association classes to set, from which the association_proxy function also infers the correct collection type for the association, which is now a set, as would be expected in this domain.

Collectives™ on Stack Overflow

Modeling a set of enum values in sqlalchemy

The setting

The problem

What I tried

2 Answers 2

6 Comments

My solution

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

The setting

The problem

What I tried

2 Answers 2

6 Comments

My solution

Comments

Your Answer

Sign up or log in

Post as a guest

Related