Refactor to use UUID as Primary Key for SQLite

[nanotaboada]

Description

Modify the Player schema to use UUID as the primary key instead of auto-incrementing integers. This will ensure unique ID generation without manual assignment, improve scalability, and provide compatibility with a future PostgreSQL implementation. Additionally, split PlayerModel into separate request and response models to improve API clarity.

Acceptance Criteria

• Update id column in the Player schema to use UUID as the primary key.
• Ensure UUID generation is compatible with both SQLite (current) and PostgreSQL (future).
• Refactor PlayerModel into separate PlayerRequestModel and PlayerResponseModel.
• Update all endpoints to work with the new model structure.
• Ensure existing data is migrated without breaking changes.
• Validate the change by running migrations and tests.

Proposal

The Player schema should be updated to use UUID:

from sqlalchemy import Column, String, Integer, Boolean
from sqlalchemy.dialects.postgresql import UUID as PGUUID
from sqlalchemy.orm import declarative_base
import uuid

class Player(Base):
    """
    SQLAlchemy schema describing a database table of football players.

    Attributes:
        id (UUID): The primary key for the player record (UUID v4).
    """

    __tablename__ = "players"
    
    # UUID column compatible with both SQLite and PostgreSQL
    id = Column(
        PGUUID(as_uuid=True),
        primary_key=True,
        default=uuid.uuid4,
        nullable=False
    )

# skipped remaining code

For SQLite compatibility:

• SQLAlchemy will store UUIDs as strings in SQLite (which doesn't have a native UUID type)
• The PGUUID type with as_uuid=True handles this automatically

For PostgreSQL compatibility:

• PostgreSQL has a native UUID type that will be used directly
• The same column definition works seamlessly when migrating to PostgreSQL
• No code changes needed when switching database backends

Among other things this change will entail splitting PlayerModel into two separate models (request/response):

class PlayerRequestModel(MainModel):
    """
    Pydantic model representing the data required for Create and Update operations on a football Player.

    Attributes:
        first_name (str): The first name of the Player.
        middle_name (Optional[str]): The middle name of the Player, if any.
        last_name (str): The last name of the Player.
        date_of_birth (Optional[str]): The date of birth of the Player, if provided.
        squad_number (int): The unique squad number assigned to the Player.
        position (str): The playing position of the Player.
        abbr_position (Optional[str]): The abbreviated form of the Player's position, if any.
        team (Optional[str]): The team to which the Player belongs, if any.
        league (Optional[str]): The league where the team plays, if any.
        starting11 (Optional[bool]): Indicates if the Player is in the starting 11, if provided.
    """
    first_name: str
    middle_name: Optional[str]
    last_name: str
    date_of_birth: Optional[str]
    squad_number: int
    position: str
    abbr_position: Optional[str]
    team: Optional[str]
    league: Optional[str]
    starting11: Optional[bool]


class PlayerResponseModel(PlayerRequestModel):
    """
    Pydantic model representing a football Player with a UUID for Retrieve operations.

    Attributes:
        id (UUID): The unique identifier for the Player (UUID v4).
    """
    id: UUID  # Changed from int to UUID

Benefits of UUID

• Globally Unique: No collisions across distributed systems or database merges
• Security: Non-sequential IDs prevent enumeration attacks
• Scalability: Generate IDs client-side or in application layer without database round-trips
• PostgreSQL Ready: Native support in PostgreSQL with no migration needed
• Database Agnostic: Works consistently across SQLite, PostgreSQL, MySQL, etc.

Resources

• 🔗 Python UUID Module Documentation
• 🔗 SQLAlchemy 2.0 Documentation - PostgreSQL UUID Type
• 🔗 SQLAlchemy 2.0 Documentation - SQLite Dialect
• 🔗 PostgreSQL UUID Data Type
• 🔗 Best Practices for Using UUIDs as Primary Keys