Create a custom query

When the wizard doesn't fit — analytics, reports, multi-table joins, stored-procedure calls, anything that doesn't map 1-1 to a single table — write the query by hand under the Unclassified tab.

This page walks the editor form field by field, with the verified shape from the QueryDef schema.

---

Where it lives

Settings → Connectors → pick a connector → mode bar: Unclassified → ＋ Add query.

The page prompts for a name, creates an empty custom query, opens its editor.

---

The editor form

---

Field by field

name

Read-only in the editor. The name is set when you click ＋ Add query and is the permission key for the query — sql:<connector>:<name>. Rename via the Rename button at the top (cross-file — every screen / menu / dictionary reference is updated in one pass; refuses to run on unsaved local edits).

Naming rules:

Rule	Why
Letters, digits, underscores.	TOML key, URL segment and permission string in one go.
Starts with a letter or underscore.	Same.
Avoid the _get / _put / _post / _delete suffixes.	The Tables tab would pick it up and try to group it into a CRUD set.

Naming conventions that scale well:

Pattern	Example
<entity>_<action>	customer_balance, invoice_send
<verb>_<noun>	get_top_clients, mark_paid
report_<topic>	report_monthly_revenue, report_overdue_invoices

type

Dropdown — drives the tab classification. For a hand-written standalone query, keep custom. The other values move the query elsewhere on the page:

Value	Tab
custom	Unclassified (this page).
table	Tables (only useful when the name matches the <base>_<crud> convention).
sequence	Sequences.
lookup	Lookups.

Switching type doesn't rewrite the query — only the page it's filed under. For sequences and lookups you usually want the Scaffold modal anyway, which writes the dictionary entry alongside.

writable

Boolean toggle.

State	Allowed
Off (default)	SELECT and CTE-only statements.
On	INSERT / UPDATE / DELETE / CALL / MERGE / DDL — anything that mutates the database.

Leave it off for read queries — the safety gate refuses non-SELECT execution otherwise. The CRUD Wizard already sets it for _put / _post / _delete; on a hand-written query you flip it yourself when needed.

sql

The SQL editor accepts a single statement (the common case) or a per-dialect map — see Per-dialect SQL variants.

Three features the editor surfaces:

Feature	What it does
Syntax highlighting	Keywords coloured; mismatched brackets flagged.
Autocompletion	Tables and columns from the connector's pool. Hit Ctrl+Space to invoke.
:name placeholders	Any :identifier in the SQL is recognised as a bind parameter — declare it in the params block below.

A few rules from how the engine binds:

• Use :name for parameters, never literal string interpolation. The engine binds them through the SQLAlchemy driver — '%' || :search || '%' is the LIKE pattern you want, not '%' + search + '%'.
• Reserved suffix _ORIGINAL is used by the dialog Save for UPDATE WHERE matching (see Create from a database table for the convention).
• A single statement per query. For chained statements, use a CALL to a stored procedure or split into multiple Liberty queries.

params

The list of declared parameters — every :placeholder in the SQL gets one row. Each entry carries:

Field	What it does
name	The placeholder name (without the colon). Matched case-insensitively.
label	What the form input shows above the field. Falls back to name when empty.
default	Pre-filled value. Blank means the caller can omit it (the engine binds SQL NULL).

Adding rows here doesn't change the SQL — it just tells screens and the AI assistant how to render an input for each placeholder. Skip this block when the query has no :placeholders; the engine accepts undeclared params at runtime (a column dialog can bind any :column_name it sees).

For binding values from other screens / sources (a row click, a parent filter, a chained action), see Parameter binding.

label (Advanced)

Short name shown in dropdowns elsewhere — the screen designer, the menu target picker, the action editor. Defaults to the query name.

description (Advanced)

Longer text. Shows on the Tables list under the base name, and as a tooltip when the operator hovers the query in the screen designer's read-query picker.

---

Top-bar actions

The buttons at the top of the single-query editor:

Action	What it does	Notes
← Back	Returns to the Unclassified list (no save).	Unsaved edits stay in the page-level dirty state — Save on the top toolbar persists them.
✎ Rename	Cross-file rename.	Refuses on unsaved local edits; updates every reference.
⎘ Clone	Per-query duplicate — prompts for new name.	Default suggestion <name>_copy. See Clone a query or a connector.
🗑 Delete	Removes the query from the connector.	A confirm modal first.

---

Save and reload

The page-level Save at the top of the Connectors page commits the connector file and triggers a hot reload. The new query is callable immediately at /api/sql/<connector>/<name> (with the bound params as query-string values for GET, JSON body for writable POST).

---

Verifying the query works

There's no dedicated Test query tab on the SQL connector — the test path is one of three:

Path	When
Use it from a screen.	When the next step is building the screen anyway.
Hit the REST endpoint directly.	For a quick smoke test — GET /api/sql/<connector>/<query>?from_date=2026-01-01&to_date=2026-01-31.
Ask the AI assistant.	"Show me the result of monthly_revenue for last month" — the assistant will run the query and return rows. Requires ai:chat and sql:<connector>:<query> permissions.

The REST path is the fastest debug — error messages come back verbatim, and you can pass params via URL.

---

When to use this vs the wizard

Use the wizard	Use the custom editor
The table exists in the DB and you want the standard CRUD shapes.	The query joins multiple tables.
You want consistent :NAME_ORIGINAL wiring on UPDATE.	The query is a GROUP BY aggregate.
You want a _delete keyed on the primary key.	The query returns a derived column the table doesn't have.
	The query is a stored-procedure call.
	The query is a write that's not a simple UPDATE / INSERT / DELETE (a MERGE, a TRUNCATE).

---

What's next

• Clone a query or a connector — start from an existing query instead of from scratch.
• Parameter binding — give the :placeholder params labels, defaults and bound sources.
• Per-dialect SQL variants — ship one query with different SQL on Postgres vs Oracle.