DEV Community

Liav Yona for Firefly

Posted on

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.]

Image description

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 = ""
resource "aws_iam_role_policy_attachment" "rds_lambda_role_attach" {
  role       =
  policy_arn = aws_iam_policy.invoke_lambda_policy.arn
resource "aws_db_instance_role_association" "rds_lambda_role_attach" {
  db_instance_identifier =
  feature_name           = "LAMBDA"
  role_arn               = aws_iam_role.rds_lambda_role.arn
Enter fullscreen mode Exit fullscreen mode

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.

Image Permission Granted

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);

Enter fullscreen mode Exit fullscreen mode

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 OR REPLACE FUNCTION respond_with_lambda()
    IF cardinality(TG_ARGV)!=2 THEN
      RAISE EXCEPTION 'Expected 2 parameters to respond_with_lambda function but got %', cardinality(TG_ARGV);
      RAISE EXCEPTION 'Lambda name is empty';
      RAISE EXCEPTION 'Lambda region is empty';
       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'), 
        RETURN NEW;
    END IF;
DROP TRIGGER IF EXISTS new_user_trigger ON users;
CREATE TRIGGER new_user_trigger
  EXECUTE PROCEDURE respond_with_lambda("on-new-user", "us-east-1");
Enter fullscreen mode Exit fullscreen mode

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

def handler(event: UserInsertionEvent, context: LambdaContext) -> Dict[str, Any]:
    client = boto3.client("sns")
    response = client.publish(
        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
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

After a few seconds, I received the following SMS:

Image SMS Success

Image We Did It!


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.

Top comments (0)