Invoking a Lambda function with an Aurora MySQL native function

You can invoke an AWS 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 AWS 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: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: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:lambda:us-east-1:123456789012:function:BasicTestLambda',
          '{"operation": "ping"}')
          INTO @result;
      END; //
mysql>DELIMITER ;