DEV Community

Cover image for Amplify  -  How to add Aurora Serverless relational db to a REST api
Mickael Lecoq
Mickael Lecoq

Posted on

Amplify  -  How to add Aurora Serverless relational db to a REST api

For a GraphQL API, it’s quite easy to add a relational database, Amplify cli has a dedicated command

amplify api add-graphql-datasource
Enter fullscreen mode Exit fullscreen mode

However, for rest apis, the only easy option you have is DynamoDb.

If you want to use relational database, you will have to add a custom cloudformation resource (see https://docs.amplify.aws/cli/usage/customcf).

To use rds data api (see https://docs.aws.amazon.com/rdsdataservice/latest/APIReference/API_ExecuteStatement.html), you need to have 2 arns

  • the arn of Aurora Serverless DB cluster
  • the arn of the secret that contains access infos to the cluster

Create a new RDS cluster resource

In backend-config.json file, add a block to define your custom resource

"rds": {
  "resourceExample": {
     "service": "RDSCluster",
     "providerPlugin": "awscloudformation",
     "dependsOn": []
  }
Enter fullscreen mode Exit fullscreen mode

Then create under amplify/backend folder rds/resourceExample

In amplify/backend/rds/resourceExample add 2 files :

  • parameters.json with the following content
{
 "DBUsername": "dbusername",
 "DBClusterName": "dbcluster"
}
Enter fullscreen mode Exit fullscreen mode

Change dbusername and dbcluster with your own values

  • template_resourceExample.json with the following content
{
  "AWSTemplateFormatVersion": "2010-09-09",
  "Description": "AWS CloudFormation Sample Template AuroraServerlessDBCluster: Sample template showing how to create an Amazon Aurora Serverless DB cluster. **WARNING** This template creates an Amazon Aurora DB cluster. You will be billed for the AWS resources used if you create a stack from this template",
  "Parameters": {
    "DBUsername": {
      "NoEcho": "true",
      "Description": "Username for MySQL database access",
      "Type": "String",
      "MinLength": "1",
      "MaxLength": "16",
      "AllowedPattern": "[a-zA-Z][a-zA-Z0-9]*",
      "ConstraintDescription": "must begin with a letter and contain only alphanumeric characters."
    },
    "env": {
      "Type": "String"
    },
    "DBClusterName": {
      "Type": "String"
    }
  },
  "Conditions": {
    "ShouldNotCreateEnvResources": {
      "Fn::Equals": [
        {
          "Ref": "env"
        },
        "NONE"
      ]
    }
  },
  "Resources": {
    "RDSSecret": {
      "Type": "AWS::SecretsManager::Secret",
      "Properties": {
        "Description": "This is a Secrets Manager secret for an RDS DB instance",
        "GenerateSecretString": {
          "SecretStringTemplate": {
            "Fn::Join": [
              "",
              ["{\"username\": \"", { "Ref": "DBUsername" }, "\"}"]
            ]
          },
          "GenerateStringKey": "password",
          "PasswordLength": 16,
          "ExcludeCharacters": "\"@/\\"
        }
      }
    },
    "RDSCluster": {
      "Type": "AWS::RDS::DBCluster",
      "Properties": {
        "MasterUsername": {
          "Fn::Join": [
            "",
            [
              "{{resolve:secretsmanager:",
              { "Ref": "RDSSecret" },
              ":SecretString:username}}"
            ]
          ]
        },
        "MasterUserPassword": {
          "Fn::Join": [
            "",
            [
              "{{resolve:secretsmanager:",
              { "Ref": "RDSSecret" },
              ":SecretString:password}}"
            ]
          ]
        },
        "DBClusterIdentifier": {
          "Fn::If": [
            "ShouldNotCreateEnvResources",
            { "Ref": "DBClusterName" },
            {
              "Fn::Join": [
                "",
                [
                  { "Ref": "DBClusterName" },
                  "-",
                  {
                    "Ref": "env"
                  }
                ]
              ]
            }
          ]
        },
        "Engine": "aurora-postgresql",
        "EngineVersion": "10.7",
        "EngineMode": "serverless",
        "EnableHttpEndpoint": true,
        "ScalingConfiguration": {
          "AutoPause": true,
          "MinCapacity": 4,
          "MaxCapacity": 32,
          "SecondsUntilAutoPause": 1000
        }
      }
    },
    "SecretRDSInstanceAttachment": {
      "Type": "AWS::SecretsManager::SecretTargetAttachment",
      "Properties": {
        "SecretId": { "Ref": "RDSSecret" },
        "TargetId": { "Ref": "RDSCluster" },
        "TargetType": "AWS::RDS::DBCluster"
      }
    }
  },
  "Outputs": {
    "Name": {
      "Value": {
        "Ref": "RDSCluster"
      }
    },
    "Arn": {
      "Value": {
        "Fn::Join": [
          "",
          [
            "arn:",
            { "Ref": "AWS::Partition" },
            ":rds:",
            { "Ref": "AWS::Region" },
            ":",
            { "Ref": "AWS::AccountId" },
            ":cluster:",
            { "Ref": "RDSCluster" }
          ]
        ]
      }
    },
    "SecretArn": {
      "Value": {
        "Ref": "RDSSecret"
      }
    }
  }
}
Enter fullscreen mode Exit fullscreen mode

With this template, your rds cluster will be created with an associated secret.

In Output section, SecretArn (the arn of the secret) and Arn (the arn of the cluster) are returned

Get arns in your lambda

To have arns in your lambda, you will need to modify again backend-config.json file

In your function add the dependsOn infos

"function": {
    "myFunction": {
      "build": true,
      "providerPlugin": "awscloudformation",
      "service": "Lambda",
      "dependsOn": [
        {
          "category": "rds",
          "resourceName": "resourceExample",
          "attributes": [
            "Arn",
            "SecretArn"
          ]
        }
      ]
    }
  }
Enter fullscreen mode Exit fullscreen mode

Then modify the CloudFormation template of your lambda (under amplify/backend/function/)

Add the new parameters in Parameters section :

"Parameters": {
    "CloudWatchRule": {
      "Type": "String",
      "Default": "NONE",
      "Description": " Schedule Expression"
    },
    "env": {
      "Type": "String"
    },
    "rdsresourceExampleArn": {
      "Type": "String"
    },
    "rdsresourceExampleSecretArn": {
      "Type": "String"
    }
  }
Enter fullscreen mode Exit fullscreen mode

Then in Environment under Resources/LambdaFunction/Properties

"Environment": {
          "Variables": {
            "ENV": {
              "Ref": "env"
            },
            "REGION": {
              "Ref": "AWS::Region"
            },
            "DB_ARN": {
              "Ref": "rdsresourceExampleArn"
            },
            "SECRET_ARN": {
              "Ref": "rdsresourceExampleSecretArn"
            }
          }
        }
Enter fullscreen mode Exit fullscreen mode

Now you can use Data API in your lambda

const AWS = require("aws-sdk");
const RDS = new AWS.RDSDataService();

const params = {
    secretArn: process.env.SECRET_ARN,
    resourceArn: process.env.DB_ARN,
    sql: "select * from test;",
};

try {
  const result = await RDS.executeStatement(params).promise();
  console.log({ result: JSON.stringify(result) });
} catch (err) {
  console.log({ err });
}
Enter fullscreen mode Exit fullscreen mode

Then run

amplify env checkout <yourEnv>
Enter fullscreen mode Exit fullscreen mode

To be sure that changes in backend-config.json are taken into account

And finally

Amplify push
Enter fullscreen mode Exit fullscreen mode

Top comments (1)

Collapse
 
eranda profile image
Eranda Hettiarachchi

Great guide @mkllecoq !

Everything works except I'm getting below in lambda logs

err: AccessDeniedException: User: arn:aws:sts::####:assumed-role/###-dev/###-dev is not authorized to perform: rds-data:ExecuteStatement on resource: arn:aws:rds:ap-southeast-2:###:cluster:###-dev

Any ideas how to fix this?