Hierarchical data walkthrough

Hierarchical data walkthrough

A start-to-finish walkthrough for SQL developers: take a three-table relational schema, pack it into one DynamoDB table with structured keys, and keep every access pattern indexed — no Scan, no FilterExpression where a key condition can do the work. The running example is the car-rental hierarchy shipped in examples/car-rental.

Read Concepts first for vocabulary (pk, sk, GSI, structural key, technicalPrefix); read Key and field design for the general key-selection rules this walkthrough applies.

The SQL starting point

A national car-rental company: states contain facilities, facilities contain vehicles.

CREATE TABLE states     (state CHAR(2) PRIMARY KEY, ...);
CREATE TABLE facilities (state CHAR(2) REFERENCES states, facility TEXT, PRIMARY KEY (state, facility), ...);
CREATE TABLE vehicles   (state CHAR(2), facility TEXT, vehicle TEXT,
                         PRIMARY KEY (state, facility, vehicle),
                         FOREIGN KEY (state, facility) REFERENCES facilities, ...);

Typical queries: one record by full key; all facilities in a state; all vehicles at a facility; all vehicles in a state; cascade a delete from a state down. In SQL those are joins and ON DELETE CASCADE. In DynamoDB, all of them become range queries on one sorted index — if the keys are designed for it.

Why structured keys

DynamoDB gives you exactly one cheap read primitive: a Query with a KeyConditionExpression (KCE) — an index seek that reads only matching items. A FilterExpression runs after items are read from disk: it shrinks the response, never the cost. So the design goal is: express every hot access pattern as a key condition.

The trick is a structural key — one technical attribute holding the joined logical keys:

Record	Logical key	Structural key (_sk)
state	{state: 'TX'}	TX
facility	{state: 'TX', facility: 'Dallas Rental'}	TX|Dallas Rental
vehicle	{state: 'TX', facility: 'Dallas Rental', vehicle: '1FTEW…'}	TX|Dallas Rental|1FTEW…

Because the sort key is sorted, begins_with covers the hierarchy:

• _sk = "TX" — the Texas state record.
• begins_with(_sk, "TX|Dallas Rental|") — every vehicle at Dallas Rental.
• begins_with(_sk, "TX|") — every facility and vehicle in Texas (excludes the state record itself — the trailing separator forces child-level matches).

Descending order is free (ScanIndexForward: false). The constraint you accept: sorting is by the composite, in declaration order — you can't sort a subtree by an arbitrary field without a secondary index (see Key and field design).

The declaration

The toolkit turns the pattern into a declaration. The shipped example, trimmed to the structural essentials:

import {Adapter} from 'dynamodb-toolkit';

const adapter = new Adapter({
  client,
  table: 'car-rental-example',
  technicalPrefix: '_',

  // Three tiers: state → facility → vehicle.
  keyFields: ['state', 'facility', 'vehicle'],
  structuralKey: '_sk',

  // Depth-based type labels, paired 1:1 with keyFields;
  // `kind` is auto-stamped on write and read back by typeOf.
  typeLabels: ['state', 'facility', 'vehicle'],
  typeField: 'kind',
  typeDiscriminator: 'kind',

  // Cascade gate for deleteAllUnder / cloneAllUnder / moveAllUnder.
  relationships: {structural: true}
});

The built-in prepare step composes _sk on every write by walking keyFields in order and joining the contiguous-from-start defined fields:

• {state: 'TX'} → _sk = "TX" — a state record.
• {state: 'TX', facility: 'Dallas Rental'} → "TX|Dallas Rental" — a facility.
• {state: 'TX', vehicle: '1FTEW…'} → throws — non-contiguous (facility missing in the middle).

The depth of the key is the record's type — no discriminator column required, though one is supported (see Multi-type tables). Number components in a composite require width (zero-padding keeps lexicographic order honest: "9" > "10" without it).

Field values must not contain the separator (default '|', any string allowed) — sanitize on the way in, or pick a separator outside your value alphabet.

Writing records

Nothing tier-specific — the key shape decides the tier:

await adapter.post({state: 'TX', name: 'Texas Operations'});
await adapter.post({state: 'TX', facility: 'Dallas Rental', phone: '…'});
await adapter.post({state: 'TX', facility: 'Dallas Rental', vehicle: '1FTEW1E53PKE00001', make: 'Ford', status: 'available'});

