Skip to main content
Skip table of contents

Database

Choosing the type Database will change the form as follows:

Datasource Configuration

Database Driver

Select the kind of database you want to connect to

  • MySQL

  • PostgreSQL

  • MSSQL

  • Oracle

  • IBM Db2

  • Snowflake SQL

Most drivers have the same fields that needs to be set.

  • Server host & port

  • Username & password

  • Database (the name of the database on your host machine)

  • SQL query

  • SSL Mode (MySQL only)

    • Server CA (MySQL only)

    • Client Cert (MySQL only)

    • Client Key (MySQL only)

The SQL query entered must return a list of data. The data will be converted into JSON automatically so it can easily be used in the a field configuration later.

Variables in Queries

The SQL Query offers the flexibility to use variables. You can utilize variables such as {myVariable} in your queries. The variable names are customizable, allowing you to tailor them according to your requirements. Later on, you can set the values for these variables in the use case configuration.

Here's an example of a query that includes a variable:

SQL
SELECT * FROM myProducts WHERE category = '{myCategoryVar}'

Another example demonstrates how you can employ a list of values as a variable:

SQL
SELECT * FROM myProducts WHERE category IN ({myCategoryList})

By utilizing variables, you enhance the adaptability and customization of your queries based on different scenarios and input parameters.

Encryption in transit

Your machine must support TLS 1.2 to enable encrypted communication for PostgreSQL, MySQL and MSSQL. For Oracle the client requires network encryption to be available on the database.

Add IP’s to Allowlist

In case you need to allow an IP to let external data access your infrastructure, please add the following IP addresses to your allowlist:

Global / US:

  • 23.20.133.116

  • 3.223.177.5

Global / EU / DE:

  • 18.194.7.117

  • 3.124.29.165

Please visit our Data Residency section to find out your region.

Security Recommendations (warning)

For best practices when connecting to an external database, we recommend the following steps:

  1. Try to avoid connecting to a remote database in the first place (if possible). Use a REST API if available.

  2. Use a dedicated user account: Create a dedicated user account with limited permissions for connecting to the external database. Grant read-only access to the specific data required by the app.

  3. Restrict IP access: Configure the external database to accept connections only from specific IP addresses and include the ones listed above.

  4. Rotate passwords regularly: Change the password for the dedicated user account every six months, or more frequently if required by your organization's security policies.

  5. Use secure communication: If the external database supports SSL/TLS encryption for connections, make sure this option is enabled to encrypt data transmitted between the app and the database.

  6. Monitor access and usage: Regularly review logs and reports from the external database to identify suspicious activities, such as unauthorized access or data exfiltration.

Json Function Support

Json functions like JSON_ARRAY are supported and will be integrated into the resulting JSON and not rendered as string. So the following SQL result

CODE
+------+---------------------+
| id   | attributes          |
+------+---------------------+
|    2 | [{ "key": "color"},{ "key": "fabric"}] 
+------+---------------------+

will be converted to

JSON
{"result":[
    {
      "id":2,
      "attributes":[
          { "key": "color" },
          { "key": "fabric" }
      ]
    }
]}

MSSQL TLS Error

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 35 - An internal exception was caught)

This error message occurs mostly when the database server does not support TLS version 1.2.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.