Subject: How do you version your database?

From: Omer Zak <[email-masked]>
Date: Thu, 05 May 2011 06:26:47 +0300

Troy Hunt details, in
http://www.troyhunt.com/2011/05/10-commandments-of-good-source-control.html, ten good version control practices.

Practice No. 7 is "Versioning your database isn't optional".

I wonder how do people actually version their databases.

--- Omer

-- 
I am the Cochlear Corporation of the Borg.  All resistance is futile.
Deaf Culture is irrelevant.  YOU SHALL BE IMPLANTED.
My own blog is at http://www.zak.co.il/tddpirate/

My opinions, as expressed in this E-mail message, are mine alone.
They do not represent the official policy of any organization with which
I may be affiliated in any way.
WARNING TO SPAMMERS:  at http://www.zak.co.il/spamwarning.html

---

From: Arik Baratz <[email-masked]>
Date: Thu, 5 May 2011 14:09:48 +1000

On Thu, May 5, 2011 at 1:26 PM, Omer Zak <[email-masked]> wrote:

> I wonder how do people actually version their databases
>

We used to have a script that creates the entire database schema from
scratch. Any DB change would be added to the script and checked in.
Developers would use this script when building their environment etc.

-- Arik

---

From: Amit Aronovitch <[email-masked]>
Date: Sat, 7 May 2011 13:30:30 +0300

> Developers would use this script when building their environment etc.
OK for the schema, but what about the actual data?

You need a script for upgrading the data, at least from each production
release to the next one. And given a db-dump, you should be able to know
which version it belongs to, so you know which script to run.
I think it would be best to store some date/version stamp in some table, so
you'd never lose track of which schema/code is relevant for the data (once
you have that, you can automate and have the upgrade-scripts use this
stamp).

   AA

---

From: Arik Baratz <[email-masked]>
Date: Sun, 8 May 2011 21:50:59 +1000

> OK for the schema, but what about the actual data?
Our script loaded the data as well :) If you start from a new database and
run the script you would end up with the up-to-date schema loaded with all
of the default values etc. All this was maintained by our DBA.

Maybe it is not the most efficient way to do it but it worked for us.

> you have that, you can automate and have the upgrade-scripts use this

I think what you are talking about is different - a web application or
something similar that holds production data.

What I was referring to was a product that was using a database and the
programming environments that we created for that. We had no need to version
live data on our database.

-- Arik

---

From: Amit Aronovitch <[email-masked]>
Date: Mon, 09 May 2011 00:14:50 +0300

On 08/05/2011 14:50, Arik Baratz wrote:

>
> On Sat, May 7, 2011 at 8:30 PM, Amit Aronovitch <[email-masked] 
> <mailto:[email-masked]>> wrote:
>
>
> Our script loaded the data as well :) If you start from a new database 
> and run the script you would end up with the up-to-date schema loaded 
> with all of the default values etc. All this was maintained by our DBA.
>
>     You need a script for upgrading the data, at least from each
>     production release to the next one. And given a db-dump, you
>     should be able to know which version it belongs to, so you know
>     which script to run.
>     I think it would be best to store some date/version stamp in some
>     table, so you'd never lose track of which schema/code is relevant
>     for the data (once you have that, you can automate and have the
>     upgrade-scripts use this stamp).
>
>
>

Right. I was thinking of SAS where the database holds the customers' 
valuable data.
But this is also relevant for installable software, where you want the 
user to have an easy way to upgrade the software without losing the data 
he already fed into the system and without having to manually select 
scripts to run according to version.
I guess if you have a backup feature that packages other stuff along 
with the db dump, the version info could be stored outside the dump 
(still it seems safer to keep the version in the db itself, to avoid 
dealing with file formats etc.)

> What I was referring to was a product that was using a database and 
> the programming environments that we created for that. We had no need 
> to version live data on our database.
>

I see. It remains to see which case the OP had ment. :-)

AA