<!-- llms.txt: https://workos.com/llms.txt -->
<!-- feedback.md: https://workos.com/docs/feedback.md -->

# MySQL

## Before getting started

The MySQL destination uses the same backfill, events API tail, and reconciliation as the [sync engine](https://workos.com/docs/events/data-syncing/database). You'll need:

- A [WorkOS account](https://dashboard.workos.com/) and an API key for the environment you want to mirror
- A MySQL database meeting the [package's compatibility requirements](https://github.com/workos/sync-engine/blob/main/packages/sync-mysql/README.md); MariaDB is not supported
- A runtime supported by the MySQL package, or a container runtime

The database user needs `CREATE`, `ALTER`, `INDEX`, `CREATE VIEW`, `CREATE TEMPORARY TABLES`, `SELECT`, `INSERT`, `UPDATE`, and `DELETE` on the mirror database. Create the database beforehand, or grant the user permission to create it.

## (1) Install and configure

```bash title="Install the MySQL destination"
npm install @workos/sync-mysql
```

The package includes the engine, a MySQL destination, and the `workos-sync-mysql` command. See the [MySQL package README](https://github.com/workos/sync-engine/blob/main/packages/sync-mysql/README.md) for the full command and configuration reference. For Docker images, tags, and usage, follow the [repository's container instructions](https://github.com/workos/sync-engine#against-a-real-environment).

Set the connection string and API key as managed secrets:

```bash title="Environment variables"
export DATABASE_URL='mysql://USER:PASSWORD@HOST:3306/workos'
export WORKOS_API_KEY='sk_example_123456789'
export WORKOS_SYNC_SCHEMA='workos'
```

`WORKOS_SYNC_SCHEMA` selects the database for all mirror tables and defaults to `workos`. The database in the `DATABASE_URL` path does not select the mirror database. On a host that provides a database for you, set `WORKOS_SYNC_SCHEMA` to that database's name.

For a remote database, set `WORKOS_SYNC_MYSQL_SSL_CA` to the provider's CA certificate, as PEM or base64-encoded PEM, to require TLS and verify the server certificate. The library equivalent is the `sslCa` destination option.

Rows are labeled `production` in their `_env` column by default. For another environment, set `WORKOS_ENVIRONMENT_NAME` before the first write. This label is part of every primary key and must not change once you have data.

## (2) Create the schema and backfill

```bash title="Create the mirror and load existing data"
npx workos-sync-mysql init
npx workos-sync-mysql backfill
```

`init` creates the database if needed, the mirror tables, their `_live` views, and the bookkeeping tables. It is safe to run repeatedly. Backfill loads current objects from the REST list endpoints and records a cursor so changes during the initial load are not missed.

If backfill is interrupted, resume from its last checkpoint:

```bash title="Resume backfill"
npx workos-sync-mysql backfill --resume
```

For hosts that gate schema changes, use `init --print-sql` and apply the DDL through the host's change process before running `init`. See the [MySQL command line guide](https://github.com/workos/sync-engine/blob/main/packages/sync-mysql/README.md#command-line) for the hosted-database workflow.

## (3) Start syncing

```bash title="Tail the events API"
npx workos-sync-mysql listen
```

Leave this running as a service. It polls the events API from the stored cursor, applies changes, and finishes its current batch on `SIGINT` or `SIGTERM`. Only one listener writes per environment; another waits for the writer lease. For scheduled polling instead, `npx workos-sync-mysql tick` polls once and exits.

## (4) Query the mirror

Read `_live` views to exclude deleted objects. Join on `_env` as well as the object id, because a database can mirror several environments.

```sql title="Members and roles by organization"
SELECT o.name AS organization, u.email, m.role_slug
  FROM workos.organization_memberships_live m
  JOIN workos.users_live u ON u.id = m.user_id AND u._env = m._env
  JOIN workos.organizations_live o ON o.id = m.organization_id AND o._env = m._env
  ORDER BY o.name, u.email;
```

### The MySQL schema

The tables and `_live` views follow the same conventions as Postgres, with MySQL-specific types:

- `_raw` is `JSON`. Fields without a generated column are queryable with MySQL JSON paths, such as `_raw->>'$.custom_attributes.department'`.
- Key columns are `VARCHAR(191)`, other text columns are `TEXT`, and boolean values are `TINYINT(1)` (`1` or `0`).
- Timestamps are `DATETIME(6)` in UTC. Use `UTC_TIMESTAMP(6)` for time comparisons, or set your session to UTC.
- Invalid values in generated projections become SQL `NULL`, while the original value remains in `_raw`. Invalid key values are rejected and quarantined rather than silently changing a row's identity.

The mirror is an eventually consistent read model. Use it for joins, lists, and analytics, not authorization decisions or reading back an object you just created. Send writes to the WorkOS API.

## (5) Reconcile and monitor

```bash title="Check for drift"
npx workos-sync-mysql reconcile
```

The command reports missed creates and deletes and exits with status `2` when it finds drift. To insert missing objects and tombstone missed deletes, run `npx workos-sync-mysql reconcile --repair`.

Check `npx workos-sync-mysql status` for the cursor, poll age, and per-stream counts. The [operations guide](https://workos.com/docs/events/data-syncing/database/operations) covers monitoring, reconciliation schedules, and recovery; replace `workos-sync-postgres` with `workos-sync-mysql` in its commands.

## Embed it in your app

```ts title="Embed the MySQL destination"
import { SyncEngine, MysqlDestination } from '@workos/sync-mysql';

const engine = new SyncEngine({
  destination: new MysqlDestination({
    databaseUrl: process.env.DATABASE_URL,
  }),
  environments: [{ name: 'production', apiKey: process.env.WORKOS_API_KEY! }],
});

await engine.backfill();
await engine.listen(); // resolves when you call engine.stop()
```

See the [library guide](https://github.com/workos/sync-engine/blob/main/packages/sync-mysql/README.md#embed-it) to reuse a `mysql2` pool, configure TLS, or customize the destination.
