> ## Documentation Index
> Fetch the complete documentation index at: https://docs.coreflux.org/llms.txt
> Use this file to discover all available pages before exploring further.

# MySQL Route

> Store MQTT data in MySQL with automatic query execution and SSL support

## MySQL Overview

The `MYSQL` route stores MQTT messages in MySQL databases. It supports SSL connections, parameterized queries, and automatic reconnection for reliable data persistence.

<Note>
  MySQL is ideal for web applications and general-purpose data storage. Its wide ecosystem support makes it a reliable choice for most IoT data persistence needs.
</Note>

## Basic Syntax

```lot theme={null}
DEFINE ROUTE SensorDB WITH TYPE MYSQL
    ADD SQL_CONFIG
        WITH SERVER "mysql.example.com"
        WITH PORT '3306'
        WITH DATABASE "iot_data"
        WITH USERNAME "iot_user"
        WITH PASSWORD "secure_password"
    ADD EVENT StoreSensorReading
        WITH SOURCE_TOPIC "sensors/+/reading"
        WITH QUERY "INSERT INTO readings (ts, sensor_id, value) VALUES (NOW(), '{sensor_id}', '{value.json}')"
```

***

## Connection Configuration

### SQL\_CONFIG Parameters

<ParamField path="SERVER" type="string" required>
  MySQL server hostname or IP address.
</ParamField>

<ParamField path="DATABASE" type="string" required>
  Target database name.
</ParamField>

<ParamField path="USERNAME" type="string" required>
  Database username.
</ParamField>

<ParamField path="PASSWORD" type="string" required>
  Database password.
</ParamField>

<AccordionGroup>
  <Accordion title="Optional Settings">
    <ParamField path="PORT" type="integer">
      MySQL port. Default: 3306.
    </ParamField>

    <ParamField path="USE_SSL" type="boolean">
      Enable SSL connection. Default: false.
    </ParamField>

    <ParamField path="CONNECTION_TIMEOUT" type="integer">
      Connection timeout in seconds. Default: 30.
    </ParamField>
  </Accordion>
</AccordionGroup>

<Warning>
  **Never hardcode credentials in production.** Use environment variables and encrypted secrets to keep sensitive values out of your route definitions:

  ```lot theme={null}
  WITH SERVER GET ENV "DB_HOST"
  WITH USERNAME GET ENV "DB_USER"
  WITH PASSWORD GET SECRET "DB_PASSWORD"
  ```

  See [Environment Variables & Secrets](/mqtt-broker/secrets-and-env) for setup and usage.
</Warning>

***

## Event Configuration

### EVENT Parameters

<ParamField path="SOURCE_TOPIC" type="string" required>
  MQTT topic pattern that triggers the INSERT. Supports wildcards.
</ParamField>

<ParamField path="QUERY" type="string" required>
  SQL INSERT statement with placeholders for dynamic values.
</ParamField>

<AccordionGroup>
  <Accordion title="Optional Settings">
    <ParamField path="DESTINATION_TOPIC" type="string">
      MQTT topic to publish query result status.
    </ParamField>
  </Accordion>
</AccordionGroup>

### Query Placeholders

| Placeholder          | Description                     |
| -------------------- | ------------------------------- |
| `{value.json}`       | Full JSON payload               |
| `{value.json.field}` | Specific field from JSON        |
| `{timestamp}`        | Message timestamp               |
| `{source_topic}`     | Original MQTT topic             |
| `{field}`            | Field extracted from topic path |

***

## Writing Data

