Data Tables: SQL Query Mode

Last updated

SQL Query mode runs one SQL statement at a time against your data tables (or a remote database) and shows the result. Use it for totals and joins the table view cannot do, bulk updates, and exporting a filtered result to CSV.

Run a query

1. On Data Tables, switch to SQL Query.
2. Target: choose Local SQLite (your data tables) or a remote connection.
3. Type the statement. Available Tables on the left lists the names to use — for local tables that is the stored name, e.g. ct_production_log.
4. Click Run Query, or press Ctrl+Enter (Cmd+Enter on a Mac). Tab inserts two spaces.
5. For INSERT, UPDATE or DELETE the button reads Run & Modify and asks "Run this UPDATE against the local database? It cannot be undone."
6. Click Export CSV to download the result as query-result-<timestamp>.csv.

The result shows "n rows" and the run time; a write shows "n rows changed".

What is allowed

Rule	Detail
One statement	A single SELECT, INSERT, UPDATE or DELETE. No schema changes (CREATE, ALTER, DROP), no multiple statements, no ATTACH
Reads	Editor or admin. Local reads see only data tables — the appliance's configuration tables (users, settings, secrets) are in a separate database that this mode cannot open
Writes	Admin only, and only into tables created under Data Tables
Result size	The first 1,000 rows are shown and exported: "Results truncated to 1000 rows (total: n)". The whole result is still read, so avoid unbounded SELECTs on very large tables

Errors you may see:

Error	Meaning
Only a single SELECT, INSERT, UPDATE or DELETE statement is allowed. Schema changes and multiple statements are not.	Two statements, or DDL
The SQL runner cannot access users, …	The statement names a protected configuration table
Only an administrator can modify data from the SQL runner	Editor tried a write
"x" is not a data table. The SQL runner can only modify tables created under Data Tables.	Write to something else
Could not determine which table this statement modifies, so it was not run.	Unusual write syntax; simplify it
That statement does not modify anything	Classified as a write but read-only
no such table: …	Wrong name — use the ct_ name

Writes through this mode are recorded in the audit log with the number of rows changed. They do not set updated_at automatically — set it yourself.

Examples

Good parts per part number today (timestamps are UTC):

SELECT part_number, SUM(qty_good) AS good, SUM(qty_scrap) AS scrap
FROM ct_production_log
WHERE created_at >= date('now')
GROUP BY part_number
ORDER BY good DESC

Clear a value the row form cannot clear:

UPDATE ct_production_log
SET operator = NULL, updated_at = datetime('now')
WHERE id = 1042

Delete rows older than 90 days:

DELETE FROM ct_production_log
WHERE created_at < datetime('now', '-90 days')

Join two data tables:

SELECT l.part_number, l.qty_good, p.description
FROM ct_production_log l
JOIN ct_parts p ON p.part_number = l.part_number
ORDER BY l.id DESC
LIMIT 50

Method	Endpoint	Purpose
POST	/api/custom-tables/query	Run { "sql": "...", "limit": 1000 } against local data tables (limit up to 10,000)

See also

• Custom Data Tables
• Using Data Tables from Scripts, Pages, Workflows and the API
• Report Module Reference