Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Should database files(scripts etc.) be on source control? If so, what is the best method to keep it and update it there?

Is there even a need for database files to be on source control since we can put it on a development server where everyone can use it and make changes to it if needed. But, then we can't get it back if someone messes it up.

What approach is best used for databases on source-control?

share|improve this question
21  
A thousand times YES! Simple question deserves a simple answer. –  maple_shaft Sep 23 '11 at 13:55
1  
Wasn't there a huge discussion on this topic once, on stackoverflow.com? –  FrustratedWithFormsDesigner Sep 23 '11 at 14:08
7  
Database SQL files (ddl, dml) are code. All code should be in a version control system. –  dietbuddha Sep 23 '11 at 15:08
4  
Aha! I think this is what I was looking for: stackoverflow.com/questions/115369/… –  FrustratedWithFormsDesigner Sep 23 '11 at 18:44
1  
Not only should your database be under source control but there should be a single script which you can run to re-create it from scratch, that's tables, sequences, views, packages etc. –  Ben Sep 24 '11 at 10:35
add comment

12 Answers

up vote 36 down vote accepted

Yes. You should be able to rebuild any part of your system from source control including the database (and I'd also argue certain static data).

Assuming that you don't want to have a tool to do it, I'd suggest you want to have the following included:

  • Creation scripts for the basic table structures including schemas, users, tables, keys, defaults and so on.
  • Upgrade scripts (either altering the table structure or migrating data from a previous schema to the new schema)
  • Creation scripts for stored procedures, indexes, views, triggers (you don't need to worry about upgrade for these as you just overwrite what was there with the correct creation script)
  • Data creation scripts to get the system running (a single user, any static picklist data, that sort of thing)

All scripts should include the appropriate drop statements and be written so they can be run as any user (so including associated schema / owner prefixes if relevant).

The process for updating / tagging / branching should be exactly as the rest of the source code - there's little point in doing it if you can't associate a database version with an application version.

Incidentally, when you say people can just update the test server, I'm hoping you mean the development server. If developers are updating the test server on the fly then you're looking at a world of pain when it comes to working out what you need to release.

share|improve this answer
    
is there any tools that automate submition of database configurations SPs properties to version control without having to do it manually ?! –  Ali Sep 24 '11 at 6:56
    
@Ali: write the SPs in a flatfile that is version controlled. Have that be the input into a db script which runs your migrations. –  dietbuddha Sep 25 '11 at 6:36
    
@dietbuddh thanks –  Ali Sep 25 '11 at 9:25
add comment

I will also like to bring a monitoring tool that can be also to used as data versioning tool. The tool I am talking about is MONyog, actually it is an MySQL monitoring tool but with a little hack we can easily use it as data versioning.

But before going further i will quote that it will not be advisable to put up whole of the database for versioning. It is be real killer to track change for a particular set of data.

MONyog has a feature called CSO (Custom SQL Objects), which can monitor the change in particular set of data. Adding a CSO is described here. Now in MONyog's monitor history section you can get a the changes over a period of time. Best, it gives a visual report in html page. The report will look something like this enter image description here

share|improve this answer
add comment

DataGrove solves some of the problems mentioned here (by jfrankcarr, for example).

It tracks all the changes to a DB and allows you to save a version of the entire DB's state to a repository. It then allows you to spawn multiple virtual copies of the same DB, so each developer or DBA can have his own separate copy (each virtual copy can be spawned from a different version). It'll make sure no one overrides someone else's code/ changes. Each one of the virtual copies is also tracked into the same repositories so all the DB states can be easily shared and recreated.

share|improve this answer
add comment

For our PHP/MySQL projects, we've been using a (once-) little tool named Ladder. It's designed to facilitate the organic growth of a database over time. All of the migrations for a project are stored in revision/source/version control, and are tracked along with the code.

It supports adding / altering / dropping columns, running queries, adding / dropping indexes, constraints, etc, etc. It'll keep track of the state the database is in, and apply any missing migrations. It also allows you to "step back in time" by specifying a migration you need to be at. (php ladder.php migrate 15)

Oh, and the latest addition is database diffing. Run the diff-save command, add and remove some columns from the database, and run its diff command. You'll see automatically-generated migration code based on the database state.

share|improve this answer
add comment

For Java, our team uses Flyway, which we find really easy to use and powerful.

If you're working in Ruby, Rails has Migrations which are also a powerful way of dealing with this problem.

Liquibase has already been mentioned -- it's a good solution but I found it more cumbersome than alternatives like Flyway.

Also, RedGate software offers a product called SQL Source Control that is designed for SQL Server. I haven't used it myself, but one of my coworkers says it's great.

share|improve this answer
add comment

If you're talking about Microsoft SQL Server database, this tool does just what you're looking for:

http://nobhillsoft.com/randolph.aspx

share|improve this answer
    
would you mind explaining more on what it does and why do you recommend it as answering the question asked? "Link-only answers" are not quite welcome at Stack Exchange –  gnat Sep 15 '13 at 13:47
add comment

Yes

And furthemore, you'll want branches.


I use Git for branches:

  • for development per-feature (like we do for regular development of the rest of the application)

  • and one for the production server as well because customers using the application create contents too.

That way, you get the benefits from source control and branching for both source codes and database (and whatever other files you have).


I haven't found a all-in-one system yet [for PostgreSQL], so I had to write functions/scripts to reindex properly when merging branches (e.g. any index from the production branch should not be modified because customers rely on them whereas indexes+foreign keys from the development branch that intersect with production contents should be reindexed: it wouldn't work for all applications, but it covers all cases of our application so it's good enough).

But the general idea is that database contents are an essential part of the application, and all ressources should be in source control, ergo yes, you should use source control for the database too.

share|improve this answer
add comment

There're tools like liquibase which are intended to provide source control for databases. It's cumbersome to maintain change/update scripts in your regular source control tool like many companies do it and you can't always redeploy the database from scratch.

We've also tried to automate this with database comparison tools (compare master vs. customer db) and that helped, but you can't trust such tools 100%, you definitely need a review process, too.

share|improve this answer
    
I just looked into this Liquibase tool that you pointed out. It looks interesting. How does it work with SQL Server databases? Have you had any experience? –  Bojan Skrchevski Sep 23 '11 at 15:17
1  
@bojanskr: I'm afraid I don't have any experiences but the website lists SQL Server as supported with "no issues". –  Falcon Sep 23 '11 at 15:42
    
thanks for the tip anyway. Your advice has been very helpfull. –  Bojan Skrchevski Sep 23 '11 at 18:09
add comment

Yes

Database scripts (ddl, dml) are code. All code should be in a version control system.

Migrations

  • Use database migrations

Allows you to use the same db files in development, qa and releases.

  • Release to the database with a release number

Store the release number somewhere for auditing, many store it in the db itself. Each release will be composed of migrations which will bring the database up to the correct version.

share|improve this answer
add comment

Think of it as "Version Control" rather than "Source Control". This implies that you can see the entire history of that particular script. Whether or not you can rebuild the database to it's current form is going to be more a matter of your practices regarding these scripts and any frameworks used to create them.

share|improve this answer
add comment

Yes.

what is the best method to keep it and update it there?

Um. Write a schema-builder script. Check it in after making changes. Check it out before running it.

It's hard to determine what you're asking for.

Write formal schema migration scripts. Check them in after testing. Check them out before running them.

What more is there?

What happens is that schema changes turn into gnarly problems because the schema evolves organically through a series of undocumented changes.

This organic evolution makes schema migration more difficult because there's no "authoritative" source for what's supposed to be there. There are two slightly different production versions, a staging version, a QA version and eight development versions. All slightly different.

If there was a single, authoritative source, then schema migration is just the delta between last version and this version.

share|improve this answer
add comment

Here's the problem I've seen many times when there is no version control or change management on development databases. Programmer A makes a change to a table, view or proc. Programmer B makes a change to the same thing and overwrites what Programmer A did. Or, DBA restores a production DB to development and overwrites changes. I've seen this kind of stuff cause considerable grief so many times it's not funny. And this is only on development systems. Things can get really messy when staging/test and even production servers get caught up in this.

The database version control doesn't have to be the same as the regular code version control to be effective. However, some kind of change control and history backups will prevent many problems.

share|improve this answer
    
You might be interested in this article: martinfowler.com/articles/evodb.html –  Falcon Sep 23 '11 at 13:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.