Reading the hierarchy

Single records use the full logical key:

const car = await adapter.getByKey({state: 'TX', facility: 'Dallas Rental', vehicle: '1FTEW1E53PKE00001'});

Subtrees use getListUnder (children of a partial key) or buildKey for the other shapes:

// All facilities + vehicles in Texas (children of the state):
const inTexas = await adapter.getListUnder({state: 'TX'}, {limit: 50});

// All vehicles at one facility:
const fleet = await adapter.getListUnder({state: 'TX', facility: 'Dallas Rental'});

// Self + descendants, or prefix-narrowing, compose buildKey + getListByParams:
const params = adapter.buildKey({state: 'TX'}, {self: true});
params.TableName = adapter.table;
const withStateRecord = await adapter.getListByParams(params, {limit: 50});

The three buildKey shapes (children default, {self: true}, {partial: 'Dal'}) are covered in depth in Querying subtrees with buildKey. Both offset and cursor pagination work on every list read — see Pagination.

Listing a tier across partitions

"All facilities in every state" can't ride the base table (each state is its own partition). That's the sparse-GSI-on-typeField pattern — one GSI keyed on the auto-stamped kind:

indices: {'by-kind': {type: 'gsi', pk: 'kind', sk: {name: '_sk', type: 'string'}, projection: 'all'}}

const facilities = await adapter.getListByParams(
  {
    TableName: adapter.table,
    IndexName: 'by-kind',
    KeyConditionExpression: '#k = :k',
    ExpressionAttributeNames: {'#k': 'kind'},
    ExpressionAttributeValues: {':k': 'facility'}
  },
  {limit: 100}
);

Three variants of this pattern (single GSI on typeField, per-tier sparse markers, tier-within-a-partition LSI) each have a recipe with full cost tables: start at List records of a tier.

Cascades

relationships: {structural: true} unlocks the subtree macros — the DynamoDB equivalent of ON DELETE CASCADE, built from resumable mass operations:

await adapter.deleteAllUnder({state: 'TX'});                      // leaf-first: vehicles, facilities, then the state record
await adapter.cloneAllUnder({state: 'TX'}, {state: 'FL'});        // root-first copy
await adapter.moveAllUnder({state: 'TX'}, {state: 'FL'});         // two-phase idempotent rename

Ordering, crash-resumability, and the put-collision semantics are documented in Cascade subtree operations; the failure buckets are in Mass operation semantics.

The REST mirror

The hierarchy maps onto URLs through keyFromPath — the handler's hook that turns the /:key segment into a logical key. For composite keys, pick a URL-safe delimiter that can't appear in values:

import {createHandler} from 'dynamodb-toolkit/handler';

const handler = createHandler(adapter, {
  keyFromPath: (raw, adp) => {
    const parts = raw.split(':'); // '/TX:Dallas Rental:1FTEW…'
    const key = {};
    adp.keyFields.forEach((f, i) => {
      if (parts[i] !== undefined) key[f.name] = parts[i];
    });
    return key;
  }
});

GET /TX:Dallas%20Rental fetches the facility; the collection routes (GET /, DELETE /, /-clone, /-move) operate on filtered sets. URL-design trade-offs — including hierarchical path schemes like /TX/Dallas%20Rental/… built on plain framework routes — are the subject of URL schema design.

When this doesn't fit

• The hierarchy isn't stable. Structural keys freeze the field order. If "vehicles move between facilities" is a hot operation, every move rewrites the key (moveByKeys handles it, but it's a delete + put, not an in-place update). Frequent re-parenting wants an adjacency-list design instead — see Key expression patterns.
• Sibling sort by arbitrary fields. The composite sorts by declaration order only. Each extra sort dimension is an LSI/GSI (declared, budgeted, provisioned).
• A tier is genuinely huge. One state's subtree shares one partition budget (~10 GB, 3000 RCU / 1000 WCU). Shard the partition key (date buckets, hash suffixes) before the ceiling is real — see Key and field design.
• Cross-hierarchy queries dominate. If most reads slice across the hierarchy (by status, by date) rather than down it, the structural key is just one more GSI away from being overhead — design the GSIs first and ask whether the base-table hierarchy still earns its place.