This post originated from an RSS feed registered with Agile Buzz
by Jared Richardson.
Original Post: Isolating databases
Feed Title: Jared's Weblog
Feed URL: http://www.jaredrichardson.net/blog/index.rss
Feed Description: Jared's weblog.
The web site was created after the launch of the book "Ship It!" and discusses issues from Continuous Integration to web hosting providers.
Eric Starr in Charlotte, North Carolina had a few questions about database isolation...
I have a question about Chapter 1 Develop in a Sandbox (from Ship It!).
From Page 16:
"That may sound easy enough, especially in terms of isolating source
code (see Practice 2, Manage Assets,, on page 19), but the real trick
is to remember that it applies to all resources: source code, database
instances, web services on which you depend, and so on."
I understand and practice isolating source code. I have never thought about isolating the database instance for each individual developer What I am curious about is how practical this has been for you given the frequency of database schema changes during the beginning of a project? How do you manage schema changes across a development team when each user has their own instance of the database? Do you put the schema meta data in SCM?
First, I always insist on having a tool that creates the schema. Usually this means a Java program that can read in a schema definition (even if it's just straight SQL) and feeds it to the database... but it could also be Perl, Ruby, whatever. Then the tool and the schema definition can be stored in your Source Code Management system (SCM). This means we can always reproduce our schema on any machine that has the database installed on it by just running the tool. We usually just write something simple (JDBC connections with basic error handling).
Second, because the entire tool can be checked into the SCM software, we manage the schema just like code. When a change is made, let everyone know to update their schema.
Along the same lines, we try to have a script to load minimal test data as well. This way every developer can have a "real" database for coding against without worrying about messing up anyone else's data.
Do you typically use a local instance of the database or a remote instance?
I try to have a local install. Most vendors will provide cheap development licenses... or you can use MySql, or Postgres which are free for everyone. Even if you can't go production with one of the free databases, if you are careful with your SQL, you can code cross platform queries. I've found it to be worth the extra work to have a complete database available to every developer and tester.
Do you typically determine which database to hit using an environment variable and a properties file or some other way?
Yup. I wrote a JDBC connection pool a few years back and I always use that one. :) But the idea is simple... have a Java Property file with your database information in it. Connection URL, usernames, etc. It makes it trivial to switch out databases.
Do you typically populate the database with test data (and remove the test data) using scripts (possibly useful for automated testing so that your data is at a steady/known state at the beginning of every test cycle...you may answer this one later when I get to the chapter about automated testing)?
Yes. In the past I've used Continuous Integration system with an Ant script that would wipe a database, load the test data and then run the tests. If you have the schema creation tool mentioned earlier, this becomes easy to do.
These questions cover the "how" but not the "why" of database isolation. The biggest reason is if everyone shares a database and some of the code you write modifies the data, how does anyone ever know the source of a problem? Is it my code or did Jim corrupt the data again? The last query ran slowly. Did I code some inefficient SQL or was Sue swamping the database again? Isolated instances help you to quickly recognize and understand problems when they occur.
The basic idea is to keep every resource isolated. You will spend more time up front building some basic tools (like the SQL schema creation tool and the data load tool), but I've found that these tools to be worth their weight in gold. They can be re-used across projects and make many useful automation tasks trivial.
Previous Topic
