All jobs

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

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

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

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

The same job on other stacks

Keep reading