Database schema migrations on AWS with Custom Resources and CDK

With AWS Custom Resources 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:

Here are the details of this solution:

• We’ll create the database Cluster 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” 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

Let’s see the code

1 —DB schema migration lambda

As mentioned above, we will use “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:

This code below shows how to use node-pg-migrate to run the migration scripts from the custom resource lambda function:👇

	export const handler = async (
	event: CdkCustomResourceEvent,
	context: Context
	): Promise<CdkCustomResourceResponse> => {
	const resp: CdkCustomResourceResponse = {
	StackId: event.StackId,
	RequestId: event.RequestId,
	LogicalResourceId: event.LogicalResourceId,
	PhysicalResourceId: context.logGroupName,
	};
	if (event.RequestType == "Delete") {
	return {
	...resp,
	Status: "SUCCESS",
	Data: { Result: "None" },
	};
	}
	try {
	const connectionString = await getDbConnectionString();
	const migrationResult = await runner({
	databaseUrl: connectionString,
	migrationsTable: "migration-table",
	dir: `${__dirname}/migrations`,
	direction: "up",
	verbose: true,
	});
	const nbOfExecutedScripts = migrationResult.length;
	return {
	...resp,
	Status: "SUCCESS",
	Data: { Result: nbOfExecutedScripts },
	};
	} catch (error) {
	if (error instanceof Error) {
	resp.Reason = error.message;
	}
	return {
	...resp,
	Status: "FAILED",
	Data: { Result: error },
	};
	}
	};

view raw db-schema-migration-lambda.ts hosted with ❤ by GitHub

☝️ 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:

	const lambdaFunction = new NodejsFunction(
	this,
	`${applicationName}-db-migration-function`,
	{
	memorySize: 128,
	timeout: Duration.seconds(60),
	runtime: Runtime.NODEJS_18_X,
	architecture: Architecture.ARM_64,
	bundling: {
	commandHooks: {
	beforeBundling: Noop,
	beforeInstall: Noop,
	afterBundling: (_, outputDir: string) => {
	return [
	`mkdir -p ${outputDir}/migrations && cp ${migrationDirectoryPath}/* ${outputDir}/migrations`,
	];
	},
	},
	},
	entry: path.join(__dirname, "./db-migration-lambda-function/handler.ts"),
	functionName: `${applicationName}-db-migration`,
	handler: "handler",
	role: executionRole,
	vpc: vpc,
	vpcSubnets: vpc.selectSubnets({
	subnetType: SubnetType.PRIVATE_WITH_EGRESS,
	}),
	securityGroups: [lambdaSecurityGroup],
	environment: {
	DB_CREDENTIALS_SECRET_NAME: secret.secretName,
	DB_CLUSTER_HOST_NAME: database.clusterEndpoint.hostname,
	DB_CLUSTER_PORT: database.clusterEndpoint.port.toString(),
	DB_NAME: dbName,
	},
	}
	);

view raw db-migration-lambda-cdk-definition.ts hosted with ❤ by GitHub

This Lambda Function requires VPC access 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:

	const secretAccessPolicy = new PolicyStatement({
	effect: Effect.ALLOW,
	actions: ["secretsmanager:GetSecretValue"],
	resources: [secret.secretArn],
	});
	executionRole.addToPolicy(secretAccessPolicy);
	executionRole.addManagedPolicy(
	ManagedPolicy.fromAwsManagedPolicyName(
	"service-role/AWSLambdaVPCAccessExecutionRole"
	)
	);
	dbSecurityGroup.addIngressRule(lambdaSecurityGroup, Port.tcp(5432));

view raw db-migration-lambda-policy.ts hosted with ❤ by GitHub

📦 On embedding migration scripts: In our example, Migration scripts need to be included in the Lambda function package. We use the afterBunding 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 the complete definition of this Lambda Function.

2 —Defining the custom resource:

Straightforward to define with CDK:

	createCustomResource(
	database: IDatabaseCluster,
	lambdaFunction: IFunction,
	migrationDirectoryPath: string
	) {
	const dbMigrationProvider = new Provider(this, "DbMigrationProvider", {
	onEventHandler: lambdaFunction,
	});
	const customResource = new CustomResource(
	this,
	"Custom::DbSchemaMigration",
	{
	serviceToken: dbMigrationProvider.serviceToken,
	resourceType: "Custom::DbSchemaMigration",
	properties: {
	migrationDirectoryHash: computeDirHash(migrationDirectoryPath),
	},
	}
	);
	customResource.node.addDependency(database);
	}
	}

view raw db-migration-custom-resource.ts hosted with ❤ by GitHub

☝️ 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 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:

	export class DbSchemaMigrationWithCustomResourcesStack extends Stack {
	constructor(scope: Construct, id: string, props?: StackProps) {
	super(scope, id, props);
	const { vpc } = new SampleVPC(this, "SampleVPCForDbMigrations");
	const database = new Database(this, "SampleDatabaseForDbMigrations", {
	applicationName: "sampleapp",
	migrationDirectoryPath: path.join(__dirname, "./migrations"),
	vpc,
	});
	}
	}

view raw db-migration-stack.ts hosted with ❤ by GitHub

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:

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

Hope you find it useful, and thanks for reading !

Further readings

@aws-cdk/custom-resources module · AWS CDK
Configuring a Lambda function to access resources in a VPC
node-pg-migrate - Postgresql database migration management tool for node.js