Amplify  -  How to add Aurora Serverless relational db to a REST api
Tags: aws, amplify, aurora
Published: 2020-12-20 20:12:24

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

```
amplify api add-graphql-datasource
```

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](https://docs.amplify.aws/cli/usage/customcf)).

To use rds data api (see [https://docs.aws.amazon.com/rdsdataservice/latest/APIReference/API_ExecuteStatement.html](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 

```json
"rds": {
  "resourceExample": {
     "service": "RDSCluster",
     "providerPlugin": "awscloudformation",
     "dependsOn": []
  }
```

Then create under *amplify/backend* folder *rds/resourceExample*

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

- **parameters.json** with the following content

```json
{
 "DBUsername": "dbusername",
 "DBClusterName": "dbcluster"
}
```

Change *dbusername* and *dbcluster* with your own values

- **template_resourceExample.json** with the following content

```json
{
  "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"
      }
    }
  }
}
```

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 

```json
"function": {
    "myFunction": {
      "build": true,
      "providerPlugin": "awscloudformation",
      "service": "Lambda",
      "dependsOn": [
        {
          "category": "rds",
          "resourceName": "resourceExample",
          "attributes": [
            "Arn",
            "SecretArn"
          ]
        }
      ]
    }
  }
```

Then modify the CloudFormation template of your lambda (under *amplify/backend/function/<myFunction>*)

Add the new parameters in *Parameters* section : 

```json
"Parameters": {
    "CloudWatchRule": {
      "Type": "String",
      "Default": "NONE",
      "Description": " Schedule Expression"
    },
    "env": {
      "Type": "String"
    },
    "rdsresourceExampleArn": {
      "Type": "String"
    },
    "rdsresourceExampleSecretArn": {
      "Type": "String"
    }
  }
```

Then in *Environment* under *Resources/LambdaFunction/Properties*

```json
"Environment": {
          "Variables": {
            "ENV": {
              "Ref": "env"
            },
            "REGION": {
              "Ref": "AWS::Region"
            },
            "DB_ARN": {
              "Ref": "rdsresourceExampleArn"
            },
            "SECRET_ARN": {
              "Ref": "rdsresourceExampleSecretArn"
            }
          }
        }
```

Now you can use Data API in your lambda 

```jsx
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 });
}
```

Then run 

```jsx
amplify env checkout <yourEnv>
```

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

And finally

```jsx
Amplify push
```