MySQL Version Control: How to Do It Like a Pro in Git, SVN, and More

Should you put your MySQL database under version control? And if you do, how?

Source control is like a time-travel machine for your code. It tracks changes, avoids chaos, and helps you collaborate seamlessly.

For databases, it’s a guardian angel. It ensures data integrity, simplifies collaboration, and saves you from database nightmares. Just like version control for code, source control for databases (like MySQL) keeps a record of changes, making it easier to roll back if something goes haywire. This way, your database becomes a well-behaved team player, allowing smooth collaboration and preventing chaos.

This article will discuss versioning your MySQL databases. And your versioning time machine? dbForge Studio for MySQL. It can play nice with known version control systems like Git, SVN, and more.

So, let’s begin.

The World of MySQL Development

You know that the development of MySQL databases is no different from the other players. It’s also not so different than software projects. It’s very dynamic and chaotic if not managed well.

If you’re diving into MySQL database version control, buckle up. There are bumps in the road. Doing it as a team can have nasty results if you don’t collaborate well. But doing it without source control is even trickier than herding cats.

Consider the pain points below together with the ideal state and the moment you may succeed or fail.

Highlights of MySQL Version Control Pain Points

The above table hits home with the following highlights:

• Like a well-choreographed dance, smooth collaboration hinges on effective database source control.
• Merging SQL changes is the hero moment, preventing conflicts from turning your database into a battleground.
• Unchecked schema changes are the silent saboteurs; version control is your shield.
• Rollback capabilities in the face of data chaos are your superhero cape in database management.
• Collaboration without source control is like a dance without rhythm, leading to a cacophony of chaos.
• The cost of chaos? It’s not just dollars; it’s the sanity tax you’d rather avoid. Database source control is your ticket to peace of mind.

You need a simple but powerful tool to ensure you only go to the hero-moment route. That’s the point we’re going to make next.

Key Features of dbForge Studio’s MySQL Version Control

Devart released dbForge Studio for MySQL version 10 with MySQL Version Control. See how it can give you peace of mind in your MySQL database development.

Version Control for MySQL Schemas and Static Table Data

Database schemas are like finely tuned symphonies – change one note, and chaos ensues. So, you need to track it. Because an innocent change from VARCHAR(200) to VARCHAR(20) can light a fire in production.

Devart’s dbForge Studio for MySQL tracks schema changes and if a problem reaches production, a simple rollback can bring relief. It’s like going back in time when things are well and good.

But it’s not just your schema. Lookup tables need not be empty. Your apps rely on these data for dropdown lists and more. DbForge Studio for MySQL can version control these too. So, if someone deletes them into Slytherins, it’s not a point of no return.

Check it out below:

Support for Dedicated and Shared Development Models

dbForge Studio for MySQL supports dedicated and shared development models.

Check out the following selection in dbForge Studio for MySQL:

How to choose between the two will be a topic for a later section.

Historical Tracking of Changes

Every time you make changes and commit it to source control, history is made. For example, you commit all tables for the first time. Then, you change a column from one of the tables and commit it. So, that table will have 2 historical entries.

See an example below:

From the above, you can also see the changes highlighted in line 4. Click on any revision to have a fast comparison of 2 versions. Or press CTRL from your keyboard and select 2 different revisions to compare.

The comparison is also visual so you can do a side-by-side comparison of different revisions.

Capability to Commit and Revert Changes

Whatever your source control system (Git, SVN, TFVC, etc.), dbForge Studio for MySQL can handle changes using Schema Compare. From there, you can revert to a database object’s previous state.

From the revision history, right-click a revision and select Schema Compare as seen below:

Then, the Schema Compare will launch showing database objects that have differences. Comparison is between the selected revision in history and the current database. You can select/unselect the database objects you want to revert. See below.

Then, follow the wizard and let Schema Compare do its thing by replacing the latest structure with the revision you chose.

Automation Within a DevOps Cycle

Deployment from development to production also becomes easy with the new source control feature. The release engineer responsible needs to have a copy of dbForge Studio for MySQL. Then, this specialist will get the latest version from development compared to production.

Schema Comparison will run its magic again and voila! The production database has new and changed database objects.

