Ferris sent me a message a little while back and asked if I had any input on handling databases, so it gave me a reason to sit down and try to write something about my experiences. I’m not too happy about the flow of these articles, but I figured it was better to get something out there. Just let me know if something should be changed. Here we go…
Changes?
I see a lot of different approaches when it comes to handling databases. Sadly most of them are on an ad-hoc basis, and something that should be something of the past. With the introduction of agile, focus has been shifted from preventing change to embracing change. And we’re pretty pretty happy to change the code and do refactorings, but when a change affects our database we tend to think it’s quite scary. We need to find ways to do this better, and it is possible.
By implementing the practices mentioned below the projects I have been in are able to perform upgrades to our different environments (testing, pre-production and production) in a consistent way quickly. And I am fairly confident that the scripts have been thoroughly tested and that the chances for human error is drastically reduced. We can create new environments for testing with a new customer in minutes and fill the environment with test data. Because of this we can focus our time and effort on developing new features instead of wasting time on upgrades and errors that can be avoided.
Most of this is based on the Evolutionary Database Design from Martin Fowler and Datbase Refactorings by Scott Ambler.
I will try to describe more details with separate articles, but for now check out the migrations in Java which is the essence of what I think is necessary.
Your database is code!
If you don’t have schema specifications, triggers, test data etc. checked into version control. You need to start doing it right now. When you tag, release or branch your “normal” source code you do the same with your database scripts. For Java developers using Maven I would recommend keeping it as a sub project in your maven project structure. That will make handling of tags/releases automatic.
Make it easy
Even though SQL, PL/SQL or whatever your poison is looks quite strange and awkward to many developers, you can script the things you need to do. For Oracle, sqlplus actually works for some basic scripting. If you don’t know how to; learn it.
These are the tools/scripts I think every developer should have as a minimum:
- Create a new database from scratch
- Clean out the data that changes during use of your application
- Populate test data in a clean database
- Copy data between two databases
- A tool for handling migrations
Making these things easy isn’t just because your developers do it all the time. It is also because having these tools will enable them to do it when it’s needed. There are a lot of good things in every project that simply is not done because it is too difficult or takes too much time. At least let us give the developers the tools we can, and remove that kind of mental blocks.
One of the most important parts of this is the migrations. Next: Migrations for Java.