I can't reproduce any problem.
Filling out your mappings from your first email, these look like:
from sqlalchemy import *
from sqlalchemy.orm import relationship
from datetime import datetime
from sqlalchemy.ext.declarative import declarative_base as db
target_metadata = MetaData()
DeclarativeBase = db(metadata=target_metadata)
users_roles = Table(
'users_roles', target_metadata,
Column('users_id', ForeignKey('users.id'), primary_key=True),
Column('roles_id', ForeignKey('roles.id'), primary_key=True),
)
roles_permissions = Table(
'roles_permissions', target_metadata,
Column('permissions_id', ForeignKey('permissions.id'), primary_key=True),
Column('roles_id', ForeignKey('roles.id'), primary_key=True),
)
users_permissions = Table(
'users_permissions', target_metadata,
Column('users_id', ForeignKey('users.id'), primary_key=True),
Column('permissions_id', ForeignKey('permissions.id'), primary_key=True),
)
class User(DeclarativeBase):
__tablename__ = 'users'
id = Column(BigInteger, primary_key=True)
email = Column(String(100), unique=True, nullable=False)
name = Column(String(100), nullable=False)
hashed_password = Column(String(100), nullable=False)
is_admin = Column(BOOLEAN, default=False)
is_active = Column(BOOLEAN, default=True)
created = Column(DateTime, default=datetime.now)
modified = Column(DateTime, default=datetime.now, onupdate=datetime.now)
roles = relationship('Role', secondary=users_roles,
back_populates='users', cascade="all, delete-orphan")
permissions = relationship('Permission',
secondary=users_permissions, back_populates='users',
cascade="all, delete-orphan")
def __repr__(self):
return "<User(name='%s', email='%s', hashed_password='%s')>" %
(self.name, self.email, self.hashed_password)
class Role(DeclarativeBase):
__tablename__ = 'roles'
id = Column(BigInteger, primary_key=True)
name = Column(String(100), unique=True, nullable=False)
users = relationship('User', secondary=users_roles,
back_populates='roles',cascade="all, delete-orphan")
permissions = relationship('Permission',
secondary=roles_permissions, back_populates='roles',
cascade="all, delete-orphan")
def __repr__(self):
return "<Role(name='%s')>" % self.name
class Permission(DeclarativeBase):
__tablename__ = 'permissions'
id = Column(BigInteger, primary_key=True)
name = Column(String(100), unique=True, nullable=False)
description = Column(String(255))
users = relationship('User', secondary=users_permissions,
back_populates='permissions',
cascade="all, delete-orphan")
roles = relationship('Role', secondary=roles_permissions,
back_populates='permissions',
cascade="all, delete-orphan")
def __repr__(self):
return "<Permission(name='%s', description='%s')>" %
(self.name, self.description)
then i run autogenerate, migrations are generated as:
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('permissions',
sa.Column('id', sa.BigInteger(), nullable=False),
sa.Column('name', sa.String(length=100), nullable=False),
sa.Column('description', sa.String(length=255), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name')
)
op.create_table('roles',
sa.Column('id', sa.BigInteger(), nullable=False),
sa.Column('name', sa.String(length=100), nullable=False),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name')
)
op.create_table('users',
sa.Column('id', sa.BigInteger(), nullable=False),
sa.Column('email', sa.String(length=100), nullable=False),
sa.Column('name', sa.String(length=100), nullable=False),
sa.Column('hashed_password', sa.String(length=100), nullable=False),
sa.Column('is_admin', sa.BOOLEAN(), nullable=True),
sa.Column('is_active', sa.BOOLEAN(), nullable=True),
sa.Column('created', sa.DateTime(), nullable=True),
sa.Column('modified', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('email')
)
op.create_table('roles_permissions',
sa.Column('permissions_id', sa.BigInteger(), nullable=False),
sa.Column('roles_id', sa.BigInteger(), nullable=False),
sa.ForeignKeyConstraint(['permissions_id'], ['permissions.id'], ),
sa.ForeignKeyConstraint(['roles_id'], ['roles.id'], ),
sa.PrimaryKeyConstraint('permissions_id', 'roles_id')
)
op.create_table('users_permissions',
sa.Column('users_id', sa.BigInteger(), nullable=False),
sa.Column('permissions_id', sa.BigInteger(), nullable=False),
sa.ForeignKeyConstraint(['permissions_id'], ['permissions.id'], ),
sa.ForeignKeyConstraint(['users_id'], ['users.id'], ),
sa.PrimaryKeyConstraint('users_id', 'permissions_id')
)
op.create_table('users_roles',
sa.Column('users_id', sa.BigInteger(), nullable=False),
sa.Column('roles_id', sa.BigInteger(), nullable=False),
sa.ForeignKeyConstraint(['roles_id'], ['roles.id'], ),
sa.ForeignKeyConstraint(['users_id'], ['users.id'], ),
sa.PrimaryKeyConstraint('users_id', 'roles_id')
)
then I run upgrade, SQL script output matches what you specified:
INFO [alembic.runtime.migration] Running upgrade -> 678e61b9e311, rev1
INFO [sqlalchemy.engine.base.Engine]
CREATE TABLE permissions (
id BIGSERIAL NOT NULL,
name VARCHAR(100) NOT NULL,
description VARCHAR(255),
PRIMARY KEY (id),
UNIQUE (name)
)
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine]
CREATE TABLE roles (
id BIGSERIAL NOT NULL,
name VARCHAR(100) NOT NULL,
PRIMARY KEY (id),
UNIQUE (name)
)
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine]
CREATE TABLE users (
id BIGSERIAL NOT NULL,
email VARCHAR(100) NOT NULL,
name VARCHAR(100) NOT NULL,
hashed_password VARCHAR(100) NOT NULL,
is_admin BOOLEAN,
is_active BOOLEAN,
created TIMESTAMP WITHOUT TIME ZONE,
modified TIMESTAMP WITHOUT TIME ZONE,
PRIMARY KEY (id),
UNIQUE (email)
)
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine]
CREATE TABLE roles_permissions (
permissions_id BIGINT NOT NULL,
roles_id BIGINT NOT NULL,
PRIMARY KEY (permissions_id, roles_id),
FOREIGN KEY(permissions_id) REFERENCES permissions (id),
FOREIGN KEY(roles_id) REFERENCES roles (id)
)
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine]
CREATE TABLE users_permissions (
users_id BIGINT NOT NULL,
permissions_id BIGINT NOT NULL,
PRIMARY KEY (users_id, permissions_id),
FOREIGN KEY(permissions_id) REFERENCES permissions (id),
FOREIGN KEY(users_id) REFERENCES users (id)
)
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine]
CREATE TABLE users_roles (
users_id BIGINT NOT NULL,
roles_id BIGINT NOT NULL,
PRIMARY KEY (users_id, roles_id),
FOREIGN KEY(roles_id) REFERENCES roles (id),
FOREIGN KEY(users_id) REFERENCES users (id)
)
On Thu, Dec 13, 2018 at 12:18 PM patrick payet <papa...@gmail.com> wrote:
>
> Hi Mike,
> The message has been truncated.
>
> The CREATE TABLE statements is :
> CREATE TABLE users (
> id BIGSERIAL NOT NULL,
> email VARCHAR(100) NOT NULL,
> name VARCHAR(100) NOT NULL,
> hashed_password VARCHAR(100) NOT NULL,
> is_admin BOOLEAN,
> is_active BOOLEAN,
> created TIMESTAMP WITHOUT TIME ZONE,
> modified TIMESTAMP WITHOUT TIME ZONE,
> PRIMARY KEY (id),
> UNIQUE (email)
> )
>
> CREATE TABLE permissions (
> id BIGSERIAL NOT NULL,
> name VARCHAR(100) NOT NULL,
> description VARCHAR(255),
> PRIMARY KEY (id),
> UNIQUE (name)
> )
>
> CREATE TABLE roles (
> id BIGSERIAL NOT NULL,
> name VARCHAR(100) NOT NULL,
> PRIMARY KEY (id),
> UNIQUE (name)
> )
>
> CREATE TABLE users_roles (
> users_id BIGINT NOT NULL,
> roles_id BIGINT NOT NULL,
> PRIMARY KEY (users_id, roles_id),
> FOREIGN KEY(users_id) REFERENCES users (id),
> FOREIGN KEY(roles_id) REFERENCES roles (id)
> )
>
> CREATE TABLE roles_permissions (
> roles_id BIGINT NOT NULL,
> permissions_id BIGINT NOT NULL,
> PRIMARY KEY (roles_id, permissions_id),
> FOREIGN KEY(roles_id) REFERENCES roles (id),
> FOREIGN KEY(permissions_id) REFERENCES permissions (id)
> )
>
> CREATE TABLE users_permissions (
> users_id BIGINT NOT NULL,
> permissions_id BIGINT NOT NULL,
> PRIMARY KEY (users_id, permissions_id),
> FOREIGN KEY(users_id) REFERENCES users (id),
> FOREIGN KEY(permissions_id) REFERENCES permissions (id)
> )
>
> My mistake is to have mixed in my mind the information of the object mapping
> and the relational constraints of the database.
> But why Alembic can’t create this model in auto-generation mode?
>
> Best regards
>
> Le jeu. 13 déc. 2018 à 18:12, patrick payet <papa...@gmail.com> a écrit :
>>
>> PRIMARY KEY (id),
>> UNIQUE (email)
>> )
>>
>> CREATE TABLE permissions (
>> id BIGSERIAL NOT NULL,
>> name VARCHAR(100) NOT NULL,
>> description VARCHAR(255),
>> PRIMARY KEY (id),
>> UNIQUE (name)
>> )
>>
>> CREATE TABLE roles (
>> id BIGSERIAL NOT NULL,
>> name VARCHAR(100) NOT NULL,
>> PRIMARY KEY (id),
>> UNIQUE (name)
>> )
>>
>> CREATE TABLE users_roles (
>> users_id BIGINT NOT NULL,
>> roles_id BIGINT NOT NULL,
>> PRIMARY KEY (users_id, roles_id),
>> FOREIGN KEY(users_id) REFERENCES users (id),
>> FOREIGN KEY(roles_id) REFERENCES roles (id)
>> )
>>
>> CREATE TABLE roles_permissions (
>> roles_id BIGINT NOT NULL,
>> permissions_id BIGINT NOT NULL,
>> PRIMARY KEY (roles_id, permissions_id),
>> FOREIGN KEY(roles_id) REFERENCES roles (id),
>> FOREIGN KEY(permissions_id) REFERENCES permissions (id)
>> )
>>
>> CREATE TABLE users_permissions (
>> users_id BIGINT NOT NULL,
>> permissions_id BIGINT NOT NULL,
>> PRIMARY KEY (users_id, permissions_id),
>> FOREIGN KEY(users_id) REFERENCES users (id),
>> FOREIGN KEY(permissions_id) REFERENCES permissions (id)
>> )
>>
>> My mistake is to have mixed in my mind the information of the object mapping
>> and the relational constraints of the database.
>> But why Alembic can’t create this model in auto-generation mode?
>>
>> Best regards
>>
>> Le jeu. 13 déc. 2018 à 14:44, Mike Bayer <mike...@zzzcomputing.com> a écrit :
>>>
>>> On Thu, Dec 13, 2018 at 7:11 AM patrick payet <papa...@gmail.com> wrote:
>>> >
>>> > Hi Mike,
>>> > Thanks for your answer.
>>> > My problem it’s a classical problem for application development, I want
>>> > to make authentication and authorisation for a web application.
>>> > I want to understand why Alembic can’t create the model in autogeneration
>>> > mode and how modify this model to resolve this problem.
>>> > With this model I have a join table to provide a many-to-many
>>> > relationship between users and roles (users group) for example.
>>> > This allows me to access all roles for one user. For this, I declare a
>>> > variable roles in users class and I want to make a foreign key with this
>>> > join table
>>> > to reach the roles.
>>> > This joins table has a composite primary key (users.id foreign key and
>>> > roles.id foreign key) and I don’t know how to make the link (foreign
>>> > key) with alembic for the users.roles and this composite primary key.
>>>
>>> the link is the user_roles table and the FOREIGN KEY constraints it
>>> has between users and roles tables. you can't locate a unique row
>>> in user_roles given only the user table because it has no column that
>>> refers to user_roles.roles_id. at least I'm trying to parse your
>>> words as given. however CREATE TABLE statements will show exactly
>>> what you mean in case it is something different that just happens to
>>> sound the same when described.
>>>
>>> When I declare just one element of this primary key, I have the error
>>> quoted previously ( there is no unique constraint matching given keys
>>> for referenced table "users_roles").
>>> > I will send you the CREATE TABLE statements by email in a moment.
>>>
>>> yup
>>>
>>>
>>>
>>> > Best regards,
>>> > Patrick
>>> >
>>> > Le mer. 12 déc. 2018 à 19:40, Mike Bayer <mike...@zzzcomputing.com> a
>>> > écrit :
>>> >>
>>> >> On Wed, Dec 12, 2018 at 1:01 PM patrick payet <papa...@gmail.com> wrote:
>>> >> >
>>> >> > I had a SQLAlchemy model like -
>>> >> > <code>
>>> >> > class User(DeclarativeBase):
>>> >> > __tablename__ = 'users'
>>> >> >
>>> >> > id = Column(BigInteger, primary_key=True)
>>> >> > email = Column(String(100), unique=True, nullable=False)
>>> >> > name = Column(String(100), nullable=False)
>>> >> > hashed_password = Column(String(100), nullable=False)
>>> >> > is_admin = Column(BOOLEAN, default=False)
>>> >> > is_active = Column(BOOLEAN, default=True)
>>> >> > created = Column(DateTime, default=datetime.now)
>>> >> > modified = Column(DateTime, default=datetime.now,
>>> >> > onpudate=datetime.datetime.now)
>>> >> > roles = relationship('Role', secondary=users_roles,
>>> >> > back_populates='users', cascade="all, delete-orphan")
>>> >> > permissions = relationship('Permission',
>>> >> > secondary=users_permissions, back_populates='users',
>>> >> > cascade="all, delete-orphan")
>>> >> >
>>> >> > def __repr__(self):
>>> >> > return "<User(name='%s', email='%s', hashed_password='%s')>" %
>>> >> > (self.name, self.email, self.hashed_password)
>>> >> >
>>> >> >
>>> >> > class Role(DeclarativeBase):
>>> >> > __tablename__ = 'roles'
>>> >> >
>>> >> > id = Column(BigInteger, primary_key=True)
>>> >> > name = Column(String(100), unique=True, nullable=False)
>>> >> > users = relationship('User', secondary=users_roles,
>>> >> > back_populates='roles',cascade="all, delete-orphan")
>>> >> > permissions = relationship('Permission',
>>> >> > secondary=roles_permissions, back_populates='roles',
>>> >> > cascade="all, delete-orphan")
>>> >> >
>>> >> > def __repr__(self):
>>> >> > return "<Role(name='%s')>" % self.name
>>> >> >
>>> >> >
>>> >> > class Permission(DeclarativeBase):
>>> >> > __tablename__ = 'permissions'
>>> >> >
>>> >> > id = Column(BigInteger, primary_key=True)
>>> >> > name = Column(String(100), unique=True, nullable=False)
>>> >> > description = Column(String(255))
>>> >> > users = relationship('User', secondary=users_permissions,
>>> >> > back_populates='permissions',
>>> >> > cascade="all, delete-orphan")
>>> >> > roles = relationship('Role', secondary=roles_permissions,
>>> >> > back_populates='permissions',
>>> >> > cascade="all, delete-orphan")
>>> >> >
>>> >> > def __repr__(self):
>>> >> > return "<Permission(name='%s', description='%s')>" %
>>> >> > (self.name, self.description)
>>> >> > </code>
>>> >> > However, Alembic is not generating the correct upgrade, i try to make
>>> >> > it
>>> >> > <code>
>>> >> >
>>> >> > def upgrade():
>>> >> > # ### commands auto generated by Alembic - please adjust! ###
>>> >> > # op.drop_table('users')
>>> >> > # ### end Alembic commands ###
>>> >> >
>>> >> > op.create_table('roles',
>>> >> > sa.Column('id', sa.BIGINT(), autoincrement=True,
>>> >> > nullable=False),
>>> >> > sa.Column('name', sa.String(100),
>>> >> > autoincrement=False, nullable=False),
>>> >> > sa.Column('users', BigInteger),
>>> >> > sa.Column('permissions', BigInteger),
>>> >> > sa.PrimaryKeyConstraint('id'),
>>> >> > sa.UniqueConstraint('name', name='roles_name_key')
>>> >> > # sa.ForeignKeyConstraint(['users'],
>>> >> > ['users_roles.users_id'], ondelete='CASCADE'),
>>> >> > # sa.ForeignKeyConstraint(['permissions'],
>>> >> > ['roles_permissions.permissions_id'], ondelete='CASCADE')
>>> >> > )
>>> >> > op.create_table('permissions',
>>> >> > sa.Column('id', sa.BIGINT(), autoincrement=True,
>>> >> > nullable=False),
>>> >> > sa.Column('name', sa.String(100),
>>> >> > autoincrement=False, nullable=False),
>>> >> > sa.Column('description', sa.String(255)),
>>> >> > sa.Column('users', BigInteger),
>>> >> > sa.Column('roles', BigInteger),
>>> >> > sa.PrimaryKeyConstraint('id')
>>> >> > # sa.ForeignKeyConstraint(['users'],
>>> >> > ['users_permissions.users_id'], ondelete='CASCADE'),
>>> >> > # sa.ForeignKeyConstraint(['roles'],
>>> >> > ['role_permissions.roles_id'], ondelete='CASCADE')
>>> >> > )
>>> >> > op.create_table('users_roles',
>>> >> > sa.Column('users_id', BigInteger,
>>> >> > sa.ForeignKey('users.id'), primary_key=True),
>>> >> > sa.Column('roles_id', BigInteger,
>>> >> > sa.ForeignKey('roles.id'), primary_key=True)
>>> >> > )
>>> >> > op.create_table('users_permissions',
>>> >> > sa.Column('users_id', BigInteger,
>>> >> > sa.ForeignKey('users.id'), primary_key=True),
>>> >> > sa.Column('permissions_id', BigInteger,
>>> >> > sa.ForeignKey('permissions.id'), primary_key=True)
>>> >> > )
>>> >> > op.create_table('roles_permissions',
>>> >> > sa.Column('roles_id', BigInteger,
>>> >> > sa.ForeignKey('roles.id'), primary_key=True),
>>> >> > sa.Column('permissions_id', BigInteger,
>>> >> > sa.ForeignKey('permissions.id'), primary_key=True)
>>> >> > )
>>> >> > op.drop_constraint('users_name_key', 'users')
>>> >> > op.add_column('users', sa.Column('is_admin', sa.BOOLEAN,
>>> >> > autoincrement=False, nullable=False, default=False))
>>> >> > op.add_column('users', sa.Column('is_active', sa.BOOLEAN,
>>> >> > autoincrement=False, nullable=False, default=True))
>>> >> > op.add_column('users', sa.Column('created', sa.DateTime,
>>> >> > autoincrement=False, nullable=False,
>>> >> > default=datetime.datetime.now))
>>> >> > op.add_column('users', sa.Column('modified', sa.DateTime,
>>> >> > autoincrement=False, nullable=False,
>>> >> > default=datetime.datetime.now))
>>> >> > op.add_column('users', sa.Column('roles', BigInteger))
>>> >> > op.add_column('users', sa.Column('permissions', BigInteger))
>>> >> >
>>> >> > op.create_foreign_key(constraint_name="users_roles_fk",
>>> >> > source_table="users", referent_table="users_roles",
>>> >> > local_cols=["roles"],
>>> >> > remote_cols=["roles_id"], ondelete='CASCADE')
>>> >> > op.create_foreign_key("users_permissions_fk", "users",
>>> >> > "users_permissions", ["permissions"], ["permissions_id"],
>>> >> > ondelete='CASCADE')
>>> >> > op.create_foreign_key("permissions_users_fk", "permissions",
>>> >> > "users_permissions", ["users"], ["users_id"],
>>> >> > ondelete='CASCADE')
>>> >> > op.create_foreign_key("permissions_roles_fk", "permissions",
>>> >> > "roles_permissions", ["roles"], ["roles_id"],
>>> >> > ondelete='CASCADE')
>>> >> > op.create_foreign_key("roles_users_fk", "roles", "users_roles",
>>> >> > ["users"], ["users_id"],
>>> >> > ondelete='CASCADE')
>>> >> > op.create_foreign_key("roles_permissions_fk", "roles",
>>> >> > "roles_permissions", ["permissions"], ["permissions_id"],
>>> >> > ondelete='CASCADE')
>>> >> > </code>
>>> >> > i have an error, primary key for users_roles is a composite keys and i
>>> >> > dont know how declare it, how make that
>>> >>
>>> >> it's not clear here if you're saying alembic is adding foreign keys
>>> >> where they dont belong during autogenerate, or if you dont know why
>>> >> you are getting the error you are getting. The error is because you
>>> >> don't need FOREIGN KEY constraints on roles, permissions, or users -
>>> >> the FOREIGN KEY is only on the many-to-many tables, in this case
>>> >> users_roles, users_permissions, roles_permissions.
>>> >>
>>> >> otherwise if you can illustrate the exact CREATE TABLE statements
>>> >> you'd like to see, I can illustrate a correct model for that which
>>> >> Alembic will generate.
>>> >>
>>> >>
>>> >>
>>> >> > <code>
>>> >> > sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) there is
>>> >> > no unique constraint matching given keys for referenced table
>>> >> > "users_roles"
>>> >> > [SQL: 'ALTER TABLE users ADD CONSTRAINT users_roles_fk FOREIGN
>>> >> > KEY(roles) REFERENCES users_roles (roles_id) ON DELETE CASCADE']
>>> >> > (Background on this error at: http://sqlalche.me/e/f405)
>>> >> > </code>
>>> >> >
>>> >> > --
>>> >> > You received this message because you are subscribed to the Google
>>> >> > Groups "sqlalchemy-alembic" group.
>>> >> > To unsubscribe from this group and stop receiving emails from it, send
>>> >> > an email to sqlalchemy-alembic+unsubscr...@googlegroups.com.
>>> >> > For more options, visit https://groups.google.com/d/optout.
>>> >>
>>> >> --
>>> >> You received this message because you are subscribed to the Google
>>> >> Groups "sqlalchemy-alembic" group.
>>> >> To unsubscribe from this group and stop receiving emails from it, send
>>> >> an email to sqlalchemy-alembic+unsubscr...@googlegroups.com.
>>> >> For more options, visit https://groups.google.com/d/optout.
>>> >
>>> > --
>>> > You received this message because you are subscribed to the Google Groups
>>> > "sqlalchemy-alembic" group.
>>> > To unsubscribe from this group and stop receiving emails from it, send an
>>> > email to sqlalchemy-alembic+unsubscr...@googlegroups.com.
>>> > For more options, visit https://groups.google.com/d/optout.
>>>
>>> --
>>> You received this message because you are subscribed to the Google Groups
>>> "sqlalchemy-alembic" group.
>>> To unsubscribe from this group and stop receiving emails from it, send an
>>> email to sqlalchemy-alembic+unsubscr...@googlegroups.com.
>>> For more options, visit https://groups.google.com/d/optout.
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy-alembic" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy-alembic+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
