Skip to main content

DB Script node

Database

The DB Script node runs one or more SQL statements against a database connection as a step in the flow. It's the imperative counterpart to the Load node's declarative "here are rows, write them to this table": DB Script lets you run DDL, cleanup, TRUNCATE, or a stored-procedure call as part of the workflow.

When to use​

  • You need to run DDL as part of the flow (create a staging table, drop an outdated view, add an index).
  • You want a targeted cleanup step (DELETE from a staging area, TRUNCATE a queue table) between a Source and a Load.
  • You need to call a stored procedure that itself does the load, and just want to trigger it from the flow.

Ports​

PortDirectionKindNotes
inindataOptional. When rows flow in, {{ row.column }} tokens in the script are substituted per row.
outoutdataThe input rows (or a summary row when emitStatementResults is on) after the script ran.
erroroutcontrolFires on SQL error.
notifyoutcontrolNotification policy port.

Configuration​

  • Connection. A database connection (SQL Server, PostgreSQL, MySQL, Oracle, Snowflake). File / API / Salesforce connections are not eligible.
  • Script. One or more SQL statements separated by ;. The Configure modal's editor is Monaco with per-dialect IntelliSense.
  • Substitutions. Tokens {{ row.column }}, {{ exec.variable }}, and {{ sys.runId }} are substituted before send.
  • Static hazard scan. The Validate button surfaces DROP without IF EXISTS, TRUNCATE TABLE, and DELETE without WHERE as warnings. The server enforces the same rules on /api/db-script/validate so a scripted caller cannot bypass.
  • Emit statement results. When on, each statement's result set becomes an output row.

Configure Action walkthrough​

  1. Connection — pick a DB-family connection. The dialect drives Monaco's language mode and the hazard scanner.
  2. Script — write the statements. Save runs Validate automatically.
  3. Validate — hazards render as chips with the offending statement index. Warnings don't block Save; you decide.
  4. Preview — optional, runs the first statement against the live connection with a small sample.

Runtime behaviour​

  • Statements run in order in a single connection. A failure mid-script routes to error with the failed statement index and the DB message.
  • When rows are flowing in, {{ row.X }} substitutions run per row — one script execution per row. Cross-row bulk substitution is not supported; use Load for that.
  • The static hazards are advisory. The runtime does NOT block a DELETE without WHERE — the operator sees the warning and decides.

Failure modes​

  • DB error. The error port gets the SQL state, error number, statement index, and message.
  • Substitution failure. A missing {{ row.X }} renders as empty. Cast columns explicitly in the script if a null would produce an invalid statement.
  • Timeout. The connection's default statement timeout applies; override on the connection config, not on the node.

Load

Write rows declaratively to a target

SQL

Query rows in-memory with DuckDB

Source

Read rows from a DB with a SELECT