Several Tables

Several Tables

A file can hold several tables, like the sheets of a workbook. Use this when details would otherwise repeat: in a statement file, each client's name and address would sit on every transaction row. With two tables, clients holds those details once, and transactions refers to them by clientId.

erDiagram
  clients ||--o{ transactions : "clientId"
  clients { string clientId PK  string name  string address  string email }
  transactions { string clientId FK  datetime date  string description  decimal amount }
  rates { string currency PK  float rate }

Loading

• Each table has its own columns (with indexes), sortedBy, chunkRows and chunkBytes, and in access-controlled files its own partitionBy and columnGroups.
• Shared by the file: the key or password, codec, metadata, embedded files and grants.
• Memory while writing: only the current table's chunk, however many tables there are.

Writing and reading

import { JazminKey, open, write } from '@smithsoft-studios/jazmin';

const clients = Array.from({ length: 200 }, (_, i) => ({
  clientId: `C${String(i + 1).padStart(4, '0')}`,
  name: `Client ${i + 1}`,
  address: `${i + 1} Main Road, Cape Town`,
  email: `client${i + 1}@example.com`,
}));
const transactions = clients.flatMap((c, i) => Array.from({ length: 15 }, (_, n) => ({
  clientId: c.clientId,
  date: new Date(Date.UTC(2026, 2, 1 + n)),
  description: n % 4 === 0 ? 'Debit order' : 'Card purchase',
  amount: `${((i * 31 + n * 17) % 500) + 10}.50`,
})));

const key = JazminKey.generate();
write('statements.jzm', { clients, transactions }, {
  key,
  tables: [
    // Small chunks suit a table looked up by key: a lookup decodes one chunk
    { name: 'clients', columns: [
      { name: 'clientId', type: 'string', nullable: false },
      { name: 'name', type: 'string' },
      { name: 'address', type: 'string' },
      { name: 'email', type: 'string' },
    ], sortedBy: ['clientId'], chunkRows: 256 },
    { name: 'transactions', columns: [
      { name: 'clientId', type: 'string', nullable: false },
      { name: 'date', type: 'datetime', nullable: false },
      { name: 'description', type: 'string' },
      { name: 'amount', type: 'decimal' },
    ], sortedBy: ['clientId', 'date'], chunkRows: 1024 },
  ],
});

const clientTable = open('statements.jzm', { key: key.export() }); // the first table
console.log(clientTable.tables, '- this reader shows', clientTable.table);
const lines = clientTable.openTable('transactions'); // the same open file: no second open, no second key check

const client = clientTable.find({ clientId: 'C0042' }).next().value;
const statement = [...lines.find({ clientId: 'C0042', date: { lt: new Date('2026-03-04') } }, { select: ['date', 'amount'] })];
console.log(client.name, '|', client.address);
for (const l of statement) console.log(' ', l.date.toISOString().slice(0, 10), l.amount);
console.log(lines.rowCount, 'transactions for', clientTable.rowCount, 'clients');
lines.close();
clientTable.close(); // the file closes with its last reader

[ 'clients', 'transactions' ] - this reader shows clients
Client 42 | 42 Main Road, Cape Town
  2026-03-01 281.50
  2026-03-02 298.50
  2026-03-03 315.50
3000 transactions for 200 clients

open(path, { key, table: 'transactions' }) opens a named table directly. With the streaming writer, declare the tables, write the first table's rows, call writer.startTable('transactions'), write its rows, then finish(). Tables can be started in any order, each once; a table you never start is written empty.

In .NET

using Jazmin;
using Jazmin.Query;

var clientColumns = new[] { new JazminColumn("clientId", JazminType.String) { Nullable = false }, new JazminColumn("name", JazminType.String) };
var lineColumns = new[] { new JazminColumn("clientId", JazminType.String) { Nullable = false }, new JazminColumn("amount", JazminType.Decimal) };

using (var writer = JazminWriter.Create("statements.jzm", new JazminWriteOptions
{
    Tables =
    [
        new JazminTable("clients", clientColumns) { SortedBy = ["clientId"], ChunkRows = 256 },
        new JazminTable("transactions", lineColumns) { SortedBy = ["clientId"], ChunkRows = 1024 },
    ],
}))
{
    writer.WriteValues("C1", "ABC Corp");
    writer.WriteValues("C2", "Test Ltd");
    writer.StartTable("transactions");
    writer.WriteValues("C1", 1000m);
    writer.WriteValues("C1", 250.50m);
    writer.WriteValues("C2", 20000m);
}

using var clients = JazminReader.Open("statements.jzm");
using var transactions = clients.OpenTable("transactions"); // or Table = "transactions" in the read options
Console.WriteLine(string.Join(", ", clients.Tables));
foreach (var c in clients.Rows())
{
    var total = transactions.Find(JazminFilter.Eq("clientId", c["clientId"])).Sum(t => decimal.Parse((string)t["amount"]!));
    Console.WriteLine($"{c["clientId"]} {c["name"]}: {total}");
}

clients, transactions
C1 ABC Corp: 1250.50
C2 Test Ltd: 20000

Nested output across tables

• Export shapes (1.3.0) nest rows from several tables in one JSON or XML export: each customer with their orders, each order's lines and each line's product. Sort each linked table by its link and every table is read once. See Export Shapes.
• .NET LINQ works on each table through AsQueryable<T>(), in lambda or query syntax. See Across tables.

Documents that read several tables (1.6.0)

A file's document reads every table. jazmin.columns, rowCount and rows() describe its own table (jazmin.table); query and count read any table by name:

// not run: a page inside a file
jazmin.tables;  // [{ name: 'clients', columns, rowCount }, { name: 'transactions', columns, rowCount }]
const lines = await jazmin.query({ clientId: 'C00042' }, { table: 'transactions', orderBy: 'date' });
const unpaid = await jazmin.count({ paid: false }, { table: 'transactions' });

An access key sees only its own partitions in each table. In renderPdf, a filter limits every table it fits; other tables need tableFilters (Server Side).

Access control across tables

Partition names and column-group names are shared by every table of a file, so a key granted client C1 sees C1's rows in every table partitioned by client: access follows the link.

import { JazminKey, open, write } from '@smithsoft-studios/jazmin';

const owner = JazminKey.generate();
const bob = owner.createAccessKey();
write('shared.jzm', {
  clients: [{ clientId: 'C1', name: 'ABC Corp', email: 'abc@example.com' }, { clientId: 'C2', name: 'Test Ltd', email: 'test@example.com' }],
  transactions: [{ clientId: 'C1', amount: 1000 }, { clientId: 'C1', amount: 250.5 }, { clientId: 'C2', amount: 20000 }],
  rates: [{ currency: 'USD', rate: 18.2 }, { currency: 'EUR', rate: 19.9 }],
}, {
  key: owner,
  access: { grants: [{ key: bob, rows: ['C1', '*'], columns: ['*'] }] }, // '*' here: the unpartitioned rates table
  tables: [
    { name: 'clients', columns: [{ name: 'clientId', type: 'string', nullable: false }, { name: 'name', type: 'string' }, { name: 'email', type: 'string' }],
      partitionBy: 'clientId', columnGroups: { contact: ['email'] } },
    { name: 'transactions', columns: [{ name: 'clientId', type: 'string', nullable: false }, { name: 'amount', type: 'float' }], partitionBy: 'clientId' },
    { name: 'rates', columns: [{ name: 'currency', type: 'string' }, { name: 'rate', type: 'float' }] }, // no partitionBy: one partition, '*'
  ],
});

const view = open('shared.jzm', { key: bob.export() });
for (const name of view.tables) {
  const t = view.openTable(name);
  console.log(name.padEnd(13), JSON.stringify([...t.rows()]));
  t.close();
}

clients       [{"clientId":"C1","name":"ABC Corp"}]
transactions  [{"clientId":"C1","amount":1000},{"clientId":"C1","amount":250.5}]
rates         [{"currency":"USD","rate":18.2},{"currency":"EUR","rate":19.9}]

Bob sees client C1 in both partitioned tables, without the email column (its contact group wasn't granted), and the whole rates table.

A table without partitionBy (here, exchange rates) is one partition named *. A grant of rows: '*' covers it; a grant of rows: ['C1'] doesn't. To share it, add '*' to the grant's rows, as above.

Changing one table

append, update and compact take a table option (.NET Table); the default is the first table. Only that table's rows change.

append('statements.jzm', { key, table: 'transactions', insert: newLines });
update('statements.jzm', { key, table: 'clients', upsert: [changedClient], keyColumns: ['clientId'] });

• An append writes new sections for that table only; the other tables stay exactly as they are.
• A full rewrite (update, compact, granting or revoking) copies the other tables into the new version, because every new version gets fresh secrets.

Adding, removing and renaming tables (1.6.0)

update() and compact() change a file's tables in one rewrite that keeps everything else:

update('statements.jzm', { key,
  addTables: [{ name: 'payments', columns: paymentColumns, sortedBy: ['clientId', 'date'], partitionBy: 'clientId', rows: payments }],
  removeTables: ['old'],
  renameTables: { tx: 'transactions' },
});

// fragment
JazminFile.Update("statements.jzm", new JazminUpdate
{
    Key = owner,
    AddTables = [new JazminAddedTable(new JazminTable("payments", paymentColumns) { SortedBy = ["clientId", "date"], PartitionBy = "clientId" }, payments)],
    RemoveTables = ["old"],
    RenameTables = new Dictionary<string, string> { ["tx"] = "transactions" },
});

• An added table's rows can be any iterable (an array, or a generator reading another file). They are read once and never all held; with sortedBy they are put in that order within the memory budget.
• Shared files: a table partitioned by the same column uses the same partition names, so a key granted client C1 sees C1's rows in the new table with no change to its grant.
• Saved shapes, the document's edits and links follow a rename. A table they read can't be removed until they go too; the error names them.
• A file's single unnamed table is named first: renameTables: { '': 'clients' }.

Links between tables (1.6.0)

Say once how the tables refer to each other. The file keeps the links in its metadata (jazmin.links: names, tables and columns, never values), and checkLinks() checks them against the rows:

write('statements.jzm', { clients, transactions }, { tables, links: [
  { name: 'client', from: { table: 'transactions', columns: ['clientId'] }, to: { table: 'clients', columns: ['clientId'] } },
] });
const r = open('statements.jzm', { key });
r.checkLinks();
// [{ name: 'client', rows: 300000, empty: 0, missing: 2, examples: [['C0999'], ['C1203']], duplicateTargets: 0, method: 'merge' }]

// fragment
var options = new JazminWriteOptions { Tables = tables, Links = [new JazminLink("client",
    new JazminLinkSide("transactions", ["clientId"]), new JazminLinkSide("clients", ["clientId"]))] };
var checks = reader.CheckLinks();   // Rows, Empty, Missing, Examples, DuplicateTargets, Method

• Checked when written: the tables and columns exist and their types are equal. Older readers ignore the links: no format change.
• missing counts rows whose link finds nothing, empty rows with no value, duplicateTargets target values that repeat. A key sees only its own rows, so a shared file is checked as that key sees it.
• Fast: tables sorted by the link columns are walked side by side, holding almost nothing. 1,000,000 rows check in about 0.1 to 0.3 s (npm run bench:links).

Changing several tables at once (1.6.0)

One update() can change rows in several tables. The usual options change the main table (table); tables gives the others theirs. set with where gives the rows a filter matches new column values. Together they re-key across tables, for example a client's number changed everywhere it is used:

const result = update('statements.jzm', { key,
  table: 'clients', set: { clientId: 'C0999' }, where: { clientId: 'C0123' },
  tables: { transactions: { set: { clientId: 'C0999' }, where: { clientId: 'C0123' } } },
});
// result.tables.transactions.updated === 20

// fragment
var change = new JazminTableChanges { Set = new Dictionary<string, object?> { ["clientId"] = "C0999" }, Where = JazminFilter.Eq("clientId", "C0123") };
JazminFile.Update("statements.jzm", new JazminUpdate
{
    Key = key, Tables = new Dictionary<string, JazminTableChanges> { ["clients"] = change, ["transactions"] = change },
});

• Each table takes insert, upsert with keyColumns, delete, set with where, indexes and sortedBy. Tables not named are copied as they are. tables names a table as the file has it now, even while renameTables renames it; the result uses the names afterwards. Each set value is checked against its column before the file is read.
• Sorted tables stay sorted. When a few rows' sort column is set, they are found by the indexes and merged into place: a re-key of 20 rows in a 1,000,000-row table took 2.1–2.2 s (a plain rewrite: 1.7–1.8 s; through the full sort: 3.8–4.4 s). Many rows are re-sorted within the memory budget.
• Shared files: the partition column can't be set, since the rows would leave the partitions keys are granted.

Is it worth it?

Measured on 20,000 clients with 11 detail columns and 300,000 transactions (15 per client), Node.js, with clients in 256-row chunks and transactions in 1,024-row chunks in both files:

	Flat (details on every row)	Two tables
File size	3.70 MB	3.39 MB (−8%)
Write	1.28 s	0.94 s (−27%)
One client's statement (open, client, 15 lines)	1.14 ms	1.03 ms with openTable
Read every transaction	176 ms	145 ms (−18%)

The size gain is modest because dictionary encoding already stores a chunk's repeated values once. The bigger gains: each table is read without decoding the other's columns, a client's details change in one row, and each table's chunk size can suit how it is read.