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),
>> 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 '<Role {} {}>'.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, but I haven't gotten a response there
>> yet so trying here too:
>> https://stackoverflow.com/questions/43690944/sqalchemy-custom-secondary-relation-with-composite-primary-keys
>> Hope it's ok.
>>
>>
>> Thank you for your help,
>> Alex.
>>
>>
>>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/a7b6f703-2769-450f-a155-7141ef72ba58%40googlegroups.com.
