Choosing the type Database will change the form as follows:

Database Driver

Select the kind of database you want to connect to

  • MySQL

  • PostgreSQL

  • MSSQL

  • Oracle

  • IBM Db2

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 panel later.

Variables in Queries

The SQL Query supports variables to make the data source more flexible or dependant on context information. An example could be 

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

You can set the value of the variable later in the panel configuration.

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.

Whitelist IP’s

In case you need to whitelist an IP to let external data access your database, please whitelist the following IP addresses:

  • 23.20.133.116

  • 3.223.177.5

Security Recommendations (warning)

For best practices to connect to a 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. create a dedicated database-user with most limited permissions. If possible only read permissions to the data you want to fetch with the App.

  3. Whitelist our IP’s in your firewall to reduce the attack surface.

  4. Rotate the password of the database-user regularly. An interval of 6 month’s is appropriate in most cases.

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

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

will be converted to

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

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.