Database Schema Reference¶

Each NostalgiaDB SQLite file contains game metadata for a single gaming platform. The schema maps closely to the GraphQL API types.

Note

The exact table names and column names may vary slightly between platform databases. Use .schema in the sqlite3 CLI or the Structure tab in Beekeeper Studio to inspect the actual schema of any downloaded file.

Core Tables¶

releases¶

The primary table containing game release metadata.

Column	Data Type	Description
`release_id`	INTEGER (PK)	Unique release identifier
`title`	TEXT	Game title
`system`	TEXT	System short code (e.g., "SNES", "NES")
`system_name`	TEXT	Full system name (e.g., "Super Nintendo")
`region`	TEXT	Release region (e.g., "USA", "Japan", "Europe")
`genre`	TEXT	Primary genre
`genre2`	TEXT	Secondary genre
`description`	TEXT	Short description
`advanced_description`	TEXT	Detailed description
`developer`	TEXT	Developer name
`publisher`	TEXT	Publisher name
`release_date`	TEXT	General release date
`date_us`	TEXT	US release date
`date_jp`	TEXT	Japan release date
`date_eu`	TEXT	Europe release date
`score`	INTEGER	Rating or score (0-100)
`players`	TEXT	Number of players supported
`online`	INTEGER	Online multiplayer (0 or 1)
`disc_count`	INTEGER	Number of discs
`series`	TEXT	Series identifier
`series_name`	TEXT	Series name
`game_type`	TEXT	Game type classification
`quality_score`	REAL	Internal quality score

roms¶

ROM file metadata associated with releases.

Column	Data Type	Description
`rom_id`	INTEGER (PK)	Unique ROM identifier
`filename`	TEXT	Full ROM filename
`filename_no_ext`	TEXT	Filename without extension
`size`	INTEGER	File size in bytes
`crc`	TEXT	CRC32 checksum
`md5`	TEXT	MD5 hash
`sha1`	TEXT	SHA1 hash
`language`	TEXT	ROM language
`region`	TEXT	ROM region
`serial`	TEXT	Serial number
`parent`	TEXT	Parent ROM reference
`header`	TEXT	Header data
`dump_source`	TEXT	Source of the ROM dump
`system_id`	INTEGER	Foreign key to systems table

systems¶

Platform/console metadata.

Column	Data Type	Description
`system_id`	INTEGER (PK)	Unique system identifier
`name`	TEXT	Full system name
`short_name`	TEXT	Short code (e.g., "SNES")
`manufacturer`	TEXT	Hardware manufacturer
`release_year`	INTEGER	Year of release
`generation`	INTEGER	Console generation number
`media_type`	TEXT	Media format (cartridge, CD, etc.)
`region`	TEXT	System region
`units_sold`	TEXT	Units sold worldwide
`specs`	TEXT	Hardware specifications
`description`	TEXT	System description

reviews¶

Game reviews from various sources.

Column	Data Type	Description
`id`	INTEGER (PK)	Review identifier
`release_id`	INTEGER (FK)	Associated release
`title`	TEXT	Review title
`body`	TEXT	Review content
`rating`	REAL	Review rating
`source`	TEXT	Publication/source name
`author`	TEXT	Review author
`date`	TEXT	Review date
`platform`	TEXT	Platform reviewed on
`url`	TEXT	Source URL
`type`	TEXT	Review type

Relationships¶

releases ←→ roms     (releases contain ROM references)
releases ←→ reviews  (releases have multiple reviews)
releases ←→ systems  (releases belong to a system)
roms     ←→ systems  (ROMs belong to a system)

Example SQL Queries¶

Find Top-Rated Games¶

SELECT title, score, developer, publisher
FROM releases
WHERE score IS NOT NULL
ORDER BY score DESC
LIMIT 20;

Search Games by Title¶

SELECT release_id, title, system, score, developer
FROM releases
WHERE title LIKE '%Mario%'
ORDER BY score DESC;

Find Games by Genre¶

SELECT title, score, developer, release_date
FROM releases
WHERE genre LIKE '%RPG%'
ORDER BY score DESC
LIMIT 25;

Count Games per Genre¶

SELECT genre, COUNT(*) as game_count
FROM releases
WHERE genre IS NOT NULL AND genre != ''
GROUP BY genre
ORDER BY game_count DESC;

Find Games Released in a Year Range¶

SELECT title, release_date, score, developer
FROM releases
WHERE release_date BETWEEN '1990' AND '1995'
ORDER BY score DESC
LIMIT 20;

Find Games by Developer¶

SELECT title, score, genre, release_date
FROM releases
WHERE developer LIKE '%Capcom%'
ORDER BY score DESC;

Find ROMs by Hash¶

SELECT r.filename, r.size, r.crc, r.md5, r.sha1
FROM roms r
WHERE r.md5 = 'your_md5_hash_here';

Find Multi-Player Games¶

SELECT title, players, score, developer
FROM releases
WHERE players != '1'
  AND players IS NOT NULL
ORDER BY score DESC
LIMIT 20;

Tips¶

Use .schema to inspect the exact table structure of any downloaded database before writing queries
Ratings and scores are on a 0-100 scale. A score of NULL means no score is available for that game
Region variants: the same game may appear multiple times for different regions (USA, Japan, Europe). Use the region column to filter
SQLite is read-only for analysis: these databases are snapshots. They are not connected to a live server
Large databases: some platforms (PS2, MAME) have very large databases. Ensure you have adequate disk space before downloading
Case sensitivity: SQLite's LIKE operator is case-insensitive by default for ASCII characters

Troubleshooting¶

Problem	Cause	Solution
Query fails with "no such table" or "no such column"	SQLite schemas vary slightly between legacy systems and modern platforms (e.g., some columns may be missing or named differently).	Inspect the database schema first using `.schema` or Beekeeper Studio's Structure tab to verify exact column names.
`LIKE` queries on non-ASCII characters do not find matches	SQLite's standard `LIKE` operator is only case-insensitive for 7-bit ASCII characters by default.	Use the `LOWER()` function explicitly in your comparison, e.g., `WHERE LOWER(title) LIKE LOWER('%mārio%')`.
Queries are extremely slow on large databases (e.g., PSX, PSP)	Missing indexes on custom columns when joining tables or filtering by text fields.	Create indexes on columns you query frequently, e.g., `CREATE INDEX IF NOT EXISTS idx_releases_title ON releases(title);`.
Database file is locked or returns "database is locked" error	Another script or connection opened the SQLite database in write mode without closing the transaction.	Force-close any active SQLite connection processes (like python scripts or database explorer tabs), or copy the database to a separate folder.