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?
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:
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.
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.
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.
Yes
Database scripts (ddl, dml) are code. All code should be in a version control system.
Migrations
Allows you to use the same db files in development, qa and releases.
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.
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.
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.
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.
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.
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.
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.
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
