How can I version control Stored Procedures while

Question

I currently have a Java web app which uses a Microsoft SQL Server back end. Database migrations are currently being done manually by sqlcmd which makes use of the :r command to reference script files of our stored procedures and views.

This way each stored procedure has his own file "proc_someprocedure.sql" The migration is done by an upgrade script "6.1 upgrade.sql" which refrences the proc file to drop and recreate it in it's current version.

We're looking to migrate to flyway and have the application migrate it's own database upon deployment. However, I can't find any easy way to maintain a single source code file for each stored procedure. In the event that a stored procedure is changed, I need to make the change in both a V_6_1__change.sql file and the proc_someprocedure.sql file in version control.

I'm trying to avoid copy/pasted sql code as it's haunted us in the past.. How are others handling this situation?

Answer 1

As of 3.0 there is no first class support for this. There are however 3 ways to achieve this:

• Use a custom MigrationResolver that hooks into whatever source control you use and detect changes that way
• Use a FlywayCallback that recreates all stored procedures with afterMigrate
• Use a separate Flyway instance within a separate schema and set cleanOnValidationError to true. Everytime a checksum changes, the schema will be cleaned and all the migrations managed by that Flyway instance will be rerun