# Add Knex Migrations to an Express App

Yes. An agent turns a hand-run SQL file into Knex migrations that adopt your existing MySQL schema, proves them on a real MySQL, and opens a PR.

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

## What the agent does

The agent reads how the schema is created today before it writes a migration, because in a repository like rodrigoms2004/nodeAPI-JWT the schema is a file someone pastes into a MySQL client. `SQL-SCHEMA/db.sql` opens with `use lokasigfox;`, creates `user` (a `VARCHAR(100)` primary key, `name` and `email` unique, `nickname`, `phone`, `password`, `establishment_id`, `profile`) and `toDoList` (an `INT AUTO_INCREMENT` key, `description TEXT`, `done TINYINT(1)`, `start BIGINT`, `finish BIGINT NULL`), and between them keeps the author's scratch work: a `drop table user`, a `delete from user`, sample `update` and `insert` statements. The README says `MySql version: 5.7`. `server/database/config.js` builds a Knex instance with `client: 'mysql'` from `DB_HOST`, `DB_USER`, `DB_PASSWORD`, `DB_NAME` and `DB_PORT`, and every model under `server/models/` calls it per query against the `user` and `toDoList` tables by name.

`package.json` declares `knex ^0.16.3`, `express ^4.16.4`, `bcrypt 3.0.0`, `jsonwebtoken`, `uuid`, `moment` and `lodash`, with a `package-lock.json` in the old version 1 format, and no `mysql` driver at all, so the Knex client it names cannot connect as committed. On a current Node, `npm install` stops at `bcrypt@3.0.0`, whose native build fails under `node-gyp`; `npm install --ignore-scripts` completes, Knex 0.16.3 loads, and once a `mysql` driver is added, `knex({ client: 'mysql' })` constructs. The agent installs MariaDB in its sandbox, creates the `lokasigfox` database and a user matching `.env.example`'s variable names, and works against that.

It writes a `knexfile.js` at the root that reads the same five `DB_*` variables through `dotenv` and names `./migrations` and `./seeds`, and points `server/database/config.js` at that file so the app and the CLI share one connection definition. The first migration, created with `knex migrate:make`, builds both tables with the schema builder: `string('id', 100).primary()`, `.unique()` on `name` and `email`, `increments('id')`, `text`, `boolean`, `bigInteger`. Run with `knex migrate:latest` on the empty database it prints `Batch 1 run: 1 migrations`, and `SHOW CREATE TABLE` shows the two tables with the same columns and nullability as `db.sql`, two differences aside: `increments` makes the todo id `int unsigned` where the SQL file has a signed `INT`, and Knex names the unique keys `user_name_unique` and `user_email_unique`. `knex migrate:rollback` drops both and prints `Batch 1 rolled back: 1 migrations`.

Because teams already have a `lokasigfox` database built from `db.sql`, the migration starts with `if (await knex.schema.hasTable('user')) return;`, so against such a database `migrate:latest` records the migration in `knex_migrations` and leaves the existing tables untouched, which the agent proves by loading `db.sql`'s two `CREATE TABLE` statements first and checking the todo id is still `int(11)` afterwards. A seed from `knex seed:make` inserts the sample todo that `db.sql` inserts by hand. `package.json` gains `migrate`, `rollback` and `seed` scripts, and `mysql` joins the dependencies. The scratch statements in `db.sql` are not carried over; the pull request says which ones were dropped.

## Start it with one API call

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

