6 ms·

Dbv.php: Database version control

97 points by tortilla 14y ago

dexen 14y ago
Pretty much the same as a home-grown system we're using at workplace :-) The only difference I could find is they use sprintf("%d", $nr) file naming schema, while we use sprintf("%03d-%s", $nr, $login) -- the $login part is to have natural explicit ordering, to avoid duplicate numbers, and the %03d is to have natural ordering in directory listing.
zrail 14y ago
Nice, looks pretty similar to Rails or Sequel migrations. Having used a bunch of these systems, both on a large team and just by myself, I would recommend using a timestamp instead of a sequence number. A timestamp ensures that different team members making migrations at the same time don't stomp on each other's version numbers and cause merge conflicts.
- freshhawk 14y ago
  I can't second that recommendation enough. Any db migration project that uses sequence numbers to label/sort migrations is badly broken for any team larger than 1. I've used enough of these migration systems now that I'm convinced any system using sequence numbers likely has other, less noticeable problems since the problem space is complex and this is a known best practice of which the developers somehow remained ignorant.
  - robmorgan 14y ago
    My DB migration system Phinx (http://phinx.org http://phinx.org) uses timestamps. I had too many problems with using sequence numbers in the past.
  - gizmogwai 14y ago
    Maybe you should have a look to liquibase (http://www.liquibase.org/ http://www.liquibase.org/) then. Although it uses sequence to label the migration changeset, it does not rely on them to ensure that the DB is in a correct state. It relies on a hash of the various changesets so that if a changeset is updated while already applied, the system will complain about it.
    - freshhawk 14y ago
      That's a very interesting solution, I like the sound of it. I will check it out.
    - mtrimpe 14y ago
      Just make real sure you don't change the formatting of a hard-coded SQL command or you'll have to add a 'validCheckSum' attribute to cover up for your past mistakes. Otherwise I can also recommend liquibase, especially since they've since added support for non-SQL (JVM code) updates and rollbacks as well.
  - shuzchen 14y ago
    I can't disagree enough. Time is too brittle of a system to rely on across various machines, especially since you can't be certain all machines can correctly report back the same UTC time. I've seen too many devs using VMs with way out of whack timestamps (VM's clock pauses when it's suspended, and they either forget, or didn't set up a hook to ntp). Also, when is the timestamp set? When the migration code is first written? That could lead to problems when the migration lives in a feature branch I started a month ago. Now that it's time to merge into master, I need to pull in upstream migrations and reorder my own migration to follow those. Using a timestamp here doesn't give any benefit over sequential ordering. Second, if you have multiple migrations created on the same table by two devs that weren't aware of the others actions, this is exactly when a merge conflict should arise. This is probably less of a deal when both are schema migrations, but if both devs included a data migration, the result could be catastrophic if both migrations touch the same data. Human intervention is necessary at this point to make sure the order the system decided for the migrations is fine. Third, if you've got a big team and either you botched your planning so much that you need a ton of migrations, or your policy freely allows everyone to create migrations without communicating with each other, you've got some major problems with your team.
    - adanto6840 14y ago
      The timestamp itself being accurate or not really does not matter at all, at least in the majority of use cases. The idea is just to avoid a filename conflict which, even though the fix may be easy enough, can cause confusion for both the humans (developers) and for the schema management system itself (they usually just keep track of which versions have / have not been run). Even just incrementing new migrations by random(0,1000) would, at least in theory, resolve the issue for the most part. It just seems a lot easier to go ahead & use timestamps though....
    - j-kidd 14y ago
      Agree on all points. Timestamp is really no better than sequence number, and can cause bigger WTF moments with its automated "conflict resolution". So, forget about timestamp and sequence number. GUID is the way to go: http://alembic.readthedocs.org/en/latest/tutorial.html#working-with-branches http://alembic.readthedocs.org/en/latest/tutorial.html#worki...
mememememememe 14y ago
alembic from SQLAlchemy author does half of that. Now the cool thing about this one is it has the gui overview.
tommi 14y ago
Any installation instructions that advice you to do "chmod -R 777 data" I skip.
- pak 14y ago
  then... it won't work? Alternatively, you could make it group writable by yourself and whatever the webserver is running as. Or, if you are using suPHP (http://www.suphp.org/Home.html http://www.suphp.org/Home.html), you won't need to chmod anything.
  - freshhawk 14y ago
    then... you would have to think about your permissions structure and actually set it up properly as opposed to turning it off with chmod -R 777 I agree with the logic, how much trust should I place in software when the documentation says "setting up groups and permissions would take 30 seconds of thought and a minute or so in total. That's too hard for me so fuck it, just do this horrible thing to turn off security completely". Did they handle other problems that way too?
ljnelson 14y ago
Also see Liquibase: http://liquibase.org http://liquibase.org
sheraz 14y ago
Looks like a nicely done tool. A cursory view makes me wonder if it handles stored procedures, custom types, triggers, etc. I ask because I've been using apgdiff [1] with a makefile for my migrations, and it works really well. There is no web interface (cli only), but it handles all the other parts of the database well. [1] - http://apgdiff.startnet.biz/ http://apgdiff.startnet.biz/
- thefreeman 14y ago
  it does handle procedures, functions, triggers, etc.
  - sheraz 14y ago
    cool. count me in. I think I might have some time to begin the Postgres interface
barlo 14y ago
Liquibase is already heavily used in enterprise settings. I could see this being fantastic for smaller dev groups, though.
mtrimpe 14y ago
The frameworks looks pretty decent, but what I would really like is to be able to do TDD-style database migrations. Right now I spend quite a bit of time carefully prepping Liquibase changesets, reverting the DB a couple of times, tweaking the script, re-running it and inspecting the changes. That sounds just like what TDD was designed for...
kitcar 14y ago
The Yii PHP framework also has a similar migration feature http://www.yiiframework.com/doc/guide/1.1/en/database.migration http://www.yiiframework.com/doc/guide/1.1/en/database.migrat...
g9 14y ago
This is nicely named tool. I will never use it, but it is informative and I really like it.
astrodust 14y ago
Is there some kind of place where useful PHP tools, frameworks, and best practices are documented? The majority of people I see trying to learn PHP have no idea these things even exist and are still building applications in the atrocious 1990s style promoted by such poisonously bad resources as w3schools. It would be nice if people actually used tools like this rather than tried to bang their own together with mysql_query.
- ataylor32 14y ago
  http://www.phptherightway.com/ http://www.phptherightway.com/
- naiquevin 14y ago
  >> It would be nice if people actually used tools like this rather than tried to bang their own together with mysql_query. Totally agree with this. After getting super impressed by Django's South migrations[1] app, I wrote a minimal migrations tool[2] for PHP-MySQL projects without checking if solutions already existed :-). Used it in two projects and it made life a lot easier for me and the team. This looks much better though. Will definitely give it a try for my next PHP project. [1] http://south.aeracode.org/ http://south.aeracode.org/ [2] https://github.com/naiquevin/phpDbMigrations https://github.com/naiquevin/phpDbMigrations
  - StavrosK 14y ago
    Yep, South is pretty much mandatory nowadays. I also use django-mediagenerator. Does anyone have a better library for media management to recommend?
fendale 14y ago
Shameless plug. I've developed a similar tool to this without the gui frontend, distributed as a ruby gem - http://dbgeni.com http://dbgeni.com It was inspired by Ruby on Rails and was quite a nice project to develop. It supports oracle, SQLite, sybase and MySQL out of the box. It also handles deploying stored procedures. In a previous job, I used a technique like this to deploy pretty big Oracle application, being worked on by 160 developers, so using migrations to manage your database does work... with some care!
adanto6840 14y ago
This morning I went ahead and implemented this on a project of mine. So far I'm pretty pleased with it overall. Here are a few thoughts after playing with it a bit first hand... I wanted to place DBV outside of my web root and then programmatically include it from existing admin authentication code. For my use case this also addressed most of my permission-related concerns. It did unfortunately require a few very minor code edits to DBV which I'm going to try to package up and submit a pull request for. These were just basic things such as changing the CSS / JS include paths, moving those same assets to an accessible location, and changing some JS / AJAX action URIs to self-reference instead of being "hard coded" to index.php... All in all, pretty trivial to get setup and it really hits the spot in terms of solving the actual problem. Thank you! :-)

