SQL Node

The SQL Node allows a workflow to query or update values in a SQL database and optionally store the result on the workflow payload.

Node Properties

The configuration of the SQL Node is broken down into four major sections.

SQL Connection

Connection configuration can be provided one of two ways …

Choose a Service Credential

This is the recommended method for providing SQL connection information for both security and usability purposes. When selected, you must provide the following …

• Credential Name Template: A string template that resolves to the name of one of your application’s SQL service credentials.
• Database Name Template: A string template resolving to the name of the database you wish to query. The user defined in the service credential must have access to the database provided here.

Note: This option is not available in Edge Workflows.

Enter Connection Configuration

When choosing to enter the database connection info inline, you must first choose the Database Management System (DBMS). Options are:

• MSSQL
• MySQL
• PostgreSQL
• SQLite 3 (only available in Edge Workflows)

When choosing any DBMS except SQLite 3, you must provide the following:

• SQL Address Template: A string template resolving to a hostname or IP address where the service is running.
• Port Number Template: The port number where the service is exposed on the host. This field is optional and, if not provided, will default to the default port of the chosen DBMS.
• Username Template: The username through which to connect to the service.
• Password Template: The password associated with the username. While this field is optional, the password is almost always required by the service.
• Database Name: The name of the database you wish to query.

When choosing SQLite 3, you must provide the following:

• SQLite File Template: A string template resolving to either a SQLite DB disk file path (e.g. /path/to/database.sqlite, /data.db, etc.) or an in-memory SQLite DB file (e.g. :memory:).

SQL Connection Encryption

When entering connection configuration and not selecting SQLite 3 as the DBMS, you may choose to establish a secure connection to the SQL service. (When choosing a service credential, this option is included in the credential configuration.) If the Secure Connection checkbox is checked, you may optionally provide SSL/Encryption Configuration either as a payload path or as a JSON template.

Depending on your chosen DBMS, the following considerations should be made when establishing a secure connection …

• For MSSQL, the option encrypt=true is automatically set in the client connection. (Note: This is required when connecting to Azure SQL Database.)
• For MySQL and PostgreSQL, ssl=true is automatically set only if SSL/Encryption Configuration is not provided. Check your SQL server configuration for the appropriate settings.

SQL Query

Next, provide a query to execute against the database. The query may utilize string templates like so:

SELECT * FROM Sales WHERE user_id='{{data.params.userId}}';

You may also pull the entire query string from a value on your payload:

{{globals.insertRequestQuery}}

Response

Finally, you may optionally provide a payload path for where to store result of the SQL query. For queries like INSERT or DELETE, you might not care about the result, but for an query like the SELECT above, you almost certainly do!

The value placed at the path will be an array whose values depend on the DBMS and query. For example, a MySQL SELECT query’s result would return an array of records matching the query with keys for each value returned:

[
  {
    "sale_id": "a001",
    "userId": "a1b2c3d4e5f6a1b2c3d4e5f6",
    "customer": "DT",
    "amount": 42.00,
    "date": "01/02/2019"
  },
  {
    "sale_id": "a002",
    "userId": "a1b2c3d4e5f6a1b2c3d4e5f6",
    "customer": "Vogons",
    "amount": 12.34,
    "date": "01/03/2019"
  }
]

If an error occurred, the value placed at the path will be an object describing the error:

{
  "errors": "Connection refused"
}

Related Nodes

• MongoDB Node
• Redis Node