Is Your Database Under Version Control?

When I ask development teams whether their database is under version control, I usually get blank stares.


This is a companion discussion topic for the original blog entry at: http://www.codinghorror.com/blog/2006/12/is-your-database-under-version-control.html

Amen to that. I’d think of leaving my database out of version control just as readily as I’d think about turning up to work not wearing pants.

Yes, we have our database structure in version control.

At least for the application I’m working on. =)

Yeah mang, I update the schema in source control whenever the mood takes me. The actual database itself is massive, so this is the easiest solution for now.

There are still problems when I update the test database and someone is running an older version of the code against it. Whatever, I do what I want.

Innovatis (http://www.innovartis.co.uk/) provides several tools that can be used for database deployment, some of which can be used in conjunction with source control system (these are not free, though). In my team, we keep all scripts to build/populate/upgrade/repair databases in source control systems, and follow a slightly modified version of the approach described in this article: “Streamline Your Database Setup Process with a Custom Installer” (http://msdn.microsoft.com/msdnmag/issues/04/09/CustomDatabaseInstaller/). We have been doing this for a few years with no issues.

Sorry, what do you mean by “your database”?

Do you mean the RDBMS itself? The schema? The data? What?

I usually let my application create the database schema (yes I have CREATE TABLE strings in my source code), so the database is implicitly under version control. Works very nicely.

If you work on oracle…

This is not fancy as a dedicated tool, but I find this script quite useful:

CREATE TABLE SOURCE_HIST – Create history table
AS SELECT SYSDATE CHANGE_DATE, USER_SOURCE.*
FROM USER_SOURCE WHERE 1=2;

CREATE OR REPLACE TRIGGER change_hist – Store code in hist table
AFTER CREATE ON SCOTT.SCHEMA – Change SCOTT to your schema name
DECLARE
BEGIN
if DICTIONARY_OBJ_TYPE in (‘PROCEDURE’, ‘FUNCTION’,
‘PACKAGE’, ‘PACKAGE BODY’, ‘TYPE’) then
– Store old code in SOURCE_HIST table
INSERT INTO SOURCE_HIST
SELECT sysdate, user_source.* FROM USER_SOURCE
WHERE TYPE = DICTIONARY_OBJ_TYPE
AND NAME = DICTIONARY_OBJ_NAME;
end if;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, SQLERRM);
END;
/
show errors

so at least, every change done to “db-code” is saved without human intervention :slight_smile:

I “stole” this code on http://orafaq.com/

Enjoy!

Amen to that. I’d think of leaving my database
out of version control just as readily as I’d
think about turning up to work not wearing pants

love it!

I don’t know if this is optimal, but here’s the setup we’ve got at work:

  • A script to pull the “schema” from the production database. Along with table structure, it also pulls a selection of tables that represent “structural” data, usually the tables you end up with in databases that are expected to never have more than 10 or 20 rows and are useful primarily as relation targets. This is run relatively rarely, really, but it’s a critical part of keeping the dev environment I describe below grounded in reality.

  • A script that any developer can run that starts by pulling in that schema, and then applying a series of transform scripts, which are of course in SVN. That script also configures that developer’s account in that database. We also maintain as a cultural ideal that at any time you should be able to blow away that database and not cry.

  • I’ve also created some easy test-data creation tools, based in our primary development language, which can be used both to generate steaming piles of test data, and also powers our unit tests.

  • The process of changing a database schema is to write a migration script and check it in, and check in the addition of that script to the developer test-db creation script. Then, after testing it on a copy of the production database, we eventually apply that script in SVN to the production database. Even later, the base schema gets refreshed and the now-defunct script comes out of the test-db creation script.

This has worked fairly well for us, though we are not as large a shop as some places. Being able to just blow away our dev databases at any time and restart from scratch is incredibly useful on its own, but never, ever, ever having the only schema change as living in a developer’s database only is almost certainly a lifesaver. (We never got into trouble with that, but I could see it coming.)

And what would you use if you had to support both SLQ Server and Oracle for the same DataBase Schema?

