Prisma Migrate Avoiding local db loss Steps I did 1 initial

Question

Prisma Migrate Avoiding local db loss Steps I did 1 initial schema model 2 `npx prisma migrate dev name init ` 3 threw an error saying `type citext does not exist` 4 `npx prisma migrate resolve rolled back xxxx init ` 5 it s rolled back 6 modified xxxx init migration sql by adding ```CREATE EXTENSION IF NOT EXISTS citext CREATE EXTENSION IF NOT EXISTS uuid ossp ``` 7 `npx prisma migrate deploy` 8 `All migrations have been successfully applied ` 9 added on column in one table in schema prisma 10 `npx prisma migrate dev name add type in flavor ` 11 It says ```The migration `xxxx init` was modified after it was applied We need to reset the PostgreSQL database postgres at localhost 5439 Do you want to continue All data will be lost ``` i feel like i did something wrong on around step 3 when I tried to resolve the failed migration How can I resolve this issue without losing data

Yunbo · Answer

Yunbo · Answer

< Casey Chow> it says ```Error P3008 The migration `xxxx init` is already recorded as applied in the database ```

Yunbo · Answer

< Casey Chow> i missed the first part what do you mean by `fix the migration state manually`

Casey Chow · Answer

like use a db admin tool to manually ensure it s in the right state

Yunbo · Answer

migration state meaning going to ` prisma migrations` table and update columns such as `finished at`

Casey Chow · Answer

no like the actual changes you wnated to make

Yunbo · Answer

it s already done step 7 8 i also went into db and confirmed citext extension was added

Casey Chow · Answer

then marking it as resolved is all you have left to do

Yunbo · Answer

i did `npx prisma migrate resolve applied xxxx init ` and it says ```Error P3008 The migration `xxxx init` is already recorded as applied in the database ``` interestingly when i went to ` prisma migrations` table and looked at the row it s `finished at` value was null which is strange logs in the table says ```A migration failed to apply New migrations cannot be applied before the error is recovered from Read more about how to resolve migration issues in a production database <https pris ly d migrate resolve> Migration name xxxx init Database error code 42704 Database error ERROR type citext does not exist Position 1m123 0m 1m124 0m CreateTable 1m125 0m CREATE TABLE accounts 1m126 0m id SERIAL NOT NULL 1m127 0m status id INTEGER NOT NULL DEFAULT 1 1m128 1 31m email CITEXT NOT NULL 0m DbError severity ERROR parsed severity Some Error code SqlState 42704 message type citext does not exist detail None hint None position Some Original 3654 where None schema None table None column None datatype None constraint None file Some parse type c line Some 274 routine Some typenameType 0 sql migration connector apply migration apply script with migration name= xxxx init at migration engine connectors sql migration connector src apply migration rs 104 1 migration core state ApplyMigrations at migration engine core src state rs 185```

Yunbo · Answer

i must have missed something here but generally i have a question about resolving failed migration when failed migration such as in this case i did step 4 ~ 7 are those right steps in this scenario

Casey Chow · Answer

what happens when you migrate

tornros · Answer

Remove it from prisma migrations smile

Yunbo · Answer

`npx prisma migrate dev` ```The migration `xxxx init` was modified after it was applied We need to reset the PostgreSQL database postgres at localhost 5439 Do you want to continue All data will be lost ```