All these features improve productivity and make development and deployment easy.

Selecting the MySQL Version Control System

Before linking a database to version control, you need to choose the version control system or repositories to use. These are the ones you can use from within dbForge Studio for MySQL:

The most prominent ones are Git and SVN. But the list includes more. And if you use a system that is not listed here? It works too as there is a Working Folder option where you can dump database objects as individual SQL files. Then, you can use the working folder with the files to your version control system not listed above.

But the question is, how do you choose?

Which One is the Right One for You?

Git

• Pros:
  • Widely adopted, strong community support.
  • Distributed version control allows for flexibility.
  • Suitable for both small and large projects.
• Cons:
  • Learning curve for new users.
  • May not handle large binary files as efficiently as other systems.
• Consider When:
  • You value a distributed model and want a versatile tool with extensive community support.

SVN (Apache Subversion)

• Pros:
  • The centralized model provides a clear history of changes.
  • Mature and stable, with good support for binary files.
• Cons:
  • Doesn’t handle branching and merging as smoothly as Git.
  • Requires a server setup for collaboration.
• Consider When:
  • You prefer a centralized model with straightforward history tracking.

TFVC (Team Foundation Version Control)

• Pros:
  • Deep integration with Microsoft ecosystem.
  • Strong support for large codebases and binary files.
• Cons:
  • May feel heavyweight for smaller projects.
  • Limited platform support compared to Git.
• Consider When:
  • You’re already invested in the Microsoft ecosystem, especially for larger projects.

Mercurial

• Pros:
  • Simple and intuitive, easy to learn.
  • Good support for binary files.
• Cons:
  • Smaller community compared to Git.
  • May not have as many third-party integrations.
• Consider When:
  • Simplicity is a priority, and you need good binary file support.

SourceGear Vault

• Pros:
  • Offers a centralized version control system.
  • Integrates well with Visual Studio.
• Cons:
  • May not be as widely adopted as Git or SVN.
  • Limited platform support.
• Consider When:
  • You’re working in a Windows-centric environment, especially with Visual Studio.

Perforce

• Pros:
  • Known for high performance, especially with large codebases.
  • Advanced branching and merging capabilities.
• Cons:
  • Licensing costs may be higher compared to some other systems.
  • The learning curve for more complex features.
• Consider When:
  • Handling a large codebase, and performance is a top priority.

Factors to Consider

1. Collaboration Model
   • Choose a model that fits your team’s collaboration style – centralized, distributed, or a combination.
2. Workflow Complexity
   • Consider your project’s complexity. Git and Perforce are known for powerful branching and merging, suitable for complex workflows.
3. Integration Requirements
   • If you’re already using a specific ecosystem, consider a version control system that integrates seamlessly.
4. Community and Support
   • Assess the community size and support. Larger communities often mean more resources and solutions.
5. Learning Curve
   • Evaluate the learning curve. Some systems are known for simplicity, while others have steeper learning curves.
6. Binary File Handling
   • If your project involves large binary files, choose a system that handles them efficiently.

Remember, the right choice depends on your team’s preferences, project requirements, and existing infrastructure. Each system has its strengths, so find the tool that aligns with your specific needs.

Selecting the Development Model

Another thing to consider before linking your database for version control is the development model.

Do you know which one to choose?

When Dedicated Takes the Spotlight:

• Complex Changes: If your changes are complex and need deep experimentation, having a personal copy avoids impacting the team.
• Frequent Schema Tweaks: If you’re constantly tweaking the schema, a dedicated setup prevents others from feeling the aftershocks.

When Shared Steals the Show:

• Tight Collaboration: For tight-knit collaboration where everyone’s steps need to be in sync, shared development is the way to go.
• Agile Environments: In agile setups where quick collaboration is key, a shared dance floor ensures fluid teamwork.

Linking Your Database to MySQL Version Control

Linking your MySQL database to version control is the starting point in tracking schema changes. Here are the steps:

