Drizzle Migrations

Overview

Migrations are versioned database schema changes that allow us to automatically update database structure and keep data consistent across environments. In Drizzle, migrations are generated by comparing our schema definition against the current database state, producing SQL files that can be safely applied to update the database.

How to run migrations locally

To work with migrations locally, use two commands:

pnpm run db:generate

The generate command outputs information about detected schema changes and creates new migration files. This should be run if the schema was updated. It creates a migration as an sql file combined with meta information necessary for drizzle to properly apply migrations.

pnpm run db:migrate

The migrate command applies migrations and confirms successful execution. This should be used locally to update the local database based on updated migrations.

How migrations are run during deployment

Migrations Lambda

Since Aurora runs in a VPC and is not directly accessible from GitHub Actions, we added a dedicated Lambda function for handling database migrations. The Lambda provisions a CustomResource which is used to run the handler function.

The handler function retrieves database credentials from AWS Secrets Manager and runs migrations with the help of drizzle-kit library. In the end it returns an object that reports back success or failure to Lambda.

In order to be able to run the handler function with a predefined set of dependencies, we created it as a separate monorepo package called packages/migrations. This package contains migrations files and a Dockerfile that runs a build and executes a handler function.

In order to be able to execute a docker image from CustomResource we wrap it with a DockerImageFunction construct which is configured with VPC access and security group permissions to be able to reach the Aurora database cluster. It also ensures that the Lambda is connected to Datadog.

CDK Integration

The migrations Lambda is imported inside of the CDK and is executed during deployment. It is added as a dependency of the remix application deployment, so that if the migration fails then the deployment is interrupted and the Remix App is not deployed.

Pre-deployment step

In order to make sure that migrations work, we added a separate step in the GitHub CDK workflow called test-migrations and it runs before deployment. Inside of this step, we run migrations against an empty database inside of the Docker container. This step allows us to discover migration failures at an early stage and gives confidence that migrations can later successfully run during deployment.

Flow Diagram

flowchart TD
    Start([Deploy with CDK Workflow Triggered]) --> PreTest[Pre-deployment Step:<br/>test-migrations]
    PreTest --> TestDocker[Run Migrations Against<br/>Empty DB in Docker]
    TestDocker --> TestResult{Test<br/>Successful?}

    TestResult -->|Failure| HaltEarly([Halt Deployment Early<br/>Migration Issues Detected])
    TestResult -->|Success| CDK[CDK Provisions<br/>CustomResource]

    CDK --> CustomResource[CustomResource Invokes<br/>DockerImageFunction<br/>with VPC Access & Security Groups]

    CustomResource --> Handler[Execute Handler Function<br/>from packages/migrations]
    Handler --> GetCreds[Retrieve DB Credentials<br/>from Secrets Manager]
    GetCreds --> RunDrizzle[Run drizzle-kit migrate<br/>Applies pending migrations if any]
    RunDrizzle --> MigrationResult{Migration<br/>Successful?}

    MigrationResult -->|Success| SuccessResponse[Handler Returns Success to CustomResource<br/>New migrations applied or none pending]
    SuccessResponse --> End([Continue Deployment])

    MigrationResult -->|Failure| ErrorResponse[Handler Returns Failure to CustomResource]
    ErrorResponse --> Halt([Halt Deployment<br/>Remix App Not Deployed])

Best practices

Migrations should run inside of a transaction. If it fails, the database should rollback. → This is already supported out of box by Drizzle.

Don't change migrations manually, but only with drizzle-kit.

It is strongly recommended to avoid all breaking changes (like deleting a column or renaming a column). Add new columns as nullable or with a default value.

It is suggested not to connect to Aurora from the local machine. Instead, in the case of a debugging emergency, we can use one of the backup snapshots and load them to the local DB.

Right now, CustomResource has a timestamp property which executes the resource and Docker image on every deployment. This is good at the early stage for easier debugging, but we can consider changing it in the future.

Custom migrations

Some migrations should be written as custom SQL, for example when adding data or updating data that belongs to us, such as user enhancement steps.

Custom migrations can be created using:

pnpm run db:add-custom-migration --name <kebab-case-description>

Drizzle handles numbering and adding the file in the correct place automatically. If the --name parameter is not used, a random name is assigned.

The file will then end up in packages/migrations/drizzle/ and is applied in order alongside generated migrations

Rules

• Never edit a migration that has already been applied
• Never create or rename migration files manually
• Never make schema changes using a custom migration