How to edit stored procedure in migration transparently?

I have a migration in Laravel project, that creates a stored procedure in the database. It looks nice the first time I create this migration.

But if I want to change an existing stored procedure, I have to create a new migration, containing the new version of the procedure code.

It works, but it doesn't allow to make and see clear diff's in git.

diff

git

Here is an example migration:

use IlluminateDatabaseSchemaBlueprint;
use IlluminateDatabaseMigrationsMigration;
use IlluminateSupportFacadesDB;

class GenerateReport extends Migration
{
public function up()
{
DB::statement(<<<SQL
CREATE OR REPLACE FUNCTION myfunc() RETURNS void AS
$func$
BEGIN
COPY (SELECT 1) TO '/tmp/myfile.tmp' BINARY;
END
$func$ LANGUAGE plpgsql;
SQL
);
}

public function down()
{
DB::statement("DROP FUNCTION myfunc()");
}
}


My question - is there a way to work with migrations and be able to see clearly every change in every line of migration in git?

git

diff

migration

2 Answers
2

The simplest solution I see is - create one specific migration file for stored procedures, file name doesn't have any date in it to avoid confusion (eg. migrations/add_stored_procedures.php).

Whenever there is a need to modify a stored procedure - edit that existing file so that Git displays the modifications properly, as you wanted.

Force this particular migration to run every time migrations are executed. Two possibilities:

Extend and replace Laravel's Migrator class to allow unlogged migrations. This would be my preferred choice, Laravel is very flexible and allows to swap implementations easily.

Right in your migration, after you modified your stored procedure, run a raw DB query that marks this migration as 'fresh' in the migrations table to make Laravel believe it was never ran. This option is definitely easier.

Thanks for idea. "to allow unlogged migrations" - what do you mean?
– mnv
May 19 '16 at 6:29

normally every migration, after execution, is being 'logged' - it's marked as successfully executed in a special MySQL table. You can see this line in Migrator.php in Laravel: $this->repository->log($file, $batch); So that's it - if we avoid this action for specific migrations (your migration with stored procedures) - target will be achieved :)
– Denis Mysenko
May 19 '16 at 11:45

Based on this answer by @Denis Mysenko.

Create a file with SQL query for each migration, for example, database/my_procedure.sql.

database/my_procedure.sql

After changing this file create a new minimalistic migration like this:

use IlluminateDatabaseMigrationsMigration;
use IlluminateSupportFacadesDB;

class GenerateReport extends Migration
{
public function up()
{
$filename = base_path() . '/database/database/my_procedure.sql';
DB::statement(file_get_contents($filename));
}

public function down()
{
}
}


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.