97 points by tortilla 14y ago

dexen 14y ago

Pretty much the same as a home-grown system we're using at workplace :-) The only difference I could find is they use sprintf("%d", $nr) file naming schema, while we use sprintf("%03d-%s", $nr, $login) -- the $login part is to have natural explicit ordering, to avoid duplicate numbers, and the %03d is to have natural ordering in directory listing.

zrail 14y ago

Nice, looks pretty similar to Rails or Sequel migrations. Having used a bunch of these systems, both on a large team and just by myself, I would recommend using a timestamp instead of a sequence number. A timestamp ensures that different team members making migrations at the same time don't stomp on each other's version numbers and cause merge conflicts.

freshhawk 14y ago
I can't second that recommendation enough. Any db migration project that uses sequence numbers to label/sort migrations is badly broken for any team larger than 1. I've used enough of these migration systems now that I'm convinced any system using sequence numbers likely has other, less noticeable problems since the problem space is complex and this is a known best practice of which the developers somehow remained ignorant.
- robmorgan 14y ago
  My DB migration system Phinx (http://phinx.org http://phinx.org) uses timestamps. I had too many problems with using sequence numbers in the past.
- gizmogwai 14y ago
  Maybe you should have a look to liquibase (http://www.liquibase.org/ http://www.liquibase.org/) then. Although it uses sequence to label the migration changeset, it does not rely on them to ensure that the DB is in a correct state. It relies on a hash of the various changesets so that if a changeset is updated while already applied, the system will complain about it.
  - freshhawk 14y ago
    That's a very interesting solution, I like the sound of it. I will check it out.
  - mtrimpe 14y ago
    Just make real sure you don't change the formatting of a hard-coded SQL command or you'll have to add a 'validCheckSum' attribute to cover up for your past mistakes. Otherwise I can also recommend liquibase, especially since they've since added support for non-SQL (JVM code) updates and rollbacks as well.
- shuzchen 14y ago
  I can't disagree enough. Time is too brittle of a system to rely on across various machines, especially since you can't be certain all machines can correctly report back the same UTC time. I've seen too many devs using VMs with way out of whack timestamps (VM's clock pauses when it's suspended, and they either forget, or didn't set up a hook to ntp). Also, when is the timestamp set? When the migration code is first written? That could lead to problems when the migration lives in a feature branch I started a month ago. Now that it's time to merge into master, I need to pull in upstream migrations and reorder my own migration to follow those. Using a timestamp here doesn't give any benefit over sequential ordering. Second, if you have multiple migrations created on the same table by two devs that weren't aware of the others actions, this is exactly when a merge conflict should arise. This is probably less of a deal when both are schema migrations, but if both devs included a data migration, the result could be catastrophic if both migrations touch the same data. Human intervention is necessary at this point to make sure the order the system decided for the migrations is fine. Third, if you've got a big team and either you botched your planning so much that you need a ton of migrations, or your policy freely allows everyone to create migrations without communicating with each other, you've got some major problems with your team.
  - adanto6840 14y ago
    The timestamp itself being accurate or not really does not matter at all, at least in the majority of use cases. The idea is just to avoid a filename conflict which, even though the fix may be easy enough, can cause confusion for both the humans (developers) and for the schema management system itself (they usually just keep track of which versions have / have not been run). Even just incrementing new migrations by random(0,1000) would, at least in theory, resolve the issue for the most part. It just seems a lot easier to go ahead & use timestamps though....
  - j-kidd 14y ago
    Agree on all points. Timestamp is really no better than sequence number, and can cause bigger WTF moments with its automated "conflict resolution". So, forget about timestamp and sequence number. GUID is the way to go: http://alembic.readthedocs.org/en/latest/tutorial.html#working-with-branches http://alembic.readthedocs.org/en/latest/tutorial.html#worki...

mememememememe 14y ago

alembic from SQLAlchemy author does half of that. Now the cool thing about this one is it has the gui overview.

tommi 14y ago

Any installation instructions that advice you to do "chmod -R 777 data" I skip.

pak 14y ago
then... it won't work? Alternatively, you could make it group writable by yourself and whatever the webserver is running as. Or, if you are using suPHP (http://www.suphp.org/Home.html http://www.suphp.org/Home.html), you won't need to chmod anything.
- freshhawk 14y ago
  then... you would have to think about your permissions structure and actually set it up properly as opposed to turning it off with chmod -R 777 I agree with the logic, how much trust should I place in software when the documentation says "setting up groups and permissions would take 30 seconds of thought and a minute or so in total. That's too hard for me so fuck it, just do this horrible thing to turn off security completely". Did they handle other problems that way too?

ljnelson 14y ago

Also see Liquibase: http://liquibase.org http://liquibase.org

sheraz 14y ago

Looks like a nicely done tool. A cursory view makes me wonder if it handles stored procedures, custom types, triggers, etc. I ask because I've been using apgdiff [1] with a makefile for my migrations, and it works really well. There is no web interface (cli only), but it handles all the other parts of the database well. [1] - http://apgdiff.startnet.biz/ http://apgdiff.startnet.biz/

thefreeman 14y ago
it does handle procedures, functions, triggers, etc.
- sheraz 14y ago
  cool. count me in. I think I might have some time to begin the Postgres interface

barlo 14y ago

Liquibase is already heavily used in enterprise settings. I could see this being fantastic for smaller dev groups, though.

mtrimpe 14y ago

The frameworks looks pretty decent, but what I would really like is to be able to do TDD-style database migrations. Right now I spend quite a bit of time carefully prepping Liquibase changesets, reverting the DB a couple of times, tweaking the script, re-running it and inspecting the changes. That sounds just like what TDD was designed for...

kitcar 14y ago

The Yii PHP framework also has a similar migration feature http://www.yiiframework.com/doc/guide/1.1/en/database.migration http://www.yiiframework.com/doc/guide/1.1/en/database.migrat...

g9 14y ago

This is nicely named tool. I will never use it, but it is informative and I really like it.

astrodust 14y ago

Is there some kind of place where useful PHP tools, frameworks, and best practices are documented? The majority of people I see trying to learn PHP have no idea these things even exist and are still building applications in the atrocious 1990s style promoted by such poisonously bad resources as w3schools. It would be nice if people actually used tools like this rather than tried to bang their own together with mysql_query.

ataylor32 14y ago
http://www.phptherightway.com/ http://www.phptherightway.com/
naiquevin 14y ago
>> It would be nice if people actually used tools like this rather than tried to bang their own together with mysql_query. Totally agree with this. After getting super impressed by Django's South migrations[1] app, I wrote a minimal migrations tool[2] for PHP-MySQL projects without checking if solutions already existed :-). Used it in two projects and it made life a lot easier for me and the team. This looks much better though. Will definitely give it a try for my next PHP project. [1] http://south.aeracode.org/ http://south.aeracode.org/ [2] https://github.com/naiquevin/phpDbMigrations https://github.com/naiquevin/phpDbMigrations
- StavrosK 14y ago
  Yep, South is pretty much mandatory nowadays. I also use django-mediagenerator. Does anyone have a better library for media management to recommend?

fendale 14y ago

Shameless plug. I've developed a similar tool to this without the gui frontend, distributed as a ruby gem - http://dbgeni.com http://dbgeni.com It was inspired by Ruby on Rails and was quite a nice project to develop. It supports oracle, SQLite, sybase and MySQL out of the box. It also handles deploying stored procedures. In a previous job, I used a technique like this to deploy pretty big Oracle application, being worked on by 160 developers, so using migrations to manage your database does work... with some care!

adanto6840 14y ago

This morning I went ahead and implemented this on a project of mine. So far I'm pretty pleased with it overall. Here are a few thoughts after playing with it a bit first hand... I wanted to place DBV outside of my web root and then programmatically include it from existing admin authentication code. For my use case this also addressed most of my permission-related concerns. It did unfortunately require a few very minor code edits to DBV which I'm going to try to package up and submit a pull request for. These were just basic things such as changing the CSS / JS include paths, moving those same assets to an accessible location, and changing some JS / AJAX action URIs to self-reference instead of being "hard coded" to index.php... All in all, pretty trivial to get setup and it really hits the spot in terms of solving the actual problem. Thank you! :-)