Note: Nintex Apps data centers are located in West US and Australia (AUS). In-region processing of Nintex Apps data is only available in these regions.

SQL

Structured Query Language (SQL) is a language that facilitates relational database interactions and queries. Working with a SQL database typically takes the form of entering in statements—made up of clauses, expressions, queries, and predicates—to accomplish a task.

Some statements create or update the database itself, as well as its general structure:

CREATE DATABASE anewdatabase;
USE anewdatabase;
CREATE TABLE newtable (
  column1 varchar(7),
  column2 varchar(7),
  column3 varchar(3)
);

And other statements query and manipulate that data:

SELECT * FROM newtable;
INSERT INTO table (column1, column2, column3)
VALUES ("bibbidi","bobbidi","boo")

Because of its flexibility and relative ease-of-use, SQL has been widely adopted by some of the largest database services in the industry.

Supported SQL dialects

Much like any other language, SQL's ubiquity has led to distinct branches off the language, each with their own unique features and expectations. These are known as SQL dialects.

Each of these dialects has their own idiosyncrasies and unique syntax requirements, so Nintex Apps contains separate connectors to connect to unique SQL instances.

All of that is to say: using a Nintex Apps connector to connect to a SQL database resolves the need to code individual SQL statements for a particular SQL dialect.

Nintex Apps currently supports:

• Oracle DB

• Microsoft SQL Server

• MySQL

• PostgreSQL

• Amazon Redshift

  Important: 

  Amazon Redshift is considered a data warehouse in contrast to a database. As such, it should be considered read-only.

  While technically possible to use DML operations when connecting to an Amazon Redshift instance, it is strongly discouraged due to Redshift's pricing structure.

  For more information, see the following resources:

  • The Data Transfer section of AWS Redshift pricing docs
  • Updating tables with DML commands from AWS Redshift docs

Prerequisites

Setup the Database

While Nintex Apps makes interacting and creating new data within a PostgreSQL database much simpler, you must create your database schema (tables, columns, and general structure) before creating the SQL connection in Nintex Apps. Nintex Apps creates connections as references to these preexisting database elements, but they must already exist.

Understand the Database

Since SQL databases can be stored in various cloud services, or on-premise, there are two important points to note when creating a SQL connection:

• The port number, which must be appended to the database URL / host field. Different SQL dialects may require different port numbers.
• The use and configuration of Secure Sockets Layer —better known as SSL—to connect to the database. Some cloud services require that this option be enabled.

Understand how these concepts relate to your database before proceeding.

Allowlist IPs

Some databases, whether in the cloud or on-premise, use a firewall to block IPs as a security measure. However, Nintex Apps may try to access the database from a few different IPs, depending on a Nintex Apps site's region. After entering the required information for the Nintex Apps connection, Nintex Apps displays a list of IPs your Nintex Apps site may use.

If the database host employs a firewall, allowlist all of these IPs to ensure Nintex Apps can properly connect. ( Contact your network administrator if necessary. )

( Nintex Apps for Salesforce ) Add Nintex Apps's SQL Service to CSP trusted sites

