Skip to main content

Manage Database Triggers with Prisma using Atlas

Triggers are powerful tools in relational databases that allow you to execute custom code when specific events occur on a table. For example, triggers can automatically log changes to a table without adding extra code to your application. This guide explains how to incorporate triggers into your Prisma project using Atlas.

Atlas Pro Feature

Atlas support for Triggers used in this guide is available exclusively to Pro users. To use this feature, run:

atlas login

Introduction​

Prisma excels at modeling your database schema using its own schema language but doesn't natively support defining database triggers. Using Atlas, you can manage advanced database features like triggers alongside your Prisma models.

Configuring Prisma with Atlas for Database Triggers​

To include both your Prisma models and custom triggers, we'll configure Atlas to combine both schemas using the composite_schema data source.

1. Define Your Prisma Models

Start with your existing Prisma models or create new ones. For this example, we'll use User and UserAuditLog:

prisma/schema.prisma
// generator {...}
// datasource {...}

model User {
    id    Int    @id @default(autoincrement())
    name  String
    email String @unique
}

model UserAuditLog {
    id            Int      @id @default(autoincrement())
    operationType String
    operationTime DateTime @default(now())
    oldValue      Json?
    newValue      Json?
}

2. Create SQL File with Trigger Definitions

Create a file named triggers.sql containing the trigger function and triggers.

triggers.sql
-- Function to audit changes in the User table.
CREATE OR REPLACE FUNCTION audit_user_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'INSERT') THEN
        INSERT INTO "UserAuditLog" (operationType, operationTime, newValue)
        VALUES (TG_OP, CURRENT_TIMESTAMP, row_to_json(NEW));
        RETURN NEW;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO "UserAuditLog" (operationType, operationTime, oldValue, newValue)
        VALUES (TG_OP, CURRENT_TIMESTAMP, row_to_json(OLD), row_to_json(NEW));
        RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
        INSERT INTO "UserAuditLog" (operationType, operationTime, oldValue)
        VALUES (TG_OP, CURRENT_TIMESTAMP, row_to_json(OLD));
        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Triggers for INSERT, UPDATE, and DELETE on the User table.
CREATE TRIGGER user_insert_audit AFTER INSERT ON "User" FOR EACH ROW EXECUTE FUNCTION audit_user_changes();
CREATE TRIGGER user_update_audit AFTER UPDATE ON "User" FOR EACH ROW EXECUTE FUNCTION audit_user_changes();
CREATE TRIGGER user_delete_audit AFTER DELETE ON "User" FOR EACH ROW EXECUTE FUNCTION audit_user_changes();

3. Use composite_schema to Combine Prisma and Trigger Definitions

Create a file named atlas.hcl and add the following configuration:

atlas.hcl
data "external_schema" "prisma" {
    program = [ 
        "npx",
        "prisma",
        "migrate",
        "diff",
        "--from-empty",
        "--to-schema-datamodel",
        "prisma/schema.prisma",
        "--script"
    ]
}

data "composite_schema" "prisma-extended" {
    schema {
        url = data.external_schema.prisma.url
    }
    schema {
        url = "file://triggers.sql"
    }
}

env "local" {
    dev = "docker://postgres/15/dev"
    schema {
        src = data.composite_schema.prisma-extended.url
    }
    migration {
        dir = "file://atlas/migrations"
    }
}

In this file, we first define an external_schema data source that loads the Prisma schema from the schema definition file and then use the composite_schema data source to combine the Prisma schema with the triggers defined in triggers.sql:

Usage​

Verify our setup​

To verify our setup is correct, we can inspect the schema using the atlas schema inspect command to see how Atlas combines the Prisma schema with the triggers:

atlas schema inspect --env local --url env://schema.src --format "{{ sql . }}"

Output:

-- Add new schema named "public"
CREATE SCHEMA IF NOT EXISTS "public";
-- Set comment to schema: "public"
COMMENT ON SCHEMA "public" IS 'standard public schema';
-- Create extension "pgcrypto"
CREATE EXTENSION "pgcrypto" WITH SCHEMA "public" VERSION "1.3";
-- Create "User" table
CREATE TABLE "public"."User" ("id" serial NOT NULL, "name" text NOT NULL, "email" text NOT NULL, PRIMARY KEY ("id"));
-- Create index "User_email_key" to table: "User"
CREATE UNIQUE INDEX "User_email_key" ON "public"."User" ("email");
-- Create "audit_user_changes" function
CREATE FUNCTION "public"."audit_user_changes" () RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    IF (TG_OP = 'INSERT') THEN
        INSERT INTO "UserAuditLog" (operationType, operationTime, newValue)
        VALUES (TG_OP, CURRENT_TIMESTAMP, row_to_json(NEW));
        RETURN NEW;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO "UserAuditLog" (operationType, operationTime, oldValue, newValue)
        VALUES (TG_OP, CURRENT_TIMESTAMP, row_to_json(OLD), row_to_json(NEW));
        RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
        INSERT INTO "UserAuditLog" (operationType, operationTime, oldValue)
        VALUES (TG_OP, CURRENT_TIMESTAMP, row_to_json(OLD));
        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$$;
