PostgreSQL

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

PostgreSQL Technical Notes

PostgreSQL Usage Example

PostgreSQL actions

PostgreSQL triggers

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.

Technical Notes

The technical notes page gives some technical details about PostgreSQL component like changelog and completeness matrix.

Environment Variables

Please Note: From the platform version 20.51 we deprecated the component LOG_LEVEL environment variable. Now you can control logging level per each step of the flow.

The component completeness matrix is also available separately.

Authentication

There are two options for authentication:

Filling out the Connection URI (host), Connection port, Database Name, User, and Password fields. This is the recommended method.
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.

The option Allow self-signed certificates add to connection options following:

ssl: {
    rejectUnauthorized: false
  }

It could be useful for instances that are is using self-signed SSL certificates (like Heroku)

See more in documentation.

Triggers

PostgreSQL component includes the following triggers:

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

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.
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.
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.
SQL Query action Expert mode. You can execute SQL query or SQL script in this action.
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.