Nintex Apps must query its own SQL service to properly communicate with SQL connections. This means, in order to utilize SQL connections in Nintex Apps for Salesforce, users must add the URL for that service ( https://gateway-us.skuid.net ) to their trusted sites.

In Salesforce

1. Navigate to Salesforce Setup.
2. Enter CSP in the Setup pane's Quick Find bar.
3. Click CSP Trusted Sites.
4. Click New Trusted Site.
5. Enter the following properties for the trusted site definition:
   • Trusted Site Name: Enter a recognizable name, like NintexAppsSQLService
   • Trusted Site URL: https://gateway-us.skuid.net
6. Click Save.

( Nintex Apps for Salesforce ) Create and add a JWT signing certificate

Because Nintex Apps must communicate with its own data services to properly parse SQL data, you must make a JWT signing certificate within Salesforce and then update Nintex Apps's certificate in the Data Services tab.

1. Navigate to Configure > Connections > Data Services.
2. Click Modify JWT Signing Certificate.
3. Follow the instructions listed in the popup to create a certificate and paste its contents in the field.
4. Click Save.

Hosted Databases

Hosted SQL databases, such as Amazon Web Services RDS or Microsoft Azure, may require SSL to connect. Consult the database's documentation to determine if and how the connection must be configured for proper usage. Azure, for example, requires SSL.

However, even if the database host doesn't require SSL, Nintex Apps strongly recommends using SSL to ensure the most secure connection.

Note:  Using SSL may require configuration within the hosting provider.

Create a SQL Connection

Select the SQL dialect for your database within the New Connection form.

Note:  Once created, SQL connections cannot be renamed.

Then, enter these base-level connection properties:

• Database Host / IP Address: The hostname or IP address of the system hosting the database to be accessed, e.g. ea2-54-345-12-137.server-1.sqlhost.com or 71.122.33.423 .
• Database Port: The port on which the database is accessible. Common default port values include 5423 for PostgresSQL instances or 3306 for MySQL instances, but database ports may vary depending on configuration.
• Database Name: The name of the individual database to be accessed. This is necessary, since several unique databases can be stored on the same host.
• Database Username: The username used to login and access the database.
• Database Password: The password used to login and access the database.

The names of these properties may not match your database's terminology exactly, but they will have always have corresponding details.

After creating the Nintex Apps connection for your database, verify the connection's details by clicking fa-plug Test Connection.

Database SSL Configuration

If you use SSL to connect to your cloud database service, some additional configuration is required after creating the connection. (You may need to consult the database's documentation and work with your database administrator to properly set these fields.)

• Use SSL to Connect: Determines whether or not to use SSL when connecting to the database. Using SSL provides an increased level of security to the data connection.

  Note:  This property must be enabled if hosting Microsoft SQL via Azure.

  • Server CA: The URL for the certificate authority (CA) that issues certificates for SSL connection.
  • Client Key and Client Certificate: A set of credentials—two certificate-signed blocks—used for SSL connections, authentication, and encryption.

Connection Configuration

Connections are Nintex Apps's way of translating the various tables and columns of a database into the declarative objects that Nintex Apps models can work with. Without connections, Nintex Apps models are totally unable to access the tables and records within the SQL database; essentially, Connections are the foundation for SQL connection use.

After creating the SQL connection, you must always import or manually create connections for the SQL tables you wish to access.

The fastest way to get started is to click fa-magic Import Connections.

For more information, see the connections topic.

Using a SQL Connection

After the prerequisite configuration (connection creation and connection management), you can use the SQL connection within a Nintex Apps page by creating a Nintex Apps model with properties pointing to the other elements configured thus far.

Model properties

In addition to several standard model properties, SQL models have some unique characteristics worth noting:

• Basic
  • Connector: Select theconnector for the SQL dialect you've connected to.
  • Connection: Select the connection you created.
  • Entity Name: Select the connection pointing to the table you wish to access.
  • Model Behavior: Determines whether a model will function as a basic model (the default Nintex Apps behavior) or as an aggregate model, which summarizes multiple rows of data into aggregations.
• Advanced
  • Request data in the same transfer as other Models: Determines whether or not this model's request will be batched with any other requests to the same connection on page load. Any models on the same connection with this property checked will have their requests batched and returned at the same time after the connection's server has resolved the request and returned all requested data. If unchecked, this model will be queried and returned first. It can be useful to disable this property if a model's data is not dependent on another object from the same connection.

Configure fields, conditions, and model actions as you would with most other models.

Note:  Connection conditions will supersede page-level conditions.

Troubleshooting

As SQL connections totally rely on connection configuration, the best place to start troubleshooting SQL errors is verifying all connections are accurate and up-to-date.

My page doesn't load data and displays this error: JWT Authorization error. Error: could not retrieve public key from microservice

This error indicates Nintex Apps may have issues with the JWT signing certificate used to facilitate communication between Salesforce and Nintex Apps's data services. Ensure your JWT signing certificate settings are properly configured.

My page seems to break with this error: Timeout exceeded while awaiting headers.

You may have manually added either a connection or a connection field that does not exist within the database. Verify that all connections and all of their fields exist with the database.

My page does not load at runtime and I receive this error: connection Provider returned zero Connections.

This error can occur on page load if there is a Nintex Apps model connected to a connection that no longer exists. Verify that all models are pointed to the correct connections and remove any models without corresponding connections.

I'm using a Microsoft SQL database hosted through Azure and getting errors with little or no information about the error.

After creating a Microsoft SQL database, set the SSL configuration to Use SSL to Connect. If this is not checked, the database will be unable to connect.