<Tabs>
  <Tab title="Basic Sensor Storage">
    Store sensor readings with timestamps:

    ```lot theme={null}
    DEFINE ROUTE TemperatureDB WITH TYPE MYSQL
        ADD SQL_CONFIG
            WITH SERVER "mysql.example.com"
            WITH PORT '3306'
            WITH DATABASE "iot_data"
            WITH USERNAME "iot_user"
            WITH PASSWORD "secure_password"
        ADD EVENT StoreTemperature
            WITH SOURCE_TOPIC "sensors/+/temperature"
            WITH QUERY "INSERT INTO temperature_readings (recorded_at, sensor_id, value) VALUES (NOW(), '{sensor_id}', {value.json.value})"
    ```
  </Tab>

  <Tab title="With SSL">
    Secure connection with SSL:

    ```lot theme={null}
    DEFINE ROUTE SecureDB WITH TYPE MYSQL
        ADD SQL_CONFIG
            WITH SERVER "mysql.cloud.com"
            WITH PORT '3306'
            WITH DATABASE "production"
            WITH USERNAME "app_user"
            WITH PASSWORD "secure_password"
            WITH USE_SSL "true"
        ADD EVENT StoreEvent
            WITH SOURCE_TOPIC "events/#"
            WITH QUERY "INSERT INTO events (ts, topic, payload) VALUES (NOW(), '{source_topic}', '{value.json}')"
    ```
  </Tab>

  <Tab title="Multiple Events">
    Different tables for different data types:

    ```lot theme={null}
    DEFINE ROUTE MultiTableDB WITH TYPE MYSQL
        ADD SQL_CONFIG
            WITH SERVER "mysql.example.com"
            WITH PORT '3306'
            WITH DATABASE "iot_data"
            WITH USERNAME "iot_user"
            WITH PASSWORD "secure_password"
        
        ADD EVENT StoreSensorData
            WITH SOURCE_TOPIC "sensors/+/data"
            WITH QUERY "INSERT INTO sensor_data (ts, sensor_id, value) VALUES (NOW(), '{sensor_id}', '{value.json}')"
        
        ADD EVENT StoreAlerts
            WITH SOURCE_TOPIC "alerts/+"
            WITH QUERY "INSERT INTO alerts (ts, alert_type, message) VALUES (NOW(), '{alert_type}', '{value.json}')"
    ```
  </Tab>

  <Tab title="UPSERT">
    Insert a record or update it if a duplicate key exists. MySQL uses `ON DUPLICATE KEY UPDATE`:

    ```lot theme={null}
    DEFINE ROUTE DeviceRegistry WITH TYPE MYSQL
        ADD SQL_CONFIG
            WITH SERVER "mysql.example.com"
            WITH PORT '3306'
            WITH DATABASE "iot_data"
            WITH USERNAME "iot_user"
            WITH PASSWORD "secure_password"
        ADD EVENT RegisterDevice
            WITH SOURCE_TOPIC "devices/+/registered"
            WITH QUERY "INSERT INTO devices (device_id, name, area, active, updated_at) VALUES ('{value.json.device_id}', '{value.json.name}', '{value.json.area}', 1, NOW()) ON DUPLICATE KEY UPDATE name = VALUES(name), area = VALUES(area), updated_at = NOW()"
    ```

    `ON DUPLICATE KEY UPDATE` fires only when a row with the same unique key already exists — the existing row is updated instead of inserting a duplicate.
  </Tab>

  <Tab title="UPDATE">
    Modify existing records when a status or value change arrives:

    ```lot theme={null}
    DEFINE ROUTE DeviceRegistry WITH TYPE MYSQL
        ADD SQL_CONFIG
            WITH SERVER "mysql.example.com"
            WITH PORT '3306'
            WITH DATABASE "iot_data"
            WITH USERNAME "iot_user"
            WITH PASSWORD "secure_password"
        ADD EVENT UpdateDeviceStatus
            WITH SOURCE_TOPIC "devices/+/status"
            WITH QUERY "UPDATE devices SET current_status = '{value.json.status}', last_seen = NOW() WHERE device_id = '{value.json.device_id}'"
    ```
  </Tab>

  <Tab title="DELETE">
    Remove records on a decommission or cleanup event:

    ```lot theme={null}
    DEFINE ROUTE DeviceRegistry WITH TYPE MYSQL
        ADD SQL_CONFIG
            WITH SERVER "mysql.example.com"
            WITH PORT '3306'
            WITH DATABASE "iot_data"
            WITH USERNAME "iot_user"
            WITH PASSWORD "secure_password"
        ADD EVENT DecommissionDevice
            WITH SOURCE_TOPIC "devices/+/decommissioned"
            WITH QUERY "DELETE FROM devices WHERE device_id = '{value.json.device_id}' AND active = 0"
    ```
  </Tab>
