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

Make sure that you're granting the role to the correct user, as shown in the mysql.users table entries. There might be multiple users with the same name, but on different hosts. Depending on which application or host is invoking the lambda_sync function, MySQL selects the user with the best match according to the host column entries.

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 ;