Dbv.php: Database version control | Hacker News Reader

Dbv.php: Database version control

dbv.vizuina.com

97 pointstortilla28 comments

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.

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.

My DB migration system Phinx (http://phinx.org) uses timestamps. I had too many problems with using sequence numbers in the past.

Maybe you should have a look to liquibase (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.

That's a very interesting solution, I like the sound of it. I will check it out.

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.

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.

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....

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#worki...

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.

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

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), you won't need to chmod anything.

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?

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.

http://www.phptherightway.com/

>> 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/

[2] https://github.com/naiquevin/phpDbMigrations

Yep, South is pretty much mandatory nowadays. I also use django-mediagenerator. Does anyone have a better library for media management to recommend?

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/

it does handle procedures, functions, triggers, etc.

cool. count me in. I think I might have some time to begin the Postgres interface

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! :-)

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

Shameless plug. I've developed a similar tool to this without the gui frontend, distributed as a ruby gem - 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!

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...

Also see Liquibase: http://liquibase.org

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

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

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

Reply on news.ycombinator.com