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?

Have you tried to use repeatable migrations - https://flywaydb.org/documentation/migration/repeatable - or it isn't suitable for you? — merz, Apr 16 '16 at 05:06
@TheQ Did you find an answer to this problem? Do you use a workaround ? We are facing the same issue here and would like to hear what you found. — MaxiWheat, Jan 31 '17 at 14:45
@MaxiWheat No, we unfortunately haven't solved this issue yet. The only "workaround" we have is that someone in the team inspects all scripts that are being released, and tries to see if there seem to be a conflict. That approach is far from perfect, and we have has issues in production because of it. I guess flyway is not the tool to use when working with feature-branches like this, but we haven't had the time or energy to find another solution yet. — TheQ, Feb 01 '17 at 10:04

score 3 · Accepted Answer · answered Mar 22 '17 at 20:48

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

Yep, that is actually a solution to the issue I described. It's pretty much the same way as RedGate SQL Source Control works, but that is a quite expensive software, so this solution is much nicer. Just be aware that grants, if you use them, will be dropped when you drop the SP in the top of the script, so all grants needs to be included in the bottom of each file, alternatively use the solution mentioned here http://stackoverflow.com/a/2885664/328864 — TheQ, Mar 24 '17 at 07:24
Yeah, did not thought about that as in our software we don't really care about grants since we use a single login to access the database and this login is the database owner. But good call for the solution on creating the empty SP if does not exist and then always alter. — MaxiWheat, Mar 24 '17 at 23:06
What about DML changes? Using the previous example, an update is performed through the script of Feature A when merged into the production branch. Later on, when Feature B is merged into the production branch, another update that changes those same records is performed. This will lead to inconsistent data. There is a way to solve this as well? — Cássio, Jun 01 '17 at 12:28
@Cássio Unfortunately, this must be handled manually in this case (that's what we do). You should however have a hint that there is something wrong since you will get errors from Flyway for migrations with the same number, but you will have to look every migration and see if they have conflicts. — MaxiWheat, Jun 01 '17 at 12:44

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

1 Answers1