Our blog

 

Using Git to Track DB Schema Changes with Git Hook

If you use Git for version control and you would like to also keep a track of your database schema (and possibly content though I'm not doing that due to potential file sizes / speed issues) then all you need to do is this simple step:

1. Go to your project folder and into the hidden .git folder, then a sub folder in there called hooks

PLAIN TEXT
CODE:
  1. cd .git/hooks

2. Create a file called pre-commit and open it in vim (or whatever text editor you like)

PLAIN TEXT
CODE:
  1. vim pre-commit

3. Add a mysql dump command to that file and save it

PLAIN TEXT
CODE:
  1. #!/bin/sh
  2. mysqldump -u DBUSER -pDBPASSWORD  DATABASE --no-data=true> SQLVersionControl/vc.sql
  3. git add SQLVersionControl/vc.sql
  4. exit 0

(note this assumes you have a folder called SQLVersionControl in the root of your project. If you don't just create it.)

No without any further effort, you will update the schema file on every commit.

eg

PLAIN TEXT
CODE:
  1. git commit -am 'this commit will include a mysql schema dump that has been run just before the commit - sweet :) '

More Reading:

8 Comments

[...] Using Git to Track DB Schema Changes with Git Hook | Edmonds Commerce Blog (tags: git, hook, pre-commit, sql, schema, backup, bash) [...]
 

Steve Nims
February 1st, 2011

How would you commit the schema file itself as part of the pre-commit without running in to some sort of loop? Is there a way to use a conditional logic block in the pre-commit script to only run the schema dump/add/commit on the correct situation? My point being, I'd rather have an atomic commit for the schema file itself rather than bundle it with another commit.
 

admin
February 1st, 2011

good question, let us know if you figure it out
 

Tim Cinel
February 5th, 2011

@Steve @admin

You could use:
git commit sqlschema.sql --no-verify -m "Pre-commit schema update"

"--no-verify" bypasses the pre-commit hook.

http://www.kernel.org/pub/software/scm/git/docs/git-commit.html

Haven't tested this, only read it in the manual.
 

Marcio
April 1st, 2011

Thanks a lot for sharing this. This seems a very nice trick indeed. If we change the mysql schema but we want to preserve the data of an already existent production database, dump will deal with it ?
 

Jean Gionet
October 26th, 2011

Thanks for this method!

I'm fairly new to using git.
I have this script working it seems.

My question is: Will this only run when there's actually a change that occurs within the repository?
What I'd like to do is only push a new .sql commit whenever there's actually a change in the DB itself.
Perhaps I should be seeking another solution to keep my mysql data in sync? I'm only pushing to production, nothing from production comes back to our staging/gold database.

thanks!
 

inf3rno
February 11th, 2012

By me it says that command mysqldump not found. I have windows 7, is there a way to make it work?

Another problem, that if I have space in the directory name then it breaks the path name, and prints directory not found. I'm trying with this:
cd $(git rev-parse --show-toplevel)
to set the location of the backup sql file.
 

inf3rno
February 11th, 2012

Now I figured out that the path containing spaces must be between double quotes, and I called the dump with exec and full path. With these modifications it creates the sql file, but doesn't add it to the commit :S
 

 

Leave a Reply