# SQL The SQL connector enables Connectware to communicate with relational databases. This implementation uses Sequelize (), a Node.js ORM (Object-Relational Mapping) tool, to provide read, write, and subscribe capabilities for database operations. ## Supported databases * MariaDB * PostgreSQL ## Usage In general, the implementation works by specifying a query or query template. The following examples use a sample table called `people` that looks like this: | id | name | lastname | gender | | -- | ----- | -------- | ------ | | 1 | Alice | Miller | female | | 2 | Bob | Jones | male | ### Connection To connect to a database, you must provide a URL when specifying the `Cybus::Connection` resource: {% code lineNumbers="true" %} ```yaml ://:@:/ ``` {% endcode %} For example, to connect to a MariaDB database use this: {% code lineNumbers="true" %} ```yaml 'mariadb://johndoe:my-secret-pw@localhost:3306/test' ``` {% endcode %} To connect to a Postgres database use this: {% code lineNumbers="true" %} ```yaml 'postgres://johndoe:my-secret-pw@localhost/test' ``` {% endcode %} We recommend defining these properties as `parameters` in the service commissioning file and reference them in the connection settings using `!ref`, so that the actual value can be edited at deployment time. See [Service Commissioning File Example](#service-commissioning-file-example) below. For more information, see [Connection Properties](https://docs.cybus.io/connectors/enterprise-connectors/mssql/mssqlconnection). ### Reading Data To read data from a database, an endpoint has to be defined with either read or subscribe properties. Subscribe works by defining a polling interval, hence the query will be executed on a regular basis. Read is executed each time an MQTT message is sent to the respective endpoint topic with the `/req` (request) suffix, where the result is sent to the endpoint topic with the `/res` suffix. The result of the query is provided in JSON format on the MQTT broker. Example endpoint definition: {% code lineNumbers="true" %} ```yaml sqlQuery1: type: Cybus::Endpoint properties: protocol: Sql connection: !ref sqlConnection subscribe: query: 'SELECT * FROM people' interval: 2000 ``` {% endcode %} This endpoint will execute the given query and return the data as MQTT messages like in the following example. If no rows are returned, you will receive an empty array (`[]`) as a value. {% code lineNumbers="true" %} ```json { "timestamp": 1231782312, // unix timestamp in ms "value": [ { "id": 1, "name": "Alice", "lastname": "Miller", "gender": "female" }, { "id": 2, "name": "Bob", "lastname": "Jones", "gender": "male" } ] } ``` {% endcode %} ### Placeholder Syntax The SQL query definition can be defined as a template string containing placeholders in the WHERE clause. In the template, the dollar sign character `$` followed by an identifier is used to denote placeholders. The placeholders will be replaced by the values from the payload of the JSON message received via MQTT. If no placeholders are defined, the query will be executed as-is. **Placeholder naming rules:** The regular expression for placeholder identifiers is `$[a-zA-Z0-9_]+`. This means: * Placeholders start with a single dollar sign `$` * Followed by an identifier consisting of ASCII letters, numbers, and underscores * Examples: `$name`, `$user_id`, `$value123` **Special cases:** * Multiple dollar signs (e.g., `$$VERSION`) are not interpreted as placeholders and will be passed unchanged to the SQL query. * A single dollar sign followed by whitespace or punctuation (e.g., `$` or `$.`) is not interpreted as a placeholder. * To use a single `$` that matches the pattern above in the SQL query, define it as a placeholder and send the desired string as a value in the message payload. **Usage requirements:** If the SQL query contains placeholder definitions, all their names must exist in the message payload, otherwise an error will be logged and the message will be ignored. The value of the placeholders must have the right data format matching the target schema of the database. Example endpoint definition: {% code lineNumbers="true" %} ```yaml sqlQuery1: type: Cybus::Endpoint properties: protocol: Sql connection: !ref sqlConnection read: query: 'SELECT * FROM people WHERE lastname = $lastname' ``` {% endcode %} Sending a message to the `/req` topic of this endpoint with the following payload: {% code lineNumbers="true" %} ```json { "lastname": "Miller" } ``` {% endcode %} will return results filtered based on the where clause configured: {% code lineNumbers="true" %} ```json { "timestamp": 1231792312, // unix timestamp in ms "value": [ { "id": 1, "name": "Alice", "lastname": "Miller", "gender": "female" } ] } ``` {% endcode %} ## Output Format on Read When data is read from SQL, results are published to the `/res` topic of the endpoint. The output message is an object with two properties: * timestamp: is the unix timestamp, in milliseconds, of when the read was executed * value: is an array of results as returned by the SQL query ## Writing Data To write data to the database, an endpoint with write properties has to be defined. This endpoint needs the definition of an SQL query with a query template containing variables. The variables will be filled by the values of the payload of the JSON message received via MQTT. In the SQL query definition, the query syntax is used as a template string containing placeholders. In the template, the dollar sign `$` followed by an identifier is used to denote placeholders (see [Placeholder Syntax](#placeholder-syntax) for details). The placeholders will be replaced by the values from the payload of the JSON message received via MQTT. All specified template variables must exist in the payload and must have the right target format matching the schema of the database. Example endpoint definition: {% code lineNumbers="true" %} ```yaml sqlQuery1: type: Cybus::Endpoint properties: protocol: Sql connection: !ref sqlConnection write: query: 'INSERT INTO people (name, lastname, gender) VALUES ($name, $lastname, $gender)' ``` {% endcode %} When using bulk insert you need to specify the endpoint like this: {% code lineNumbers="true" %} ```yaml sqlQuery2: type: Cybus::Endpoint properties: protocol: Sql connection: !ref sqlConnection write: query: 'INSERT INTO people (name, lastname, gender) VALUES' queryValues: '($name, $lastname, $gender)' ``` {% endcode %} To write data, you must send an MQTT message like the following to the `/set` topic of the endpoint: {% code lineNumbers="true" %} ```json { "name": "Alice", "lastname": "Miller", "gender": "female" } ``` {% endcode %} Alternatively, you can also send multiple rows into a single message for performance reasons like this: {% hint style="warning" %} When using this method of insertion make sure all rows have the same amount of columns. {% endhint %} You will also need to specify the parameter queryValues in the endpoint definition. {% code lineNumbers="true" %} ```json [ { "name": "Alice", "lastname": "Miller", "gender": "female" }, { "name": "John", "lastname": "Clark", "gender": "male" } ] ``` {% endcode %} {% hint style="warning" %} The SQL connection on the Connectware side does not perform any data validation against the database schema. The senders of the MQTT messages themselves must ensure to send the data in the correct format. {% endhint %} ### Output Format on Write When data is written to an SQL endpoint, a message is published to the `/res` topic of the endpoint. The output message is an object with two properties: * timestamp: is the unix timestamp, in milliseconds, of when the write was executed * value: is set to true when the write was successful * [Connection Properties](https://docs.cybus.io/connectors/enterprise-connectors/sql/sqlconnection) * [Endpoint Properties](https://docs.cybus.io/connectors/enterprise-connectors/sql/sqlendpoint) ## Service Commissioning File Example {% file src="" %} {% code title="sql-example.yml" lineNumbers="true" %} ```yaml --- description: > Sample SQL service commissioning file metadata: name: Sample SQL service icon: https://www.cybus.io/wp-content/uploads/2017/10/for-whom1.svg provider: cybus homepage: https://www.cybus.io version: 1.0.0 parameters: sqlURL: type: string default: 'mariadb://root:my-secret-pw@localhost:3306/mysql' initialReconnectDelay: type: integer default: 1000 maxReconnectDelay: type: integer default: 30000 factorReconnectDelay: type: integer default: 2 resources: sqlConnection: type: Cybus::Connection properties: protocol: Sql connection: url: !ref sqlURL connectionStrategy: initialDelay: !ref initialReconnectDelay maxDelay: !ref maxReconnectDelay incrementFactor: !ref factorReconnectDelay sqlQuery1: type: Cybus::Endpoint properties: protocol: Sql connection: !ref sqlConnection subscribe: query: 'SELECT User FROM user' # Be very careful with this setting. A low value might overload # the database (the unit is milliseconds!). interval: 2000 sqlQuery2: type: Cybus::Endpoint properties: protocol: Sql connection: !ref sqlConnection write: # Here we use the placeholder $someValue. The protocol driver will insert # the value from the input JSON message under the key someValue query: 'INSERT INTO test (id, some_column) VALUES ($id, $someValue)' mapping: type: Cybus::Mapping properties: mappings: - subscribe: endpoint: !ref sqlQuery1 publish: topic: 'database-users' - subscribe: topic: 'database-insert-test' publish: endpoint: !ref sqlQuery2 ``` {% endcode %} --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.cybus.io/connectors/enterprise-connectors/sql.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.