All jobs

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 RushOpens 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 with this body:

{
  "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.

The use case this job follows, with its automation and trigger: Hand off a long task.

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

The same job on other stacks

Keep reading