# Move a Socket.IO Chat From SQLite to PostgreSQL

Yes. An agent replaces the sqlite3 store in your clustered Socket.IO server with a pg pool, keeps message ids and reconnect replay intact, tests it, and opens a PR.

[Run this on Rush](https://cloud.getrush.ai/run/migrate-sqlite-to-postgres/socket-io/). Opens the console with this prompt ready to send, in a Project that has your repository.

## What the agent does

The agent reads the whole server first, because the store is tied to how reconnection works. In a chat built like socketio/chat-example, `index.js` forks one worker per CPU with `node:cluster`, each on `PORT` 3000 plus its index, and runs `setupPrimary()` for the `@socket.io/cluster-adapter`. Every worker opens the same `chat.db` file with `sqlite3` through the `sqlite` wrapper and runs `CREATE TABLE IF NOT EXISTS messages (id INTEGER PRIMARY KEY AUTOINCREMENT, client_offset TEXT UNIQUE, content TEXT)`. The `chat message` handler inserts the content with the client's offset, treats `e.errno === 19`, SQLite's constraint error, as a duplicate and acknowledges it, and otherwise broadcasts the message with `result.lastID` as the server offset. On a connection that was not recovered, it replays `SELECT id, content FROM messages WHERE id > ?` from `socket.handshake.auth.serverOffset`. In `index.html` the client sends `${socket.id}-${counter++}` as its offset, retries with `ackTimeout: 10000` and `retries: 3`, and stores each `serverOffset` it receives.

Those three facts fix the design: ids must stay monotonic because clients hold them as offsets, the duplicate check must survive the change because the client retries, and each worker needs its own connection because each is its own process. The agent replaces `sqlite` and `sqlite3` in `package.json` with `pg`, creates one `Pool` per worker from `DATABASE_URL`, and writes the table as `id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, client_offset TEXT UNIQUE, content TEXT NOT NULL`. The insert becomes `INSERT ... VALUES ($1, $2) RETURNING id`, the duplicate check becomes `err.code === '23505'`, PostgreSQL's unique violation, and the replay adds `ORDER BY id`, which SQLite's rowid scan gave for free and PostgreSQL does not promise.

Existing messages matter because every connected browser holds an offset into them. The agent adds `scripts/import-sqlite.js`, which reads `chat.db` with the old driver and inserts each row with `OVERRIDING SYSTEM VALUE` so ids are preserved, then sets the identity sequence past the highest id. A fresh database with ids restarting at 1 would make a client with `serverOffset: 4000` miss everything until the counter caught up, which is the bug the script prevents. A `.env.example` carries `DATABASE_URL`, the README gets the two commands, and `connectionStateRecovery` and the cluster adapter are left as they are.

The repository has no tests, so the agent writes `test/replay.test.js` with `node:test` and `socket.io-client`: it starts one worker against a PostgreSQL it brings up in its sandbox, sends a message from one client, connects a second with `serverOffset: 0` and asserts the replay, resends the first message with the same client offset and asserts a single row. It runs the test, runs the import script against a copied `chat.db`, and starts the clustered server to see two workers broadcast to each other through the adapter.

## Start it with one API call

`POST https://api.phoenix.so/o/{org}/sessions`

```json
{
  "agent": "claude",
  "input": "Move this Socket.IO chat server from SQLite to PostgreSQL. Read package.json, index.js, index.html and README.md first. Replace sqlite and sqlite3 with pg, open one Pool per cluster worker from DATABASE_URL, create the messages table with a BIGINT identity id, a unique client_offset and a non-null content, use INSERT ... RETURNING id, treat error code 23505 as the duplicate the acknowledgement already handles, and add ORDER BY id to the replay query. Keep connectionStateRecovery and the cluster adapter unchanged. Add scripts/import-sqlite.js that copies chat.db rows into PostgreSQL with their ids preserved using OVERRIDING SYSTEM VALUE and advances the identity sequence, a .env.example with DATABASE_URL, and README instructions. Write a node:test test with socket.io-client that proves replay from serverOffset 0 and deduplication of a retried message against a real PostgreSQL, run it, run the import script against a copy of chat.db, and open a pull request quoting the test output.",
  "resources": [
    {
      "url": "https://github.com/your-org/your-app"
    }
  ]
}
```

1. **Get an API key.** In the console, open Settings, then API keys, and create a key for your org. Send it in the Authorization header as a Bearer token.
2. **Send the request.** POST the body in request.json to https://api.phoenix.so/o/{org}/sessions, with your repository as the resources URL. The run starts in its own cloud sandbox.
3. **Read what came back.** Follow the run on its live stream or wait for the run.completed webhook, then read the result and its trajectory.

## What comes back

What comes back is a pull request that changes `index.js` and `package.json`, adds the import script, the test, `.env.example` and a `test` script, and extends the README. The description quotes the `node --test` output and the import script's count of rows copied and the sequence value it set, and shows the before and after of the three queries that changed.

The run's record keeps the test run, the import run and the clustered server's startup lines, so a reviewer can see two workers broadcasting one message rather than take the adapter's behaviour on trust.

Before merging, a reviewer settles the pool size per worker, since a machine with many CPUs now opens many pools against one database, and whether `DATABASE_URL` should fall back to anything in development. The agent leaves the fallback out so a missing variable fails at startup instead of writing to a surprise database.

## Run it on every chat service still on a SQLite file

A team with several small realtime services starts one run per repository with one `POST /o/{org}/sessions` each, this prompt as `input` and the repository in `resources`. Each run reads its own handler and schema, so a service whose table has a `room` column or a different duplicate check gets the PostgreSQL version of what it actually does, not of this example.

Send a `request_id` that is a UUID v5 of the repository's full name, so a retried call returns the run it already started. The key's `webhook_url` reports when each run the key starts finishes, with `session_url` to read the pull request from and the run's final answer in `result`.

## Why run it on Rush?

The migration is small in lines and large in consequences: an id sequence that restarts, a replay query without an order, a duplicate check that still looks for SQLite's error number, and every browser that reconnects after the deploy sees the wrong history. Each of those is caught only by running the server against a real PostgreSQL with real clients, which the agent does in its own sandbox before anyone deploys.

The pull request carries the test, the import script's output and the server's startup lines, so the reviewer reads evidence rather than a description of what should happen. Across several services the job runs in parallel, one sandbox and one pull request each.

## Frequently asked questions

### Why one pool per worker rather than one shared connection?

Because each cluster worker is a separate Node.js process; they share nothing but the primary's adapter channel. Each worker opened its own SQLite handle before, and each opens its own `Pool` now. The pool's size is the setting to watch, since it multiplies by the number of CPUs.

### Does reconnection still work during the switch?

Yes, provided ids are preserved. A client's `serverOffset` is the id of the last message it saw, and `connectionStateRecovery` handles short gaps on its own. The import script keeps every id and advances the sequence, so a client that reconnects after the deploy replays from where it left off.

### What happens to the retry when the insert fails for another reason?

The same as before: the handler returns without acknowledging, and the client's `retries: 3` with `ackTimeout: 10000` sends it again. Only the unique violation, now `23505`, is acknowledged as a duplicate.

### Can this use a hosted PostgreSQL?

Yes. `DATABASE_URL` is the only setting, and `pg` reads `sslmode` from it. The test needs a database it can create a table in, which is why the agent runs one in its sandbox rather than pointing the test at yours.

## More Socket.IO jobs

- [Write Integration Tests for a Socket.IO Chat Server](https://getrush.ai/run/write-integration-tests/socket-io/)
- [Add the Admin UI to a Socket.IO Server](https://getrush.ai/run/add-admin-ui/socket-io/)
- [Add CORS to a Socket.IO Server](https://getrush.ai/run/add-cors/socket-io/)

## The same job on other stacks

- [Migrate a Strapi App From SQLite to PostgreSQL](https://getrush.ai/run/migrate-sqlite-to-postgres/strapi/)
- [Migrate a Drizzle Schema from SQLite to PostgreSQL](https://getrush.ai/run/migrate-sqlite-to-postgres/drizzle/)
- [Migrate an Echo and GORM API From SQLite to Postgres](https://getrush.ai/run/migrate-sqlite-to-postgres/echo/)

## Keep reading

- [How a Rush run works on your repository, from request to pull request](https://getrush.ai/cloud/)
- [Compare cloud agents, agent sandboxes and AI code review](https://getrush.ai/compare/)
- [Rush pricing: Free, Pro and Max](https://getrush.ai/pricing/)
