Invoking a Lambda function from an Amazon Aurora MySQL DB cluster

You can invoke an Amazon Lambda function from an Amazon Aurora MySQL-Compatible Edition DB cluster with the native function lambda_sync or lambda_async. Before invoking a Lambda function from an Aurora MySQL, the Aurora DB cluster must have access to Lambda. For details about granting access to Aurora MySQL, see Giving Aurora access to Lambda. For information about the lambda_sync and lambda_async stored functions, see Invoking a Lambda function with an Aurora MySQL native function.

You can also call an Amazon Lambda function by using a stored procedure. However, using a stored procedure is deprecated. We strongly recommend using an Aurora MySQL native function if you are using one of the following Aurora MySQL versions:

Giving Aurora access to Lambda

Before you can invoke Lambda functions from an Aurora MySQL DB cluster, make sure to first give your cluster permission to access Lambda.

Invoking a Lambda function with an Aurora MySQL native function

You can invoke an Amazon Lambda function from an Aurora MySQL DB cluster by calling the native functions lambda_sync and lambda_async. This approach can be useful when you want to integrate your database running on Aurora MySQL with other Amazon services. For example, you might want to send a notification using Amazon Simple Notification Service (Amazon SNS) whenever a row is inserted into a specific table in your database.

Working with native functions to invoke a Lambda function

The lambda_sync and lambda_async functions are built-in, native functions that invoke a Lambda function synchronously or asynchronously. When you must know the result of the Lambda function before moving on to another action, use the synchronous function lambda_sync. When you don't need to know the result of the Lambda function before moving on to another action, use the asynchronous function lambda_async.

Granting the role in Aurora MySQL version 3

In Aurora MySQL version 3, the user invoking a native function must be granted the AWS_LAMBDA_ACCESS role. To grant this role to a user, connect to the DB instance as the administrative user, and run the following statement.

GRANT AWS_LAMBDA_ACCESS TO user@domain-or-ip-address

You can revoke this role by running the following statement.

REVOKE AWS_LAMBDA_ACCESS FROM user@domain-or-ip-address

If you get an error such as the following when you try to invoke a Lambda function, then run a SET ROLE statement.

SQL Error [1227] [42000]: Access denied; you need (at least one of) the Invoke Lambda privilege(s) for this operation

Granting the privilege in Aurora MySQL version 2

In Aurora MySQL version 2, the user invoking a native function must be granted the INVOKE LAMBDA privilege. To grant this privilege to a user, connect to the DB instance as the administrative user, and run the following statement.

GRANT INVOKE LAMBDA ON *.* TO user@domain-or-ip-address

You can revoke this privilege by running the following statement.

REVOKE INVOKE LAMBDA ON *.* FROM user@domain-or-ip-address

Syntax for the lambda_sync function

You invoke the lambda_sync function synchronously with the RequestResponse invocation type. The function returns the result of the Lambda invocation in a JSON payload. The function has the following syntax.

lambda_sync (
  lambda_function_ARN,
  JSON_payload
)

Parameters for the lambda_sync function

The lambda_sync function has the following parameters.

Example for the lambda_sync function

The following query based on lambda_sync invokes the Lambda function BasicTestLambda synchronously using the function ARN. The payload for the function is {"operation": "ping"}.

SELECT lambda_sync(
    'arn:aws-cn:lambda:us-east-1:123456789012:function:BasicTestLambda',
    '{"operation": "ping"}');

Syntax for the lambda_async function

You invoke the lambda_async function asynchronously with the Event invocation type. The function returns the result of the Lambda invocation in a JSON payload. The function has the following syntax.

lambda_async (
  lambda_function_ARN,
  JSON_payload
)

Parameters for the lambda_async function

The lambda_async function has the following parameters.

Example for the lambda_async function

The following query based on lambda_async invokes the Lambda function BasicTestLambda asynchronously using the function ARN. The payload for the function is {"operation": "ping"}.

SELECT lambda_async(
    'arn:aws-cn:lambda:us-east-1:123456789012:function:BasicTestLambda',
    '{"operation": "ping"}');

Invoking a Lambda function within a trigger

You can use triggers to call Lambda on data-modifying statements. The following example uses the lambda_async native function and stores the result in a variable.

mysql>SET @result=0;
mysql>DELIMITER //
mysql>CREATE TRIGGER myFirstTrigger
      AFTER INSERT
          ON Test_trigger FOR EACH ROW
      BEGIN
      SELECT lambda_async(
          'arn:aws-cn:lambda:us-east-1:123456789012:function:BasicTestLambda',
          '{"operation": "ping"}')
          INTO @result;
      END; //
