Switch Prisma from MySQL to PostgreSQL

Flipping provider = "mysql" to "postgresql" is a one-line edit — but Prisma will refuse to generate if your schema still carries MySQL-only native types, and your existing migration history is written in MySQL SQL that PostgreSQL cannot replay. Here is the order that gets you a clean prisma migrate on the other side.

1. Point the datasource at PostgreSQL

In prisma/schema.prisma, change the provider and update the connection string format. PostgreSQL URLs use port 5432 and a schema parameter:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

Then in .env:

# before (MySQL)
DATABASE_URL="mysql://user:pass@localhost:3306/mydb"
# after (PostgreSQL)
DATABASE_URL="postgresql://user:pass@localhost:5432/mydb?schema=public"

2. Remove MySQL-only native types

If you added @db.* attributes, several are MySQL-specific and have no PostgreSQL equivalent. npx prisma validate will fail until they are gone. The usual culprits:

@db.UnsignedInt / @db.UnsignedBigInt — PostgreSQL has no unsigned integers. Drop the attribute and use a plain Int / BigInt.
@db.TinyInt — for a Boolean field, just remove it; Prisma maps Boolean to a real PostgreSQL boolean.
@db.MediumInt, @db.Year — no PostgreSQL counterpart; remove them.
@db.Text and @db.VarChar(n) are valid on both — those you can keep.

Run npx prisma validate and fix every reported type before continuing. Prisma enum blocks need no change — they map to native PostgreSQL enum types automatically.

3. Reset the migration history

The SQL files in prisma/migrations/ were generated for MySQL (backticks, AUTO_INCREMENT, engine clauses) and will error if PostgreSQL tries to apply them. For a fresh database, archive the old history and create one clean baseline migration:

# keep a copy, then start clean
mv prisma/migrations prisma/migrations.mysql.bak

# generate a fresh PostgreSQL migration + apply it
npx prisma migrate dev --name init

If you are keeping an existing PostgreSQL database whose tables already match the schema, baseline it instead so Prisma doesn't try to recreate everything:

mkdir -p prisma/migrations/0_init
npx prisma migrate diff \
  --from-empty --to-schema-datamodel prisma/schema.prisma \
  --script > prisma/migrations/0_init/migration.sql
npx prisma migrate resolve --applied 0_init

4. Regenerate the client

npx prisma generate

The generated client is database-aware, so regenerating after the provider change is required — otherwise queries still target the MySQL shape and fail at runtime.

5. Move the data — Prisma won't

There is no Prisma command that copies rows between engines. Take a SQL dump from MySQL and convert the dialect (type mappings, AUTO_INCREMENT → sequences, backtick quoting):

mysqldump --single-transaction --no-tablespaces \
  -u root -p mydb > mydb.sql

Convert mydb.sql to PostgreSQL, load it with psql -d mydb -f converted.sql, and your existing IDs and foreign keys come across intact. Do this against the database after the baseline migration so the schema lines up.

6. The runtime gotchas that bite after cutover

Case-sensitive text. MySQL's default collation made a findUnique on email: "[email protected]" match [email protected]; PostgreSQL will not. Normalise emails on write, or use a citext column.
Sequences, not AUTO_INCREMENT. If you imported rows with explicit IDs, the sequence behind an @default(autoincrement()) column is now behind reality and the next insert collides. Reset each one with SELECT setval(pg_get_serial_sequence('"User"', 'id'), (SELECT MAX(id) FROM "User"));
Stricter SQL. PostgreSQL rejects invalid dates like 0000-00-00 that MySQL accepted. Scrub those rows before importing.

Finally, run npx prisma migrate status (should show everything applied) and your test suite against PostgreSQL before pointing production at the new DATABASE_URL.

Skip the dialect rewrite — convert your MySQL dump to PostgreSQL now →

Chat with us