SQL Databases

QUBIQ connects to relational databases as a consumer — to read data onto screens, log values, run named queries, and store the audit journal. This is separate from the configuration database, which is internal and not user-accessible.

Supported drivers

Driver	Typical port	Notes
PostgreSQL	5432	Also covers Postgres-compatible engines.
MySQL	3306	MariaDB included.
SQL Server	1433	Windows and Linux hosts.
SQLite	—	File-based; sandboxed, see below.

Configuration

Field	Description
Name	Connection identifier.
Driver	One of the above.
Host / Port	Server address (not used for SQLite).
Database	Database or catalog name.
Username / Password	Credentials. Encrypted at rest, never returned to the browser.
SSL	Require an encrypted connection.
Max connections	Pool ceiling. Sized to the database's limits, not to QUBIQ's demand.
AI username / password	Optional read-only credential used by the AI assistant instead of the primary one.

SQLite specifics

Field	Description
Path	File path, confined to the user-databases/ sandbox inside the data directory.
Create if missing	Create the file on first open.

SQLite paths are resolved through a sandbox at both create time and open time. Directory traversal, absolute paths outside the sandbox, and any attempt to open the internal configuration database are rejected; only .db, .sqlite and .sqlite3 extensions are permitted.

That is a security boundary, not a convenience limit: a user-defined connection must never be able to name an arbitrary file on the host.

Pooling and sizing

The pool is shared by everything that uses the connection — named queries, pipeline nodes, bound widgets, the AI assistant.

Size max connections against the database's limit and the number of QUBIQ services that will use it, not against your expected query rate. A pool larger than the server allows produces connection-refused errors under load; a pool of 5–20 is right for most deployments.

What you can do with a SQL connection

Use	Where
Bind a widget to query results	Query bindings
Run a saved, parameterised query	Named queries
Read/write from a pipeline	The SQL Query node — Pipeline nodes
Read/write from a script	system.db.* — Scripting API
Store the security audit journal	Audit journal

Named queries

A named query is a saved statement with declared parameters and its connection baked in. Prefer them over inline SQL:

• The statement lives in one place; changing it does not mean editing every screen.
• Parameters are bound, not concatenated — the injection surface disappears.
• A screen or script refers to it by name, so it needs no database credentials of its own.

-- named query: production_by_shift
SELECT shift, SUM(units) AS units
FROM production
WHERE ts >= :start AND ts < :end
GROUP BY shift
ORDER BY shift

Called as system.db.runNamedQuery("production_by_shift", {start, end}) or bound directly to a table widget.

Declared parameters

Each :name in the statement is declared in the editor's parameter table:

Column	Notes
Parameter Name	The name after the colon.
Data Type	string, number, boolean or date.
Means	Optional. What the value refers to, so the AI assistant can fill it from an operator's own words — see below.

Every declared parameter is required. Changed in 1.0.7 The editor used to carry a Req column, and unticking it never did what it said: the statement contains :shift literally, so the database cannot run it without a value for it. An "optional" parameter could only ever be passed empty, and a caller told it could leave one out got a worse error than the one the tick box was supposed to prevent. The column is gone. A query saved with a parameter marked optional still loads; the flag is ignored.

Means links a parameter to something real:

For a	Means can be	Effect
String	A plant-vocabulary kind — Equipment, Area or line, Material or product, State, Metric, Shift, Other	The assistant resolves what the operator called something into the code the database holds before it runs the query. Advisory: a value missing from the vocabulary is not refused, because the vocabulary maps the words people use rather than listing every code in the plant.
Date	Window start, Window end, Point in time	The server computes the date from a window name like last 7 days, instead of the model doing date arithmetic against a "today" it has to guess.

Changed in 1.0.7 Three changes make Means do what it shows:

• It is checked when you save, as an external system's parameter already was. A typo used to save cleanly and silently unhook the parameter from the vocabulary.
• Point in time now works. It was offered and accepted but read nowhere, so choosing it was identical to leaving the field blank. It resolves to the start of the named window — never the end, which for today is tomorrow's midnight.
• A mixed-case kind (Equipment) now matches the vocabulary it names; it used to match nothing.

When the assistant calls a saved query without a required value, it now sees the refusal — which parameter, its type and meaning, and the known values — and can fill the gap. Before, it was told only that the query "could not be run", which it could not tell apart from an outage, so it retried instead. Database errors are still withheld from it.

Colons in strings and comments Changed in 1.0.7

A : is a parameter only where it could be one. A colon inside a string literal, a comment, a quoted, backtick or bracketed identifier, a PostgreSQL dollar-quoted string, a :: cast or a := assignment is left exactly as written:

SELECT to_char(ts, 'YYYY-MM-DD"T"HH24:MI:SS') AS stamp,   -- :MI and :SS are text
       starts_at::text                                    -- a cast, not a parameter
FROM shifts
WHERE line = :line                                        -- the only parameter here
  AND note <> 'handover at 09:30'

Before 1.0.7 every colon was read as the start of a parameter, whatever surrounded it. The worst part was that it rarely failed: the statement the database received was not the one you wrote, and it came back with the wrong number of rows and no error — a timestamp format mask returned one row of five, a comment mentioning a time returned none. The rules follow each engine's own manual: PostgreSQL block comments nest and MySQL's do not, MySQL also takes # comments and needs a space after --, and an arr[1:3] slice keeps its colon.

A query that ends in a -- comment also paginates and counts correctly now; the comment used to swallow the paging clause QUBIQ adds after it, so a paged table showed no rows.

Stored procedures and transactions

Capability	API
Call a procedure, receiving every result set	system.db.callProc(...)
Managed transaction — auto-commit, auto-rollback on throw	system.db.transaction(opts, fn)
Manual transaction handle	system.db.transaction(opts)

Transactions accept an isolation level (ReadUncommitted … Serializable, Snapshot), a readOnly flag and a server-side timeoutMs after which the transaction is rolled back automatically. That timeout matters: it means a browser disconnect cannot strand an open transaction holding locks.

For anything transactional, prefer a Gateway script, which runs entirely server-side. → Gateway scripts

Writing data

Two paths, with different guarantees:

Path	Guarantee
Pipeline SQL node	Covered by store-and-forward: buffered on outage, replayed in order, quarantined on permanent failure.
Script system.db.*	Executes immediately; failure is returned to the caller to handle.

If a write must not be lost when the database is unavailable, put it in a pipeline.

Security

• Give QUBIQ its own database account with the minimum rights it needs. A dashboard usually needs SELECT only.
• Use the AI credential for a read-only account if you enable the assistant on a production database.
• Prefer named queries to inline SQL. Where inline SQL is unavoidable, always use parameters.
• Upstream errors are sanitised before they reach a client — QUBIQ does not leak the database's raw error text or schema detail to the browser.

Troubleshooting

Symptom	Check
Test fails with a timeout	Firewall or the server not listening on that interface.
Authentication fails but credentials are right	Host-based auth (pg_hba.conf), or the account is restricted by source host.
Works, then "too many connections" under load	Pool larger than the server's limit; reduce max connections.
Queries slow only through QUBIQ	Missing index on the filter columns the binding adds; check the plan for the actual statement.
SQLite path rejected	Outside the sandbox, or a disallowed extension. Move the file under user-databases/.
TLS required by server	Enable SSL on the connection.

Next

→ MongoDB · QuestDB