[sqlalchemy] Re: Custom secondary relation with composite primary keys
Hi John,
Composite primary keys: id + account_id is for multi-tenancy and consistent
and efficient indexes/joins. Multiple customers use the same postgres
database and to the split of their data, the most efficient way that I know
is to add a tenant id (in our case it's account_id) for each table.
Q: Why composite primary keys? Why not just primary key on id?
Usually the cardinality of you tenant_id will be a lot smaller than the
`id` of your table so that should make lookups much faster. Note that only
works if you have the correct order in your constraint - meaning that
tentant id should always be first: Example: constraint your_table_pkey
primary key (account_id, id)
Q: Great, but why not a composite unique index?
That works too, primary key is just a unique index anyway behind the
scenes, but it allows some tools (DataGrip SQL client for example) to work
better by automatically generating the correct query for a join based on
the primary/foreign key constraint for example.
Hope it helps, just remember that the order of columns in an index matters
- if you know you're gonna have a lot of data it's not going to be easy to
change a primary key on a table that already has a lot of data.
On Sunday, April 26, 2020 at 10:43:46 AM UTC-7, John Walker wrote:
>
> Hello Alex,
>
> This is super old, so I don't have a lot of hope.
> But I'm wondering if you could explain a line in your example text.
>
> I'm trying to figure out if I need a data model similar to yours, but I'm
> not sure.
>
> Could you explain the datamodel/biz requirements reasoning behind this
> quote "This is done for a few reasons, but let's say it's done to keep
> everything consistent."
>
> I would kill to know what the "few reasons" are, it might help me big time.
>
> Thanks for any time/help.
> John
>
> On Friday, April 28, 2017 at 7:49:40 PM UTC-6, Alex Plugaru wrote:
>>
>> Hello,
>>
>> There are 3 tables: `*Account*`, `*Role*`, `*User*`. Both `*Role*` and `
>> *User*` have a foreign key `*account_id*` that points to `*Account*`.
>>
>> A user can have multiple roles, hence the `*roles_users*` table which
>> acts as the secondary relation table between `*Role*` and `*User*`.
>>
>> The `*Account*` table is a tenant table for our app, it is used to
>> separate different customers.
>>
>> Note that all tables have (besides `*Account*`) have composite primary
>> keys with `*account_id*`. This is done for a few reasons, but let's say
>> it's done to keep everything consistent.
>>
>> Now if I have a simple secondary relationship (`*User.roles*` - the one
>> that is commented out) all works as expected. Well kind of.. it throws a
>> legitimate warning (though I believe it should be an error):
>>
>>
>> SAWarning: relationship 'User.roles' will copy column role.account_id to
>> column roles_users.account_id, which conflicts with relationship(s):
>> 'User.roles' (copies user.account_id to roles_users.account_id). Consider
>> applying viewonly=True to read-only relationships, or provide a
>> primaryjoin condition marking writable columns with the foreign()
>> annotation.
>>
>> That's why I created the second relation `*User.roles*` - the one that
>> is not commented out. Querying works as expected which has 2 conditions on
>> join and everything. However I get this error when I try to save some roles
>> on the user:
>>
>> sqlalchemy.orm.exc.UnmappedColumnError: Can't execute sync rule for
>> source column 'roles_users.role_id'; mapper 'Mapper|User|user' does not
>> map this column. Try using an explicit `foreign_keys` collection which
>> does not include destination column 'role.id' (or use a viewonly=True
>> relation).
>>
>>
>> As far as I understand it, SA is not able to figure out how to save the
>> secondary because it has a custom `*primaryjoin*` and `*secondaryjoin*`
>> so it proposes to use `*viewonly=True*` which has the effect of just
>> ignoring the roles relation when saving the model.
>>
>> The question is how to save the roles for a user without having to do it
>> by hand (the example is commented out in the code). In the real app we have
>> many secondary relationships and we're saving them in many places. It would
>> be super hard to rewrite them all.
>>
>> Is there a solution to keep using `*User.roles = some_roles*` while
>> keeping the custom `*primaryjoin*` and `*secondaryjoin*` below?
>>
>> The full example using SA 1.1.9:
>>
>>
>> from sqlalchemy import create_engine, Column, Integer, Text, Table,
>> ForeignKeyConstraint, ForeignKey, and_
>> from sqlalchemy.ext.declarative import declarative_base
>> from sqlalchemy.orm import foreign, relationship, Session
>>
>>
>> Base = declarative_base()
>>
>>
>>
>>
>> class Account(Base):
>> __tablename__ = 'account'
>> id = Column(Integer, primary_key=True)
>>
>>
>>
>>
>> roles_users = Table(
>> 'roles_users', Base.metadata,
>> Column('account_id', Integer, primary_key=True),
>> Column('user_id', Integer, primary_key=True
[sqlalchemy] Re: Custom secondary relation with composite primary keys
Hello Alex,
This is super old, so I don't have a lot of hope.
But I'm wondering if you could explain a line in your example text.
I'm trying to figure out if I need a data model similar to yours, but I'm
not sure.
Could you explain the datamodel/biz requirements reasoning behind this
quote "This is done for a few reasons, but let's say it's done to keep
everything consistent."
I would kill to know what the "few reasons" are, it might help me big time.
Thanks for any time/help.
John
On Friday, April 28, 2017 at 7:49:40 PM UTC-6, Alex Plugaru wrote:
>
> Hello,
>
> There are 3 tables: `*Account*`, `*Role*`, `*User*`. Both `*Role*` and `
> *User*` have a foreign key `*account_id*` that points to `*Account*`.
>
> A user can have multiple roles, hence the `*roles_users*` table which
> acts as the secondary relation table between `*Role*` and `*User*`.
>
> The `*Account*` table is a tenant table for our app, it is used to
> separate different customers.
>
> Note that all tables have (besides `*Account*`) have composite primary
> keys with `*account_id*`. This is done for a few reasons, but let's say
> it's done to keep everything consistent.
>
> Now if I have a simple secondary relationship (`*User.roles*` - the one
> that is commented out) all works as expected. Well kind of.. it throws a
> legitimate warning (though I believe it should be an error):
>
>
> SAWarning: relationship 'User.roles' will copy column role.account_id to
> column roles_users.account_id, which conflicts with relationship(s):
> 'User.roles' (copies user.account_id to roles_users.account_id). Consider
> applying viewonly=True to read-only relationships, or provide a
> primaryjoin condition marking writable columns with the foreign()
> annotation.
>
> That's why I created the second relation `*User.roles*` - the one that is
> not commented out. Querying works as expected which has 2 conditions on
> join and everything. However I get this error when I try to save some roles
> on the user:
>
> sqlalchemy.orm.exc.UnmappedColumnError: Can't execute sync rule for
> source column 'roles_users.role_id'; mapper 'Mapper|User|user' does not
> map this column. Try using an explicit `foreign_keys` collection which
> does not include destination column 'role.id' (or use a viewonly=True
> relation).
>
>
> As far as I understand it, SA is not able to figure out how to save the
> secondary because it has a custom `*primaryjoin*` and `*secondaryjoin*`
> so it proposes to use `*viewonly=True*` which has the effect of just
> ignoring the roles relation when saving the model.
>
> The question is how to save the roles for a user without having to do it
> by hand (the example is commented out in the code). In the real app we have
> many secondary relationships and we're saving them in many places. It would
> be super hard to rewrite them all.
>
> Is there a solution to keep using `*User.roles = some_roles*` while
> keeping the custom `*primaryjoin*` and `*secondaryjoin*` below?
>
> The full example using SA 1.1.9:
>
>
> from sqlalchemy import create_engine, Column, Integer, Text, Table,
> ForeignKeyConstraint, ForeignKey, and_
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import foreign, relationship, Session
>
>
> Base = declarative_base()
>
>
>
>
> class Account(Base):
> __tablename__ = 'account'
> id = Column(Integer, primary_key=True)
>
>
>
>
> roles_users = Table(
> 'roles_users', Base.metadata,
> Column('account_id', Integer, primary_key=True),
> Column('user_id', Integer, primary_key=True),
> Column('role_id', Integer, primary_key=True),
>
>
> ForeignKeyConstraint(['user_id', 'account_id'], ['user.id',
> 'user.account_id']),
> ForeignKeyConstraint(['role_id', 'account_id'], ['role.id',
> 'role.account_id']),
> )
>
>
>
>
> class Role(Base):
> __tablename__ = 'role'
> id = Column(Integer, primary_key=True)
> account_id = Column(Integer, ForeignKey('account.id'), primary_key=
> True)
> name = Column(Text)
>
>
> def __str__(self):
> return ''.format(self.id, self.name)
>
>
>
>
> class User(Base):
> __tablename__ = 'user'
> id = Column(Integer, primary_key=True)
> account_id = Column(Integer, ForeignKey('account.id'), primary_key=
> True)
> name = Column(Text)
>
>
> # This works as expected: It saves data in roles_users
> # roles = relationship(Role, secondary=roles_users)
>
>
> # This custom relationship - does not work
> roles = relationship(
> Role,
> secondary=roles_users,
> primaryjoin=and_(foreign(Role.id) == roles_users.c.role_id,
> Role.account_id == roles_users.c.account_id),
> secondaryjoin=and_(foreign(id) == roles_users.c.user_id,
>account_id == roles_users.c.account_id))
>
>
>
>
> engine = create_engine('sqlite:///')
> engine.echo = True
> Base.metadata.create_all(engine)
> session = Session(engine)
>
>
