# MySQL

> Send conversation messages stored in MySQL to Dimension Labs with the universal API.

If your conversations already land in a MySQL table, you can send them to Dimension Labs from there. A scheduled job on your side queries the rows that are new since its last run and posts each message to the [universal API](/api/tracker).

## What you need

- A MySQL account that can create users and grant access to the table that holds your conversations.
- The database's host, port and name, and network access to it from wherever the job runs. Managed hosts often need the job's IP address on an allowlist.
- A Dimension Labs API key for a new **Universal API** source. See [Your API key](/api/tracker#your-api-key).

## 1. Get access

Ask your database admin to create a MySQL user for the job that can only read the conversations table:

```sql
CREATE USER 'dl_sync'@'%' IDENTIFIED BY 'choose-a-long-password';
GRANT SELECT ON support.conversation_messages TO 'dl_sync'@'%';
```

Replace `'%'` with the job's host or IP address if you can, so the account only works from there. Keep the password in a secrets manager, never in code you commit.

## 2. Query new rows

Use the `mysql2` package. Each run asks for the rows changed since the last run, using `updated_at` as the cursor:

```sql
SELECT message_id, conversation_id, customer_id, sender_type, body, channel, sent_at, updated_at
FROM support.conversation_messages
WHERE updated_at >= ?
ORDER BY updated_at;
```

- `connection.execute()` prepares the statement and fills each `?` from the values you pass ([mysql2 quickstart](https://sidorares.github.io/node-mysql2/docs)). The code passes the cursor as a JavaScript `Date`.
- mysql2 turns `DATETIME` and `TIMESTAMP` values into JavaScript `Date` objects, and back, using its `timezone` option, which defaults to `'local'` ([connection options](https://github.com/sidorares/node-mysql2/blob/master/typings/mysql/lib/Connection.d.ts)). The code sets `timezone: 'Z'`, so a `DATETIME` is read and written as UTC. Store your times in UTC, or set `timezone` to the offset they are stored in, in the form `+HH:MM` ([option reference](https://github.com/mysqljs/mysql#connection-options), which mysql2 follows).

## 3. Map the columns

This guide assumes a table with one row per message, with these columns. Rename them to match yours.

| Column | Holds |
|---|---|
| `message_id` | The message's own id |
| `conversation_id` | The conversation, ticket or chat the message belongs to |
| `customer_id` | Your internal id for the customer in that conversation, on every row, including agent and bot rows. Not an email address or phone number. |
| `sender_type` | Who wrote the message, for example `customer`, `agent` or `bot` |
| `body` | The message text |
| `channel` | Chat, email, voice and so on |
| `sent_at` | When the message was sent (`DATETIME` in UTC, or `TIMESTAMP`) |
| `updated_at` | When the row was written or last changed (same type) |

| Dimension Labs | MySQL column | Notes |
|---|---|---|
| `text` | `body` | Skip rows where it is empty. Strip HTML if bodies are HTML. |
| `userId` | `customer_id` | If it is null, use the `sessionId`. |
| `incoming` | `sender_type` | `true` only when the customer wrote it. Agent and bot rows are `false`. |
| `sessionId` | `conversation_id` | Replace any colons. |
| `conversationId` | `conversation_id` | Same value as `sessionId`. |
| `dimensionlabs_timestamp` | `sent_at` | Pass the `Date` through `toMillis`. |
| `platformJson` | `message_id`, `channel` | Add any join keys you have, such as an account or store id. |

**Personal data.** Don't select email, phone or name columns. If your only customer key is an email, send a consistent hash of it instead. Message text can still hold personal details: redact them in the function before you send. See [PII redaction](/data/pii-redaction).

## 4. Send it

Install the driver in your project first: `npm install mysql2`.

```js
// mysql.mjs. Environment: MYSQL_HOST, MYSQL_PORT (optional, 3306), MYSQL_USER, MYSQL_PASSWORD, MYSQL_DATABASE, DL_API_KEY.



const HOUR = 60 * 60 * 1000;
const SQL = `SELECT message_id, conversation_id, customer_id, sender_type, body, channel, sent_at, updated_at
  FROM support.conversation_messages WHERE updated_at >= ? ORDER BY updated_at`;
const clean = (s) => String(s ?? '').replace(/<[^>]+>/g, ' ').replace(/\s+/g, ' ').trim();

// Sends every row written or changed at or after `since` (Unix ms). Returns the new `since`.
export async function syncMySQL({ since, historical = false }) {
  const connection = await mysql.createConnection({
    host: process.env.MYSQL_HOST,
    port: Number(process.env.MYSQL_PORT || 3306),
    user: process.env.MYSQL_USER,
    password: process.env.MYSQL_PASSWORD,
    database: process.env.MYSQL_DATABASE,
    timezone: 'Z', // read and write DATETIME values as UTC
  });
  try {
    const [rows] = await connection.execute(SQL, [new Date(since)]);
    let newest = since;
    for (const row of rows) {
      const text = clean(row.body);
      const id = String(row.conversation_id).replaceAll(':', '-');
      if (text) {
        const ts = toMillis(row.sent_at);
        await sendToDimensionLabs({
          text,
          userId: row.customer_id ? String(row.customer_id).replaceAll(':', '-') : id,
          incoming: row.sender_type === 'customer',
          sessionId: id,
          conversationId: id,
          dimensionlabs_timestamp: ts,
          platformJson: { message_id: String(row.message_id), channel: row.channel },
        }, { historical: historical || ts < Date.now() - HOUR });
      }
      newest = Math.max(newest, toMillis(row.updated_at));
    }
    return newest;
  } finally {
    await connection.end();
  }
}
```

If your host requires TLS, add the `ssl` option to `createConnection` ([option reference](https://github.com/mysqljs/mysql#ssl-options)).

The cursor follows `updated_at`, so a row that changes later (a status column, say) is sent again. That's fine: duplicates are removed automatically. An index on `updated_at` keeps the query fast as the table grows.

## 5. Run it

Save the code as `mysql.mjs` in a project set up as in [Set up your project](/api/tracker#set-up-your-project), and run it with the [scheduled sync](/api/scheduled-sync) runner: `import { syncMySQL as sync } from './mysql.mjs';`.

- **History first:** `node run.mjs 2025-01-01` sends every row since that date to `/trackhistorical`. Then start a [backfill](/dimensions/backfill) from the **Backfill** tab.
- **Then every 15 minutes:** the scheduled job sends new rows to `/track`, and they are enriched automatically every day. A message whose `sent_at` is more than an hour old when the job sees it (a late-loaded batch, say) goes to `/trackhistorical`, and needs a backfill.
- **Re-runs are safe:** duplicates are removed automatically.
- **No API rate limit:** the database is the only limit. The query reads all new rows into memory at once. For a very large history, start from a recent date and upload the older rows as a file.

You can also export the rows to a CSV file and [upload it](/data/upload-a-file). Run it through the [file checker](/data/check-a-file) first.

## MySQL reference

- [mysql2: Quickstart](https://sidorares.github.io/node-mysql2/docs)
- [mysql2: API and configuration](https://sidorares.github.io/node-mysql2/docs/api-and-configurations)
- [mysql2: connection options (source)](https://github.com/sidorares/node-mysql2/blob/master/typings/mysql/lib/Connection.d.ts)
- [Connection options reference (mysqljs)](https://github.com/mysqljs/mysql#connection-options)

Checked against the mysql2 documentation on 2026-10-26.