Updated: 03 September 2023

Run a Database Migration on RDS with CDK Custom Resources

Refer to Custom cloud infrastructure as code with AWS CDK - CloudFormation Custom Resources Lambda Backend

Cloud Formation Custom Resources are the method by which we can provision resources that cloud formation doesn’t have an existing resource definition for but we still need to create.

They provide us with a way to provision or modify resources by way of a function

This enables us to run pretty much any code or before or after a particular resource lifecycle event so we can do things like provision 3rd party or on-prem services or even run something like a database migration when the database is created - the latter of which is what we’re going to do in this post

Note that there are other types of custom resources, but we’ll be specifically using one that uses a Lambda as its basis

Setup the CDK App

To create a CDK app, you will need to use the cdk CLI, init a new app like this:

1
mkdircustom-resources
2
cd custom-resources
3

4
cdk init --language typescript

The created app will have all the usual CDK files, for our purposes you can delete the default Stack in the lib directory. If you’re using the same name as I am this will be the lib/custom-resources-stack.ts file

Next, since we’ll need to create Custom Resources as the packages needed to configure an RDS instance as well as work with it using postgres we need to install the following packages using our package manager:

1
"@aws-cdk/aws-ec2": "1.109.0",
2
"@aws-cdk/aws-iam": "1.109.0",
3
"@aws-cdk/aws-lambda": "1.109.0",
4
"@aws-cdk/aws-lambda-nodejs": "1.109.0",
5
"@aws-cdk/aws-logs": "1.109.0",
6
"@aws-cdk/aws-rds": "1.109.0",
7
"@aws-cdk/aws-secretsmanager": "1.109.0",
8
"@aws-cdk/core": "1.109.0",
9
"@aws-cdk/custom-resources": "1.109.0",
10
"@types/pg": "^8.6.0",
11
"pg": "^8.6.0",

Replace the version in the above with the version used by your CDK app, you can see what this is in your pacakge.json file in the @aws-cdk/core dependency

Now that we’ve got all the dependencies in place, we can move on to actually building our the functionality

Define the Stacks

Our app will make use of two stack, one to setup the database and one to handle the migration by way of a Custom Resource, the stacks will need the following respectively:

A DatabaseStack with a vpc, secret, instance, and connection clearances as well as Exporting the above resources
A MigrateStack which will define our custom resource which requires the Exports from the DatabaseStack and contains a NodejsFunction for our Lambda, a Provider and a CustomResource definition

Once that’s all done, we can actually create the Lambda which will carry our our migration

`DatabaseStack`

Definition of a secret for our DB Credentials, we will want to export this for use by our MigrateStack by way of a public property on our class

1
const dbSecret = new sm.Secret(this, 'DBCredentials', {
2
  generateSecretString: {
3
    secretStringTemplate: JSON.stringify({
4
      username: 'dbAdmin',
5
    }),
6
    excludePunctuation: true,
7
    excludeCharacters: '/@"\' ',
8
    generateStringKey: 'password',
9
  },
10
})

Creation of a vpc for the RDS instance

1
const vpc = new ec2.Vpc(this, 'AppVPC', {
2
  maxAzs: 2,
3
})

The instance for our database:

1
const dbInstance = new rds.DatabaseInstance(this, 'Instance', {
2
  vpc,
3
  engine: rds.DatabaseInstanceEngine.postgres({
4
    version: rds.PostgresEngineVersion.VER_12_6,
5
  }),
6
  databaseName: this.dbName,
7
  // optional, defaults to m5.large
8
  instanceType: ec2.InstanceType.of(
9
    ec2.InstanceClass.BURSTABLE3,
10
    ec2.InstanceSize.SMALL
11
  ),
12
  credentials: rds.Credentials.fromSecret(dbSecret),
13
  publiclyAccessible: true,
14
})

Some network clearences to allow traffic to our database (We’re keeping this simple and allowing all connections, but in practice you would want to use a more secure connection strategy)

1
dbInstance.connections.allowFromAnyIpv4(ec2.Port.allTraffic())
2

3
dbInstance.connections.allowInternally(ec2.Port.allTraffic())

And lastly, we’ll export these to public properties with:

1
// top of the `DatabaseStack` class
2
public readonly dbInstance: rds.DatabaseInstance;
3
public readonly dbSecret: sm.Secret;
4
public readonly vpc: ec2.Vpc;
5
public readonly dbName: string = "appdb";
6

7

8
// after we have done all the setup above
9
this.vpc = vpc;
10
this.dbInstance = dbInstance;
11
this.dbSecret = dbSecret;

Putting all this together into a lib/database-stack.ts file to define the DatabaseStack we have:

lib/database-stack.ts

1
import * as cdk from '@aws-cdk/core'
2
import * as ec2 from '@aws-cdk/aws-ec2'
3
import * as sm from '@aws-cdk/aws-secretsmanager'
4
import * as rds from '@aws-cdk/aws-rds'
5
import * as cr from '@aws-cdk/custom-resources'
6
import * as lambda from '@aws-cdk/aws-lambda'
7
import * as iam from '@aws-cdk/aws-iam'
8
import * as logs from '@aws-cdk/aws-logs'
9
import { NodejsFunction } from '@aws-cdk/aws-lambda-nodejs'
10
import { CfnOutput } from '@aws-cdk/core'
11

