Using Branches while developing the database

In the previous tutorial, we discussed how DB Version Control supports multiple developers working with the database - both dedicated and shared development models are supported.

In this tutorial, we will demonstrate how to use branches within your preferred version control system (GIT, SVN) while developing your database.

We will continue using a previously created project within DB Version Control with the AdventureWorks sample database as well as Github Desktop for the file versioning.

Creating a new branch and switching to it

First, let's create a new branch FeatureA and switch to that branch in our source control system.

Next, open the project within DB Version Control - note that the database files will be from branch FeatureA even though our project still points to AdventureWorks database.

There are a couple ways how branching can be done in DB Version Control:

  • using the same project within DB Version Control but using a different database - switching back between branches would require changing the database in the Source connection settings
  • using a new project within DB Version Control by opening an existing project from Projects list but specifying a different database in the Source connection settings. This option allows a bit easier switching between branches as both projects with different databases will be shown in the Projects list

In this tutorial, let's use the same project but will switch to a different database once the branch switched in the version control system.

Creating a new database for the branch

Branching only makes sense when a dedicated database development model is used - a separate database is needed for feature development.

To create a new database from existing migration scripts, open an existing project within DB Version Control:

  • open project's Settings tab
  • under Source connection group, tick Create a new database from migration scripts
  • enter the database name and click Create

That's it - a new database was created from the migration scripts from Migrations directory (branch FeatureA). Do not forget to change Database name in the project's settings to the new database.

Note that in the previous examples we restored AdventureWorks database from the backup which included all database objects and data - creating a database from migration scripts only creates those database objects and fills only those data tables that are being version controlled. Of course, you can always do backup/restore manually instead of creating a database from the migration scripts.

Now, let's make a change in our AdventureWorks_FeatureA database by creating a new table for our feature - dbo.FeatureA. If we go back to our project in DB Version Control and click Get changes, we will see our change - click Apply Changes to create a new migration script and update database object files.

If we switch to our version control system, we will see that a new migration script has been created as well as a new database object scripted into a file.

Next, commit these changes to FeatureA branch.

Once we are happy with our feature development, we can merge our feature back to the master branch using tools available to our version control system.

Once switched to the master branch and merged changes from FeatureA branch, change the database name in Source connection details to the AdventureWorks.

Next, switch to the Changes tab and click Get Changes - AdventureWorks database will be compared with the Migrations directory and a message will appear displaying that our newly merged migration script (V0__2020.07.10_100039__New table for FeatureA.sql) has not been applied to AdventureWorks database:

This is correct - only the AdventureWorks_FeatureA database had these changes. Choose dedicated database development mode and Execute missing scripts option to apply the missing migration script.

That's it - we demonstrated a simple scenario on how to develop a new feature in another branch and then merge those changes back into the master branch.

Is it always this simple?

Unfortunately, no. The situations are more complex when changes to the database are developed in parallel.

Let's take an example when a master branch had a bugfix migration added and different stages of FeatureA development:

  • the feature has been in the development (changes were made to the database) but not scripted to the files just yet
  • the feature has been developed and scripted into files - the bugfix migration date was created before our feature migration date
  • the feature has been developed and scripted into files - the bugfix migration date was created after our feature migration date

The feature has been in the development but not scripted to files just yet

In this case, update feature branch FeatureA from master and click Get Changes. You will get an info screen saying that the bug fix migration has not been applied to FeatureA database - our choice will be to use Dedicated database development mode and choose to Execute missing migration(s).

Note that in this case there are Local changes in our current database - a newly created table dbo.FeatureA has not been scripted into the migration script just yet. Before applying the bugfix migration, the script contents should be reviewed - depending on what the bugfix migration does, it may or may not affect our changes made in FeatureA database and that is how it should be decided how local changes should be treated.

Apply corrections will apply the bugfix migration on the FeatureA database.

After the feature development is complete and a new migration V0__2020.07.10_100039__New table for FeatureA.sql has been created, the changes from FeatureA branch can be merged back into the master branch - the order of migration scripts will be correct.

The feature has been developed and scripted into files - the bugfix migration date was created _before_ feature migration date

What happens if a bugfix migration (V0__2020.07.10_090000__Bugfix.sql) was added before FeatureA migration (V0__2020.07.10_100039__New table for FeatureA.sql) was created but before our FeatureA was merged back into the master branch?

 Master branch Feature branch
V0__2020.06.11_094800__Local migration.sql V0__2020.06.11_094800__Local migration.sql
V0__2020.07.10_090000__Bugfix.sql
V0__2020.07.10_100039__New table for FeatureA.sql

Similarly as in previous examples, FeatureA branch should be updated with changes from the master branch first.

