# Move a Shopify App's Session Storage From SQLite to Postgres

Yes. The agent switches the Prisma datasource to Postgres, regenerates the Session migration, proves the deploy path on an empty database and opens a PR.

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

## What the agent does

The agent reads where sessions live before it touches the schema, because in a Shopify app the database exists for one table and the app will not install without it. In an app generated from Shopify/shopify-app-template-react-router, `app/shopify.server.ts` builds `shopifyApp` with `sessionStorage: new PrismaSessionStorage(prisma)` from `@shopify/shopify-app-session-storage-prisma`, `distribution: AppDistribution.AppStore` and `expiringOfflineAccessTokens: true` under `future`, so every offline token and its `refreshToken` and `refreshTokenExpires` go through that table. `app/db.server.ts` creates one `PrismaClient` and caches it on `global.prismaGlobal` outside production. `prisma/schema.prisma` declares `provider = "sqlite"` with `url = "file:dev.sqlite"` and a `Session` model with `id`, `shop`, `state`, `isOnline`, `scope`, `expires`, `accessToken`, `userId` as `BigInt`, the five account fields, `locale`, `collaborator`, `emailVerified` and the two refresh token columns. The one migration, `20240530213853_create_session_table`, is SQLite SQL with `DATETIME` columns, and `package.json`'s `setup` script runs `prisma generate && prisma migrate deploy`.

It also reads what the README says about this exact move: SQLite works in production only while the app runs as a single instance, and for Postgres the change is a different datasource provider in `schema.prisma`. The session storage package's own README adds that the `Session` table needs at least the columns the template already has, that `migrate` must be run to apply the schema, and that some adapters cap `String` length, which matters for `accessToken`. `.gitignore` ignores `/prisma/dev.sqlite` and its journal, `.dockerignore` ignores `.cache`, `build` and `node_modules`, and `Dockerfile` builds on `node:22-alpine`, adds `openssl` for Prisma, runs `npm ci --omit=dev` and starts with `npm run docker-start`, which is `setup` followed by `start`. `.github/workflows/ci.yml` runs `npx prisma generate && npx prisma validate` on Node 22 and 24 across npm, yarn and pnpm.

The agent changes `schema.prisma` to `provider = "postgresql"` with `url = env("DATABASE_URL")`, keeps every `Session` column and type, since Prisma maps `BigInt` and `DateTime` to Postgres itself, and starts a Postgres in the sandbox to regenerate the migration history: it removes the SQLite migration and `migration_lock.toml`, runs `prisma migrate dev --name create_session_table` to produce Postgres SQL with `TIMESTAMP(3)` and `BIGINT` columns and a lock file naming `postgresql`, then runs `prisma migrate deploy` against a fresh database to prove the deploy path the `setup` script uses. It adds `DATABASE_URL` to the README's environment section and to the Cloud Run and Fly notes the README links, removes the two `dev.sqlite` lines from `.gitignore` as dead weight or leaves them with a note, and checks that `Dockerfile` needs nothing more than the `openssl` it already installs, since the Prisma engine for Alpine is what changes, not the image.

Then it proves the app still installs. It runs `npm ci`, `npm run typecheck`, which is `react-router typegen && tsc --noEmit`, `npm run lint`, `npx prisma validate` and `npm run build`, the same four checks `ci.yml` runs, and starts the built server with `npm run start` against the sandbox Postgres with placeholder `SHOPIFY_API_KEY`, `SHOPIFY_API_SECRET` and `SHOPIFY_APP_URL` values to see the `Session` table reached and the auth route answer. It cannot complete an OAuth install without a development store, and the pull request says so rather than claiming one.

## Start it with one API call

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

