Extreme Enthusiasm

A database for every developer. No, *two* databases for every developer.

This is a fundamental for project organization that many projects get wrong. Every development workstation should be equipped with a full local development environment, with a local copy of the database software, and a one-command way to recreate the databases from scratch.

Why *two* databases? Well, one is for exploratory testing of the application we’re building. The other one is for automatic unit tests.

Why *local*? Because whenever the database server is not local, it becomes difficult to add a new workstation, it’s impossible to work when you’re not in the office, and you must depend on other people to fix your database problems.

The software that we write should *not depend* on the data sources that live outside our development workstation. To this end it’s a good start to have simple scripts that allow you to rebuild your database, so that you know you can experiment, change everything, make mistakes, and you’re still able to get back to a known working situation in a flash.

Why it’s important that I can rebuild the databases with *one command*? Because if it takes more than one command, it’s too complicated and I’m likely to make mistakes. Because it’s too easy to fall in the trap of not knowing exactly which steps are needed to set up a new database instance. If you have a single script that does the job, that script is also a living, always up-to-date document that describes how to recreate the database from scratch.

The benefits are not just in development; when the time comes to release our software in production, you can see how helpful it is to have a script that is able to set up the database with no effort. In fact, all database maintenance operations should be automated. It’s one of the principles explained so well in The Pragmatic Programmer, a very good book.

For example, this is a typical script that I use in my non-Rails projects:

#!/bin/bash

src=src/main/sql
dbname=myapp_development
dbname_test=myapp_test
dbuser=myapp_user
dbpassword=myapp_password

# Usually no changes needed beyond this point

if [ ! -d "$src" ]; then
  echo "Run this script from the main directory"
  exit 1
fi
read -s -p "mysql root password? (type return for no password) " MYSQL_ROOT_PASSWORD

if [ "$MYSQL_ROOT_PASSWORD" != "" ]; then
    MYSQL_ROOT_PASSWORD=-p$MYSQL_ROOT_PASSWORD
fi

mysqladmin -uroot $MYSQL_ROOT_PASSWORD drop $dbname
mysqladmin -uroot $MYSQL_ROOT_PASSWORD --force drop $dbname_test
mysqladmin -uroot $MYSQL_ROOT_PASSWORD create $dbname
mysqladmin -uroot $MYSQL_ROOT_PASSWORD create $dbname_test
echo "$dbname created"
echo "grant all on $dbname.* to '$dbuser'@localhost identified by '$dbpassword';" \
     | mysql -uroot $MYSQL_ROOT_PASSWORD $dbname
echo "grant all on $dbname_test.* to '$dbuser'@localhost identified by '$dbpassword';" \
     | mysql -uroot $MYSQL_ROOT_PASSWORD $dbname_test
echo "$dbuser authorized"
cat $src/???_*.sql | mysql -u$dbuser -p$dbpassword $dbname 
cat $src/???_*.sql | mysql -u$dbuser -p$dbpassword $dbname_test 
echo "schema loaded"

This handy little script will create the development and test databases, and load all sql scripts. I like to name sql scripts like 001_create_foobar_table.sql and 002_add_frobniz_column_to_foobar.sql, so that they can be loaded in sequence. It’s a simple way to develop the database schema incrementally. I may talk about it in another post.

This entry was posted on 17 October 2009 at 14:51 and is filed under Fundamentals. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.