SQL Connectors

The SQL Connectors editor is where named SQL queries are declared. A SQL connector is a reusable "talks to one database" definition — a JDBC connection plus a list of named queries — that the rest of NomaUBL references by name. It mirrors the structure of API Connectors: the same picker drives Action Bindings and Notification Rules, with a · SQL suffix in the dropdown so the kind is visible.

Typical targets are:

• A second NomaUBL database for cross-environment lookups.
• A source ERP database — JD Edwards, SAP, NetSuite or a custom schema — when an action needs to read a customer email, a payment date, a status code that the HTTP API does not expose.
• An operational database that an action must update — for example flagging an invoice as sent in a downstream system, archiving a row, recording an audit entry.

The page applies to documents from any source system, since the SQL connector can point at any reachable JDBC database.

New in 2026.05.7

SQL connectors are brand new in 2026.05.7. They sit alongside API connectors in Settings, and both kinds can be wired into action bindings and notification rules — including the new multi-call list with Stop on failure and {call.N.fieldName} response chaining. See the 2026.05.7 release notes for the full list of changes.

The editor has three tabs:

1. Connection — database type, JDBC URL, credentials, schema and query limits.
2. Queries — collapsible per-query cards: name, label, description, SQL, parameters, Writable flag.
3. Test — runs a query against the live database and shows columns + rows or affected count.

---

Opening the editor

• Settings → + Add SQL at the top right of the resource sidebar to create a new SQL connector.
• Existing SQL connectors live under the SQL group of the resource sidebar with an sql badge — click any of them to open the editor.

---

At a glance

---

Tab 1 — Connection

Connection

Field	Values	Description
Database Type	Oracle / PostgreSQL	Backend type. Drives the JDBC URL placeholder and the dialect-specific parsing in the runtime.
JDBC URL	text	Full JDBC connection string. jdbc:oracle:thin:@host:1521/service_name for Oracle, jdbc:postgresql://host:5432/database_name for PostgreSQL.
Schema	text (optional)	Default schema. Used by the SQL — no automatic prefixing happens at runtime, the value is informational and lets you write unqualified table names when the JDBC user already has the right current_schema.

Credentials

Field	Description
DB User	Database account name with the privileges required by the queries declared on this connector. Read-only access is enough for SELECT-only queries.
DB Password	Password for the DB account. Stored Base64-encoded in the config file, the same way as db-nomaubl and db-jde.

Limits

Field	Default	Description
Query timeout (ms)	30000	Per-statement JDBC timeout. Long-running queries are cancelled past this.
Max rows	1000	Hard cap on rows returned per SELECT call. A runaway query cannot exhaust the JVM heap.

---

Tab 2 — Queries

The Queries tab is a list of collapsible cards. The card header shows a READ / WRITE badge, the query name, and the description as a secondary line so a connector with several queries reads as a punch-list at a glance. Click a header to expand the editor; click + Add query to append a new one.

Per-query fields

