Automating MySQL schema migrations with GitHub Actions and more

Schema Change as Code

GitHub’s development pace has accelerated in the past year, and with MySQL backing most services, every new feature often requires a change to the underlying schema. On average, two migrations run on production daily, sometimes six. The database infrastructure team owns this process end to end, and until recently, most of it was manual.

The workflow begins with a developer who needs a new table or column. They experiment locally, then submit a pull request. Peers review the design, and if the change is significant, a volunteer group of schema reviewers chimes in. The database infrastructure team then reviews for performance and operational impact. Once approved, an engineer must determine which cluster the change affects, translate the desired schema into exact SQL statements, choose a direct or online migration method (via a tool like gh-ost), and pick a safe time to run it. For large tables this can take hours or days, with tracking and status updates along the way. Even completion is work: cleanup, unblocking any queued migrations, and notifying the developer.

The potential for friction is high. Reviews could lag, the engineer might be busy, multiple migrations overlap, developers ping for status, and work stays blocked until everything finishes. This manually consumed several hours per day of infrastructure engineer time even in the best weeks.

Evolution of Tooling

GitHub’s core application remains a Ruby on Rails (RoR) app. RoR’s ActiveRecord exposes a declarative schema model that can generate the full CREATE TABLE statements directly from the repository. This declarative approach means for any commit or branch, we know exactly which schema it represents. The team built chatops commands that list pull requests with schema changes and generate the required CREATE/ALTER/DROP statements via rake, then wrapped those with cluster metadata to produce a runnable script.

Later, gh-ost added online migration capability with chat-based control: checking progress, adjusting runtime settings, and performing cut-over. Still, every step was manual. As GitHub expanded into more repositories — some RoR, others Go — the Application, which avoided object relational mapping, couldn’t lean on framework tooling. Tracking lived across chat, issues, and pull requests. The infrastructure team juggled migrations in memory, context-switching daily.

When GitHub Actions arrived, the team found a vehicle for a orchestrated solution: several loosely coupled components each own one piece of the flow, coordinated by a controller service. Our design centered on one core principle.

Treat Schema Like Application Code

Schema changes are code, versioned with the application in the same git repository. This coupling is essential because GitHub ships not only the continuous deployment on github.com but also GitHub Enterprise, the on-premise product with periodic releases. A schema change made on github.com must be reproducible on a customer’s Enterprise server, so the association between schema and code version is critical.

Developers follow the everyday GitHub flow: branch, commit, push, and open a pull request. Our entire pipeline attaches to that pull request, where review, CI, and discussion already happen. This standardizes schema work across all repositories and blends it into the normal development cycle.

Reviewing schema changes in a pull request

When a pull request touches code, reviewing the diff is straightforward. But when it changes a MySQL schema, a plain git diff of the schema files is rarely useful. Consider a simplified table definition:

CREATE TABLE some_table (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  hostname varchar(128) NOT NULL,
  PRIMARY KEY (id),
  KEY (hostname)
);

If we add a column and drop the index on hostname, the new schema is:

CREATE TABLE some_table (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  hostname varchar(128) NOT NULL,
  time_created TIMESTAMP NOT NULL,
  PRIMARY KEY (id)
);

Running git diff against the two versions produces output like this:

@@ -1,6 +1,6 @@
 CREATE TABLE some_table (
   id int(10) unsigned NOT NULL DEFAULT 0,
   hostname varchar(128) NOT NULL,
-  PRIMARY KEY (id),
-  KEY (hostname)
+  time_created TIMESTAMP NOT NULL,
+  PRIMARY KEY (id)
 );

The pull request's “Files changed” view shows the same problem: a trailing comma on the PRIMARY KEY line drags unrelated lines into the diff.

That diff doesn't communicate what actually changed in the schema. Ruby on Rails ships tooling for this, but we still had to review those diffs carefully. A better option exists for MySQL.

skeema for schema diffs

skeema is an open source schema management tool by Evan Elias. It expects a declarative schema definition in the file system: one directory per schema/database, one file per table, and config files that identify MySQL servers per environment. Key commands are:

• skeema diff — generate the SQL statements to transform an existing database to match the file system definition, including CREATE, ALTER, and DROP TABLE statements.
• skeema push — apply those changes to a live database.
• skeema pull — rewrite the file system definitions from a live server.

Skeema can also invoke online schema change tools, which is beyond what we cover here. For us the most valuable output is skeema diff, which produces clean, formal SQL for the schema transition. For the example above, its output is:

