Skip to main content

Pre-approved Migrations

Introduction​

One of the main benefits of moving schema changes into a declarative workflow is the ability to offload the responsibility of planning schema changes from developers to a specialized tool. However, because the database is such a critical part of applications, many organizations still want to have a human in the loop to approve schema changes before they are applied.

This document describes Pre-approvals: with this flow, schema changes are planned during the development or CI process and stored in the schema registry.

Overview​

Pre-approvals typically work this way:

  • Developers create a pull request with the schema changes.
  • The CI/CD pipeline detects this change and suggests a migration plan.
  • The PR is reviewed along with the migration plan.
  • Once the PR is merged, the migration plan is stored in the schema registry as an "approved" plan.

Deployment​

Then, when the new schema is deployed to the cluster, the Operator will search the schema registry for the approved plan transitioning from the current schema to the new schema. If it finds one, it will apply the plan without further human intervention.

If no approved plan is found, the Operator will proceed based on the defined review policy. This concept is explained below.

How Pre-approvals work​

Pre-approvals work by adding two automated steps to the schema change process:

  1. Plan Generation - When a schema change is detected, the CI pipeline runs a plan generation step to create a migration plan. This requires that the CI runner has Atlas installed and can access the database. This step executes the atlas schema plan command which diffs the target database and the desired schema and presents the changes as a plan.

    This is typically accomplished using the ariga/atlas-action/schema/plan GitHub Action but can be done using any method that can run the atlas schema plan command.

  2. Plan Approval - The PR containing the schema changes is reviewed by a human. The migration plan is included in the PR description or as a comment. Once the plan is reviewed and approved, the PR is merged.

    This step is manual and requires a human to review the changes and the migration plan.

    Once the PR is merged, a second automated step stores the approved plan in the schema registry. This is typically done ariga/atlas-action/schema/plan/approve GitHub Action.

Guide: Pre-approval flows​

In this guide, we demonstrate how to set up pre-approval flows for declarative migrations using GitHub Actions and the Kubernetes Operator.

Step 1: Set up the Atlas Project​

Let's start our journey by setting up an Atlas project. In this guide we will be managing a simple PostgreSQL database and defining our desired schema using plain SQL files.

Create a new directory in your GitHub repository named preapproval-k8s:

mkdir preapproval-k8s

In this directory, create a new file named schema.sql with the following content:

preapproval-k8s/schema.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

Next, let's create the project configuration file atlas.hcl:

preapproval-k8s/atlas.hcl
variable "db_url" {
  type = string
  default = getenv("DB_URL")
}

env {
  name = atlas.env
  url = var.db_url
  schema {
    src = "file://schema.sql"
    repo {
      name = "preapproval-k8s"
    }
  }
  dev = "docker://postgres/16/dev?search_path=public"
}

Verify our project is set up correctly by running the following commands:

export DB_URL="..." # Your database URL

Diff the current schema with the desired schema:

atlas schema diff --env local --from env://url --to env://schema.src --format "{{ sql . }}"

If everything is set up correctly, you should see the diff between the current schema (presumably empty) and the desired schema:

-- Create "users" table
CREATE TABLE "users" ("id" serial NOT NULL, "name" character varying(100) NOT NULL, PRIMARY KEY ("id"));

Finally, commit these changes to the mainline branch of your repository.

Step 2: Push to Schema Registry​

Now that we have our schema defined, let's push it to the schema registry. This will allow the Operator to use it when applying the schema to the database.

Before proceeding, make sure you have an Atlas Cloud account and are logged in:

atlas login

If you don't have an account, or need to log in please follow the instructions on screen.

Now, push the schema to the registry:

atlas schema push --env local

If the push is successful, you should see output similar to the following:

Schema: preapproval-k8s
  -- Atlas URL: atlas://preapproval-k8s
  -- Cloud URL: https://rotemtam85.atlasgo.cloud/schemas/141733920820

