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.

  • I just use DataGrip. It has git support built in, and is database agnostic – Philᵀᴹ May 4 '19 at 8:18
  • mysqldump can extract stored routine sources into text file(s). Use git from there. – Rick James May 4 '19 at 14:44
  • DataGrip looks promising, thanks. I've had good experiences with Intellij and PyCharm, so I went ahead and bought it. It looks like I can develop within the IDE and then export to disk. Kind of surprising there isn't something open source available though; version control of routines seems to be an overlooked area in MySQL. – blindsnowmobile May 13 '19 at 15:43
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.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.