The Database Connections configuration page holds the named connections to external databases that Data Grid objects read from and write to, that Data Forwarders write to, and that the Database Query tool (PageDatabaseQuery in page scripts) runs SQL on. All of them refer to a connection by its Name, so the connection string is stored once in the configuration, never on a page, in a page script or per forwarder.
Supported database types are SQL Server, MySQL, SQLite, PostgreSQL and InfluxDB. A PostgreSQL connection also connects to TimescaleDB. An InfluxDB connection is used by Data Forwarders, and an InfluxDB 3 connection also by History Sources; see the InfluxDB section below. A SQLite connection points at a file; a relative path resolves against the EnvisionSCADA installation folder. The file is opened for reading and writing and is never created by EnvisionSCADA, so a wrong path is reported as an error instead of producing an empty database.
The built-in databases keep their protection: a Data Grid on Configuration.db or Historian.db is read only, and Local.db cannot be used as a source.
Database Connection Settings
Create a connection with the Add button in the toolbar. The Add dialog contains the sections General, Database and Database Query Access. Enter the Name, select the Database Type, enter the Connection String and press Create. The Load template icon next to the Connection String fills in an example for the selected Database Type.
Press Test Connection in the dialog to open the database with the values as typed, before or after saving. The result and the response time are shown next to the button. The Test icon on a connection row tests the saved connection; its result is shown in the Status column.
The connection row shows Name, Database Type, Description and Allow Database Query and these values can be edited in place. Press the Settings icon to open the Edit Database Connection dialog, which also holds the Connection String and the Access Right.
| Setting | Description | Example | Units / Format |
|---|---|---|---|
| Name | Unique name of the connection. Data Grid objects and Data Forwarders select it by this name. Renaming a connection renames the reference on every data forwarder that uses it, and the Connection of every History Source that reads from it. | ProductionSQL | Text |
| Description | Free text description for documentation purposes. | Batch orders database | Text |
| Database Type | Database provider. Select from SQL Server, MySQL, SQLite, PostgreSQL or InfluxDB. Select PostgreSQL for TimescaleDB. | SQL Server | Enum |
| Connection String | Provider connection string. SQL Server: Server, Database and either User Id and Password or Integrated Security. MySQL: Server, Port, Database, User and Password. SQLite: Data Source with the file path. PostgreSQL: Host, Port, Database, Username and Password, optionally SSL Mode and a client certificate (see the PostgreSQL section). InfluxDB 2.x: Url, Org, Bucket and Token. InfluxDB 3: Url, Database and Token. | Server=sql01;Database=Production;User Id=envision;Password=secret;TrustServerCertificate=True; | Text |
| Allow Database Query | Allows the Database Query tool (PageDatabaseQuery in page scripts, the runtime API and MCP) to run SQL on this connection. Data Grid objects and Data Forwarders use the connection regardless of this setting. | true | Boolean |
| Access Right | User groups that may run Database Query on this connection. Press ... to select the groups. When empty, every logged-in user can query the connection. Members of Administrators always have access; Guests never do. | Operators | User groups |
No rows match the filter.
Usage
Once a connection exists, the Data Grid decorator lists it under Connection and reads the tables, views and columns of the database from there, so no SQL is written in the designer. For an InfluxDB 3 connection it lists the measurements of the database. The Data Forwarders page lists it under Connection as well. See the Data Grid design article and the Data Forwarders configuration article. A History Source of Type Database lists the SQL Server, MySQL, SQLite, PostgreSQL and InfluxDB 3 connections under Connection and reads the history of its variables with a query on the connection; see the History Sources article.
The connection is part of the project export and of the Git configuration files, connection string included. When a project from an older version is upgraded or imported, the connection strings of its Data Forwarders and DataTable objects become connections on this page, named after the server and database or the SQLite file, and the forwarders and the converted Data Grids are linked to them. A connection that a data forwarder selects cannot be deleted.
Connections are also available through the Config API and MCP as the DatabaseConnections entity, with the extra endpoints Test, Tables and Columns.
With Allow Database Query set, a page script queries the connection by its Name, for example PageDatabaseQuery('ProductionSQL', 'SELECT ...'). The Database Query Access section of the dialog shows this call for the connection. Renaming a connection does not change the name in page scripts. See the Database Query article.
SQL Server
Without the Encrypt key in the Connection String, a SQL Server connection encrypts the login only. When the server forces encryption, the whole connection is encrypted and the server certificate is accepted without validation, unless the Connection String contains TrustServerCertificate=False.
Add Encrypt=True to encrypt the whole connection. The server certificate is then validated, so a server with a self-signed certificate also needs TrustServerCertificate=True. Test Connection reports a certificate that is not trusted with the message The certificate chain was issued by an authority that is not trusted.
Server=sql01;Database=Production;User Id=envision;Password=secret;Encrypt=True;TrustServerCertificate=True;PostgreSQL
The tables, views and materialized views of a PostgreSQL database are listed with their schema, for example public.orders. A table name without a schema refers to the first schema in the search path of the connecting user, normally public. Table and column names are used with the exact spelling the database reports. A materialized view cannot be changed by statements, so use it in a Data Grid without editing.
In a Data Grid, Search and the contains and starts with filters are case-insensitive. Columns of a type without a matching Data Grid column type, such as uuid, enum, json, jsonb and arrays, are shown as Text; filters compare their text form, and an edited value is converted to the column type by the database. A value the database cannot convert, for example an invalid uuid, is refused with the database message.
Values of timestamp with time zone columns are shown and entered in the local time of the EnvisionSCADA server. Values of timestamp columns are shown and stored as they are.
TimescaleDB is PostgreSQL with the TimescaleDB extension and uses a PostgreSQL connection. Hypertables are listed as tables and continuous aggregates as views. The internal TimescaleDB schemas, which hold the chunks of every hypertable, are not listed. The Data Forwarders article shows how to set up the forwarded historian data as a hypertable.
Without the SSL Mode key in the Connection String, a PostgreSQL connection is encrypted when the server supports it, and the server certificate is accepted without validation. SSL Mode=VerifyFull encrypts the connection and validates the server certificate and its host name. Root Certificate holds the file of the CA certificate that issued the server certificate.
For a login with a client certificate instead of a password, add SSL Certificate and SSL Key with the certificate file and the key file of the database user, leave out the Password key, and configure certificate authentication for that user on the PostgreSQL server (hostssl with the cert method in pg_hba.conf). The files must be present on the EnvisionSCADA server, on both the Primary and the Standby server in a redundant setup, and readable by the account that runs EnvisionSCADA.
Host=db01.example.local;Port=5432;Database=plant;Username=envision;SSL Mode=VerifyFull;Root Certificate=C:\Certs\ca.crt;SSL Certificate=C:\Certs\envision.crt;SSL Key=C:\Certs\envision.keyInfluxDB
An InfluxDB connection connects to InfluxDB 2.x or InfluxDB 3 and is used by Data Forwarders. A History Source of Type Database also reads the history of its variables from an InfluxDB 3 connection, through the SQL of InfluxDB 3; see the History Sources article. The keys of the Connection String select the version: Org and Bucket for InfluxDB 2.x, Database for InfluxDB 3. InfluxDB stores measurements with tags, fields and a timestamp, and InfluxDB 3 reads them with SQL, each measurement as a table. An InfluxDB 3 connection is therefore also offered to Data Grid objects and the Database Query tool, read only: InfluxDB 3 SQL has no INSERT, UPDATE or DELETE. An InfluxDB 2.x connection serves Data Forwarders: the Data Grid lists the InfluxDB 3 connections, and the Database Query Access section of the dialog is shown for an InfluxDB 3 connection string.
| Connection string key | Description | Example |
|---|---|---|
| Url | Address of the InfluxDB server, http or https, with the port. InfluxDB 2.x listens on port 8086 by default, InfluxDB 3 on port 8181. | http://influx01:8086 |
| Org | InfluxDB 2.x: organization the bucket belongs to. | Plant |
| Bucket | InfluxDB 2.x: bucket the data forwarder writes to. | historian |
| Database | InfluxDB 3: database the data forwarder writes to and a History Source reads from. | historian |
| Token | API token that may write to the bucket or database, and read the database for a History Source. For an InfluxDB 3 server that runs without authentication the key can be left out. | (token from InfluxDB) |
No rows match the filter.
Url=http://influx01:8086;Org=Plant;Bucket=historian;Token=...Url=http://influx01:8181;Database=historian;Token=...Test Connection checks that the server answers and accepts the token. For InfluxDB 2.x it also checks that the token is allowed to write to the bucket, for InfluxDB 3 that the database exists. The test stores no data.
InfluxDB 3 creates a database on the first write to a name it does not know. A data forwarder therefore writes only to an InfluxDB 3 database that exists, and reports a database that does not exist in the System Log instead of creating it. Create the token and the database on the InfluxDB 3 server first:
influxdb3 create token --admin
influxdb3 create database historian --token apiv3_...