Named Queries

Last updated

A named query is a piece of SQL saved once under a name and then run by that name from dashboard widgets, buttons, reports and scripts. Callers only supply parameter values; the SQL itself can't be changed by whoever runs it. Values are always bound by the database driver, never pasted into the SQL, so a value containing quotes or a semicolon is just a value.

Open Named Queries in the sidebar.

Creating a query

1. Click New query.
2. Fill in Name, Data source, Description and the SQL (see the field table).
3. Reference each value as :name in the SQL, and declare it under Parameters with the + button.
4. Click Save. Validation errors appear in red under the form.
5. Use Test run (appears once the query is saved) to try it with real values.

SELECT reason, COUNT(*) AS stops, SUM(duration_s) / 60.0 AS minutes
FROM ct_downtime_log
WHERE line = :line AND started_at >= :start
GROUP BY reason
ORDER BY minutes DESC

With parameters line (number, required) and start (date, required).

Fields

Field	What it does	Default	Allowed values
Name	How callers refer to the query. Case-sensitive.	—	Starts with a letter; letters, digits, _; up to 64 characters.
Data source	Where the SQL runs. Local (appliance database) or a connection from Remote Databases (listed as name and type).	Local (appliance database)
Description	Shown in the list and to scripts via namedQueries.list().	empty
SQL — one statement, with :name placeholders	The statement.	SELECT 1 AS example	One SELECT (or WITH … SELECT), INSERT, UPDATE or DELETE.
Cache TTL (seconds)	Reuse a result for identical parameter values for this long. 0 disables caching.	0	0–3600. Forced to 0 for queries that modify data.
Max rows	Rows returned are cut at this number, and the result is flagged as truncated.	1000	1–10000
Allowed roles	admin / editor / operator / viewer check boxes.	none ticked	See Permissions.
Parameters	One row per :name: name, type (string, number, boolean, date), required.	none	Name starts with a letter; letters, digits, _; up to 32 characters; no duplicates.

The list on the left shows each query's name, a writes badge for queries that modify data, disabled for disabled ones, and the allowed roles.

What the SQL may contain

• Exactly one statement. A trailing ; is fine; two statements are refused.
• CREATE, ALTER, DROP, TRUNCATE, ATTACH, DETACH, PRAGMA, VACUUM, REINDEX, GRANT, REVOKE, REPLACE INTO, EXEC and EXECUTE are refused anywhere in the SQL itself.
• A SELECT that uses INSERT, UPDATE, DELETE, MERGE or INTO as SQL is refused, since that can change data or create a table.
• Words inside a quoted string ('Update pending'), a double-quoted name ("update") or a comment (-- and /* */) are not checked, so they don't cause a refusal. The check reads quotes the way every supported database does, and refuses a statement whose quoting those databases would read differently — for example a string ending in a backslash, or a keyword inside a name quoted with backticks or [brackets].
• Every :name must be declared: SQL references undeclared parameter ":x".

Local queries run against the appliance's user-data database, which holds your local Data Tables and nothing else. Data Table physical names start with ct_ — the Data Tables page shows each one as SQLite: ct_…. Users, settings and other configuration tables are not reachable. See Data Tables.

Remote queries run on the connected database in its own SQL dialect. Parameters are rewritten for that driver (@p0 for SQL Server, ? for MySQL and ODBC, :1 for Oracle).

Parameter values

• An omitted or empty value counts as missing: Missing required parameter "x" if required, otherwise it's bound as NULL.
• number: must convert to a finite number (Parameter "x" must be a number).
• boolean: true, 'true', 1 or '1' become 1; anything else 0.
• date: anything new Date() understands; bound as an ISO string in UTC, e.g. 2026-09-14T06:00:00.000Z (Parameter "x" must be a date). Compare against columns stored in the same format.
• string: bound as text.
• A value for a parameter that isn't declared is refused: Unknown parameter "x".

Queries that modify data

An INSERT, UPDATE or DELETE gets extra rules, shown as a warning under the SQL while you type:

