Git: how to version MySQL?

Question

I'm using MODx CMS and would like to use git for co-working. However, as you know, MODx stores some of code places in database. So, I'd like to know, how to version MySQL for co-working except dumping? Thanks

Answer 1

You'll need to store the database in some format, and it can't be SQL as you can't just tell a live database to update the schema using a SQL file.

Basically, you'll have to store an incremental set of commands that updates the database. Git won't really help you in this case, you are going to have to use your own system.

The easiest way to achieve this is by just numbering your database versions and storing only the changes in your favorite programming language.

---

Step 1: store the current database version somewhere.

Step 2: store all updates to the database in files.

update/mysql_1_to_2.py
update/mysql_2_to_3.py
# etc

Step 3: create the initial database

update/mysql_0_to_1.py

---

If you use that system, all you need to do is keep track of the current in use version and the version that's most recent in your code. When you need to update, just run all mysql_x_to_y scripts and you're done with the versioning. mysql_0_to_1 can do the initial bootstrap and if you somehow manage to ever reach 1000 revisions, just use mysql_0_to_1000 to take a shortcut.

The system I outlined above should work really well for linear repositories, but might be a bit harder to use for trees (branches, multiple developers, etc). I'm sure you can come up with something though.

Answer 2

It sounds like perhaps you are talking about stored procedures and/or user-defined functions?

In any case, the best method is mysqldump - presumably, you don't need the data, just the stored procs, user-defined functions, and perhaps table structures. There are options for mysqldump to control what gets output.

Answer 3

If you want to add versioning of database to versioning of code, you have to see at Liquibase. To avoid repeating everything again, my older full answer on topic