# Migrate a Feathers App From SQLite to Postgres

Yes. An agent moves a Feathers and Knex app from SQLite to Postgres: the connection, config, migrations, a data copy script, tests and CI, in one pull request.

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

## What the agent does

The agent finds every place the app says SQLite, which is more than the driver. In an app laid out like feathersjs/feathers-chat, the server in `feathers-chat-ts` records `"database": "sqlite"` in the `feathers` block of `package.json` and depends on `knex`, `sqlite3` and `@feathersjs/knex`. `config/default.json` has a `sqlite` key with the `sqlite3` client, a file name and `useNullAsDefault`. `src/sqlite.ts` builds the Knex instance and stores it as `sqliteClient`, with a type declaration for that name; `getOptions` in both `users.class.ts` and `messages.class.ts` reads `app.get('sqliteClient')`; and `knexfile.ts` exports `app.get('sqlite')` for the `migrate` script. `config/custom-environment-variables.json` maps the port, host and authentication secrets to environment variables and has no entry for a database.

It replaces those in one consistent pass. `sqlite3` gives way to `pg` in `package.json`, `src/sqlite.ts` becomes `src/postgresql.ts` setting `postgresqlClient`, the two `getOptions` functions and `knexfile.ts` follow the new names, `config/default.json` gets a `postgresql` block with the `pg` client and a local connection string, and `config/custom-environment-variables.json` maps that connection to `DATABASE_URL` so production never reads a URL from the repository. `src/configuration.ts` validates configuration at start-up, so the agent checks the new key against its schema and boots the app to see it accepted.

Then it reads the three migrations for types that behave differently. They create `users` and `messages` with `increments('id')`, then add `createdAt` and `userId` to messages as `bigint`, with `userId` referencing `users.id`. The schemas in `messages.schema.ts` declare both as `Type.Number()`, and the `pg` driver hands 64-bit integers back as strings unless told otherwise. On Postgres that would fail result validation or, worse, make `service('users').get(message.userId)` look up a string. The agent adds a migration that makes `userId` an integer to match the key it references, and registers a parser for the 64-bit type in `src/postgresql.ts` so `createdAt`, a millisecond timestamp too large for a 32-bit column, stays a number. It also notes that the first migration stores `githubId` as a string while `users.schema.ts` types it as a number, and aligns the two only if you ask.

Existing rows move with a script. `scripts/copy-sqlite-to-postgres.ts` opens the old file and the new database with Knex, copies `users` then `messages` in one transaction so the foreign key holds, and resets each id sequence to the highest id copied, without which the first insert after the move collides with an existing row. To prove all of it, the agent installs PostgreSQL in its sandbox, creates a database, runs `npm run migrate` from empty, runs the copy from a SQLite file it seeded through the app, and runs `npm test`, which here means a migration plus the four Mocha files, with `DATABASE_URL` set. It adds a `postgres` service and that variable to the `feathers-chat-ts` job in `.github/workflows/nodejs.yml`, and updates the readme's setup lines.

## Start it with one API call

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

