PostgreSQL

The PostgreSQL component integrates the PostgreSQL object-relational database management system (as well as AWS Redshift) with Squiz Connect.

Completeness matrix

The completeness matrix provides details about the level of features currently available in the PostgreSQL component.

Authentication

There are two options for authentication:

Completing 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
```
Learn more about connection strings like these in the relevant PostgreSQL documentation.

If you complete both the Connection String and all the other connection data fields, Connect will use the connection string to connect to the database.

Selecting the Allow self-signed certificates check box adds the following to connection options:

{
  ...
  ssl: {
    rejectUnauthorized: false
  }
  ...
}

This option might be useful when using self-signed SSL certificates,like Heroku.

Triggers

The PostgreSQL component supports the following trigger:

SELECT trigger
- executes an SQL query that returns multiple results,
- has no limitations on the query, and
- is best suited for SELECT-type queries.

Actions

The PostgreSQL component supports the following actions:

SELECT action
Functions the same way as the SELECT trigger, although as an action of this component (i.e. a 2nd or subsequent component in a flow).
INSERT/UPDATE/DELETE action
Executes SQL queries that INSERT, UPDATE or DELETE data (which in turn typically return no data values). If any data values are returned, these are ignored. The number of rows affected by this action can be seen in the log file. After such a query is executed, the original message is pushed to the next component.
INSERT Bulk action
Executes the bulk INSERT SQL query in one transaction and returns the execution result. The incoming message for this action must contain a body with an array of objects.
SQL Query action (Expert mode)
Executes an SQL query or SQL script without prepared statements, and returns an array of execution results for each query.
SQL Injection action (Expert mode)
Executes an SQL query or SQL script with prepared statements, and returns an array of execution results for each query.

A JSONata expression can be used as the source of such an SQL query.

How SQL templates work

Since SQL is an extensive and complex language, SQL templating has been designed to express SQL in a manner that achieves maximum flexibility with minimal effort.

The implementation of SQL templating is based on a prepared statement, that helps prevent SQL injection attacks.

Additionally, expression interpolation of JavaScript template literals (which internally uses the Node SQL template strings library) allows property traversal and string manipulation in templates.

The following incoming message demonstrates how templating works. To insert the following JSON data into your PostgreSQL database:

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

Use the following template:

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

In this example, type conversion happens automatically, which allows values to be traversed and concatenated.

The following is a SELECT example, which uses concatenation and traversal in action:

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

Known limitations

The standard type default JavaScript to PostgreSQL data-type coercion utilises the node-postgresql client.

Help Center

Menu