Using Database Triggers in Sequelize
Triggers are useful tools in relational databases that allow you to execute custom code when specific events occur on a table. For instance, triggers can automatically populate the audit log table whenever a new mutation is applied to a different table. This way we ensure that all changes (including those made by other applications) are meticulously recorded, enabling the enforcement on the database-level and reducing the need for additional code in the applications.
This guide explains how to attach triggers to your Sequelize models and configure the schema migration to manage both the triggers and the Sequelize models as a single migration unit using Atlas.
Atlas support for Triggers used in this guide is available exclusively to Pro users. To use this feature, run:
atlas login
Getting started with Atlas and Sequelize
Before we continue, ensure you have installed the Atlas Sequelize Provider on your Sequelize project.
To set up, follow along the getting started guide for Sequelize and Atlas.
Composite Schema
The Sequelize models are mostly used for defining tables and interacting with the database. Triggers or any other database native objects do not have representation in Sequelize models.
In order to extend our PostgreSQL schema to include both our Sequelize models and their policies, we configure Atlas to read the state of the schema from a Composite Schema data source. Follow the steps below to configure this for your project:
1. Let's define a simple model with two types (tables): users
and user_audit_logs
:
'use strict';
module.exports = function(sequelize, DataTypes) {
const User = sequelize.define('User', {
name: {
type: DataTypes.STRING,
allowNull: false
}
});
return User;
};
'use strict';
module.exports = function(sequelize, DataTypes) {
const UserAuditLog = sequelize.define('UserAuditLog', {
operationType: {
type: DataTypes.STRING,
allowNull: false
},
operationTime: {
type: DataTypes.STRING,
allowNull: false
},
newValue: {
type: DataTypes.STRING,
allowNull: true
},
oldValue: {
type: DataTypes.STRING,
allowNull: true
},
},
{
timestamps: false,
}
);
return UserAuditLog;
};
Now, suppose we want to log every change to the users
table and save it in the user_audit_logs
table.
To achieve this, we need to create a trigger function on INSERT
, UPDATE
and DELETE
operations and attach it to
the users
table.
2. Next step, we define a trigger function ( audit_users_changes
) and attach it to the users
table using the CREATE TRIGGER
commands:
-- Function to audit changes in the users table.
CREATE OR REPLACE FUNCTION audit_users_changes()
RETURNS TRIGGER AS $$
BEGIN
IF (TG_OP = 'INSERT') THEN
INSERT INTO "UserAuditLogs"("operationType", "operationTime", "newValue")
VALUES (TG_OP, CURRENT_TIMESTAMP, row_to_json(NEW));
RETURN NEW;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO "UserAuditLogs"("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 "UserAuditLogs"("operationType", "operationTime", "oldValue")
VALUES (TG_OP, CURRENT_TIMESTAMP, row_to_json(OLD));
RETURN OLD;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-- Trigger for INSERT operations.
CREATE TRIGGER users_insert_audit AFTER INSERT ON "Users" FOR EACH ROW EXECUTE FUNCTION audit_users_changes();
-- Trigger for UPDATE operations.
CREATE TRIGGER users_update_audit AFTER UPDATE ON "Users" FOR EACH ROW EXECUTE FUNCTION audit_users_changes();
-- Trigger for DELETE operations.
CREATE TRIGGER users_delete_audit AFTER DELETE ON "Users" FOR EACH ROW EXECUTE FUNCTION audit_users_changes();
3. In your atlas.hcl
config file, add a composite_schema
that includes both your Sequelize models and
your custom triggers defined in schema.sql
:
data "composite_schema" "app" {
// First, load the schema
schema "public" {
url = data.external_schema.sequelize.url
}
// Next, load the triggers
schema "public" {
url = "file://schema.sql"
}
}
env "local" {
src = data.composite_schema.app.url
dev = "docker://postgres/15/dev?search_path=public"
}
Usage
After setting up our composite schema, we can get its representation using the atlas schema inspect
command, generate
schema migrations for it, apply them to a database, and more. Below are a few commands to get you started with Atlas:
Inspect the Schema
The atlas schema inspect
command is commonly used to inspect databases. However, we can also use it to inspect our
composite_schema
and print the SQL representation of it:
atlas schema inspect \
--env local \
--url env://src \
--format '{{ sql . }}'
The command above prints the following SQL. Note, the audit_users_changes
function and the triggers are defined after
the Users
and UserAuditLogs
tables:
-- Create "UserAuditLogs" table
CREATE TABLE "UserAuditLogs" ("id" serial NOT NULL, "operationType" character varying(255) NOT NULL, "operationTime" character varying(255) NOT NULL, "newValue" character varying(255) NOT NULL, "oldValue" character varying(255) NULL, "createdAt" timestamptz NOT NULL, "updatedAt" timestamptz NOT NULL, PRIMARY KEY ("id"));
-- Create "Users" table
CREATE TABLE "Users" ("id" serial NOT NULL, "name" character varying(255) NOT NULL, "createdAt" timestamptz NOT NULL, "updatedAt" timestamptz NOT NULL, PRIMARY KEY ("id"));
-- Create "audit_users_changes" function
CREATE FUNCTION "audit_users_changes" () RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
IF (TG_OP = 'INSERT') THEN
INSERT INTO "UserAuditLogs"("operationType", "operationTime", "newValue")
VALUES (TG_OP, CURRENT_TIMESTAMP, row_to_json(NEW));
RETURN NEW;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO "UserAuditLogs"("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 "UserAuditLogs"("operationType", "operationTime", "oldValue")
VALUES (TG_OP, CURRENT_TIMESTAMP, row_to_json(OLD));
RETURN OLD;
END IF;
RETURN NULL;
END;
$$;
-- Create trigger "users_delete_audit"
CREATE TRIGGER "users_delete_audit" AFTER DELETE ON "Users" FOR EACH ROW EXECUTE FUNCTION "audit_users_changes"();
-- Create trigger "users_insert_audit"
CREATE TRIGGER "users_insert_audit" AFTER INSERT ON "Users" FOR EACH ROW EXECUTE FUNCTION "audit_users_changes"();
-- Create trigger "users_update_audit"
CREATE TRIGGER "users_update_audit" AFTER UPDATE ON "Users" FOR EACH ROW EXECUTE FUNCTION "audit_users_changes"();
Generate Migrations for the Schema
To generate a migration for the schema, run the following command:
atlas migrate diff \
--env local
Note that a new migration file is created with the following contents:
-- Create "UserAuditLogs" table
CREATE TABLE "UserAuditLogs" ("id" serial NOT NULL, "operationType" character varying(255) NOT NULL, "operationTime" character varying(255) NOT NULL, "newValue" character varying(255) NOT NULL, "oldValue" character varying(255) NULL, "createdAt" timestamptz NOT NULL, "updatedAt" timestamptz NOT NULL, PRIMARY KEY ("id"));
-- Create "Users" table
CREATE TABLE "Users" ("id" serial NOT NULL, "name" character varying(255) NOT NULL, "createdAt" timestamptz NOT NULL, "updatedAt" timestamptz NOT NULL, PRIMARY KEY ("id"));
-- Create "audit_users_changes" function
CREATE FUNCTION "audit_users_changes" () RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
IF (TG_OP = 'INSERT') THEN
INSERT INTO "UserAuditLogs"("operationType", "operationTime", "newValue")
VALUES (TG_OP, CURRENT_TIMESTAMP, row_to_json(NEW));
RETURN NEW;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO "UserAuditLogs"("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 "UserAuditLogs"("operationType", "operationTime", "oldValue")
VALUES (TG_OP, CURRENT_TIMESTAMP, row_to_json(OLD));
RETURN OLD;
END IF;
RETURN NULL;
END;
$$;
-- Create trigger "users_delete_audit"
CREATE TRIGGER "users_delete_audit" AFTER DELETE ON "Users" FOR EACH ROW EXECUTE FUNCTION "audit_users_changes"();
-- Create trigger "users_insert_audit"
CREATE TRIGGER "users_insert_audit" AFTER INSERT ON "Users" FOR EACH ROW EXECUTE FUNCTION "audit_users_changes"();
-- Create trigger "users_update_audit"
CREATE TRIGGER "users_update_audit" AFTER UPDATE ON "Users" FOR EACH ROW EXECUTE FUNCTION "audit_users_changes"();
Apply the Migrations
To apply the migration generated above to a database, run the following command:
atlas migrate apply \
--env local \
--url "postgres://postgres:pass@localhost:5432/database?search_path=public&sslmode=disable"
Sometimes, there is a need to apply the schema directly to the database without generating a migration file. For example, when experimenting with schema changes, spinning up a database for testing, etc. In such cases, you can use the command below to apply the schema directly to the database:
atlas schema apply \
--env local \
--url "postgres://postgres:pass@localhost:5432/database?search_path=public&sslmode=disable"