Generated columns

[laurent512]

Hello,

Sorry in advance if question is basic, I am a beginner.

Is there a way, in the schema.prisma definition, to create a column which is always the result of a specific operation of other columns ?

e.g. column 1 = Sum ( column 2 + column 3) ?

So that if column 3 is modified, column 1 will automatically be updated as well.

Thank you in advance

[ryands17]

@laurent512 👋

If you're using Postgres, you can use it's feature of generated columns with Prisma Migrate.

For e.g.

Create a schema:

model User {
  id        Int       @id @default(autoincrement())
  firstName String
  lastName  String?
  fullName  String?
}

Run prisma migrate dev --create-only

Edit the .sql file to use generated:

-- CreateTable
CREATE TABLE "User" (
    "id" SERIAL NOT NULL,
    "firstName" TEXT NOT NULL,
    "lastName" TEXT,
    "fullName" TEXT GENERATED ALWAYS AS ("firstName" || ' ' || "lastName") STORED,

    CONSTRAINT "User_pkey" PRIMARY KEY ("id")
);

Run prisma migrate dev.

Now you will get the result of fullName always generated.

[laurent512]

Basically it is :
Column A = true if (column B !=null && Column C==true && Column D > Today) ; false otherwise

(column D contains an expiry date in timestamp form)

[ryands17]

For complex conditions, you're better off defining a trigger that updates this as you create/update the record. Generated fields only accept immutable expressions and the above is not immutable.

[ryands17]

You can create a trigger in your migration .sql file and that should make this work.

[tsongas]

@ryands17 I've found this solution is not workable in terms of type safety, at least with my limited knowledge of TS. If I make fullName nullable as shown above, then I can't make it required in my GraphQL schema because Prisma says it could be null, which is inaccurate because the generated column always has a value. If I make fullName not null, then Prisma requires it to be passed in, which defeats the purpose of a generated column.

Any ideas?

[Bennison-Devadoss]

Prisma provides the preview option for full-text search (We can perform a full-text search using Prisma), Then how they don't have the features to define the generated column in the schema.prisma file?