You can visit the Cloud URL to see the schema in the registry.

Step 3: Set up GitHub Actions​

Now that we have our schema in the registry, let's set up a GitHub Action to manage the pre-approval process.

As a prerequisite, please make sure:

  1. You have a Bot Token with access to the registry repo we just pushed to. To learn how to create a Bot Token, please refer to the Atlas docs.

  2. Once you have obtained the Bot Token, add it to your GitHub repository secrets as ATLAS_TOKEN. To learn how, refer to the GitHub docs.

  3. You are able to connect to the database from the CI runner.

  4. You store the database URL in the GitHub repository secrets as DB_URL.

Next, create a new file in your repository named .github/workflows/k8s-pre-approval.yml with the following content:

.github/workflows/k8s-pre-approval.yml
name: Plan Declarative Migrations
on:
  workflow_dispatch:
  push:
    branches:
      - master
    paths:
      - .github/workflows/k8s-pre-approve.yaml
      - 'preapproval-k8s/*'
  pull_request:
    branches:
      - master
    paths:
      - .github/workflows/k8s-pre-approve.yaml
      - 'preapproval-k8s/*'
permissions:
  contents: read
  pull-requests: write
jobs:
  plan:
    name: plan
    if: ${{ github.event_name == 'pull_request' }}
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Setup Atlas
        uses: ariga/setup-atlas@v0
        with:
          cloud-token: ${{ secrets.ATLAS_TOKEN }}
      - name: Run schema plan
        uses: ariga/atlas-action/schema/plan@master
        env:
          GITHUB_TOKEN: ${{ github.token }}
        with:
          working-directory: preapproval-k8s
          from: ${{ secrets.DB_URL }}
          env: ci
  approve-push:
    name: approve-push
    if: ${{ github.event_name == 'push' && github.ref == 'refs/heads/master' }}
    runs-on: ubuntu-latest
    env:
      GITHUB_TOKEN: ${{ github.token }}
    steps:
      - uses: actions/checkout@v4
      - name: Setup Atlas
        uses: ariga/setup-atlas@v0
        with:
          cloud-token: ${{ secrets.ATLAS_TOKEN }}
      - name: Approve the plan
        id: plan-approve
        uses: ariga/atlas-action/schema/plan/approve@master
        with:
          env: ci
          working-directory: preapproval-k8s
          from: ${{ secrets.DB_URL }}
      - name: Push the schema
        id: schema-push
        uses: ariga/atlas-action/schema/push@master
        with:
          env: ci
          working-directory: preapproval-k8s

Let's break down the workflow's two main jobs:

On Pull Request​

  1. Setup Atlas - This step sets up the Atlas CLI with the provided token.
  2. Run schema plan - This step runs the atlas schema plan command to generate a migration plan. The plan is then included in the PR description. These are the provided inputs:
    • working-directory - The directory where the schema files are located.
    • from - The database URL to compare against.
    • env - The environment to use for the plan. We use ci again to mark this as a CI environment.

On Push to Master​

  1. Setup Atlas - This step sets up the Atlas CLI with the provided token.
  2. Approve the plan - This step approves the plan generated in the previous step. The plan is then stored in the schema registry. These are the provided inputs:
    • working-directory - The directory where the schema files are located.
    • from - The database URL to compare against.
    • env - The environment to use for the plan. We use ci to differentiate between the CI environment and the local environment.
  3. Push the schema - This step pushes the schema to the schema registry. These are the provided inputs:
    • working-directory - The directory where the schema files are located.
    • env - The environment to use for the plan. Again, ci denotes the CI environment.

Next, create a branch and push the changes to your repository:

git checkout -b add-pre-approval-workflow
git add .github/workflows/k8s-pre-approval.yml
git commit -m "Add pre-approval workflow"
git push origin add-pre-approval-workflow

Create a pull request from the GitHub UI and wait for the workflow to run. Once the workflow completes, you should see the migration plan in the PR description:

