Verifying migration safety

Verifying migration safety - 图1Supporting repository

The change described in this section can be found in PR #8 in the supporting repository.

Verifying migration safety

As the database is a critical component of our application, we want to make sure that when we make changes to it, we don’t break anything. Ill-planned migrations can cause data loss, application downtime and other issues. Atlas provides a mechanism to verify that a migration is safe to run. This mechanism is called migration linting and in this section we will show how to use it to verify that our migration is safe to run.

Linting the migration directory

To lint our migration directory we can use the atlas migrate lint command. To demonstrate this, let’s see what happens if we decide to change the Title field in the User model from optional to required:

  1. // Fields of the User.
  2. func (User) Fields() []ent.Field {
  3.     return []ent.Field{
  4.         field.String("name"),
  5.         field.String("email").
  6.           Unique(),
  7. --      field.String("title").
  8. --         Optional(),
  9. ++      field.String("title"),
  10.     }
  11. }

Let’s re-run codegen:

  1. go generate ./...

Next, let’s automatically generate a new migration:

  • MySQL
  • MariaDB
  • PostgreSQL
  • SQLite
  1. atlas migrate diff user_title_required \
  2.   --dir "file://ent/migrate/migrations" \
  3.   --to "ent://ent/schema" \
  4.   --dev-url "docker://mysql/8/ent"
  1. atlas migrate diff user_title_required \
  2.   --dir "file://ent/migrate/migrations" \
  3.   --to "ent://ent/schema" \
  4.   --dev-url "docker://mariadb/latest/test"
  1. atlas migrate diff user_title_required \
  2.   --dir "file://ent/migrate/migrations" \
  3.   --to "ent://ent/schema" \
  4.   --dev-url "docker://postgres/15/test?search_path=public"
  1. atlas migrate diff user_title_required \
  2.   --dir "file://ent/migrate/migrations" \
  3.   --to "ent://ent/schema" \
  4.   --dev-url "sqlite://file?mode=memory&_fk=1"

A new migration file was created in the ent/migrate/migrations directory:

ent/migrate/migrations/20221116051710_user_title_required.sql

  1. -- modify "users" table
  2. ALTER TABLE `users` MODIFY COLUMN `title` varchar(255) NOT NULL;

Now, let’s lint the migration directory:

  1. atlas migrate lint --dev-url mysql://root:pass@localhost:3306/dev --dir file://ent/migrate/migrations --latest 1

Atlas reports that the migration may be unsafe to run:

  1. 20221116051710_user_title_required.sql: data dependent changes detected:
  3.         L2: Modifying nullable column "title" to non-nullable might fail in case it contains NULL values

Atlas detected that the migration is unsafe to run and prevented us from running it. In this case, Atlas classified this change as a data dependent change. This means that the change might fail, depending on the concrete data in the database.

Atlas can detect many more types of issues, for a full list, see the Atlas documentation.

Linting our migration directory in CI

In the previous section, we saw how to lint our migration directory locally. In this section, we will see how to lint our migration directory in CI. This way, we can make sure that our migration history is safe to run before we merge it to the main branch.

GitHub Actions is a popular CI/CD product from GitHub. With GitHub Actions, users can easily define workflows that are triggered in various lifecycle events related to a Git repository. For example, many teams configure GitHub actions to run all unit tests in a repository on each change that is committed to a repository.

One of the powerful features of GitHub Actions is its extensibility: it is very easy to package a piece of functionality as a module (called an “action”) that can later be reused by many projects.

Teams using GitHub that wish to ensure all changes to their database schema are safe can use the atlas-action GitHub Action.

This action is used for linting migration directories using the atlas migrate lint command. This command validates and analyzes the contents of migration directories and generates insights and diagnostics on the selected changes:

  • Ensure the migration history can be replayed from any point in time.
  • Protect from unexpected history changes when concurrent migrations are written to the migration directory by multiple team members.
  • Detect whether destructive or irreversible changes have been made or whether they are dependent on tables’ contents and can cause a migration failure.

Usage

Add .github/workflows/atlas-ci.yaml to your repo with the following contents:

  1. name: Atlas CI
  2. on:
  3.   # Run whenever code is changed in the master branch,
  4.   # change this to your root branch.
  5.   push:
  6.     branches:
  7.       - master
  8.   pull_request:
  9.     paths:
  10.       - 'ent/migrate/migrations/*'
  11. jobs:
  12.   lint:
  13.     services:
  14.       # Spin up a mysql:8.0.29 container to be used as the dev-database for analysis.
  15.       mysql:
  16.         image: mysql:8.0.29
  17.         env:
  18.           MYSQL_ROOT_PASSWORD: pass
  19.           MYSQL_DATABASE: dev
  20.         ports:
  21.           - "3306:3306"
  22.         options: >-
  23.           --health-cmd "mysqladmin ping -ppass"
  24.           --health-interval 10s
  25.           --health-start-period 10s
  26.           --health-timeout 5s
  27.           --health-retries 10
  28.     runs-on: ubuntu-latest
  29.     steps:
  30.       - uses: actions/checkout@v3.0.1
  31.         with:
  32.           fetch-depth: 0 # Mandatory unless "latest" is set below.
  33.       - uses: ariga/atlas-action@v0
  34.         with:
  35.           dir: ent/migrate/migrations
  36.           dev-url: mysql://root:pass@localhost:3306/dev

Now, whenever we make a pull request with a potentially unsafe migration, the Atlas GitHub action will run and report the linting results. For example, for our data-dependent change: Verifying migration safety - 图2

For more in depth documentation, see the atlas-action docs on the Atlas website.

Let’s fix the issue by back-filling the title column. Add the following statement to the migration file:

ent/migrate/migrations/20221116051710_user_title_required.sql

  1. -- modify "users" table
  2. UPDATE `users` SET `title` = "" WHERE `title` IS NULL;
  4. ALTER TABLE `users` MODIFY COLUMN `title` varchar(255) NOT NULL;

Re-hash the migration directory:

  1. atlas migrate hash --dir file://ent/migrate/migrations

Re-running atlas migrate lint, we can see that the migration directory doesn’t contain any unsafe changes:

  1. atlas migrate lint --dev-url mysql://root:pass@localhost:3306/dev --dir file://ent/migrate/migrations --latest 1

Because no issues are found, the command will exit with a zero exit code and no output.

When we commit this change to GitHub, the Atlas GitHub action will run and report that the issue is resolved:

Verifying migration safety - 图3

Conclusion

In this section, we saw how to use Atlas to verify that our migration is safe to run both locally and in CI.

This wraps up our tutorial on how to upgrade your Ent project from automatic migration to versioned migrations. To recap, we learned how to:

  • Enable the versioned migrations feature-flag
  • Create a script to automatically plan migrations based on our desired Ent schema
  • Upgrade our production database to use versioned migrations with Atlas
  • Plan custom migrations for our project
  • Verify migrations safely using atlas migrate lint

In the next steps

Verifying migration safety - 图4For more Ent news and updates: