Skip to main content

SQL

The SQL connector is an enterprise connector that allows you to read/write data from/to an SQL database. It currently supports:

  • PostgreSQL
  • MySQL/MariaDB
  • Microsoft SQL Server

Connection Settings​

SettingDescription
Database TypeThe type of database to connect to.
HostThe hostname or IP address of the database.
PortThe port of the database.
UserThe user to log in to the database with.
PasswordThe password to log in to the database with.
DatabaseThe database to log in to.
Command TimeoutTime to wait (in seconds) before canceling a command. Set to 0 to disable.
Extra OptionsExtra settings that are unique to each database type.123

Reading data from a database​

Data can be read from a database with an input property. All input properties have a polling interval and a query that will be periodically executed.

If you have the following table named measurements:

time                          | location | temperature
------------------------------+----------+-------------
2025-04-01 12:36:10.003265+00 | office   |        22.8
2025-04-01 18:36:10.003265+00 | kitchen  |        23.0

The data from measurements can be read with a simple query like this:

SELECT * FROM measurements;

The result of this query will be formatted as a json array like follows:

[
    {
        "time": "2025-04-01 12:36:10.003265+00",
        "location": "office",
        "temperature": 22.8,
    },
    {
        "time": "2025-04-01 18:36:10.003265+00",
        "location": "kitchen",
        "temperature": 23.0,
    }
]

Writing data to a database​

Data can be written to a database with an output property. Output properties do not have a polling interval, their queries will be executed when data is written to them.

Parametrizing a query​

To dynamically write data to a database, you can parameterize a query. Parameters are prepended by an @. Data is written to the property as a json object (eg. to write to @param1 and @param2 you need {"param1":"value1","param2":"value2"}).

To write data to measurements you can create an output property with a query like this:

INSERT INTO measurements (time, location, temperature, humidity)
VALUES (@time, @location, @temperature);

Specifying parameter data types​

It's also necessary to specify the data type of each parameter. Below the query field dropdown menus will appear to pick the type for each parameter.

SQL Data Types

Writing values to parameters​

To actually write values to the database you can write a json object to the property like this:

{
    "time": "2025-04-02 15:24:03.001",
    "location": "office",
    "temperature": 23.1
}

After the values are written to the bridge property the query will be executed.

Testing a query​

Before saving a property, you might want to test the query to see if it works. As soon as you start typing in the query field a test button will appear underneath it. This allows you to test your query without making any changes to the database.

Testing a query with parameters​

When using query parameters, you can't test your query without specifying the values of the parameters. Below the parameter types you can add values for testing the query. You can also copy the values as a json object.

SQL Test Query

Transforming your data​

Your data might not be in the correct format required for the SQL connector, this can be easily fixed by means of a transformation.

Footnotes​

  1. Extra Options for PostgreSQL ↩

  2. Extra Options for MySQL/MariaDB ↩

  3. Extra Options for Microsoft SQL ↩