chat-state-mysql

Community MySQL state adapter for Chat SDK built with mysql2. Use this when MySQL is your primary datastore and you want state persistence without a separate Redis dependency.

Installation

pnpm add chat chat-state-mysql

Usage

createMySqlState() auto-detects MYSQL_URL (or DATABASE_URL) so you can call it with no arguments:

import { Chat } from "chat";
import { createMySqlState } from "chat-state-mysql";

const bot = new Chat({
  userName: "mybot",
  adapters: { /* ... */ },
  state: createMySqlState(),
});

To provide a URL explicitly:

const state = createMySqlState({
  url: "mysql://root:root@localhost:3306/chat",
});

Using an existing client

import mysql from "mysql2/promise";

const client = mysql.createPool(process.env.MYSQL_URL!);
const state = createMySqlState({ client });

Configuration

| Option | Required | Description | |--------|----------|-------------| | url | No* | MySQL connection URL | | client | No | Existing mysql2/promise Pool instance | | keyPrefix | No | Prefix for all state rows (default: "chat-sdk") | | logger | No | Logger instance (defaults to ConsoleLogger("info").child("mysql")) |

*Either url, MYSQL_URL/DATABASE_URL, or client is required.

Environment variables

MYSQL_URL=mysql://root:root@localhost:3306/chat

DATABASE_URL is also supported as a fallback.

Data model

The adapter creates these tables automatically on connect():

chat_state_subscriptions
chat_state_locks
chat_state_cache
chat_state_lists
chat_state_queues

All rows are namespaced by key_prefix. Prefixes, thread IDs, and cache keys are stored as text, with SHA-256 hash columns used for MySQL primary keys and indexes so long platform IDs and long or multibyte prefixes remain supported.

The schema avoids window functions and generated columns, and is compatible with MySQL 5.7 and newer.

Features

| Feature | Supported | |---------|-----------| | Persistence | Yes | | Multi-instance | Yes | | Subscriptions | Yes | | Distributed locking | Yes | | Key-value caching | Yes (with TTL) | | Automatic table creation | Yes | | Key prefix namespacing | Yes |

Locking considerations

The Redis state adapters use atomic SET NX PX for lock acquisition. The MySQL adapter uses InnoDB row-level locking through INSERT ... ON DUPLICATE KEY UPDATE, replacing a lock only when the stored expires_at timestamp has passed. This is safe for typical multi-instance workloads, but Redis remains a better fit for high-contention distributed locking.

Expired row cleanup

Unlike Redis, MySQL does not automatically delete expired rows. The adapter performs opportunistic cleanup: expired locks are overwritten on the next acquireLock() call, expired cache entries are deleted on the next get() call for that key, and expired queue entries are purged during queue operations.

For high-throughput deployments, you may want to run a periodic cleanup job:

DELETE FROM chat_state_locks WHERE expires_at <= CURRENT_TIMESTAMP(3);
DELETE FROM chat_state_cache WHERE expires_at <= CURRENT_TIMESTAMP(3);
DELETE FROM chat_state_lists WHERE expires_at <= CURRENT_TIMESTAMP(3);
DELETE FROM chat_state_queues WHERE expires_at <= CURRENT_TIMESTAMP(3);

License

MIT