Documentation

Best Practices

Patterns that keep your game queries fast, maintainable, and free of common pitfalls.

FK Navigation vs JOINs

GameSchema gives you three ways to cross a foreign key relationship. Each has a different performance profile and readability trade-off.

Navigation Properties — best for one row, reading convenience

Lazy-loads the related row on first access and caches it. Each uninitialised property is one extra SQL query.

Follow Chain — best for one row, reading a specific deep value

Resolves the entire path in a single SQL query using JOINs under the hood. No extra round-trips regardless of chain depth.

Batch Dictionary — best for many rows with FK lookups

Fetch all needed FKs in one query, build a dictionary, join in memory. Avoids N+1 entirely with full type safety.

QueryBuilder JOIN — best for aggregations or projecting mixed columns

Raw INNER JOIN / LEFT JOIN. Requires string column names but allows SELECT of specific columns from multiple tables into a slim DTO.

The N+1 Problem — and How to Avoid It

The most common mistake with any ORM-style API is accidentally firing one query per row when iterating a list. With GameSchema, this happens when you access navigation properties inside a loop.

✗ N+1 — avoid this

// ✗ N+1 — one SQL query per enemy
var enemies = Enemies.GetAll(Enemies.Level > 5);
foreach (var e in enemies)
{
    // Each access fires a separate SELECT against the weapons table
    var weapon = e.Weapon;
    Debug.Log($"{e.Name} wields {weapon.Name}");
}

If your enemy list has 200 rows, the loop above fires 201 SQL queries (1 for the list + 1 per enemy). Fix it with either a JOIN or a batch dictionary:

✓ Single JOIN query

// ✓ Single query with a JOIN (QueryBuilder)
// Fetch all columns you need in one round-trip
var data = DB.Table("enemies")
    .JoinFK("weapon_id", "weapons")
    .Select("enemies.id", "enemies.name", "weapons.name")
    .Where("enemies.level", Op.Gt, 5)
    .Get<EnemyCombatView>();

// EnemyCombatView maps only the columns you selected:
public class EnemyCombatView
{
    public int    Id;
    public string Name;         // enemies.name
    public string WeaponName;   // weapons.name — mapper matches by column alias
}

✓ Batch dictionary (typed columns)

// ✓ Batch-load with a dictionary (typed API, no string in queries)
var enemies = Enemies.GetAll(Enemies.Level > 5);

// Collect FK IDs first
var weaponIds = enemies.Select(e => e.WeaponId).Distinct().ToList();

// Fetch all needed weapons in one query
var weapons = Weapons.GetAll(Weapons.Id.In(weaponIds.ToArray()));
var weaponById = weapons.ToDictionary(w => w.Id);

// Join in memory
foreach (var e in enemies)
{
    var weapon = weaponById[e.WeaponId];
    Debug.Log($"{e.Name} wields {weapon.Name}");
}

When to Use Each Approach

Situation	Recommended approach
Showing a detail screen for one row	Navigation properties or Follow chain
Reading one deep value for one row	Follow chain (single query)
Iterating a list and needing FK data for every row	Batch dictionary — one query per FK table
Projecting mixed columns for a list view	QueryBuilder with JOIN into a slim DTO
Aggregating data (COUNT, SUM, GROUP BY)	Low-level QueryBuilder or DB.Main.Scalar()

Follow Chain vs Navigation Properties

Both resolve in O(depth) queries for navigation properties vs O(1) for a Follow chain. The Follow chain wins whenever you traverse more than one hop, or when you care about latency.

// Follow chain — single SQL query, ideal for reading one deep value
string effectName = Enemies.From(42)
    .Follow(Enemies.WeaponId, "weapons")
    .Follow(Weapons.StatusEffectId, "status_effects")
    .Fetch(StatusEffects.Name);

// Navigation property — separate query per access, fine for one row
EnemyData boss = Enemies.Get(42);
string effectName2 = boss.Weapon.StatusEffect.Name; // 2 extra queries

When the Follow Chain Shines

// Good: read a single value for one specific row
string iconAddress = Items.From(itemId)
    .Follow(Items.CategoryId, "item_categories")
    .Fetch(ItemCategories.IconAddress);

// Good: resolve a chain at display time (detail screen, one item)
var detail = Enemies.From(selectedId)
    .Follow(Enemies.WeaponId, "weapons")
    .Fetch<WeaponData>();

Performance Tips

Always Limit large queries

SQLite reads rows sequentially. Without a Limit, fetching a 50,000-row table allocates all 50,000 objects. Page your results or filter aggressively.

// Always Limit large result sets
var page = Enemies.Query()
    .Where(Enemies.Level > 5)
    .OrderBy(Enemies.Level)
    .Limit(50)
    .Offset(page * 50)
    .FetchAll();

Initialize once — query many times

DB.Initialize()opens a SQLite connection and keeps it open. It's cheap to query repeatedly against an open connection; reopening is expensive. Never call Initialize per-frame or per-query.

// Safe startup order — initialize before any generated class is called
void Awake()
{
    // 1. Open the database
    DB.Initialize();

    // 2. NOW it's safe to query
    var all = Enemies.GetAll();
}

Use Count instead of GetAll().Count

Enemies.Count(filter) executes SELECT COUNT(*) — no rows are fetched. Enemies.GetAll(filter).Count fetches every matching row just to count them.

Prefer FetchExists over FetchCount > 0

FetchExists() uses SELECT COUNT(*) with a LIMIT 1 internally. It short-circuits as soon as one row is found.

Keep databases read-only at runtime

GameSchema opens your database read-only in builds by design. This avoids locking overhead and journal files that confuse Unity's asset importer. For player save data, use a separate writable database in persistentDataPath and open it manually via GameDatabase.Open(path).

VCS exports are for diffing, not runtime

The VCS JSON export (Settings tab) writes one JSON file per table for readable diffs in Git/Plastic. These files are editor-only — do not read them at runtime. Always query the .db file directly.

Platform Notes

// Android: StreamingAssets is inside the APK.
// GameSchema automatically copies the .db to persistentDataPath on first run.
// No action needed — just call DB.Initialize() as normal.

// WebGL: Same automatic copy behaviour.
// The copy is synchronous — consider doing it in a loading screen
// if your database is large (> a few MB).

// iOS: StreamingAssets is accessible directly as a file path.
// No copy step needed; GameSchema opens it in place.

Platform	DB location at runtime	Notes
Windows / macOS / Linux	StreamingAssets/ (direct path)	Opened in place, read-only.
Android	persistentDataPath/ (auto-copied)	First launch copies from APK. File is cached on subsequent runs.
iOS	StreamingAssets/ (direct path)	Accessible as a file path. No copy needed.
WebGL	persistentDataPath/ (auto-copied)	Copied via UnityWebRequest on first run. May show a brief load stall on large files.

Note: On all platforms the DB is opened with journal_mode=DELETEin the Editor to prevent Unity's asset importer from seeing -wal and -shm side files. This setting is not applied at runtime (where the DB is read-only anyway).

IL2CPP & Stripping

GameSchema uses reflection to map query results to your data classes. If you build with IL2CPP and aggressive code stripping, Unity may strip the setters on your generated data classes. To prevent this, either:

Add a link.xml file preserving your generated namespace, or
Enable Serializable in GameSchema Settings (adds [Serializable], which Unity's stripping rules already protect), or
Set Managed Stripping Level to Minimal during development.