Migrate: Lift overshoots with the database steps

[luhagel]

EDIT: Version preview-6.1

Heya, what I have been noticing is that lift currently alters close to every table in the book, even for small and completly unrelated schema changes, dropping and readding columns left and right,which tends to break a lot of stuff with every migration.

Given a steps.json like this:

{
  "version": "0.1.29",
  "steps": [
    {
      "stepType": "UpdateField",
      "model": "User",
      "name": "assessedModules",
      "arity": "list"
    }
  ]
}

We get this relevant part of the migration README.md

Database Steps

ALTER TABLE "Public"."ModuleHandbook" DROP COLUMN "studyProgram",ADD COLUMN "studyProgram" text   REFERENCES "Public"."StudyProgram"("id") ON DELETE SET NULL;

ALTER TABLE "Public"."UserDocument" DROP COLUMN "user",ADD COLUMN "user" text   REFERENCES "Public"."User"("id") ON DELETE SET NULL;

ALTER TABLE "Public"."ModuleHandIn" DROP COLUMN "semesterModule",ADD COLUMN "semesterModule" text   REFERENCES "Public"."SemesterModule"("id") ON DELETE SET NULL;

ALTER TABLE "Public"."MentoringLog" DROP COLUMN "mentoringRelationship",ADD COLUMN "mentoringRelationship" text   REFERENCES "Public"."MentoringRelationship"("id") ON DELETE SET NULL;

ALTER TABLE "Public"."ResourceDocument" DROP COLUMN "learningResource",ADD COLUMN "learningResource" text   REFERENCES "Public"."LearningResource"("id") ON DELETE SET NULL;

ALTER TABLE "Public"."ProjectMembership" DROP COLUMN "student",ADD COLUMN "student" text   REFERENCES "Public"."User"("id") ON DELETE SET NULL,DROP COLUMN "project",ADD COLUMN "project" text   REFERENCES "Public"."Project"("id") ON DELETE SET NULL;

ALTER TABLE "Public"."ProjectOutline" DROP COLUMN "professor",ADD COLUMN "professor" text   REFERENCES "Public"."User"("id") ON DELETE SET NULL,DROP COLUMN "budgetFairy",ADD COLUMN "budgetFairy" text   REFERENCES "Public"."User"("id") ON DELETE SET NULL;

ALTER TABLE "Public"."_StarredResourcesRelation" DROP COLUMN "A",ADD COLUMN "A" text   REFERENCES "Public"."LearningResource"("id") ON DELETE CASCADE,DROP COLUMN "B",ADD COLUMN "B" text   REFERENCES "Public"."User"("id") ON DELETE CASCADE;

ALTER TABLE "Public"."_SignedOutMemberToEvent" DROP COLUMN "A",ADD COLUMN "A" text   REFERENCES "Public"."Event"("id") ON DELETE CASCADE,DROP COLUMN "B",ADD COLUMN "B" text   REFERENCES "Public"."User"("id") ON DELETE CASCADE;

ALTER TABLE "Public"."_ProfessorToDepartment" DROP COLUMN "A",ADD COLUMN "A" text   REFERENCES "Public"."Department"("id") ON DELETE CASCADE,DROP COLUMN "B",ADD COLUMN "B" text   REFERENCES "Public"."User"("id") ON DELETE CASCADE;

ALTER TABLE "Public"."_DepartmentToLearningResources" DROP COLUMN "A",ADD COLUMN "A" text   REFERENCES "Public"."Department"("id") ON DELETE CASCADE,DROP COLUMN "B",ADD COLUMN "B" text   REFERENCES "Public"."LearningResource"("id") ON DELETE CASCADE;

ALTER TABLE "Public"."_LecturerToDepartment" DROP COLUMN "A",ADD COLUMN "A" text   REFERENCES "Public"."Department"("id") ON DELETE CASCADE,DROP COLUMN "B",ADD COLUMN "B" text   REFERENCES "Public"."User"("id") ON DELETE CASCADE;

