Working with a team while making changes to the versioned database

In the previous tutorials, we demonstrated couple simple scenarios when a single developer made changes to the database. In this tutorial, we will cover how DB Version Control works when multiple developers are involved in the database development.

Dedicated vs shared database model

A dedicated database model is when each database developer has their own copy of the database. This model is the preferred one because making changes to the database does not affect other team members' work.

A shared database model is when a single database is used for development and all team members make changes to it.

DB Version Control supports both models automatically - the only difference is how to handle conflicts between the records in dbup.Versions table and files from the Migrations directory.

In the previous tutorials, when a single developer made changes to the database, the records in dbup.Versions table (migrations that have been applied) were in sync with files in Migrations directory. However, when multiple team members are making changes, the records in dbup.Versions table may be different than a list of migration files.

Let's review different scenarios when records in dbup.Versions table are not in sync with the files in Migrations directory and how such conflicts can be resolved.

For the dedicated database development model, we assume that the files in the Migrations directory are correct. However, for the shared database development model, we assume that the records in dbup.Versions table are correct.

Option in bold indicates the primary suggested option for the specific situation.

  Dedicated Shared
A migration exists in files only (not in dbup.Versions table)
  • Execute missing migration(s)
  • Delete migration file(s) from the Migrations directory
  • Delete migration file(s) from the Migrations directory
  • Execute missing migration(s)
