How to add new database models that have relationships with User?

[rbracco]

Hey @frankie567, thanks again for your work, I read a lot of past issues trying to get to the bottom of this and your replies were helpful.

I am using fastapi-sqlalchemy-alembic and I'm trying to build relationships between the User and some items on my site, but I'm having trouble knowing how to build relationships between the various fastapi_users classes (User, UserDB, and UserTable) classes. Code (mostly working) and description are below, hopefully it will help someone in the same spot. My question...

1. What is the difference between UserDB and UserTable? What info should I be passing to UserDB here? I've noticed that a UserDB instance is what actually gets injected in my route.
2. Do I need to do anything in User or UserCreate to make this work properly? I'm at a prototype stage so I can nuke my database and restart very easily. Thank you.

Code

I have a very basic class AudioFile such that one user can have many audiofiles. As is, when the route is hit it creates a new audiofile that links back to the user by id.

# app.py
def create_file(
    request: Request,
    file: UploadFile = File(...),
    target: str = Form(...),
    user: User = Depends(fastapi_users.current_user()),
    db: Session = Depends(get_db),
):
    to_create = AudioFile(prompt=target, user_id=user.id)
    db.add(to_create)
    db.commit()

    #Querying my AudioFiles to make sure it is connected to the user, this works
    for row in db.query(AudioFile):
            print(row)
            print(row.user_id)
            print("User", row.user)
    #Querying my user (userTable) to make sure it is connected to the AudioFiles, this works
    for row in db.query(UserTable):
            print(row)
            print(row.country)
            print(row.audiofiles)

#models.py
class AudioFile(Base):
    __tablename__ = "audiofiles"
    id = Column(Integer, primary_key=True)
    user_id = Column(GUID(), ForeignKey("user.id"))
    user = relationship("UserTable", back_populates="audiofiles")
    prompt = Column(String, nullable=False)

    def __repr__(self):
        return f"{self.user_id} saying {self.prompt}"

class User(models.BaseUser):
    language: str
    country: str

    class Config:
        orm_mode = True


class UserCreate(models.BaseUserCreate):
    language: str
    country: str

    @validator("password")
    def valid_password(cls, v: str):
        if len(v) < 6:
            raise ValueError("Password should be at least 6 characters")
        return v


class UserUpdate(User, models.BaseUserUpdate):
    pass


class UserDB(User, models.BaseUserDB):
    pass


class UserTable(Base, SQLAlchemyBaseUserTable):
    __tablename__ = "user"

    language = Column(String(100))
    country = Column(String(100))
    audiofiles = relationship("AudioFile", back_populates="user")

Possible addition to docs?

I think this could be a great addition to the docs as most sites with Users also link those users to some type of separate data objects.A full example that includes a few custom user properties like "country" and also some objects that have relationships with the user models (maybe a chatroom -> 1 object many users) and a photo -> many objects, one user would be awesome. I'd be happy to submit a docs PR (for SQLAlchemy at least) once I get this all straight in my head and understand the pieces. Cheers.

[frankie567]

Hi @rbracco!

What is the difference between UserDB and UserTable?

The UserDB is the model with all the fields that should exist in your database to help you with your logic, but that shouldn't be shown to the end-user. Typically, the hashed_password field. When using the dependency injection, FastAPI Users will always give you the BaseUserDB, so that you have all the data to do what you need.

UserDB is a Pydantic model. This is how FastAPI handle data validation and data serialization.

UserTable is a table definition for SQLAlchemy (https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/basic_use.html#basic-use).

➡️ You should also have Pydantic model for your AudioFile ; it'll greatly help you to validate/serialize this data in FastAPI. Read: https://fastapi.tiangolo.com/tutorial/sql-databases/?h=sql#create-the-pydantic-models

About the relationship

It seems the relationship is working here, and that your foreign key is correctly inserted in your database. The problem here is that FastAPI Users will return only the data for the users table ; he won't be able to automatically do the relationship query. So, you'll always get a UserDB without audiofiles list.

What you could do:

• When you want to retrieve the audio files, just make the query yourself. It's quite easy to wrap it in a dependency to re-use it:

def get_user_audio_files(user: UserDB = Depends(fastapi_users.current_user(active=True))):
    return db.query(AudioFile).filter_by(user_id=user.id).all()

• Overload the get/_make_user methods of SQLAlchemyUserDatabase (https://github.com/frankie567/fastapi-users/blob/master/fastapi_users/db/sqlalchemy.py) to add your relationship query and return the user with audio files attached.

[rbracco]

Thank you so much, this was extremely helpful and I understand how it all fits together now.. I created a schemas.py and added the pydantic models as well as some more complex relationships and it's all working great. Cheers.

[frankie567]

Glad to have helped :) Thank you again for the sponsorship, truly appreciated 👍👍👍