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)