# Upgrade Knex 0.20 to Knex 3 in an Express API

Yes. An agent moves Knex, pg and bcrypt forward together, reruns the migrations and seeds on a real Postgres, and opens a PR that says what changed.

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

## What the agent does

An upgrade this old starts with what is pinned. In an API like robmclarty/knex-express-project-sample, `package.json` pins `knex` 0.20.8, `pg` 7.18.1 and `bcrypt` 3.0.8 exactly, `engines` asks for Node 12 or later, and `package-lock.json` is still lockfile version 1. The `db:migrate` and `db:seed` scripts call `npx knex migrate:latest` and `npx knex seed:run`, `knexfile.js` points development at `postgres://localhost:5432/knex-test`, and the `test` script is the string `ha ha, no.`, so there is no suite to lean on. Every query goes through `server/helpers/model-guts.js`, which builds `create`, `find`, `update` and `destroy` for the two models.

The agent tries the locked install on the sandbox's Node before it changes anything, because the result decides the shape of the pull request. On Node 22, `npm ci` stops at `bcrypt` 3.0.8: that release has no prebuilt binary for the Node 22 ABI, and its fallback source build fails. `pg` 7.18.1 installs, but on Node 22 it never completes a connection, so `npx knex migrate:latest` ends in `Knex: Timeout acquiring a connection` without running a migration. The two cannot be fixed one at a time either. Knex 0.20.8 declares `pg@^7.17.0` as an optional peer, so npm refuses `pg` 8 beside it with `ERESOLVE`. The agent therefore moves `knex`, `pg` and `bcrypt` in one step and says why in the description.

Then it reads Knex's `UPGRADING.md` from 0.21 to 3.0 and checks each breaking change against this code, not against a generic list. Version 1.0 makes `RETURNING` always return objects. `model-guts.js` already passes the `selectableProps` array to `.returning()`, so its rows keep their shape, and the agent searches for a single-column `.returning('id')` that would change from bare values to objects. Version 0.95 moves connection strings to WHATWG URL parsing, makes an empty insert throw, and forbids chaining `first` with `pluck`; the development URL has no character that needs encoding, each seed file inserts two rows, and neither method is used. Versions 0.21, 1.0 and 3.0 each raise the minimum Node, ending at 16, so `engines` moves with them. The sqlite3 driver swaps in 1.0 and 2.0 do not apply to a `pg` app.

Proof is a real database. The agent installs PostgreSQL with `sudo apt-get install -y postgresql`, creates a role and the `knex-test` database, and supplies `PGUSER` and `PGPASSWORD` in the environment, because the URL in `knexfile.js` names neither and editing it would change what the next developer runs. It runs `npm run db:migrate`, `npx knex migrate:rollback` and `npm run db:migrate` again to exercise both directions of the two migrations, then `npm run db:seed`. It starts `./server/start.js` and calls the user and project routes with curl, checking each JSON body against the `selectableProps` list of its model. It leaves `express` and `body-parser` alone, since nothing in this upgrade needs them to move.

## Start it with one API call

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

