How to version control database configuration

Working as a Drupal developer for many years, I learned a few good ways to manage development workflow well and achieve perfect release management process.

Working mainly with Drupal and relying on Git for development, I always recommend other developers – that are not familiar with Git – to start using it. Git is not a rocket science and it offers many benefits, especially when it comes to complex projects with many developers working on it in parallel.

Several times I was asked how to version control a database. All modern CMS websites have their configuration split between the code and the database, it may become critical to version control the database-based configuration. The main reason is that other developers could pick up configuration changes easily during their daily development routine.

How to version control a database? The short answer is “No, you don’t!”.

Instead, you make the configuration changes to the database via code and then you version control the code.

In this article I will share my experience in managing several Drupal 7 and Drupal 8projects, where my team was sharing the database configuration via Git. Such approach allows for smooth configuration sharing in the team and avoid configuration conflicts.

What part of database could be versioned

The steps outlined here work for configuration management and don’t apply to content. In Drupal 7 we use the Features module. In Drupal 8 we use the native configuration management to export and import.

Versioning workflow

It is important to note that the idea of sharing database configuration requires a reliable versioning workflow. I use Git Flow across all projects I manage, it is important that all developers in your team follow the same versioning workflow.

Helper functions

To avoid writing a lot of new code for your configuration changes, I suggest you get few helper or wrapper functions. Here is a good example of helper functions for Drupal 7 in GitHub that anyone could start using: https://github.com/salsadigitalauorg/salsa_helpers

It allows to easily revert a feature or create a block.

Drupal Features module

Most of database configuration changes could (and should) be exported into features by using the excellent Features module. However having your database configuration exported does not ensure that all developers receive the changes. It is necessary to add a hook_update_N() so that the changes received via new or updated feature apply.

Hint: ask your developers to run updates every time they merge any new code into their feature branch. drush updb is the secret!

Hook Update example: Revert Features

In a custom module create an update hook function and place the code (update it for your needs):

<?php

/**
 * Reverts 'blog' feature to create new fields [JIRA-1234].
 */
function mymodule_update_7001() {
  salsa_helpers_features_revert('blog');
  salsa_helpers_features_revert('events');
}

The logic of database versioning

Let’s assume we have started a Drupal project. Once your team begins working on stories, developers begin making code and database changes. Based on your release type, the flow will be different.

Let’s review two release types:

1. Planned release. Typically fortnightly or monthly.
2. Hotfix release. A single fix that it applied and deployed to resolve critical errors.

Planned release flow

Step 1. Getting the Release Database snapshot

The Tech Lead or Release Manager prepares a fresh copy of the latest database (typically from QA environment). This database will be used by all developers during the release. Let’s call it “release database”, so that we could refer to it later.

When release starts, a new snapshot of the database is taken. This snapshot replaces the “release database” from any previous release. It is made available to the team.

Step 2. Preparing development environment

  2.1 Developer picks up a story from current release, creates new feature branch in Git: git flow feature start <name>

2.2 Importing the “release database” snapshot: drush @myproject.local sql-drop && drush sql-sync @myproject.dev @myproject.local

Alternatively import the database using your favourite MySQL client

  2.3 Developer runs database update script, either via drush drush updb -y

or by visiting updates.php file in browser.

At this point the database configuration matches the latest available in the develop branch.

Step 3. Completing a story

Developer works on a story and exports any configuration changes into a new or an existing feature.  Then the code is committed and pushed for QA as usual.

It is important that developer provides a hook_update_N() to apply the configuration (revert features or apply modifications via code).

As the story passes the QA and the feature branch is getting merged into the develop branch, it becomes available to the team.

Hotfix release flow

Step 1. Preparing development environment

The developer takes a copy of the production database and creates a hotfix branch off the master.

Step 2. Exporting any configuration changes

It is possible that production database has configuration modifications, made by the end client. For this reason we should inspect any enabled Features to see if they are in Overridden or Review Required state – in which case the developer has to review overrides or conflicts and export them into code.

I use `drush fl` to inspect enabled features.

drush fl | grep Enabled

Any Features that are Overridden, need to be exported into code. To export configuration changes into existing features, execute

drush fu [feature_name]

Commit the changes

Step 3. Working on the hotfix

The developer begins the story and exports any configuration changes into an existing or a new Feature.

Step 4. Preparing deployment automation

Developer writes a hook_update_N() in order to automatically revert any features. See the example of using hook_update above.

Step 5. Finalising the story

Developer commits the  code and pushes it for code review and QA.

At this point, should the story be approved by QA manager, it then merges into the master  and develop branches, ready to be released. Other developers pull the updated code and execute database update to get their database copies updated to match the production environment.

Hint! Make it the standard step to run any database updates (or script it to achieve 100% automation) immediately after deploying of the the hotfix to production.

Developer begins working on a ticket and imports the

When developer prepares a story for QA, one exports all configuration changes into a feature and writes an update hook.

When Features can’t manage it

Any configuration changes that cannot be exported to a feature, have to be applied via an update hook. See a few handy examples below.

Examples of using hook_update_N

These are a few examples of using hook_update_N to make configuration changes in Drupal 7. In Drupal 8 use the core-bundled configuration management, which exports the entire configuration of the site into code.

/**
 * Updates menu block position for the 'govcms_ui_kit' theme.
 */
function mymodule_update_7001() {
  require_once DRUPAL_ROOT . "/profiles/govcms/modules/custom/govcms_core/govcms_core.module";
  require_once DRUPAL_ROOT . "/profiles/govcms/govcms.install";

  salsa_helpers_add_block_to_region('menu_block', 'govcms_menu_block-sidebar', 'first_sidebar', -50, 'govcms_ui_kit');
}

/**
 * Setting menu block title to 'In this section' for the 'govcms_ui_kit' theme.
 */
function mymodule_update_7002() {
  db_update('block')
    ->fields(array('title' => t('In this section'), 'weight' => '-49'))
    ->condition('delta', 'govcms_menu_block-sidebar', '=')
    ->condition('theme', 'govcms_ui_kit', '=')
    ->execute();
}

/**
 * Helper to change the max length of a text field.
 *
 * @param string $field_name
 *   The name of the field being modified.
 * @param int $new_length
 *   The new field length.
 */
function megt_entities_change_text_field_max_length($field_name, $new_length) {
  $field_table = 'field_data_' . $field_name;
  $field_revision_table = 'field_revision_' . $field_name;
  $field_column = $field_name . '_value';

  // Alter value field length in fields table
  db_query("ALTER TABLE `{$field_table}` CHANGE `{$field_column}` `{$field_column}` VARCHAR( {$new_length} )");
  // Alter value field length in fields revision table
  db_query("ALTER TABLE `{$field_revision_table}` CHANGE `{$field_column}` `{$field_column}` VARCHAR( {$new_length} )");

  // Update field config with new max length
  $result = db_query("SELECT CAST(`data` AS CHAR(10000) CHARACTER SET utf8) FROM `field_config` WHERE field_name = '{$field_name}'");
  $config = $result->fetchField();
  $config_array = unserialize($config);
  $config_array['settings']['max_length'] = $new_length;
  $config = serialize($config_array);
  db_update('field_config')
    ->fields(array('data' => $config))
    ->condition('field_name', $field_name)
    ->execute();
}