12
export class DatabaseStack extends cdk.Stack {
13
  public readonly dbInstance: rds.DatabaseInstance
14
  public readonly dbSecret: sm.Secret
15
  public readonly vpc: ec2.Vpc
16
  public readonly dbName: string = 'appdb'
17

18
  constructor(scope: cdk.Construct, id: string, props?: cdk.StackProps) {
19
    super(scope, id, props)
20

21
    const dbSecret = new sm.Secret(this, 'DBCredentials', {
22
      generateSecretString: {
23
        secretStringTemplate: JSON.stringify({
24
          username: 'dbAdmin',
25
        }),
26
        excludePunctuation: true,
27
        excludeCharacters: '/@"\' ',
28
        generateStringKey: 'password',
29
      },
30
    })
31

32
    const vpc = new ec2.Vpc(this, 'AppVPC', {
33
      maxAzs: 2,
34
    })
35

36
    const dbInstance = new rds.DatabaseInstance(this, 'Instance', {
37
      vpc,
38
      engine: rds.DatabaseInstanceEngine.postgres({
39
        version: rds.PostgresEngineVersion.VER_12_6,
40
      }),
41
      databaseName: this.dbName,
42
      // optional, defaults to m5.large
43
      instanceType: ec2.InstanceType.of(
44
        ec2.InstanceClass.BURSTABLE3,
45
        ec2.InstanceSize.SMALL
46
      ),
47
      credentials: rds.Credentials.fromSecret(dbSecret),
48
      publiclyAccessible: true,
49
    })
50

51
    dbInstance.connections.allowFromAnyIpv4(ec2.Port.allTraffic())
52

53
    dbInstance.connections.allowInternally(ec2.Port.allTraffic())
54

55
    this.vpc = vpc
56
    this.dbInstance = dbInstance
57
    this.dbSecret = dbSecret
58
  }
59
}

MigrateStack

Props defintion so the DatabaseStack Exports can be provided:

1
interface StackProps extends cdk.StackProps {
2
  dbInstance: rds.DatabaseInstance
3
  dbSecret: sm.Secret
4
  vpc: ec2.Vpc
5
  dbName: string
6
}
7

8
// which can be destructured later with:
9
const { dbInstance, dbName, dbSecret, vpc } = props

NodeJS Lambda to run our migration:

1
const onEventHandler = new NodejsFunction(this, 'DatabaseMigrate', {
2
  vpc,
3
  runtime: lambda.Runtime.NODEJS_14_X,
4
  entry: 'lib/database-migrate-lambda.ts',
5
  handler: 'handler',
6
  environment: {
7
    DB_HOST: dbInstance.dbInstanceEndpointAddress,
8
    DB_PORT: dbInstance.dbInstanceEndpointPort,
9
    DB_USERNAME: dbSecret.secretValueFromJson('username').toString(),
10
    DB_PASSWORD: dbSecret.secretValueFromJson('password').toString(),
11
    DB_NAME: dbName,
12
  },
13
  logRetention: logs.RetentionDays.ONE_DAY,
14
  timeout: Duration.minutes(2),
15
})

The Provider to be used in the CustomResource Creation:

1
const databaseMigrationProvider = new cr.Provider(
2
  this,
3
  'DatabaseMigrateProvider',
4
  {
5
    onEventHandler,
6
    logRetention: logs.RetentionDays.ONE_DAY,
7
  }
8
)

Lastly, the CustomResource itself:

1
const databaseMigrationResource = new cdk.CustomResource(
2
  this,
3
  'DatabaseMigrateResource',
4
  {
5
    serviceToken: databaseMigrationProvider.serviceToken,
6
  }
7
)

The overall lib/migrate-stack.ts file will look like this:

lib/migrate-stack.ts

1
import * as cdk from '@aws-cdk/core'
2
import * as ec2 from '@aws-cdk/aws-ec2'
3
import * as sm from '@aws-cdk/aws-secretsmanager'
4
import * as rds from '@aws-cdk/aws-rds'
5
import * as cr from '@aws-cdk/custom-resources'
6
import * as lambda from '@aws-cdk/aws-lambda'
7
import * as iam from '@aws-cdk/aws-iam'
8
import * as logs from '@aws-cdk/aws-logs'
9
import { NodejsFunction } from '@aws-cdk/aws-lambda-nodejs'
10
import { Duration } from '@aws-cdk/core'
11

12
interface StackProps extends cdk.StackProps {
13
  dbInstance: rds.DatabaseInstance
14
  dbSecret: sm.Secret
15
  vpc: ec2.Vpc
16
  dbName: string
17
}
18