Once the PR is approved and merged, the approve-push job will run and store the approved plan in the schema registry.

To verify the plan was stored correctly, run the following command:

atlas schema plan list --env local

If the plan was stored correctly, you should see output similar to the following:

Plan Status: APPROVED
  -- Atlas URL: atlas://preapproval-k8s/plans/pr-41-PKrr2qio
  -- Cloud URL: https://rotemtam85.atlasgo.cloud/schemas/141733920820/plans/210453397571

Step 4: Deploy the Schema​

Local Cluster Setup​

To get started, you will need a Kubernetes cluster running on your local machine. For the purpose of this guide, we will use minikube.

To install minikube on macOS, you can use brew:

brew install minikube

For other operating systems, follow the instructions on the official website.

Provision secrets​

Next, provision a secret with the database URL:

kubectl create secret generic db-url --from-literal=db-url=$DB_URL

Additionally, create a secret with the Atlas token:

kubectl create secret generic atlas-token --from-literal=token=<your token here>

Install the Atlas Operator​

Now we can install the Atlas Operator using Helm:

helm install atlas-operator oci://ghcr.io/ariga/charts/atlas-operator

Apply the schema​

To apply the schema to the database, create a file named atlas-schema.yaml with the following content:

atlas-schema.yaml
apiVersion: db.atlasgo.io/v1alpha1
kind: AtlasSchema
metadata:
  name: atlasschema-pg
spec:
  urlFrom:
    secretKeyRef:
      key: db-url
      name: db-url
  policy:
    lint:
      review: ALWAYS
  cloud:
    tokenFrom:
      secretKeyRef:
        name: atlas-token
        key: token
    schema:
      url: atlas://preapproval-k8s?tag=latest

Apply the schema to the cluster:

kubectl apply -f atlas-schema.yaml

Then wait for the schema to be applied:

 kubectl wait --for=condition=Ready atlasschema/atlasschema-pg --timeout=60s

Step 5: Verify the flow​

To verify the flow, let's show how our pipeline works end-to-end.

Create a new branch​

Let's create a new branch and make a change to the schema:

git checkout -b add-column

Add a new column to the users table:

preapproval-k8s/schema.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
+   email VARCHAR(100) NOT NULL
);

Commit and push the changes:

git add .
git commit -m "Add email column"
git push origin add-column

Next, go to the GitHub UI and create a pull request from the add-column branch to your mainline branch (e.g master) and wait for the workflow to run.

A new migration plan should be generated and displayed as a comment in the PR:

Next, approve the PR (if needed) and merge it. Once the PR is merged, the approve-push job will run and store the approved plan in the schema registry.

Finally, update the atlas-schema.yaml file to use the new schema:

atlas-schema.yaml
apiVersion: db.atlasgo.io/v1alpha1
kind: AtlasSchema
metadata:
  name: atlasschema-pg2
spec:
  urlFrom:
    secretKeyRef:
      key: db-url
      name: db-url
  cloud:
    tokenFrom:
      secretKeyRef:
        name: atlas-token
        key: ATLAS_TOKEN
  policy:
    lint:
      review: ALWAYS
  schema:
    url: atlas://preapproval-k8s?tag=9d30700715e2ea91898ef426fe6b7ce2088b69dd

Notice we use the tag of the new schema version. By default, the approve-push job will tag the schema with the commit SHA of the PR that was merged. You can find the tag by visiting the "Tags" tab in the schema registry:

Apply the new schema to the cluster:

kubectl apply -f atlas-schema.yaml

Then wait for the schema to be applied:

kubectl wait --for=condition=Ready atlasschema/atlasschema-pg2 --timeout=60s

Notice that the schema is applied without any runtime approval, as the plan was pre-approved.

Conclusion​

In this guide, we demonstrated how to set up a pre-approval flow for declarative migrations using GitHub Actions and the Atlas Operator. This flow allows you to plan schema changes during the development process and store them in the schema registry for later use.