# Add Optimistic Locking to a Sequelize App

Yes. An agent turns on Sequelize's version column for your models, makes PUT handlers answer 409 on a stale write, tests the conflict, and opens a PR.

[Run this on Rush](https://cloud.getrush.ai/run/add-optimistic-locking/sequelize/). Opens the console with this prompt ready to send, in a Project that has your repository.

## What the agent does

The agent reads how updates reach the database before it adds a lock to them. In an app laid out like the `express-main-example` in sequelize/express-example, `sequelize/index.js` opens a SQLite file at `sqlite-example-database/example-db.sqlite` with `logQueryParameters` and `benchmark` on, defines three models from `sequelize/models/user.model.js`, `instrument.model.js` and `orchestra.model.js`, and applies the `orchestra.hasMany(instrument)` association in `extra-setup.js`. `express/app.js` mounts each route module under `/api/<name>` with a loop that wires `getAll`, `getById`, `create`, `update` and `remove`, wrapped in `makeHandlerAwareOfAsyncErrors`. Each `update` is the same shape: `getIdParam` from `express/helpers.js`, a check that `req.body.id` equals the path id, then `models.<name>.update(req.body, { where: { id } })` and a bare `200`. `package.json` runs `sequelize ^6.3.3` and `sqlite3 ^5.0.0` with `start` and `setup-example-db` scripts, no test script and, through `.npmrc`, no lockfile.

Sequelize 6 has the lock built in. Setting `version: true` in a model's options adds an integer `version` column, and an instance's `save()` writes `WHERE id = ? AND version = ?` with the version incremented, then throws `OptimisticLockError` when no row matched. The static `Model.update(values, { where })` the routes use today does not check the version at all, which the agent confirms by reading `lib/model.js` in `node_modules`, so the change is in two places: the three `sequelize.define` calls gain `version: true`, and each route's `update` becomes `findByPk`, a `404` when the row is gone, a `400` when `req.body.version` is missing or not an integer, `instance.set` of the body without `id` and `version`, and `await instance.save()` inside a `try` that maps `OptimisticLockError` to `409` with a body naming the current version. The response on success is the updated row as JSON rather than an empty `200`, so a client gets the new version to send next time.

Reads need no change: `findAll` and `findByPk` return every column, so `GET /api/users/1` now carries `version` and the `<h2>` page in `app.js` keeps pointing at the same routes. `sqlite-example-database/setup.js` runs `sequelize.sync({ force: true })`, so `npm run setup-example-db` recreates the tables with the new column and seeds them as before; the agent documents that an existing `example-db.sqlite` needs that command once. The comment in `sequelize/index.js` already says the connection should come from the environment, so the agent reads an optional `DB_STORAGE` variable there, defaulting to the file, which is what lets the tests use `:memory:`.

There is no test runner, so the agent adds a small one that needs no new dependency: `tests/optimistic-locking.test.js` with `node --test`, started by a `test` script, which raises `engines.node` from `>=10` to `>=18` and says so in the pull request. The test sets `DB_STORAGE=:memory:`, syncs the models, seeds one user, starts `app` on port 0 with `listen`, reads the user with `fetch` to get `version` 0, sends the same `PUT /api/users/1` body twice with `version: 0`, and asserts the first answers `200` with `version: 1` and the second `409`; a second case sends a body with no `version` and expects `400`. The agent runs `npm install`, `npm run setup-example-db` and `npm test` in its sandbox and keeps the output.

## Start it with one API call

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

```json
{
  "agent": "claude",
  "input": "Add optimistic locking to this Express and Sequelize app. Read package.json, index.js, express/app.js, express/helpers.js, every file under express/routes and sequelize, and sqlite-example-database/setup.js first, and confirm in node_modules/sequelize/lib/model.js that the static update does not check a version. Set version: true on the user, instrument and orchestra models. Change each route's update to load the row with findByPk, answer 404 when it is missing and 400 when req.body.version is not an integer, set the body without id and version, save the instance, map OptimisticLockError to 409 with the current version in the response, and return the updated row on success. Read an optional DB_STORAGE environment variable in sequelize/index.js, defaulting to the current file. Add a node --test suite that runs against DB_STORAGE=:memory:, sends the same PUT twice with the same version and asserts 200 then 409, plus a test script and an engines bump to Node 18. Run npm install, npm run setup-example-db and npm test and quote the output. Open a pull request.",
  "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

The pull request changes the three model files (one option each), `express/routes/users.js`, `instruments.js` and `orchestras.js` (the new `update`), `sequelize/index.js` (the `DB_STORAGE` read), `package.json` (the `test` script and `engines`) and `readme.md` (the `version` field in the `PUT` body format and the re-seed note), and adds `tests/optimistic-locking.test.js`. Its description quotes the `npm test` output and shows the `409` body a client sees on a stale write.

A reviewer runs `npm run setup-example-db`, opens two terminals, reads a user in both, updates it in one and then the other, and sees the second answer `409`. In the code they check that `instance.set` cannot be used to move `id` or `version`, and that the `404` and `400` paths return before any write. They also decide whether a `409` should carry the whole current row or only its version, which the pull request keeps to the version.

`GET`, `POST` and `DELETE` keep their responses apart from the new `version` field on every row. A client that never sends `version` on `PUT` now gets `400`, which is the one visible break and the reason the readme changes.

## Run it on every Linear issue labelled concurrency

A team that tracks lost-update bugs in Linear starts one run per issue from a handler on its own server: when an issue gains the `concurrency` label, the handler sends this body to `POST /o/{org}/sessions` with the repository in `resources` and the issue's `https://linear.app/<workspace>/issue/<ID>` URL as a second resource, with a `request_id` that is a UUID v5 of the issue id so a redelivery returns the run already started. The prompt tells the agent which model the issue is about, and to leave the others alone.

A Rush automation with a `linear` trigger on `linear.issue.labeled` can watch the label too, but its runs are read-only: they read the repository and comment, and do not push. That fits a plan step that lists the routes and models the change would touch on the issue, with the handler above starting the run that opens the pull request.

## Why run it on Rush?

A lost update is invisible in a diff and in a single-user test. The agent proves the lock by running the app in its own sandbox and sending the conflicting write itself, so the first evidence a reviewer sees is a `409` in the test output, kept with the pull request, rather than a sentence saying the option was set.

The change is the same shape in every Sequelize service a team runs, and different in every detail: which models, which routes, which seed script. One request per repository, started at once, gives each one a pull request written against its own `define` calls and handlers.

## Frequently asked questions

### Why not keep Model.update with a version in the where clause?

It would work, but every handler would have to repeat the version check and the increment by hand. Sequelize already does both in `save()` when `version: true` is set, and throws `OptimisticLockError` when the row moved, so the instance path is shorter and the behavior is the library's rather than the app's.

### Does the existing SQLite file need a migration?

The example has no migration tool; `sqlite-example-database/setup.js` recreates the schema with `sync({ force: true })`. Running `npm run setup-example-db` once adds the `version` column and reseeds. The pull request says so, and notes that an app with migrations would add the integer column with a default of 0 instead.

### What does a client have to change?

Send the `version` it last read in every `PUT` body, and on `409` read the row again before retrying. `GET` responses already include the field once the option is on, so no new endpoint is needed.

### Why does the pull request raise the Node engine?

Because `node --test` and the global `fetch` the test uses arrived in Node 18, and the repository had no test runner to extend. The agent names the bump in the description so a reviewer can swap in another runner if they would rather keep `>=10`.

## More Sequelize jobs

- [Add Pagination to a Sequelize Express API](https://getrush.ai/run/add-pagination/sequelize/)
- [Add Prometheus Metrics to an Express and Sequelize API](https://getrush.ai/run/add-prometheus-metrics/sequelize/)
- [Add Slow Query Logging to a Sequelize App](https://getrush.ai/run/add-slow-query-logging/sequelize/)

## The same job on other stacks

- [Add Optimistic Locking to SQLModel Updates](https://getrush.ai/run/add-optimistic-locking/sqlmodel/)
- [Add Optimistic Locking to TypeORM Entities](https://getrush.ai/run/add-optimistic-locking/typeorm/)
- [Add Optimistic Locking to a GORM Model in a Gin API](https://getrush.ai/run/add-optimistic-locking/gorm/)

## 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/)
