Using Flyway with multiple feature-branches that change the same object

Question

There are already multiple questions and answers touching this subject, How to use Flyway when working with feature branches for example, but none answers the question I have. It might even not be possible to solve.

Say I have a simple stored procedure:

CREATE PROCEDURE GetSomeData AS
    SELECT Id, CreateDate, Stuff
    FROM Data

Now two different feature branches are created, and both features needs to change the same SP. Feature A creates the first change-script, 20160414104532__limit_data.sql:

ALTER PROCEDURE GetSomData
    SELECT Id, CreateDate, Stuff
    FROM Data
    WHERE CreateDate > DATEADD(day,-7,GETDATE())

And feature B needs to add a column to the output. However the teams working with the different features are located in different parts of the world, and really doesn't know anything about each other. They create 20160413153225__add_column.sql:

ALTER PROCEDURE GetSomData
    SELECT Id, CreateDate, Stuff, Things
    FROM Data

When one of the features are completed, it will be merged into the production branch. Three weeks later, the second feature is completed, and merged into production. Here is the dilemma, the second feature will overwrite the stored procedure that was changed by the first feature, and we will potentially have a bug in production.

The real solution here is of course to merge the procedure, but since the scripts are independent of each other, there is no indication of a conflict during the merge. The only way to find out that something bad has happened, is to run the code and find out at runtime.

Are there any simple solutions or workarounds to find these kinds of issues earlier in the process? Maybe flyway isn't the tool to use in these kinds of environments? If not, what are the alternatives?

Answer 1

We kinda solved this issue by using repeatable migrations (as suggested by merz). The idea behind our solution is by keeping the "code" migrations in repeatable migrations and db schema migrations in regular migrations.

Structure of the root of our project :

Structure inside Stored Procedures (and other folders) :

We made each Stored Procedure script contain the definition of one Stored Proc (SQL Server syntax here). To make it repeatable, at the top of every script, the Stored Proc is dropped (if it exists) and recreated right after (in other RDBMS could simply be CREATE OR ALTER) :

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyStoredProc]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[MyStoredProc]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[MyStoredProc] AS
BEGIN
    SET NOCOUNT ON;

    SELECT 1 AS one;
END
GO

Everytime a developer wants to edit some code in the database, he does it in the file named after the stored proc in our project. Then flyway migrate can be run to migrate to the last version every time we git pull our project (since flyway executes the repeatable script when it changes checksum). For table migrations we keep regular migrations because table can generally be altered incrementally (ALTER TABLE dbo.MyTable ADD total INT NULL)

If we use git branches, the code can be easily merged between different branches because changes in code can be compared and resolved in case of conflicts and then merged in the wanted branch.

Hope it helps