DEV Community

loading...
Cover image for Database Triggers in PostgreSQL

Database Triggers in PostgreSQL

ogwurujohnson profile image Johnson Ogwuru ・3 min read

Ah, it's been a while I wrote about something. Been a busy year, so much to do, and so much learnt.

In this article, I will try to explain what a database trigger is. When to use it, and a practical example of it.

Definition:

A database trigger is a procedural code that is automatically executed in response to certain events on a particular table or view on a database.


Usage:

Recently, I was working on a discord bot, at some point in the development processes, we needed a way to notify the discord users, of the status of their transaction. Because we had a transactions table, with a status column, one way i could do this was to use a database trigger.

I am sure there are other solutions to this problem, but this was the solution I went with.


Setup:

So I set up a trigger to listen for UPDATE events in the transactions table. Here is the code required for doing this.

CREATE FUNCTION notify_transaction_status_change() RETURNS trigger AS $$
      DECLARE
      BEGIN
        PERFORM pg_notify('transaction_status_changed', json_build_object(
            'operation', TG_OP,
            'record', row_to_json(NEW)
          )::text
        );

        RETURN NEW;
      END;
      $$ LANGUAGE 'plpgsql';
Enter fullscreen mode Exit fullscreen mode

In the above code, we are creating a database function, that would reference the trigger named, transaction_status_changed.
Below is the script for the trigger.

 CREATE TRIGGER transaction_status_changed 
 AFTER UPDATE 
 ON transactions 
 FOR EACH ROW 
 EXECUTE PROCEDURE notify_transaction_status_change()
Enter fullscreen mode Exit fullscreen mode

The trigger gets called after every update on the transactions table, for each row. So if you have 10 rows on the transactions table, if an update is performed on them, this trigger would run for each.

Now the next thing to do would be to listen for this event triggers, get the payload from the event and do what whatever with it, in our case, we will be notifying the user who owns the transaction.


We would need to create a connection to the database. With this connection created, the application would have to listen for events.
Find the script I used in achieving this below;

function doConnectionSetupStuff(connection: any, knex: any, client: Client) {
  connection.query('LISTEN transaction_status_changed');

  connection.on('notification', (msg: { channel: string; payload: string }) => {
    const jsonResponse = JSON.parse(msg.payload);
    //notify user when status of transaction is confirmed
    if (jsonResponse.record.status === 'confirmed') {
      notifyUsers(jsonResponse, client);
    }
  });

  connection.on('end', () => {
    reconnectClient(knex, client);
  });
  connection.on('error', (err: any) => {
    logger.error(err);
  });
}

const PostgresNotificationListener = (knex: Knex, client: Client): void => {
  knex.client
    .acquireRawConnection()
    .then((connection: any) => {
      logger.info('Listening for events in Database');
      doConnectionSetupStuff(connection, knex, client);
    })
    .catch((e: any) => {
      logger.error(e);
    });
};

function reconnectClient(knex: any, client: Client) {
  const fn = setInterval(() => {
    try {
      knex.client
        .acquireRawConnection()
        .then((connection: any) => {
          doConnectionSetupStuff(connection, knex, client);
          clearInterval(fn);
        })
        .catch((e: any) => {
          logger.error(e);
        });
      console.log('connected to DB');
    } catch (e) {
      logger.error(e);
    }
  }, 3000);
}

PostgresNotificationListener();
Enter fullscreen mode Exit fullscreen mode

At the time of this writing, I was making use of [Knex], (http://knexjs.org/), PostgreSQL and Typescript.

The script creates a connection to the database, and when this connection ends, it reconnects the script to the database, so that it's constantly listening for trigger events.
And when these events are received and certain conditions are met, the payload gets cleaned up and presented to a user or be used in making a logical decision within the application.

There are plenty other use-cases for database triggers. One other use could be enforcing rules for certain columns in a table.

** Quick note:
if you are making use of Knex, here is how to setup the trigger and function in your table migration.

import * as Knex from 'knex';

export async function up(knex: Knex): Promise<void> {
  // Create table
  await knex.schema.createTable('transactions', table => {
    table.increments();
    table.string('type').notNullable();
    table.uuid('sender'); // Sender id (or null if type = withdrawal)
    table.uuid('recipient'); // Recipient id (or null if type = deposit)
    table.string('withdraw_address');
    table.decimal('amount', 18, 8).notNullable();
    table.string('hash'); // Transaction hash if type = deposit or withdrawal
    table.string('status').defaultTo('pending');
    table.timestamp('created_at', { useTz: false }).defaultTo(knex.fn.now());
    table.timestamp('updated_at', { useTz: false }).defaultTo(knex.fn.now());
  });

// Create function/trigger
  await knex.raw(
    `
      CREATE FUNCTION notify_transaction_status_change() RETURNS trigger AS $$
      DECLARE
      BEGIN
        PERFORM pg_notify('transaction_status_changed', json_build_object(
            'operation', TG_OP,
            'record', row_to_json(NEW)
          )::text
        );

        RETURN NEW;
      END;
      $$ LANGUAGE 'plpgsql';
    `,
  );

// Assign trigger
  await knex.raw(`
      CREATE TRIGGER transaction_status_changed 
      AFTER UPDATE 
      ON transactions 
      FOR EACH ROW 
      EXECUTE PROCEDURE notify_transaction_status_change()
  `);
}

export async function down(knex: Knex): Promise<void> {
  await knex.raw('DROP TRIGGER IF EXISTS transaction_status_changed ON transactions');
  await knex.raw('DROP FUNCTION IF EXISTS notify_transaction_status_change CASCADE');
  await knex.schema.dropTableIfExists('transactions');
}

Enter fullscreen mode Exit fullscreen mode

Triggers are a powerful feature that can improve the efficiency of any application using a database. I hope someone finds it useful like I did.

If you have any questions feel free to ask on the comment section or reach out to me on Twitter @devopsjay

cover image from https://www.digitalocean.com/community/tutorials/understanding-relational-databases

Discussion (0)

Forem Open with the Forem app