MySQL Schema Version Control: Git Workflows and Diffs

The database release bottleneck in MySQL

The application ships several times a week through a pipeline nobody has to think about, and the schema change that goes with it is still a statement pasted into a terminal. Put the schema in the same repository as the code, as a file a reviewer can read, and the database change travels the same road as the application change: a branch, a diff, a review, a merge.

The pasted statement costs you three things that Git gives back. Nothing records who ran it or against which server, so an environment that fell behind is discovered by a query that works in staging and fails in production. Nothing tells a reviewer what changed, because the only artifact is a statement that has already run. And nothing recreates the schema at the commit a bug was reported against, so reproducing that bug starts with a guess about what the tables looked like. Treating the MySQL schema as a file in the repository fixes all three at once.

Checking the MySQL server you are deploying to

Before you version anything, write down what the target server actually is. Between 5.7, 8.0 and 8.4 the defaults moved, and a migration script written against one set of defaults produces different tables on another.

SHOW VARIABLES WHERE Variable_name IN ('character_set_server', 'default_storage_engine', 'version');

Those are the documented defaults for a stock 8.4 server: the manual gives utf8mb4 as the default value of character_set_server[1], and names InnoDB the default storage engine in MySQL 8.4, adding that CREATE TABLE creates InnoDB tables by default[2]. SELECT @@version returns the same version string on its own if that is all you need. A server that answers latin1 or MyISAM here is not the server your local design assumes, and finding that out now is cheaper than finding it out from a failed deployment.

State-based versus migration-based versioning

Two ways of putting a schema under version control are in common use, and they disagree about what the source of truth is. State-based versioning stores the schema you want: one model that describes every table as it should be, from which a comparison against the live database produces the statements that close the gap. Migration-based versioning stores the journey instead: an ordered series of scripts, each applied once and recorded in a changelog table.

Use both, in that order. Edit the model, because that is the artifact a reviewer can read and the only one that says what the schema is supposed to be right now, then generate the migration script and commit it beside the model so the pipeline has something ordered to run. The cost is two artifacts in the repository that have to stay in step. Migration scripts alone win in one case worth naming: a data migration that no schema comparison can infer, such as splitting one column into two and deciding where each existing value goes. Write those by hand, whatever else you do; the migration tooling around them is a separate choice.

The MySQL design model as a plain XML file

DbSchema keeps its copy of the schema in a .dbs design model file: XML, human-readable, openable in any text editor, and independent of the database. It holds the structure, the diagrams, the virtual foreign keys and the comments, so one file is the whole design rather than the tables alone.

That file is what you commit. Because it is text, Git treats it like source: a branch per change, a diff in the pull request, a merge when two people touched different tables. Designing offline is the mode this depends on, since a disconnected DbSchema session writes every change to the model file and sends nothing to the database, which is what makes the change reviewable before it exists anywhere. DbSchema also carries a Git client of its own: choose Git — Collaborative Design from the Model menu to clone the repository, then stage, commit, push and pull the model file from that dialog.

A worked before-and-after diff of the model file

Take a table that stores accounts, and a change that adds a verification flag with an index on it. Before the change, the table looks like this in the .dbs file:

<table name="users" >
  <column name="id" type="bigint" length="64" mandatory="y" />
  <column name="username" type="varchar" length="50" mandatory="y" />
  <index name="pk_users" unique="PRIMARY_KEY" >
    <column name="id" />
  </index>
</table>

You add the column and the index in the DbSchema diagram and save. The file now reads:

<table name="users" >
  <column name="id" type="bigint" length="64" mandatory="y" />
  <column name="username" type="varchar" length="50" mandatory="y" />
  <column name="email_verified" type="tinyint" length="1" />
  <index name="pk_users" unique="PRIMARY_KEY" >
    <column name="id" />
  </index>
  <index name="idx_users_email_verified" unique="NORMAL" >
    <column name="email_verified" />
  </index>
</table>

