CODEWITHSUNDEEP
next.jsprisma
lt512
lt512

Reputation: 183

Prisma model Generation -

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

Is there a way in the schema.prisma definition to enforce a a column to be the result of an operation on several others column ?

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

All that in the same table. So that if column 3 is modified, column 1 will automatically be updated as well.

Thank you in advance

Upvotes: 1

Views: 1897

Answers (1)

Tasin Ishmam
Tasin Ishmam

Reputation: 7248

While generated columns aren't natively supported in Prisma, you could use a Postgres trigger to achieve the same behavior.

Here's a step-by-step example of how to create a database table with a "generated" column value when using Prisma.

1. Create the Prisma schema with the optional generated column

This should be self evident. Dummy example:

model Foo {
  id        Int      @id @default(autoincrement())
  col1 Int
  col2 Int
  colSum Int?  // colSum = col1 + col2
}

2. Write the Trigger

The trigger will update the value of colSum field any time there is a create or update operation on the Foo Table. This is what it looks like

CREATE OR REPLACE FUNCTION trigger_col_update()
RETURNS TRIGGER
AS $$
BEGIN
  NEW."colSum" := NEW."col1" + NEW."col2";

RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_col_trigger
    BEFORE INSERT OR UPDATE ON "Foo"
                         FOR EACH ROW
                         EXECUTE PROCEDURE trigger_col_update();

You could manually invoke these SQL commands for your database, but I would recommend storing them in your migration history, as shown in step 3.

3. (Optional) Add trigger to your migration history

Create a new migration file for model Foo but do not apply the migration, as you will be making some changes to it manually. You can do this with the --create-only flag.

npx prisma migrate dev --create-only

Go to the migration.sql file of the generated migration and at the end, paste the SQL provided in step 2.

Apply the migration

npx prisma migrate dev

Now any time you make changes to col1 or col2, it should automatically be reflected in colSum.

Alternative: Prisma Middleware

The solution with triggers performs the operations in the database server. You could also do these updates in the application layer with Prisma Middleware. Just perform this same update manually before the query is invoked in Prisma.

However, there are a lot of conditions to check and doing it in the application layer has an unnecessary performance impact. I would not recommend this for the simple use case you mentioned.

Upvotes: 5

Related Questions