CDK CustomResource to initialise RDS instances

In this post, I’m going to provide the Infrastructure as Code (IaC) resources using AWS CDK. I’m going to describe how to initialise an Amazon Relational Database (RDS) instances using AWS lambdas Functions and AWS CloudFormation Custom Resources. For this example I’m going to use Mysql but the concepts can be use for other AWS RDS environments.

The problem to solve in this post is how to initialise AWS RDS instances. For that engineers runs SQL scripts. Within the initialization process of an RDS you can:

• Initialize databases with corresponding schema or table structures.
• Initialize and maintain users and permissions.
• Initialize and maintain stored procedures, views, or other database resources.
• Run custom code.

Prerequisites

To complete this walkthrough, you must have the following:

• AWS CDK version 2.82.0 or later installed and configured on your local machine.
• Node.js version 18 or later installed on your local machine.
• Docker installed on your local machine.
• A basic understanding of AWS CloudFormation.
• A basic understanding of AWS CDK constructs and stacks.
• Software development experience with TypeScript and JavaScript.

Walkthrough

The following sections describe how to initialize an Amazon RDS for MySQL instance. This post is very similar from this one written by AWS team. The main difference is that I’m going to use AWS CustomResources using Provider and CustomResource constructs, instead of using AwsCustomResource like the AWS blog post example. Because using AwsCustomResource is not able to fail if the sql script fails, more this will be explained later.

For this post I assume you already have installed all the requirements and you already have created a CDK project.

Next step is to create the construct in charge of creating all resources.
Create DbCustomResource under lib folder:

import { DockerImageCode, DockerImageFunction, Function } from 'aws-cdk-lib/aws-lambda'
import { RetentionDays } from 'aws-cdk-lib/aws-logs'
import { Construct } from 'constructs'
import { ISecurityGroup, IVpc } from 'aws-cdk-lib/aws-ec2'
import { CustomResource, Duration, RemovalPolicy, Stack } from 'aws-cdk-lib'
import { Provider } from 'aws-cdk-lib/custom-resources'
import { PolicyStatement, Role, ServicePrincipal } from 'aws-cdk-lib/aws-iam'

export interface DbCustomResourceProps {
  fnCode: DockerImageCode
  fnLogRetention: RetentionDays
  memorySize?: number
  securityGroups: ISecurityGroup[]
  fnTimeout: Duration
  config: any
  vpc: IVpc
}

export class DbCustomResource extends Construct {
  readonly customResource: CustomResource
  readonly response: string
  readonly dbInitializerFn: Function

  constructor(scope: Construct, id: string, props: DbCustomResourceProps) {
    super(scope, 'DBInitializer')

    this.dbInitializerFn = this.createDbLambdaFunction(props, id)
    this.customResource = this.createProviderCustomResource(props, id)
  }

  private createDbLambdaFunction(props: DbCustomResourceProps, id: string): Function {
    return new DockerImageFunction(this, 'dbInitializerFunction', {
      memorySize: props.memorySize || 128,
      functionName: `${id}-lambdaFunction`,
      code: props.fnCode,
      vpc: props.vpc,
      securityGroups: props.securityGroups,
      timeout: props.fnTimeout,
      logRetention: props.fnLogRetention,
      allowAllOutbound: true
    })
  }

  private createProviderCustomResource(props: DbCustomResourceProps, id: string): CustomResource {
    const customResourceFnRole = new Role(this, 'AwsCustomResourceRole', {
      assumedBy: new ServicePrincipal('lambda.amazonaws.com')
    })

    const region = Stack.of(this).region
    const account = Stack.of(this).account

    customResourceFnRole.addToPolicy(
      new PolicyStatement({
        actions: ['lambda:InvokeFunction'],
        resources: [`arn:aws:lambda:${region}:${account}:function:${this.dbInitializerFn.functionName}`]
      })
    )

    const provider = new Provider(this, 'Provider', {
      onEventHandler: this.dbInitializerFn,
      logRetention: props.fnLogRetention,
      vpc: props.vpc,
      securityGroups: props.securityGroups
    })

    return new CustomResource(this, 'CustomResource', {
      serviceToken: provider.serviceToken,
      properties: {
        config: props.config
      },
      removalPolicy: RemovalPolicy.DESTROY,
      resourceType: 'Custom::DBCustomResource'
    })
  }
}