Two added elements, and Git shows them as two added blocks of lines. A reviewer can see from the diff alone that email_verified carries no mandatory="y", so the column is nullable and the change is safe to apply while the old application version is still running, and that the index name follows whatever convention the team agreed on. The type is worth a second look: MySQL has no separate boolean storage, and the manual states that BOOL and BOOLEAN are synonyms for TINYINT(1)[3], so a boolean written in the model arrives in the database as a one-byte integer.

Generating the migration script between two versions

The merged model describes the schema you want; the live database is still the schema you have. DbSchema compares the two and writes the difference as SQL:

1. Open Schema → Compare Model with Database to see what differs, object by object.
2. Open Schema → Synchronize Model with Database.
3. Choose the target schema or catalog.
4. Read the generated statements, and edit them in place if you want something else.
5. Click Execute to run them against the database.

Only the last of those touches MySQL. Steps 1 to 4 read the database and write nothing, which is why the DbSchema documentation suggests saving the model to a .dbs file first: if the execution goes wrong, the file is the state you go back to. For a database that has no tables yet, Schema → Create or Upgrade Schema in Database writes the whole model out instead, once the MySQL database itself exists.

For the change above, the generated script is two statements:

ALTER TABLE users ADD COLUMN email_verified tinyint(1) NULL;
CREATE INDEX idx_users_email_verified ON users (email_verified);

Deploy that in three passes rather than one, the pattern usually called expand and contract. Expanding first and contracting last is what makes the change zero-downtime, because every state in between still works for the application version that is already running. First add the nullable column and its index to the live database, where nothing that is running notices them; adding a column supports the INSTANT algorithm, which the manual calls Instant ADD COLUMN and describes as modifying only metadata in the data dictionary, leaving table data unaffected and permitting concurrent DML, subject to the limitations the same page lists[4]. Then deploy the application version that writes both the old and the new structure, and backfill the existing rows. Only when nothing reads the old column any more do you drop it, in a third change that goes through the same model, review and script.

MySQL version control in a CI/CD pipeline

Once the model file and the generated script are both in the repository, the pipeline has everything it needs and no human step in the middle. Start a MySQL container in the pull request build, apply the script to it, and run the application tests against the result: a script that fails on an empty schema fails in the build rather than at midnight. Promote the same script through staging before it reaches production, so the statement that runs last has already run twice.

Schema synchronization does not need the desktop application to be open for any of this. DbSchema can run it headless from a Groovy automation script or from DbSchemaCLI, which is what turns the comparison step into a pipeline job: compare the committed model against the environment and fail the build when the two have drifted apart.

The commit worth making first is the one where nothing has changed yet: reverse-engineer the MySQL database you already run into a model, and put the .dbs file next to the application code so the next change has something to diff against. Get DbSchema at https://dbschema.com/download.html. Connecting, reverse-engineering and the diagrams are in the free Community edition; saving the model to a file and schema synchronization are in Pro, and the download includes a 15-day Pro trial.

Frequently asked questions

What is MySQL schema version control?

Keeping every table, index and constraint definition in the same repository as the application code, so a structural change arrives as a commit that can be branched, reviewed and reverted. With DbSchema the versioned artifact is the .dbs model file, and the migration script generated from it is committed alongside.

How do I check my current MySQL version?

Run SELECT @@version for the version string on its own, or SHOW VARIABLES WHERE Variable_name IN ('character_set_server', 'default_storage_engine', 'version') to get the character set and the default storage engine in the same result. Why those three values decide what your DDL produces is in checking the MySQL server you are deploying to.

Can I store my MySQL database schema in Git?

What the .dbs file holds, and how DbSchema's own Git client reaches a repository, is in the MySQL design model as a plain XML file. After a pull, Compare with Current opens the synchronization dialog on what your teammate changed, and the same Git dialog carries Stash, Pop and Create Branch. One repository can hold several .dbs files, one per database or project component.

How do I generate migration scripts for MySQL?

The five steps from the comparison to Execute are in generating the migration script between two versions. The comparison runs in both directions, and DbSchema offers three choices for each difference it lists: update the model from the database, push the change to the database, or skip it.

Sources

1. dev.mysql.com
2. dev.mysql.com
3. dev.mysql.com
4. dev.mysql.com