# Add Database Indexes to a Sequelize App

Yes. An agent reads the queries your routes send through Sequelize, adds the indexes they need, proves it with EXPLAIN QUERY PLAN in a test and opens a PR.

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

## What the agent does

The agent starts from the queries, not from the columns, because an index nobody searches by only slows down writes. In an app like the `express-main-example` in sequelize/express-example, `sequelize/index.js` builds one `Sequelize` instance on `dialect: 'sqlite'` with `storage: 'sqlite-example-database/example-db.sqlite'`, `logQueryParameters: true` and `benchmark: true`, so every query is already printed with its parameters and its timing. Three models are defined from `sequelize/models/`: `user` with an `id` and a `username` that is `unique: true` and validated against `/^\w{3,}$/`, `instrument` with `type` and `purchaseDate`, and `orchestra` with `name`. `sequelize/extra-setup.js` then declares `orchestra.hasMany(instrument)` and `instrument.belongsTo(orchestra)`, which is what adds the `orchestraId` column to `instruments`. `sqlite-example-database/setup.js` rebuilds the file with `sequelize.sync({ force: true })` and seeds four users, three orchestras and ten random instruments per orchestra.

The routes tell the agent which access paths matter. `express/app.js` wires `getAll`, `getById`, `create`, `update` and `remove` from each file in `express/routes/` onto `/api/<name>` and `/api/<name>/:id`, all through `findAll`, `findByPk`, `create`, `update` and `destroy` by primary key. The one query that is not a primary-key lookup is in `express/routes/orchestras.js`: when `includeInstruments` is in the query string, `getAll` and `getById` run `findAll({ include: models.instrument })` and `findByPk(id, { include: models.instrument })`, which Sequelize turns into a `LEFT OUTER JOIN` from `orchestras` to `instruments` on `instruments.orchestraId`. Sequelize creates no index for a foreign key it adds through an association, and SQLite makes none either, so that join scans the whole `instruments` table for every orchestra row. `username` needs nothing: its `unique: true` already gives SQLite an automatic unique index.

The change is small and lives in the model. The agent adds an `indexes` option to the `instrument` definition in `sequelize/models/instrument.model.js` with one entry, `{ name: 'instruments_orchestra_id', fields: ['orchestraId'] }`, and a comment explaining that the column is added by the association in `extra-setup.js` and exists by the time `sync` creates indexes. It deliberately adds nothing on `type` or `purchaseDate`, because no route filters or sorts by them, and says so in the pull request so a reviewer who knows of a report query can ask for one. For an app that has moved off `sync` to `sequelize-cli` migrations, the same index goes in a migration through `queryInterface.addIndex` instead, and the agent writes that form when it finds a `migrations/` directory.

The proof is a query plan. The repository has no test runner, lint or CI, so the agent adds `test/indexes.test.js` on `node:test`, a `test` script that runs `node --test`, and raises `engines.node` from `>=10` to `>=18`, the first version with that runner, noting in the pull request that Node 10 has been unsupported for years. The test calls `sequelize.sync({ force: true })` on a temporary SQLite file, inserts one orchestra with a few instruments, runs `EXPLAIN QUERY PLAN` through `sequelize.query` for the exact SQL the include produces, and asserts the plan line for `instruments` says `SEARCH` with `USING INDEX instruments_orchestra_id` rather than `SCAN`. It also asserts `PRAGMA index_list('instruments')` names the new index. The agent runs `npm run setup-example-db`, `npm start` and `curl 'localhost:8080/api/orchestras?includeInstruments'` in its sandbox to show the route still returns the nested instruments, with the `benchmark` timing line from the Sequelize log before and after.

## Start it with one API call

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

