Existing MySQL to Postgres

Naghell · 2025-01-30T00:06:46.643Z

Hi everyone! I'm planning to migrate my old MySQL database to PostgreSQL. I used pgloader to transfer all the data—no issues there. I also adjusted my schema to be compatible since there were some differences. However, when I run prisma migrate, I keep getting errors. The only solution I’ve found so far is to reset my migrations, but that would wipe all my data. Is there any way to avoid that? What should I do? Thanks in advance!

P

Prisma AI Help•1/30/25, 12:06 AM

You selected the bot's instantaneous insights. Let me compile your answer right away!

PPrisma AI Help You selected the bot's instantaneous insights. Let me compile your answer right ...

P

Prisma AI HelpAPP•1/30/25, 12:06 AM

Based on the knowledge sources provided, I can offer some guidance on how to handle your situation without resetting your database and losing data. This process is called "baselining" in Prisma terminology.

To avoid resetting your database and losing data when migrating from MySQL to PostgreSQL, you can follow these steps:

Create an initial migration that represents your current database state without applying it:

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

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

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

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

After this, run
```
npx prisma migrate dev
```
```
npx prisma migrate dev
```
. It should indicate that everything is in sync.
You should also manually add a lock file in
```
prisma/migrations/migration_lock.toml
```
prisma/migrations/migration_lock.toml
```
prisma/migrations/migration_lock.toml
```
prisma/migrations/migration_lock.toml
with the following content:

# Please do not edit this file manually
# It should be added in your version-control system (i.e. Git)
provider = "postgresql"

# Please do not edit this file manually
# It should be added in your version-control system (i.e. Git)
provider = "postgresql"

# Please do not edit this file manually
# It should be added in your version-control system (i.e. Git)
provider = "postgresql"

# Please do not edit this file manually
# It should be added in your version-control system (i.e. Git)
provider = "postgresql"

This approach allows you to baseline your existing PostgreSQL database without resetting it and losing data. It tells Prisma Migrate to assume that the initial migration has already been applied, so it won't try to create tables that already exist.

For more detailed information on baselining, you can refer to the Prisma documentation on baselining.

If you encounter any issues during this process or need further clarification, please don't hesitate to ask for more specific guidance.

Github Issue Comment

PPrisma AI Help Based on the knowledge sources provided, I can offer some guidance on how to han...

P

Prisma AI HelpAPP•1/30/25, 12:08 AM

Alright, thanks for letting us know that there's still some help needed. A team member will reply to you shortly.

In the meantime, please let us know any additional info, questions, or which parts of the answer may need clarification.

N

Nurul•1/30/25, 7:52 AM

What errors do you get?
Can you share them?

N

NaghellOP•1/30/25, 12:55 PM

The first step works fine, and I have no problem, but when I move on to the second one to apply the migration, it shows me this message again.

Drift detected: Your database schema is not in sync with your migration history.

The following is a summary of the differences between the expected database schema given your migrations files, and the actual schema of the database.

It should be understood as the set of changes to get from the expected schema to the actual schema.

? We need to reset the "dev" schema at "localhost:5432"
Do you want to continue? All data will be lost. » (y/N)

Drift detected: Your database schema is not in sync with your migration history.

The following is a summary of the differences between the expected database schema given your migrations files, and the actual schema of the database.

It should be understood as the set of changes to get from the expected schema to the actual schema.

? We need to reset the "dev" schema at "localhost:5432"
Do you want to continue? All data will be lost. » (y/N)

Drift detected: Your database schema is not in sync with your migration history.

The following is a summary of the differences between the expected database schema given your migrations files, and the actual schema of the database.

It should be understood as the set of changes to get from the expected schema to the actual schema.

? We need to reset the "dev" schema at "localhost:5432"
Do you want to continue? All data will be lost. » (y/N)

Drift detected: Your database schema is not in sync with your migration history.

The following is a summary of the differences between the expected database schema given your migrations files, and the actual schema of the database.

It should be understood as the set of changes to get from the expected schema to the actual schema.

? We need to reset the "dev" schema at "localhost:5432"
Do you want to continue? All data will be lost. » (y/N)

N

NaghellOP•1/30/25, 1:01 PM

And I also get several supposed changes.

