17

How should procedural database code like stored procedures be managed with a database migration tool like Flyway?

Unlike DDL I would not want to see changes to a stored procedure stored within multiple database migration files. How can procedural code be managed within a single file under source control, but also take advantage of database migrations with a tool like Flyway?

Nick Allen
  • 1,443
  • 1
  • 11
  • 29
  • I created a very similar [question](http://stackoverflow.com/questions/25839666/flyway-strategy-for-migrating-frequently-changing-functions-stored-procedures) given this is 18months old and a major release has been published since. – markdsievers Sep 16 '14 at 01:50

2 Answers2

13

There is no special construct in the framework to deal with this. Mainly because I haven't figured out a way to do this that pleases me.

Update: Repeatable scripts are now fully supported as of Flyway 4.0. See https://flywaydb.org/documentation/migrations#repeatable-migrations

Axel Fontaine
  • 34,542
  • 16
  • 106
  • 137
  • 1
    Any closer to a suggested approach given the 3.0 release? I've re-asked this question [here.](http://stackoverflow.com/questions/25839666/flyway-strategy-for-migrating-frequently-changing-functions-stored-procedures) – markdsievers Sep 16 '14 at 01:52
3

What we do is:

  1. Have a complete CREATE OR REPLACE Flyway script for stored procedures whenever they change.
  2. Store each of the stored procedures in a separate area in source control, so that changes are tracked and differences are computable.

Not a great solution, and a pain to remember each time, but it gets past some of the limitations of the tool.

orbfish
  • 7,381
  • 14
  • 58
  • 75