[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 sqlal
[sqlalchemy] Re: Custom secondary relation with composite primary keys
It worked! Thanks a lot!
On Friday, 28 April 2017 18:49:40 UTC-7, 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)
>
>
> # Create our account
> a = Account()
> session.add(a)
&g
[sqlalchemy] Re: Custom secondary relation with composite primary keys
Hi Mike,
Thanks! I followed your advice and indeed it does work as expected. However
I still get this warning:
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.
I have many m2m tables and there is a huge output of these warnings every
time which is super annoying. Is there a way to tell SA not to complain
about this and only this? I would still like to see other warnings.
Again the full code:
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, joinedload,
remote
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_(id == roles_users.c.user_id,
account_id == roles_users.c.account_id),
secondaryjoin=and_(Role.id == roles_users.c.role_id,
Role.account_id == roles_users.c.account_id))
engine = create_engine('sqlite://')
# engine.echo = True
Base.metadata.create_all(engine)
session = Session(engine)
# Create our account
a1 = Account()
a2 = Account()
session.add(a1)
session.add(a2)
session.commit()
# Create roles
u_role = Role()
u_role.id = 1
u_role.account_id = a1.id
u_role.name = 'user'
session.add(u_role)
m_role = Role()
m_role.id = 2
m_role.account_id = a1.id
m_role.name = 'member'
session.add(m_role)
a2_role = Role()
a2_role.id = 3
a2_role.account_id = a2.id
a2_role.name = 'member'
session.add(a2_role)
session.commit()
# Create 1 user
u = User()
u.id = 1
u.account_id = a1.id
u.name = 'user'
# This does not work
u.roles = [u_role, m_role, a2_role]
session.add(u)
session.commit()
# Works as expected
# i = roles_users.insert()
# i = i.values([
# dict(account_id=a.id, role_id=u_role.id, user_id=u.id),
# dict(account_id=a.id, role_id=m_role.id, user_id=u.id),
# ])
# session.execute(i)
# re-fetch user from db
u = session.query(User).options(joinedload('roles')).first()
for r in u.roles:
print(r)
Thank you!
Alex.
On Friday, 28 April 2017 18:49:40 UTC-7, 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 wo
[sqlalchemy] Custom secondary relation with composite primary keys
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)
# Create our account
a = Account()
session.add(a)
session.commit()
# Create 2 roles
u_role = Role()
u_role.id = 1
u_role.account_id = a.id
u_role.name = 'user'
session.add(u_role)
m_role = Role()
m_role.id = 2
m_role.account_id = a.id
m_role.name = 'member'
session.add(m_role)
session.commit()
# Create 1 user
u = User()
u.id = 1
u.account_id = a.id
u.name = 'user'
# This does not work
# u.roles = [u_role, m_role]
session.add(u)
session.commit()
# Works as expected
i = roles_users.insert()
i = i.values([
dict(account_id=a.id, role_id=u_role.id, user_id=u.id),
dict(account_id=a.id, role_id=m_role.id, user_id=u.id),
])
session.execute(i)
# re-fetch user from db
u = session.query(User).first()
for r in u.roles:
print(r)
FYI: I posted this on SO as well,
