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.
• Instance Name Template: A string template resolving to the instance name of the MSSQL server you wish to connect to. This field is optional and is only displayed when choosing an MSSQL service credential (or entering a template for the credential name).

Note: Choosing a service credential 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 MSSQL, you may optionally also provide an Instance Name Template, which is a string template resolving to the instance name of the MSSQL server you wish to connect to. (For edge workflows, this setting is only available in GEA versions 1.58.0 and higher.)

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"
}

Generate SQL Suggestions

To assist workflow developers, WEGnology exposes an AI-powered service that accepts a prompt and optionally an example payload, and returns a SQL query matching the description provided in the prompt. The model backing the SQL generator handles a variety of general-purpose use cases, making the service especially useful for building queries with complex joins, aggregate functions, date filtering, and more.

Request AI SQL Generation

To generate a SQL query, click the "Request AI SQL Generation" button above the SQL Node's query editor. Doing so will display a modal where you may enter a prompt and choose a payload to serve as context for the prompt.

Using the Suggested SQL

Once ready, click the "Generate SQL Suggestion" button at the bottom of the modal to return a SQL query matching the provided prompt and sample payload context. Depending on your inputs, the result may include some instructions along with the generated query.

Beneath the output are two buttons:

• Replace Config with Suggestion: Pressing this will overwrite any current query in the SQL Node with the returned suggestion and will close the modal.
• Append Suggestion to Current Config: Pressing this will add the suggestion beneath any query that is currently in the SQL Node and will close the modal.

If you are not satisfied with the result, click "Change Prompt" to try again with a different prompt and/or example context.

Limitations

• The service does not maintain the context of previous requests; therefore, if you would like to modify a prompt to receive a query that is more suitable to your use case, you should provide the prompt in full with modifications.
• As with any AI-powered generative service, output should be tested before put into production use to ensure that the provided prompt is satisfied by the query output. In particular, verify that the generated SQL is compatible with your chosen DBMS, as SQL dialects vary between database engines.
• The service has a maximum output per request. If that limit is reached with a given prompt, you will be alerted that the full output could not be displayed. You may either use the output that was retrieved, or you may modify your prompt to receive a more succinct SQL suggestion.
• The SQL generation service can only be used to generate SQL queries to be used in WEGnology's SQL Node. The agent generating the query is instructed not to provide code in other languages, or to generate content about any other subject.

Related Nodes

• MongoDB Node
• Redis Node