Using Data Tables from Scripts, Pages, Workflows and the API

Last updated

Everything that reads or writes a data table refers to it by its display name (the name shown in the list, e.g. production_log), not the stored ct_ name. Rename a table and every script, page and widget that uses it must be updated.

Where tables are used

From	How	Details
Scripts	db.query, db.insert, db.update, db.delete, db.tables, db.transaction	Below, and Script API
Custom pages	scadaink.db.* (Promises)	Below, and Custom pages
Dashboards	Data Table widget	Shows rows by table name
Forms	Form widget	Inserts a row per submission, with the same checks as Add Row
Workflows	Read DB and Write DB nodes	See Workflow I/O nodes
Transaction groups	Data Infrastructure → Transaction Groups	Log tag values into a table on a timer, tag change, rising edge, condition or schedule (Transaction Groups)
Reports	A Data Table dashboard widget placed in the report	The report's own data modules cannot read data tables — see Report modules
REST	/api/custom-tables/...	Below

Scripts

// Log a finished job
const row = db.insert('production_log', {
  part_number: 'BRK-2207',
  qty_good: 48,
  qty_scrap: 2,
  cycle_time_s: 41.6,
  first_article_ok: 1,          // use 1/0, not true/false
  operator: 'auto',
});
log('Logged job as row ' + row.id);

// Latest 10 rows for one part
const recent = db.query('production_log', {
  where: { part_number: 'BRK-2207' },
  orderBy: 'created_at',
  limit: 10,
});

// Correct a count, then delete a test row
db.update('production_log', row.id, { qty_scrap: 3 });
db.delete('production_log', 999);

// Several writes that must succeed or fail together
db.transaction(() => {
  db.insert('production_log', { part_number: 'BRK-2208', qty_good: 50 });
  db.update('production_log', row.id, { qty_good: 47 });
});

Call	Behavior
db.query(table, { where, orderBy, limit, offset })	where matches columns (or id) exactly, joined with AND. orderBy sorts ascending by a column, id or created_at; without it rows come newest first. limit is capped at 1,000; with no limit every row is returned
db.insert(table, row)	Returns { id }. Keys that are not columns are ignored. A required column that is missing, '', false or NaN throws Column "X" is required (0 is accepted)
db.update(table, rowId, changes)	Returns { changes } (0 if the row does not exist). Sets updated_at
db.delete(table, rowId)	Returns { changes }
db.tables()	Display names of all local and remote tables
db.transaction(fn)	Runs fn in one SQLite transaction (local tables only)

Scripts write values as given, without the type checks the page and REST API apply:

• Pass booleans as 1/0. true/false throw SQLite3 can only bind numbers, strings, bigints, buffers, and null.
• A string written to an integer column is stored as text.
• For local tables these calls are synchronous; for remote tables they return Promises, so use await.

Unknown table: Table not found: production_log. Read-only remote table: Table "X" is read-only.

Custom pages

const rows = await scadaink.db.query('production_log', {
  limit: 20, sortBy: 'created_at', sortDir: 'desc', search: 'BRK',
});
const created = await scadaink.db.insert('production_log', { part_number: 'BRK-2207', qty_good: 48 });
await scadaink.db.update('production_log', created.id, { qty_scrap: 1 });
await scadaink.db.delete('production_log', created.id);   // or scadaink.db.remove(...)
const names = await scadaink.db.tables();

• query options: limit (default 100, max 1,000), offset, search (text in any column), sortBy (a column, id, created_at or updated_at) and sortDir (asc or desc, default desc).
• These calls run as the signed-in viewer: reading works for any user, writing needs editor or admin. An operator's insert fails with a permission error.
• Values are checked and converted by type, and bad values are refused, e.g. Invalid value for "qty_good": Error: Expected integer, got: 4.5.
• Pages can read and write local tables only. tables() lists remote table names too, but querying one from a page answers Table not found.

Workflows

• Read DB: Table name, Max rows (default 100, max 1,000), optional where column and value variable, output variable (default rows). Newest first.
• Write DB: Table name, Operation (Insert or Update), Row ID for updates, and a Column values list — one entry per column, with a value that accepts {{var}} or a tag's current value. For inserts that need logic of their own, use a Run Script step with db.insert.

REST API

Method	Endpoint	Purpose
GET	/api/custom-tables	All tables (local and remote) with columns
POST	/api/custom-tables	Create a table (admin): { name, description, columns: [{ name, dataType, required, defaultValue }] }
GET	/api/custom-tables/:id	One table with columns
PUT	/api/custom-tables/:id	Rename or change description (admin)
DELETE	/api/custom-tables/:id	Delete a table (admin)
POST	/api/custom-tables/:id/columns	Add a column (admin)
DELETE	/api/custom-tables/:id/columns/:colId	Remove a column (admin)
GET	/api/custom-tables/:id/data	Rows: limit (default 100, max 1,000), offset, search, sortBy, sortDir (asc or desc, either case); response has pagination.total
POST	/api/custom-tables/:id/data	Insert { "row": { ... } } (editor)
PUT	/api/custom-tables/:id/data/:rowId	Update { "changes": { ... } } (editor)
DELETE	/api/custom-tables/:id/data/:rowId	Delete a row (editor)
GET	/api/custom-tables/by-name/:name	One table by display name
GET	/api/custom-tables/by-name/:name/data	Rows by table name
POST	/api/custom-tables/by-name/:name/data	Insert by table name

curl -X POST "http://scada.local/api/custom-tables/by-name/production_log/data" \
  -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
  -d '{"row": {"part_number": "BRK-2207", "qty_good": 48, "first_article_ok": true}}'

The REST API converts values by column type (true becomes 1 for a boolean column). On update, a key that is not a column is refused with Unknown column: X; on insert it is ignored.

Troubleshooting

Symptom	Cause	Fix
Table not found	Wrong display name, or the table was renamed	Check the exact name in the table list (case matters)
Column "X" is required	Missing required value; in scripts also '' or false	Supply a value; use 0 rather than false
SQLite3 can only bind numbers, strings, bigints, buffers, and null	A script passed true/false or an object	Use 1/0; JSON.stringify objects into a text column
Invalid value for "x": Error: Expected integer, got: …	Page or REST insert with the wrong type	Round the number or change the column type
Invalid column name: X	Reserved word, bad characters or over 64 characters	Rename the column, e.g. part_key instead of key
A table with that name already exists	Same display or stored name	Choose a different name
New, Add Column or Delete Table fails with a permission error	Needs admin	Ask an admin
An operator cannot add rows	Row writes need editor	Give the user the editor role, or write from a script

See also

• Scripting API Reference
• Custom Pages
• Input / Output Nodes
• Data Table Widget
• REST API Reference