ALTER TABLE "Public"."_StarredProjectsRelation" DROP COLUMN "A",ADD COLUMN "A" text   REFERENCES "Public"."Project"("id") ON DELETE CASCADE,DROP COLUMN "B",ADD COLUMN "B" text   REFERENCES "Public"."User"("id") ON DELETE CASCADE;

ALTER TABLE "Public"."_EventGroupToParticipant" DROP COLUMN "A",ADD COLUMN "A" text   REFERENCES "Public"."EventGroup"("id") ON DELETE CASCADE,DROP COLUMN "B",ADD COLUMN "B" text   REFERENCES "Public"."User"("id") ON DELETE CASCADE;

ALTER TABLE "Public"."_WorkingStudentToDepartment" DROP COLUMN "A",ADD COLUMN "A" text   REFERENCES "Public"."Department"("id") ON DELETE CASCADE,DROP COLUMN "B",ADD COLUMN "B" text   REFERENCES "Public"."User"("id") ON DELETE CASCADE;

ALTER TABLE "Public"."Module" DROP COLUMN "department",ADD COLUMN "department" text   REFERENCES "Public"."Department"("id") ON DELETE SET NULL,DROP COLUMN "coordinator",ADD COLUMN "coordinator" text   REFERENCES "Public"."User"("id") ON DELETE SET NULL;

ALTER TABLE "Public"."_AssistantToAssessment" DROP COLUMN "A",ADD COLUMN "A" text   REFERENCES "Public"."Assessment"("id") ON DELETE CASCADE,DROP COLUMN "B",ADD COLUMN "B" text   REFERENCES "Public"."User"("id") ON DELETE CASCADE;

ALTER TABLE "Public"."_ElectiveBlockToModuleHandbookModule" DROP COLUMN "A",ADD COLUMN "A" text   REFERENCES "Public"."ModuleHandbookModule"("id") ON DELETE CASCADE,DROP COLUMN "B",ADD COLUMN "B" text   REFERENCES "Public"."ModuleHandbookModule"("id") ON DELETE CASCADE;

ALTER TABLE "Public"."LearningResource" DROP COLUMN "submittedBy",ADD COLUMN "submittedBy" text   REFERENCES "Public"."User"("id") ON DELETE SET NULL;

ALTER TABLE "Public"."_PrerequisiteModuleToModule" DROP COLUMN "A",ADD COLUMN "A" text   REFERENCES "Public"."Module"("id") ON DELETE CASCADE,DROP COLUMN "B",ADD COLUMN "B" text   REFERENCES "Public"."Module"("id") ON DELETE CASCADE;

ALTER TABLE "Public"."_LearningResourceTags" DROP COLUMN "A",ADD COLUMN "A" text   REFERENCES "Public"."LearningResource"("id") ON DELETE CASCADE,DROP COLUMN "B",ADD COLUMN "B" text   REFERENCES "Public"."Tag"("id") ON DELETE CASCADE;

ALTER TABLE "Public"."_EventGroupToSemesterModule" DROP COLUMN "A",ADD COLUMN "A" text   REFERENCES "Public"."EventGroup"("id") ON DELETE CASCADE,DROP COLUMN "B",ADD COLUMN "B" text   REFERENCES "Public"."SemesterModule"("id") ON DELETE CASCADE;

ALTER TABLE "Public"."StudyProgramEnrollment" DROP COLUMN "student",ADD COLUMN "student" text   REFERENCES "Public"."User"("id") ON DELETE SET NULL,DROP COLUMN "studyProgram",ADD COLUMN "studyProgram" text   REFERENCES "Public"."StudyProgram"("id") ON DELETE SET NULL;

ALTER TABLE "Public"."_ProjectIdeaToTags" DROP COLUMN "A",ADD COLUMN "A" text   REFERENCES "Public"."ProjectIdea"("id") ON DELETE CASCADE,DROP COLUMN "B",ADD COLUMN "B" text   REFERENCES "Public"."Tag"("id") ON DELETE CASCADE;

