How to handle multiple db alter scripts coming from different Git feature branches?

Question

A bit complex to describe, but I'll do my best. Basically we're using the Git workflow, meaning we have the following branches:

• production, which is the live branch. Everything is production is running in the live web environment.
• integration, in which all new functionality is integrated. This branch is merged to production every week.
• one or more feature branches, in which developers or development teams develop new functionality. After this is done, developers merge their feature branch to integration.

So, nothing really complex here. But, since our application is a web application running against a MySQL database, new functionality often requires changes to the database scheme. To automate this, we're using dbdeploy, which allows us to create alter scripts, given a number. E.g. 00001.sql, 00002.sql, etc. Upon merging to the integration branch, dbdeploy will check which alter scripts have a higher number than the latest executed one on that specific database, and will execute those.

Now assume the following. - integration has alter scripts up until 00200.sql. All of these are executed on the integration database. - developer John has a feature branch featureX, which was created when integration still had 00199.sql as the highest alter script.

John creates 00200.sql because of some required db schema changes.

Now, at some point John will merge his modifications back to the integration branch. John will get a merge conflict and will see that his 00200.sql already exists in integration. This means he needs to open the conflicting file, extract his contents, reset that file back to 'mine' (the original state as in integration) and put his own contents in a new file.

Now, since we're working with ten developers, we get this situation daily. And while we do understand the reasons behind this, it's sometimes very cumbersome. John renames his script, does a merge commit to integration, pushes the changes to the upstream only to see that somebody else already created a 00201.sql, requiring John to do the proces again.

Surely there must be more teams using the Git workflow and using a database change management tool for automating database schema changes?

So, in short, my questions are:

• How to automate database schema changes, when working on different feature branches, that operate on different instances of the same db?
• How to prevent merge conflicts all the time, while still having the option to have a fixed order in the executed alter scripts? E.g. 00199.sql must be executed before 00200.sql, because 00200.sql might be depending on something done in 00199.sql.

Any other tips are most welcome ofcourse.

Answer 1

Rails used to do this, with exactly the problems you describe. They changed to the following scheme: the files (rails calls them migrations) are labelled with a utc timestamp of when the file was created, eg

20140723069701_add_foo_to_bar

(The second part of the name doesn't contribute to the ordering).

Rails records the timestamps of all the migrations that have been run. When you ask it to run pending migrations it selects all the migration files whose timestamp isn't in the list of already run migrations and runs them in numerical order.

You'll no longer get merge conflicts unless two people create one at exactly the same point in time.

Files still get executed in the order you wrote them, but possibly interleaved with someone else's work. In theory you can still have problems - eg developer a decides to rename a table that I had decided to add a column too. That is much less common than 2 developers both making any changes to the db and you would have problems even not considering the schema changes presumably I have just written code that queries a no longer existant table - at some point developers working on related stuff will have to talk to each other!