alembic doesn't detect relationship table

Question

I've defined some models with some association tables for m2m relationships:

from itsdangerous import TimedJSONWebSignatureSerializer
from passlib.hash import bcrypt
from sqlalchemy.ext.declarative import declarative_base

import app 
from app import db

Base = declarative_base()

class UserGroupRelationship(Base):

    __tablename__ = 'users_groups'
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), primary_key=True)
    group_id = db.Column(db.Integer, db.ForeignKey('groups.id'), primary_key=True)

class FriendRelationship(Base):
    __tablename__ = u'users_friends'

    id = db.Column(db.Integer, primary_key=True)
    user_left = db.Column(db.Integer, db.ForeignKey('users.id'), primary_key=True)
    user_right = db.Column(db.Integer, db.ForeignKey('users.id'), primary_key=True)


class User(db.Model):

    __tablename__ = u'users'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    email = db.Column(db.String(120), unique=True)
    password = db.Column(db.String(120))
    # ...
    last_login = db.Column(db.DateTime, default=db.func.now())
    friends = db.relationship(FriendRelationship,
            primaryjoin=id==FriendRelationship.user_left,
            backref='friends', lazy='dynamic')
    friends_with = db.relationship(FriendRelationship,
            primaryjoin=id==FriendRelationship.user_right,
            backref='friends_with', lazy='dynamic')


class Group(db.Model):

    __tablename__ = u'groups'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    users = db.relationship(UserGroupRelationship,
            primaryjoin=id==UserGroupRelationship.group_id,
            backref='groups', lazy='dynamic')


class Device(db.Model):
    ''' devices linked to users '''

    __tablename__ = u'devices'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    uuid = db.Column(db.String(50))
    date_added = db.Column(db.DateTime)
    user_id = db.Column(db.Integer, db.ForeignKey('groups.id'))

running alembic revision --autogenerate does generate table for classes inheriting from db.Model but not for the tables used for my m2m relationships.

INFO  [alembic.migration] Context impl PostgresqlImpl.
INFO  [alembic.migration] Will assume transactional DDL.
INFO  [alembic.autogenerate] Detected added table u'groups'
INFO  [alembic.autogenerate] Detected added table u'users'
INFO  [alembic.autogenerate] Detected added table u'devices'
INFO  [alembic.autogenerate] Detected added table u'question'
  Generating /Users/rxdazn/w/xxx/xxx-
  yyy/migrations/versions/4e47aa7f3050_.py...done

My alembic.ini and env.py files are the default ones. I simply import my models init my project's __init__.py

any idea of what could cause this behaviour?

Why do you inherit the association tables from `Base` while your models inherit from `db.Model`? They are all models, right? — Miguel Grinberg, Feb 20 '14 at 15:19
@Miguel I thought relationships were something different than simple models. I mean I thought they shouldn't be models. Inheriting from `db.Model` seems to have solved my problem. Could you please create post it as answer so I can mark my question as solved? Thanks (and thanks for your blog posts too!). — rxdazn, Feb 20 '14 at 21:54
There are two patterns for many-to-many relationships. The [basic one](http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#many-to-many) uses a `Table` object for the association table. The [advanced one](http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#association-object) uses models, and has the advantage that you can store additional columns in the association table. You seem to be using the advanced one, but if you are using Flask-SQLAlchemy then all your models should inherit from `db.Model`, you should not go directly to SQLAlchemy. — Miguel Grinberg, Feb 21 '14 at 05:38
I see, inheriting from db.Model is like using Django's M2M with a `through` attribute. I'm using Flask-SQLAlchemy indeed. — rxdazn, Feb 21 '14 at 06:17
Any improvement on the issue if you change your tables to inherit from `db.Model`? — Miguel Grinberg, Feb 21 '14 at 07:04
Yes it did solve the issue, see this comment http://stackoverflow.com/questions/21906544/alembic-doesnt-detect-relationship-table?noredirect=1#comment33201915_21906544 Create an answer so I can mark it as solved! — rxdazn, Feb 22 '14 at 02:29

score 7 · Answer 1 · edited May 23 '17 at 10:33

(Miguel answered this in the comments. I'll delete this answer if he posts it and someone pokes me in a comment. I only posted it so it could be marked as an answer, as I had the same problem and almost left the page before reading the comments.)

Don't inherit the association tables from Base. All models should inherit from db.Model, like this:

class FriendRelationship(db.Model):

This is why:

There are two patterns for many-to-many relationships. The basic one uses a Table object for the association table. The advanced one uses models, and has the advantage that you can store additional columns in the association table. You seem to be using the advanced one, but if you are using Flask-SQLAlchemy then all your models should inherit from db.Model. You should not go directly to SQLAlchemy.

alembic doesn't detect relationship table

1 Answers1