Migration containing DML statements hangs mysteriously
Hi, I'm changing the shape of my schema but wanted to preserve existing user data, which is why I wrote a bunch of queries to shape the data into the new format. I created this by using
npx prisma migrate --create-only then editing the file to place my data processing steps in between the DDL changes.
When I try applying it, it apparently executes the changes on the database, but the migrate command keeps hanging at this step, so I can't trust it to run correctly on my production build.
Could someone from the team let me know if prisma supports running these non DDL statements on migrations or not? If not, I'll have to add a temporary table and run the data processing migration queries by hand when deploying to production, something I'd like to avoid if possible.7 Replies
Ok, apparently the migration executed successfully when running without the DEBUG flag. I'd still like confirmation from someone to be sure this is supported and risk free
Ok, actually it seems to work or hang intermittently...
I'll need to bump this... does anyone know if this is supported by prisma migrations or not?
Prisma may have some logic before/after, but I'm pretty sure it does just run the SQL file.
I've used prisma migrations for modifying data between schema changes, for example renaming a TYPE value by 1. adding a new possible value to the TYPE, 2. updating data to use the new value, 3. removing the old value from the TYPE https://github.com/amelioro/ameliorate/blob/3506c2aa1a52112c2d9197f2c38e16cf59102721/src/db/migrations/20240927095951_rename_embodies_to_fulfills/migration.sql#L10
Have you tried running that SQL directly against your test db without prisma? To see if it's the SQL itself that's hanging
Thank you! This is the sort of confirmation I was looking for. I was worried there could be some underlying limitation to using DML queries since I don't know how the migration engine works, but if you've used them like this I think I can trust it and try it out on my preview/QA database.
I've tried running the SQL directly on the the database while I was testing it, but I did it in a "statement by statement" basis and not the entire script at once, so this could be related. It works, it even works through prisma sometimes (I tried running it, then manually running a "reverse" migration multiple times), but through prisma it's not deterministic.
I think it hanging could be just an artifact of repeatedly running it and reversing it on my development machine since I've found that restarting the PC can make it run successfully (maybe unknown state on the database or the prisma migration rust service).
Hey @Giovani Granzotto 👋
You can write non DDL statements in the migration file. As long as the SQL is valid, it will run fine
Thanks for the confirmation, Nurul. I deployed to vercel and it did not hang at all 👍
I am glad to hear that 🙌