How Postgres Triggers Can Simplify Your Backend Development

The Mythical Engineer
Apr 23, 2023


postgres-triggers-banner

In this blog post, we will explore what triggers are, how they work, and how you can use them to simplify your backend service code.

What Are Postgres Triggers?

A trigger is a specification that the database should automatically execute a particular function in response to certain events.

These events can include changes to data in a table, such as insertions, updates, or deletions. When an event occurs, the trigger can perform a specified action, such as running a function or updating another table.

How Do Postgres Triggers Work?

Triggers are associated with a specific table and event, and are defined using SQL commands. When the event occurs, the trigger is activated and executes the specified action.

For example, you could create a trigger that automatically sets a timestamp column to the current time whenever a row is inserted or updated in a table.

How Can Triggers Simplify Your Backend Service Code?

By using triggers, you can offload some of the work that would normally be done in your backend service code to the database itself. This can simplify your code. For example, instead of writing complex code to update related tables, you could define triggers that automatically perform these tasks whenever the relevant event occurs.

You should consider the fact that complexity will still be there, but it will be abstracted away inside the database.


Let’s take an example to see the utility of triggers.

Let’s say we have two database tables wallet and passbook.

CREATE TABLE wallet (id BIGINT PRIMARY KEY, amount INTEGER NOT NULL DEFAULT 0)

CREATE TABLE passbook (id BIGINT references wallet(id), txn_id BIGSERIAL PRIMARY KEY, amount INTEGER NOT NULL DEFAULT 0, current_amount INTEGER NOT NULL DEFAULT 0)

Table wallet schema

Column Type Constraints
id bigint PRIMARY KEY
amount integer NOT NULL, DEFAULT 0

Table passbook schema

Column Type Constraints
id bigint FOREIGN KEY (wallet.id)
txn_id bigint PRIMARY KEY
amount integer NOT NULL, DEFAULT 0
current_amount integer NOT NULL, DEFAULT 0

Use Case:


Triggers Implementation

Here’s an example of how we can write a PostgreSQL trigger function that adds amount to the wallet table when a new passbook record is inserted:

CREATE FUNCTION update_wallet()
RETURNS TRIGGER AS $$
BEGIN
  -- Get the current wallet balance for the user
  SELECT amount INTO NEW.current_amount FROM wallet WHERE id = NEW.id;

  -- Update the wallet balance with the amount from the passbook record
  UPDATE wallet SET amount = amount + NEW.amount WHERE id = NEW.id;

  -- Set the current amount in the passbook record to the updated wallet balance
  NEW.current_amount = NEW.amount + NEW.current_amount;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

We have to create a trigger on the passbook table that calls this function whenever a new record is inserted:

CREATE TRIGGER update_wallet_trigger
BEFORE INSERT ON passbook
FOR EACH ROW
EXECUTE FUNCTION update_wallet();


Application Code:

I’ll demonstrate the code using postgres npm package

Application Code before triggers:

const addMoney = async (transaction, db = sql) => {
  const txn = {
    amount: Math.floor(transaction.amount),
    id: Math.floor(transaction.id),
  };
  await db.begin(async (sql) => {
    const wallet = await getWallet(id, sql); // SQL query (1)
    const current_amount = wallet.amount + txn.amount;
    const updatedWallet = {
      id: Math.floor(id),
      amount: current_amount,
    };
    const passbookRecord = {
      ...txn,
      current_amount: current_amount,
    };
    await updateWallet(updatedWallet, sql); // SQL query (2)
    await insertIntoPassbook(passbookRecord, sql); // SQL query (3)
  });
};

// db.begin starts a database transaction
// getWallet : SELECT * from wallet where id=1;
// updateWallet: UPDATE wallet SET amount = current_amount where id=1;
// insertIntoPassbook: INSERT INTO passbook (id, amount, current_amount) VALUES (1, 100, 100);
// txn_id is auto incremented in passbook

Application Code after triggers:

const addMoney = async (transaction, db = sql) => {
  const txn = {
    amount: Math.floor(transaction.amount),
    id: Math.floor(transaction.id),
  };
  //  Wallet updates handled by postgres trigger automatically
  await insertIntoPassbook(txn, db); // SQL query (1)
};

As we can see that our lines of code has reduced drastically on the application side. We are now doing only one SQL query from the application code.

We have to keep in mind that we have just moved the logic inside the database. Number of operations getting executed inside database is still the same.

SELECT amount INTO NEW.current_amount FROM wallet WHERE id = NEW.id;

This query is equivalent to calling getWallet(id, sql) method

UPDATE wallet SET amount = amount + NEW.amount WHERE id = NEW.id;

This query is equivalent to calling updateWallet(updatedWallet, sql) method


These are few improvements we can have using triggers:

Triggers should be used with caution since they can obscure critical logic and create an illusion of automatic processes. While this can be advantageous in certain scenarios, it can also pose a challenge in terms of debugging, testing, and monitoring since they are not readily visible to developers.

As a result, it is important to weigh the benefits and drawbacks of using triggers before implementing them in a database system.


Update:

Many individuals have left comments on the HN thread, sharing their personal experiences with triggers.

HN Thread: https://news.ycombinator.com/item?id=35676572

I’ll summarise few of the the discussions:

I recommend thoroughly reviewing the discussions before deciding to implement triggers in production. While it works well for us, it may not be suitable for everyone. Therefore, I suggest using it sparingly.

Please share your thoughts and experiences on the HN thread.


P.S. My friend is actively seeking opportunities in Product Management, Marketing Operations, or Data Analytics.

He brings strong expertise in data analytics, mathematics, and finance. Currently based in Bangalore, India, he is open to both remote and in-office opportunities.

If you're looking for a driven professional with strong analytical skills and quick learning ability, please reach out to him directly:


#development  #postgres  #database  #trigger  #optimization  #backend  #scale 

Suggested Reading

  • * How to setup self hosted wiki for your startup

    * How to install Cursor AI IDE on Fedora Asahi Linux Arm64

    * Revert a commited secret from remote repository

    * Setup a Production Redis Cluster

    * From Chaos to Control: Using Direnv to Manage Multiple GitHub Accounts

    • Share this: