Why database migrations indicate the importance of quality for a programmer

Photo by Crawford Jolly on Unsplash

Assume there is a new feature you are about to develop for your project or your customer’s wish. There are plenty of different ways how to deploy your code, how to handle several versions or to keep quality by using code analysis tools, for example. Deployment pipelines (CI/CD) allow you to take your brand new feature live. And in case of emergency they will easily let you rollback to previous state. But what if your feature requires changes to your database’s schema or data?

This is why “migrations” exist. Migrations are a concept or even more an additional functionality put on top of the database abstraction layer (DBAL) and object-relational mapping (ORM). It allows you to implement a some kinda “versioning” for your database schema and even your data itself.

And I’d go even further: Because migrations allow you to generate so-called “migration classes” containing a bunch of database statements, any planned changes to your database schema can be reviewed by your quality assurance team or your colleagues in advance.

No sensational novelty! You may have read about migrations, and you may have come into contact with migrations. In PHP and Doctrine context the doctrine/migrations package already exists since the mid of 2015.

doctrine/migrations

But why are migrations more than just a version control method of your database schema?

Let me quote from the introduction of the doctrine/migrations project documentation. Their definition of the projects functionality says:

…It makes it easy and safe to deploy changes to it in a way that can be reviewed and tested before being deployed to production…

This often overlooked subordinate clause is actually one of the most important aspects. Migration classes consist of two dedicated methods, up() and down(). They contain statements to upgrade your database schema or to downgrade it, e.g. in case of a rollback. Rollbacks do not necessarily have to be emergency measures. Of course, a rollback can be done for several other reasons. But if it really is an emergency, has the feature been adequately tested before it was released? Well?

I’m sorry, but it’s so important that I really need to do it again…

Migrations let you test your changes before they are being deployed

First, let’s take a step back. A programmers main motivation for using migrations is to upgrade the database schema. Thus, the doctrine/migrations package allows to generate both, a blank migrations class where to write database statements manually or a migrations class containing automatically detected schema diff by comparing against your entities metadata.

Let’s consider the first case because it implies some extra work for a programmer. As you can see below, the console command

// generate a blank migrations class
$ ./vendor/bin/doctrine-migrations generate

will generate an almost blank migrations class looking pretty much like this:

// ./src/App/Migrations/Version20191028222453.php
<?php
declare(strict_types=1);  
namespace App\Migrations;  
use Doctrine\DBAL\Schema\Schema; 
use Doctrine\Migrations\AbstractMigration;  
/**  
* Auto-generated Migration: Please modify to your needs!  
*/ 
final class Version20191028222453 extends AbstractMigration { 
    public function up(Schema $schema): void
    {         
        // this up() migration is auto-generated, 
        // please modify it to your needs 
    }
    public function down(Schema $schema): void     
    {         
        // this down() migration is auto-generated,   
        // please modify it to your needs         
    }
}

Okay, what’s next? Well, first we will have a look at the up() method. Let’s assume we need to add a new column to one of our tables. In that case our code for example might look as follows:

...
public function up(Schema $schema): void
{         
    $platform = $this->connection->getDatabasePlatform()->getName();
    $this->abortIf(
        $platform !== 'mysql', 
        'Migration can only be executed safely on mysql'
    );
    $this->addSql('ALTER TABLE users ADD active TINYINT(1) NOT NULL');
}
...

If we now execute this migration there should be a new column “active” in our previously existing table “users”.

$ ./vendor/bin/doctrine-migrations execute 20191028222453 --up
WARNING! You are about to execute a database migration that could result in schema changes and data lost. Are you sure you wish to continue? (y/n)y 
  ++ migrating 20191028222453 
  
      -> ALTER TABLE users ADD active TINYINT(1) NOT NULL 
  ++ migrated (took 42.6ms, used 8M memory)

So far, so good. As you can see, pretty simple stuff. It’s just about reading the documentation and then… straight forward. I fully understand, “straight forward” leads to hurry. One change after another, POW! But have you ever wondered if your database statements are working properly? Have you ever tried them twice to see if everything is alright? Yes? No? Maybe?

Right now, the down() method enters the stage! In it, we need to write one or many database statement(s) to undo the changes made during the up() method. So if you undo and redo your migration, the result should be the same. Therefor, the up() and down() functions must be implemented idempotent (repeatable). So here we go:

...
public function down(Schema $schema): void
{         
    $platform = $this->connection->getDatabasePlatform()->getName();
    $this->abortIf(
        $platform !== 'mysql', 
        'Migration can only be executed safely on mysql'
    );
    $this->addSql('ALTER TABLE users DROP active');
}
...

Executing the migrations with the “--down” option let’s us undo the previously made changes to our database table’s structure:

$ ./vendor/bin/doctrine-migrations execute 20191028222453 --down
WARNING! You are about to execute a database migration that could result in schema changes and data lost. Are you sure you wish to continue? (y/n)y
++ migrating 20191028222453 
  
      -> ALTER TABLE users DROP active
++ migrated (took 38.7ms, used 8M memory)

Finally we get into the funny part! Up! Down! Up! Down! Up! And down again! One more time? ;-) It’s better to be safe than sorry! If you are able to do that at least twice to end up with the same result, your code is ready to deploy! Awesome!

As you see, it does not take much to improve the quality of your project. Just invest a couple of extra minutes to implement that god damn down() method. ’cause if you really think it is never needed you’re totally wrong!

Resume

Finally, let’s put all things together! I summarize — migrations provide a versioning to your database schema and data. Migrations give you and your team the ability to review the planned changes first before deployment. And migrations let you double check your database statements using the up() and down() methods to test them the “do, undo and redo”-way.

If you care about quality, don’t forget implementing the down() method. If you care about your very own comfort whilst avoiding issues right before they occur, don’t forget implementing the down() method. And if you care about reducing emergency moments in your software projects, do not let yourself, your colleagues or your team forget about implementing the down() method.

The effort is manageable, but the meaning clear — no down() method means “I don’t really care”!

More from me — If you like, please check it out:

Being a better programmer than this morning — some aspects to focus on

Be less rude — Code quality is a matter of courtesy