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_id>.<role>@pg.db9.io:5433/postgres`
Protocol	MySQL wire protocol	pgwire (PostgreSQL wire protocol)
Auto-increment	`AUTO_INCREMENT`	`GENERATED BY DEFAULT 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 enforced — `INSERT`/`DELETE` violations, `TRUNCATE` of a referenced table, and `DROP TABLE` of a referenced table are all refused
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 BY DEFAULT 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 BY DEFAULT 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 BY DEFAULT 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. Two of them are in every real mysqldump file. tinyint(1) values are written as bare 1/0, which a BOOLEAN column refuses (42804 column "active" is of type boolean but expression is of type integer). And string values keep MySQL’s backslash escapes, while DB9 always reads a backslash literally (standard_conforming_strings cannot be turned off), so a value written as 'It\'s' breaks the statement and an escaped JSON value fails with 22P02. Load tinyint(1) columns as INTEGER and convert them after the import, as described for booleans in Migrate from Turso, and rewrite escaped values before loading them.

Your mysqldump INSERT statements include the original id values, which is why the conversion above rewrites AUTO_INCREMENT to GENERATED BY DEFAULT AS IDENTITY. A GENERATED ALWAYS column refuses explicit ids, as in PostgreSQL (428C9 cannot insert a non-DEFAULT value into column "id"), and DB9 does not parse PostgreSQL’s way around that, OVERRIDING SYSTEM VALUE (42601). A BY DEFAULT column accepts the ids but does not advance its sequence, so the sequence is left at 1 while your table already holds id 1. The import looks completely successful and the first insert your application makes fails:

INSERT INTO users (id, name, email)
VALUES (1,'Alice','alice@example.com'),(2,'Bob','bob@example.com');   -- accepted
SELECT last_value FROM users_id_seq;                                  -- 1

INSERT INTO users (name, email) VALUES ('Dave','dave@example.com');
-- ERROR: duplicate key value violates unique constraint "users_pkey"  (23505)
-- DETAIL: Key (id)=(1) already exists.

Reposition every identity sequence once the data is in, before pointing traffic at DB9:

SELECT setval('users_id_seq', (SELECT max(id) FROM users));

Repeat for each table you converted, including the pgloader path below. A SERIAL column needs the same setval after an id-carrying import. All of this is ordinary PostgreSQL behaviour; the DB9 difference is only that OVERRIDING SYSTEM VALUE is unavailable. See DDL — identity columns.

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

Import these files with psql in step 5, schema first. pg_dump’s output loads as written, including the ADD GENERATED ... AS IDENTITY statements it writes for identity columns, which keep their GENERATED ALWAYS or BY DEFAULT behaviour. 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(), but only the YYYY, MM, DD, HH24, MI, SS and MS (milliseconds, truncated) patterns are interpreted. MySQL’s %p (AM/PM), %b/%M (month name), %a/%W (day name), %I/%h (12-hour), %j (day-of-year) and %f (sub-second) have no working equivalent — MS is the only sub-second pattern DB9 has, so compare its output with what your queries produce today — and DB9 emits the unsupported pattern instead of raising an error — so these fail silently rather than failing your tests. Grep your queries for DATE_FORMAT and move any such formatting into application code. See TO_CHAR.
- String-to-date parsing — MySQL STR_TO_DATE() has no counterpart: TO_DATE() and TO_NUMBER() are unavailable (42883). Use casts — '2026-09-25'::date, '1234.56'::numeric.

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. DB9 enforces them on every path: a violating INSERT or DELETE raises 23503, TRUNCATE of a referenced table raises 0A000, and DROP TABLE of a referenced table raises 2BP01 unless you add CASCADE, which drops the referencing constraint with it.
Stored procedure differences — MySQL stored procedures must be rewritten in PL/pgSQL. DB9 supports basic PL/pgSQL, including CONTINUE, dynamic EXECUTE, exception handling and nested BEGIN ... END blocks, but not WHILE or FOREACH loops, or cursors.

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