> Node: TimescaleDB (`timescaledb`) · Action · v1 > Category: Data & Storage · Credentials: TimescaleDB (`timescaleDb`) > Updated: 2026-08-16 # TimescaleDB > Execute SQL operations against TimescaleDB databases with parameterized queries for time-series data storage and retrieval. ## Overview Runs SQL against a TimescaleDB database — the PostgreSQL extension built for time-series data — over the PostgreSQL wire protocol. Three operations are available: Execute Query (arbitrary SQL with numbered placeholders), Insert (add a row built from the incoming item, with optional per-column type casts) and Update (change rows matched by one or more key columns, also with type casts). Values are sent to the database as query parameters rather than pasted into the statement text. SSL connections are supported. **Category:** Data & Storage **Tool Name:** `timescaledb` **Version:** 1 **Appearance:** Icon: `lucide-Database` | Color: `#FDB515` ## Node Type **Action** — processes input items and produces output ## Input / Output | Direction | Port(s) | |-----------|--------| | Input | `Input` | | Output | `Output`, `Error` | ## Credentials This tool requires **TimescaleDB** credentials. See the [Credentials Guide](https://busybot.net/credentials/timescale-db/) 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 | — | SQL query to execute. Use $1, $2, $3, etc. for parameterized values (set query parameter property names in Additional Fields > Query Parameters). NEVER concatenate user input directly into the query. | #### Insert (`insert`) | Parameter | Type | Required | Default | Description | |-----------|------|----------|---------|-------------| | Schema | `string` | Yes | `public` | Name of the schema the table belongs to. | | Table | `string` | Yes | — | Name of the table in which to insert data. | | Columns | `string` | No | — | Comma-separated list of the properties which should be used as columns for the new rows. Supports type casting with colon syntax (e.g. id:int,name:text,created_at:timestamp). | | Return Fields | `string` | No | `*` | Comma-separated list of the fields that the operation will return (RETURNING clause). Use * for all columns. | #### Update (`update`) | Parameter | Type | Required | Default | Description | |-----------|------|----------|---------|-------------| | Schema | `string` | Yes | `public` | Name of the schema the table belongs to. | | Table | `string` | Yes | — | Name of the table in which to update data. | | Update Key | `string` | Yes | `id` | Name of the property which decides which rows in the database should be updated. Normally that would be "id". Supports composite keys (comma-separated, e.g. tenant_id,record_id) and type casting (e.g. id:int). | | Columns | `string` | No | — | Comma-separated list of the properties which should be used as columns for rows to update. Supports type casting with colon syntax (e.g. name:text,status:text). | | Return Fields | `string` | No | `*` | Comma-separated list of the fields that the operation will return (RETURNING clause). Use * for all columns. | #### All Operations | Parameter | Type | Required | Default | Description | |-----------|------|----------|---------|-------------| | Additional Fields | `collection` | No | `{}` | Additional configuration for the operation. | | — Mode | `options` | No | `multiple` | The way queries should be sent to database. Can be used in conjunction with error handling modes. | | | | | | Options: `independently` (execute each query independently), `multiple` (default — sends multiple queries at once to the database), `transaction` (executes all queries in a single transaction) | | — Query Parameters | `string` | No | — | Comma-separated list of properties which should be used as query parameters. The values from these input item properties are mapped to $1, $2, etc. in the query. _(shown when Operation is `executeQuery`)_ | | Max Concurrency | `number` | No | `10` | Maximum number of items to process concurrently. | ## Output Data **Every returned row becomes its own output item**, so one input item can produce many output items — a query that matches 500 rows emits 500 items. The row's columns *are* the item JSON: the input item's own JSON is replaced rather than merged. Binary data on the input item is forwarded to every item produced from it. When a statement returns no rows at all, the node emits a single item that keeps the input JSON and adds a marker: `_queryResult: []` for Execute Query, or `success: true` for Insert and Update. | Operation | Output item JSON | |-----------|------------------| | `executeQuery` | One item per returned row. Statements that return no rows (INSERT/UPDATE/DELETE without RETURNING, DDL) produce one `{ "success": true, "rowCount": n }` item. A SELECT that matches nothing produces `{ …input, "success": true }`. | | `insert` | One item per inserted row, holding the columns named by Return Fields (all columns by default). | | `update` | One item per updated row, holding the columns named by Return Fields. An update that matches no row produces `{ …input, "success": true }`. | Reference the values downstream by column name, e.g. `{{ $json.device_id }}`. ## Usage Examples - Execute a raw SQL query against TimescaleDB with parameters - Insert time-series sensor data into a hypertable - Update device status in TimescaleDB matched by device_id - Query time-bucketed aggregations from TimescaleDB - Insert IoT telemetry data with type casting ## Example Configuration Run a parameterized query, taking the two placeholder values from properties of the incoming item: ```json { "type": "timescaledb", "parameters": { "operation": "executeQuery", "query": "SELECT * FROM sensor_data WHERE timestamp > $1 AND device_id = $2", "maxConcurrency": 5, "additionalFields": { "queryParams": "startTime,deviceId" } } } ``` Insert a sensor reading, casting each column to its stored type: ```json { "type": "timescaledb", "parameters": { "operation": "insert", "schema": "public", "table": "sensor_readings", "columns": "device_id:int,temperature:float,humidity:float,timestamp:timestamp", "returnFields": "id,device_id,timestamp", "maxConcurrency": 10 } } ``` Update a device's status row, matched on a cast key column: ```json { "type": "timescaledb", "parameters": { "operation": "update", "schema": "public", "table": "device_status", "updateKey": "device_id:int", "columns": "status:text,last_seen:timestamp,battery_level:int", "returnFields": "*", "maxConcurrency": 8 } } ``` Write time-series points into a hypertable, including a JSONB tag column: ```json { "type": "timescaledb", "parameters": { "operation": "insert", "schema": "public", "table": "metrics", "columns": "timestamp:timestamp,metric_name:text,value:float,tags:jsonb", "returnFields": "timestamp,metric_name", "maxConcurrency": 15 } } ``` Aggregate with `time_bucket` over a bounded window: ```json { "type": "timescaledb", "parameters": { "operation": "executeQuery", "query": "SELECT time_bucket('1 hour', timestamp) AS hour, AVG(value) AS avg_value FROM metrics WHERE timestamp BETWEEN $1 AND $2 AND metric_name = $3 GROUP BY hour ORDER BY hour", "additionalFields": { "queryParams": "startTime,endTime,metricName" } } } ``` Update rows keyed on a composite key: ```json { "type": "timescaledb", "parameters": { "operation": "update", "schema": "analytics", "table": "user_metrics", "updateKey": "user_id:int,date:date", "columns": "page_views:int,session_duration:int,updated_at:timestamp", "returnFields": "user_id,date,updated_at", "maxConcurrency": 12 } } ``` ### 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 a TimescaleDB time-series database using the PostgreSQL wire protocol. Use for time-series data storage and retrieval. ### Values and identifiers - **Values are bound, not pasted.** The data written by Insert and Update, and the `$1`, `$2` … placeholders of Execute Query, are handed to the database as query parameters, so quotes and semicolons inside your data cannot change the statement. - **Identifiers are written into the statement.** Schema, Table, Columns, Update Key and Return Fields name database objects, so their text becomes part of the SQL (double-quoted and escaped as identifiers), and the `:cast` suffix becomes a type cast in the statement. Keep those fields pinned to values you control rather than filling them from data that arrives with the item. - **Execute Query runs exactly what you write.** Only the placeholders are bound. Build the statement yourself and route every incoming value through Query Parameters instead of concatenating it into the text. ### Mapping items to columns - **Columns names item properties and table columns at once.** Each entry is read from the input item under that name and written to the column of the same name. A property that is not on the item is written as `NULL`, so a missing field is a silent null rather than an error. - **Casts use colon syntax.** `device_id:int` sends the value with an explicit `::int` cast — useful when the item carries numbers or timestamps as strings. Leave the suffix off to let the database infer the type. - **Update Key locates the row; Columns supplies the new values.** A key column listed in both places is used for matching, not for the update, so an Update whose Columns list contains only key columns has nothing to write. - **Query Parameters names properties, not literals.** Each entry is a property name on the input item; its value fills the matching placeholder in order.