Generated columns (MySQL)

[drupsys]

Problem

It is currently not clear how to setup prisma schema to allow for generated mysql columns. Assume we have the following column migration

-- AlterTable
ALTER TABLE `LineItem` ADD COLUMN `total` DECIMAL(13, 4) GENERATED ALWAYS AS (`price`*`count`) STORED NOT NULL AFTER `count`;

The question then is how do you setup a schema so that total would not be required when creating this record. If your schema is

model LineItem {
  id Int @id @default(autoincrement())
  price Decimal @db.Decimal(13, 4)
  count Int
  total Decimal @db.Decimal(13, 4)
}

then lineItem.create(...) will require you to set total to something and mysql query will crash because you are not allowed to set/update generated columns. If your schema is

model LineItem {
  id Int @id @default(autoincrement())
  price Decimal @db.Decimal(13, 4)
  count Int
  total Decimal? @db.Decimal(13, 4)
}

then in your query responses total will potentially be undefined, which is better but we are using typescript and it is a pain to check if a column is set when we know there is no way it isn't set. Finally, I did found a way that does work

model LineItem {
  id Int @id @default(autoincrement())
  price Decimal @db.Decimal(13, 4)
  count Int
  total Decimal @db.Decimal(13, 4) @default(dbgenerated())
}

This, however, feels like a hack that might stop working at some point in the future. If the above is the intended way to achieve my usecase then I guess this is not a feature request, but it would be good if someone confirmed this approach will continue to work in the future.

Suggested solution

Assuming the above is not intended to work then I would be happy with anything basic like an option to mark column as db generated, e.g.

model LineItem {
  id Int @id @default(autoincrement())
  price Decimal @db.Decimal(13, 4)
  count Int
  total Decimal @db.Decimal(13, 4) @db.generated()
}

The above would be good enough for my usecase.

Alternatives

there is also an option to provide complete support for generated columns, e.g.

@db.generated("GENERATED ALWAYS AS (`price`*`count`) STORED")

this would just be an extension of the above solution.

Additional context

I have looked into the Computed Fields feature, unfortunatelly I cannot use that approach due to existing databases depending on this column :(.

[janpio]

Is your request covered by #3001?

[drupsys]

@janpio I'm not sure I know what you mean by "covered", it is the same issue if that is what you are asking.

[janpio]

Perfect. Can we just close this issue then, and you maybe leave a comment over there with the relevant part of our message? We have quite a few issues already, so we are trying to avoid duplicates.

[janpio]

I am closing this issue as a duplicate of #6336

This will help us actually prioritize the issue, as we are not distracted by having multiple ones that compete for attention. Please make sure you left a 👍 reaction on the original issue description over there, and leave a comment if any aspect of the feature has not been talked about yet. Thank you!