Database Migrations: The Next Killer PHP Application
This post, written by Adam Wiggins, got me thinking:
Migrations bother me. On one hand, migrations are the best solution we have for the problem of versioning databases. The scope of that problem includes merging schema changes from different developers, applying schema changes to production data, and creating a DRY representation of the schema. But even though migrations is the best solution we have, it still isn’t a very good one.
Schema synchronization is the killer feature missing in phpMyAdmin!
This feature can be found in some commercial applications, like SQLyog Enterprise for example. SQLyog’s Database Synchronization Tool (DST) is a visual comparison/synchronization tool designed for developers who work between different MySQL servers or need to keep databases between two MySQL servers in sync. This means reporting the differences between tables, indexes, columns of two databases, and generating the necessary “Up” and “Down” scripts to bring them in sync.
However, SQLyog DST is not free and only runs on Windows.
So, if you want to build the next killer PHP application, port SQLyog’s database synchronization tool to PHP and open source it. People will love you for it.
ezcDatabaseSchema (www.ezcomponents.org) already supports abstract schema diffing, at least for a subset of features of mysql. you could probably add the rest.
the problem of integrating that into phpmyadmin is, that pma mostly is installed for only one server. what you can do is writing a tool, which can register servers and allows remote access to them and diffing the db schema and stuff.
this probably requires only to write some glue around the ezcDbSchema component.
beberlei
March 3, 2009 at 1:26 pm
Yes, I was thinking more of a small, standalone application: ezcDbSchema + UI = Killer app
Federico
March 3, 2009 at 1:43 pm
There’s also mysqldiff, the UI won’t kill you (it’s 4 years old) but works as expected
http://www.mysqldiff.org/
WaaX
March 3, 2009 at 3:35 pm
Does it have everything SQLyog has?
Federico
March 3, 2009 at 6:28 pm
For database migration, I use Talend. But the scripts generated are either in perl or java. I hope an output in PHP will be soon added!
Alexis
March 4, 2009 at 4:39 pm
Some ORM libraries already offer migration features, e.g. Doctrine.
symbiat
March 4, 2009 at 5:01 pm
Hi Federico, I’m actually working on such a tool. It’s not usable yet but I’m probably going to have a usable version ready within the next month. It does exactly as you say, with a commandline binary to drive the API.
Felix
March 4, 2009 at 5:32 pm
Great! Sounds like the tool we all have been waiting for. Good luck and please keep me updated :)
Federico
March 4, 2009 at 7:46 pm
I wrote a simple Zend-dependent tool to do primitive migrations, I personally haven’t a need to abstract out Zend, but it could be done simply and quickly in one sitting …
http://github.com/tsmckelvey/mingo
Tom
March 5, 2009 at 6:59 am
One mistake in this post:
SQLyog (both the free and Open Source COMMNUNITY version and the ENTERPRISE version (that includes Schema Sync and Data Sync) runs with Wine on at least the platforms:
* Linux
* Mac OS-X
* FreeBSD
* (and should run on Solaris too, but I have no option to test)
Links:
Official SQLyog FAQ:
http://www.webyog.com/faq/content/31/71/en/can-i-use-sqlyog-with-the-wine-windows-emulator-on-linux.html
Independent reviews on wineHQ;
http://appdb.winehq.org/objectManager.php?sClass=application&iId=1328
Peter Laursen
March 5, 2009 at 12:32 pm
Yes, you are right. It runs with Wine on those platforms. I have the enterprise version running on my VM.
Thanks for the links Peter.
Federico
March 5, 2009 at 2:26 pm
@Tom Check out SQLyog Enterprise, it generates all the up/down queries for you. SQLyog, so far, is the best tool for the job.
Federico
March 5, 2009 at 2:42 pm
I’m still using http://www.fabforce.net/ to model the database and then keep it in sync. That Database Syncronisation ( + Reverse Engeneering ) work just good enough for me for.
AdvanCode
March 6, 2009 at 4:15 pm
I’m currently using the MySQL Migration Toolkit. It’s made my life tons simpler. It’s windows only though. For some reason, MySQL has links to the Linux and Mac OS X installation, but then tells you it’s not available.
http://dev.mysql.com/doc/migration-toolkit/en/index.html
Frank Maulit
March 12, 2009 at 4:35 pm
You might want to check out Brazil, sure it not a db diff tool. But it is a tool to track changes in a database. We are currently using this at work and our dbas are very pleased with it.
http://github.com/jbripley/brazil/tree/master
johan nilsson
March 20, 2009 at 1:09 pm
Looks good, but it’s missing the diff tool :) I think what’s key in db migrations is not having to write a single line of SQL or XML.
Federico
March 21, 2009 at 9:10 pm
Federico, have you yet discovered any such tool?
2 years on and no one has implemented it yet? I can see great value for it – I would be willing to pay $$ for a good implementation.
Mohammad
October 20, 2011 at 10:17 am
Hi all,
Like some of you I have encountered the need for a rails like migration database tool to use with full php project. After a quick search, I haven’t found any tool that might fit with what I need. I have also used the good java migration tool MyBatis and was in need of a migration tool designed for php that can support both of rails and MyBatis functionnality. So I decided to write my own php migration tool you can found here :
https://github.com/alwex/PhpDbMigration
Only tested through postgresql database, but based on PDO, so it may be used with any PDO supported database.
Hope it help someone.
Alex
Alwex
October 21, 2011 at 3:41 pm