</Tabs>

<Info>
  **Alternative: STORE IN with Models** — Instead of writing EVENT queries, you can bind a [model](/lot-language/models/overview) directly to this route. Every `PUBLISH MODEL` call automatically inserts a row — no query needed:

  ```lot theme={null}
  DEFINE MODEL SensorReading
      ADD STRING "sensor_id"
      ADD DOUBLE "value"
      STORE IN "TemperatureDB"
          WITH TABLE "temperature_readings"
  ```

  See [Data Storage Overview](./overview#with-models-store-in) for the full STORE IN workflow.
</Info>

***

## Reading Data

EVENTs also support SELECT queries to read data back from the database. Publish a message to the event's `SOURCE_TOPIC`, and the query result is published to `DESTINATION_TOPIC`:

```lot theme={null}
DEFINE ROUTE SensorDB WITH TYPE MYSQL
    ADD SQL_CONFIG
        WITH SERVER "mysql.example.com"
        WITH PORT '3306'
        WITH DATABASE "iot_data"
        WITH USERNAME "iot_user"
        WITH PASSWORD "secure_password"
    ADD EVENT GetLatestReading
        WITH SOURCE_TOPIC "db/query/latest"
        WITH DESTINATION_TOPIC "db/result/latest"
        WITH QUERY "SELECT sensor_id, value FROM readings WHERE sensor_id = '{payload}' ORDER BY ts DESC LIMIT 1"
```

To trigger this query, publish the sensor ID to the source topic:

```
Topic:   db/query/latest
Payload: temp001
```

The query result is published to `db/result/latest`, where your actions or external clients can consume it.

### Timed Polling

EVENTs can also run SELECT queries on a fixed schedule using `WITH EVERY`:

```lot theme={null}
DEFINE ROUTE DeviceRegistry WITH TYPE MYSQL
    ADD SQL_CONFIG
        WITH SERVER "mysql.example.com"
        WITH PORT '3306'
        WITH DATABASE "iot_data"
        WITH USERNAME "iot_user"
        WITH PASSWORD "secure_password"
    ADD EVENT PollActiveDevices
        WITH EVERY 10 SECONDS
        WITH DESTINATION_TOPIC "dashboard/devices/active"
        WITH QUERY "SELECT device_id, name, current_status, last_seen FROM devices WHERE active = 1 ORDER BY name"
```

***

## Table Schema Examples

```sql theme={null}
-- Basic sensor readings
CREATE TABLE sensor_readings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    sensor_id VARCHAR(50),
    value DOUBLE
);

-- Event log
CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    topic VARCHAR(255),
    payload JSON
);
```

***

## Troubleshooting

<AccordionGroup>
  <Accordion title="Connection Refused">
    * Verify SERVER and PORT are correct
    * Check MySQL is running and accepting connections
    * Verify firewall allows connections on port 3306
    * Check `bind-address` in MySQL configuration
  </Accordion>

  <Accordion title="Authentication Failed">
    * Verify USERNAME and PASSWORD are correct
    * Check user has permissions on the DATABASE
    * Verify user can connect from your host
  </Accordion>

  <Accordion title="Query Syntax Errors">
    * Check placeholder names match topic structure
    * Verify table and column names exist
    * Test query manually in MySQL client first
  </Accordion>
</AccordionGroup>

***

## Next Steps

<CardGroup cols={2}>
  <Card title="Data Storage Routes Overview" icon="database" href="./overview">
    Compare all storage options.
  </Card>

  <Card title="MariaDB Route" icon="database" href="./mariadb">
    Configure MariaDB storage (MySQL-compatible).
  </Card>
</CardGroup>