Migrating stored procedures with Grails Database Migration Plugin

Stored procedures are often preferred over writing long inline queries for getting reports from database. But they tend to change constantly depending on the business logic. The database migration plugin handles the changes in domain structure effectively but too bad the default management of changes in stored procedures is not as effective.

We can manage the changes in stored procedures with the runOnChange=’true’ configuration in the changeSet tag. This forces the plugin to check if the change set was changed instead of just checking if it was run once before so it can be skipped.

That way every time we modify the stored procedure we don’t need to add new change set to the changelog file, the previous change set gets executed automatically, this help us keep the change log file smaller and keep the stored procedures updated.

Also it is advisable to use sqlFile tag which accepts relative path of sql file to load create stored procedure sql scripts instead of writing the sql scripts inside the changelogs to reduce the size of changelogs as stored procedures can be pretty lengthy. By default the sql statement in the file is split on ;’s to avoid this splitStatements = “false” configuration is used to makes sure the complete create procedure script is read as one instead of splitting it on ;’s for each individual sql statement inside.

This is what our final changeset looks like :

changeSet(author: "Rahul (manual update)", id: "1435600003872-128", runOnChange: "true") {
    sql("DROP PROCEDURE IF EXISTS BestCustomers;")
    sqlFile(path: "../sql/BestCustomers.sql", splitStatements: false)
}

Code for BestCustomers.sql :

CREATE PROCEDURE `BestCustomers`(
     //Input params here
   )
BEGIN
    //Sql scripts here ;
END

Hope this helps !!
Please share if you find more optimum way for this.