Automating MySQL Database Migrations in AWS CI/CD using Knex.js and TypeScript

Managing database schema changes across environments (Development, Staging, Production) can quickly become a bottleneck if done manually. Automating schema migrations inside your AWS deployment pipeline ensures that database updates are applied consistently, safely, and in perfect sync with your application code.

In this guide, we will walk through setting up automated MySQL migrations in AWS using Knex.js, TypeScript, and AWS CodeBuild.

Architecture & Pipeline Flow

The goal is to execute schema migrations automatically during the build/deployment phase. The workflow runs in the following order:

Knex handles schema version control by maintaining a knex_migrations table inside your target MySQL database. Every migration file is tracked with a timestamp; Knex runs only pending scripts, preventing duplicate executions.

Step 1: Project Configuration & Dependencies

Install Knex, the MySQL client driver, and TypeScript execution tools:

Bash

npm install knex mysql2
npm install --save-dev typescript ts-node @types/node

Update your package.json to include dedicated migration scripts:

JSON

{
  "scripts": {
    "db:migrate": "knex --knexfile src/knexfile.ts migrate:latest",
    "db:rollback": "knex --knexfile src/knexfile.ts migrate:rollback",
    "db:make-migration": "knex --knexfile src/knexfile.ts migrate:make"
  }
}

Step 2: Setting Up knexfile.ts

Create a knexfile.ts in your project root or src/ directory. This file configures the connection parameters and specifies where migration files live.

TypeScript

import type { Knex } from 'knex';

const config: { [key: string]: Knex.Config } = {
  production: {
    client: 'mysql2',
    connection: {
      host: process.env.DB_HOST,
      port: Number(process.env.DB_PORT) || 3306,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: process.env.DB_NAME,
      ssl: process.env.DB_SSL === 'true' ? { rejectUnauthorized: true } : false,
    },
    migrations: {
      tableName: 'knex_migrations',
      directory: './migrations',
      extension: 'ts',
    },
    pool: {
      min: 2,
      max: 10,
    },
  },
};

export default config;

Step 3: Writing Idempotent Migration Scripts

To ensure safety in production environments, migrations should be idempotent (safe to run even if partially executed or interrupted) and always include a downfunction for rollback capability. Use Knex’s schema helper methods like hasTableand hasColumnto prevent accidental runtime errors.

Create a new migration script using:

Bash

npm run db:make-migration add_status_to_users

Edit the generated migration file:

TypeScript
import { Knex } from 'knex';

export async function up(knex: Knex): Promise<void> {
  const hasTable = await knex.schema.hasTable('users');

  if (hasTable) {
    const hasColumn = await knex.schema.hasColumn('users', 'status');
    if (!hasColumn) {
      await knex.schema.alterTable('users', (table) => {
        table.string('status', 20).defaultTo('ACTIVE').notNullable().index();
      });
    }
  }
}

export async function down(knex: Knex): Promise<void> {
  const hasTable = await knex.schema.hasTable('users');

  if (hasTable) {
    const hasColumn = await knex.schema.hasColumn('users', 'status');
    if (hasColumn) {
      await knex.schema.alterTable('users', (table) => {
        table.dropColumn('status');
      });
    }
  }
}

Step 4: AWS CI/CD Pipeline Integration

To run migrations seamlessly in your AWS deployment script (deploy.sh), encapsulate the migration execution inside a dedicated shell function. Place this step after unit tests pass and before updating application services (e.g., ECS, Lambda, or EC2 instances).

deploy.shsnippet
Bash

#!/usr/bin/env bash
set -e

# 1. Run Unit Tests
echo "Running unit tests..."
npm test

# 2. Migration Function
migrateBackend() {
  echo "Starting MySQL database migration via Knex..."

  # Ensure DB credentials are exported in environment
  export NODE_ENV=production

  if npm run db:migrate; then
    echo "Database migration completed successfully."
  else
    echo "Database migration failed! Initiating rollback..."
    npm run db:rollback || true
    exit 1
  fi
}

# Run database migration
migrateBackend

# 3. Deploy Application Code
echo "Deploying application service..."
# aws ecs update-service --cluster my-cluster --service my-service --force-new-deployment

Step 5: AWS CodeBuild Configuration

In your buildspec.yml, execute deploy.sh during the buildor post_build phase. Ensure that database environment variables are injected securely using AWS Secrets Manager or Systems Manager (SSM) Parameter Store.

YAML

version: 0.2

env:
  secrets-manager:
    DB_HOST: "production/db/credentials:host"
    DB_USER: "production/db/credentials:username"
    DB_PASSWORD: "production/db/credentials:password"
    DB_NAME: "production/db/credentials:dbname"
  variables:
    DB_PORT: "3306"
    DB_SSL: "true"

phases:
  install:
    runtime-versions:
      nodejs: 20
    commands:
      - npm ci
  build:
    commands:
      - chmod +x ./deploy.sh
      - ./deploy.sh

Key Takeaways & Best Practices

1. Test Rollbacks: Periodically verify that your down migration functions run correctly by simulating failures in lower environments.

2. Never Modify Existing Migrations:
Once a migration script has executed in Staging or Production, do not alter its contents. Always create a new migration file to fix or alter schema attributes.

3. Keep Schema Checks Defensive: Using checks like hasColumn and hasTable prevents pipeline failures caused by out-of-sync developer environments.

4. Isolate Database Permissions: The CI/CD database user should have DDL permissions (CREATE, ALTER, DROP) restricted to the target schema and require SSL connections.