USE `test`;
ALTER TABLE `some_table` ADD COLUMN `time_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, DROP KEY `hostname`;

That output is reliable and consistent, regardless of how the schema files are formatted — case, default values, and other incidental differences don't affect correctness.

To see what a pull request would change in production, we needed to diff the master schema (assumed to reflect production) against the schema on the pull request's branch. Skeema does exactly this, if we tell it where to find both schemas.

There were design questions: should developers own skeema? Should each repository? Should a central service? Each option caused problems, from unclear ownership to over-broad access. GitHub Actions gave us a cleaner answer.

A GitHub Action for schema diffs

GitHub Actions runs arbitrary code in a container in response to repository events — pull requests, reviews, comments, and more. The container can access the repository through an API token that GitHub Actions provides. It also comes with common software installed, including a MySQL server.

We built an action for the pull_request event that runs skeema as part of a flow called skeema-diff. In simplified form, the action:

1. Fetches the skeema binary.
2. Checks out the master branch.
3. Runs skeema push to load master's schema into the container's MySQL instance.
4. Checks out the pull request's branch.
5. Runs skeema diff to get the SQL statements that would take the schema from master to the pull request's version.
6. Posts that diff as a comment on the pull request.
7. Adds a label indicating the pull request contains a schema change.

The production code is more elaborate: it diffs the base and head of the pull request rather than assuming master, edits and validates the diff output, and handles commits that change the schema further during review.

Now we have a flow contained entirely on GitHub's platform: schema change as code, standard pull request review, an automated schema analysis via skeema in an Action, and a readable comment showing what will change. Nothing in this flow touches production, which keeps the repository and its developers in their own domain.

Running migrations in production

Even a simple migration has three cases, each with different risks:

• CREATE TABLE is safe and instantaneous. On a sharded cluster, the create must run on all shards; with vitess, the vtgate layer handles that automatically.
• DROP TABLE is simple but dangerous, since code may still depend on the table. We never issue a direct DROP. Instead we rename: RENAME TABLE repositories TO _repositories_DROP_20200101123456. If something breaks, the revert is another RENAME back. Renamed tables are garbage-collected after a few days.
• ALTER TABLE is the hard case, because it takes time. We don't alter tables in place; we use gh-ost to emulate an ALTER without locking applications. gh-ost throttles as needed and gives us a controllable, auditable process. We have run it in production for over three and a half years with little to no visible impact.

Large tables can take hours or days to migrate with gh-ost. We also run only one ALTER at a time per cluster, since concurrent migrations compete for resources and take longer overall. This means ALTER migrations require scheduling: we need to know if a migration is already running on a cluster, prioritize and queue migrations for the same cluster, report status over hours or days, and — on sharded clusters — run the migration per shard.

Before running any migration, we must decide its strategy. Is it a direct query, gh-ost, or a manual step? Where can it run? How do we handle sharded clusters? When should it run, given other queued migrations?

skeefree: the orchestrator

skeefree is our orchestrating service. It's stateless and runs on kubernetes, backed by a MySQL database that holds state — and whose schema is itself managed by skeefree. It talks to GitHub's API, to our internal inventory and discovery services for locating production clusters, and to gh-ost for running migrations.

Here is the flow from a developer's perspective:

1. A developer opens a pull request to change the schema.
2. The skeema-diff Action runs. If there's no schema change, nothing happens. Otherwise, it posts a well-formed diff comment and adds the migration:skeema:diff label.
3. The developer reviews the change, gets peer review, and eventually adds the migration:for:review label when they want the database infrastructure team to look at it.
4. skeefree watches for open pull requests that carry both labels and have at least one approval.
5. When it finds one, skeefree reads the diff comment generated by the Action, maps the repository schema to the production schema, checks the inventory/discovery service for sharding, and identifies the cluster.
6. skeefree stores this in its database and posts a comment to the pull request — “here's what I will do if you approve.” It then requests review from an authority.
7. For most repositories, that authority is the database-infrastructure team. For our original Rails repository, skeefree also asks a cross-functional db-schema-reviewers team. It routes review requests per repository automatically.
8. On approval, skeefree picks a strategy: direct SQL for CREATE and RENAME, or gh-ost for ALTER. It queues the migration(s).
9. The scheduler determines what to run next. With a single ALTER allowed per cluster and a limited number of runner hosts, skeefree waits for a free slot, then starts the migration. It announces the start with a pull request comment.
10. When the migration finishes or fails, skeefree says so in the comments. For multiple changes — several tables, or a sharded cluster — it announces completion of the full set. The developer is then clear to merge and deploy.

Why this works well

• The database infrastructure team is not interrupted until the developer adds migration:for:review. Developers can work in a draft-like state with their own team before requesting formal review.
• Skeema analysis happens in the repository, with no external service. A developer sees the diff result immediately.
• Only the Action reads the code. Skeefree and gh-ost never touch the repository or need git access.
• The database team's only step is reviewing the pull request.
• Developers own pull requests, peer review, merging, and deployment. They see migration status — queued, started, completed, failed — from the pull request page. Chatops give a global view of the queue and live migration progress.
• The database team owns the mapping from repository schema to production, can cancel a pull request, retry failures, and take operational control. They can throttle or terminate a running gh-ost. Our stack also throttles long-running migrations relative to higher-priority operations.
• The flow relies on our own preferred practices — GitHub's pull request flow, Actions, the GitHub API, and our existing infrastructure — all familiar internally.

Public code, public caveats

skeefree and the skeema-diff Action were written at GitHub for our internal environment. They depend on our inventory/discovery services, chatops, and internal libraries. We open sourced the code to share the ideas, but with clear caveats:

• The code is incomplete — it will not build without some internal libraries.
• It expects services that exist inside GitHub's network, not necessarily on yours.
• It integrates with chatops, which you may not use.
• Adaptation to your environment will require rewriting parts of it.

The repository is public for reading, not for issues and pull requests. We hope it's useful as a reference.

Get the code