What are efficient ways to deal with database schemas that are shared between code branches?

Question

Working on a project with multiple branches, where each branch is eventually merged back to the main branch, and in essence is isolated in order to develop a new feature.

The database, which is MS SQL Server, has a shared schema, however each branch makes changes to the schema as it progresses.

My primary inquiry is what are good ways to deal with sharing the schema from the main branch down to the derived branch, such that changes that are made to the main branch are easily merged into the derived branch, without stepping on new changes in the derived branch?

Answer 1

I have successfully used the following methodology, elaborated in Version Control and your Database:

• maintain a version number in metadata (I use a database extended property)
• any schema change is coded as a script that updates from current version to next version
• application ships with all the scripts to upgrade from version 0 (initial deployment) all the way to current version
• Every change is done through a script. Including 'system' data changes like dictionaries and lookup table entries.
• when deployed, the application checks the on-disk schema version, then runs all the upgrade steps to bring the schema to current required version

I often hear the opinion of 'how is this different from just keeping the object definition scripts under source control?'. The difference is huge, because when you deploy a new version of your app you're not going to simply create a new database. Most times your app will have to upgrade the existing database, including the existing data. This is a crucial difference, your upgrade steps need to ensure the integrity and consistency of existing data during the upgrade. Some operations are trivial in code (add a non-nullable column with default value to the table object definition script, done), but they are in fact hugely painful at actual deployment (the table has 1.5 Billion rows, the add column would run out of log space if done the 'simpleton' way).

How does this works with branching:

• when the branch is created, it snaps the current schema version, say version 1.6
• as the team start working on the branch, it adds a new version, 1.7, and then it starts coding the upgrade step from 1.6 to 1.7
• the upgrade step gets changed as modifications are done in the branch. It always runs the script that upgrade from v 1.6 to 1.7, but what exactly those scripts do, is subject to the normal code iterations and check-ins in the branch
• branch finishes development, it prepares for the reverse integration (to be merged back into baseline)
  • it does a new forward integration from the baseline to the branch. If the integration does not bring any changes to the schema version, all things are good, the branch can reverse integrate as-is. version 1.7 becomes the new baseline version.
  • the interesting stuff is when another branch has reverse integrated into base in the meantime and now the base schema version has changed to, say, 1.7. In this case our branch has to bump it's deployment target schema version to 1.8 and do a review of the upgrade step that was formerly from 1.6 to 1.7 to see how it operates in the new environment, upgrading from 1.7 to 1.8. Logical schema conflicts have to be resolved, script may require changes, testing has to be done. Once completed, the branch can reverse integrate into base. The deployed target version of the product now becomes 1.8.
  • when another branch that has forked at schema version 1.6 wants to reverse-integrate, it needs to it will have to bump it's schema version to 1.9, test the upgrade script from 1.8 to 1.9, then it can integrate back into the base.

Notice that there is no tool involved, no magic schema diff scripting, no wizards and no right-button-click-generate-script involved. This is a 100% developer driven process, based on source (scripts). Many find this whole process elaborate, but it works. In fact, as a SQL Server user, you have already leveraged the results of this process in your daily use of SQL Server: SQL Server itself uses a very similar database upgrade process and, as you probably expect, the product development process makes extensive use of branching and the problem you mentioned is a very real problem that has to be solved.

BTW, how the branching/integration actually occurs differs between source control products, I'm using the terms familiar from the perforce integrate mode of operation.

Answer 2

While my answer might not be as lengthy as Remus', I found this to be a really good solution. I haven't got it set up in production just yet, so YMMV*.

Liquibase

Essentially it is an XML file where you make schema changes to your database as new elements inside the XML file. For example:

<createTable tableName="department">
            <column name="id" type="int">
                <constraints primaryKey="true" nullable="false"/>
            </column>

It has a fully fleshed out syntax so you can pretty much do anything you want to your database.

You also specify in your Liquibase installation what database you want to be versioning. Then you "run" the .xml with the included Java executable (jar file). This essentially recreates those changes specified in the XML to your database.

The real kicker is that you store this XML file in the same versioned folder as your code. So in my instance that was Git. I had this XML file in my project folder (same level as /.git) and then whenever I switched branches the XML file would change to that branch version and I would run the .jar file and my database would now reflect that branch.

*Note: I haven't finished implementation because I had trouble connecting Java to SQL Server. Needs some jdbc drivers and such and I wasn't in the mood. Hence, your mileage may vary.

Answer 3

Here at Red Gate we're soon releasing a database versioning solution that leverages both SQL Compare and SQL Source Control. This uses a migration scripts upgrade approach and stamps the database with a version extended property that corresponds to a source control revision.

We're hoping to release in mid-December. There is a release candidate available now. For more information, visit:

http://www.red-gate.com/products/sql-development/sql-source-control/entrypage/migration

We're hoping to build on this solution in the coming months so please let us know what you think.

Answer 4

I'm in a similar situation where I work on a live website and several development branches in which I need to change the database schema.

I solved it by writing a post-checkout and a post-merge hook that can be used nicely with git. I store all my migrations in the form of SQL files in a separate directory and commit them alongside the changed PHP code. Each time I perform a

git checkout

or a

git merge

git will automatically call the appropriate up- and down-migrations. See my implementation on Github.

Answer 5

If you and handling your schema changes by generating scripts and keeping those scripts under source control, then you should be able to treat the changes as you would any other code merge. You can chose to auto merge or to take more manual intervention.