<!-- BusyBot node reference — https://busybot.net/tools/cratedb/ -->

> Node: CrateDB (`cratedb`) · Action · v1
> Category: Data & Storage · Credentials: CrateDB (`crateDbApi`)
> Updated: 2026-08-16

# 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](https://busybot.net/credentials/crate-db-api/) 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:

```json
{
  "_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:

```json
{
  "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:

```json
{
  "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:

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

Insert every property the item carries:

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

Update rows matched on `id`, writing only two columns:

```json
{
  "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:

```json
{
  "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".