Database
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}'
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
For best practices to connect to a database we recommend the following steps:
Try to avoid connecting to a remote database in the first place (if possible). Use a REST API if available.
create a dedicated database-user with most limited permissions. If possible only read permissions to the data you want to fetch with the App.
Whitelist our IP’s in your firewall to reduce the attack surface.
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"}]
+------+---------------------+
will be converted to
{"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.