```json
{
  "agent": "claude",
  "input": "Add Knex migrations to this Express app. Read package.json, package-lock.json, .env.example, README.md, SQL-SCHEMA/db.sql, server/database/config.js, server/app.js, the files under server/models/ and server/route/api.js first. Install with --ignore-scripts if the native bcrypt build fails on your Node and say so in the pull request; install a MySQL-compatible server in your sandbox and create the database and user the .env variables describe. Add a root knexfile.js that reads the same DB_* variables through dotenv, make server/database/config.js use it so there is one connection definition, and add the mysql driver the client needs. Write one migration with knex migrate:make that creates the user and toDoList tables from db.sql with the schema builder, guarded with hasTable so a database already built from db.sql is adopted without change, and a seed for the sample todo. Run knex migrate:latest on an empty database and on one built from db.sql, compare SHOW CREATE TABLE with db.sql and name every difference, run migrate:rollback, add migrate, rollback and seed scripts, and open a pull request that pastes 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

What comes back is a pull request that adds `knexfile.js`, `migrations/<timestamp>_create_user_and_todolist.js` and `seeds/sample_todo.js`, changes `server/database/config.js` to build its connection from the knexfile, and adds the `mysql` dependency and three scripts to `package.json`. Nothing under `server/models/`, `server/controllers/` or `server/route/` changes, and `SQL-SCHEMA/db.sql` stays in place as the record of what the migration was written from.

The description pastes four things from the sandbox: `migrate:latest` on an empty database with the `SHOW CREATE TABLE` output for both tables, the same command on a database built from `db.sql` showing `knex_migrations` gaining a row while the todo id stays `int(11)`, `migrate:rollback`, and the `npm install` failure on `bcrypt@3.0.0` that forced `--ignore-scripts`. It lists the two deliberate differences from `db.sql`, the unsigned id and the unique key names, and the scratch statements it left behind.

Before merging, a reviewer decides whether a signed todo id matters enough to use `specificType` instead of `increments` on fresh databases, whether `bcrypt` should move to a release that builds on the Node the team runs, which is a separate change, and runs `npm run migrate` against each existing database so the migration row exists before the next one is written.

## Run it on every pull request that changes a table

An automation in the Project with a `github` trigger on `pull_request.opened` and `pull_request.synchronize` runs a shorter prompt against a pull request that touches `server/models/` or `migrations/`: run `migrate:latest` and `migrate:rollback` on a fresh database, confirm the models' column names exist, and report on the pull request. The trigger adds the pull request to the prompt, and pull requests from forks never start a run.

A team with several Express services still creating tables from a SQL file sends one `POST /o/{org}/sessions` per repository from a script, with this prompt and 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. Each run opens at most one pull request.

## Why run it on Rush?

A migration written from a SQL file is only right once it has been applied and compared: the column types the schema builder picks, the key names it generates and the order tables are created in all differ a little from the hand-written DDL, and an existing database makes the first `migrate:latest` either a no-op or a crash. The agent installs a database in its own sandbox, applies the migration to an empty one and to one built from `db.sql`, diffs the `SHOW CREATE TABLE` output, and rolls it back, before the pull request exists.

Every command and its output stays on the run, so a reviewer reads the two `SHOW CREATE TABLE` blocks and the rollback without starting MySQL. Several Express services can be migrated at once, each in its own sandbox, and a difference the agent could not remove, like the unsigned id, is listed in the pull request rather than hidden behind a passing command.

## Frequently asked questions

### Will migrate:latest break a database that was created from db.sql?

No. The migration's `up` begins with `if (await knex.schema.hasTable('user')) return;`, so on a database that already has the tables it inserts its row into `knex_migrations` and changes nothing; the agent checked the todo id was still `int(11)` afterwards. On an empty database the same migration creates both tables. The `down` drops them either way, which the pull request points out.

### Why does the pull request add a mysql package?

Because `server/database/config.js` builds Knex with `client: 'mysql'` and `package.json` never declared the driver, so a fresh install cannot connect at all. Knex needs the driver in the project's own dependencies; the agent adds `mysql`, which that client name maps to, and the same driver serves the Knex CLI for migrations.

### What about bcrypt failing to install?

`bcrypt@3.0.0` ships no prebuilt binary for a current Node and its source does not compile under today's `node-gyp`, so `npm install` fails before Knex is even reached. The agent installs with `--ignore-scripts` to work on the migrations, which never touch bcrypt, and names the failure in the pull request as a separate upgrade; the app's login path still needs a bcrypt that builds.

### How does the migration differ from db.sql?

In two ways the agent lists. `increments('id')` produces `int unsigned NOT NULL AUTO_INCREMENT`, while `db.sql` declares a signed `INT`; and Knex names the unique constraints `user_name_unique` and `user_email_unique` instead of `name_UNIQUE` and `email_UNIQUE`, and does not repeat the redundant unique index on the primary key. Columns, types, nullability and the primary keys match.

### Does the app's own connection change?

Only in where it reads its settings. `server/database/config.js` now requires the root `knexfile.js` instead of building the same object from the same five `DB_*` variables, so the CLI and the models cannot drift apart. The models still call `db.connection()` exactly as before.

## More Knex jobs

- [Upgrade Knex 0.20 to Knex 3 in an Express API](https://getrush.ai/run/upgrade-knex/knex/)
- [Write Integration Tests for a Knex and Express API](https://getrush.ai/run/write-integration-tests/knex/)

## The same job on other stacks

- [Add TypeORM Migrations to a Koa API](https://getrush.ai/run/add-database-migrations/koa/)
- [Add Database Migrations to a Mongoose App](https://getrush.ai/run/add-database-migrations/mongoose/)
- [Move a Sequelize App From sync() to Migrations](https://getrush.ai/run/add-database-migrations/sequelize/)
- [Add Database Migrations to a Slim 4 App](https://getrush.ai/run/add-database-migrations/slim/)

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