model UserRole {
  userId    Int
  roleId    Int
  academyId Int     @default(1)
  user      User    @relation(fields: [userId], references: [id])
  role      Role    @relation(fields: [roleId], references: [id])
  academy   Academy @relation(fields: [academyId], references: [id])

  @@id([userId, roleId, academyId])
} 

[*] Changed the `UserRole` table
  [-] Removed foreign key on columns (academyId)
  [-] Removed foreign key on columns (roleId)
  [-] Removed foreign key on columns (userId)

model UserRole {
  userId    Int
  roleId    Int
  academyId Int     @default(1)
  user      User    @relation(fields: [userId], references: [id])
  role      Role    @relation(fields: [roleId], references: [id])
  academy   Academy @relation(fields: [academyId], references: [id])

  @@id([userId, roleId, academyId])
} 

[*] Changed the `UserRole` table
  [-] Removed foreign key on columns (academyId)
  [-] Removed foreign key on columns (roleId)
  [-] Removed foreign key on columns (userId)

model UserRole {
  userId    Int
  roleId    Int
  academyId Int     @default(1)
  user      User    @relation(fields: [userId], references: [id])
  role      Role    @relation(fields: [roleId], references: [id])
  academy   Academy @relation(fields: [academyId], references: [id])

  @@id([userId, roleId, academyId])
} 

[*] Changed the `UserRole` table
  [-] Removed foreign key on columns (academyId)
  [-] Removed foreign key on columns (roleId)
  [-] Removed foreign key on columns (userId)

model UserRole {
  userId    Int
  roleId    Int
  academyId Int     @default(1)
  user      User    @relation(fields: [userId], references: [id])
  role      Role    @relation(fields: [roleId], references: [id])
  academy   Academy @relation(fields: [academyId], references: [id])

  @@id([userId, roleId, academyId])
} 

[*] Changed the `UserRole` table
  [-] Removed foreign key on columns (academyId)
  [-] Removed foreign key on columns (roleId)
  [-] Removed foreign key on columns (userId)

N

NaghellOP•1/30/25, 1:06 PM

https://hastebin.com/share/tirimorisi.markdown

Hastebin

Hastebin is a free web-based pastebin service for storing and sharing text and code snippets with anyone. Get started now.

N

NaghellOP•1/30/25, 3:39 PM

I tried this: https://github.com/prisma/prisma/discussions/20025

Now I get this:

⚠️  Warnings for the current datasource:

  • You are about to drop the `academy` table, which is not empty (2 rows).
  • You are about to drop the `academyconfig` table, which is not empty (1 rows).
  • You are about to drop the `academydomain` table, which is not empty (1 rows).
  • You are about to drop the `academyseo` table, which is not empty (1 rows).
  • You are about to drop the `academystyle` table, which is not empty (1 rows).
  • You are about to drop the `audience` table, which is not empty (132 rows).
  • You are about to drop the `category` table, which is not empty (10 rows).
  • You are about to drop the `course` table, which is not empty (230 rows).
  • You are about to drop the `courseinscription` table, which is not empty (35 rows).
  • You are about to drop the `coursesubcategory` table, which is not empty (163 rows).
  • You are about to drop the `event` table, which is not empty (328 rows).
  • You are about to drop the `lead` table, which is not empty (225 rows).
  • You are about to drop the `milestone` table, which is not empty (1687 rows).
  • You are about to drop the `paymentintent` table, which is not empty (16 rows).
  • You are about to drop the `price` table, which is not empty (126 rows).
  • You are about to drop the `role` table, which is not empty (6 rows).
  • You are about to drop the `subcategory` table, which is not empty (17 rows).
  • You are about to drop the `user` table, which is not empty (221 rows).
  • You are about to drop the `usercourse` table, which is not empty (187 rows).
  • You are about to drop the `userrole` table, which is not empty (235 rows).

√ Are you sure you want to create and apply this migration? ...

