CrateDB

Execute SQL queries, insert, and update data in CrateDB distributed databases with parameterized queries.

Overview

CrateDB distributed SQL database tool. Supports three operations: Execute Query (run arbitrary SQL with parameterized $1, $2 placeholders), Insert (add rows from input item properties), and Update (modify rows matched by key columns). CrateDB uses the PostgreSQL wire protocol and is accessed via the pg driver. Important: CrateDB does NOT support multi-row UPDATE syntax, so updates are issued as individual statements per item. Default schema is “doc” (not “public” as in PostgreSQL). All queries use parameterized values to prevent SQL injection.

Category: Data & Storage
Tool Name: cratedb
Version: 1

Appearance: Icon: lucide-Database | Color: #009DC7

Node Type

Action — processes input items and produces output

Input / Output

Direction	Port(s)
Input	Input
Output	Output, Error

Credentials

This tool requires CrateDB credentials. See the Credentials Guide for setup instructions.

Operations

Operation	Value	Description
Execute Query	executeQuery	Execute an SQL query
Insert	insert	Insert rows in database
Update	update	Update rows in database

Parameters

Execute Query (executeQuery)

Parameter	Type	Required	Default	Description
Query	string	Yes	—	The SQL query to execute. Use $1, $2, etc. for parameterized values (set via Additional Fields > Query Parameters). NEVER concatenate user input directly into the query.

Insert (insert)

Parameter	Type	Required	Default	Description
Schema	string	Yes	doc	Name of the schema the table belongs to. CrateDB default schema is “doc”.
Table	string	Yes	—	Name of the table to insert data into.
Columns	string	No	—	Comma-separated list of property names from the input item to use as columns for the new rows. Leave empty to use all input item properties.
Return Fields	string	No	*	Comma-separated list of fields to return in the RETURNING clause, or ”*” for all fields.

Update (update)

Parameter	Type	Required	Default	Description
Schema	string	Yes	doc	Name of the schema the table belongs to. CrateDB default schema is “doc”.
Table	string	Yes	—	Name of the table to update data in.
Update Key	string	Yes	id	Comma-separated list of column names to use for matching rows (WHERE clause). Normally “id”.
Columns	string	No	—	Comma-separated list of property names from the input item to use as columns to update. Leave empty to update all input item properties except the update key(s).
Return Fields	string	No	*	Comma-separated list of fields to return in the RETURNING clause, or ”*” for all fields.

All Operations

Parameter	Type	Required	Default	Description
Additional Fields	collection	No	{}	Optional settings for how the statement is sent.
— Mode	options	No	multiple	The way queries should be sent to the database.
				Options: independently (execute each query independently), multiple (default — sends multiple queries at once to database)
— Query Parameters	string	No	—	Comma-separated list of property names from the input item to use as positional query parameters ($1, $2, etc.). (shown when Operation is executeQuery)
Max Concurrency	number	No	10	Maximum number of items to process concurrently. Accepts 1–100.

Output Data

Each returned row becomes its own output item, and the row replaces the item JSON — the columns arrive at the top level, and the properties the item carried in are not preserved. Binary data on the input item is forwarded.

Operation	Output
executeQuery	Fans out — one item per row the query returned. A statement that returns no rows and is not a SELECT produces one item carrying success: true and rowCount.
insert	One item per input item, carrying the columns named in Return Fields for the inserted row. With no RETURNING output the item carries success: true.
update	One item per input item, carrying the columns named in Return Fields for the updated row. With no RETURNING output the item carries success: true.

A SELECT that matches nothing still emits one item — the input item with an empty _queryResult array added — so the branch never goes silent:

{
  "_queryResult": []
}

Reference columns downstream by name, e.g. {{ $json.id }} or {{ $json.rowCount }}.

Usage Examples

• Execute a SQL query against CrateDB with parameterized placeholders
• Insert rows into a CrateDB table from input item properties
• Update rows in a CrateDB table matched by key columns
• Query time-series data from CrateDB with WHERE filters
• Bulk insert sensor data into CrateDB

Example Configuration

Run a parameterized SELECT. Query Parameters names the item properties whose values fill $1 and $2 — never paste values into the SQL:

{
  "type": "cratedb",
  "parameters": {
    "operation": "executeQuery",
    "query": "SELECT id, name, price FROM doc.product WHERE quantity > $1 AND price <= $2",
    "additionalFields": {
      "queryParams": "minQuantity,maxPrice"
    }
  }
}

Query a time range in a time-series table:

{
  "type": "cratedb",
  "parameters": {
    "operation": "executeQuery",
    "query": "SELECT device_id, avg(temperature) AS avg_temp FROM doc.readings WHERE ts >= $1 AND ts < $2 GROUP BY device_id",
    "additionalFields": {
      "queryParams": "windowStart,windowEnd"
    }
  }
}

Insert named columns from each input item:

{
  "type": "cratedb",
  "parameters": {
    "operation": "insert",
    "schema": "doc",
    "table": "events",
    "columns": "id,name,description",
    "returnFields": "*"
  }
}

Insert every property the item carries:

{
  "type": "cratedb",
  "parameters": {
    "operation": "insert",
    "schema": "doc",
    "table": "sensor_data",
    "columns": "",
    "returnFields": "id"
  }
}

Update rows matched on id, writing only two columns:

{
  "type": "cratedb",
  "parameters": {
    "operation": "update",
    "schema": "doc",
    "table": "devices",
    "updateKey": "id",
    "columns": "name,description",
    "returnFields": "*"
  }
}

Match on a composite key and update everything else on the item:

{
  "type": "cratedb",
  "parameters": {
    "operation": "update",
    "schema": "doc",
    "table": "readings",
    "updateKey": "device_id,ts",
    "columns": "",
    "returnFields": "device_id,ts"
  }
}

Error Handling

Mode	Behavior
stop	Halts workflow on first error
continue	Skips failed items, passes successful ones through
errorPort	Routes failed items to Error output port

Tips

Execute SQL queries, insert rows, and update rows in CrateDB distributed databases. CrateDB uses the PostgreSQL wire protocol with default schema “doc”.