CDK Pipelines - Migrating a database in the pipeline

I have a CDK Pipelines pipeline that is handling the self mutation and deployment of my application on ECS and I am having a tough time figuring out how to implement database migrations.

My migration files as well as the migration command reside inside of the docker container that are built and deployed in the pipeline. Below are two things I've tried so far:

My first thought was just creating a pre step on the stage, but i believe there is a chicken/egg situation. Since the migration command requires database to exist (as well as having the endpoint and credentials) and the migration step is pre, the stack doesn't exist when this command would run...

    const pipeline = new CodePipeline(this, "CdkCodePipeline", {
      // ...
      // ...
    }

    pipeline.addStage(applicationStage).addPre(new CodeBuildStep("MigrateDatabase", {
      input: pipeline.cloudAssemblyFileSet,
      buildEnvironment: {
        environmentVariables: {
          DB_HOST: { value: databaseProxyEndpoint },
          // ...
          // ...
        },
        privileged: true,
        buildImage: LinuxBuildImage.fromAsset(this, 'Image', {
          directory: path.join(__dirname, '../../docker/php'),
        }),
      },
      commands: [
        'cd /var/www/html',
        'php artisan migrate --force',
      ],
    }))

In the above code, databaseProxyEndpoint has been everything from a CfnOutput, SSM Parameter to a plain old typescript reference but all failed due to the value being empty, missing, or not generated yet.

I felt this was close, since it works perfectly fine until I try and reference databaseProxyEndpoint.

My second attempt was to create an init container in ECS.

   const migrationContainer = webApplicationLoadBalancer.taskDefinition.addContainer('init', {
      image: ecs.ContainerImage.fromDockerImageAsset(webPhpDockerImageAsset),
      essential: false,
      logging: logger,
      environment: {
        DB_HOST: databaseProxy.endpoint,
        // ...
        // ...
      },
      secrets: {
        DB_PASSWORD: ecs.Secret.fromSecretsManager(databaseSecret, 'password')
      },
      command: [
        "sh",
        "-c",
        [
          "php artisan migrate --force",
        ].join(" && "),
      ]
    });

    // Make sure migrations run and our init container return success
    serviceContainer.addContainerDependencies({
      container: migrationContainer,
      condition: ecs.ContainerDependencyCondition.SUCCESS,
    });

This worked, but I am not a fan at all. The migration command should run once in the ci/cd pipeline on a deploy, not when the ECS service starts/restarts or scales... My migrations failed once and it locked up cloudformation because the health check failed both on the deploy and then naturally on the rollback as well causing a completely broken loop of pain.

Any ideas or suggestions on how to pull this off would save me from losing the remaining hair i have left!

You can run your migrations (1) within a stack's deployment with a Custom Resource construct, (2) after a stack's or stage's deployment with a post Step, (3) or after the pipeline has run with an EventBridge rule.

1. Within a stack: Migrations as a Custom Resource

One option is to define your migrations as a CustomResource. It's a CloudFormation feature for executing user-defined code (typically in a Lambda) during the stack deployment lifecycle. See @mchlfchr's answer for an example. Also consider the CDK Trigger construct, a higher-level Custom Resource implementation.

2. After a stack or stage: "post" Step

If you split your application into, say, a StatefulStack (database) and StatelessStack (application containers), you can run your migrations code as a post Step between the two. This is the approach attempted in the OP.

In your StatefulStack, the variable producer, expose a CfnOutput instance variable for the environment variable values: readonly databaseProxyEndpoint: CfnOutput. Then consume the variables in a pipeline migration action by passing them to a post step as envFromCfnOutputs. The CDK will synth them into CodePipeline Variables:

pipeline.addStage(myStage, { // myStage includes the StatefulStack and StatelessStack instances
    stackSteps: [
        {
            stack: statefulStack,
            post: [
                new pipelines.CodeBuildStep("Migrate", {
                    commands: [ 'cd /var/www/html', 'php artisan migrate --force',],
                    envFromCfnOutputs: { TABLE_ARN: stack1.tableArn },
                    // ... other step config
                }),
            ],
        },
    ],
    post: // steps to run after the stage
});

The addStage method's stackSteps option runs post steps after a specific stack in a stage. The post option work similarly, but runs after the stage.

3. After the Pipeline execution: EventBridge rule

Although it's likely not the best option, you could run migrations after the pipeline executes. CodePipeline emits events during pipeline execution. With an EventBridge rule, listen for CodePipeline Pipeline Execution State Change events where "state": "SUCCEEDED".

Note on failure modes: The three options have different failure modes. If the migrations fail as a Custom Resource, the StatefulStack deployment will fail (with changes rolled back) and the pipeline execution will fail. If the migrations are implemented as a step, the pipeline execution will fail but the StatefulStack won't roll back. Finally, if migrations are event-triggered, a failed migration will affect neither the stack nor execution, as they will already be finished when the migrations run.