Skip to content

SQL Database Routes

Feature Since Version Notes
SQL Database Routes >v1.6.3 Persists MQTT data to SQL databases.

1. Overview

The SQL Database Route implementations in the Coreflux MQTT broker provide a powerful and flexible way to store data from MQTT topics directly into various SQL-compliant databases.

For practical examples of LOT Routes, check out the Routes examples in the LOT Samples Repository. These routes support multiple database systems and offer robust configuration options for connection handling, security, and data storage. They can automatically create tables and map data types, simplifying the integration between your MQTT data streams and relational databases.

2. Supported Databases

Coreflux provides native support for the following SQL databases, each with its own specific route implementation:

  • PostgreSQL: Optimized for PostgreSQL databases.
  • MariaDB: Optimized for MariaDB databases.
  • MySQL: Optimized for MySQL databases.
  • Microsoft SQL Server: Support for MSSQL is also included.

Please refer to the specific documentation for each database for detailed configuration and examples.

3. Key Features across SQL Routes

  • Flexible Connection Configuration: Connect using a full connection string or individual parameters.
  • Secure Communication: Supports TLS/SSL connections to encrypt data in transit.
  • Automatic Table Creation: If a target table does not exist, the route will automatically create it based on the structure of the incoming data or a predefined model.
  • Dynamic Schema Handling: Intelligently maps JSON data types to appropriate SQL column types.
  • Nested JSON Flattening: Automatically flattens nested JSON objects into a single-level structure suitable for relational table storage.

4. Dynamic Table and Data Handling

4.1 Automatic Table Creation

When data is routed to a table that does not exist, the SQLClient will create it. The table schema is inferred from the fields of the JSON payload or from a MODEL if one is used in the PUBLISH statement. An id column (auto-incrementing primary key) and a timestamp column are automatically added. JSON field names are used as column names. Dots (.) in nested field names are replaced with underscores (_).

5.2 Data Type Mapping

The client maps JSON data types to corresponding SQL types for the specified database engine.

JSON/LOT Type PostgreSQL SQL Server MySQL/MariaDB
INT INTEGER INT INT
DOUBLE DOUBLE PRECISION FLOAT DOUBLE
BOOL BOOLEAN BIT TINYINT(1)
TIME_STRING TIMESTAMP DATETIME2 DATETIME
other TEXT NVARCHAR(MAX) TEXT

5.3 Nested Payload Handling

A nested JSON object like {"device": {"id": 1}, "value": 25.5} will be flattened into columns device_id and value.

6. Deployment and Management

Adding Routes via MQTT Command

All SQL routes can be deployed to the broker using the -addRoute command by publishing to the $SYS/Coreflux/Command topic. The specific configuration format varies by database type.

For more information about broker commands, see the MQTT Broker Commands documentation.

Deploying Routes with LOT Notebooks

Routes can also be created and deployed interactively using LOT Notebooks in Visual Studio Code. This provides a convenient way to develop, test, and document your routes.

To get started with LOT Notebooks: 1. Install the LOT Notebooks extension in VS Code 2. Create a new .lotnb file 3. Add code cells with your route definitions 4. Execute the cells to deploy directly to your broker

For detailed instructions, see the LOT Notebooks Getting Started Guide.