```json
{
  "agent": "codex",
  "input": "Move this Feathers 5 app from SQLite to PostgreSQL. The server is in feathers-chat-ts. Read its package.json, knexfile.ts, config/default.json, config/test.json, config/custom-environment-variables.json, src/app.ts, src/configuration.ts, src/sqlite.ts, src/declarations.ts, both *.class.ts and *.schema.ts files under src/services, the three migrations, the tests and .github/workflows/nodejs.yml first. Replace sqlite3 with pg, rename the connection module and the sqliteClient setting to postgresql and postgresqlClient everywhere they are read, add a postgresql block to config/default.json and map its connection to DATABASE_URL in custom-environment-variables.json. Check every column type against the TypeBox schemas: bigint columns come back from pg as strings, so add a migration making messages.userId an integer and register a parser so createdAt stays a number. Report the githubId string-versus-number mismatch without changing it. Write scripts/copy-sqlite-to-postgres.ts that copies users then messages in a transaction and resets the id sequences. Install PostgreSQL in your sandbox, run the migrations from empty, seed a SQLite file through the app, run the copy, and run npm test against Postgres. Add a postgres service and DATABASE_URL to the CI job. Open a pull request with the test output, the row counts before and after the copy, and the cut-over steps.",
  "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 renames `src/sqlite.ts` to `src/postgresql.ts`, changes `package.json` and its lock file, `knexfile.ts`, `src/app.ts`, both service class files, `config/default.json`, `config/custom-environment-variables.json` and `.github/workflows/nodejs.yml`, and adds one migration and the copy script. The description shows `npm test` passing against Postgres in the sandbox, the row counts in each table before and after the copy, and a read of one copied message through the service to show `userId` and `createdAt` arriving as numbers.

It also lists the steps to cut over, in order: create the database, set `DATABASE_URL`, run `npm run migrate`, stop writes, run the copy script, start the app. And it names what the change leaves alone: the `react-chat` and `quick-start` directories, the services' hooks and the public client, none of which know which database is underneath.

A reviewer checks three things. The type migration: an integer `userId` is right only while user ids fit one, which they do with `increments`. The connection settings: the pull request adds no pool or SSL options beyond the URL, and a managed Postgres often needs both. And the CI job, which now starts a database service; the first run of that workflow on the branch is the confirmation that the tests pass outside the agent's sandbox.

## Run it on every service still on SQLite

A team with several Feathers services generated the same way sends this body once per repository to `POST /o/{org}/sessions`, each with its own 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 and not a second branch. Every run reads its own migrations, so a service with `bigint` columns gets the parser and one without them does not.

Give the key a `webhook_url` on the console's Developers page and each run posts `run.completed` when its turn finishes, with the agent's final answer, which links the pull request, in `result`. When one repository's run ends in `run.failed`, the session's transcript shows whether its suite was already failing on SQLite in the first test run.

## Why run it on Rush?

This migration fails in the gaps between two databases, and those gaps only show when both are running. The agent has a sandbox of its own where it installs Postgres, keeps the SQLite file beside it, and runs the app against each: the migrations from empty, the copy, the full suite, a message read back through the service. A column that returns a string where a number was expected is found by a failing test in the sandbox, with the output kept, and not by the first user after the switch.

Doing this on a laptop means installing a database server, juggling two connection strings and remembering to reset the sequences. In a run, that setup is disposable, the row counts and test output are recorded, and the pull request arrives with the cut-over steps written down. Several services can be moved in parallel, each with its own Postgres.

## Frequently asked questions

### Does it move my existing data?

It writes and tests the script that does. `scripts/copy-sqlite-to-postgres.ts` copies each table in dependency order inside a transaction and resets the id sequences. The agent runs it in the sandbox on seeded data and reports the counts; running it on your production file is a step in the cut-over list for you to perform.

### Why do bigint columns need special handling?

The `pg` driver returns 64-bit integers as strings, because JavaScript numbers cannot hold every such value. The app's schemas expect numbers for `userId` and `createdAt`. The agent changes `userId` to a 32-bit integer, which matches the key it points at, and registers a parser so `createdAt` is read as a number.

### Will local development still work without Postgres installed?

After this change the app needs a Postgres to start. The readme gains the two commands to create a local database and the variable to set. If you would rather keep SQLite for development and tests and use Postgres only in production, say so in the prompt; the agent then keeps both clients and selects by configuration.

### Does it change the Feathers services or hooks?

Only the two `getOptions` functions, which read the renamed client. Hooks, resolvers, schemas and the authentication setup are untouched, apart from the one schema-and-column mismatch the agent reports for you to settle.

## More Feathers jobs

- [Run a Security Audit on a Feathers App](https://getrush.ai/run/security-audit/feathers/)
- [Write Service Tests for a Feathers App](https://getrush.ai/run/write-service-tests/feathers/)

## The same job on other stacks

- [Move a Gin API From SQLite to Postgres](https://getrush.ai/run/migrate-sqlite-to-postgres/gin/)
- [Migrate a Sequelize App From SQLite to Postgres](https://getrush.ai/run/migrate-sqlite-to-postgres/sequelize/)
- [Move a Shopify App's Session Storage From SQLite to Postgres](https://getrush.ai/run/migrate-sqlite-to-postgres/shopify-app/)
- [Move a Socket.IO Chat From SQLite to PostgreSQL](https://getrush.ai/run/migrate-sqlite-to-postgres/socket-io/)

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