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
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.