Field	Description
Name	Identifier of the query. Used as the endpoint value when wiring the query into an action binding or a notification rule (connector / name).
Label	Short human-readable title shown next to the name in the dropdowns (name — Label).
Description	Free-form sentence shown as the collapsed-card secondary line. Reading the rule list out loud should make sense.
Writable	Yes / No. Required to be Yes when the SQL is anything other than SELECT — otherwise the runtime rejects the call before the connection opens.
SQL	The SQL itself, with :name placeholders for parameters. The first non-comment, non-string keyword decides the statement type (SELECT / INSERT / UPDATE / DELETE / MERGE).
Parameters	Spec of :placeholder tokens used in the SQL. Format: `name

Statement-type allow-list

Only SELECT, INSERT, UPDATE, DELETE and MERGE are accepted. Anything else — DROP, TRUNCATE, ALTER, GRANT, CREATE, etc. — is rejected before the connection even opens. A query that begins with one of those keywords cannot be saved as Writable=No either: the runtime always re-parses the first keyword on call, so the gate is not bypassable by editing the resource by hand.

Parameter binding (:name → ?)

Parameter binding goes through PreparedStatement. The runtime parses the SQL, rewrites every :name token to a positional ?, and binds the values positionally — values are never string-substituted into the SQL. The parser respects:

• single-quote string literals ('O''Brien'),
• double-quoted identifiers ("customer.name"),
• line comments (-- …) and block comments (/* … */),
• the PostgreSQL ::type cast operator ('foo'::text).

Tokens inside any of those constructs are passed through unchanged.

The parameter spec drives the Test tab and the call-site editor: each declared :name becomes a labelled input pre-filled with its default value. Spec rows are semicolon-separated; each row has up to three pipe-delimited fields. Empty fields are accepted (a parameter with no default just renders an empty input).

Writable flag

Writable=No is the safe default. Setting it to Yes is required for any non-SELECT statement and is what the runtime checks at call time:

• A SELECT query with Writable=Yes runs fine — the flag widens the allow-list, it does not narrow it.
• An UPDATE query with Writable=No is rejected with an error message in the test panel and STOP in the audit trail at runtime.
• A typo in a notification rule that points to a non-Writable query but tries to fire DELETE FROM … cannot succeed — the gate runs before the connection opens.

---

Tab 3 — Test

A built-in runner that executes the selected query against the live database and shows the result.

Element	Description
Query	Dropdown of every query declared on the Queries tab, with name — Label rendering. Selecting a query pre-fills the parameter rows from the spec.
Parameters	One row per declared :placeholder, plus an Add param button for ad-hoc parameters not in the spec. Each row has a name input and a value input.
Run	Sends the call to /api/sql-connectors/{connector}/{query} with the parameter values. The result is rendered next to the button.

Result panel

Statement type	What's shown
SELECT	Statement label + row count + duration on the green-on-success line; below it, a compact table of columns × rows. The runtime caps the number of rows to Max rows on the Connection tab.
INSERT / UPDATE / DELETE / MERGE	Statement label + affected count + duration on the green-on-success line. No table — the runtime returns only the update count for non-SELECT calls.
Error	Red error box with the JDBC / parser error message (SQLException, allow-list violation, missing :placeholder value, etc.).

The Test tab calls the live database — Save the connector first if you have just edited it; otherwise the test runs against the previously-saved version of the query.

---

How SQL connectors are used

SQL connectors plug into the same call sites as API connectors:

• Action Bindings — the regulatory buttons in the invoice detail modal can call any SQL query, alone or in a chain, via the multi-call list shipped in 2026.05.7.
• Notification Rules — same multi-call list, fires automatically whenever the rule matches a status change.

In both cases, the connector dropdown lists API connectors and SQL connectors merged with · API / · SQL suffixes; the target dropdown loads endpoints or queries depending on the picked connector kind.

Outputs and response chaining

When a SQL call returns, its outputs land in the dispatch context under call.N.* keys, where N is the 1-based index of the call within the rule or binding. Subsequent calls reference them with {call.N.fieldName} placeholders in their parameter values.

Field	Source
call.N.success	true when the call ran without error.
call.N.statementType	SELECT / INSERT / UPDATE / DELETE / MERGE.
call.N.rowCount	For SELECT — number of rows returned.
call.N.updateCount	For non-SELECT — number of rows affected.
call.N.error	Error message when success is false.
call.N.<column>	For SELECT — every column of the first row, by name.

Example: a rule that first looks up the customer's email via a SQL query, then sends a follow-up HTTP webhook, would set the webhook payload's to parameter to {call.1.EMAIL} (the EMAIL column of the first row of call #1).

---

Tips & best practices

• Keep one connector per database, not per query. A crm connector with five named queries is easier to read than five connectors each holding one query. The dropdown groups queries under the parent connector.
• Name queries by intent, not by SQL shape. findCustomerEmail reads better than selectKcoFromF0101. The body of the SQL is one click away in the editor — the name is what the rule list shows.
• Start every connector with Writable=No. Flip the flag to Yes only on the queries that actually need to write. A SELECT-only connector cannot be coerced into running a DELETE even if a typo points the rule at the wrong query.
• Use :name placeholders for everything user-supplied. Concatenating values into the SQL string defeats the parameter-binding safety net. The parser deliberately ignores tokens inside literals and comments so : characters inside strings are safe.
• Set Max rows tighter than the default for dashboard-style queries. A widget that reads the top 10 rejected invoices does not need 1000 rows back; capping at 50 keeps the UI snappy and the JDBC fetch cheap.
• Test before saving when changing an existing query. The Test tab uses the in-memory edit of the query when you have unsaved changes, but the Run button calls the live database — there is no rollback. A Writable=Yes query under test will commit if the SQL says so.
• Pair SQL connectors with API connectors via response chaining. A notification rule can read a value from a SQL query and forward it as a parameter to an HTTP webhook, without code on either side. See the Notification Rules — Actions tab for details.
• Keep credentials in a dedicated DB account. Even on a read-only connector, give the JDBC user just the privileges the queries need — SELECT on a small set of tables, no broad schema access.