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

> Explore RomM's core database models and discover how SQLAlchemy 2.0 manages game, platform, and user relationships using Mapped annotations and explicit relationship declarations.

- Repository: [The RomM Project/romm](https://github.com/rommapp/romm)
- Tags: internals
- Published: 2026-07-06

---

**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:

```python
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:

```python
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`:

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

```

While `RomUser` defines both sides:

```python
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"`:

```python
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:

```python
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

```python
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

```python
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`](https://github.com/rommapp/romm/blob/main/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`](https://github.com/rommapp/romm/blob/main/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`](https://github.com/rommapp/romm/blob/main/base.py) (declarative base), [`platform.py`](https://github.com/rommapp/romm/blob/main/platform.py), [`rom.py`](https://github.com/rommapp/romm/blob/main/rom.py), [`user.py`](https://github.com/rommapp/romm/blob/main/user.py), and [`permission.py`](https://github.com/rommapp/romm/blob/main/permission.py). Each model inherits from the shared `BaseModel` class defined in [`base.py`](https://github.com/rommapp/romm/blob/main/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.