DB Script node

Database

What it does

The DB Script node runs SQL statements, or calls a stored procedure, on a database connection before the pipeline reads any data. Use it to prepare tables, clean up a staging area, or start a procedure that does the work itself.

It receives no rows, and the SQL is sent to the database as written.

When to use

• The flow needs DDL first: create a staging table, drop an old view, add an index.
• A staging area must be emptied before the pipeline reads, with DELETE or TRUNCATE.
• A stored procedure does the load, and the flow only needs to start it.

DB Script runs before the Sources. For SQL over rows between reading and writing, use a Script node.

Configure

The node panel

• Header: the connector's logo, the node's kind and its connector.
• Status: says the node is ready to run, or lists what needs fixing.
• Node name: the name shown on the card.
• Connection: the connection, with Edit to open it.
• Action: the saved script this node runs, with Configure and the saved-script picker. A node whose SQL is held on the node itself, such as the Data Pipeline Builder's Create target table step, runs without a saved script, and its card reads Script: Inline SQL.
• Execution hooks: the pre- and post-steps that run before and after the script.
• Advanced: notifications. It starts closed.

Configure Script

Connect Start or another DB Script to the node's dep port. Several DB Scripts can be chained, across different databases. Then press Configure Action to open Configure Script.

• Connection: SQL Server, PostgreSQL, MySQL, Oracle or Snowflake.
• Action name: required, for example Create staging tables. The script is saved on this action.
• Execution mode:
  • SQL Script: one or more statements separated by ;.
  • Stored Procedure: search for a procedure by name, or type it, such as dbo.RunBatch. Its parameters load when you enter the name; each value can be literal or {{var}}.
• Override connection default database: run against another database on the same connection.
• Output handling:
  • No output (DDL / fire-and-forget).
  • Return rows: every returned row leaves on out.
  • Map first row to variables: map columns of the first row to variables in Row to variable map, such as ResultId to {{invoiceId}}.
  • Capture output parameters: a procedure's output parameters.

Validate Script reports risky statements as warnings: DROP without IF EXISTS, TRUNCATE TABLE, and DELETE without WHERE. Warnings do not block saving. On SQL Server and PostgreSQL it also runs the script inside a transaction and rolls it back, so syntax and permission errors show before a run.

Run script runs the script on the live connection. It is a real run: its changes are kept.

Save action saves the script and keeps the dialog open. Done saves it and closes the dialog, and stays open if the save fails. Close closes it without saving.

Ports

Port	Direction	Kind	Notes
dep	in	control	Sets the order: the script runs after the node before it. No rows arrive here.
out	out	control	The returned rows, under Return rows. Empty under the other output options.
error	out	control	Receives a row with the SQL error, the statement number and the database's message.

A DB Script can feed a Source or another DB Script.

How it runs

• Statements run in order on one connection. When one fails, the rest do not run.
• The connection's statement timeout applies. Change it on the connection.
• The risky-statement warnings are advice. The run does not block them.

Examples

Prepare a staging table. Start > DB Script (CREATE TABLE IF NOT EXISTS stage.orders (...)) > Source > Transform > Load into stage.orders.

Empty a queue table before reading. Start > DB Script (DELETE FROM stage.queue WHERE processed = 1;) > Source reading stage.queue.

Start a procedure and keep its result. Execution mode Stored Procedure, procedure dbo.RunBatch, Output handling Map first row to variables with BatchId mapped to {{batch_id}}. Later nodes read the batch id from that variable.

Troubleshooting

Symptom	Cause	What to do
SQL script is required	The saved action has no script.	Open Configure Script, write the script and save.
A database error on error	A statement failed. The statement number and message say which.	Fix that statement, then Validate Script.
The run times out on a long statement	The connection's statement timeout was reached.	Raise the timeout on the connection.
The node cannot connect to a Transform or Load	DB Script only feeds a Source or another DB Script.	Put a Source after it.
A procedure's parameters do not appear	The name is not found on the connection.	Check the schema and name, or pick it from Search procedures.

Related nodes

Source

Read rows after the setup step

Load

Write rows to a target

Script

Query rows with DuckDB SQL in the middle of a flow