In the code above first the lambda function is created based in DockerImageFunction, this is to ensure to have all dependencies the scrip needs to be executed.
The other part is to create the custom resource using Provider and CustomResource and all the resources needed for the custom resource like the role, policies.
This approach is used instead of creating the custom request using AwsCustomResource since when using AwsCustomResource and there is an error in the lambda execution (in our case it can be an error in the sql script) the stack continue to be deployed fine without failing. For this reason for this scenario I’m using CustomResource and Provider.

export class InitResources extends Construct {
    private readonly dataBase: DatabaseInstance;

    constructor(scope: Construct, id: string, props: InitResourcesProps) {
        super(scope, id);

        this.dataBase = this.createDatabase(props, id);
        const initializer = this.createDbCustomResourceInitializer(props);
        this.setPermissionsBetweenResources(initializer);
    }

    private setPermissionsBetweenResources(initializer: InitResources) {
        initializer.customResource.node.addDependency(this.dataBase);
        this.dataBase.connections.allowFrom(initializer.dbInitializerFn, Port.tcp(3306));
        this.dataBase.secret?.grantRead(initializer.dbInitializerFn);
    }

    private createParameterGroup(): CustomParameterGroup {
        // Wrapper custom construct to create parameter group
        return new CustomParameterGroup(this, 'CustomParameterGroup', {
            characterSet: 'utf8mb4',
            collation: 'utf8mb4_unicode_ci',
            parameterGroupId: 'CustomDB.mysql5.7',
            tlsVersion: Config.TLSVerion,
        });
    }

    private createDatabase(props: InitResourcesProps, _id: string): DatabaseInstance {
        const parameterGroup: ParameterGroup = this.createParameterGroup().parameterGroup;

        const dbConfig = props.config;
        // Custom wrapper Database construct
        return new CustomDataBase(this, `custom-db`, {
            dbName: `${DB_ID}`,
            dbInstanceId: `${DB_ID}`,
            parameterGroupId: `custom${DB_ID}Optimized.mysql5.7`,
            dbUsername: `${DB_ID}`,
            instanceType: 't3.small',
            dbCapacityInGb: dbConfig,
            allowPortFrom: props.realTimeServerConnections,
            parameterGroup,
            storageEncryptionEnabled: false,
        }).dbInstance;
    }

    private createDbCustomResourceInitializer(props: InitResourcesProps): DbCustomResource {
        const sg = new SecurityGroup(this, 'ResourceInitializerFnSg', {
            securityGroupName: 'ResourceInitializerFnSg',
            vpc: props.vpc,
            allowAllOutbound: true,
        });

        return new DbCustomResource(this, 'CustomResource', {
            fnLogRetention: RetentionDays.ONE_MONTH,
            fnCode: DockerImageCode.fromImageAsset(`${__dirname}/rds-init-fn-code`, {}),
            fnTimeout: Duration.minutes(5),
            securityGroups: [sg],
            config: {
                credsSecretName: this.dataBase.secret?.secretName,
            },
            vpc: props.vpc,
        });
    }
}

First we create the database using custom construct that basically is just instantiating a DatabaseInstance.
Then the we create the resources needed for the custom resource, like a SecurityGroup and the DbCustomResource construct specified above.

Next we need a folder containing our assets needed for our lambda. The docker file to install all the nodes dependencies and copying the sql script and lambda handler.

FROM amazon/aws-lambda-nodejs:18

WORKDIR ${LAMBDA_TASK_ROOT}

