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.

put on hold as off-topic by mustaccio, John Eisbrener, LowlyDBA, Marcello Miorelli, hot2use Aug 16 at 10:32

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Shopping list question - questions about which tool, library, product or resource you should use are off-topic here because they quickly become obsolete and often are just about the preferences of the answerer. If you have an issue with or a question about a specific tool, please revise your question to conform to that scope." – mustaccio, John Eisbrener, LowlyDBA, Marcello Miorelli, hot2use
If this question can be reworded to fit the rules in the help center, please edit the question.

  • I just use DataGrip. It has git support built in, and is database agnostic – Philᵀᴹ May 4 at 8:18
  • mysqldump can extract stored routine sources into text file(s). Use git from there. – Rick James May 4 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 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.

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