• Only an administrator can save one (Only an administrator can save a query that modifies data).
• With no roles ticked it is admin-only, not open to everyone.
• It runs only from a deliberate action — a dashboard Button, a script, the Test run, or the API. Used as a widget data source it is refused: it would run on every refresh.
• It can never run from a public dashboard or without signing in.
• Against a remote database, that connection must allow named-query writes (set on the connection under Remote Databases).
• Results are never cached, and every run is written to the activity log with the number of rows affected.
• Test run asks for confirmation (Run "x"? It will modify data and cannot be undone.) and its button reads Run & modify.

Test run

Type a value for each parameter and click Run. Results show the row count (or rows changed), the time, · from cache when the cached result was used, and · truncated at the row cap when Max rows cut it off, followed by the rows. The test uses the same binding and permission checks as a dashboard. Clear cache drops cached results without editing; saving a change also clears them.

Where named queries are used

Caller	How	Rules
Table and Bar Chart widgets	Data Source = Named Query, pick the query and parameter values; Refresh (ms) default 15000	Read queries only. Runs as the viewer, so the viewer's role must be allowed.
Public dashboards	Same widgets	Only queries with no roles ticked, and only read queries.
Dashboard Button	Action = Named Query (write)	Runs as the person pressing it.
Reports	Report data source	Runs with full authority, read queries only.
Scripts	await namedQueries.run('name', { ... })	Runs with the authority of whoever last saved the script's code.
REST	POST /api/named-queries/:idOrName/execute with { "params": { ... } }	Runs as the caller.

Permissions

Action	Who
See the query list	Any signed-in user
Create, edit, delete, Clear cache	admin, editor (write queries: admin only)
Run a read query	With no roles ticked: any signed-in user. With roles ticked: those roles, plus admin always.
Run a write query	The ticked roles, plus admin; admin only when none are ticked

Note: From a script, a read query runs without checking Allowed roles. Roles are checked for write queries only.

Caching and performance

• A cached result is keyed by query and exact parameter values, kept in memory (lost on restart), at most 500 entries.
• A slow aggregate used on several dashboards is a good candidate for a 60–300 s TTL. On the appliance, a query scanning a large Data Table on a 5-second refresh from many screens adds up quickly.
• Rows beyond Max rows are never fetched into memory.

Troubleshooting

Message	Cause	Fix
Name must start with a letter and contain only letters, numbers and underscores	Invalid name	Rename, e.g. downtime_by_reason.
SQL must be a single SELECT, INSERT, UPDATE or DELETE statement…	Two statements, a forbidden keyword, or a write word inside a SELECT	See "What the SQL may contain".
That database does not allow named queries to modify data…	Remote connection without write permission	Enable it on the connection under Remote Databases.
no such table: …	Local query using the Data Table's display name	Use the ct_… physical name.
You do not have permission to run "x"	Caller's role isn't ticked	Tick the role, or leave all unticked for a read query.
"x" modifies data, so it must be run by an explicit action…	A write query used as a widget data source	Use a Button with Named Query (write).
"x" is restricted to … and cannot run on a public dashboard	Roles ticked on a query used on a public dashboard	Untick all roles, or keep the dashboard private.
Query "x" is disabled	The query is disabled	Enable it.
Only read-only SELECT queries are allowed / Query must return rows	The local engine found the SELECT isn't a plain read	Simplify the statement.

API endpoints

Method	Path	Purpose
GET	/api/named-queries	List definitions
GET	/api/named-queries/:id	One definition
POST	/api/named-queries	Create (admin, editor)
PUT	/api/named-queries/:id	Update (admin, editor)
DELETE	/api/named-queries/:id	Delete (admin, editor)
POST	/api/named-queries/:idOrName/execute	Run; body { "params": { ... } }
POST	/api/named-queries/:id/invalidate	Clear cached results (admin, editor)

See also

• Custom Data Tables
• Scripting API Reference
• Table Widget
• Button Widget
• Dashboards Overview
• Remote Databases (SQL Server, MySQL, PostgreSQL, Oracle, ODBC, MongoDB)