Re: Version control for databases

On Mon, 15 May 2006, mike nicholaides wrote:

> One way I can think of is to dump the contents of the database into a
> SQL script, and put that under version control. Of course, that
> doesn't seem like the most elegant solution.

If your dev team for the DB isn't too large, here's a fairly
generic approach that, while it has overhead, isn't too bad.

Somewhere in your project, assume a directory called 'sql'
in your branch. Assuming some form of the DB already exists,
the first entity committed will be the full schema. If you
were committing this today, this would be stored in

   sql/2006/05/15/01.sql

Now someone goes today in and add a field to a table (or
whatever). They store the changes required to be made to
the DB in

   sql/2006/05/15/02.sql

The idea being, of course, to serialize changes such that
they can be applied in order to transform the DB from the
old state to the new one.

Details and gotchas:

 - It is a good idea to write matching SQL that rolls back
   from the new state to the old state, especially if you
   depend on VC to roll back a running app. This, of course,
   adds quite a bit of work, especially if you need to capture
   data.
 
 - The SQL files need to be considered read only upon commit,
   if multiple people are working on the DB in order to keep
   serialization. This means that if, in the example above,
   02.sql had an error, correcting it would have to be done
   in 03.sql (or whenever later). (If someone commits a syntax
   error, after spanking them, this is the only time to modify
   a file and null op it, and then make the fix in a later
   file.)
 
 - In situations where one is applying DB changes to different
   systems, it is a good idea to commit something like

   sql/2006/05/15/03.checkpoint

   to indicate that that is the point up through which the
   SQL patches have been applied in a given branch

 - Merging branches with SQL changes has to be done manually,
   as these files must (per above) be considered read only.

 - This really has nothing to do with this particular approach,
   but good comments here are important, since one is storing
   a series of DB diffs that humans might well have to step
   through.

If one becomes reasonably confident in this scheme, scripting
updates to the DB is really easy. I apply them manually,
because I don't fully trust these coming in from other developers
to be correct.

As mentioned, it is a bit of work, but this has worked well
for us.

HTH,

-j

-- 
Jamie Lawrence                                        jal@jal.org
Don't just save the whales - collect the whole set.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org