@default(dbgenerated(...)) results in never-ending migration change generation

[iiian]

Bug description

I have a field multi_index on a model:

model MyModel {
  key         String
  index       Int @default(0)
  // allows for matrix and tensor-valued keys.
  multi_index Json    @db.JsonB
  value       String?
  image_id    String   @db.Uuid
  image       Image    @relation(fields: [image_id], references: [id])
  created_at  DateTime @default(now())

  @@id([key, image_id, index, multi_index])
  @@map("field")
}

When I add the attribute @default(dbgenerated("NULL")) to multi_index, prisma migrate dev no longer behaves idempotently, generating the following migration over and over, once per each time it is called:

-- AlterTable
ALTER TABLE "field" ALTER COLUMN "multi_index" SET DEFAULT NULL;

How to reproduce

See above

Expected behavior

I would have expected that a single migration gets generated to apply the migration.

Prisma information

// Add your schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator js_cli {
  provider = "prisma-client-js"
  output   = "node_modules/@prisma/client/service-layer"
}

// This is just the relevant model trace with many other models cut out. They can be provided privately at further request

model MyModel {
  key         String
  index       Int @default(0)
  // allows for matrix and tensor-valued keys.
  multi_index Json    @db.JsonB
  value       String?
  created_at  DateTime @default(now())

  @@id([key, index, multi_index])
  @@map("field")
}

Environment & setup

• OS: Windows 11 running WSL2 With Ubuntu22.
• Prisma Version: Verified occurs with versions 4.8.0, 4.10.1, 4.12.0
• Database: Postgres v14
• Node.js version: v18.14.0

Prisma Version

4.12.0

[iiian]

Ohhhhh. Probably another important consideration here. If I mark the field ? nullable, that achieves the same result, but it also makes the @@id invalid. Since that annotation only works on required fields. I'm wondering if that is perhaps part of it. In any case, I've tried a few different default JSON values as well, such as '{}', and it still goes for infinity.

[janpio]

What happens when you run prisma db pull (for the schema in the initial state)? That should update your schema file to reflect what Prisma understand the state of the database to be.

[iiian]

Looks like it strips off the @db.JsonB and @default(dbgenerated("NULL")) which I assume means that those are implicit defaults. It also makes similar changes across many of my string id fields (id String @id @default(uuid()) @db.Uuid), dropping the @default(uuid()) annotation. After doing prisma db pull, the infinite migration script generation glitch disappears.

If @db.JsonB is the default for Json type fields and some kind of json 'null' is the default value, and if @default(uuid()) is the default for uuids, then I'm content with accepting the changes prisma db pull applied to my schema and closing this issue.

[janpio]

Did you run db pull with the existing schema.prisma in place or did you remove that? That uuid() was removed indicates to me that you probably removed it.

• Json @db.JsonB is indeed the default, so we only render Json:
  
  https://www.prisma.io/docs/concepts/database-connectors/postgresql#:~:text=*Default%20mapping%20for%20Json%20%2D%20no%20type%20attribute%20added%20to%20schema.
• NULL in the database we represent by doing field? instead of field (which impacts both the nullability, and the default - unless you define another one).
• And @default(uuid()) is indeed only Prisma side, so when you introspect without an existing usage for a field, Prisma can not know that this is supposed to be uuid() and leaves it out. uuid() is not the default.

I by the way think we should probably not just close the issue, as @default(dbgenerated("NULL")) should probably just be forbidden. I don't think this can ever lead to good results long term.

[iiian]

Did you run db pull with the existing schema.prisma in place or did you remove that? That uuid() was removed indicates to me that you probably removed it.

Yes, I ran db pull with the existing schema.prisma in place.

[iiian]

I suppose it is also probably worth noting that the SQL migration statement related to the Json @default(dbgenerated("NULL")) is:

-- AlterTable
ALTER TABLE "my_model" DROP CONSTRAINT "field_pkey",
DROP COLUMN "id",
ALTER COLUMN "index" SET NOT NULL,
ALTER COLUMN "index" SET DEFAULT 0,
ALTER COLUMN "multi_index" SET NOT NULL,
ALTER COLUMN "multi_index" SET DEFAULT NULL, <~~~~~~~~~~~~~ This guy!
ADD CONSTRAINT "field_pkey" PRIMARY KEY ("key", "image_id", "index", "multi_index");

However, when I do prisma db pull, the multi_index field is Json (as mentioned previously) but prisma migrate dev does not result in a new migration to reflect the change in nullability, which might also be of some concern. That's kind of funny, seeing multi_index say SET NOT NULL and then immediately contradicting it with SET DEFAULT NULL. Made me chuckle, gotta love it.

[razzeee]

I'm seeing this due to this line in my schema

deleteAfter   DateTime @default(dbgenerated("NOW() + interval '6 months'"))

prisma db pull suggests this, which then stops the neverending schema updates it seems

deleteAfter   DateTime @default(dbgenerated("(now() + '6 mons'::interval)"))

[janpio]

Yes, yours is a "normal" case of this problem @razzeee, where the database just represents the same things slightly different. We have not figured out a good way how to automate fixing that. Ian on the other hand had found a "special" one with NULL that cause the same problem, but for different underlying reasons.

[Stubbs]

I'm also seeing this when creating a default time in a field:

model UserValidation {
  user_id        String   @unique @db.Uuid
  user           User     @relation(fields: [user_id], references: [id])
  validation_key String
  expires_at     DateTime @default(dbgenerated("CURRENT_DATE + interval '3 days'"))
}

Always creates a migration:

-- AlterTable
ALTER TABLE "UserValidation" ALTER COLUMN "expires_at" SET DEFAULT CURRENT_DATE + interval '3 days';

even when there are no other changes to make to the structure of the tables.

If I run db pull on the db, I get a slightly different syntax written to the schema:

expires_at DateTime @default(dbgenerated("(CURRENT_DATE + '3 days'::interval)"))

If I replace my version with the version it suggests I no longer get the un-needed migrations.

[tlbignerd]

The solution here is, whenever using dbgenerated to run both a prisma migrate dev and immediately after a prisma db pull. that updates the schema to match what the database actually generated. The preference/fix would be for Prisma to do this automatically when adding/updating a dbgenerate, or at least giving us a flag to do this automatically.

In my specific case with postgresql having the following schema:

accountExpireAt DateTime @default(dbgenerated("NOW() + interval '1 year'"))

actually generates

accountExpireAt DateTime @default(dbgenerated("(now() + '1 year'::interval)"))

So then every time a prisma migrate dev is run Prisma introspects the database and thinks it's different and creates a new migrate file.