-- Create trigger "user_delete_audit"
CREATE TRIGGER "user_delete_audit" AFTER DELETE ON "public"."User" FOR EACH ROW EXECUTE FUNCTION "public"."audit_user_changes"();
-- Create trigger "user_insert_audit"
CREATE TRIGGER "user_insert_audit" AFTER INSERT ON "public"."User" FOR EACH ROW EXECUTE FUNCTION "public"."audit_user_changes"();
-- Create trigger "user_update_audit"
CREATE TRIGGER "user_update_audit" AFTER UPDATE ON "public"."User" FOR EACH ROW EXECUTE FUNCTION "public"."audit_user_changes"();
-- Create "UserAuditLog" table
CREATE TABLE "public"."UserAuditLog" ("id" serial NOT NULL, "operationType" text NOT NULL, "operationTime" timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, "oldValue" jsonb NULL, "newValue" jsonb NULL, PRIMARY KEY ("id"));

Generate Migration Files​

Next, let's see how we can generate new migration files using the atlas migrate diff command:

atlas migrate diff --env local

Output:

.
├── atlas
│   └── migrations
│       ├── 20241204122818.sql
│       └── atlas.sum
├── atlas.hcl
├── prisma
│   ├── migrations
│   │   ├── ...
│   │       └── migration.sql
│   └── schema.prisma
└── triggers.sql

Apply Migrations to the Database​

atlas migrate apply --env local --url "postgresql://root:pass@localhost:5432/postgres?sslmode=disable"

Output:

Output
Migrating to version 20241204122818 (1 migrations in total):

  -- migrating version 20241204122818
    -> CREATE EXTENSION "pgcrypto" WITH SCHEMA "public" VERSION "1.3";
    -> CREATE TABLE "public"."User" ("id" serial NOT NULL, "name" text NOT NULL, "email" text NOT NULL, PRIMARY KEY ("id"));
    -> CREATE UNIQUE INDEX "User_email_key" ON "public"."User" ("email");
    -> CREATE FUNCTION "public"."audit_user_changes" () RETURNS trigger LANGUAGE plpgsql AS $$
       BEGIN
           IF (TG_OP = 'INSERT') THEN
               INSERT INTO "UserAuditLog" (operationType, operationTime, newValue)
               VALUES (TG_OP, CURRENT_TIMESTAMP, row_to_json(NEW));
               RETURN NEW;
           ELSIF (TG_OP = 'UPDATE') THEN
               INSERT INTO "UserAuditLog" (operationType, operationTime, oldValue, newValue)
               VALUES (TG_OP, CURRENT_TIMESTAMP, row_to_json(OLD), row_to_json(NEW));
               RETURN NEW;
           ELSIF (TG_OP = 'DELETE') THEN
               INSERT INTO "UserAuditLog" (operationType, operationTime, oldValue)
               VALUES (TG_OP, CURRENT_TIMESTAMP, row_to_json(OLD));
               RETURN OLD;
           END IF;
           RETURN NULL;
       END;
       $$;
    -> CREATE TRIGGER "user_delete_audit" AFTER DELETE ON "public"."User" FOR EACH ROW EXECUTE FUNCTION "public"."audit_user_changes"();
    -> CREATE TRIGGER "user_insert_audit" AFTER INSERT ON "public"."User" FOR EACH ROW EXECUTE FUNCTION "public"."audit_user_changes"();
    -> CREATE TRIGGER "user_update_audit" AFTER UPDATE ON "public"."User" FOR EACH ROW EXECUTE FUNCTION "public"."audit_user_changes"();
    -> CREATE TABLE "public"."UserAuditLog" ("id" serial NOT NULL, "operationType" text NOT NULL, "operationTime" timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, "oldValue" jsonb NULL, "newValue" jsonb NULL, PRIMARY KEY ("id"));
  -- ok (45.681333ms)

  -------------------------
  -- 90.735625ms
  -- 1 migration
  -- 8 sql statements

Conclusion​

By combining Prisma ORM with Atlas, you can effectively manage both your application's data models and version-controlled database triggers. This approach lets you leverage Prisma's ORM capabilities while maintaining advanced database features like triggers through Atlas's schema management.