SQL and parameters

Write SQL with fixed statement structure and pass changing values as parameters. This keeps request data separate from executable SQL and makes queries easier to test.

Selectors are values#

In a saved Database script, Cloudgate binds ${...} selectors as command parameters. A selector cannot replace a table name, a column name, or an arbitrary WHERE clause.

Keep the schema names fixed and validate input before querying. If clients can choose a sort field, map their choice to a small set of developer-authored statements instead of injecting the supplied name into SQL.

Explicit parameter input#

A parameterized script starts with the Cloudgate marker and uses named parameters:

-- cloudgate:parameters
SELECT id, title, status
FROM Tasks
WHERE status = @status
ORDER BY id DESC
LIMIT 20;

Supply a JSON object in the Database node's input with the matching parameter name:

{
  "status": "open"
}

For dynamic input, use validated values or a previous node's JSON object as supported by the input template. Test missing parameters and null values against your selected database engine.

Use the database's quoting rules#

For new SQLite SQL, use single quotes for literal strings and double quotes for identifiers:

INSERT INTO "Tasks" ("title", "status")
VALUES ('Review O''Brien account', 'open');

Bound parameters are preferable for runtime values. Compatibility with older quoting behavior is not a reason to build SQL from arbitrary text.

SQL generated by a Function#

An advanced workflow can make the entire Database script one reference, such as ${BuildQuery}, to a successful Function that ran earlier in the same execution. That Function's actual output becomes the SQL source.

This mode is for developer-authored SQL generation. Request selectors and stored keys cannot impersonate the Function result. Generated SQL is not recursively expanded and cannot contain ${...} placeholders. Prefer a parameterized statement with input JSON when generated SQL needs user data.

Test this mode with a full workflow run: isolated node tests and resumed sessions cannot supply an earlier Function result that did not execute in the current run.