```json
{
  "agent": "claude",
  "input": "Move this Shopify app's session storage from SQLite to Postgres. Read app/shopify.server.ts, app/db.server.ts, prisma/schema.prisma, prisma/migrations/, package.json, Dockerfile, .dockerignore, .gitignore, .github/workflows/ci.yml and the Application Storage section of the README first. Change the Prisma datasource to postgresql with url = env(\"DATABASE_URL\"), keep every Session column and type, replace the SQLite migration with a fresh Postgres migration generated by prisma migrate dev against a Postgres you start in the sandbox, and run prisma migrate deploy on an empty database to prove the setup script's path. Document DATABASE_URL in the README. Run npm ci, npm run typecheck, npm run lint, npx prisma validate and npm run build, then start the built server against that Postgres with placeholder Shopify credentials and confirm it reaches the Session table. Open a pull request that lists each change, quotes the generated migration and the commands you ran, and says what still needs a real development store to verify. Change nothing in extensions/.",
  "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 on its own branch with a two-line change to `prisma/schema.prisma`, a new `prisma/migrations/<timestamp>_create_session_table/migration.sql` in Postgres SQL with its `migration_lock.toml`, the old SQLite migration removed, a README section on `DATABASE_URL`, and no change to `app/shopify.server.ts`, because `PrismaSessionStorage` does not care which provider the client talks to. The description quotes the generated SQL, lists the typecheck, lint, validate and build output, shows the `migrate deploy` log against an empty database, and names the one thing it could not do: an OAuth install on a development store.

The session keeps the record: the Postgres it started, every `prisma` and `npm` command with its full output, and the server's startup log. `GET /o/{org}/sessions/{id}/messages` returns that transcript, and `GET /o/{org}/p/{project}/sessions/{id}/changes` returns the diff per file.

Before merging, a reviewer provisions the production database, sets `DATABASE_URL` wherever the app is hosted, runs `npm run deploy` for the Shopify side as usual, and installs the app on a development store once to see a `Session` row land. They also check that the generated migration's `BIGINT` for `userId` and `TIMESTAMP(3)` for the token expiry columns match what the session package's README asks for. A follow-up message to the same session, for instance to add a connection pool URL for serverless hosting, continues on the same branch.

## Run it on every app still on dev.sqlite

Start it from your terminal with one `POST /o/{org}/sessions`, this prompt as `input` and the app's repository in `resources`, then close the laptop. The key's `webhook_url` receives one `run.completed` with the pull request in `result` when the turn ends. A `request_id` that is a UUID v5 of the repository's full name makes a retried request return the run already started instead of a second pull request.

An agency that hosts several merchants' apps from the same template sends the same body once per repository from its own server. `GET /o/{org}/github` lists every repository the Rush GitHub App installation reaches, so a script can keep the ones whose `prisma/schema.prisma` still says `sqlite`. Each run reads its own schema, so an app that already added a second model to the same database gets both tables in its new migration.

## Why run it on Rush?

The schema change is two lines, and the part that goes wrong is everything around it: a migration history that still says SQLite, a `migration_lock.toml` that makes `migrate deploy` refuse the new provider, a Docker image that cannot find the Prisma engine, a first deploy with no `DATABASE_URL`. In a Rush sandbox the agent starts a real Postgres, regenerates the history against it, runs the deploy path the container runs, and boots the server, so the pull request shows the move working rather than describing it.

The pull request carries its proof: the generated SQL, the migrate and build output, and the transcript of every command. Several apps from the same template can be moved at the same time in separate sandboxes, each with its own Postgres on its own port, and the only step left for a person is the OAuth install on a development store that no sandbox can do.

## Frequently asked questions

### Why regenerate the migration instead of editing the SQL?

Because the existing one is SQLite SQL, with `DATETIME` columns, and `prisma/migrations/migration_lock.toml` names the provider. `prisma migrate deploy` compares the history to the lock file, so the clean path is a fresh initial migration generated against Postgres. A production app that already has data in SQLite needs a copy step first, which the pull request calls out as a separate job.

### Does the Dockerfile change?

Usually not. It already installs `openssl` on `node:22-alpine`, which is what Prisma's engine needs there, and `docker-start` runs `prisma migrate deploy` before `start`, which is right for Postgres. The agent reports if the installed Prisma version wants a different engine target and adds a `binaryTargets` line in `schema.prisma` only then.

### Can it keep SQLite for local development?

Prisma reads one provider per schema, so the pull request moves local development to a Postgres URL in `.env` as well, and the README section says how to run one locally. The agent does not maintain two schemas; if you want that, say so in the prompt and it adds a second schema file and script.

### What about my app's own tables?

They ride along. The fresh migration the agent generates includes every model in `schema.prisma`, not just `Session`, and `prisma migrate deploy` against the empty database creates them all. The description lists each table the migration creates so a reviewer can see nothing was dropped.

## More Shopify app jobs

- [Review Pull Requests on a Shopify App](https://getrush.ai/run/review-pull-requests/shopify-app/)
- [Write Vitest Tests for a Shopify App's Webhooks and Actions](https://getrush.ai/run/write-vitest-tests/shopify-app/)
- [Add Subscription Billing to a Shopify App](https://getrush.ai/run/add-app-billing/shopify-app/)

## The same job on other stacks

- [Move a Socket.IO Chat From SQLite to PostgreSQL](https://getrush.ai/run/migrate-sqlite-to-postgres/socket-io/)
- [Migrate a Strapi App From SQLite to PostgreSQL](https://getrush.ai/run/migrate-sqlite-to-postgres/strapi/)
- [Migrate a Drizzle Schema from SQLite to PostgreSQL](https://getrush.ai/run/migrate-sqlite-to-postgres/drizzle/)

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