1. Create the repository depending on the version control system of your choice. For Git, you can prepare this using the GitHub Desktop. Or TortoiseSVN for SVN.
2. From the Database Explorer, right-click the database you want to put in version control.
3. Then, select Source Control -> Link database to Source Control.
4. Select the Source control repository or click + to create a new repository.
5. For new repositories, select the source control system you need and fill in the needed information. For Git, you need to provide the repository folder. For SVN, you need the Server URL, Username, Password, and Database Folder. Meanwhile, TFVC needs the Server URL, Authentication Type, and Database Folder.
6. Choose the Development Model if Dedicated or Shared.
7. Click the Link and wait until all objects from your database display.
8. Select the objects (tables, functions, etc.) to include in source control.
9. Add a comment then click Commit.
10. Wait until every object commits then press OK.

Below is a sample of a committed database in GitHub:

Do you also need some data included in the MySQL version control? That’s the next topic we’re going to discuss.

The Magic of Static Data Control

Some data are so important like the system settings of your app. You may opt to include some of these in version control and dbForge Studio for MySQL can do it for you.

Here are the steps:

1. From the Database Explorer, right-click the database you already linked to source control.
2. Then, select Source Control -> Link/Unlink Static Data.
3. Choose the tables you want the data to go to version control.
4. Select the data you want included for each table.
5. Add a comment then click Commit.

See a sample below when Commit is a few clicks away.

Chronicle of Changes: The Historical Record

Let’s make history to this table:

CREATE TABLE sales.store (  store_id int NOT NULL,  store_name varchar(255) NOT NULL,  address varchar(255) NOT NULL,  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  PRIMARY KEY (store_id))ENGINE = INNODB,AVG_ROW_LENGTH = 3276,CHARACTER SET utf8mb4,COLLATE utf8mb4_0900_ai_ci; ALTER TABLE sales.storeADD INDEX ix_store_store_name (store_name);

We’ll add a store phone number and email. The repository is GitHub, the one we’ve used earlier.

Assuming a branch in GitHub is already created, let’s change the table.

USE sales;ALTER TABLE store   ADD COLUMN phone_number VARCHAR(30) DEFAULT NULL;ALTER TABLE store   ADD COLUMN email VARCHAR(50) DEFAULT NULL;

Then, let’s refresh the Source Control Manager. After that, you will see it detects changes in the table structure. Since it also added null values to the new columns, changes in the data were also detected. See below:

To commit this to source control, add a comment then click Commit. Or Undo it if you want to revert to the latest changes.

To see the version history of the store table, right-click the table. Then, select Source Control -> View Changes History. Select a revision to see a comparison between the previous version and the current version. Check it out below and notice the added columns in red.

The changes in dbForge Studio are also reflected in GitHub. Check it out below:

As you change objects in the database, the more revisions you see in both dbForge Studio for MySQL and Github. You can revert to a previous version if you wish.

Collaborative Conquests: Teaming Up in Source Control

Source Control is the linchpin of efficient collaboration. It enables developers to seamlessly harmonize their efforts. It serves as a shared canvas where team members contribute, track changes, and synchronize code effortlessly.

Through versioning, developers navigate the evolution of the codebase. It’s for mitigating conflicts and promoting a fluid development flow. Branching empowers parallel work. It allows features or fixes to evolve independently, only merging when polished. Commits serve as checkpoints, documenting alterations and facilitating targeted rollbacks.

With these mechanisms, Source Control orchestrates a collaborative symphony. It ensures code and data integrity. It empowers experimentation and transforms development into a cooperative endeavor. This is where individual contributions coalesce into a harmonious ensemble.

For example, different revisions will help other developers who will touch the same database object. Each revision shows who did it, when, and what changed. Commit notes and comments help why the changes were made so nobody in the team will wonder why was it necessary.

See the history in dbForge Studio again with notes on what we are talking about.

Important! MySQL Version Control does not replace backups. It will save the database structure and static data and allow you to roll back (if necessary). However, this does not guarantee the preservation/integrity of all data in the database. Therefore, it’s essential to remember regular backups.

Takeaways in MySQL Version Control

Our favorite MySQL GUI tool grew even more in version 10 by adding Source Control. It integrates well with other Source Control systems. Database versioning is now possible and team collaboration is easier. Each change adds to an object’s history which also opens up undoing any damage done.

Why not try it today? With its other notable features like the Query Profiler, Data Generator, and more it can make you productive in no time.