COPY package.json ./
RUN npm install 
COPY index.js ./
COPY script.sql ./

CMD [ "index.handler" ]

Next will be the code needed to be executed by our lambda. index.js:

const mysql = require('mysql')
const AWS = require('aws-sdk')
const fs = require('fs')
const path = require('path')
const crypto = require('crypto')

const secrets = new AWS.SecretsManager({})

exports.handler = async (event) => {
  console.log('Custom::DBInitializer event', event)
  switch (event.RequestType) {
    case 'Create':
      return createEvent(event)
    case 'Update':
      return updateEvent(event)
    case 'Delete':
      return deleteEvent()
    default:
      console.log(`Request type ${requestType} is not supported`);
      break;
  }     
}

async function createEvent(event) {
  const response = getReponseObj(event)

  try {
    const { config } = event.ResourceProperties
    const { password, username, host } = await getSecretValue(config.credsSecretName)
  

    const connection = mysql.createConnection({
      host,
      user: username,
      password,
      multipleStatements: true
    })

    connection.connect()

    const sqlScript = fs.readFileSync(path.join(__dirname, 'script.sql')).toString()
    const res = await query(connection, sqlScript)

    response.Status = 'SUCCESS';
    response.Data = { Result: res };

    console.log('SUCCESS')
    console.table(response)

    return response
  } catch (err) {
    throw new Error(err) 
  }
}

function updateEvent(event) {
  console.log('Custom::DBInitializer Update event')
  return {
    ...getReponseObj(event),
    Status: 'SUCCESS',
    Data: { Result: 'Update event Skipped' }
  };
} 

function query (connection, sql) {
  return new Promise((resolve, reject) => {
    connection.query(sql, (error, res) => {
      if (error) return reject(error)

      return resolve(res)
    })
  })
}

function deleteEvent() {
  return {
    Status: 'SUCCESS',
    Data: { Result: 'Delete event Skipped, nothing to delete' }
  }
}

function getSecretValue (secretId) {
  return new Promise((resolve, reject) => {
    secrets.getSecretValue({ SecretId: secretId }, (err, data) => {
      if (err) return reject(err)

      return resolve(JSON.parse(data.SecretString))
    })
  })
}

function getId(event) {
  return crypto
    .createHash('md5')
    .update(`${event.StackId}-${event.LogicalResourceId}`)
    .digest("hex").substring(0, 7);
}

function getReponseObj(event) {
  return {
    StackId: event.StackId,
    RequestId: event.RequestId,
    LogicalResourceId: event.LogicalResourceId,
    PhysicalResourceId: event.PhysicalResourceId ?? getId(event),
  };
}

The package.json

{
  "name": "rds-init-script",
  "version": "1.0.0",
  "description": "RDS initialization implementation in Node.js",
  "main": "index.js",
  "scripts": {},
  "keywords": [],
  "author": "",
  "license": "MIT",
  "dependencies": {
    "mysql": "^2.18.1",
    "aws-sdk": "^2.1377.0",
    "crypto": "^1.0.1"
  }
}

Finally the sql script

use customDB;

CREATE TABLE IF NOT EXISTS TableName1(
    DeviceID NVARCHAR(75) NOT NULL,
    ServerUuid BINARY(16) NOT NULL COMMENT 'Unique identifier',
    DeviceData MEDIUMBLOB NOT NULL COMMENT 'Binary data used to store images and documents.',
    LastUpdatedTimestamp TIMESTAMP(3) NOT NULL,
    PRIMARY KEY (DeviceID, ServerUuid)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8
AVG_ROW_LENGTH=512;

Then in our stack, the InitResources construct needs to be instantiated.

new InitResources(this, 'ResourceDBInitializer', {
    config: props.initConfig,
    vpc: props.vpc,
});

With this approach is possible to create a custom resource to execute a SQL script and if it fail, the stack will fail as well.

A final note: to understand better this example is better to first have read and understand this example from AWS.