Database schema migrations on AWS with Custom Resources and CDK
Tags: cdk, customresources, database, serverless
Published: 2023-06-14 18:36:19



![Photo by [Tomas Kirvėla](https://unsplash.com/ja/@tomkirvela?utm_source=medium&utm_medium=referral) on [Unsplash](https://unsplash.com?utm_source=medium&utm_medium=referral)](https://cdn-images-1.medium.com/max/8576/0*4Yin6EjOtLJ3LYAa)

With AWS [Custom Resources](https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/template-custom-resources.html) you can manage resources that are not natively supported by CloudFormation. You can execute application-specific provisioning logic as well as custom code during the deployment, the update or the deletion of a Stack. 

In this article, we will focus on using Custom Resources to handle schema migrations on an Aurora Postgres database. We will create a custom database migration resource that executes schema changes during the Stack deployment. To accomplish this, we will associate a Lambda function with our Custom Resource, this function ensures that any new changes to the database are automatically applied when necessary.

## Solution overview

We’ll use Aurora Serverless V2 with Postgres engine, NodeJs and typescript for the Lambda function code and CDK for the IaC:

![DB schema migrations using custom resources -Solution overview ](https://cdn-images-1.medium.com/max/4458/1*RaSEXAwy2Cvk0uWEbL3ouA.png)

Here are the details of this solution: 

* We’ll create the [database Cluster](https://github.com/ziedbentahar/db-schema-migration-with-custom-resources/blob/6d783560e1fedeadbfa7fb039d45c1b38e4485eb/lib/database.ts#L60) on an isolated subnet as well as a secret that stores the credentials of the database. This secret is accessed by the DB schema migration Lambda function .

* The **DB schema migration Lambda** **function** is responsible for executing the necessary database schema changes. It uses the “[node-pg-migrate](https://salsita.github.io/node-pg-migrate/#/)” tool, which allows us to run migration scripts programmatically. We’ll need to configure this lambda function to access the Aurora resource in our VPC.

* **Migration scripts** files are included in the zip package of the lambda function : each file contains a set of changes to apply to the database, these files are stored in the same repository as the application. Each new set of changes need to be written into a new distinct file.

* **DB migration custom resource** invokes the lambda function during the stack deployment when it detects changes on the migration scripts.

### TL;DR

You can find the complete repo of this solution here:
[**GitHub - ziedbentahar/db-schema-migration-with-custom-resources**](https://github.com/ziedbentahar/db-schema-migration-with-custom-resources)

## Let’s see the code

### 1 —DB schema migration lambda

As mentioned above, we will use “[node-pg-migrate](https://salsita.github.io/node-pg-migrate/#/)” to run schema changes. 

One interesting aspect of this library is its flexibility in defining migration scripts: We have the option to write our migration scripts in either ES or TypeScript, allowing us to define database schemas with code. Alternatively, we can also define migration scripts as plain SQL files, providing a more traditional approach to managing database schema changes:

![Example of a migration script directory](https://cdn-images-1.medium.com/max/2000/1*0Xfd8Ez_KGdNB846gUxyrQ.png)

This code below shows how to use node-pg-migrate to run the migration scripts from the custom resource lambda function:👇
{% gist https://gist.github.com/ziedbentahar/3b52f75e2fbced31e6237d15e950b1cf.js %}
☝️ **Note**: This lib can handle forward and backward migrations but with  our solution we will only be supporting forward migrations.

Here is CDK definition of this lambda function:
{% gist https://gist.github.com/ziedbentahar/da05a10dc4713d0a4383f4abfcb8a90f.js %}
This Lambda Function requires [VPC access](https://docs.aws.amazon.com/lambda/latest/dg/configuration-vpc.html) as it needs to access the Aurora Database. We will also make sure to place the network interface associated with the Lambda function in a `PRIVATE_WITH_EGRESS` subnet as we need to access the secrets manager service. 

Additionally, we’ll associate the security group of the Lambda function to the security group of the Aurora Cluster:
{% gist https://gist.github.com/ziedbentahar/9cedbdca7e73aa6ae95fc85cc9e11783.js %}
📦 **On embedding migration scripts:** In our example, Migration scripts need to be included in the Lambda function package. We use the [`afterBunding`](https://docs.aws.amazon.com/cdk/api/v1/docs/@aws-cdk_aws-lambda-nodejs.ICommandHooks.html) hook to copy the content of the migration dir to the bundle output dir:

    afterBundling: (_, outputDir: string) => {
      return [
        `mkdir -p ${outputDir}/migrations && cp ${migrationDirectoryPath}/* ${outputDir}/migrations`,
      ];
    },

You can find [here](https://github.com/ziedbentahar/db-schema-migration-with-custom-resources/blob/6d783560e1fedeadbfa7fb039d45c1b38e4485eb/lib/database.ts#L101) the complete definition of this Lambda Function.

### 2 —Defining the custom resource:

Straightforward to define with  CDK:
{% gist https://gist.github.com/ziedbentahar/faede7479c4d1042eb65ec59ffc229d2.js %}
**☝️ One important note**: the `computeHash` function computes a hash of the migration script directory content. This hash is passed as a property of the custom resource. During the stack deployment, whenever this hash changes, the lambda function gets invoked and the new migrations scripts are taken into account.

You will find [here](https://github.com/ziedbentahar/db-schema-migration-with-custom-resources/blob/6d783560e1fedeadbfa7fb039d45c1b38e4485eb/lib/database.ts#L185) the definition of the Custom::DbSchemaMigration custom resource.

### 3-Putting all together

And here is how we use this Database construct that supports schema migration:
{% gist https://gist.github.com/ziedbentahar/f084df59628640dec157adf04bc0dd9e.js %}
Et voilà!  Once you make a deployment with new migration script files, you can see the DB schema migration in action via the Lambda CloudWatch logs:

![Db schema migration logs — Lambda logs](https://cdn-images-1.medium.com/max/4136/1*WPgPcsyHRol84DZYmu_PsA.png)

## Wrapping up

In this article, we have seen how to use custom resources to run database schema migrations on AWS. CDK makes it a breeze ! You can find a complete sample application repository with the complete github action workflow here:
[**GitHub - ziedbentahar/db-schema-migration-with-custom-resources**](https://github.com/ziedbentahar/db-schema-migration-with-custom-resources)

Hope you find it useful, and thanks for reading !

## Further readings
[**@aws-cdk/custom-resources module · AWS CDK**](https://docs.aws.amazon.com/cdk/api/v1/docs/custom-resources-readme.html)
[**Configuring a Lambda function to access resources in a VPC**](https://docs.aws.amazon.com/lambda/latest/dg/configuration-vpc.html)
[**node-pg-migrate - Postgresql database migration management tool for node.js**](https://salsita.github.io/node-pg-migrate/#/)