Database Migrations in CI: Automating Schema Updates

Previously in this course, we covered pulling images in production to ensure your server runs the latest version of your application. Today, we bridge the gap between application code and persistent data by automating your database migrations.

In a professional environment, you never want to manually run SQL scripts against a production database. It’s error-prone, undocumented, and difficult to audit. By integrating migrations into your CI/CD pipeline, you ensure that every deployment is preceded by the necessary schema updates, keeping your data layer in sync with your application logic.

Databases and Migrations: The First Principle

At its core, a database migration is a version-controlled script that transitions your database schema from one state to another. Whether you are adding a column, creating a new table, or indexing a field, these changes must be applied in a specific order.

When we talk about databases, migrations, and deployment, the golden rule is "Migrations first, code second." If your code expects a users table to have a phone_number column, that column must exist in the database before the new application container starts. If you deploy the code first, your application will crash the moment it tries to read or write to that missing column.

Automating Migration Scripts in GitHub Actions

To automate this, we treat our migration tool as a CLI command that our runner executes. Most modern frameworks (like Django, Rails, or Laravel) provide built-in migration tools, but the logic remains the same regardless of the stack.

Here is a conceptual look at how to structure a migration job in your YAML pipeline. We use the needs keyword to ensure migrations complete successfully before the deploy job triggers.

YAML
jobs:
  migrate:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout code
        uses: actions/checkout@v3

      - name: Run Database Migrations
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
        run: |
          # Replace this with your specific migration command
          # e.g., 'python manage.py migrate' or 'npx prisma migrate deploy'
          ./bin/migrate-db.sh

  deploy:
    needs: migrate
    runs-on: ubuntu-latest
    steps:
      - name: Deploy application
        run: ./scripts/deploy.sh

Why "Run Before" is Mandatory

If you skip the needs: migrate constraint, you introduce a race condition. If your migration takes 30 seconds to run and your deployment starts simultaneously, your old code might be running against the new schema, or your new code might be running against the old schema.

By enforcing this dependency in GitHub Actions, we turn the migration step into a gate. If the migration script fails (e.g., due to a syntax error or a constraint violation), the pipeline stops immediately. Your broken code never reaches the production server.

Hands-on Exercise: The Migration Gate

1. Create a script: In your repository, add a bin/migrate-db.sh file. For this exercise, add a simple echo "Applying migrations..." followed by exit 0.
2. Update your workflow: Edit your existing pipeline YAML file to include a migrate job similar to the example above.
3. Add the dependency: Ensure your deployment job includes needs: migrate.
4. Verify: Push the changes to GitHub and watch the Actions tab. You should see the migrate job execute successfully before the deploy job begins.

Common Pitfalls

• Non-Idempotent Scripts: Migrations should be idempotent, meaning running them twice shouldn't cause errors. Use tools that track which migrations have already been applied (like those discussed in Implementing Schema Migrations: A Guide for Scalable Databases).
• Hardcoding Credentials: Never put your production database password in your scripts. Always use GitHub Secrets as we learned in Managing Secrets.
• Large Data Migrations: If you need to transform millions of rows, don't do it during the deployment pipeline. That will cause your deployment to time out. Keep migrations focused on schema changes only.

FAQ

What if a migration fails halfway? Modern migration tools use transactions. If a script fails, the database rolls back to the previous state. Always ensure your chosen tool supports transactional migrations.

Should I run migrations in production? Yes, but only through your automated pipeline. Never manually run them from your local machine against a production host.

Can I run migrations in parallel? No. Migrations must be sequential. If you have multiple app instances, ensure only one instance runs the migration command to avoid locking conflicts.

Recap

Automating your database schema changes ensures that your environment remains consistent. By treating migrations as a mandatory gate in your pipeline, you prevent deployment failures and ensure your application always has the data structure it expects. For more on the theory of maintaining these over time, review Schema Versioning Basics: Managing Database Evolution for SaaS.

Up next: Blue-Green Deployment Concept, where we will learn how to shift traffic between environments with minimal downtime.