Executing database migrations at scale with Symfony and Doctrine

There are not many programming languages which don’t have at least one way to access a database. In a few decades, databases, and more precisely relational databases, have become the standard way to store software data in an organized, accessible and safe way. While the recent introduction of NoSQL platforms like MongoDB or Elasticsearch is changing the way databases are used, relational databases are still the norm and the most used databases around the world.

Databases are a great way to handle many difficult aspects of storing data: how to allow multiple programs to access and write the same data at the same time, how to store data efficiently, how to ensure validity of the data, how to secure access to the data, etc. There is one thing, however, that databases can’t automatically solve: how to migrate data from a database structure to another. This is where database migrations are useful.

Doctrine Migrations

Database migrations are a list of instructions (usually SQL queries) stored with the code of your application (for instance, in the same Git repository). These instructions are ran when the code is deployed, in order to adapt the database structure to the code currently in production.

These migrations are essential to control the way your database is structured and how you want to migrate data between the different versions of your application. Using migrations is necessary when your application begin to scale.

If you are using Symfony with Doctrine ORM, you may currently use the command :

bin/console doctrine:schema:update --force

to update your database. While this may work, you don’t have any control on what this command does: for instance, if you renamed a field in an entity of your application, this command won’t move data between the fields but remove the first and create a new one, loosing production data!

Fortunately, you can use migrations instead, for example with the DoctrineMigrationsBundle.

This bundle will let you generate diffs between your current database and the structure you have in your entities. For instance, by adding a picture_filename field in your Project entity and running the command :

bin/console doctrine:migrations:diff

the bundle would automatically generate something like:

<?php

namespace DoctrineMigrations;

use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;

class Version20180510051757 extends AbstractMigration
{
    public function up(Schema $schema)
    {
        $this->addSql('ALTER TABLE projects ADD picture_filename VARCHAR(70) DEFAULT NULL');
    }

    public function down(Schema $schema)
    {
        $this->addSql('ALTER TABLE projects DROP picture_filename');
    }
}

This generated code is yours: you can adapt the instructions used to change your database! Once edited as you need, you can safely run the command:

bin/console doctrine:migrations:migrate

in production to run the migrations.

Internally, the DoctrineMigrationsBundle uses a table to store the list of all the migrations that were executed. This means that you can run this command multiple times without it failing.

The problem of database migrations for deployment

As you may understand, migrations are extremely important to be able to safely change your database structure without loosing production data.

However, there is still an issue: when you deploy a new version of your code, if there are migrations to execute, it usually means the old version of your code is not able to interact with the database anymore as it only knows about the old structure.

Moreover, if you have multiple instances of your application to be able to handle more users at the same time, you may have different versions of your code running at the same time during deployments.

This means that at some point, to avoid errors in production, you need to stop the old version of your code to interact with your database, in order for you to apply the migrations and only then enable the new version of your code.

This is why it is not possible to have real “zero-downtime deployment” when you have SQL migrations to run: you have to stop your code to interact with the database for at least a bit of time. It is also the reason why you need to create a “maintenance mode” in order to tell your users to come back later during this time.

Using Symfony Cache to create a “maintenance mode”

If you work on a small project, hosted on a single server, it is perfectly fine to create a “manual” maintenance mode, in which you manually enable and disable this mode using, for instance, a file you read on each request.

However, when your application starts to grow and you have multiple instances handling numerous requests per second, it is important to create a maintenance mode that is synchronized between all the instances and that is fast to check, to avoid as much as possible any overhead.

While we could use the Symfony Lock component for this feature, there is another major component that is almost always available in our application and that needs to be as fast as possible and synchronized between instances: application cache.

Since Symfony 3, Symfony splits the cache in two main parts: system cache and application cache.

System cache is the cache generated by the framework, that is going to be read-only in production. It is generated on the filesystem and not shared between multiple instance, because it is read-only and only needs to be generated once. It is configured automatically by Symfony.

Application cache is the cache your application will use at runtime: all the things you may want to put in cache to avoid having to compute them multiple times over time. Application cache needs to be synchronized between instances, and rely on different backends to do that (like Redis). It needs to be configured by you.

For instance, to configure the application cache to use Redis in your application, you can use the following configuration:

framework:
    cache:
        prefix_seed: your/app
        app: cache.adapter.redis
        default_redis_provider: 'redis://%env(REDIS_HOST)%'

Once configured, we get a cache service that is synchronized between all our instances using Redis. We can rely on it to create a maintenance mode using a Listener:

<?php

namespace App\Listener;

use Psr\SimpleCache\CacheInterface;
use Symfony\Component\EventDispatcher\EventSubscriberInterface;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\HttpKernel\Event\GetResponseEvent;
use Symfony\Component\HttpKernel\KernelEvents;
use Twig\Environment;

