MySQL

In this article you'll learn:

Like most nodes, the MySQL 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 MySQL

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

In the MySQL 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.

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.

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

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 MySQL database needs to be accessible from the internet for FactBranch to connect to it. The default port for MySQL is 3306.

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.

Output format

The MySQL 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.