```json
{
  "agent": "claude",
  "input": "Upgrade Knex in this Express API from the pinned 0.20 release to the current major and open a pull request. Read package.json, package-lock.json, knexfile.js, config/database.js, db/migrations, db/seeds, server/helpers/model-guts.js and server/models first. Before changing anything, run npm ci on the sandbox's Node and npx knex migrate:latest against a local PostgreSQL, and record exactly what fails. Install PostgreSQL with apt, create the knex-test database, and pass credentials through PGUSER and PGPASSWORD; do not edit the development connection string. Upgrade knex, and upgrade pg and bcrypt only as far as the install and the connection require, explaining each. Read Knex's UPGRADING.md for every version between the old and the new one and, for each breaking change, state whether this code is affected and where. Raise engines.node to what the new versions require. Then run db:migrate, a rollback, db:migrate again and db:seed, start the server, call each users and projects route, and paste the outputs. Do not fix unrelated bugs; list any you hit under a separate heading with the file and line. Leave express and body-parser alone. If the migrations or the routes fail after the upgrade, stop and report it.",
  "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 `package.json` and `package-lock.json` and, in a repository like this one, no file under `server/` or `db/`. The description opens with a version table for `knex`, `pg` and `bcrypt`, then the reason they travel together: the failed `bcrypt` build, the connection timeout under `pg` 7 and npm's `ERESOLVE` on the peer range, each quoted from the sandbox. Below that is the list of `UPGRADING.md` entries, with one line each on why it does or does not touch this code.

Then the evidence: the output of both migration directions, the seed run, and the status and body of each route it called. It also reports what is broken that the upgrade neither caused nor fixes. In this repository `User.verify` in `server/models/user.js` never returns its query, so `POST /login` answers 500, and the password check it leaves running rejects with nothing to catch it, which stops the server. `auth_controller.js` uses `CONFLICT` without importing it, so registering an existing username answers 500 with `CONFLICT is not defined`. Those sit under their own heading so nobody blames the new versions for them.

A reviewer checks two things the sandbox cannot. The first is real password hashes: a hash written by the old `bcrypt` should be verified under the new one against a copy of real data before deploy. The second is TLS. `pg` 8 made `rejectUnauthorized` default to true when SSL is on, which matters for a production connection even though the local database in the sandbox uses none. The `staging` and `production` blocks in `knexfile.js` are placeholders, so the agent flags the setting and leaves the choice to the team.

## Run it on every Monday morning

Majors like this one are rare, but the minor releases between them are not. An automation in the Project that holds the repository, with a `schedule` trigger whose `cron` is `0 6 * * 1` and a `timezone`, starts one run each Monday with a shorter prompt: read the registry, upgrade `knex`, `pg` and the other dependencies within their current majors, run the migrations and seeds against a fresh local PostgreSQL, and open a pull request, or end with one line when nothing moved. Turning the automation back on after a pause moves the schedule to its next slot; it does not fire the Mondays it missed.

For the next major, a maintainer opens a GitHub issue that names the target version and adds the team's upgrade label. A second trigger on the same automation, `github` on `issues.labeled` with that label in `labels`, starts a run with the issue placed after the prompt, so the prompt tells the agent to take the target version from the issue text that follows it. Both triggers share the automation's max runs per hour.

## Why run it on Rush?

This upgrade cannot be judged by reading a changelog, because its first three failures are not in one. A missing prebuilt binary, a driver that never connects and a peer range that blocks the obvious fix only show up when the install and the migration actually run on a current Node against a real PostgreSQL. In a Rush sandbox the agent can use sudo, installs the database and runs each step, and every command and its output stay in the run's record, so each claim in the description can be traced to the lines that produced it.

It also keeps a laptop out of it. Nobody has to keep a spare PostgreSQL around to find out whether a lockfile from the Knex 0.20 era still works, and a second service on the same stack gets the same request with its own repository in `resources`. A run whose migrations or route checks fail after the upgrade ends `needs_review` with the output quoted, not as a finished upgrade.

## Frequently asked questions

### Do I have to change my queries for Knex 3?

Often not. The change most code feels is in 1.0, where `RETURNING` always yields objects. Code that passes an array of columns, as this repository's models do, already gets objects. A call like `.returning('id')` that expected bare values needs the property read added, and the agent searches for those.

### Why does the pull request bump pg and bcrypt too?

Because the old versions do not work on a current Node and cannot be mixed with the new Knex. Knex 0.20.8 accepts only `pg` 7 as its peer, `pg` 7.18.1 times out connecting on Node 22, and `bcrypt` 3.0.8 has no binary for it. Moving one without the others leaves an install that fails.

### Will my existing migrations be changed or run again in production?

No. Knex records applied migrations in the `knex_migrations` table named in `knexfile.js`, and the upgrade does not touch the files in `db/migrations`. The agent runs them only against an empty database in the sandbox, to prove the new version can still apply them and roll them back.

### What Node version do I need afterwards?

Knex 3 dropped Node versions before 16, and the current `pg` asks for 16 or later as well. The agent raises `engines.node` in `package.json` to match and names the Node it ran on, so CI and the deploy image can be checked against it.

## More Knex.js jobs

- [Write Integration Tests for a Knex and Express API](https://getrush.ai/run/write-integration-tests/knex/)
- [Add Knex Migrations to an Express App](https://getrush.ai/run/add-database-migrations/knex/)

## More jobs like this: Upgrade dependencies weekly

- [Upgrade Retired Lambda Runtimes in an AWS CDK App](https://getrush.ai/run/upgrade-lambda-runtime/aws-cdk/)
- [Upgrade the Lambda Runtime in a Serverless Service](https://getrush.ai/run/upgrade-lambda-runtime/serverless/)
- [Upgrade LangChain in a Streamlit App](https://getrush.ai/run/upgrade-langchain/streamlit/)
- [Upgrade marshmallow 2 to marshmallow 3 (strict, .data and Hook Signatures)](https://getrush.ai/run/upgrade-marshmallow/marshmallow/)

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