ALTER TABLE "Public"."_ResourceToSemesterModule" DROP COLUMN "A",ADD COLUMN "A" text   REFERENCES "Public"."LearningResource"("id") ON DELETE CASCADE,DROP COLUMN "B",ADD COLUMN "B" text   REFERENCES "Public"."SemesterModule"("id") ON DELETE CASCADE;

ALTER TABLE "Public"."_PreliminaryProjectModuleChoices" DROP COLUMN "A",ADD COLUMN "A" text   REFERENCES "Public"."ProjectMembership"("id") ON DELETE CASCADE,DROP COLUMN "B",ADD COLUMN "B" text   REFERENCES "Public"."SemesterModule"("id") ON DELETE CASCADE;

ALTER TABLE "Public"."ModuleHandbookModule" DROP COLUMN "moduleHandbook",ADD COLUMN "moduleHandbook" text   REFERENCES "Public"."ModuleHandbook"("id") ON DELETE SET NULL,DROP COLUMN "module",ADD COLUMN "module" text   REFERENCES "Public"."Module"("id") ON DELETE SET NULL;

ALTER TABLE "Public"."_ProjectToTags" DROP COLUMN "A",ADD COLUMN "A" text   REFERENCES "Public"."ProjectArchive"("id") ON DELETE CASCADE,DROP COLUMN "B",ADD COLUMN "B" text   REFERENCES "Public"."Tag"("id") ON DELETE CASCADE;

ALTER TABLE "Public"."_EventGroupToLearningResource" DROP COLUMN "A",ADD COLUMN "A" text   REFERENCES "Public"."EventGroup"("id") ON DELETE CASCADE,DROP COLUMN "B",ADD COLUMN "B" text   REFERENCES "Public"."LearningResource"("id") ON DELETE CASCADE;

ALTER TABLE "Public"."StudentSemester" DROP COLUMN "student",ADD COLUMN "student" text   REFERENCES "Public"."User"("id") ON DELETE SET NULL,DROP COLUMN "semester",ADD COLUMN "semester" text   REFERENCES "Public"."Semester"("id") ON DELETE SET NULL,DROP COLUMN "studyProgramEnrollment",ADD COLUMN "studyProgramEnrollment" text   REFERENCES "Public"."StudyProgramEnrollment"("id") ON DELETE SET NULL;

ALTER TABLE "Public"."StudentModuleHandbook" DROP COLUMN "student",ADD COLUMN "student" text   REFERENCES "Public"."User"("id") ON DELETE SET NULL,DROP COLUMN "moduleHandbook",ADD COLUMN "moduleHandbook" text   REFERENCES "Public"."ModuleHandbook"("id") ON DELETE SET NULL;

ALTER TABLE "Public"."Assessment" DROP COLUMN "user",ADD COLUMN "user" text   REFERENCES "Public"."User"("id") ON DELETE SET NULL,DROP COLUMN "assessor",ADD COLUMN "assessor" text   REFERENCES "Public"."User"("id") ON DELETE SET NULL,DROP COLUMN "semester",ADD COLUMN "semester" text   REFERENCES "Public"."Semester"("id") ON DELETE SET NULL,DROP COLUMN "module",ADD COLUMN "module" text   REFERENCES "Public"."Module"("id") ON DELETE SET NULL,DROP COLUMN "semesterModule",ADD COLUMN "semesterModule" text   REFERENCES "Public"."SemesterModule"("id") ON DELETE SET NULL,DROP COLUMN "event",ADD COLUMN "event" text   REFERENCES "Public"."Event"("id") ON DELETE SET NULL;

ALTER TABLE "Public"."EventGroup" DROP COLUMN "host",ADD COLUMN "host" text   REFERENCES "Public"."User"("id") ON DELETE SET NULL;

ALTER TABLE "Public"."ModuleUpdate" DROP COLUMN "author",ADD COLUMN "author" text   REFERENCES "Public"."User"("id") ON DELETE SET NULL;

