piccolo migrations new user --auto does not detect changes in user/tables.py

[Numbers00]

Hello, I've copied the entire user app on piccolo-orm and had its route set up in my piccolo.conf.py like so:

APP_REGISTRY = AppRegistry(
    apps=[
        "app.piccolo.apps.user.piccolo_app",
        "app.piccolo.apps.emotion_log.piccolo_app",
        "app.piccolo.apps.trivia.piccolo_app",
        "piccolo_admin.piccolo_app"
    ]
)

The documentation and some discussions here point to this being a viable alternative, especially since what I want to modify are the datatype for id (from Serial to UUID) and password length restrictions (from 6-128 to 8-64). I'm running my system in docker, with litestar as backend framework and the postgres database in another container.

piccolo migrations new user --auto is able to detect the changes in any of my other apps and writes their migration in the proper directories, but not for the user app. Even when adding new columns no changes are still detected. What am I doing wrong? Please tell if there's some other information I need to give, thank you.

[sinisaos]

@Numbers00 Unfortunately it's not documented, but your custom user app must go after the piccolo_admin.piccolo_app like this

APP_REGISTRY = AppRegistry(
    apps=[
        "app.piccolo.apps.emotion_log.piccolo_app",
        "app.piccolo.apps.trivia.piccolo_app",
        "piccolo_admin.piccolo_app", # you can also safely delete piccolo_admin.app
        "app.piccolo.apps.user.piccolo_app", # <- HERE
    ]
)

because if piccolo_admin.piccolo_app is before the custom user app, migrations don't happen because Piccolo Admin uses the default BaseUser from Piccolo ORM
https://github.com/piccolo-orm/piccolo_admin/blob/7f3383bfd403f07e2af4c3c2fd2437380c41360c/piccolo_admin/endpoints.py#L17
After that change, you can make migrations with piccolo migrations new user --auto and the changes will happen. You should also be careful if you want to use Piccolo Admin because Piccolo Admin uses Piccolo API session_auth which will not work because you changed the BaseUser primary key from Serial to UUID. If you want to use session_auth, you have to do the same thing as with the user app. Copy session_auth to your project and make changes to user_id (must be UUID type) and you have to change all user_id type hints from int to UUID in tables.py of session_auth. Make migrations for your custom session_auth app with piccolo migrations new session_auth --auto and then run migration with piccolo migrations forwards session_auth. After that, you will be able to log in to Piccolo Admin with your custom user. Hope that helps.

[Numbers00]

Sorry for the late reply, I changed the ordering of the apps and copied and modified session_auth and added it to APP_REGISTRY as well. It worked and piccolo was able to detect all changes for the BaseUser table but not for the id column.

APP_REGISTRY = AppRegistry(
    apps=[
        "piccolo_admin.piccolo_app",
        "app.piccolo.apps.session_auth.piccolo_app",
        "app.piccolo.apps.user.piccolo_app",
        "app.piccolo.apps.emotion_log.piccolo_app",
        "app.piccolo.apps.trivia.piccolo_app"
    ]
)

I then tried including this in the auto-generated migration file for the user app but forwarding with it results in cannot cast type integer to uuid.

manager.alter_column(
        table_class_name="BaseUser",
        tablename="users",
        column_name="id",
        db_column_name="id",
        params={
            "default": UUID4()
        },
        old_params={
            "null": False,
            "primary_key": True,
            "unique": False,
            "index": False,
            "index_method": IndexMethod.btree,
            "choices": None,
            "db_column_name": "id",
            "secret": False
        },
        column_class=UUID,
        old_column_class=Serial,
        schema=None,
    )

root@5e6b698e7b93:/usr/src/app# poetry run piccolo migrations forwards user
Loading settings from: /usr/src/app/app/utils
👍 4 migrations already complete
⏩ 4 migrations not yet run
🚀 Running 4 migrations:
  - 2019-11-14T21:52:21 [forwards]... ok! ✔️
  - 2020-06-11T21:38:55 [forwards]... ok! ✔️
  - 2021-04-30T16:14:15 [forwards]... ok! ✔️
  - 2024-01-07T10:17:12:475466 [forwards]... The command failed.
cannot cast type integer to uuid
For a full stack trace, use --trace

Looking at another discussion it seems I may have to remove all migrations for the user app and all tables depending on it (which includes the sessions table)? My other tables are able to have a UUID as primary key just fine, so I hope resetting the migrations for both the user and session_auth apps won't break some things in the future. I'll try again next weekend as I'm trying piccolo first on a personal project, thanks for the help!

[sinisaos]

@Numbers00 As written in the discussion you linked, changing the primary key type is problematic. If your project is new with no data and you're just testing Piccolo (you have the option to drop the database and delete the data), the easiest way is to just drop the database, then delete all migrations from your applications, create new migrations for your applications, run the migrations and all should be fine. Good luck.