Core Database Models in RomM: How SQLAlchemy 2.0 Manages Game, Platform, and User Relationships

RomM uses SQLAlchemy 2.0's declarative mapping with a centralized BaseModel class to define type-safe relationships between platforms, ROMs, and users through modern Mapped annotations and explicit relationship() declarations with lazy="raise" loading strategies.

RomM stores its game library metadata and user data using SQLAlchemy 2.0's declarative syntax. The core database models reside in the backend/models package, inheriting from a shared BaseModel that provides common timestamps and type safety. These models form the backbone of RomM's data layer, handling everything from platform-game associations to user-specific ROM progress and permission groups.

The Declarative Base Architecture

BaseModel in backend/models/base.py

All models inherit from BaseModel, a thin wrapper around SQLAlchemy 2.0's DeclarativeBase. This base class provides created_at and updated_at timestamp columns and a __repr__ method for debugging. The modern SQLAlchemy 2.0 style uses Mapped[list[Type]] type annotations with mapped_column() declarations instead of the legacy Column syntax, enabling full IDE type checking and autocompletion.

Core Entity Models

Platform Model (backend/models/platform.py)

The Platform model represents gaming consoles like Nintendo Switch or PlayStation. It includes external ID columns (igdb_id, sgdb_id, slug, name) and a one-to-many relationship to Rom defined as:

roms: Mapped[list[Rom]] = relationship(lazy="raise", back_populates="platform")

The lazy="raise" strategy prevents accidental N+1 queries by raising an error if the relationship is accessed without explicit eager loading.

Rom Model (backend/models/rom.py)

The Rom model represents individual game entries with metadata and file information. Key columns include id, platform_id (foreign key to platforms.id), fs_size_bytes, and various metadata fields. It defines the inverse relationship:

platform = relationship("Platform", back_populates="roms")

User Model (backend/models/user.py)

The User model handles authentication, roles, and extensive collections including saves, states, screenshots, rom_users, collections, and devices. Each uses relationship(..., back_populates=...) to maintain bidirectional object graphs. The user links to PermissionGroup via a nullable foreign key with ondelete="SET NULL".

SQLAlchemy 2.0 Relationship Patterns

One-to-Many Platform-to-Rom Relationships

The foreign key resides on the Rom table (platform_id), while Platform declares the collection. When accessing platform.roms, SQLAlchemy generates a SELECT joining the tables. The lazy="raise" flag ensures developers explicitly choose between joinedload, selectinload, or other eager loading strategies rather than triggering implicit lazy loads that cause N+1 issues.

Many-to-Many via Association Object (RomUser)

Instead of a simple secondary table, RomM uses the RomUser association object pattern to store per-user ROM data like ownership flags and playtime. This table contains foreign keys to both users.id and roms.id. In User:

rom_users: Mapped[list[RomUser]] = relationship(lazy="raise", back_populates="user")

While RomUser defines both sides:

user = relationship("User", back_populates="rom_users")
rom = relationship("Rom", back_populates="rom_users")

User-to-PermissionGroup Relationships

Users link to permission groups through a nullable foreign key with ondelete="SET NULL":

permission_group_id = mapped_column(
    ForeignKey("permission_groups.id", ondelete="SET NULL"),
    nullable=True,
    index=True,
)
permission_group: Mapped[PermissionGroup | None] = relationship(lazy="raise")

This allows users to fall back to server-wide defaults when groups are deleted, maintaining referential integrity without cascading deletions to users.

Cascade Deletion Strategies

Child objects like devices, client_tokens, and play_sessions declare cascade="all, delete-orphan" on their relationships. When a user is deleted, SQLAlchemy automatically removes these dependent rows, maintaining referential integrity without relying solely on database-level cascades.

Computed Column Properties

The Platform model uses column_property to compute derived values via subqueries:

rom_count = column_property(
    select(func.count(Rom.id))
    .where(Rom.platform_id == id)
    .correlate_except(Rom)
    .scalar_subquery()
)

This calculates the number of ROMs per platform dynamically while maintaining the SQLAlchemy 2.0 typing system.

Practical Query Examples

Fetching a Platform with Its Games

from models.platform import Platform
from sqlalchemy import select
from utils.database import async_session

async def list_platform_games(slug: str):
    async with async_session() as s:
        stmt = select(Platform).where(Platform.slug == slug)
        platform = (await s.execute(stmt)).scalar_one()
        # Accessing platform.roms requires explicit loading due to lazy="raise"

        for rom in platform.roms:
            print(f"{rom.title} ({rom.id})")

Retrieving User-Specific ROM Data

from models.user import User
from sqlalchemy import select
from utils.database import async_session

async def user_rom_status(user_id: int, rom_id: int):
    async with async_session() as s:
        stmt = (
            select(RomUser)
            .where(RomUser.user_id == user_id, RomUser.rom_id == rom_id)
        )
        link = (await s.execute(stmt)).scalar_one_or_none()
        if link:
            print(f"Owned: {link.owned}, Progress: {link.playtime}")
        else:
            print("User has no entry for this ROM")

Summary

• RomM uses SQLAlchemy 2.0's declarative base with Mapped type annotations in backend/models/base.py
• The Platform and Rom models demonstrate one-to-many relationships with lazy="raise" to prevent N+1 queries
• RomUser acts as an association object for many-to-many user-ROM relationships with per-user metadata
• Cascade deletion is handled via cascade="all, delete-orphan" on user relationships to child objects
• Computed properties like rom_count use column_property with correlated subqueries against the Rom table

Frequently Asked Questions

What is the purpose of lazy="raise" in RomM's relationships?

The lazy="raise" strategy forces developers to explicitly load related data using eager loading techniques like joinedload() or selectinload(). This prevents accidental N+1 query issues that could degrade performance when accessing relationships in loops, ensuring database queries remain explicit and optimized.

How does RomM handle many-to-many relationships between users and ROMs?

RomM uses the association object pattern via the RomUser model, which contains foreign keys to both users.id and roms.id. This allows storing additional per-user data (like ownership status, ratings, and playtime) beyond simple linkage, implemented in the backend/models/rom_user.py file.

Where are the core database models defined in the RomM repository?

The core models are located in the backend/models/ directory, with key files including base.py (declarative base), platform.py, rom.py, user.py, and permission.py. Each model inherits from the shared BaseModel class defined in base.py.

How does the PermissionGroup relationship work in the User model?

Users optionally link to permission groups via a nullable permission_group_id foreign key with ondelete="SET NULL". If a permission group is deleted, affected users automatically revert to NULL rather than being deleted, allowing fallback to default server permissions defined in the application logic.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}