Covered in this article
Postgresql-component pages
Created on Updated on
PostgreSQL Component

PostgreSQL Component

PostgreSQL is a general purpose and open source object-relational database management system.

Latest changelog

1.3.4 (May 22, 2020)

  • Update sailor version to 2.6.7

To see the full changelog please use the following link.

Description

This is an open source component for working with PostgreSQL object-relational database management system on elastic.io platform.The component also works well with AWS Redshift.

Overview

With this component you will have the following trigger:

  • SELECT - this trigger will execute an SQL query that returns multiple results, it has no limitations on the query but apparently best suited for SELECT type of queries

Following actions are also inside:

  • SELECT - same as above but as an action
  • INSERT/UPDATE/DELETE - this action executes the SQL query that returns no data, for example insert, delete or update. After query is executed original message will be pushed to the next component.
  • INSERT Bulk - this action executes the bulk INSERT SQL query and returns execution result.
  • SQL Injection - Expert mode. This action executes the SQL query or SQL script without prepared statements and returns an array of results of execution each query.
  • SQL Query - Expert mode. This action executes the SQL query or SQL script with prepared statements and returns an array of results of execution each query. JSONata expression can be used as a source of SQL query.

Environment Variables

LOG_LEVEL - trace debug info warning error controls logger level

The component completeness matrix is also available separately.

Authentication

There are two options for authentication:

  1. Filling out the Connection URI (host), Connection port, Database Name, User, and Password fields. This is the recommended method.
  2. Adding a full PostgreSQL connection URL in the Connection String field to connect to your database, as follows:
postgresql://user:password@your.postgresql.host:5432/dbname

Note: if you fill out both the Connection String and all the other connection data fields, the platform will use the connection string to connect to the database.

See more in documentation.

Triggers

PostgreSQL component includes the following triggers:

  1. SELECT trigger
    This trigger and action are actually the same but can be used in two different scenarios - trigger as a first step and action in between other steps.

Actions

  1. SELECT action
    This trigger and action are actually the same but can be used in two different scenarios - trigger as a first step and action in between other steps.

  2. INSERT/UPDATE/DELETE action
    Use this action to insert, update or delete some data, returned value is ignored, number of affected rows you can see in the log file.

  3. INSERT Bulk action
    This action is useful to execute a bulk insert query in one transaction. An incoming message needs to contain a body with an array of objects.

  4. SQL Query action
    Expert mode. You can execute SQL query or SQL script in this action.

  5. SQL Injection action
    Expert mode. You can execute SQL Injection in this action. You can not use prepare statement there, for this purpose use SQL Query Action.

How SQL templates work

SQL language is pretty extensive and complicated, so we tried to design the templating as minimum invasive so that you could express your self in SQL with maximum flexibility. Implementation of the templating is based on prepared statement and hence should be safe to many SQL injection attacks. Second technology that is used here is JavaScript template literals (we are using this library internally) so you can even property traversal and string manipulation in the templates. Let us demonstrate how the templating works on a sample. Let’s take an incoming message like this:

{
  "body": {
    "name": "Homer Simpson",
    "age": 38,
    "address": {
      "street": "742 Evergreen Terrace",
      "city": "Springfield"
    }
  }
}

If we would like to insert it into the database, we would use following template:

INSERT INTO customers (name, age, address) VALUES (${name},${age},${address.street + address.city})

So as you can see in the example above type conversion will happen automatically and you can traverse and concatenate values.

Now the SELECT example:

SELECT * FROM customers WHERE address LIKE ${'%' + address.city + '%'}

Same as above, concatenation and traversal in action.

Known limitations

There are several limitations of the component:

We are relying on standard type default js<->postgresql data-type coercion see here

If in doubt call support.