# Moving from PlanetScale

> Move a PlanetScale database to Fortly's PostgreSQL, converting it from MySQL with pgloader if needed, and deploy the app beside it.

Source: https://docs.fortly.com/migrate/planetscale

This guide is for you if your database is on PlanetScale and you want it and the app that uses it in one Fortly project. PlanetScale offers two engines, and the move depends on which one you use. A **PlanetScale Postgres** database comes across with a `pg_dump`. A **PlanetScale MySQL** (Vitess) database has to be converted to PostgreSQL first, because Fortly's database is PostgreSQL and nothing on Fortly speaks MySQL. This page converts it with pgloader, a third-party open-source tool, then covers what changes in your app's SQL.

> **Warning:**
>
> Check that Fortly can run your app before you move the database. Fortly runs JavaScript and TypeScript apps (Next.js, Nuxt, SvelteKit, Astro, Express, NestJS, Hono and others), and static sites. It does not run PHP or Ruby apps, or ordinary Python, Go or Rust servers. A Laravel, Rails or Django app on PlanetScale cannot move to Fortly. See [Deploying your code](https://docs.fortly.com/deploy) for the full list.

## What maps to what

| PlanetScale | On Fortly | What changes |
|---|---|---|
| Database (Postgres) | Your project's [PostgreSQL database](https://docs.fortly.com/data) | Same engine. You load it from a SQL dump. |
| Database (MySQL / Vitess) | Your project's PostgreSQL database | Converted to PostgreSQL. Your app's raw SQL and its driver change. |
| Connection string and passwords | `DATABASE_URL`, set by Fortly for your app | You don't manage database passwords. There is no public address to connect to from outside. |
| Branches and deploy requests | Nothing built in | Change the schema with your app's migrations or through `/sql`, against the one database. |
| Sharding (Vitess keyspaces) | Nothing built in | One database per project, on a shared server with per-database limits. |
| Your app, hosted elsewhere | [Deploying](https://docs.fortly.com/deploy) | The app moves into the same project as its database. |

PlanetScale never handled your users' sign-in or files, so there is no auth or storage to move. Your users are ordinary rows in your own tables, and they come across with everything else.

## Before you start

You need:

- A Fortly API token in `$FORTLY_TOKEN`, from [API tokens](https://docs.fortly.com/account/tokens). Importing a database needs the account owner or an admin.
- `pg_dump` and `psql` installed locally.
- For a MySQL database, also: the `pscale` CLI (signed in with `pscale auth login`), Docker, `myloader` from the mydumper project, and pgloader. All three are third-party tools. Install them with your package manager, for example `brew install planetscale/tap/pscale mydumper pgloader`.

> **Note:**
>
> The import takes a SQL script up to 256 MB and runs it in one transaction, and each statement must finish within 30 seconds. A larger database can be imported as several scripts, for example one per group of tables.

## Export your database as PostgreSQL

**PlanetScale Postgres**

Create a role for the export under your database's **Connect** page in PlanetScale, and copy its connection string. Then dump the `public` schema:

```bash
export PLANETSCALE_URL='postgresql://USER:PASSWORD@HOST:5432/postgres?sslmode=verify-full'

pg_dump "$PLANETSCALE_URL" \
  --schema=public \
  --no-owner --no-privileges \
  --no-publications --no-subscriptions \
  --inserts \
  --file=planetscale.sql
```

Your local `pg_dump` must be the same major version as the database or newer. `--inserts` is required: the import runs SQL statements, and a default dump's `COPY … FROM stdin` blocks are not statements it can run. `--no-owner --no-privileges` leaves out PlanetScale's roles, which don't exist on Fortly.

**PlanetScale MySQL**

The conversion runs on your machine. You load PlanetScale's dump into a local MySQL, let pgloader copy it into a local PostgreSQL, check the result there, and dump that.

**1. Dump the database from PlanetScale.** This writes a directory of SQL files, a schema file and data files per table:

```bash
pscale database dump my-db main --output ./ps-dump
```

**2. Start a local MySQL and PostgreSQL.** They are only staging, and you delete them when you are done:

```bash
docker run -d --name stage-mysql -p 3307:3306 \
  -e MYSQL_ROOT_PASSWORD=staging -e MYSQL_DATABASE=my_db \
  mysql:8.0 --default-authentication-plugin=mysql_native_password

docker run -d --name stage-postgres -p 5433:5432 \
  -e POSTGRES_PASSWORD=staging \
  postgres:16
```

The native password plugin is there because many pgloader builds cannot sign in with MySQL 8's default authentication method.

**3. Load the dump into MySQL.** PlanetScale's dump files are in mydumper's format, so `myloader` reads them:

```bash
myloader --directory ./ps-dump \
  --host 127.0.0.1 --port 3307 --user root --password staging \
  --database my_db
```

**4. Convert it with pgloader.** Save this as `convert.load`:

```text title="convert.load"
LOAD DATABASE
  FROM mysql://root:staging@127.0.0.1:3307/my_db
  INTO postgresql://postgres:staging@127.0.0.1:5433/postgres

WITH include drop, create tables, create indexes, reset sequences, foreign keys

ALTER SCHEMA 'my_db' RENAME TO 'public';
```

```bash
pgloader convert.load
```

pgloader puts each MySQL database in a PostgreSQL schema of the same name by default. The `ALTER SCHEMA` line puts your tables in `public` instead, which is where Fortly's data API looks. pgloader prints a summary table at the end, and its row counts should match MySQL's.

**5. Check the result, and fix it while you can still connect.** This local PostgreSQL is the last copy you can open with `psql`, so do your review here:

```bash
psql "postgresql://postgres:staging@127.0.0.1:5433/postgres" -c '\d+ users'
```

See [What the conversion changes](#what-the-conversion-changes) for what to look for.

**6. Dump the converted database:**

```bash
pg_dump "postgresql://postgres:staging@127.0.0.1:5433/postgres" \
  --schema=public \
  --no-owner --no-privileges \
  --inserts \
  --file=planetscale.sql
```

`--inserts` is required: the import runs SQL statements, and a default dump's `COPY … FROM stdin` blocks are not statements it can run.

Either way, remove the line that creates the `public` schema if the dump has one, because the schema already exists on Fortly:

```bash
perl -0777 -pi.bak -e 's/^CREATE SCHEMA public;\n//gm' planetscale.sql
```

## Move to Fortly

### Create the Fortly project

```bash
export FORTLY_TOKEN=fly_...

curl -X POST https://api.fortly.com/projects \
  -H "Authorization: Bearer $FORTLY_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"name": "my-app"}'
```

The reply has a `name`, a `host` and a `url`. Fortly adds your account's suffix to the name you typed, so `my-app` comes back as something like `my-app-ada`. Every `/projects/…` route takes that full `name`, which this guide writes as `my-app-ada`, and `GET /projects` lists it for every project you have.

### Import the dump

```bash
curl -X POST https://api.fortly.com/projects/my-app-ada/import \
  -H "Authorization: Bearer $FORTLY_TOKEN" \
  --data-binary @planetscale.sql
```

```json
{ "imported": "statements", "ran": 9312, "skipped": 14, "tables": 9 }
```

`skipped` counts the dump's session settings and its own `BEGIN`/`COMMIT`, which the import leaves out on purpose. If a statement is refused, nothing is applied and the reply's `error` names it. Fix that statement and send the script again. Because a failed import changes nothing, retrying is safe. A successful import is not undone, so don't send the same script twice. See [Importing a database](https://docs.fortly.com/data/import).

A `create extension` line either works or stops the import and names itself. Fortly installs no extension for you.

### Point your app at PostgreSQL

Fortly gives your app its database connection as `DATABASE_URL`, plus `PGHOST`, `PGPORT`, `PGDATABASE`, `PGUSER`, `PGPASSWORD` and `PGSSLMODE`. Read those instead of your PlanetScale credentials, and switch your driver:

| You use | Change |
|---|---|
| Prisma | `provider = "postgresql"` and `url = env("DATABASE_URL")` in the datasource. Remove `relationMode = "prisma"` if you want PostgreSQL to enforce your foreign keys. Then create a fresh baseline migration, because MySQL migrations don't apply to PostgreSQL. |
| Drizzle | Import tables from `drizzle-orm/pg-core` instead of `mysql-core`, and use a PostgreSQL driver. |
| Knex, Sequelize, TypeORM, Kysely | Switch the client or dialect to PostgreSQL. |
| `mysql2` or `@planetscale/database` directly | Switch to a PostgreSQL driver such as `pg`, and review every query (see below). |

See [Using PostgreSQL directly](https://docs.fortly.com/data/postgres) for connecting and pooling.

### Set your secrets

Copy your app's other environment values to the project:

```bash
curl -X POST https://api.fortly.com/projects/my-app-ada/secrets \
  -H "Authorization: Bearer $FORTLY_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"name": "SESSION_SECRET", "value": "..."}'
```

Names use capital letters, digits and underscores. Add `"scope": "both"` to a value your build also reads. Setting a secret restarts the project. Leave out `DATABASE_URL`, `DATABASE_HOST`, `DATABASE_USERNAME` and `DATABASE_PASSWORD` from PlanetScale. Fortly refuses secrets named `DATABASE_URL` or `PG*` because it sets those itself. See [Environment and secrets](https://docs.fortly.com/deploy/environment).

### Deploy your app

Upload your source and Fortly installs dependencies and runs your `build` script:

```bash
tar --exclude=node_modules --exclude=.git -czf release.tgz .

curl -X POST https://api.fortly.com/projects/my-app-ada/deploys \
  -H "Authorization: Bearer $FORTLY_TOKEN" \
  --data-binary @release.tgz
```

You can also connect a GitHub repository from the console. See [Deploying your code](https://docs.fortly.com/deploy).

### Point your domain at it

```bash
curl -X POST https://api.fortly.com/projects/my-app-ada/domains \
  -H "Authorization: Bearer $FORTLY_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"host": "www.example.com"}'
```

Publish the two records the reply gives you: a `TXT` record that proves the domain is yours, and a `CNAME` that sends traffic to your project. Then verify:

```bash
curl -X POST https://api.fortly.com/projects/my-app-ada/domains/www.example.com/verify \
  -H "Authorization: Bearer $FORTLY_TOKEN"
```

The domain serves nothing until it is verified. See [Custom domains](https://docs.fortly.com/domains).

## What the conversion changes

This section is about MySQL databases only. A PlanetScale Postgres database arrives as it was.

### Types

pgloader's default conversions are sensible, but check these in the staging PostgreSQL:

| MySQL | Becomes | Check |
|---|---|---|
| `AUTO_INCREMENT` | A sequence-backed integer key | `reset sequences` sets each sequence past the highest imported id. |
| `TINYINT(1)` | `boolean` | Code that compares to `1` or `0` now compares to `true` or `false`. |
| `DATETIME`, `TIMESTAMP` | `timestamptz` | MySQL's `DATETIME` has no time zone. Check the times came out in the zone you meant. |
| `'0000-00-00'` dates | `null` | Count them first in MySQL, so you know how many rows changed. |
| `ENUM(…)` | A PostgreSQL enum type | Adding a value later is `alter type … add value`. |
| Unsigned integers | The next larger signed type | Usually invisible. |

### Case-insensitive comparisons

This one fails silently. MySQL's default collations ignore case, so `WHERE email = 'Ada@Example.com'` finds a row stored as `ada@example.com`. PostgreSQL compares text exactly, so the same query finds nothing, and a user who typed their email differently can't sign in.

Fix it in the staging database, before you dump:

```sql
-- Store one form...
update users set email = lower(email);

-- ...and make that form unique.
create unique index users_email_lower on users (lower(email));
```

Then compare with `lower(email) = lower($1)` in your code. Do the same for usernames, slugs and any other column you look things up by. If lowering the emails reports a duplicate, two accounts differ only by case. Decide which one to keep before you go on.

`LIKE` is case-sensitive in PostgreSQL too. Use `ILIKE` where you relied on MySQL ignoring case.

### Raw SQL

An app that goes through an ORM usually has only a few of these. One built on hand-written SQL has many, so search your code for each:

| MySQL | PostgreSQL |
|---|---|
| `` `name` `` | `"name"`, or no quotes at all |
| `?` placeholders (`mysql2`) | `$1`, `$2` (`pg`) |
| `LIMIT 20, 10` | `LIMIT 10 OFFSET 20` |
| `IFNULL(a, b)` | `COALESCE(a, b)` |
| `GROUP_CONCAT(x)` | `string_agg(x, ',')` |
| `INSERT … ON DUPLICATE KEY UPDATE` | `INSERT … ON CONFLICT (key) DO UPDATE` |
| `LAST_INSERT_ID()` | `INSERT … RETURNING id` |
| `DATE_FORMAT(d, '%Y-%m')` | `to_char(d, 'YYYY-MM')` |
| `NOW()` in a `DATETIME` column | `now()`, which returns a `timestamptz` |

## What's different

- **Your app and database are in one project.** Deleting the project deletes both.
- **Only your app connects to the database.** It gets a connection string, but there is no public address for a desktop client or `psql`. You load data with the import, and read it with the console, the query endpoint, or an [export](https://docs.fortly.com/data/backups).
- **No branches, no deploy requests, no sharding.** Schema changes run against the one database, from your app's migrations or through `/sql`. Databases share a server, with a small connection limit per database (10 by default), so keep your app's pool small. See [Limits](https://docs.fortly.com/account/limits).
- **Foreign keys are enforced** if your schema declares them. PlanetScale MySQL often ran without them. Data that was inconsistent there stops the import when the constraint is added.
- **An idle project stops** after 15 minutes and starts again on the next request.

## Things to check after moving

- **Row counts match.** Compare each table with PlanetScale:

  ```bash
  curl -X POST https://api.fortly.com/projects/my-app-ada/query \
    -H "Authorization: Bearer $FORTLY_TOKEN" \
    -H "Content-Type: application/json" \
    -d '{"sql": "select count(*) from users"}'
  ```

- **A new row gets a new id.** Insert one into a busy table and check it doesn't collide with an imported row.
- **Sign-in works for a user whose email has capitals in it.** This is how the collation change shows up.
- **Money and decimal columns kept their precision.** Spot-check a few values against PlanetScale.
- **Times are in the right zone.** Compare a few `created_at` values against PlanetScale.
- **Your code has no MySQL left in it.** Search for backticks, `IFNULL`, `GROUP_CONCAT` and `ON DUPLICATE KEY`.
- **Remove the staging containers** with `docker rm -f stage-mysql stage-postgres` once you are done.
- **Keep the PlanetScale database** until you have run on Fortly for a while.

- [Importing a database](https://docs.fortly.com/data/import): What the import accepts, what it skips, and how it fails.
- [Using PostgreSQL directly](https://docs.fortly.com/data/postgres): Connecting your app with a driver or an ORM.
- [Deploying your code](https://docs.fortly.com/deploy): Which frameworks run, and how builds work.