Database per Developer and Environment

Our Continuous Integration talk Monday got a bit rushed at the end and I missed an important point when we talked about CI with databases.  For the love of all that is good and holy in the world create a separate database/schema for:



  • Each development workstation
  • Build environment
  • Test environment 
  • A “Sheep” database for the Business Analysts (baaaa) or Product Manager to use for ongoing demos to the customer

Running automated tests against a database is a complete joke unless the database is in a completely known state.  There are some “hacky” alternatives, but the easiest alternative by far is a completely isolated database with only one user.  Think about going to a hospital and seeing them reuse needles.  A shared development database should make you recoil in horror in much the same way.


“Jeremy, everybody already knows that and it’s been a best practice since before you were born.”  I didn’t know that a couple of years ago on my first CI/TDD project and I know there are people out there who haven’t thought about this issue at all.  I’ve been a pair of projects now where we attempted to run automated testing against shared databases (in both cases it was organizational stupidity and bureaucracy in Fortune 100 internal IT departments).  It was excruciating.  An untrustworthy automated build is completely useless.  If your initial reaction to the build busting is “Oh, it’s probably a database conflict.  I’ll just force the build again” — your investment in CI has been squandered.   


It’s also more fuel on the fire for being able to test the majority of your code without the database being involved.  You’ll also need the ability to automatically create a database structure from scratch inside the build to pull off the separate database scheme.


 

About Jeremy Miller

Jeremy is the Chief Software Architect at Dovetail Software, the coolest ISV in Austin. Jeremy began his IT career writing "Shadow IT" applications to automate his engineering documentation, then wandered into software development because it looked like more fun. Jeremy is the author of the open source StructureMap tool for Dependency Injection with .Net, StoryTeller for supercharged acceptance testing in .Net, and one of the principal developers behind FubuMVC. Jeremy's thoughts on all things software can be found at The Shade Tree Developer at http://codebetter.com/jeremymiller.
This entry was posted in Continuous Integration, Database and Persistence, Ranting, Test Driven Development. Bookmark the permalink. Follow any comments here with the RSS feed for this post.