SQL Triggers with AWS Lambda — Take Your DB to the Next Level

Relational databases are still a powerful and effective tool to define relationships between structured data points. Among all the functionalities these databases expose, Triggers are a special stored procedure that runs before or after specific actions occur within the database.

Triggers (or hooks) play a major role in keeping your codebase simple by passing the event handling to the database itself instead of implementing this with code. However, maintaining those triggers is not trivial; they’ve been proven to be hard to debug; their capabilities are limited, and they suffer a lack of explainability and visibility (logging abilities).

In this blog, I will introduce the usage of AWS Lambda as the solution for these problems in an AWS RDS Aurora with a PostgreSQL engine.

[NOTE: This blog assumes you have a working knowledge of Python, AWS Lambdas, AWS SNS, Terraform and PostgreSQL.]

Granting Permissions to Invoke Lambda Function

First of all, we start by giving our RDS cluster permissions to invoke a Lambda function (using Terraform):

resource "aws_iam_policy" "invoke_lambda_policy" {
  name        = "invoke_lambda_policy"
  path        = "/"
  description = "A policy which grants permission to invoke a Lambda function."

  policy      = jsonencode({
    Version = "2012-10-17"
    Statement = [
      {
        Action = "lambda:InvokeFunction"
        Effect   = "Allow"
        Resource = "arn:aws:lambda:*:123456789123:function:*"
      },
    ]
  })
}
resource "aws_iam_role" "rds_lambda_role" {
  name               = "rds_lambda_role"
  assume_role_policy = jsonencode({
    Version = "2012-10-17"
    Statement = [
      {
        Action = "sts:AssumeRole"
        Effect = "Allow"
        Principal = {
          Service = "rds.amazonaws.com"
        }
      },
    ]
  })
}
resource "aws_iam_role_policy_attachment" "rds_lambda_role_attach" {
  role       = aws_iam_role.rds_lambda_role.name
  policy_arn = aws_iam_policy.invoke_lambda_policy.arn
}
resource "aws_db_instance_role_association" "rds_lambda_role_attach" {
  db_instance_identifier = aws_db_instance.test.id
  feature_name           = "LAMBDA"
  role_arn               = aws_iam_role.rds_lambda_role.arn
}

Now, our DB instance has permission to invoke any Lambda function in our AWS account. Next, we want to create triggers responding to operational actions.

Writing DB Trigger in SQL

In our database, we have a user table to manage all of our customers.

CREATE TABLE users (id integer, firstname varchar(100), lastname varchar(100), age integer);
  INSERT into users (id, firstname, lastname, age) values (1, 'albert', 'einstein', 40);
  INSERT into users (id, firstname, lastname, age) values (2, 'isaac', 'newton', 25);
  INSERT into users (id, firstname, lastname, age) values (3, 'marie', 'curie', 32);

We now want to trigger a Lambda function that will publish an SNS message. Afterward, we can add an SMS subscriber to get a push notification for each new user.

In the following SQL code snippet, we use the aws_lambda extension and used the invoke function after inserting a new row to our users table. The event contains the first and last name of the new user and the creation timestamp. We build the event in a JSON format using the keyword NEW as a reference to the new entity.

[NOTE: PostgreSQL triggers are executed within the same transaction as the statement that triggered them. As a result, if the performing of trigger fails so as the statement’s.]

CREATE EXTENSION IF NOT EXISTS aws_lambda CASCADE;
CREATE OR REPLACE FUNCTION respond_with_lambda()
  RETURNS TRIGGER
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
    IF cardinality(TG_ARGV)!=2 THEN
      RAISE EXCEPTION 'Expected 2 parameters to respond_with_lambda function but got %', cardinality(TG_ARGV);
   ELSEIF TG_ARGV[0]='' THEN
      RAISE EXCEPTION 'Lambda name is empty';
   ELSEIF TG_ARGV[1]='' THEN
      RAISE EXCEPTION 'Lambda region is empty';
   ELSE
       PERFORM * FROM aws_lambda.invoke(aws_commons.create_lambda_function_arn(TG_ARGV[0], TG_ARGV[1]),
                               CONCAT('{"firstname": "', NEW.firstname,
                                    '", "lastname": "', NEW.lastname,
                                    '", "created_at": "', TO_CHAR(NOW()::timestamp, 'YYYY-MM-DD"T"HH24:MI:SS'), 
                                   '"}')::json,
                                    'Event');
        RETURN NEW;
    END IF;
END
$$;
DROP TRIGGER IF EXISTS new_user_trigger ON users;
CREATE TRIGGER new_user_trigger
  AFTER INSERT ON users
  FOR EACH ROW
  EXECUTE PROCEDURE respond_with_lambda("on-new-user", "us-east-1");

Add on user inserted SQL trigger

Now, we need to implement a Lambda function that receives such events and publishes new user notifications to a SNS topic.
[NOTE: Be mindful that the aws_lambda.invoke method can receive the Event parameter for asynchronous execution. You can use the RequestResponse parameter instead for synchronous invocation.]

Notification Producer Lambda

We create a Python Lambda function named on_new_user in our DB instance region and VPC in order to maximize performances and maintain security. In our case, the Lambda publishes a new user message to a SNS topic (which can have SMS subscribers) but since we are in a code execution unit our options of responding to such an event are endless (We chose a SMS message in this blog but you can respond with any way you wish).

So we have the following code which handles the new user event:

# Built-ins
from os import getenv
from json import dumps
from typing import Any, Dict
from http import HTTPStatus
# Third party
from aws_lambda_powertools.utilities.typing import LambdaContext
from aws_lambda_powertools.utilities.parser import event_parser, BaseModel
import boto3

class UserInsertionEvent(BaseModel):
    firstname: str
    lastname: str
    created_at: str

@event_parser(model=UserInsertionEvent)
def handler(event: UserInsertionEvent, context: LambdaContext) -> Dict[str, Any]:
    client = boto3.client("sns")
    response = client.publish(
        TopicArn=getenv("NOTIFICATION_TOPIC_ARN"),
        Message=f"A new user was added at {event.created_at}: {event.firstname} {event.lastname}"
    )
    return {
        "lambda_request_id": context.aws_request_id,
        "lambda_arn": context.invoked_function_arn,
        "status_code": HTTPStatus.OK.value,
        "event": event.json(),
        "response": response
    }

Lambda handler for on_user_inserted event

After our Lambda is deployed with all its dependencies and configured with the accurate environment variables, let’s test our new user creation flow.

Piece the Components Together

In order to execute the model, we need to insert a new user. For the sake of simplicity, we will do it straight from the DB console instead of having an application in front of our database.

INSERT into users (id, firstname, lastname, age) values (4, 'stephen', 'hawking', 55);

After a few seconds, I received the following SMS:

Summary

We created the following architecture:

On each INSERT action in the users table, we triggered a Lambda function that publishes a notification to our SNS and then straight to our SMS subscriber.
With such a solution, we managed to control our DB trigger easily, effectively and I might say beautifully. With a Lambda function, we can add logging to increase explainability and visibility. In addition, we can respond to any event using the preferred programming language of our choice making use of its advanced and extensive capabilities.

In this blog, we’ve seen an example of SMS notification for every new user. Even though, such an architecture could be helpful:

• Be notified for stock price’s change.
• Create a new tenant environment asynchronously.
• Send metrics to a remote endpoint.

To conclude, once our Lambda function has the modified entity from our database — Our options of responding to the event are unlimited.