Automate Database CI/CD with GitHub Actions
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 re-used by many projects.
Atlas provides a number of GitHub Actions that can be used to automate database schema management tasks.
| Action | Use Case |
|---|---|
| ariga/setup-atlas | Install Atlas from a GitHub Actions workflow |
| ariga/atlas-action/migrate/push | Push your migration directory to Atlas Cloud (atlasgo.cloud) |
| ariga/atlas-action/migrate/lint | CI for schema changes |
| ariga/atlas-action/migrate/apply | Deploy versioned migrations from GitHub Actions |
| ariga/atlas-action/migrate/drift | Check a database for schema drift against its migration history |
| ariga/atlas-action/migrate/down | Revert migrations to a database |
| ariga/atlas-action/migrate/test | Test migrations on a database |
| ariga/atlas-action/migrate/autorebase | Fix atlas.sum conflicts in migration directory |
| ariga/atlas-action/migrate/diff | Run Migrate diff and commit the changes to the migration directory |
| ariga/atlas-action/schema/test | Test schema on a database |
| ariga/atlas-action/schema/lint | Lint schema |
| ariga/atlas-action/schema/apply | Apply a declarative migrations to a database |
| ariga/atlas-action/schema/push | Push a schema to Atlas Registry |
| ariga/atlas-action/schema/plan | Plan a declarative migration for a schema transition |
| ariga/atlas-action/schema/plan/approve | Approve a declarative migration plan |
| ariga/atlas-action/monitor/schema | Sync the database schema to Atlas Cloud Monitoring |
| ariga/atlas-action/script/test | Test Data Scripts before they run against a real database |
| ariga/atlas-action/script/push | Push Data Scripts to Atlas Registry |
| ariga/atlas-action/script/exec | Run script "exec" transactional mutations on a database |
| ariga/atlas-action/script/query | Run script "query" reads and reports on a database |
| ariga/atlas-action/script/loop | Run script "loop" batched operations on a database |
Offline Access
ariga/setup-atlas includes built-in grant caching for ~/.atlas and restores it across workflow runs. This ensures Atlas Cloud is not a single point of failure in your GitHub workflow, keeping your pipelines operational even without connectivity to Atlas Cloud.
No additional cache configuration is required beyond using ariga/setup-atlas with cloud-token.
GitHub Environments
Jobs that deploy to a database read its URL from a secret. Keep each URL in a
GitHub environment
for that database, not in the repository's secrets, and name the environment in the deploy job with
environment: <name>. A job gets an environment's secrets only when it names the environment and runs
on a branch or tag the environment allows, so a pull request, even one from a compromised account or a
bot, cannot read the URLs or deploy to production.
- Create one environment per database environment, such as
stagingandproduction, and store the URL under the same secret name in each, such asDATABASE_URL. - Non-production environments, such as
staging, deploy on every merge. Limit their deployment branches to the default branch. productionadds required reviewers, so every deployment waits for an approval, and limits its deployment branches and tags to the default branch or your release tags.
On a private repository, environments, environment secrets, and deployment branches need GitHub Pro, GitHub Team, or GitHub Enterprise, and required reviewers need GitHub Enterprise.
ariga/setup-atlas
The ariga/setup-atlas action can be used to install Atlas from a GitHub Actions workflow.
Usage
name: Deploy schema changes
run-name: I'm using Atlas 🚀
on: [push]
jobs:
use-atlas:
runs-on: ubuntu-latest
steps:
- uses: ariga/setup-atlas@v0
with:
cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
- run: atlas version
# - run: atlas schema apply ...
Inputs
cloud-token- (Optional) The Atlas Cloud token to use for authentication. To create a cloud token see the docs.version- (Optional) The version of the Atlas CLI to install. Defaults to the latest version.
ariga/atlas-action/migrate/push
Push the current version of your migration directory to Atlas Cloud.
Usage
- MySQL
- MariaDB
- PostgreSQL
- SQLite
- SQL Server
- Clickhouse
name: Push Migrations
on:
# Run whenever code is changed in the master branch,
# change this to your main branch.
push:
branches:
- master
jobs:
push:
services:
# Spin up a mysql:8 container to be used as the dev-database.
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
- uses: ariga/setup-atlas@v0
with:
cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
- uses: ariga/atlas-action/migrate/push@v1
with:
dir: 'file://migrations'
dir-name: 'my-project'
dev-url: 'mysql://root:pass@localhost:3306/dev'
name: Push Migrations
on:
# Run whenever code is changed in the master branch,
# change this to your main branch.
push:
branches:
- master
jobs:
push:
services:
# Spin up a mariadb:11 container to be used as the dev-database.
maria11:
image: mariadb:11
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
- uses: ariga/setup-atlas@v0
with:
cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
- uses: ariga/atlas-action/migrate/push@v1
with:
dir: 'file://migrations'
dir-name: 'my-project'
dev-url: 'maria://root:pass@localhost:3306/dev'
name: Push Migrations
on:
# Run whenever code is changed in the master branch,
# change this to your root branch.
push:
branches:
- master
jobs:
push:
services:
# Spin up a postgres:15 container to be used as the dev-database.
postgres15:
image: postgres:15
env:
POSTGRES_DB: test
POSTGRES_PASSWORD: pass
ports:
- 5432:5432
options: >-
--health-cmd pg_isready
--health-interval 10s
--health-timeout 5s
--health-retries 5
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v3
- uses: ariga/setup-atlas@v0
with:
cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
- uses: ariga/atlas-action/migrate/push@v1
with:
dir: 'file://migrations'
dir-name: 'my-project'
dev-url: postgres://postgres:pass@localhost:5432/test?sslmode=disable
name: Push Migrations
on:
# Run whenever code is changed in the master branch,
# change this to your root branch.
push:
branches:
- master
jobs:
push:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v3
- uses: ariga/setup-atlas@v0
with:
cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
- uses: ariga/atlas-action/migrate/push@v1
with:
dir: 'file://migrations'
dir-name: 'my-project'
dev-url: sqlite://file?mode=memory&_fk=1
name: Push Migrations
on:
# Run whenever code is changed in the master branch,
# change this to your root branch.
push:
branches:
- master
jobs:
push:
services:
# Spin up a SQL Server container to be used as the dev-database.
sqlserver:
image: mcr.microsoft.com/mssql/server:2022-latest
env:
ACCEPT_EULA: Y
MSSQL_PID: Developer
MSSQL_SA_PASSWORD: P@ssw0rd0995
ports:
- 1433:1433
options: >-
--health-cmd "/opt/mssql-tools/bin/sqlcmd -U sa -P P@ssw0rd0995 -Q \"SELECT 1\""
--health-interval 10s
--health-timeout 5s
--health-retries 5
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v3
- uses: ariga/setup-atlas@v0
with:
cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
- uses: ariga/atlas-action/migrate/push@v1
with:
dir: 'file://migrations'
dir-name: 'my-project'
dev-url: sqlserver://sa:P@ssw0rd0995@localhost:1433?database=master
name: Push Migrations
on:
# Run whenever code is changed in the master branch,
# change this to your root branch.
push:
branches:
- master
jobs:
push:
services:
# Spin up a Clickhouse container to be used as the dev-database.
clickhouse:
image: clickhouse/clickhouse-server:23.10
env:
CLICKHOUSE_DB: test
CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT: 1
CLICKHOUSE_PASSWORD: pass
CLICKHOUSE_USER: root
ports:
- 9000:9000
options: >-
--health-cmd "clickhouse-client --host localhost --query 'SELECT 1'"
--health-interval 10s
--health-timeout 5s
--health-retries 5
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v3
- uses: ariga/setup-atlas@v0
with:
cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
- uses: ariga/atlas-action/migrate/push@v1
with:
dir: 'file://migrations'
dir-name: 'my-project'
dev-url: 'clickhouse://root:pass@localhost:9000/test'
Inputs
All inputs are optional as they may be specified in the Atlas configuration file.
dir- The URL of the migration directory to push. For example:file://migrations. Read more about Atlas URLs.dir-name- The name (slug) of the project in Atlas Cloud.dev-url- The URL of the dev-database to use for analysis. For example:mysql://root:pass@localhost:3306/dev. Read more about dev-databases.tag- The tag to apply to the pushed migration directory. By default the current git commit hash is used.latest- Whether to implicitly push the "latest" tag. True by default.config- The path to the Atlas configuration file. By default, Atlas will look for a file namedatlas.hclin the current directory. For example,file://config/atlas.hcl. Learn more about Atlas configuration files.env- The environment to use from the Atlas configuration file. For example,dev.vars- Stringify JSON object containing variables to be used inside the Atlas configuration file. For example:'{"var1": "value1", "var2": "value2"}'.working-directory- The working directory to run from. Defaults to project root.
Outputs
url- The URL of the migration directory in Atlas Cloud, containing an ERD visualization of the schema.
ariga/atlas-action/migrate/lint
Teams using GitHub that wish to ensure all changes to their database schema are safe
can use the atlas-action/migrate/lint 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 at time.
- Protect from unexpected history changes when concurrent migrations are written to the migration directory by multiple team members. Read more about the consistency checks in the section below.
- 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:
- MySQL
- MariaDB
- PostgreSQL
- SQLite
- SQL Server
- Clickhouse
name: Atlas CI
on:
# Run whenever code is changed in the master branch,
# change this to your root branch.
push:
branches:
- master
# Run on PRs where something changed under the `migrations/` directory.
pull_request:
paths:
- 'migrations/*'
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
- uses: ariga/setup-atlas@v0
with:
cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
- uses: ariga/atlas-action/migrate/lint@v1
with:
dir: 'file://migrations'
dir-name: 'my-project' # The name of the project in Atlas Cloud
dev-url: 'mysql://root:pass@localhost:3306/dev'
name: Atlas CI
on:
# Run whenever code is changed in the master branch,
# change this to your root branch.
push:
branches:
- master
# Run on PRs where something changed under the `migrations/` directory.
pull_request:
paths:
- 'migrations/*'
jobs:
lint:
services:
# Spin up a maria:11 container to be used as the dev-database for analysis.
mariadb:
image: mariadb:11
env:
MYSQL_DATABASE: dev
MYSQL_ROOT_PASSWORD: pass
ports:
- 3306:3306
options: >-
--health-cmd "healthcheck.sh --su-mysql --connect --innodb_initialized"
--health-interval 10s
--health-start-period 10s
--health-timeout 5s
--health-retries 10
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v3
- uses: ariga/setup-atlas@v0
with:
cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
- uses: ariga/atlas-action/migrate/lint@v1
with:
dir: 'file://migrations'
dir-name: 'my-project' # The name of the project in Atlas Cloud
dev-url: 'maria://root:pass@localhost:3306/dev'
name: Atlas CI
on:
# Run whenever code is changed in the master branch,
# change this to your root branch.
push:
branches:
- master
# Run on PRs where something changed under the `path/to/migration/dir/` directory.
pull_request:
paths:
- 'migrations/*'
jobs:
lint:
services:
# Spin up a postgres:15 container to be used as the dev-database for analysis.
postgres:
image: postgres:15
env:
POSTGRES_DB: dev
POSTGRES_PASSWORD: pass
ports:
- 5432:5432
options: >-
--health-cmd pg_isready
--health-interval 10s
--health-start-period 10s
--health-timeout 5s
--health-retries 5
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v3
- uses: ariga/setup-atlas@v0
with:
cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
- uses: ariga/atlas-action/migrate/lint@v1
with:
dir: 'file://migrations'
dir-name: 'my-project' # The name of the project in Atlas Cloud
dev-url: postgres://postgres:pass@localhost:5432/dev?sslmode=disable
name: Atlas CI
on:
# Run whenever code is changed in the master branch,
# change this to your root branch.
push:
branches:
- master
# Run on PRs where something changed under the `path/to/migration/dir/` directory.
pull_request:
paths:
- 'migrations/*'
jobs:
lint:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v3
- uses: ariga/setup-atlas@v0
with:
cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
- uses: ariga/atlas-action/migrate/lint@v1
with:
dir: 'file://migrations'
dir-name: 'my-project' # The name of the project in Atlas Cloud
dev-url: sqlite://file?mode=memory&_fk=1
name: Atlas CI
on:
# Run whenever code is changed in the master branch,
# change this to your root branch.
push:
branches:
- master
# Run on PRs where something changed under the `path/to/migration/dir/` directory.
pull_request:
paths:
- 'migrations/*'
jobs:
lint:
services:
# Spin up a SQL Server container to be used as the dev-database for analysis.
sqlserver:
image: mcr.microsoft.com/mssql/server:2022-latest
env:
ACCEPT_EULA: Y
MSSQL_PID: Developer
MSSQL_SA_PASSWORD: P@ssw0rd0995
ports:
- 1433:1433
options: >-
--health-cmd "/opt/mssql-tools/bin/sqlcmd -U sa -P P@ssw0rd0995 -Q \"SELECT 1\""
--health-interval 10s
--health-timeout 5s
--health-retries 5
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v3
- uses: ariga/setup-atlas@v0
with:
cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
- uses: ariga/atlas-action/migrate/lint@v1
with:
dir: 'file://migrations'
dir-name: 'my-project' # The name of the project in Atlas Cloud
dev-url: sqlserver://sa:P@ssw0rd0995@localhost:1433?database=master
name: Atlas CI
on:
# Run whenever code is changed in the master branch,
# change this to your root branch.
push:
branches:
- master
# Run on PRs where something changed under the `path/to/migration/dir/` directory.
pull_request:
paths:
- 'migrations/*'
jobs:
lint:
services:
# Spin up a Clickhouse container to be used as the dev-database for analysis.
clickhouse:
image: clickhouse/clickhouse-server:23.10
env:
CLICKHOUSE_DB: test
CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT: 1
CLICKHOUSE_PASSWORD: pass
CLICKHOUSE_USER: root
ports:
- 9000:9000
options: >-
--health-cmd "clickhouse-client --host localhost --query 'SELECT 1'"
--health-interval 10s
--health-timeout 5s
--health-retries 5
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v3
- uses: ariga/setup-atlas@v0
with:
cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
- uses: ariga/atlas-action/migrate/lint@v1
with:
dir: 'file://migrations'
dir-name: 'my-project' # The name of the project in Atlas Cloud
dev-url: 'clickhouse://root:pass@localhost:9000/test'
Inputs
All inputs are optional as they may be specified in the Atlas configuration file.
dir- The URL of the migration directory to lint. For example:file://migrations. Read more about Atlas URLs.dir-name- The name (slug) of the project in Atlas Cloud.tag- The tag of migrations to used as base for linting. By default, thelatesttag is used.dev-url- The URL of the dev-database to use for analysis. For example:mysql://root:pass@localhost:3306/dev. Read more about dev-databases.config- The path to the Atlas configuration file. By default, Atlas will look for a file namedatlas.hclin the current directory. For example,file://config/atlas.hcl. Learn more about Atlas configuration files.env- The environment to use from the Atlas configuration file. For example,dev.vars- Stringify JSON object containing variables to be used inside the Atlas configuration file. For example:'{"var1": "value1", "var2": "value2"}'.working-directory- The working directory to run from. Defaults to project root.
Outputs
url- The URL of the CI report in Atlas Cloud, containing an ERD visualization and analysis of the schema migrations.
ariga/atlas-action/migrate/apply
You can use ariga/atlas-action/migrate-apply to deploy migrations to your database directly from GitHub Actions.
Atlas needs network access to your database to deploy migrations, so make sure your database is either publicly accessible or that you have otherwise enabled network access to it from your GitHub Actions runners.
This action supports two workflows:
- Local - the migration directory is checked in to the repository.
- Cloud - the migration directory is connected to Atlas Cloud. Runs are reported to your Atlas Cloud account.
Usage
Notice that the following examples rely on a DATABASE_URL secret of the production
environment that the deploy job names.
To learn how to set secrets, read GitHub's documentation.
The DATABASE_URL secret should be set to the URL of your database, for examples please see Atlas URL formats.
Local
name: Deploy Database Migrations
on:
push:
branches:
- master
jobs:
deploy:
runs-on: ubuntu-latest
environment: production
steps:
- uses: actions/checkout@v3
- uses: ariga/setup-atlas@v0
- name: Deploy Atlas Migrations
uses: ariga/atlas-action/migrate/apply@v1
with:
url: ${{ secrets.DATABASE_URL }}
dir: path/to/migrations
Deploy from Cloud
name: Deploy Database Migrations
on:
push:
branches:
- master
jobs:
deploy:
runs-on: ubuntu-latest
environment: production
steps:
- uses: actions/checkout@v3
- uses: ariga/setup-atlas@v0
with:
cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
- name: Deploy Atlas Migrations
uses: ariga/atlas-action/migrate/apply@v1
with:
url: ${{ secrets.DATABASE_URL }}
dir: atlas://my-project # name (slug) of your project in Atlas Cloud. Add `?tag=<tag>` to deploy a specific tag.
Deploy with a drift check
Add a pre-apply drift check to an env in atlas.hcl to
stop the deploy when the database has drifted from the expected state at its last applied version:
env "prod" {
check "migrate_apply" {
drift {
on_error = FAIL
}
}
}
Pass the env to the action. The drift check is an Atlas Pro feature and reads the
expected state from Atlas Cloud, so ariga/setup-atlas needs a cloud-token:
name: Deploy Database Migrations
on:
push:
branches:
- master
jobs:
deploy:
runs-on: ubuntu-latest
environment: production
steps:
- uses: actions/checkout@v3
- uses: ariga/setup-atlas@v0
with:
cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
- name: Deploy Atlas Migrations
uses: ariga/atlas-action/migrate/apply@v1
with:
url: ${{ secrets.DATABASE_URL }}
dir: atlas://my-project
env: prod
If the database has drifted, the step fails and no migration file runs. The action does not print
the diff, and with on_error = CONTINUE the step passes without showing the drift.
Inputs
All inputs are optional as they may be specified in the Atlas configuration file.
url- The URL of the target database. For example:mysql://root:pass@localhost:3306/prod.dir- The URL of the migration directory to apply. For example:atlas://dir-namefor cloud based directories orfile://migrationsfor local ones.config- The URL of the Atlas configuration file. By default, Atlas will look for a file namedatlas.hclin the current directory. For example,file://config/atlas.hcl. Learn more about Atlas configuration files.env- The environment to use from the Atlas configuration file. For example,dev.dry-run- Print SQL without executing it. Defaults tofalsevars- Stringify JSON object containing variables to be used inside the Atlas configuration file. For example:'{"var1": "value1", "var2": "value2"}'.working-directory- The working directory to run from. Defaults to project root.allow-dirty- Allow applying migration on a non-clean database. Defaults to false.tx-mode- The transaction mode for migrations. It can be eitherfile,all, ornone. The default isfile.