Setting up CI for your Database with Atlas Cloud
As your application evolves, so does your database schema. If you're not careful with schema changes (migrations), you can end up introducing all sorts of issues that are painful and expensive to fix.
To mitigate the risk of deploying dangerous changes to database schemas, many teams apply CI/CD practices to their database. This means that every change to the database schema is automatically reviewed and tested before it is deployed to production.
In this guide, we'll show you how to set up CI for your database using Atlas Cloud.
GitHub Actions or App Workflows
There are two ways to set up CI for your database with Atlas Cloud:
GitHub Action (recommended) - Install the
ariga/atlas-action
GitHub Action on your GitHub repository and configure it to run on any pull request that modifies your database schema.Note, this workflow does not require you to grant Atlas Cloud any permissions on your repository. It also gives you more control on how to set it up, and what Dev Database to use (e.g, PostGIS) for linting, change simulation, etc.
GitHub App - Install the Atlas Cloud GitHub App on your GitHub repository. Atlas Cloud will automatically pull changes from your GitHub repository, then run tests on them.
This workflow is faster to set up, but it does require the necessary permissions to install the GitHub App on your repository.
Prerequisites
A GitHub repository containing an Atlas migration directory. If you don't have one handy and would like to experiment with this workflow, you can use the
ariga/atlas-template
template repo to create one for you.An Atlas Cloud account. If you don't have one, you can sign up for free in auth.atlasgo.cloud, or by running
atlas login
in your terminal:$ atlas login
You are now connected to "a8m" on Atlas Cloud.
GitHub Action Workflow
Step 1: Create a Bot Token for Atlas Cloud
In order to report the results of your CI runs to Atlas Cloud, you will need to create a bot token for Atlas Cloud to use. Follow these instructions to create a token and save it somewhere safe.
Step 2: Install the Atlas GitHub CLI Extension
To streamline the process of configuring the GitHub Action, we've created a GitHub CLI extension that will do most of the work for you:
Make sure you have the GitHub CLI installed.
brew install gh
See here for more installation options.
Install the Atlas GitHub CLI extension:
gh extension install ariga/gh-atlas
Step 3: Configure the GitHub Action
Permissions - Make sure you have the necessary permissions to configure your action:
gh auth refresh -s write:packages,workflow
Go to repo root - Make sure your current working directory is the root of your repository.
cd path/to/my/repo
Run the extension - Run the
init-ci
command to configure the GitHub Action:gh atlas init-action --token <your-bot-token>
Atlas will scan your repository (locally) for directories containing Atlas migrations and ask you which one you would like to use for CI. Select the desired directory and press "Enter":
Use the arrow keys to navigate: ↓ ↑ → ←
? choose migration directory:
▸ migrationsAtlas will then ask you which database driver this directory contains migrations for. Select the desired driver and press "Enter":
Use the arrow keys to navigate: ↓ ↑ → ←
? choose driver:
▸ mysql
postgres
mariadb
sqliteNext, the GitHub extension will save your bot token to a GitHub secret and create a pull request with the necessary configuration for the GitHub Action.
Screenshot Example
The PR contains a GitHub Actions workflow similar to this:
Code Example
name: Atlas
on:
push:
branches:
- master
pull_request:
paths:
- 'migrations/*'
# Permissions to write comments on the pull request.
permissions:
contents: read
pull-requests: write
jobs:
lint:
services:
# Spin up a mysql:8 container to be used as the dev-database for analysis.
mysql:
image: mysql:8
env:
MYSQL_DATABASE: dev
MYSQL_ROOT_PASSWORD: pass
ports:
- 3306:3306
options: >-
--health-cmd "mysqladmin ping -ppass"
--health-interval 10s
--health-start-period 10s
--health-timeout 5s
--health-retries 10
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v3
with:
fetch-depth: 0
- uses: ariga/atlas-action@v0
with:
dir: 'migrations'
dev-url: "mysql://root:pass@localhost:3306/dev"
cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
sync:
needs: lint
if: github.ref == 'refs/heads/master'
runs-on: ubuntu-latest
services:
# Spin up a mysql:8 container to be used as the dev-database for analysis.
mysql:
image: mysql:8
env:
MYSQL_DATABASE: dev
MYSQL_ROOT_PASSWORD: pass
options: >-
--health-cmd "mysqladmin ping -ppass"
--health-interval 10s
--health-start-period 10s
--health-timeout 5s
--health-retries 10
steps:
- uses: actions/checkout@v3
- uses: ariga/atlas-sync-action@v0
with:
dir: 'migrations'
dev-url: 'mysql://root:pass@mysql:3306/dev' # Use the service name "mysql" as the hostname
cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
After reviewing the changes, merge the pull request to enable the GitHub Action.
Step 4: Test the Action
After merging the pull request, the GitHub Action will run atlas migrate lint
on every pull request
and sync the migrations to Atlas Cloud on every push to master
.
To test this setup, create a new branch for making some changes to your database schema:
git checkout -b test-ci
Create a new migration in interactive mode:
atlas migrate new --edit dummy
Type some broken SQL into the migration file and save it:
CREATE users (
col varchar(255)
);(Notice that we're missing the
TABLE
keyword in theCREATE TABLE
statement.)Commit the changes and push them to GitHub:
git add migrations
git commit -m "migrations: add dummy migration"
git push origin test-ciOur changes are pushed to GitHub:
remote: Resolving deltas: 100% (3/3), done.
remote:
remote: Create a pull request for 'test-ci' on GitHub by visiting:
remote: https://github.com/rotemtam/gh-init-demo/pull/new/test-ci
remote:
To github.com:rotemtam/gh-init-demo.git
* [new branch] test-ci -> test-ciOpen a pull request for the
test-ci
branch and wait for the GitHub Action to run.Screenshot Example
Notice that after a few moments, a comment appears on the pull request with the results of the linting run:
Screenshot Example
The linting run failed because of the syntax error we introduced in the migration file.
Click on "Full Report on Atlas Cloud" to see the full report on Atlas Cloud:
Screenshot Example
GitHub App Workflow
Step 1: Install the Atlas Cloud GitHub App
After logging in to Atlas Cloud, create a new migration directory by clicking on "Projects" in the sidebar and then click on the "Add Directory" button.
Screenshot Example
Click "Connect with GitHub" to initiate the GitHub App installation flow.
Screenshot Example
Step 2: Connect Your Repository
Once you've installed the GitHub App, you need to grant it access to the relevant repositories. Click on the "Organization" field and choose "Add Organization" from the dropdown:
Screenshot Example
A popup requesting you to grant permission to specific repositories will appear. Select the repositories you want to grant access to, then click "Install and Authorize".
Screenshot Example
Step 3: Configure the Migration Directory
Fill the rest of the form with the relevant configuration details:
- Directory Path - The path to the directory containing your migrations relative to your repository root.
- Directory Name - An identifier for the directory. This will be used to identify the directory in the Atlas Cloud and APIs.
- Migration Engine - The format of the migrations in the directory. Atlas works best with migration directories in its
format, but also supports
golang-migrate
, Flyway, and Liquibase. - Database - the database engine for the target database.
Screenshot Example
Click "Connect Directory" to finish the process.
Step 4: Test the Integration
After connecting your directory, let's create a pull request with a destructive change to make sure that Atlas Cloud catches it.
Create a new branch:
git checkout -b test-branch
Create a new migration file:
atlas migrate new --edit destructive
Add the following SQL to the migration file and save:
DROP TABLE posts;
Commit the changes and push them to GitHub:
git add .
git commit -m "add destructive migration"
git push origin test-branch
Create a pull request from the test-branch
branch to your main branch.
After a few seconds, Atlas Cloud will pick up the changes and run CI against them. Shortly after, you will see a comment on your pull request with the results of the CI run:
Screenshot Example
Amazing! Atlas Cloud caught the destructive change and prevented it from being accidentally merged to your main branch.
To see a more detailed report of the CI run, click on the "Full Report on Atlas Cloud" link in the comment:
Screenshot Example
Summary
In this guide, we've shown how to configure Atlas Cloud to apply continuous integration for our database schema changes. With this setup, whenever a developer proposes a change to the database schema, Atlas Cloud will verify the safety of the change using various checks and report back the results.