Database Migrations Or Git Hooks


This is my 3rd try of get answers about it. The first was in a e-mail list, another in a portuguese list and finally here. I really would appreciate any of your opinions.

I started to study Yii Database Migrations just to get impressed in how much it is a manual work. I dont think that I should be doing editing/creation of so many migrations just to syncronize my database with my application. It is (IMO) a duplicated work since i have to make my db changes in a workbench tool and turn it into migration codes. I just would need forget about to do it one time to have problems on migrate.

What I am doing? I am using Git Hooks.

I created a script that before every commit generates a copy of the db (a sql file via mysqldump), puts it in protected/data, adds to commit, and, finally, commits it. I just continue to use ‘git commit’. I dont need learn new things, I nor even would be able to forget it cause it is an automatic. Every time I do a checkout, my sql file containing the state of the database is there just waiting for me. I just need to put the script in the specific folder and it is done.

Note: it is possible choose to save structure and/or data and it is also possible to make a script to recover automatically the database on a git checkout.

So, I really would like to know: what are the good things I am loosing about yii migrations? What about git hooks? What do you think to be the pros and cons?

Really would appreciate your opinion. I feel should there to be something very good in migrations yii that I have not found till now and I really would love to find.

Thanks in advance.

No answers? :blink:

  • SQL dump of database structure can’t do altering of existing production database. (It is very likely to happen on live sites).

Suppose you want to add one column, index or maybe change ‘name’ field from varchar(100) to varchar(55) in a particular table. Migration could alter the table while full dump can only drop and recreate the table.

  • Migrations allow writing db manipulation code in database agnostic way while SQL dumps may contain database specific statements.

Thank you so much Galymzhan! You were the first one to point me a real reason to use the migrations! You are completely right.

I just think hard to use it cause I really like to use visual database modelling tools but i will try soon. Maybe after to have a project in production mode while I will continue using the script in dev mode.

Just one more question:

Is not there a way to generate the first scripts based on my current database? It would save so much time since I have some projects having dozens of tables!

Thank you guy.

You are welcome.

No, there isn’t (yet) any way to do this. See this issue.

A bit late, but it worth to mention that there is a simple command to do this - in the comments to the definitive guide article about migrations.

Late for the project i was working on, early for the next ones. :)