ALTER TABLE "Public"."ProjectIdea" DROP COLUMN "semester",ADD COLUMN "semester" text   REFERENCES "Public"."Semester"("id") ON DELETE SET NULL;

ALTER TABLE "Public"."Project" DROP COLUMN "originator",ADD COLUMN "originator" text   REFERENCES "Public"."User"("id") ON DELETE SET NULL;

ALTER TABLE "Public"."EventAttendance" DROP COLUMN "attendee",ADD COLUMN "attendee" text   REFERENCES "Public"."User"("id") ON DELETE SET NULL,DROP COLUMN "event",ADD COLUMN "event" text   REFERENCES "Public"."Event"("id") ON DELETE SET NULL;

ALTER TABLE "Public"."Notification" DROP COLUMN "user",ADD COLUMN "user" text   REFERENCES "Public"."User"("id") ON DELETE SET NULL;

ALTER TABLE "Public"."MentoringRelationship" DROP COLUMN "mentee",ADD COLUMN "mentee" text   REFERENCES "Public"."User"("id") ON DELETE SET NULL,DROP COLUMN "mentor",ADD COLUMN "mentor" text   REFERENCES "Public"."User"("id") ON DELETE SET NULL;

ALTER TABLE "Public"."LearningJourneyEntry" DROP COLUMN "user",ADD COLUMN "user" text   REFERENCES "Public"."User"("id") ON DELETE SET NULL;

ALTER TABLE "Public"."SemesterModule" DROP COLUMN "coordinator",ADD COLUMN "coordinator" text   REFERENCES "Public"."User"("id") ON DELETE SET NULL,DROP COLUMN "semester",ADD COLUMN "semester" text   REFERENCES "Public"."Semester"("id") ON DELETE SET NULL,DROP COLUMN "module",ADD COLUMN "module" text   REFERENCES "Public"."Module"("id") ON DELETE SET NULL;

Changes

diff --git datamodel.mdl datamodel.mdl
migration 20190813095856-squished-migration..20190813142222-fix-assess-modules-cardinality
--- datamodel.dml
+++ datamodel.dml
@@ -91,9 +91,9 @@
   programHeadFor             Department?              @relation("ProgramHeadToDepartment")
   professorFor               Department[]             @relation("ProfessorToDepartment")
   lecturerFor                Department[]             @relation("LecturerToDepartment")
   workingStudentFor          Department[]             @relation("WorkingStudentToDepartment")
-  assessedModules            SemesterModule?          @relation("SemesterModuleToPrimaryAssessor")
+  assessedModules            SemesterModule[]         @relation("SemesterModuleToPrimaryAssessor")
   assessments                Assessment[]             @relation("UserToAssessment")
   assessedAssessments        Assessment[]             @relation("AssessorToAssessment")
   assistedAssessments        Assessment[]             @relation("AssistantToAssessment")
   learningJourney            LearningJourneyEntry[]

From the looks of it, instead of just altering the required column, every single relation table/column containing either an semesterModule or user was dropped and then readded, loosing all data in the process.

[timsuchanek]

@pantharshit00 is there a complete reproduction?

[pantharshit00]

@timsuchanek Added a more detailed reproduction with steps here:
https://github.com/harshit-test-org/prisma-lift-100

[robmurtagh]

Running into the same issue here. Have an operation which should be essentially:

ALTER TABLE `platform`.`status` ADD COLUMN `new_field` varchar(191)

but instead ALTERs every table having a relation to that table...

This ends up in errors such as:

Error: QueryError(QueryError(QueryError(MySqlError { ERROR 1089 (HY000): Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys }

We're using [email protected], binary version: 6146c81a771bef78248317cef9e37fc00863dc08

Do you know what is going on here?

[mavilein]

@luhagel : Thanks for the detailed description! 🙏 Can you also provide the datamodel you started with please? Right now i can't reproduce it.

[mavilein]

I think i found the reason for this behavior. I have just pushed a fix which will be available on alpha in a few minutes. Let me know if that works for you. If not we will reopen this issue again.

[luhagel]

Fix works like a charm, thanks a lot!