Keeping data

A suite app keeps data in one of two places, kept by Wardian in its data folder. Choose by size and shape.

A page app has neither. It is sandboxed and keeps nothing between visits. To keep data in a page, let the user download it and load it again. Or make it a suite.

Small state: storage#

Declare storage in the part's caps, in suite.json and in Kernel.register:

{ "name": "inputs", "slot": "aside", "caps": ["storage"] }

Then read and write by key:

const saved = ctx.store.get('loan') || { amount: 300000, rate: 6, years: 30 };
ctx.store.set('loan', { amount, rate, years });

• get is synchronous. It returns null for a missing key.
• Values must be JSON-compatible: no Date, Map or class instances. Store a date as a string.
• Each part has its own store. Two parts never see each other's keys.
• A part may keep 1 MB this way, and a whole package 5 MB.

Wardian keeps the data in DATA_DIR/state/, so a restart, another browser or cleared site data does not lose it (ADR-2610071055).

Tables: db#

A part that declares db gets the package's own SQLite database, kept in DATA_DIR/db/<app>.sqlite (ADR-2610071219).

const db = await ctx.cap('db');

await db.insertRows({
  table: 'runs', columns: ['n', 'x'],
  rows: [[1, 980], [2, 1900]],
  create: true,                 // make the table if it is missing
});

const page = await db.page({
  table: 'runs', offset: 0, limit: 100,
  orderBy: 'x', desc: true,
  where: 'n > ?', params: [1],
});
// page = { columns, rows, total, offset }

const { columns, rows } = await db.query({ sql: 'SELECT n, avg(x) FROM runs GROUP BY n' });

Show large tables a page at a time#

Never load every row into the frame. Ask for the page the viewer sees:

async function show(offset) {
  const { rows, total } = await db.page({ table: 'data', offset, limit: 100, orderBy: sortCol, desc });
  render(rows);
  status.textContent = `${offset + 1}–${offset + rows.length} of ${total.toLocaleString()}`;
}

csv-explorer pages 100,000 rows this way, and its sort, filter and export all run in SQL.

Always use placeholders#

Put user input in params, never in the SQL text:

// Right
db.page({ table: 'data', where: 'city LIKE ?', params: [`%${text}%`] });
// Wrong: the user's text becomes SQL
db.page({ table: 'data', where: `city LIKE '%${text}%'` });

Table and column names may hold only letters, digits and _, and cannot be placeholders. Check a column name against columns from the table before you use it in orderBy or in SQL.

What Wardian refuses#

Apps write their own SQL, so Wardian keeps each one inside its own file:

• no ATTACH or DETACH, no loading extensions, no pragma that sets anything;
• another app's tables can be read only, and only with the user's permission;
• a database may hold 1 GB;
• a statement that runs longer than 10 seconds is stopped.

Share a large table with another package#

A channel message holds at most 256 KB. To hand a large table to another package, send a dataset reference instead of the rows:

ctx.channel('splunk.table').send({
  dataset: { package: 'splunk-table', table: 'search', total, columns, fields },
});

The receiver declares db and reads the pages it needs:

const page = await db.readPage({ package: msg.dataset.package, table: msg.dataset.table, offset: 0, limit: 1000 });

The user is asked once whether the receiver may read that package's tables. The Splunk table app and the USL lab work this way. See Apps that talk to each other.

Take the data with you#

Download on an app's page saves the app as a .wardian file. Tick Include my data to add its storage, its layout and its tables. Wardian lists what goes in first. Keys, accounts, permission answers and history never go in. See Share, import and remove apps.

This page is docs/site/data.md in the repository. Something wrong or missing? Change that file.