How do I resolve Prisma ORM drift after making values non nullable with CockroachDB? [closed]

Question

Closed. This question is not about programming or software development. It is not currently accepting answers.

This question does not appear to be about a specific programming problem, a software algorithm, or software tools primarily used by programmers. If you believe the question would be on-topic on another Stack Exchange site, you can leave a comment to explain where the question may be able to be answered.

Closed 4 months ago.

Improve this question

My database is CockroachDB, my Prisma version is 6.11.1.

I added these two models:

model Font {
  id         String        @id @default(uuid())
  name       String        @unique
  created_at DateTime      @default(now())
  deleted_at DateTime?
  variants   FontVariant[] @relation("FontVariants")
}

model FontVariant {
  id           String    @id @default(uuid())
  name         String
  font_id      String
  created_at   DateTime  @default(now())
  deleted_at   DateTime?
  file_link_id String    @unique
  file_link    FileLink  @relation("FontFileLink", fields: [file_link_id], references: [id])
  font         Font      @relation("FontVariants", fields: [font_id], references: [id])
}

Initially, file_link_id and file_link were nullable. My next migration made those fields required. Migration was a success. However, then I added some fields. This is my new schema:

model Font {
  id              String        @id @default(uuid())
  name            String
  variants        FontVariant[] @relation("FontVariants")
  created_at      DateTime      @default(now())
  deleted_at      DateTime?
  owner_id        String
  visibility      Visibility    @default(INTERNAL)
  organization_id String?

  @@unique([name, owner_id])
  @@unique([name, organization_id])
  @@index([owner_id])
}

model FontVariant {
  id           String    @id @default(uuid())
  name         String    @unique
  font_id      String
  font         Font      @relation(fields: [font_id], references: [id], name: "FontVariants")
  created_at   DateTime  @default(now())
  deleted_at   DateTime?
  file_link_id String?   @unique
  file_link    FileLink? @relation(fields: [file_link_id], references: [id], name: "FontFileLink")
}

And then I got this error:

- Drift detected: Your database schema is not in sync with your migration history.

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.

[*] Changed the `FontVariant` table
  [*] Altered column `file_link_id` (changed from Nullable to Required)

This is my most recent successful migration, 20250714220438_font_file_link_is_no_longer_optional, the one I believe is causing the problem:

/*
 Warnings:


 - Made the column `file_link_id` on table `FontVariant` required. This step will fail if there are existing NULL values in that column.


*/
-- DropForeignKey
ALTER TABLE "FontVariant" DROP CONSTRAINT "FontVariant_file_link_id_fkey";


-- AlterTable
ALTER TABLE "FontVariant" ALTER COLUMN "file_link_id" SET NOT NULL;


-- AddForeignKey
ALTER TABLE "FontVariant" ADD CONSTRAINT "FontVariant_file_link_id_fkey" FOREIGN KEY ("file_link_id") REFERENCES "FileLink"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

So far, I tried this command: prisma migrate diff, I also tried marking the migrations as applied.

Resetting is not an option. How can I resolve this?

Jeff Swenson · Accepted Answer · 2025-07-16 14:10:29Z

0

It is possible the schema change failed to apply because there are NULL values in the FontVariant table. You can check for this condition via a SQL query

SELECT count(\*) AS null_count  
FROM   "FontVariant"  
WHERE  file_link_id IS NULL;

If the query returns a non-zero NULL count. You will need to find the rows with null file_link_id's and repair them by adding a file like or deleting the row.

Once there are no NULLs in the table, you should be able to get the schema in sink with prisma by manually running the command that marks the column as NON NULL.

ALTER TABLE "FontVariant" ALTER COLUMN "file_link_id" SET NOT NULL;

answered Jul 16 at 14:10

Jeff Swenson

312 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jacob Miller Jul 17 at 1:57

Thank you, but there are no values at all in the table. I actually found the solution!

Jacob Miller · Accepted Answer · 2025-07-17 01:57:40Z

0

After trying a dozen different things, creating a migration manually seems to do the trick:

# 1) Create the migration
mkdir -p prisma/migrations/20250728130000_enforce_file_link_required
cat > prisma/migrations/20250728130000_enforce_file_link_required/migration.sql <<EOF
ALTER TABLE "FontVariant" ALTER COLUMN "file_link_id" SET NOT NULL;
EOF

# 2) Tell Prisma it’s already been applied
npx prisma migrate resolve \
  --applied 20250728130000_enforce_file_link_required

# 3) Verify / run
npx prisma migrate status
npx prisma migrate dev

answered Jul 17 at 1:57

Jacob Miller

7641 gold badge7 silver badges22 bronze badges

Collectives™ on Stack Overflow

How do I resolve Prisma ORM drift after making values non nullable with CockroachDB? [closed]

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related