Skip to main content

Manage Database Triggers with Drizzle 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 Drizzle 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

Configuring Drizzle with Atlas for Database Triggers​

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

Follow these steps in the Drizzle Quick Start guide to set up your Drizzle project. After setting up your Drizzle project, You structure your project as follows:

data "external_schema" "drizzle" {
    program = [ 
      "npx",
      "drizzle-kit",
      "export",
    ]
}

env "local" {
  dev = "docker://postgres/16/dev?search_path=public"
  schema {
    src = data.external_schema.drizzle.url
  }
  migration {
    dir = "file://atlas/migrations"
  }
}

1. Modify Your Drizzle Models

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

src/schema.ts
import { integer, pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core';

export const User = pgTable('User', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
});

export const UserAuditLog = pgTable('UserAuditLog', {
  id: serial('id').primaryKey(),
  operationType: text('operationType').notNull(),
  operationTime: timestamp('operationTime').notNull().defaultNow(),
  oldValue: text('oldValue').nullable(),
  newValue: text('newValue').nullable(),
});

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 Drizzle and Trigger Definitions

Modify your atlas.hcl file to include the composite_schema data source to combine the Drizzle schema with the triggers:

atlas.hcl
data "external_schema" "drizzle" {
    program = [ 
      "npx",
      "drizzle-kit",
      "export",
    ]
}

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

env "local" {
    dev = "docker://postgres/16/dev?search_path=public"
    schema {
        src = data.composite_schema.drizzle-extended.url
    }
    migration {
        dir = "file://atlas/migrations"
    }
}

In this file, we first define an external_schema data source that loads the Drizzle schema from the schema definition file and then use the composite_schema data source to combine the Drizzle 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 Drizzle schema with the triggers:

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

Output:

-- Create "User" table
CREATE TABLE "User" ("id" serial NOT NULL, "name" text NOT NULL, "email" text NOT NULL, PRIMARY KEY ("id"), CONSTRAINT "User_email_unique" UNIQUE ("email"));
-- Create "audit_user_changes" function
CREATE FUNCTION "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 "User" FOR EACH ROW EXECUTE FUNCTION "audit_user_changes"();
-- Create trigger "user_insert_audit"
CREATE TRIGGER "user_insert_audit" AFTER INSERT ON "User" FOR EACH ROW EXECUTE FUNCTION "audit_user_changes"();
-- Create trigger "user_update_audit"
CREATE TRIGGER "user_update_audit" AFTER UPDATE ON "User" FOR EACH ROW EXECUTE FUNCTION "audit_user_changes"();
-- Create "UserAuditLog" table
CREATE TABLE "UserAuditLog" ("id" serial NOT NULL, "operationType" text NOT NULL, "operationTime" timestamp NOT NULL DEFAULT now(), "oldValue" text NULL, "newValue" text 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
├── drizzle.config.ts
├── schema.ts
└── triggers.sql

Apply Migrations to the Database​

First, create a PostgreSQL development database with Docker:

docker run --name postgres -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d postgres:latest

Then, apply the migrations to the database using the atlas migrate apply command:

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

Output:

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

  -- migrating version 20250103091149
    -> CREATE TABLE "User" ("id" serial NOT NULL, "name" text NOT NULL, "email" text NOT NULL, PRIMARY KEY ("id"), CONSTRAINT "User_email_unique" UNIQUE ("email"));
    -> CREATE FUNCTION "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 "User" FOR EACH ROW EXECUTE FUNCTION "audit_user_changes"();
    -> 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 TABLE "UserAuditLog" ("id" serial NOT NULL, "operationType" text NOT NULL, "operationTime" timestamp NOT NULL DEFAULT now(), "oldValue" text NULL, "newValue" text NULL, PRIMARY KEY ("id"));
  -- ok (10.9565ms)

  -------------------------
  -- 63.790958ms
  -- 1 migration
  -- 6 sql statements

Conclusion​

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