```json
{
  "agent": "claude",
  "input": "Add the database indexes this Sequelize app needs. Read package.json, index.js, express/app.js, every file in express/routes, sequelize/index.js, sequelize/extra-setup.js, every model in sequelize/models and sqlite-example-database/setup.js first. List every query the routes send and which column each one searches or joins on, then add an index only where a query needs one and explain each column you leave alone. Put the indexes in the model definitions since the app uses sync, or in a migration if the app has a migrations directory. Add a node:test test that syncs a temporary SQLite database, runs EXPLAIN QUERY PLAN for the include query and asserts it uses the new index, add a test script, and raise engines.node to the version node:test needs. Run the test, the setup script and the server, curl the orchestras route with includeInstruments, and open a pull request with the 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

The pull request changes `sequelize/models/instrument.model.js` and `package.json`, and adds `test/indexes.test.js`. Its description lists the queries the routes send with the column each one uses, shows the `EXPLAIN QUERY PLAN` rows for the include query before and after, and pastes the `node --test` output and the `curl` response.

A reviewer sees one index with a stated reason, an explicit list of columns that were not indexed and why, and a test that fails if the index is ever removed, since the plan would go back to `SCAN instruments`. The run's record keeps the Sequelize log with `benchmark` timings for both runs of the orchestras route.

Before merging, a reviewer confirms the production database is created through `sync` as the example is, or asks for the migration form if it is not, and decides whether a future report endpoint justifies an index on `purchaseDate`. The routes, the models' fields and the seed script are unchanged.

## Run it on every Sequelize service whose joins are scanning

A team with several Express services on Sequelize starts one run per repository from its own server: a `POST /o/{org}/sessions` each, with the repository in `resources` and a `request_id` that is a UUID v5 of the repository's full name, so a retried call returns the run already started instead of opening a second pull request. Each run reads its own routes, so a service with `where` clauses on a status column gets an index there and a plan to prove it, and one on Postgres gets `EXPLAIN (ANALYZE)` instead of SQLite's `EXPLAIN QUERY PLAN`.

Afterwards, an automation on the Project with a `github` trigger on `pull_request.opened` and the prompt "if this pull request adds a findAll, findOne or include with a where clause on a column that has no index, say which column and propose the index entry; otherwise say nothing" catches the next scan before it ships. The trigger adds the pull request after the prompt, and only work by an owner, member or collaborator fires it.

## Why run it on Rush?

The right index is the one the query planner actually uses, and the only way to know is to ask it. The agent reads the route code to find the real queries, runs `EXPLAIN QUERY PLAN` against a database it builds in its sandbox, and turns the answer into a test, so the pull request carries a plan rather than a guess about which columns look important.

Every command stays with the run, from the seed script to the `curl` with its timing line, and the pull request quotes them. A handful of Sequelize services can be checked at once, each in its own sandbox, each with its own plan and its own pull request.

## Frequently asked questions

### Why only one index?

Because only one query in the routes searches by something other than a primary key: the `include` of instruments on orchestras, which joins on `orchestraId`. `username` is already covered by its `unique: true` constraint, and nothing filters by `type` or `purchaseDate`. The agent lists each column it left alone in the pull request so a reviewer can add a case it does not see in the code.

### Does sync create the index on an existing database?

`sequelize.sync()` without `force` or `alter` creates tables that are missing and adds indexes declared in the model that do not exist yet, so a deployment that starts the app on an existing file gets the index on the next start. The example's seed script uses `force: true`, which rebuilds everything. For a database managed by migrations, the pull request uses `queryInterface.addIndex` instead.

### Why does the test use a temporary SQLite file?

So it never touches `sqlite-example-database/example-db.sqlite` and runs anywhere with no service to start. The test opens its own `Sequelize` instance on a file under the system temp directory, syncs the same models and association, and deletes the file afterwards.

### Why change the Node version?

The repository has no test runner, and `node:test` is the one that needs no new dependency; it arrived in Node 18. `engines.node` goes from `>=10` to `>=18`, and the pull request says so. A team that prefers Jest or Mocha gets the same assertion in that runner by saying so in the prompt.

## More Sequelize jobs

- [Move a Sequelize App From sync() to Migrations](https://getrush.ai/run/add-database-migrations/sequelize/)
- [Add Dependabot to a Sequelize App](https://getrush.ai/run/add-dependabot/sequelize/)
- [Add a Dockerfile to a Sequelize App](https://getrush.ai/run/add-dockerfile/sequelize/)

## The same job on other stacks

- [Add Database Indexes to a SQLModel App With Alembic](https://getrush.ai/run/add-database-indexes/sqlmodel/)
- [Add Database Indexes to a sqlx Project](https://getrush.ai/run/add-database-indexes/sqlx/)
- [Add Missing Indexes to a Supabase Database](https://getrush.ai/run/add-database-indexes/supabase/)

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