CDK DB Migration

AWS CDK L3 construct for managing DB migrations. Currently implemented DBMS:

• Athena

I created this construct because CloudFormations Glue Table doesn't support TBLPROPERTIES. I needed an alternative to create a table. Since creating a table is a DB migration, I created a migration construct instead of a simple table construct, which would be hard to impossible to update.

Usage

import * as Migration from 'cdk-db-migration';

const m1 = new Migration.Athena(this, 'M1', {
  up: 'CREATE EXTERNAL TABLE foo ...;',
  down: 'DROP TABLE foo;',
 });

const m2 = new Migration.Athena(this, 'M2', {
  dependsOn: m1,
  up: 'ALTER TABLE foo ...;',
  down: 'ALTER TABLE foo ...;',
});

Every migration requires a query for up and down migrations. up is executed when the migration is created. down is executed when the migration is destroyed.

A full example including creating bucket, database, workgroup and permissions can be found in the test directory.

Notes

No modifications: The construct will refuse to update any existing migration, because this is not how migrations work. Add another migration or first delete the migration, then add the modified statement.

Dependencies: Since migrations (might) depend on one another, make sure to set dependencies where required. In CDK you usually add dependencies like this:

const m1 = new Migration.Athena(this, 'M1', {...});
const m2 = new Migration.Athena(this, 'M2', {...});
m2.node.addDependency(m1);

Since dependencies are a very common pattern for migrations, a migration also accepts dependencies directly:

const m1 = new Migration.Athena(this, 'M1', {...});
const m2 = new Migration.Athena(this, 'M2', {
  dependsOn: m1,
  ...
});

Permissions: The Lambda function which runs the migrations, is not authorized to do anything at all, because the required permissions are very custom to the use case (database, workgroup, S3 location, KMS etc). Instead of giving too wide permissions by default, none are given at all. The construct exposes the IAM role and you need to grant the required permissions.

Best solution for your use case?: While the construct is capable of managing the state of a database over time, have a good thought if you really want to do this with CDK/CloudFormation. CloudFormation can ony handle up to 500 resources in a stack, so this (minus all the other resources in your stack) is going to be your hard limit of migrations. Migrations are executed by a Lambda function. Since the maximum execution time of a Lambda function is 15 minutes, migrations cannot exceed this limit.