⚠️  Warnings for the current datasource:

  • You are about to drop the `academy` table, which is not empty (2 rows).
  • You are about to drop the `academyconfig` table, which is not empty (1 rows).
  • You are about to drop the `academydomain` table, which is not empty (1 rows).
  • You are about to drop the `academyseo` table, which is not empty (1 rows).
  • You are about to drop the `academystyle` table, which is not empty (1 rows).
  • You are about to drop the `audience` table, which is not empty (132 rows).
  • You are about to drop the `category` table, which is not empty (10 rows).
  • You are about to drop the `course` table, which is not empty (230 rows).
  • You are about to drop the `courseinscription` table, which is not empty (35 rows).
  • You are about to drop the `coursesubcategory` table, which is not empty (163 rows).
  • You are about to drop the `event` table, which is not empty (328 rows).
  • You are about to drop the `lead` table, which is not empty (225 rows).
  • You are about to drop the `milestone` table, which is not empty (1687 rows).
  • You are about to drop the `paymentintent` table, which is not empty (16 rows).
  • You are about to drop the `price` table, which is not empty (126 rows).
  • You are about to drop the `role` table, which is not empty (6 rows).
  • You are about to drop the `subcategory` table, which is not empty (17 rows).
  • You are about to drop the `user` table, which is not empty (221 rows).
  • You are about to drop the `usercourse` table, which is not empty (187 rows).
  • You are about to drop the `userrole` table, which is not empty (235 rows).

√ Are you sure you want to create and apply this migration? ...

⚠️  Warnings for the current datasource:

  • You are about to drop the `academy` table, which is not empty (2 rows).
  • You are about to drop the `academyconfig` table, which is not empty (1 rows).
  • You are about to drop the `academydomain` table, which is not empty (1 rows).
  • You are about to drop the `academyseo` table, which is not empty (1 rows).
  • You are about to drop the `academystyle` table, which is not empty (1 rows).
  • You are about to drop the `audience` table, which is not empty (132 rows).
  • You are about to drop the `category` table, which is not empty (10 rows).
  • You are about to drop the `course` table, which is not empty (230 rows).
  • You are about to drop the `courseinscription` table, which is not empty (35 rows).
  • You are about to drop the `coursesubcategory` table, which is not empty (163 rows).
  • You are about to drop the `event` table, which is not empty (328 rows).
  • You are about to drop the `lead` table, which is not empty (225 rows).
  • You are about to drop the `milestone` table, which is not empty (1687 rows).
  • You are about to drop the `paymentintent` table, which is not empty (16 rows).
  • You are about to drop the `price` table, which is not empty (126 rows).
  • You are about to drop the `role` table, which is not empty (6 rows).
  • You are about to drop the `subcategory` table, which is not empty (17 rows).
  • You are about to drop the `user` table, which is not empty (221 rows).
  • You are about to drop the `usercourse` table, which is not empty (187 rows).
  • You are about to drop the `userrole` table, which is not empty (235 rows).

√ Are you sure you want to create and apply this migration? ...

⚠️  Warnings for the current datasource:

  • You are about to drop the `academy` table, which is not empty (2 rows).
  • You are about to drop the `academyconfig` table, which is not empty (1 rows).
  • You are about to drop the `academydomain` table, which is not empty (1 rows).
  • You are about to drop the `academyseo` table, which is not empty (1 rows).
  • You are about to drop the `academystyle` table, which is not empty (1 rows).
  • You are about to drop the `audience` table, which is not empty (132 rows).
  • You are about to drop the `category` table, which is not empty (10 rows).
  • You are about to drop the `course` table, which is not empty (230 rows).
  • You are about to drop the `courseinscription` table, which is not empty (35 rows).
  • You are about to drop the `coursesubcategory` table, which is not empty (163 rows).
  • You are about to drop the `event` table, which is not empty (328 rows).
  • You are about to drop the `lead` table, which is not empty (225 rows).
  • You are about to drop the `milestone` table, which is not empty (1687 rows).
  • You are about to drop the `paymentintent` table, which is not empty (16 rows).
  • You are about to drop the `price` table, which is not empty (126 rows).
  • You are about to drop the `role` table, which is not empty (6 rows).
  • You are about to drop the `subcategory` table, which is not empty (17 rows).
  • You are about to drop the `user` table, which is not empty (221 rows).
  • You are about to drop the `usercourse` table, which is not empty (187 rows).
  • You are about to drop the `userrole` table, which is not empty (235 rows).

√ Are you sure you want to create and apply this migration? ...

GitHub

Drift detected on first migration after using migrate diff on exist...

Bug description I am attempting to add Prisma 4.16 to an existing project (it used to use Prisma 2.12 ) on a MacBook Pro with an M1 Pro chip. During the upgrade process, I am encountering an issue ...