“One does not simply update a database” – migration based database development


I already described in a previous post why databases are different to applications from a development perspective. The most impactful difference in my opinion is, that you can deploy applications by state. You pick a certain point in your version control system, build your application and have a certain state of your application at hand.

Best case, this state is backed by a number of self-tests and has a unique version number (well, it has one by the hash of your specific commit). You take that build and can deploy it, no matter if your customer had a different version (state) of your application installed or not.

Of course this is simplified, there might be external APIs, libraries, configuration files or – who had thought – databases which prevent you from easily switching application states, but normally such constraints are relatively few.

Databases are different. You can’t preserve state in a database once it’s shipped to a customer. The moment you give it out of your hands, a database starts to change its state continuously. You cannot simply “install” a new version of your database schema on a customer database – there’s data to be preserved and in most cases it’s not even possible to clearly separate schema and data.
And yes, data changes for applications, too, but in my experience database schema is usually much tighter coupled to the data than applications are (which makes sense, doesn’t it?).

So, how do we overcome these difficulties, especially if we are aiming towards frequent releases and agile, collaborative development? Continue reading

There is no clean (database) development without Version Control

I was surprised when I did a bit of research for this current blog article and learned that SCCS, the first system for Version Control, was invented in 1972. That was 45 years ago. I wasn’t even born.

But even today, after such a long time, there are projects – especially database projects – which are not under version control. I myself remember several projects I worked on (or even was responsible for, shame on me) which were not under Version Control at all or at least the database part wasn’t. So why is this and what can we do against it?

Continue reading

DRY SQL – Writing simple, maintainable queries

One of the strongest recommendations for clean and pragmatic development is the DRY-principle:

Don’t Repeat Yourself!

This principle is not only part of every guide about professional development but also the reason why we even have things like subroutines, config files and  – hello, database professional – data normalization.

Although normalization should be a well-known practice for database professionals, I caught myself writing the same SQL statements again and again, copy-pasting parts as sub-selects, use it in procedures and functions and ending up at having the same query logic in many different places.

We database people are very strict about redundant data in our database design – why aren’t we as strict about the code we write?

Continue reading