Migrate from PlanetScale

PlanetScale is a MySQL-compatible database built on Vitess. DB9 is PostgreSQL-compatible. Migrating from PlanetScale requires converting your schema and queries from MySQL to PostgreSQL syntax.

This guide covers exporting from PlanetScale, converting the MySQL dump to PostgreSQL-compatible SQL, importing into DB9, and updating your application.

Key Differences

Area	PlanetScale (MySQL/Vitess)	DB9 (PostgreSQL)
SQL dialect	MySQL	PostgreSQL
Connection string	`mysql://user:pass@host/db?ssl={"rejectUnauthorized":true}`	`postgresql://tenant.role@pg.db9.io:5433/postgres`
Protocol	MySQL wire protocol	pgwire (PostgreSQL wire protocol)
Auto-increment	`AUTO_INCREMENT`	`GENERATED ALWAYS AS IDENTITY` or `SERIAL`
String quoting	Backticks ` for identifiers	Double quotes `"` for identifiers
Boolean	`TINYINT(1)`	Native `BOOLEAN`
JSON	`JSON` (stored as text internally)	`JSONB` (binary, indexable)
Date/time	`DATETIME`, `TIMESTAMP`	`TIMESTAMPTZ` (timezone-aware)
Branching	Git-like schema branching with deploy requests	Full data copy branches
Foreign keys	Not supported (Vitess limitation)	Supported, and `TRUNCATE` of a referenced table is refused — but dropping the referenced table bypasses the check, see compatibility matrix
Joins	Supported (with Vitess limitations on cross-shard)	Full JOIN support without restrictions
Transactions	Supported (single-shard only in some configs)	Full ACID (READ COMMITTED default, REPEATABLE READ available)
Connection pooling	Built-in	No built-in pooler — use application-side pooling
Replication	Managed by Vitess	Not supported

Prerequisites

Access to your PlanetScale database (connection string from the PlanetScale dashboard)
mysqldump or PlanetScale CLI (pscale) installed
pgloader installed (for automated MySQL → PostgreSQL conversion), or manual conversion
DB9 CLI installed: curl -fsSL https://db9.ai/install | sh
A DB9 account: db9 create --name my-app to create your target database

Export from PlanetScale

Option A: mysqldump

Get your connection string from PlanetScale dashboard → Connect → General.

mysqldump --no-tablespaces --column-statistics=0 \
  -h host.connect.psdb.cloud \
  -u username -p \
  --ssl-mode=REQUIRED \
  your_database > export.sql

Option B: PlanetScale CLI

pscale db dump your-database main --output ./dump

This creates one SQL file per table in the ./dump directory.

Convert MySQL to PostgreSQL

MySQL and PostgreSQL have different SQL dialects. You need to convert the dump.

Option A: Manual conversion

Common MySQL → PostgreSQL conversions:

MySQL                              →  PostgreSQL
─────────────────────────────────────────────────────────
`column_name`                      →  "column_name" (or just remove backticks)
AUTO_INCREMENT                     →  GENERATED ALWAYS AS IDENTITY
INT UNSIGNED                       →  BIGINT
TINYINT(1)                         →  BOOLEAN
DATETIME                           →  TIMESTAMPTZ
MEDIUMTEXT / LONGTEXT              →  TEXT
ENUM('a','b','c')                  →  TEXT CHECK (col IN ('a','b','c'))
ON UPDATE CURRENT_TIMESTAMP        →  (use a trigger instead)
ENGINE=InnoDB                      →  (remove — not applicable)
CHARACTER SET / COLLATE            →  (remove — DB9 is UTF-8 only)
IF NOT EXISTS (in some contexts)   →  IF NOT EXISTS (same in PostgreSQL)

Example conversion of a table:

-- MySQL (PlanetScale)
CREATE TABLE `users` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `active` tinyint(1) DEFAULT '1',
  `metadata` json DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB;

-- PostgreSQL (DB9)
CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  active BOOLEAN DEFAULT true,
  metadata JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Option B: Use a conversion script

For larger schemas, use sed for common patterns:

