fastapi fastapi-users with Database adapter for SQLModel users table is not created

Question

I was trying to use fastapi users package to quickly Add a registration and authentication system to my FastAPI project which uses the PostgreSQL database. I am using asyncio to be able to create asynchronous functions.

In the beginning, I used only sqlAlchemy and I have tried their example here. And I added those line of codes to my app/app.py to create the database at the starting of the server. and everything worked like a charm. the table users was created on my database.

@app.on_event("startup")
async def on_startup():
    await create_db_and_tables()

Since I am using SQLModel I added FastAPI Users - Database adapter for SQLModel to my virtual en packages. And I added those lines to fastapi_users/db/__init__.py to be able to use the SQL model database.

try:
    from fastapi_users_db_sqlmodel import (  # noqa: F401
        SQLModelBaseOAuthAccount,
        SQLModelBaseUserDB,
        SQLModelUserDatabase,
    )
except ImportError:  # pragma: no cover
    pass

I have also modified app/users.py, to use SQLModelUserDatabase instead of sqlAchemy one.

async def get_user_manager(user_db: SQLModelUserDatabase = Depends(get_user_db)):
    yield UserManager(user_db)

and the app/dp.py to use SQLModelUserDatabase, SQLModelBaseUserDB, here is the full code of app/db.py

import os
from typing import AsyncGenerator

from fastapi import Depends
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.orm import sessionmaker

from fastapi_users.db import SQLModelUserDatabase, SQLModelBaseUserDB
from sqlmodel import SQLModel


from app.models import UserDB

DATABASE_URL = os.environ.get("DATABASE_URL")


engine = create_async_engine(DATABASE_URL)

async_session_maker = sessionmaker(
    engine, class_=AsyncSession, expire_on_commit=False)


async def create_db_and_tables():
    async with engine.begin() as conn:
        await conn.run_sync(SQLModel.metadata.create_all)


async def get_async_session() -> AsyncSession:
    async_session = sessionmaker(
        engine, class_=AsyncSession, expire_on_commit=False
    )
    async with async_session() as session:
        yield session


async def get_user_db(session: AsyncSession = Depends(get_async_session)):
    yield SQLModelUserDatabase(UserDB, session, SQLModelBaseUserDB)

Once I run the code, the table is not created at all. I wonder what could be the issue. I could not understand. Any idea?

Answer 1

I had the same problem, but managed to make it work by making a couple changes

The changes that I needed to make (code is based on the full example in the documentation):

1. In models.py, make UserDB inherit from SQLModelBaseUserDB, User, and add table=True for sqlmodel to create the table:

    class UserDB(SQLModelBaseUserDB, User, table=True):
        pass

It's important that SQLModelBaseUserDB is inherited from first, because otherwise User.id trumps SQLModelBaseUserDB.id and sqlmodel cannot find primary_key column

2. Use SQLModelUserDatabaseAsync in get_user_db, like this (as far as I understand, you don't need to pass in SQLModelBaseUserDB in SQLModelUserDatabase. The third argument is for oauth account model):

async def get_user_db(session: AsyncSession = Depends(get_async_session)):
    yield SQLModelUserDatabaseAsync(UserDB, session)

Answer 2

By the time I posted this question that was the answer I received from one of the maintainer of fastapi-users that made me switch to sqlAlchemy that time, actually I do not know if they officially released sqlModel DB adapter or not

My guess is that you didn't change the UserDB model so that it inherits from the SQLModelBaseUserDB one. It's necessary in order to let SQLModel detect all your models and create them.

You can have an idea of what it should look like in fastapi-users-db-sqlmodel tests: https://github.com/fastapi-users/fastapi-users-db-sqlmodel/blob/3a46b80399f129aa07a834a1b40bf49d08c37be1/tests/conftest.py#L25-L27

Bear in mind though that we didn't officially release this DB adapter; as they are some problems with SQLModel regarding UUID (tiangolo/sqlmodel#25). So you'll probably run into issues.

and here is the GitHub link of the issue: https://github.com/fastapi-users/fastapi-users/discussions/861