
A table that should exist in all projects with a database - fogus
http://blog.cherouvim.com/a-table-that-should-exist-in-all-projects-with-a-database/
======
troels
It's all true, but please take the lesson from rails and use timestamps rather
than sequential id's.

~~~
StavrosK
What's the advantage?

~~~
shimon
Consistent ordering even if two developers happen to add migrations starting
from the same source version.

~~~
mgedmin
Could you elaborate on that?

~~~
nerme
Schema version is at 200.

Developer A creates a migration numbered 201. Developer B creates a migration
numbered 201. They push their code, pull, and get conflicts.

So instead, they use a timestamp based system. Developer A created his and got
20100604181252. Developer B got 20100604182530. Now, everyone is happy, and no
one has to change their migrations.

~~~
alextgordon
I think I prefer the first version. At least the conflict is obvious.

Developer A creates migration 20100604181252. Developer B creates migration
20100604181253. Developer B doesn't notice Developer A's migration: there's no
way to know from the timestamps that there might be a conflict. Everything
breaks.

vs

Developer A creates migration 201. Developer B creates migration 201.
Developer B gets a handy notification that something has happened in the
meantime, and gets a chance to patch the migration if necessary.

~~~
binbasti
Actual conflicts from working with the same fields in the same tables are so
rare, I haven't even experienced it once.

However, the second scenario would happen every single day in a team.

~~~
tim_iles
I have occasionally modified a sproc, only to find that a conflicting check-in
has also modified that same sproc. One of our modifications has been
overwritten by the other.

I do concede it is rare, but it happened, and we only discovered the bug too
late.

------
rmc
Some frameworks take care of this automatically. Mango has an excellent
library called South that does exactly this. It keep tracks of migrations and
it can automatically detect nearly all migrations. It's much easier than doing
it all by hand.

~~~
rmc
s/Mango/Django/

Danny you autocorrect on my Android

~~~
RickHull
"Danny you"... heh, priceless!

Mango would be a good nickname for Django + MongoDB

------
tbrownaw
Another approach I've seen used is to map the hash of your schema to an
upgrade script (which would be an empty script for the latest version):

    
    
        while(true) {
          string current_version = <sha1 of the dictionary table contents>
          if (!upgrade_scripts.containsKey(current_version)) {
            complain_loudly()
            exit(1)
          } else {
            string script = upgrade_scripts[current_version]
            if (script.empty())
              break
            else
              execute(script)
          }
        }

------
checoivan
Another option is to have the schema creation/upgrade as scripts (either hand
made or autogenerated ) , then check them into source control.

And resist the temptation of hacking the schema directly in your servers, it
ends being a larger effort :)

~~~
z2amiller
Checking the schema and upgrade scripts into source control is extremely
important, and this is how I have managed it in the past. (names like
table.alter.12.sql). I regret not setting this type of versioning table up
from the beginning, though - when you have multiple environments (dev, QA,
staging, etc) it can get very confusing in a hurry as to which schema changes
have been applied to which databases. With a versioning table and maybe even
some deployment time scripts to sanity check versions between the release and
the DB, mismatches become immediately apparent.

------
jasonkester
There's no particular reason to keep a whole table for this, since all you
really want to know is what version you're at.

We use a UDF called VersionNumber that returns an integer. When the continuous
build processes a new database change script, that function gets modified to
increment the value it returns. So you can always call VersionNumber() on any
of our databases and know which version you're looking at. The builds actually
use it to decide which scripts they need to run to get from where they are to
where they need to be.

You already have all the commentary you need in source control and in the
change scripts themselves. I don't see a reason to duplicate it in the
database as well.

------
moe
I'd argue that "all projects with a [relational] database [and an ORM]" should
rather move to modern tooling instead of writing migrations by hand.

Rails is surprisingly anachronistic here, which probably stems from that awful
conceptual separation between model and schema.

Most other platforms have semi-automatic schema evolution that usually works
very well (datamapper automigrate, django south, hibernate SchemaUpdate,
etc.).

------
lsb
If you don't have enormous amounts of data, one thing I've found helpful on my
own projects is to make a hot backup of the database every time the schema
changes, as well as make hot backups daily, and then when you check out a
version of code you pair it with the latest hot backup.

(This is far easier when using SQLite, which has its own tradeoffs.)

------
StavrosK
Oh, django-south, how I love you.

~~~
GFischer
I find this a more informative comment than the one above:

<http://news.ycombinator.com/item?id=1984609>

as I don't have to google around to find out what south is (I haven't used
Django yet)

------
fendale
Yes yes yes. In my experience in Enterprise Dev teams it seems to be way too
common that people just don't version control their DB schema, or use tools to
produce schema diffs between dev and prod to product upgrade scripts, all
driven by using GUI's to create their tables and then exporting the DDL using
a tool (eg TOAD if you are doing Oracle work).

A few years back, I built an installer in Ruby to apply 'plsql modules' to an
Oracle database. This was a massive project with > 100 developers at this
point and probably heading toward 1M lines of code.

My migrations table worked on modules, so there were a set of migrations per
application area, but it was really just an extension of this idea (and I
borrowed the idea from Rails too)!

------
smarterchild
Migrator.NET uses a similar setup for .NET programmers.

------
rezaman
Although the project is still pretty young,
liquibase(<http://www.liquibase.org/>) is a solid opensource project for not
only managing schema revisions, but inserting seed data as well as abstracting
schema structure from DBMS.

Nathan also does a good job of applying submitted patches quickly.

~~~
quicksilver03
There are also other projects like:

DbDeploy <http://dbdeploy.com/> DbMaintain <http://www.dbmaintain.org/>

------
shashivelur
I wrote a blog post on the same DB migration topic about 2 years ago. This can
be applied to any RDBMS and any programming language. Check it out:
<http://shashivelur.com/blog/2008/07/hibernate-db-migration>

------
mise
I have small PHP/MySQL projects I develop across multiple home computers and
the server. This is a problem that's sometimes a bit hampering. Files are
source controlled through SVN. Any suggestions on a tool that would be
lightweight enough to be worth my time using it.

~~~
chopsueyar
You could use phing.

Here is an older article... [http://www.davedevelopment.co.uk/2008/04/14/how-
to-simple-da...](http://www.davedevelopment.co.uk/2008/04/14/how-to-simple-
database-migrations-with-phing-and-dbdeploy/)

------
stepancheg
<ads> Just use <http://bitbucket.org/stepancheg/mysql-diff/> to compare
schemas. </ads>

------
terra_t
Its generally true, but I've developed systems that go sideways and fork into
multiple versions, so the data structures get more complicated.

------
BrandonM
Why manually specify a key as a string instead of using an int auto_increment?

~~~
brandon
In this case, the key corresponds to the filename containing the migration
statements.

Further, consider if your company had three deployments of your product. One
for bleeding edge testing, one for staging/QA, and live. You'd want the schema
version to be consistent across all three schemas.

