External Database Connector

Last modified: November 19, 2024

Introduction

Connect to Microsoft SQL, MySQL, PostgreSQL, Oracle, and Snowflake databases with the External Database Connector. The External Database Connector is supported for Studio Pro 10.6 and above.

This document provides instructions on how to configure and use the External Database Connector. For information on the database wizard and how to connect using the External Database Connection document, see External Database Connection. For information on how to create and validate SQL queries, see Use the External Database Connector.

Typical Use Cases

Use this module if you need to connect to databases and select data to use in your app. This connector allows you to directly test connections and queries during configuration in Studio Pro (design time).

If you need to connect to other database types, check out the Database Connector. Keep in mind that design time support is not available for the older version of the connector.

Features

This connector supports connections to the following database types:

If you are looking for another database type, follow the prompt to request support your database when you open the database connection wizard.

This connector supports the following statements:

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • Stored Procedure

Limitations

  • SELECT queries and Stored Procedure can be saved only if they are successfully executed and a response structure is created
  • The connector supports columns and stored procedure parameters with primitive data types only
  • If column names contain special characters, use an alias for the column name
  • Parameters are only supported for filter values (prepared statements)
  • Certificate-based authentication for PostgreSQL is not supported on macOS

Prerequisites

  • Studio Pro 10.6 or above
  • External database connection details, including the following:
    • Login credentials
    • Database type
    • Hostname, port, and database name; or, instead, the JDBC connection string

Installation

Download the External Database Connector and add it to your app.

Configuration in Design Time

With this connector, you can test database connections and add queries and parameters during design time before your app is running. This allows you to make sure everything works before deploying your app.

Getting Started: Connecting to a Database

After installing the connector, get started by doing the following:

  1. Right-click the module you would like to add the connection to and click Add other > External database connection. This opens the Database Connection wizard:

  2. Select the database to which you would like to connect and enter the required information.

  3. Click Test Connection to see if the connection works. If you do not see a green Connection Successful text confirmation, try checking your database details again.

  4. Click Save to open the external database document for this database.

Now you can start querying the database to select data to use in your app.

Querying a Database

To query the database, do the following:

  1. Enter a query Name so you can access the same query later.

  2. Enter your SQL Query to select data from your database for use in your app. For example, the query SELECT * from customers selects all rows in the Customers table:

  3. Click Run Query to move to the Response data tab and view the queried data.

Adding Parameters

Click Add Parameter to add parameters to your SQL queries to pass dynamic values to the query at runtime.

The example database in Querying a Database is a table of customer details with information such as customer name, address, and phone number. Let’s say you want to specify a specific customer while your app is running. You can add the following parameter:

Then, use the parameter in the query:

select * from customers where contactFirstName like {paramFirstName}

Using Query Response

After querying the database, you can view the response in the Response screen.

Click Use Response if you want to create an entity from the response.

Creating an Entity from the Response

In the Response Structure tab, there is a preview of the queried data in an entity. You can adjust the entity name, though one is suggested for you:

Click Save Query & Create Entity to create the entity and add it to your domain model:

Using the Entity in a Microflow

Use the Query External Database activity to call the database in a microflow. Do the following:

  1. Create a new microflow and drag the Query external database activity into it.

  2. Double-click the activity and in the Database field, click Select to choose the database you want to query.

  3. Select the Query you want to include in the activity (that you saved while querying the database).

  4. Include any parameters.

  5. In the Output field, choose if you want to Use return value.

  6. Click OK.

  7. Configure the end event (such as displaying a list, if you are selecting data to appear in a list).

You can now use the microflow in your app. Below is an example of a configured microflow:

See the Integration Activities section of the Studio Pro Guide for further explanation of the properties in this activity. See the Call Stored Procedure section of Use the External Database Connector for more information on how to call a stored procedure.

Use Certificate-Based Authentication for PostgreSQL Connections

Prerequisites

The certificates below are required for server configuration and the SSL mode selected.

  • Authority certificate (CA certificate), which is used to sign the server and client certificate. The CA file should have only one certificate.
  • A PKCS12 certificate file that contains a private key. These files typically have the .pfx or .p12 file extension and a password to open the file.

Running Locally

You can configure custom settings that are only used when you run your app locally. To do this, follow these steps:

  1. Add the authority certificate (CA) to the Certificates tab in the App Settings. See the Certificates Tab section of App Settings for information about adding certificates.

  2. If the PostgreSQL server requires Mendix to authenticate using a client certificate, add the client certificate details to the App Settings by clicking Configuration > Edit > Custom. See the Running Locally section of Use a Client Certificate for further instructions of how to add the certificate details.

  3. Add the connection details to the Database Connection wizard. Fill in the following details:

    • Set SSL encryption to Yes
    • Set SSL mode as per your requirement
    • Add the Client certificate identifier; this must match the value provided in the custom settings dialog

  4. Click Test Connection.

  5. Run your application to test the connection for local runtime.

Running in the Cloud

To connect to PostgreSQL when the application is running in Mendix Cloud, follow these steps:

  1. To configure SSL-based authentication in Mendix Cloud, add a CA certificate and client certificate for server configuration and the selected SSL mode. For more details, see the Running in the Cloud section of Use a Client Certificate.
  2. After the client certificate has been added, double-click the client certificate and add the value ClientCertificateIdentifier to Use Client Certificate for specific services. This must match the value provided for the constant ClientCertificateIdentifier.
  3. Add the required values to the constants created for DBSource, DBUsername, DBPassword, and ClientCertificateIdentifier.