Yes, I know that using the same logical structure (tables, stored procedures, etc) for SQL Server and Oracle is one of the most stupid things to do, but my team has only 5 people, and none of us is an Oracle guru, so… :stuck_out_tongue:

Regarding tools:

At a certain level, if you really want your database under TRUE version control inside of a repeatable process, everything needs to be in scripts. AFAIK, there’s no easy way to point-and-click your way to a version-controlled database. You have to use DDL.

The way we do it is we have a script to create the entire schema (you can script it right from the database the first time), and then we have “upgrade” scripts that run with each release. Our build system (CruiseControl/MSBuild/MSBuild Community Tasks) runs the scripts in order, essentially performing upgrade after upgrade. (We wrote a tool to merge each of the upgrade scripts into one single script, in order.)

After that, our unit tests are run against that newly created database. Chances are that if the unit tests fail on the auto-generated test database, but they succeed on developers’ desktops, the database is inconsistent and someone will have to modify the change scripts (which you could do by investigating with Red Gate’s SQL Compare).

What about FREE solutions to get my database under version control?

The best and free solution (as people mentioned above) - to keep everything in scripts.

Perhaps you ought to look into Rails migrations, it may give you an idea of how to implement database versioning.

In Rails you specify what the schema is for each version of your application, and migrations can take care of everything (including data manipulation/transformation).

You just issue a command like “rake migrate VERSION=#” and that’s all!

http://www.emxsoftware.com/RubyOnRails/Ruby+on+Rails+Migrations+Explained

Yeah, it’s nifty. But it’s not the best tool for the job.

Red-Gate SQL tools are the best thing for comparing, scripting, and RE-ing your data and schema. SQL Packager is what sold me. ( http://www.red-gate.com/products/SQL_Packager/index.htm ). The ability to bundle up database changes into an exe easily is a “good thing”.

Heck, they even provide intellisense for Query Analyzer. They beat MS at their own game.

As a developer in a fairly large shop (20+ developers, 10ish database peeps), I dream of the day that the database group (both Oracle and SQL) support version control. Virtually every deployment we’ve had in the last 4 years has been boogered because of the database group. Our deployment group deploys our code, using our ant scripts straight of CVS and then has to send out an email saying “OK dba’s, do your stuff…” The release guys have no idea what’s going in to the build from the database side of things. And management is OK with this… And then us mere “developers” have to chase “bugs” for a couple of days only to determine that somebody forgot to manually update a table or worse insert some records into a code table.

I forwarded this post to all of them…maybe it’ll resonate. Doubt it, but a guy can hope.

We mere developers are responsible for our own database modifications. If I need a field changed, I must write the script, then test it against a local copy of the database (SQL Server Dev) before even attempting to get it rolled onto the network versions. The local dbs are synced with the network dbs periodically.

That script to update the databases, along with the initial generating script, are what we keep in our version control.

Yes, version control has saved my butt more times than I care to count, and we haven’t had it a year yet.

I’m using ActiveRecord migrations on a project today. The DSL is much nicer than SQL create scripts and you get the ability to go up/down to any version built in. This also can be used outside of Ruby on Rails so you don’t need a rake file or any other stuff. We use Ant and shell out to a simple script that invokes the migrations via ruby.

The other nice part is that you avoid DB specific create scripts. This is nice for us as we wanted portability (as much as possible), however; it may be less important to others. One caveat is that we did add a few helper methods on top of the ActiveRecord adapters to support simple creation of foreign keys.

Finally, you can quickly define ActiveRecord model types in a migration which makes searching and manipulating data in scripts easy.

I posted about this here:
http://blog.harborsidesoftware.com/articles/2006/10/27/versioning-database-schemas-the-easy-way

Is the VSTS Db Prof going to stick us with yet another minimally functional database project in the IDE, like all the past “attempts”? It’s great that they attempt something, but there have always been products out there that beat easily, and actually seem to fit in a db developers workflow properly.

I just fear that they will put out yet another POS “database” project and we will all be stuck with it because of the usual management BS. I’m happier with nothing, thank you.