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)	Fully supported
Joins	Supported (with Vitess limitations on cross-shard)	Full JOIN support without restrictions
Transactions	Supported (single-shard only in some configs)	Full ACID with REPEATABLE READ
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 pg_dump from local PostgreSQL and import to DB9
pg_dump --no-owner --no-privileges temp_db > converted.sql
db9 db sql my-app -f converted.sql

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

# For small to medium databases
db9 db sql my-app -f import.sql

# For larger databases
psql "$(db9 db status my-app --json | jq -r .connection_string)" -f import.sql

If you separated schema and data:

# Schema first (includes foreign keys)
psql "$(db9 db status my-app --json | jq -r .connection_string)" -f schema.sql

# Then data
psql "$(db9 db status my-app --json | jq -r .connection_string)" -f data.sql

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 status my-app --json | jq -r .connection_string)" 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.
Stored procedure differences — MySQL stored procedures must be rewritten in PL/pgSQL. DB9 supports basic PL/pgSQL but not WHILE, EXECUTE, or exception handling.

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