How to Persist WebSocket Messages with MySQL in a PHP Webman App

Message Persistence Overview

WebSocket servers can store incoming messages in a relational database so that the data remains available after a client disconnects.

Requirement Analysis

The UI mimics a typical chat list: each row shows username, avatar, last message content, and timestamp. Clicking a row opens the chat view; a new session is created for first‑time chats, otherwise recent history is loaded.

Database Design

User table (tinywan_user)

CREATE TABLE `user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增',
  `username` varchar(32) NOT NULL COMMENT '用户名',
  `nickname` varchar(32) NOT NULL COMMENT '用户昵称',
  `avatar` varchar(200) NOT NULL COMMENT '头像',
  `create_time` int(11) unsigned NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

Session table (tinywan_message_session)

CREATE TABLE `tinywan_message_session` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自动递增',
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户ID（存储个人登录IM后的用户ID）',
  `to_user_id` bigint(20) unsigned NOT NULL COMMENT '对方用户id',
  `name` varchar(32) NOT NULL COMMENT '会话列表名称',
  `type` tinyint(4) unsigned NOT NULL DEFAULT '1' COMMENT '消息类型（1：群对话，2：个人对话）',
  `group_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '群组id',
  `avatar` varchar(255) NOT NULL DEFAULT '' COMMENT '头像',
  `create_time` int(11) NOT NULL DEFAULT '0' COMMENT '创建时间',
  `update_time` int(11) NOT NULL DEFAULT '0' COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='消息会话表';

Message table (tinywan_message)

CREATE TABLE `tinywan_message` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自动递增',
  `from_user_id` int(11) unsigned NOT NULL COMMENT '来自用户id',
  `from_username` varchar(64) NOT NULL COMMENT '来自用户昵称',
  `from_avatar` varchar(128) NOT NULL COMMENT '来自用户头像',
  `to_user_id` int(11) unsigned NOT NULL COMMENT '接受用户id',
  `group_id` bigint(20) unsigned NOT NULL COMMENT '群组ID',
  `type` tinyint(4) unsigned NOT NULL DEFAULT '1' COMMENT '消息类型（1：文字，2：图片，3：文件，4：日程）',
  `mode` tinyint(4) unsigned NOT NULL DEFAULT '1' COMMENT '消息模式（1：单聊，2：群聊）',
  `content` text NOT NULL COMMENT '内容',
  `create_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
  `session_id` int(11) NOT NULL COMMENT '会话id',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_group_id` (`group_id`) USING BTREE,
  KEY `idx_from_user_id` (`from_user_id`) USING BTREE,
  KEY `idx_to_user_id` (`to_user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='消息表';

ORM Integration

The project uses webman/think-orm, a wrapper that installs the official topthink/think-orm component, generates config/thinkorm.php, and registers a periodic SELECT 1 timer to keep the MySQL connection alive.

Composer Installation

composer require -W webman/think-orm

Model Definition

class MessageModel extends BaseModel {
    protected $table = 'tinywan_message';
}

Message Event Handler

public static function onMessage(string $clientId, string $message): bool {
    try {
        $originMessage = json_decode($message, true);
        if (json_last_error() !== JSON_ERROR_NONE) {
            Gateway::closeClient($clientId, broadcast_json(400, 'Invalid JSON'));
            return false;
        }
        if (!is_array($originMessage)) {
            Gateway::closeClient($clientId, broadcast_json(400, 'Unparseable data'));
            return false;
        }
        $validate = new MessageFormatValidate();
        if (false === $validate->check($originMessage)) {
            Gateway::closeClient($clientId, broadcast_json(400, $validate->getError()));
            return false;
        }
        $groupId = $originMessage['group_id'] ?? 0;
        switch ($originMessage['event']) {
            case 'join':
                if ($originMessage['mode'] === 2) {
                    $_SESSION['group_id'] = $groupId;
                    Gateway::joinGroup($clientId, $groupId);
                } else {
                    Gateway::bindUid($clientId, $originMessage['from_user_id']);
                }
                $_SESSION['mode'] = $originMessage['mode'];
                $_SESSION['event'] = $originMessage['event'];
                $_SESSION['group_id'] = $groupId;
                $_SESSION['from_user_id'] = $originMessage['from_user_id'];
                $_SESSION['from_username'] = $originMessage['from_username'];
                Gateway::sendToCurrentClient(broadcast_json(0, 'success', $originMessage));
                break;
            case 'speak':
                MessageModel::create([
                    'from_user_id'   => $originMessage['from_user_id'],
                    'from_username' => $originMessage['from_username'],
                    'from_avatar'    => $originMessage['from_avatar'] ?? '',
                    'to_user_id'     => $originMessage['to_user_id'],
                    'group_id'       => $groupId,
                    'type'           => 1,
                    'mode'           => $originMessage['mode'],
                    'content'        => $originMessage['content'],
                ]);
                if ($originMessage['mode'] == 1) {
                    $msg = $originMessage['from_username'] . '[单聊对]' . $originMessage['to_user_id'] . '[说]：' . $originMessage['content'];
                    Gateway::sendToUid($originMessage['to_user_id'], broadcast_json(0, $msg, $originMessage));
                } else {
                    $msg = $originMessage['from_username'] . '[群聊说]：' . $originMessage['content'];
                    Gateway::sendToGroup($groupId, broadcast_json(0, $msg, $originMessage));
                }
                break;
            default:
                Gateway::sendToCurrentClient(broadcast_json(400, 'Invalid event', $originMessage));
        }
    } catch (\Throwable $e) {
        return Gateway::sendToClient($clientId, broadcast_json(500, $e->getMessage()));
    }
    return true;
}

Persistence Test (JavaScript client)

let $_content = {
  "event": "speak",
  "mode": 2,
  "group_id": 100,
  "from_user_id": "10000",
  "from_username": "UserA",
  "to_user_id": "10086",
  "content": "WebSocket tutorial: message persistence"
};
ws.send(JSON.stringify($_content));
ws.onmessage = function(evt) {
  console.log("[UserA] received: " + evt.data);
};

Sample Query

SELECT from_user_id, from_username, to_user_id, group_id, mode, content,
       FROM_UNIXTIME(create_time) AS date_time
FROM tinywan_message;

Performance Considerations

Persisting WebSocket messages to MySQL can affect throughput under high concurrency. Recommended mitigations include using a connection pool, adding appropriate indexes (as defined in the schema), and periodically cleaning or archiving old records to reduce table size.

Source Repository

https://github.com/Tinywan/webman-admin