Database Migrations Guide

Last updated: January 2, 2025

Core Principles

Important Guidelines

  • Never delete or modify checked in (committed) migration files for sequential upgrades. (If you are doing a downgrade, then you should revert to a commit before the migration was created after the DB downgrade has succeeded.)
  • If you delete a migration that was commited (without downgrading) and then try to upgrade to a new migration you will get a split head in your Staging database and the only solution at that point is to tear down and rebuild the database (whole environment), which can be done via the Site Detail page.
  • Always create new migrations for changes.
  • Test migrations in your dev SSH remote before pushing to Staging. Test in Staging before promoting to Production.
  • Use the merge process for multiple heads in dev.

Creating Migrations

Prerequisites

  1. Ensure you have a "versions" directory in your migrations folder
  2. Activate your Python virtual environment: 
    source /home/ec2-user/py3.11-venv/bin/activate

Initial Setup

Before creating new migrations, initialize your database and run the first (empty) migration:

flask db upgrade

Generate New Migrations

After making changes to app/models.py:

flask db migrate -m "describe the migration"

Apply Migrations

Development Environment:

flask db upgrade

Important

To deploy migrations to staging and production, ensure your migration files are included in your commit to the staging and main branches.

Rolling Back Migrations

Development Environment

flask db downgrade

Staging/Production Environment

  1. Navigate to your SiteDetail page at app.devopser.io
  2. Use the "downgrade Staging/Production database" button and wait until the relevant workspace shows as "applied".
  3. Update your code: 
    # Find and checkout the commit to rollback to (one that no longer includes the migration you are rolling back)
git log  # Find the commit hash
git checkout <commit-hash>
git checkout -b rollback-branch-name

# Test in staging
git checkout staging
git merge rollback-branch-name
git push origin staging

# Deploy to production
git checkout main
git merge rollback-branch-name
git push origin main

Note About Downgrades

The downgrade process occurs one version at a time. To downgrade multiple versions, you'll need to trigger the downgrade button multiple times.

Best Practices for Alembic Migrations

  1. Always review auto-generated migrations before committing
  2. Use consistent naming conventions for models and tables
  3. Keep test tables separate from your main database
  4. Test migrations in staging before production deployment
  5. Document and review migration scripts as part of your code review process
  6. Only roll back 1 migration at a time. However you can upgrade the database with multiple migrations at a time.

Important Note About Table Names

Tables aren't actually deleted during migrations - only your application schema changes. Consider this when naming tables to avoid conflicts with previous migrations.

Need Help?

Need Help?

Join our Slack community to connect with other developers and get help in the #support channel.

Chat with our AI assistant