PostgreSQL

In this article you'll learn:

Like most nodes, the PostgreSQL node is divided into two areas. The main area on the left is for writing your query and configuring the node and the Preview box on the right lets you test the node with test input data and shows you what the node returns.

TODO: SCREENSHOT

First, let's look at the config area on the left.

Authenticating at PostgreSQL

If you haven't done so, create a PostgreSQL authentication in FactBranch.

In the PostgreSQL node, select the "Authentication" tab and select the authentication you've just created.

Writing an SQL query in FactBranch

Write your SQL query in the main area on the left where you can also use placeholders in the query. See the section using placeholders below for more info.

Example query

Here's a simple example that looks up customer information by email:

SELECT id, first_name, last_name, email, created_at 
FROM customers 
WHERE email = $data.email

PostgreSQL also supports more advanced features like JSON queries:

SELECT id, name, metadata->>'plan' as plan_type
FROM users 
WHERE metadata->>'status' = $data.status

In order for placeholders to be substituted with real data, we first need to define some test data. So let's look at the Preview box on the right.

Test input data

Most nodes use dynamic data somewhere (more on using placeholders for dynamic data in the next section below) and so it's essential to try out your node with input data.

The Input data is displayed as individual editable fields in the Preview box. When you have a previous node in your flow, the output data from that node automatically appears as the test input data for the current node. You can edit each field value individually to test different scenarios.

If this is the first node in your flow (connected directly to a trigger), the test input data will be populated with the data captured from your trigger.

Using placeholders

To dynamically insert data in your SQL query, you can use placeholders. They begin with a dollar-sign ($) just like variables in many programming languages.

The input data to this node are stored in the $data variable. Let's say your node receives the following data:

{
  "email": "john@example.com",
  "user_id": 12345
}

You can then use these values in your SQL query like this:

SELECT * FROM customers WHERE email = $data.email

Or:

SELECT * FROM orders WHERE customer_id = $data.user_id

FactBranch automatically handles SQL escaping to prevent SQL injection attacks, so you can safely use placeholders with user input.

Database connection requirements

Your PostgreSQL database needs to be accessible from the internet for FactBranch to connect to it. The default port for PostgreSQL is 5432.

If your database is behind a firewall, you'll need to allow connections from FactBranch's static IP address. You can find this IP address in the authentication configuration screen.

We recommend creating a dedicated database user for FactBranch with only SELECT permissions on the tables you'll be querying. This follows the principle of least privilege and keeps your data secure.

SSL connections

FactBranch connects to PostgreSQL databases using SSL by default for security. Most modern PostgreSQL installations support SSL connections out of the box.

Output format

The PostgreSQL node returns data in a structured format where each row is an object with column names as keys:

[
  {
    "id": 1,
    "first_name": "John",
    "last_name": "Doe",
    "email": "john@example.com",
    "created_at": "2023-01-15T10:30:00Z"
  },
  {
    "id": 2,
    "first_name": "Jane",
    "last_name": "Smith",
    "email": "jane@example.com",
    "created_at": "2023-01-16T14:22:00Z"
  }
]

This format makes it easy to access individual fields in subsequent nodes or display templates using placeholders like $data[0].first_name for the first row's first name.

Working with Amazon RDS and Aurora

FactBranch works seamlessly with Amazon RDS PostgreSQL and Amazon Aurora PostgreSQL. Simply use your RDS endpoint as the host address and ensure your security groups allow connections from FactBranch's static IP address.