Good way to manage database test data?

Question

Standard LAMP stack: CentOS, Apache, PHP, MySQL plus Java (via Google Web Tools), eclipse, mercurial

Currently we have a manual integration step where a feature branch is pushed to a staging server where QA tests the feature by poking at it with the GUI. We use a shared dev database. We're running into the problem where feature branches require different database schema creating a bottleneck between feature branch development and acceptance testing.

We'd like to start having separate dev databases but do not know how to handle populating the database with test data (the web app requires some base amount of data to be present to start up i.e. users) and keep that test data in sync with the merging of feature branches, etc.

I read lots of suggestions and tools regarding database schema changes, but not managing database test data. We're only a two person shop so keeping things simple is important. We've thought of just maintaining scripts (SQL files or otherwise) to populate the test database.

Does anyone have resources, success stories, or advice to share?

Answer 1

In my work we have two main ways of doing it: SQL inserts in a version controlled package, and setting up as much data as we can via internal tools.

Since the SQL inserts are versioned then we can branch those alongside any application updates and create a modified set. This can then be merged just as with any other code. One word of advice with this method would be to always use named columns in the SQL inserts. Trying to run in your data and getting the "column count does not match error" is annoying.

A lot of our test data concerns users of our system. This used to be set up by manually inserting it as well, but we extended our internal admin tools to allow scripted setups. This means that we keep the user data in schema-agnostic files (in our case, we have Perl scripts) which use a webservice exposed by the admin webapp to set up the test data.