Using SQLite database fails tests related to UUID

[slkoo-cc]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched in the documentation/README.
• I already searched in Google "How to do X" and didn't find any information.
• I already read and followed all the tutorial in the docs/README and didn't find an answer.

Commit to Help

• I commit to help with one of those options 👆

Description

First fix the passlib error by changing backend/pyproject.toml from "passlib[bcrypt]<2.0.0,>=1.7.4", to "libpass<2.0.0", temporarily. Run uv sync to update the dependencies. Run the steps according to .github/workflows/test-backend.yml to see that only the last 2 test failed with PostgreSQL:

tests/scripts/test_backend_pre_start.py F
tests/scripts/test_test_pre_start.py F

Steps to reproduce bug (keeping the passlib fix above):

1. In backend/app/core/config.py, replace the following lines:

POSTGRES_SERVER: str
    POSTGRES_PORT: int = 5432
    POSTGRES_USER: str
    POSTGRES_PASSWORD: str = ""
    POSTGRES_DB: str = ""

    @computed_field  # type: ignore[prop-decorator]
    @property
    def SQLALCHEMY_DATABASE_URI(self) -> PostgresDsn:
        return PostgresDsn.build(
            scheme="postgresql+psycopg",
            username=self.POSTGRES_USER,
            password=self.POSTGRES_PASSWORD,
            host=self.POSTGRES_SERVER,
            port=self.POSTGRES_PORT,
            path=self.POSTGRES_DB,
        )

and also self._check_default_secret("POSTGRES_PASSWORD", self.POSTGRES_PASSWORD).
Replace with:

    SQLITE_FILE: str = "app.sqlite"

    @computed_field  # type: ignore[prop-decorator]
    @property
    def SQLALCHEMY_DATABASE_URI(self) -> str:
        return f"sqlite:///{self.SQLITE_FILE}"

2. Delete everything inside backend/app/alembic/versions and run uv run alembic revision --autogenerate to regenerate new migrations for SQLite.
3. Run the same steps as in .github/workflows/test-backend.yml:

uv run bash scripts/prestart.sh
uv run bash scripts/tests-start.sh

Result

See that backend/tests/api/routes/test_items.py, test_private.py and test_users.py that was passing when using PostgreSQL is now failing:

FAILED tests/api/routes/test_items.py::test_create_item - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_items.py::test_read_item - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_items.py::test_read_item_not_found - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_items.py::test_read_item_not_enough_permissions - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_items.py::test_read_items - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_items.py::test_update_item - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_items.py::test_update_item_not_found - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_items.py::test_update_item_not_enough_permissions - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_items.py::test_delete_item - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_items.py::test_delete_item_not_found - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_items.py::test_delete_item_not_enough_permissions - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_login.py::test_use_access_token - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_private.py::test_create_user - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_users.py::test_get_users_superuser_me - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_users.py::test_get_users_normal_user_me - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_users.py::test_create_user_new_email - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_users.py::test_get_existing_user - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_users.py::test_get_existing_user_current_user - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_users.py::test_get_existing_user_permissions_error - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_users.py::test_create_user_existing_username - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_users.py::test_create_user_by_normal_user - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_users.py::test_retrieve_users - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_users.py::test_update_user_me - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_users.py::test_update_password_me - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_users.py::test_update_password_me_incorrect_password - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_users.py::test_update_user_me_email_exists - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_users.py::test_update_password_me_same_password_error - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_users.py::test_update_user - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_users.py::test_update_user_not_exists - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_users.py::test_update_user_email_exists - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_users.py::test_delete_user_me - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_users.py::test_delete_user_me_as_superuser - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_users.py::test_delete_user_super_user - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_users.py::test_delete_user_not_found - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_users.py::test_delete_user_current_super_user_error - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
FAILED tests/api/routes/test_users.py::test_delete_user_without_privileges - sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'

This seem to be fixable by changing backend/app/api/deps.py line 41 from user = session.get(User, token_data.sub) to:

    import uuid
    user = session.get(User, uuid.UUID(token_data.sub))

but SQLModel in docs (https://sqlmodel.tiangolo.com/advanced/uuid/#create-a-record-with-a-uuid) advertise that selecting UUID in SQLite using string will work? Bumping SQLModel version manually to 0.0.27 in pyproject.toml and syncing doesn't help.

Operating System

Linux, Windows

Operating System Details

Linux machine provided by GitHub workspace, Windows 11

Python Version

3.12.*

Additional Context

No response