cat export.sql \
  | sed 's/`//g' \
  | sed 's/ENGINE=InnoDB[^;]*//' \
  | sed 's/AUTO_INCREMENT/GENERATED ALWAYS AS IDENTITY/g' \
  | sed 's/tinyint(1)/BOOLEAN/g' \
  | sed 's/int([0-9]*)/INTEGER/g' \
  | sed 's/bigint([0-9]*)/BIGINT/g' \
  | sed 's/varchar([0-9]*)/TEXT/g' \
  | sed 's/MEDIUMTEXT/TEXT/g' \
  | sed 's/LONGTEXT/TEXT/g' \
  | sed 's/datetime/TIMESTAMPTZ/g' \
  | sed 's/ json / JSONB /g' \
  | sed '/^\/\*/d' \
  | sed '/^--/d' \
  | sed '/^SET /d' \
  | sed '/^LOCK TABLES/d' \
  | sed '/^UNLOCK TABLES/d' \
  > import.sql

Review the output manually — automated conversion will miss edge cases.

Option C: pgloader (automated)

pgloader can read directly from MySQL and write to PostgreSQL, handling type conversion automatically. However, since DB9 uses pgwire, you would run pgloader against a local PostgreSQL first, then export and import into DB9:

# pgloader from MySQL dump to local PostgreSQL
pgloader mysql://user:pass@host/db postgresql://localhost/temp_db

# Then export schema and data separately from local PostgreSQL
pg_dump --schema-only --no-owner --no-privileges temp_db > schema.sql
pg_dump --data-only --no-owner temp_db > data.sql

# Rewrite pg_dump's SERIAL and identity columns into forms DB9 runs
sed -i.bak "s/nextval('\([^']*\)'::regclass)/nextval('\1')/g" schema.sql
perl -0pi.orig -e 's/ALTER TABLE (\S+) ALTER COLUMN (\S+) ADD GENERATED (?:ALWAYS|BY DEFAULT) AS IDENTITY \(\s*SEQUENCE NAME (\S+)(.*?)\);/CREATE SEQUENCE $3$4;\nALTER SEQUENCE $3 OWNED BY $1.$2;\nALTER TABLE $1 ALTER COLUMN $2 SET DEFAULT nextval(\x27$3\x27);/gs' schema.sql

Import these files with psql in step 5, schema first. A single-file pg_dump loses its primary keys when loaded into DB9, DB9 cannot run the nextval('...'::regclass) defaults and ADD GENERATED ... AS IDENTITY statements that pg_dump writes, and db9 db sql -f cannot run a pg_dump file at all — see Migrate from PostgreSQL.

Add Foreign Keys

PlanetScale (Vitess) does not support foreign keys, so your application likely enforces referential integrity in application code. Now you can add proper foreign keys:
SQL
```
ALTER TABLE posts ADD CONSTRAINT fk_posts_user
  FOREIGN KEY (user_id) REFERENCES users(id);

ALTER TABLE comments ADD CONSTRAINT fk_comments_post
  FOREIGN KEY (post_id) REFERENCES posts(id);

ALTER TABLE comments ADD CONSTRAINT fk_comments_author
  FOREIGN KEY (author_id) REFERENCES users(id);
```
Review your schema and add foreign keys where appropriate. This gives you database-level referential integrity that PlanetScale could not provide.

Create the DB9 Database

db9 create --name my-app --show-connection-string

Import into DB9
Terminal
```
# Small files: the HTTP SQL API behind db9 db sql rejects requests over about 2 MB
# and gives up after about 15 seconds
db9 db sql my-app -f import.sql

# Anything larger
psql "$(db9 db connect my-app --output quiet)" -f import.sql
```
If you separated schema and data (required for the Option C pg_dump files), save the step 3 statements as foreign_keys.sql instead of adding them to schema.sql. The pgloader database has no foreign keys, so pg_dump writes data.sql in table-name order, not dependency order: with the foreign keys already in place, a child table that loads before its parent rejects its rows. Load the three files in this order:
Terminal
```
# Schema first: tables and primary keys, without the step 3 foreign keys
psql "$(db9 db connect my-app --output quiet)" -f schema.sql

# Then data
psql "$(db9 db connect my-app --output quiet)" -f data.sql

# Last, the step 3 ALTER TABLE ... FOREIGN KEY statements
psql "$(db9 db connect my-app --output quiet)" -f foreign_keys.sql
```
psql keeps going after an error, so scan its output for ERROR: lines. If foreign_keys.sql reports 23503, the data has orphan rows that application code never caught — delete or repair them, then re-run that statement.

Update Your Application

Replace the MySQL driver with a PostgreSQL driver

