Prerequisites
In this article you'll learn:
Like most nodes, the Microsoft SQL Server 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.
If you haven't done so, create a SQL Server authentication in FactBranch.
In the SQL Server node, select the "Authentication" tab and select the authentication you've just created.
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.
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
SQL Server also supports advanced features like JSON queries (SQL Server 2016+):
SELECT id, name, JSON_VALUE(metadata, '$.plan') as plan_type
FROM users
WHERE JSON_VALUE(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.
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.
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.
Your SQL Server database needs to be accessible from the internet for FactBranch to connect to it. The default port for SQL Server is 1433.
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.
FactBranch connects to SQL Server databases using SSL by default for security. Make sure your SQL Server instance is configured to accept encrypted connections.
The SQL Server 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.
FactBranch works seamlessly with Azure SQL Database and Azure SQL Managed Instance. Simply use your Azure SQL endpoint as the host address and ensure your firewall rules allow connections from FactBranch's static IP address.
For Azure SQL Database, you can find the server name in the Azure portal under
your database's connection strings. It typically looks like:
your-server.database.windows.net