Issue between Alembic migration and GUID

[johnsxiong]

I have authorized, but it show Error here

[frankie567]

Could you provide more details?

Which database backend are you using? Which authentication backend?

Could you provide your fastapi-users configuration?

[johnsxiong]

import databases
import sqlalchemy
from fastapi import FastAPI, Request
from fastapi_users import FastAPIUsers, models
from fastapi_users.authentication import JWTAuthentication
from fastapi_users.db import SQLAlchemyBaseUserTable, SQLAlchemyUserDatabase
from sqlalchemy import Column, String
from sqlalchemy.ext.declarative import DeclarativeMeta, declarative_base

DATABASE_URL = "mysql://root:[email protected]:3306/fast_api_test"
SECRET = "SECRET"


class User(models.BaseUser):
    id: int = None


class UserCreate(models.BaseUserCreate):
    pass


class UserUpdate(User, models.BaseUserUpdate):
    pass


class UserDB(User, models.BaseUserDB):
    id: int = None


database = databases.Database(DATABASE_URL)
Base: DeclarativeMeta = declarative_base()


class UserTable(Base, SQLAlchemyBaseUserTable):
    id = Column(String(40), primary_key=True, comment='UUID')


engine = sqlalchemy.create_engine(
    DATABASE_URL
)
Base.metadata.create_all(engine)

users = UserTable.__table__
user_db = SQLAlchemyUserDatabase(UserDB, database, users)


def on_after_register(user: UserDB, request: Request):
    print(f"User {user.id} has registered.")


def on_after_forgot_password(user: UserDB, token: str, request: Request):
    print(f"User {user.id} has forgot their password. Reset token: {token}")


jwt_authentication = JWTAuthentication(
    secret=SECRET, lifetime_seconds=3600, tokenUrl="/auth/jwt/login"
)

app = FastAPI()
fastapi_users = FastAPIUsers(
    user_db,
    [jwt_authentication],
    User,
    UserCreate,
    UserUpdate,
    UserDB,
)
app.include_router(
    fastapi_users.get_auth_router(jwt_authentication), prefix="/auth/jwt", tags=["auth"]
)
app.include_router(
    fastapi_users.get_register_router(on_after_register), prefix="/auth", tags=["auth"]
)
app.include_router(
    fastapi_users.get_reset_password_router(
        SECRET, after_forgot_password=on_after_forgot_password
    ),
    prefix="/auth",
    tags=["auth"],
)
app.include_router(fastapi_users.get_users_router(), prefix="/users", tags=["users"])


@app.on_event("startup")
async def startup():
    await database.connect()


@app.on_event("shutdown")
async def shutdown():
    await database.disconnect()

Here is my code,I use MySQL,thanks for your reply!

[johnsxiong]

Login in is success

But Error: Unauthorized occured!

[frankie567]

Why did you add this:

class UserTable(Base, SQLAlchemyBaseUserTable):
    id = Column(String(40), primary_key=True, comment='UUID')  # ⬅️ this

SQLAlchemyBaseUserTable already provide the id column. I think this line breaks the user retrieval by id.

[johnsxiong]

I use Alembic migrate the table , it seems Alembic doesn't apply the GUID, So i add this ID

[johnsxiong]

INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 76e12df573f8, init
Traceback (most recent call last):
  File "/Users/johnxiong/.conda/envs/fastapi/bin/alembic", line 8, in <module>
    sys.exit(main())
  File "/Users/johnxiong/.conda/envs/fastapi/lib/python3.8/site-packages/alembic/config.py", line 581, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/Users/johnxiong/.conda/envs/fastapi/lib/python3.8/site-packages/alembic/config.py", line 575, in main
    self.run_cmd(cfg, options)
  File "/Users/johnxiong/.conda/envs/fastapi/lib/python3.8/site-packages/alembic/config.py", line 552, in run_cmd
    fn(
  File "/Users/johnxiong/.conda/envs/fastapi/lib/python3.8/site-packages/alembic/command.py", line 298, in upgrade
    script.run_env()
  File "/Users/johnxiong/.conda/envs/fastapi/lib/python3.8/site-packages/alembic/script/base.py", line 489, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/Users/johnxiong/.conda/envs/fastapi/lib/python3.8/site-packages/alembic/util/pyfiles.py", line 98, in load_python_file
    module = load_module_py(module_id, path)
  File "/Users/johnxiong/.conda/envs/fastapi/lib/python3.8/site-packages/alembic/util/compat.py", line 184, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 783, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "alembic/env.py", line 81, in <module>
    run_migrations_online()
  File "alembic/env.py", line 75, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/Users/johnxiong/.conda/envs/fastapi/lib/python3.8/site-packages/alembic/runtime/environment.py", line 846, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/Users/johnxiong/.conda/envs/fastapi/lib/python3.8/site-packages/alembic/runtime/migration.py", line 522, in run_migrations
    step.migration_fn(**kw)
  File "/Users/johnxiong/PycharmProjects/fast-api/db/alembic/alembic/versions/76e12df573f8_init.py", line 22, in upgrade
    sa.Column('id', fastapi_users.db.sqlalchemy.GUID(), nullable=False),
NameError: name 'fastapi_users' is not defined

[johnsxiong]

Why did you add this:

class UserTable(Base, SQLAlchemyBaseUserTable):
    id = Column(String(40), primary_key=True, comment='UUID')  # ⬅️ this

SQLAlchemyBaseUserTable already provide the id column. I think this line breaks the user retrieval by id.

Hi Frankie, It get work after I removed this line! Thank you!!!

[frankie567]

I've found this, which may be interesting : https://stackoverflow.com/questions/15668115/alembic-how-to-migrate-custom-type-in-a-model

Basically, you have to tweak your Alembic migration file (76e12df573f8_init.py) yourself and either:

• Change fastapi_users.db.sqlalchemy.GUID() to sa.CHAR(32)
• Or import the custom class:

from fastapi_users.db.sqlalchemy import GUID

And then : sa.Column('id', GUID(), nullable=False),

[johnsxiong]

Get it! It is really helpful !!