1

Is there a decent way to sync MySQL database source code (ie: functions and stored procedures) to git?

I'd like to be able to develop stored procedures within my IDE and version control them in git so I can easily move them between servers, and identify changes to the code. It would be great if I could use git-hooks to auto update my stored procedures on push. It seems like a reasonable thing to want to do, but I don't see anyone doing things this way.

I'd rather not attempt to store a long SP in a migration, or something like Liquibase. Ideally, I'd like to store the pure SQL in my git repo.

Disclaimer: I'm a new DBA, so I may be missing something obvious.

blindsnowmobile
  • 113
  • 1
  • 2
  • 10

1 Answers1

1

I do this by putting each stored procedure/function into its own SQL file and in a folder and then executing them with a a recursive PHP script.

Example file procedure_name.sql would look something like this.

DROP PROCEDURE IF EXISTS procedure_name;
DELIMITER //
/**
Procedure Documantation
*/
CREATE PROCEDURE procedure_name()
BEGIN
   Code goes here
END//
DELIMITER ;

This also makes it easy to do a find and replace of column names etc.

The PHP code looks like so

foreach ($paths as $p) {
     if (strpos($p, '.sql') !== false) {
            echo $p . "\n";
            exec("mysql -u " . $user . "   -p" . $password . " < $p");
      }
 }

After making the changes one can either execute the sql file in MySQLWorkbench of run the PHP script. To sync them to another server you push them up with git pull them down and then execute the PHP script.

Do note that if you have functions they need to be created first.

Charlie Brumbaugh
  • 223
  • 2
  • 10