19
export class MigrateStack extends cdk.Stack {
20
  constructor(scope: cdk.Construct, id: string, props: StackProps) {
21
    super(scope, id, props)
22

23
    const { dbInstance, dbName, dbSecret, vpc } = props
24

25
    const onEventHandler = new NodejsFunction(this, 'DatabaseMigrate', {
26
      vpc,
27
      runtime: lambda.Runtime.NODEJS_14_X,
28
      entry: 'lib/database-migrate-lambda.ts',
29
      handler: 'handler',
30
      environment: {
31
        DB_HOST: dbInstance.dbInstanceEndpointAddress,
32
        DB_PORT: dbInstance.dbInstanceEndpointPort,
33
        DB_USERNAME: dbSecret.secretValueFromJson('username').toString(),
34
        DB_PASSWORD: dbSecret.secretValueFromJson('password').toString(),
35
        DB_NAME: dbName,
36
      },
37
      logRetention: logs.RetentionDays.ONE_DAY,
38
      timeout: Duration.minutes(2),
39
    })
40

41
    const databaseMigrationProvider = new cr.Provider(
42
      this,
43
      'DatabaseMigrateProvider',
44
      {
45
        onEventHandler,
46
        logRetention: logs.RetentionDays.ONE_DAY,
47
      }
48
    )
49

50
    const databaseMigrationResource = new cdk.CustomResource(
51
      this,
52
      'DatabaseMigrateResource',
53
      {
54
        serviceToken: databaseMigrationProvider.serviceToken,
55
      }
56
    )
57
  }
58
}

The Migration Lambda

The Migration Lambda itself is pretty much just a function that will use the Postgres Client for Node.js to run some SQL queries against the database - you can implement this however you want, and I would suggest using some kind of ORM if needed, but again, to keep it simple we’re going to just run some regular SQL, we can do this in a file called lib/database-migrate-lambda.ts which is what we actually referenced above when creating the NodeJS Lambda

lib/database-migrate-lambda.ts

1
import { Client } from 'pg'
2

3
interface EventType {
4
  RequestType: 'Create' | string
5
}
6

7
export const handler = async (event: EventType): Promise<any> => {
8
  const { DB_HOST, DB_PORT, DB_USERNAME, DB_PASSWORD, DB_NAME } = process.env
9

10
  // the RequestType that will be sent to us from CloudFormation when the lambda is executed
11
  if (event.RequestType == 'Create') {
12
    const port = +(DB_PORT || 5432)
13

14
    const rootClient = new Client({
15
      port,
16
      host: DB_HOST,
17
      user: DB_USERNAME,
18
      password: DB_PASSWORD,
19
      database: 'postgres',
20
    })
21

22
    try {
23
      await rootClient.connect()
24

25
      console.log(
26
        await rootClient.query(`
27
        CREATE DATABASE ${DB_NAME};
28
      `)
29
      )
30
    } catch (error) {
31
      console.warn(
32
        `Error creating db ${DB_NAME}, check if due to db existing, move on anyway`
33
      )
34
      console.warn(error)
35
    } finally {
36
      rootClient.end()
37
    }
38

39
    const appClient = new Client({
40
      port,
41
      host: DB_HOST,
42
      user: DB_USERNAME,
43
      password: DB_PASSWORD,
44
      database: DB_NAME,
45
    })
46

47
    await appClient.connect()
48

49
    console.log(
50
      await appClient.query(`
51
      CREATE TABLE users (
52
        id serial PRIMARY KEY,
53
        username VARCHAR ( 50 ) UNIQUE NOT NULL,
54
        password VARCHAR ( 50 ) NOT NULL
55
      );
56
      `)
57
    )
58

59
    console.log(
60
      await appClient.query(`
61
      INSERT INTO users
62
        (id, username, password)
63
      VALUES (1, 'helloworld', 'securepassword');
64
    `)
65
    )
66

67
    console.log(
68
      await appClient.query(`
69
      SELECT * FROM users;
70
      `)
71
    )
72

73
    appClient.end()
74
  }
75
}

Deploy the App Stacks

So, we’ve defined two stacks for our application, but we’re not yet ready to deploy since we need to actually get CDK to recognize and deploy these stacks.

To configure the deployment, we need to edit the bin/custom-resources.ts file to create the stack instance and pass the Exports from the DatabaseStack to the MigrateStack:

lib/custom-resources.ts

1
#!/usr/bin/env node
2
import 'source-map-support/register'
3
import * as cdk from '@aws-cdk/core'
4
import { DatabaseStack } from '../lib/database-stack'
5
import { MigrateStack } from '../lib/migrate-stack'
6

7
const app = new cdk.App()
8

9
const dbStack = new DatabaseStack(app, 'CRDatabaseStack')
10

11
const migrateStack = new MigrateStack(app, 'CRMigrateStack', {
12
  dbInstance: dbStack.dbInstance,
13
  dbName: dbStack.dbName,
14
  dbSecret: dbStack.dbSecret,
15
  vpc: dbStack.vpc,
16
})

Then, use yarn cdk deploy --all to build and deploy your application:

1
yarn cdk deploy --all

This will take a while to deploy, and the cdk CLI will ask you for some confirmations while it runs, once done you can look for the Lambda in CloudWatch to view the results of the deploy, however if there are any errors while running you will see it in the terminal or CloudFormation output

Final Notes

The above setup is just intended to be a starting point for using Lambdas to run custom logic on application deploys. This isn’t a database setup I’d recommend but it should allow you