Version control development test records in database

Question

I have some table and records in a database, that I use to manually test my (PHP) code. Suppose, I'd like to share these records and table structure (e.g: with other developers); if some model changes or new table added, everyone will aware.

How do you integrate to your development process this need? My idea is on every commit dump the DB into a file, and commit that file as well, after the script made sure it's modified. And on update, update this file as well and reload the other development db. This could be possiple via eclipse's external tool function, but I feel this a bit hacky.

How do you deal with test-data in database, if it should be version controlled/shared?

Answer 1

There are numerous methodologies for handling test data, but the process we use is as follows:

• Maintain a project (Visual Studio supports database projects) or at least a folder structure with your database scripts in it
• Build a tool that is capable of grabbing the latest scripts and creating a database from them
• Export your data into some format that can be executed after the scripts have run (INSERT statements, or whatever)

You will need to maintain your data, obviously, as your database changes, which may mean refactoring your data files when new not-null columns get added. However, once you have this system in place, all of your developers can easily grab all the SQL from our source control and generate their own database with test data easily.

The alternative to this would be to maintain a development database for all developers, which we also have. This way, you have one "gold" copy of the development database with the latest schema changes and test data.

Answer 2

Test data is as much part of the code as the tests themselves, so they should definitely be in version control.

As test data usually does not change every commit, but is updated only to accommodate new tests or schema, this is done by hand, mostly to prevent dumping of data which is altered by testing (using the test data). You could however script that dump, or make a target in your build software.

The way we do it is by using ant and a merge file. The merge file is the update of the data file in SQL (alter table, INSERT, UPDATE statements and such). The ant target initializes with the last version of the test data, and executes the merge file on that, and dumps the new data. That way it will always be clear what the changes were.

Answer 3

SQL Data Compare Pro works side by side SQL Compare to allow for static data as well as schema to be scripted out and stored in source control. It does this by saving .sql files with DML (INSERT) statements. As well as scripting out to files, SQL Data Compare Pro can also deploy from these very files to a database.

http://www.red-gate.com/products/SQL_Data_Compare/index.htm

Disclaimer: I work as product manager for Red Gate's SQL Comparison tools.