A migration exists in the dbup.Versions table only (not in files)
  • Mark the migration(s) as deleted in the Versions table. The changes made by executed migration(s) are not reverted
  • Update Migrations directory from source code repository or add missing migration file(s) (manually)
  • Update the database to the latest version
    • Update Migrations directory from source code repository or add missing migration file (manually)
    • Mark the migration(s) as deleted in the Versions table. The changes made by executed migration(s) are not reverted
    • Update the database to the latest version
      The order of migrations is different between dbup.Versions table and files
      • Update the database to the latest version
      • Update Migrations directory from source code repository or correct the order of migration files (manually)
      • Update Migrations directory from source code repository or correct the order of migration files (manually)
      • Update the database to the latest version

        Getting changes

        To get changes from the source code repository, a user should do an update through their choice of version control system (e.g. git pull or svn update).

        It is up to the developer to resolve merge conflicts (if there are any) in the same way application's source code merge conflicts are resolved (by choosing theirs, ours, or merging differences in the files).

        After scripted database files are updated, click Get changes within DB Version Control application.

        Conflict resolution using a dedicated database model

        In the dedicated database development model, the files in the Migrations directory are being used as the source of truth for the list of migrations.

        Scenario 1 - additional migration script(s) available in the Migrations directory

        The most likely scenario is that another developer added a new migration script - therefore we should probably execute this script on our local database to have exactly the same version of the database as other developers.

        If, on the other hand, a new migration script was added by accident, DB Version Control tool could delete the file from the Migrations directory.

        Scenario 2 - a migration script is missing from the Migrations directory

        This could happen because a migration file has been removed - even though it is not recommended to delete migration scripts, sometimes such situation may occur.

        In order to have the files in Migrations directory in sync with dbup.Versions table, there are couple options available:

        • Mark the migration(s) as deleted in the Versions table. It is important to note that only the status of the record in the Versions table is being changed to DELETED - no changes to the actual database are done and changes made by that particular migration are NOT reverted.
        • Update Migrations directory from source code repository or add missing migration file(s) (manually)
        • Update the database to the latest version. Even though DB Version Control uses migrations based approach which is forward only (in order to revert a migration, a new migration has to be added that reverts changes made), it is possible to undo a migration by comparing source files with the database and applying the differences to the local database directly (to make the local database exactly the same as the database from sources). Note that no migration scripts will be executed and local changes in the database will be removed. The records in the Versions table will be updated to match files from the Migrations directory.

          Scenario 3 - the order of migration scripts is different between dbup.Versions table and the Migrations directory

          This could easily happen if you make changes and create a new migration in your local database, and then update source files just to find out that another developer added another migration earlier.

          Of course, the best way to avoid such situation is to update source files often as well as commit/push new migrations to the source repository as soon as they are available.

          However, how this situation can be resolved? Again, there are couple options:

          • Update the database to the latest version - the same way as mentioned in Scenario 2
          • Update Migrations directory from source code repository or correct the order of migration files (manually)

          Conflict resolution using a shared database model

          In the shared database development model, dbup.Versions table is being used as the source of truth for the list of migrations.

          Scenario 1 - additional migration script available in the Migrations directory

          This should really not happen because a migration is first being executed and recorded in the dbup.Versions table, and only then added to the Migrations directory. It could be that the migration was marked as DELETED in the versions table.

          In this scenario, the migration file should be deleted from the Migrations directory.

          If, on the other hand, you are sure that the migration should exist, you can apply it on the shared database.

          Scenario 2 - a migration script is missing from the Migrations directory

          This could happen when a migration script has been applied to the shared database but has not been committed/pushed to the source repository. Ask your team members to push this change and update your local files from the repository or add the missing migration file manually.

          On the other hand, if the migration was executed by mistake or migration file is no longer available, the migration could be marked as DELETED in the Versions table. In this case, the changes made by the migration are NOT reverted.

          The last option would be to Update the database to the latest version. This option compares source files with the database and applies the differences to the local database (to make the local database exactly the same as the database from sources). Note that no migration scripts will be executed and local changes in the database will be removed. The records in the Versions table will be updated to match files from the Migrations directory.

          Scenario 3 - the order of migration scripts is different between dbup.Versions table and the Migrations directory

          This could happen when the migrations have been applied to the database but are not pushed/updated to/from the source code repository.

          Probably the easiest way to resolve this is to fix the files in Migrations directory manually to be in sync with the records in the Versions table.

          On the other hand, if you are sure that the order of files in the Migrations directory is correct, you can choose to Update the database to the latest version to resolve the issue.

          Examples

          Scenario 1 - additional migration script available in the Migrations directory

          First, let's take an example where the Migrations directory contains an additional V0__2020.07.07_102313__New migration.sql file.

           Versions table Migration directory
          V0__2020.06.10_102235__Baseline.sql V0__2020.06.10_102235__Baseline.sql
          V0__2020.06.10_124822__Added Notes column to Product table.sql V0__2020.06.10_124822__Added Notes column to Product table.sql
          V0__2020.06.11_094800__Updated view with Notes column.sql V0__2020.06.11_094800__Updated view with Notes column.sql
          V0__2020.07.07_102313__New migration.sql


          Here is how this may look in DB Version Control application for both dedicated and shared database development models.


          Choose the correct option and click Apply corrections.

          If there are any changes made to the database that have not been scripted yet, you will be asked how to handle them:

          • Keep them as is - in this case, the new migration will be executed. Depending on what the migration does to the database, the local changes may be overridden or the migration script may fail (if the script is not compatible with local changes)
          • Discard changes before applying scripts - in this case, your local changes will be reverted and then the migration script will be executed
          • Save changes to a script and discard changes - in this case, your local changes will be scripted into a file, reverted in the database, and new migration script executed. You will be able to re-apply those changes after the migration script has been executed. 

          If we chose Save changes to a script and discard changes, after Apply corrections we would get a new script under Not applied scripts.

          Scenario 2 - a migration script is missing from the Migrations directory

          Next, let's take an example where the Migrations directory does not contain a V0__2020.07.07_102313__New migration.sql file.

           Versions table Migration directory
          V0__2020.06.10_102235__Baseline.sql V0__2020.06.10_102235__Baseline.sql
          V0__2020.06.10_124822__Added Notes column to Product table.sql V0__2020.06.10_124822__Added Notes column to Product table.sql
          V0__2020.06.11_094800__Updated view with Notes column.sql V0__2020.06.11_094800__Updated view with Notes column.sql
          V0__2020.07.07_102313__New migration.sql


          Here is how this may look in DB Version Control application for both dedicated and shared database development models.

          Scenario 3 - the order of migration scripts is different between dbup.Versions table and the Migrations directory

          Lastly, let's take an example where the Migrations directory contains an additional V0__2020.06.25_140659__MiddleMigration.sql file.

           Versions table Migration directory
          V0__2020.06.10_102235__Baseline.sql V0__2020.06.10_102235__Baseline.sql
          V0__2020.06.10_124822__Added Notes column to Product table.sql V0__2020.06.10_124822__Added Notes column to Product table.sql
          V0__2020.06.11_094800__Updated view with Notes column.sql V0__2020.06.11_094800__Updated view with Notes column.sql
          V0__2020.06.25_140659__MiddleMigration.sql
          V0__2020.07.07_102313__New migration.sql V0__2020.07.07_102313__New migration.sql


          Here is how this may look in DB Version Control application for both dedicated and shared database development models.

          Summary

          DB Version Control handles the differences between the Versions table and files from the Migrations directory no matter which database development model you choose - dedicated or shared.

          The simplest scenarios can be handled by applying the migration script or updating the Migrations directory from the source code repository. However, if a migration has to be undone or the order of applied migration scripts is not correct, DB Version Control allows updating the database to be in sync with the source files and fixing Versions table.

          To have less conflicting situations, we recommend updating the Migrations directory from the source code repository often as well as committing/pushing newly added migration so that other developers could quickly receive and apply migrations in the correct order.

          In the next tutorial, we will look at how to use branching for the database development.

          Leave a comment