String size constraint in array triggers new migration with every migrate

[dariacm]

Bug description

I have a Prisma schema where I create a table with a column set to be an array of strings with a max size constraint. The first time I run prisma migrate dev to sync the DB schema with my Prisma schema, the operation is successful. If I run prisma migrate dev another time, however, Prisma mistakenly detects that the DB schema is out of sync and prompts to create a new migration. All the migration does it dropping the column and re-adding it again with the same constraint.

How to reproduce

1. Change the Prisma schema as shown below:

model Test {
  id          String   @id @unique @default(dbgenerated("gen_random_ulid()"))
  testStrings String[] @db.String(16)

  @@schema("translation")
}

2. Run prisma migrate dev once
3. Run ``prisma migrate dev` a second time

Expected behavior

Running prisma migrate dev a second time does not prompt to create a new migration

Prisma information

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["multiSchema"]
}

datasource db {
  provider     = "cockroachdb"
  url          = env("DATABASE_URL")
  relationMode = "prisma"
  schemas      = ["translation"]
}

model Test {
  id          String   @id @unique @default(dbgenerated("gen_random_ulid()"))
  testStrings String[] @db.String(16)

  @@schema("translation")
}

Environment & setup

• OS: macOS
• Database: CockroachDB
• Node.js version: v20.0.0

Prisma Version

prisma                  : 5.3.1
@prisma/client          : 5.3.1
Current platform        : darwin-arm64
Query Engine (Node-API) : libquery-engine 61e140623197a131c2a6189271ffee05a7aa9a59 (at ../../../node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Schema Engine           : schema-engine-cli 61e140623197a131c2a6189271ffee05a7aa9a59 (at ../../../node_modules/@prisma/engines/schema-engine-darwin-arm64)
Schema Wasm             : @prisma/prisma-schema-wasm 5.3.1-2.61e140623197a131c2a6189271ffee05a7aa9a59
Default Engines Hash    : 61e140623197a131c2a6189271ffee05a7aa9a59
Studio                  : 0.494.0
Preview Features        : multiSchema

[janpio]

Can you share the migration it creates for you?
Does you schema change when you run db pull?
If so, does that fix the problem?

[Jolg42]

Note: related feature request for supporting ULID IDs in the Prisma schema #13679
It should also work when using dbgenerated() though, and I'm curious to know more about your issue here.

Though that's probably unrelated since you mention it's because of "String size constraint in array" here, so sharing the migration created by Prisma Migrate would help

[dariacm]

This is the migration it generates:

/*
  Warnings:

  - The `testStrings` column on the `Test` table would be dropped and recreated. This will lead to data loss if there is data in the column.

*/
-- AlterTable
ALTER TABLE "translation"."Test" DROP COLUMN "testStrings";
ALTER TABLE "translation"."Test" ADD COLUMN     "testStrings" STRING(16)[];

If I try to run prisma db pull, I see that the size constraint is removed from the Prisma schema:

model Test {
  id          String   @id @unique @default(dbgenerated("gen_random_ulid()"))
  testStrings String[]

  @@schema("translation")
}

So I would say that db pull doesn't fix the issue, because in the end it just drops the constraint.

[janpio]

Yes, that would be an unrelated bug then :/ Can you open an issue for it if it does not exist yet @dariacm? Thanks.

[dariacm]

@janpio #21227