Then the order of applied migration scripts in the FeatureA branch would look like this:

  • V0__2020.06.11_094800__Local migration.sql
  • V0__2020.07.10_100039__New table for FeatureA.sql
  • V0__2020.07.10_090000__Bugfix.sql

However, this is not a correct order because the bugfix migration should have been applied first and only then - FeatureA migration. If we click Get changes, we will get a similar screen saying that the order of applied scripts is not correct:

Even though merging FeatureA branch back into the master branch would result in the correct order of applied migration scripts (in the master database), doing it is not recommended because FeatureA has not been tested with the bugfix migration.

Migrations in DB Version Control are forward only - therefore it is not possible to revert already applied migration V0__2020.07.10_100039__New table for FeatureA.sql.

Let's review available options:

  • Update the database to the latest version - this option makes the database exactly the same as the one created from the migration scripts and fixes the Versions table records but does not execute a bugfix migration. You should be careful when choosing this option and fully understand how it works
  • Update Migrations directory to correct the order of the migration scripts - not applicable in this case
  • (not displayed under a list of options) Restore the database from a backup and re-apply migration scripts in the correct order
  • (not displayed under a list of options) Create the database from the migration scripts in the correct order

In this case, we will re-create the database from the migration scripts:

  • drop FeatureA database
  • open project's Settings tab
  • under Source connection group, tick Create a new database from migration scripts
  • enter the database name and click Create

Once the FeatureA database has been re-created with the correct order of migrations scripts and re-tested then the master branch can be updated from FeatureA branch by merging back changes from FeatureA into the master branch.

The feature has been developed and scripted into files - the bugfix migration date was created _after_ feature migration date

What happens if a bugfix migration (V0__2020.07.13_105301__Bugfix.sql) was added after FeatureA migration (V0__2020.07.10_100039__New table for FeatureA.sql) was created but before our FeatureA was merged back into the master branch?

 Master branch Feature branch
V0__2020.06.11_094800__Local migration.sql V0__2020.06.11_094800__Local migration.sql
V0__2020.07.10_100039__New table for FeatureA.sql
V0__2020.07.13_105301__Bugfix.sql

Similarly as in previous examples, FeatureA branch should be updated with changes from the master branch first.

Then the migration scripts in the FeatureA branch would look like this (which is the correct order for FeatureA database):

  • V0__2020.06.11_094800__Local migration.sql
  • V0__2020.07.10_100039__New table for FeatureA.sql
  • V0__2020.07.13_105301__Bugfix.sql

However, if FeatureA changes V0__2020.07.10_100039__New table for FeatureA.sql are merged back into the master branch, then the order of applied migrations may end up different than expected:

  • V0__2020.06.11_094800__Local migration.sql
  • V0__2020.07.13_105301__Bugfix.sql
  • V0__2020.07.10_100039__New table for FeatureA.sql

This situation clearly shows why simply ordering migration scripts by date is not enough. To resolve such situations, DB Version Control migrations support version number in the migration script file name.

Instead of using the same version number as from the master branch, the version number can be increased to any valid number (e.g. "V1" or "V20200713"):

 Master branch Feature branch
V0__2020.06.11_094800__Local migration.sql V0__2020.06.11_094800__Local migration.sql
V1__2020.07.10_100039__New table for FeatureA.sql
V0__2020.07.13_105301__Bugfix.sql

Before merging FeatureA branch to the master branch, update FeatureA with changes from the master branch:

  • V0__2020.06.11_094800__Local migration.sql
  • V0__2020.07.13_105301__Bugfix.sql
  • V1__2020.07.10_100039__New table for FeatureA.sql

Once FeatureA branch has been updated with the bugfix, the order of applied scripts is no longer correct.

The correct order is to first apply bugfix and only then - FeatureA changes.

Similarly as in the above example, FeatureA script has already been applied to FeatureA database - the options to resolve this situation are:

  • Update the database to the latest version - this option makes the database exactly the same as the one created from the migration scripts and fixes the Versions table records but does not execute a bugfix migration. You should be careful when choosing this option and fully understand how it works
  • Update Migrations directory to correct the order of the migration scripts - not applicable in this case
  • (not displayed under a list of options) Restore the database from a backup and re-apply migration scripts in the correct order
  • (not displayed under a list of options) Create the database from the migration scripts in the correct order

Once the FeatureA database has been re-created with the correct order of migrations scripts and re-tested then the master branch can be updated from FeatureA branch by merging back changes from FeatureA into the master branch.

Summary

In this tutorial, we demonstrated how to:

  • create a branch to develop the database and later merge changes into the master branch
  • create a database from the migration scripts
  • use the version number in the migration script filename
  • fix the order of migration scripts

The migrations in DB Version Control are forward only, therefore, cannot be simply reverted - to learn more, check the documentation on the migrations.

Leave a comment