Using AWS Lambda with Amazon Aurora DSQL

Sign in to the AWS Management Console and open the AWS Lambda console at https://console.aws.amazon.com/lambda/.

Choose Create function.

Provide a name, such as dsql-sample.

Don't edit the default settings to make sure that Lambda creates a new role with basic Lambda permissions.

Choose Create function.

In your Lambda function, choose Configuration > Permissions.

Choose the role name to open the execution role in the IAM console.

Choose Add Permissions > Create inline policy, and use the JSON editor.

In Action paste in the following action to authorize your IAM identity to connect using the admin database role.

"Action": ["dsql:DbConnectAdmin"],

In Resource, add your cluster’s Amazon Resource Name (ARN). You can also use a wildcard.

"Resource": ["*"]

Choose Next.

Enter a name for the policy, such as dsql-sample-dbconnect.

Choose Create policy.

Create a folder named myfunction.

In the folder, create a new file named package.json with the following content.

{
  "dependencies": {
    "@aws-sdk/dsql-signer": "^3.705.0",
    "assert": "2.1.0",
    "pg": "^8.13.1"
  }
}

In the folder, create a file named index.mjs in the directory with the following content.

import { DsqlSigner } from "@aws-sdk/dsql-signer";
import pg from "pg";
import assert from "node:assert";
const { Client } = pg;


async function dsql_sample(clusterEndpoint, region) {
  let client;
  try {
    // The token expiration time is optional, and the default value 900 seconds
    const signer = new DsqlSigner({
      hostname: clusterEndpoint,
      region,
    });
    const token = await signer.getDbConnectAdminAuthToken();
    // <https://node-postgres.com/apis/client>
    // By default `rejectUnauthorized` is true in TLS options
    // <https://nodejs.org/api/tls.html#tls_tls_connect_options_callback>
    // The config does not offer any specific parameter to set sslmode to verify-full
    // Settings are controlled either via connection string or by setting
    // rejectUnauthorized to false in ssl options
    client = new Client({
      host: clusterEndpoint,
      user: "admin",
      password: token,
      database: "postgres",
      port: 5432,
      // <https://node-postgres.com/announcements> for version 8.0
      ssl: true,
      rejectUnauthorized: false
    });

    // Connect
    await client.connect();

    // Create a new table
    await client.query(`CREATE TABLE IF NOT EXISTS owner (
      id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      name VARCHAR(30) NOT NULL,
      city VARCHAR(80) NOT NULL,
      telephone VARCHAR(20)
    )`);

    // Insert some data
    await client.query("INSERT INTO owner(name, city, telephone) VALUES($1, $2, $3)", 
      ["John Doe", "Anytown", "555-555-1900"]
    );

    // Check that data is inserted by reading it back
    const result = await client.query("SELECT id, city FROM owner where name='John Doe'");
    assert.deepEqual(result.rows[0].city, "Anytown")
    assert.notEqual(result.rows[0].id, null)

    await client.query("DELETE FROM owner where name='John Doe'");

  } catch (error) {
    console.error(error);
    throw new Error("Failed to connect to the database");
  } finally {
    client?.end();
  }
  Promise.resolve();
}


// https://docs.aws.amazon.com/lambda/latest/dg/nodejs-handler.html
export const handler = async (event) => {
  const endpoint = event.endpoint;
  const region = event.region;
  const responseCode = await dsql_sample(endpoint, region);
  
  const response = {
    statusCode: responseCode,
    endpoint: endpoint,
  };
  return response;
};

Use the following commands to create a package.

npm install
zip -r pkg.zip .

In your Lambda function’s Code tab, choose Upload from > .zip file

Upload the pkg.zip you created. For more information, see Deploy Node.js Lambda functions with .zip file archives.

In your Lambda function’s Test tab, paste in the following JSON payload, and modify it to use your cluster ID.

In your Lambda function’s Test tab, use the following Event JSON modified to specify your cluster’s endpoint.

{"endpoint": "replace_with_your_cluster_endpoint"}

Enter an Event name, such as dsql-sample-test. Choose Save.

Choose Test.

Choose Details to expand the execution response and log output.

If it succeeded, the Lambda function execution response should return a 200 status code.

{"statusCode": 200, "endpoint": "your_cluster_endpoint"}

If the database returns an error or if the connection to the database fails, the Lambda function execution response returns a 500 status code.

{"statusCode": 500,"endpoint": "your_cluster_endpoint"}