class MaintenanceListener implements EventSubscriberInterface
{
    private $cache;
    private $twig;

    public function __construct(CacheInterface $c, Environment $t)
    {
        $this->cache = $c;
        $this->twig = $t;
    }

    public static function getSubscribedEvents(): array
    {
        return [
            KernelEvents::REQUEST => [
                'enableMaintenanceOnRequest',
                1000000 // Always execute the listener as first
            ],
        ];
    }

    public function enableMaintenanceOnRequest(GetResponseEvent $e)
    {
        if (!$this->cache->get('maintenance')) {
            return;
        }

        $e->setResponse(new Response(
            $this->twig->render('maintenance.html.twig'),
            Response::HTTP_SERVICE_UNAVAILABLE
        ));
    }
}

Now, if we create a non-false item in our cache called maintenance, all our application instances will display a maintenance page (the maintenance.html.twig template).

Finally, we probably want to limit as much as possible the maintenance time by automatically enabling maintenance, running the migrations and then disabling maintenance. I personally like to use a command to do this:

<?php

namespace App\Command;

use Doctrine\Bundle\MigrationsBundle\Command\DoctrineCommand;
use Doctrine\Bundle\MigrationsBundle\Command\Helper\DoctrineCommandHelper;
use Psr\SimpleCache\CacheInterface;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Output\OutputInterface;
use Doctrine\DBAL\Migrations\Tools\Console\Command\MigrateCommand;
use Symfony\Component\Console\Style\SymfonyStyle;

class MigrateDatabaseCommand extends MigrateCommand
{
    protected static $defaultName = 'app:db:migrate';

    private $cache;

    public function __construct(CacheInterface $cache)
    {
        parent::__construct();

        $this->cache = $cache;
    }

    protected function configure()
    {
        parent::configure();

        $this
            ->setDescription('Check if a database migration is needed and enable maintenance mode to execute it if there is.')
            ->addOption('db', null, InputOption::VALUE_REQUIRED, 'The database connection to use for this command.')
            ->addOption('em', null, InputOption::VALUE_REQUIRED, 'The entity manager to use for this command.')
            ->addOption('shard', null, InputOption::VALUE_REQUIRED, 'The shard connection to use for this command.')
        ;
    }

    public function execute(InputInterface $input, OutputInterface $output)
    {
        $io = new SymfonyStyle($input, $output);

        if ($this->cache->get('migrating')) {
            $io->success('Another instance locked the migration');

            return;
        }

        // Take the responsibility to execute the migration
        $io->text('Locking');
        $this->cache->set('migrating', true, 60);
        $io->text('Lock obtained');

        // Check whether there are migrations to execute or not
        $io->text('Loading migrations');

        $app = $this->getApplication();
        DoctrineCommandHelper::setApplicationHelper($app, $input);

        DoctrineCommand::configureMigrations(
            $app->getKernel()->getContainer(),
            $this->getMigrationConfiguration($input, $output)
        );

        $configuration = $this->getMigrationConfiguration($input, $output);
        $toExecute = array_diff(
            $configuration->getAvailableVersions(),
            $configuration->getMigratedVersions()
        );

        if (!$toExecute) {
            // No migration to execute: do not enable maintenance
            $io->success('No migration to execute');

            $io->text('Releasing lock');
            $this->cache->delete('migrating');
            $io->text('Lock released');

            return;
        }

        // Migrations to execute: enable maintenance and run them
        $io->text('Migration(s) to execute: '.implode(', ', $toExecute));

        $io->text('Enabling maintenance mode');
        $this->cache->set('maintenance', true, 60);
        $io->text('Maintenance enabled');

        $io->text("Executing the migration(s)\n");

        // Enable full output and disable the migration question
        $output->setVerbosity(OutputInterface::VERBOSITY_DEBUG);
        $input->setOption('no-interaction', true);

        parent::execute($input, $output);

        $output->write("\n");
        $io->text('Migration(s) executed');

        $io->text('Disabling maintenance mode');
        $this->cache->delete('maintenance');
        $io->text('Maintenance disabled');

        $io->text('Releasing lock');
        $this->cache->delete('migrating');
        $io->success('Lock released');
    }
}

The code relies on DoctrineMigrationsBundle to actually run the migrations.

Note the introduction of a “migrating” item in the cache, with an expiration of 60 seconds. I use this item to avoid having multiple instances to run the same migrations at the same time.

I personally run this command in every instance of my application, after each deployment: because of the lock system and the migration mode developed, I am certain that no user will ever get an error from an old database structure!