mysql>DELIMITER ;

Invoking a Lambda function with an Aurora MySQL stored procedure (deprecated)

You can invoke an Amazon Lambda function from an Aurora MySQL DB cluster by calling the mysql.lambda_async procedure. This approach can be useful when you want to integrate your database running on Aurora MySQL with other Amazon services. For example, you might want to send a notification using Amazon Simple Notification Service (Amazon SNS) whenever a row is inserted into a specific table in your database.

Aurora MySQL version considerations

Starting in Aurora MySQL version 2, you can use the native function method instead of these stored procedures to invoke a Lambda function. For more information about the native functions, see Working with native functions to invoke a Lambda function.

In Aurora MySQL version 2, the stored procedure mysql.lambda_async is no longer supported. We strongly recommend that you work with native Lambda functions instead.

In Aurora MySQL version 3, the stored procedure isn't available.

Working with the mysql.lambda_async procedure to invoke a Lambda function (deprecated)

The mysql.lambda_async procedure is a built-in stored procedure that invokes a Lambda function asynchronously. To use this procedure, your database user must have EXECUTE privilege on the mysql.lambda_async stored procedure.

Syntax

The mysql.lambda_async procedure has the following syntax.

CALL mysql.lambda_async (
  lambda_function_ARN,
  lambda_function_input
)

Parameters

The mysql.lambda_async procedure has the following parameters.

Examples

As a best practice, we recommend that you wrap calls to the mysql.lambda_async procedure in a stored procedure that can be called from different sources such as triggers or client code. This approach can help to avoid impedance mismatch issues and make it easier to invoke Lambda functions.

import boto3

ses = boto3.client('ses')

def SES_send_email(event, context):

    return ses.send_email(
        Source=event['email_from'],
        Destination={
            'ToAddresses': [
            event['email_to'],
            ]
        },

        Message={
            'Subject': {
            'Data': event['email_subject']
            },
            'Body': {
                'Text': {
                    'Data': event['email_body']
                }
            }
        }
    )

DROP PROCEDURE IF EXISTS SES_send_email;
DELIMITER ;;
  CREATE PROCEDURE SES_send_email(IN email_from VARCHAR(255),
                                  IN email_to VARCHAR(255),
                                  IN subject VARCHAR(255),
                                  IN body TEXT) LANGUAGE SQL
  BEGIN
    CALL mysql.lambda_async(
         'arn:aws-cn:lambda:us-west-2:123456789012:function:SES_send_email',
         CONCAT('{"email_to" : "', email_to,
             '", "email_from" : "', email_from,
             '", "email_subject" : "', subject,
             '", "email_body" : "', body, '"}')
     );
  END
  ;;
DELIMITER ;

mysql> call SES_send_email('example_from@amazon.com', 'example_to@amazon.com', 'Email subject', 'Email content');

import boto3

sns = boto3.client('sns')

def SNS_publish_message(event, context):

    return sns.publish(
        TopicArn='arn:aws-cn:sns:us-west-2:123456789012:Sample_Topic',
        Message=event['message'],
        Subject=event['subject'],
        MessageStructure='string'
    )

DROP PROCEDURE IF EXISTS SNS_Publish_Message;
DELIMITER ;;
CREATE PROCEDURE SNS_Publish_Message (IN subject VARCHAR(255),
                                      IN message TEXT) LANGUAGE SQL
BEGIN
  CALL mysql.lambda_async('arn:aws-cn:lambda:us-west-2:123456789012:function:SNS_publish_message',
     CONCAT('{ "subject" : "', subject,
            '", "message" : "', message, '" }')
     );
END
;;
DELIMITER ;

CREATE TABLE 'Customer_Feedback' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'customer_name' varchar(255) NOT NULL,
  'customer_feedback' varchar(1024) NOT NULL,
  PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELIMITER ;;
CREATE TRIGGER TR_Customer_Feedback_AI
  AFTER INSERT ON Customer_Feedback
  FOR EACH ROW
BEGIN
  SELECT CONCAT('New customer feedback from ', NEW.customer_name), NEW.customer_feedback INTO @subject, @feedback;
  CALL SNS_Publish_Message(@subject, @feedback);
END
;;
DELIMITER ;

mysql> insert into Customer_Feedback (customer_name, customer_feedback) VALUES ('Sample Customer', 'Good job guys!');