marsh wave
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!
velvet scarabBOT
You selected the bot's instantaneous insights. Let me compile your answer right away!
velvet scarabBOT
You selected the bot's instantaneous insights. Let me compile your answer right ...
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:
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. It should indicate that everything is in sync.
You should also manually add a lock file in 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"
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.
velvet scarabBOT
Based on the knowledge sources provided, I can offer some guidance on how to han...
🤔 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.
sleek field
What errors do you get?
Can you share them?
marsh wave
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.
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)```And I also get several supposed changes.
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)