Adding relationship to tables already created on SQLAlchemy

On this post you could have a small idea how SQLAlchemy works. However, all my study on SQLAlchemy basics was due to a problem I was having that took me a lot of time to figure it out. Since the problem was more complex and didn’t actually fit on the last post, I decided to create a new one dedicated to it, so here it is :)

While I was creating the Native Authenticator I realized that I needed to store some information about my user that wasn’t available in the default User table. Information such as email or password, for instance. The JupuyterHub User class was like this:

# jupyterhub/orm.py

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(Unicode(255), unique=True)

I decided, then, to create a UserInfo table that would store any other information that I wanted. My initial class was like this:

# nativeauthenticator/orm.py 

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class UserInfo(Base):
    __tablename__ = 'users_info'
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String, nullable=False)
    password = Column(String, nullable=False)

Once I had the class ready, all I needed was to add this table to my database. So I added this creation on the __init__ method of my authenticator class. Such as:

# nativeauthenticator/nativeauthenticator.py

class NativeAuthenticator(Authenticator):

    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)

        inspector = inspect(self.db.bind)
        if 'users_info' not in inspector.get_table_names():
            UserInfo.__table__.create(self.db.bind)

Obs: self.db.bind is the SQLAlchemy engine.

I first checked to see if my table isn’t there already (otherwise I would get an error) and, if it isn’t, than I created the table. All was working so far :)

The problem started when I decided to add a relationship between UserInfo and User.

For some reason, I couldn’t make it work. The UserInfo table would never find the User table and I kept getting this error:

NoReferencedTableError: Foreign key associated with column 'product.user_id' could not find table 'user' with which to generate a foreign key to target column 'id'.

I spent some time going through Stack Overflow issues and after getting nowhere I wrote an issue asking for help.

That’s when André asked me if I were using the same Base for both models and, guess what? I wasn’t.

So, the first thing you should do when creating two classes for the same SQLAlchemy engine is to use the same Base in all models. Seems trivial, but it isn’t. So, I fixed this:

# nativeauthenticator/orm.py 

from jupyterhub.orm import Base
from sqlalchemy import Column, Integer, String

class UserInfo(Base):
    __tablename__ = 'users_info'
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String, nullable=False)
    password = Column(String, nullable=False)

After that, you can add the attributes for the relationship on your class

class UserInfo(Base):
    ...
    user_id = Column(Integer, ForeignKey('users.id'))
    user = relationship(User)

Now you can simply add the table and it will work!

However, this way you won’t be able to see which UserInfo instances are connected to your User, because only UserInfo knows User. Thus, you must add a relationship to the User class. Since we are not adding it on the class creation (aka original file), we add it to the class in the moment that you need it:

User.info = relationship(UserInfo, backref='users')

See a full example on how you work with these two classes here:

Done! Everything worked!

Special thanks to André, Yuvi and Pastore for all the help ❤️!

Comments