# Memory DB SQL

> Write and manage SQL queries against an exported Unity memory-snapshot database (DuckDB/SQLite), and keep the schema, views, version, and docs consistent. Use when writing/editing queries over native_objects, native_allocations, native_roots, memory_regions, system_memory_regions, etc.; investigating memory regions; adding or changing tables/views/macros; or checking database version compatibility.

- Skill: `unity-technologies/memory-db-sql` (Agent Skill)
- Install (CLI): `npx skillmds@latest add unity-technologies/memory-db-sql`
- Raw SKILL.md: https://api.skillmd.com/api/skills/unity-technologies/memory-db-sql/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: Unity-Technologies (https://skillmd.com/u/unity-technologies)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/unity-technologies/memory-db-sql

---


# Memory snapshot database SQL

Use this when working with the database the tool exports from a `.snap` (the `export` command), as
opposed to running the export/report pipeline itself (use `memory-snapshot-report` for that).

**Canonical schema reference:** [`docs/database-schema.md`](../../../docs/database-schema.md). It
lists every table, column, view, macro, join key, and the version policy. Read it before writing
non-trivial queries — this skill is the workflow; that doc is the source of truth.

## Before you query: check the schema version

The database stamps a **major.minor** version in `schema_meta`:

```sql
SELECT schema_version_major, schema_version_minor, msdt_version FROM schema_meta;
```

- **No `schema_meta` table** → a pre-versioning export (treated as 0.0): re-export the `.snap`.
- **major < `DatabaseSchemaInfo.SchemaMajor`** → table/column structure changed; **re-export required**
  (`MemorySnapshotDataTools export "<snap>" "<db>"`).
- **same major, minor < `DatabaseSchemaInfo.SchemaMinor`** → only views/indexes changed; **upgrade in
  place** with `MemorySnapshotDataTools upgrade "<db>"` (no re-export).
- In code, classify with `DatabaseSchemaInfo.Evaluate(major, minor)` → `SchemaAction`
  (`None`/`UpgradeInPlace`/`ReExport`/`ToolOutdated`); the CLI `SchemaGate` already does this before
  `report`/`summary`/`validate`, and `DatabaseMaintenance.{Inspect,UpgradeInPlace}` are the entry
  points (see [`Core/Models/DatabaseSchemaInfo.cs`](../../../Core/Models/DatabaseSchemaInfo.cs)).

## Querying: the things that bite

These are spelled out in the schema doc, but the high-frequency traps:

- **Two region tables, no FK between them.** `memory_regions` = Unity allocator buckets
  (`native_allocations.memory_region_index` points here). `system_memory_regions` = OS/VM regions
  (the RAM truth). Bridge an allocation to its OS region **by address range**, not a key.
- **Prefer the views/macros** over rebuilding joins: `v_allocation_enriched` (allocation + Unity
  region + OS region + root + object), `v_system_region_summary` (committed/resident/Unity-tracked
  per OS region), `v_region_owner_breakdown`, `v_connection_edges` (reference graph with both
  endpoints typed — filter it, don't `SELECT *`), `v_assetbundle_utilization` (per-bundle: does it
  reference other loaded assets, and how much). DuckDB also has `region_allocations(name)` and
  `region_page_density(name)` (SQLite has the views only).
- **Connections: don't hand-count "own" edges with magic numbers.** A native object's outbound edges
  include its self-reference and its managed wrappers (`native_gc_handle_bridge`/`native_connection`
  to managed). For "references to *other* loaded objects," count `native_object→native_object`
  `native_connection` edges where `to_index <> from_index` — exactly what `v_assetbundle_utilization`
  does.
- **`native_object_address` ≠ `native_allocations.address`** — bridge objects and allocations
  through a shared `root_reference_id` → `native_roots.root_id`, never by address.
- **Don't divide by `memory_regions.address_size`** (0 for `ALLOC_DEFAULT` et al.).
- **`system_memory_regions.type` is uniformly 0 on iOS** — group by `name`.
- **Resident data and `page_size` require `snap_format_version` ≥ 17** (else NULL).
- `region_page_density` is for **small-allocation zones** (NANO/TINY/SMALL); `avg_fill_pct` > 100%
  means the region holds page-spanning allocations and the metric doesn't apply.

## Writing query code safely

This repo's first-class rule is SQL safety — see [`CLAUDE.md`](../../../CLAUDE.md) and
[`docs/sql-safety.md`](../../../docs/sql-safety.md):

- **Parameterize every value.** DuckDB: positional `?`. SQLite: named `$name`.
- **Identifiers can't be parameters** — validate against the catalog (`information_schema.columns`
  for DuckDB, `pragma_table_info($t)` for SQLite); see the `HasColumn` helpers.
- **Open read-only** for analysis (`ACCESS_MODE=READ_ONLY` / `Mode=ReadOnly`).
- The report `ExecuteQuery(string)` sink takes only internally-constructed SQL (constants in
  `ReportSql`); never pass it external input.

Ad-hoc CLI querying (no SQL command exists in the tool): use the `duckdb` CLI on a `.duckdb` file
(open with `-readonly`; if a lock error mentions DataGrip, ask the user to close that connection).

## Upkeep: when you change the schema

A schema change is not done until **all** of these are consistent:

1. **Both backends** — mirror the change in
   [`DuckDbExportDestination.cs`](../../../Core/ExportDestination/DuckDbExportDestination.cs) **and**
   [`SqliteWriter.cs`](../../../Core/ExportDestination/SqliteWriter.cs) (tables, indexes, and the
   `CreateViewsScript`). Remember: DuckDB has `ASOF` joins and table macros; SQLite does not (use a
   correlated subquery; macros are DuckDB-only). Keep index/view DDL **re-runnable** (`CREATE INDEX
   IF NOT EXISTS`, DuckDB `CREATE OR REPLACE VIEW` / SQLite drop-then-create) so `UpgradeSchema` works.
2. **The doc** — update [`docs/database-schema.md`](../../../docs/database-schema.md): table/column
   tables, view/macro list, join keys, and the version table. New tables, views, columns, and
   identifiers must appear here.
3. **The version** — in
   [`Core/Models/DatabaseSchemaInfo.cs`](../../../Core/Models/DatabaseSchemaInfo.cs):
   - **View/index change only** (add/change a view or index): bump **`SchemaMinor`**. Existing
     databases can be upgraded in place (`MemorySnapshotDataTools upgrade`), so no re-export.
   - **Table/column change** (add/rename/remove a table or column, or change a column's
     meaning/units): bump **`SchemaMajor`** and reset `SchemaMinor` to 0. Old databases require a
     re-export. Make sure both writers' table DDL and `schema_meta` insert stay in sync.
   - Add a row to the doc's version table either way.
4. **Readers** — `snapshot_info` is read with `SELECT *` by column name
   (`SummaryReportRunner.ReadSnapshotInfo`), so new columns are safe; if you add a column some
   reader needs, wire it there.
5. **Tests** — extend `DatabaseSchemaInfoTests` and the export round-trip tests so the new
   schema/view/version is asserted.

## See also

- `memory-snapshot-report` skill — export a `.snap` to a database and generate reports.
- [`docs/database-schema.md`](../../../docs/database-schema.md) — canonical schema.