import mysql from 'mysql2/promise';
const pool = mysql.createPool(process.env.DATABASE_URL);
const [rows] = await pool.execute('SELECT * FROM users WHERE id = ?', [userId]);
import pg from 'pg';
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
const { rows } = await pool.query('SELECT * FROM users WHERE id = $1', [userId]);

Key query syntax changes

MySQL (PlanetScale)	PostgreSQL (DB9)
`SELECT * FROM users LIMIT 10, 20`	`SELECT * FROM users LIMIT 20 OFFSET 10`
`IFNULL(col, default)`	`COALESCE(col, default)`
`NOW()`	`now()` (same, but returns TIMESTAMPTZ)
`GROUP_CONCAT(col)`	`string_agg(col, ',')`
`INSERT ... ON DUPLICATE KEY UPDATE`	`INSERT ... ON CONFLICT DO UPDATE`
Backtick identifiers `col`	Double-quote identifiers `"col"` (or just unquoted)
`?` parameter placeholders	`$1, $2, $3` numbered placeholders

Update your ORM

If you use an ORM, switch the database provider:

// Prisma
 provider = "mysql"
 provider = "postgresql"

// Drizzle
 import { mysqlTable } from 'drizzle-orm/mysql-core';
 import { pgTable } from 'drizzle-orm/pg-core';

For ORM-specific setup, see: Prisma, Drizzle, SQLAlchemy.

Connection string

DATABASE_URL=mysql://user:pass@host.connect.psdb.cloud/mydb?ssl={"rejectUnauthorized":true}
DATABASE_URL=postgresql://a1b2c3d4e5f6.admin@pg.db9.io:5433/postgres?sslmode=require

Validate

Check schema
Terminal
```
db9 db dump my-app --ddl-only
```
Verify tables, columns, types, and constraints match your expectations.

Check row counts
Terminal
```
db9 db sql my-app -q "SELECT count(*) FROM users"
db9 db sql my-app -q "SELECT count(*) FROM posts"
```
Compare against the source PlanetScale database.

Run your test suite
Terminal
```
DATABASE_URL="$(db9 db connect my-app --output quiet)" npm test
```
Common test failures
- MySQL-specific syntax — backtick identifiers, LIMIT offset, count, IFNULL, GROUP_CONCAT
- Parameter placeholders — ? must become $1, $2, ...
- Boolean handling — MySQL uses 0/1; PostgreSQL uses true/false
- Date formatting — MySQL DATE_FORMAT() → PostgreSQL to_char()

Rollback Plan

Your PlanetScale database is unchanged. To revert:

Switch DATABASE_URL back to the PlanetScale connection string and revert driver/ORM changes.
If you need to export data created in DB9 back to PlanetScale, export from DB9 and convert PostgreSQL SQL back to MySQL syntax.

Caveats

Full SQL dialect migration — This is not a simple connection string swap. Every raw SQL query must be converted from MySQL to PostgreSQL syntax. ORMs reduce this effort significantly.
Driver change required — MySQL drivers (mysql2, pymysql, go-sql-driver/mysql) must be replaced with PostgreSQL drivers (pg, psycopg, pgx).
Type differences — MySQL and PostgreSQL handle types differently (TINYINT vs BOOLEAN, DATETIME vs TIMESTAMPTZ, ENUM handling). Review all column types.
No schema branching — PlanetScale’s git-like branching with deploy requests has no direct equivalent. DB9 has data-copy branches, but no schema-diff deploy workflow.
Foreign keys are now available — PlanetScale (Vitess) does not support foreign keys. Take advantage of this by adding proper referential integrity constraints. One statement does bypass them: dropping a referenced table leaves a dangling constraint that breaks pg_constraint for the whole database — see compatibility matrix.
Stored procedure differences — MySQL stored procedures must be rewritten in PL/pgSQL. DB9 supports basic PL/pgSQL, including CONTINUE and dynamic EXECUTE, but not WHILE or FOREACH loops, or cursors. Exception handling and nested BEGIN ... END blocks work only inside a DO block, not in a CREATE FUNCTION body.

Compatibility Matrix — full list of supported and unsupported PostgreSQL features
Connect — connection string format and authentication options
Prisma Guide — type-safe ORM (handles dialect differences)
Drizzle Guide — lightweight TypeScript ORM
Production Checklist — deployment readiness

Migrate from PlanetScale

Key Differences

Prerequisites

Rollback Plan

Caveats

Next Pages