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 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:

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

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

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

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

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

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