Skip to main content

TimescaleDB

TimescaleDB is an open-source time-series SQL database optimized for fast ingest and complex queries.

!!! note "Credentials" You can find authentication information for this node here.

Basic Operations​

  • Execute an SQL query
  • Insert rows in database
  • Update rows in database

Example Usage​

1. Cron node​

The Cron node will trigger the workflow every minute.

  1. Click on Add Cron Time.
  2. Select 'Every Minute' from the Mode dropdown list.
  3. Click on Execute Node to run the node.

2. HTTP Request node (GET)​

This node will make a GET request to the API https://api.wheretheiss.at/v1/satellites/25544/positions to fetch the position of the ISS. This information gets passed on to the next node in the workflow.

  1. Enter https://api.wheretheiss.at/v1/satellites/25544/positions in the URL field.
  2. Click on the Add Parameter button in the Query Parameters section.
  3. Enter timestamps in the Name field.
  4. Click on the gears icon next to the Value field and click on Add Expression.
  5. Enter the following expression: {{Date.now()}}. This expression will return the current timestamp.
  6. Click on Execute Node to run the node.

3. Set node​

We will use the Set node to ensure that only the data that we set in this node gets passed on to the next nodes in the workflow.

  1. Click on Add Value and select 'String' from the dropdown list.
  2. Enter latitude in the Name field.
  3. Click on the gears icon next to the Value field and click on Add Expression.
  4. Select the following in the Variable Selector section: Current Node > Input Data > JSON > 0 > latitude. You can also add the following expression: {{$json["0"]["latitude"]}}.
  5. Click on Add Value and select 'String' from the dropdown list.
  6. Enter longitude in the Name field.
  7. Click on the gears icon next to the Value field and click on Add Expression.
  8. Select the following in the Variable Selector section: Current Node > Input Data > JSON > 0 > longitude. You can also add the following expression: {{$json["0"]["longitude"]}}.
  9. Click on Add Value and select 'String' from the dropdown list.
  10. Enter timestamp in the Name field.
  11. Click on the gears icon next to the Value field and click on Add Expression.
  12. Select the following in the Variable Selector section: Current Node > Input Data > JSON > 0 > timpestamp. You can also add the following expression: {{$json["0"]["timestamp"]}}.
  13. Toggle Keep Only Set to true. We set this option to true to ensure that only the data that we have set in this node get passed on to the next nodes in the workflow.
  14. Click on Execute Node to run the node.

4. TimescaleDB node (Insert)​

We will insert the data from the previous node to a table named iss. To create the table, use the following SQL command.

CREATE TABLE iss(latitude NUMERIC, longitude NUMERIC, timestamp NUMERIC);
  1. First of all, you'll have to enter credentials for the TimescaleDB node. You can find out how to do that here.
  2. Enter iss in the Table field.
  3. Enter latitude, longitude, timestamp in the Columns field.
  4. Click on Execute Node to run the node.

!!! note "Activate workflow for production" This example workflow uses the Cron node, which is a Trigger node. You'll need to save the workflow and then click on the Activate toggle on the top right of the screen to activate the workflow. Your workflow will then be triggered as specified by the settings in the Cron node.

FAQs​

How to specify the data type of a column?​

To specify the data type of a column, append the column name with :type, where type is the data type of that column. For example, if you want to specify the type int for the column id and type text for the column name, you can use the following snippet in the Columns field: id:init,name:text.