Prisma.js: We found changes that cannot be executed

Question

I've used prisma.js as an ORM in my project.

After executing the npx prisma migrate dev --name rename_and_add_some_columns, I got this error:

We found changes that cannot be executed

Error Details:

Step 1 Added the required column CategoryId to the Post table without a default value. There are 2 rows in this table, it is not possible to execute this step. • Step 1 Added the required column ModifiedDate to the Post table without a default value. There are 2 rows in this table, it is not possible to execute this step. • Step 2 Added the required column ModifiedDate to the Profile table without a default value. There are 1 rows in this table, it is not possible to execute this step. • Step 4 Added the required column ModifiedDate to the User table without a default value. There are 2 rows in this table, it is not possible to execute this step.

You can use prisma migrate dev --create-only to create the migration file, and manually modify it to address the underlying issue(s). Then run prisma migrate dev to apply it and verify it works.

How can I solve it?

// This is my Prisma schema file,

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model Category {
  Id           Int      @id @default(autoincrement())
  CreatedDate  DateTime @default(now())
  ModifiedDate DateTime @updatedAt
  Title        String   @db.VarChar(50)
  IsActive     Boolean
  Posts        Post[]
}

model Post {
  Id                 Int       @id @default(autoincrement())
  CreatedDate        DateTime  @default(now())
  ModifiedDate       DateTime  @updatedAt
  Title              String    @db.VarChar(255)
  Description        String?
  IsPublished        Boolean   @default(false)
  IsActive           Boolean   @default(true)
  IsActiveNewComment Boolean   @default(true)
  Author             User      @relation(fields: [AuthorId], references: [Id])
  AuthorId           Int
  Comment            Comment[]
  Tag                Tag[]     @relation("TagToPost", fields: [tagId], references: [Id])
  tagId              Int?
  Category           Category  @relation(fields: [CategoryId], references: [Id])
  CategoryId         Int
}

model User {
  Id           Int       @id @default(autoincrement())
  CreatedDate  DateTime  @default(now())
  ModifiedDate DateTime  @updatedAt
  Email        String    @unique
  Name         String?
  Posts        Post[]
  Profile      Profile?
  Comments     Comment[]
}

model Profile {
  Id           Int      @id @default(autoincrement())
  CreatedDate  DateTime @default(now())
  ModifiedDate DateTime @updatedAt
  Bio          String?
  User         User     @relation(fields: [UserId], references: [Id])
  UserId       Int      @unique
}

model Comment {
  Id           Int      @id @default(autoincrement())
  CreatedDate  DateTime @default(now())
  ModifiedDate DateTime @updatedAt
  Comment      String
  WrittenBy    User     @relation(fields: [WrittenById], references: [Id])
  WrittenById  Int
  Post         Post     @relation(fields: [PostId], references: [Id])
  PostId       Int
}

model Tag {
  Id           Int      @id @default(autoincrement())
  CreatedDate  DateTime @default(now())
  ModifiedDate DateTime @updatedAt
  Title        String   @unique
  Posts        Post[]   @relation("TagToPost")
}

Answer 1

In order to run this migration, you need to:

1. Create the fields first as optional and then run migrate

2. Fill the fields first with the required date.

3. Remove the optional (?) from the field.

Prisma automatically adds @updatedAt (it's not done at the database level) so these steps need to be followed.

Answer 2

Alternatively, you can just add @default(now()) to your ModifiedDate properties. So, for instance, the Category model would be:

model Category {
  Id           Int      @id @default(autoincrement())
  CreatedDate  DateTime @default(now())
  ModifiedDate DateTime @default(now()) @updatedAt
  Title        String   @db.VarChar(50)
  IsActive     Boolean
  Posts        Post[]
}

Answer 3

In thePost model, I changed the Category to Category? and Int to Int?

Also, I changed Datetime in ModifiedDate to Datetime?

model Category {
  Id           Int       @id @default(autoincrement())
  CreatedDate  DateTime  @default(now())
  ModifiedDate DateTime? @updatedAt
  Title        String    @db.VarChar(50)
  IsActive     Boolean
  Posts        Post[]
}

model Post {
  Id                 Int       @id @default(autoincrement())
  CreatedDate        DateTime  @default(now())
  ModifiedDate       DateTime? @updatedAt
  Title              String    @db.VarChar(255)
  Description        String?
  IsPublished        Boolean   @default(false)
  IsActive           Boolean   @default(true)
  IsActiveNewComment Boolean   @default(true)
  Author             User      @relation(fields: [AuthorId], references: [Id])
  AuthorId           Int
  Comment            Comment[]
  Tag                Tag[]     @relation("TagToPost", fields: [tagId], references: [Id])
  tagId              Int?
  Category           Category? @relation(fields: [CategoryId], references: [Id])
  CategoryId         Int?
}

model User {
  Id           Int       @id @default(autoincrement())
  CreatedDate  DateTime  @default(now())
  ModifiedDate DateTime? @updatedAt
  Email        String    @unique
  Name         String?
  Posts        Post[]
  Profile      Profile?
  Comments     Comment[]
}

model Profile {
  Id           Int       @id @default(autoincrement())
  CreatedDate  DateTime  @default(now())
  ModifiedDate DateTime? @updatedAt
  Bio          String?
  User         User      @relation(fields: [UserId], references: [Id])
  UserId       Int       @unique
}

model Comment {
  Id           Int       @id @default(autoincrement())
  CreatedDate  DateTime  @default(now())
  ModifiedDate DateTime? @updatedAt
  Comment      String
  WrittenBy    User      @relation(fields: [WrittenById], references: [Id])
  WrittenById  Int
  Post         Post      @relation(fields: [PostId], references: [Id])
  PostId       Int
}

model Tag {
  Id           Int       @id @default(autoincrement())
  CreatedDate  DateTime  @default(now())
  ModifiedDate DateTime? @updatedAt
  Title        String    @unique
  Posts        Post[]    @relation("TagToPost")
}

Answer 4

I retroactively added the createdAt and updatedAt fields, and I don't want to provide an updatedAt value for existing fields, but I can live with the fact that now() will become the default createdAt for existing values.

Add a ? question mark behind the DateTime? of the updatedAt field, making it optional. The field will be null by default when you migrate your schema but will populate as expected on subsequent updates of the row.

model MyModel {
...
    createdAt   DateTime  @default(now())
    updatedAt   DateTime? @updatedAt
}