Migrating data from a SQL source

This documentation page shows an example how Drupal 8 nodes can be migrated from an external SQL database. The example can be easily modified for migrating any Drupal configuration or content entity.

Overview of this example

We have manually created a content type Game where we want to migrate data from an external SQL database. For the sake of simplicity, our content type has only two fields in this example.

Pre-requisites

In order to execute the migration, you will need:

• Drush and command line access to the server.
• Drupal 8 core Migrate module.
• Migrate Tools contributed module. 
• Migrate Plus contributed module.

Custom SQL string migrate source plugin

See Custom SQL Migrate Source Plugin .

Writing a custom source plugin

The heart and soul of this example is the custom source plugin shown below.

• The source plugin, Games.php in this example, must be saved in src/Plugin/migrate/source subdirectory of your custom module.
• Pay attention to the namespace. In this example the custom module is called ''mymodule' so the namespace is Drupal\mymodule\Plugin\migrate\source.
• Our migrate source plugin class extends the abstract SqlBase class provided by the core Migrate module. 
• The source plugin has @MigrateSource annotation with id 'games'. This ID will be used in the actual migration definition below.

The SQL source plugin must implement three methods:

• query(): Returns the query that selects the data from the source database.
• fields(): Returns available fields on the source. 
• getIds(): Defines the source fields uniquely identifying a source row.

Our example plugin also implements prepareRow() to demonstrate how source properties can be added in the source plugin. Refer to SqlBase API documentation for further details of the methods.

<?php

namespace Drupal\mymodule\Plugin\migrate\source;

use Drupal\migrate\Annotation\MigrateSource;
use Drupal\migrate\Plugin\migrate\source\SqlBase;
use Drupal\migrate\Row;

/**
 * Minimalistic example for a SqlBase source plugin.
 *
 * @MigrateSource(
 *   id = "games",
 *   source_module = "mymodule",
 * )
 */
class Games extends SqlBase {

  /**
   * {@inheritdoc}
   */
  public function query() {
    // Source data is queried from 'curling_games' table.
    $query = $this->select('curling_games', 'g')
      ->fields('g', [
          'game_id',
          'title',
          'date',
          'time',
          'place',
        ]);
    return $query;
  }

  /**
   * {@inheritdoc}
   */
  public function fields() {
    $fields = [
      'game_id' => $this->t('game_id' ),
      'title'   => $this->t('title' ),
      'date'    => $this->t('date'),
      'time'    => $this->t('time'),
      'place'   => $this->t('place' ),
    ];
    return $fields;
  }

  /**
   * {@inheritdoc}
   */
  public function getIds() {
    return [
      'game_id' => [
        'type' => 'integer',
        'alias' => 'g',
      ],
    ];
  }

  /**
   * {@inheritdoc}
   */
  public function prepareRow(Row $row) {
    // This example shows how source properties can be added in
    // prepareRow(). The source dates are stored as 2017-12-17
    // and times as 16:00. Drupal 8 saves date and time fields
    // in ISO8601 format 2017-01-15T16:00:00 on UTC.
    // We concatenate source date and time and add the seconds.
    // The same result could also be achieved using the 'concat'
    // and 'format_date' process plugins in the migration
    // definition.
    $date = $row->getSourceProperty('date');
    $time = $row->getSourceProperty('time');
    $datetime = $date . 'T' . $time . ':00';
    $row->setSourceProperty('datetime', $datetime);
    return parent::prepareRow($row);
  }
}

Defining the source database connection

Drupal 8 Database API allows us to define multiple database connections.

• The database connections are defined in settings.php or settings.local.php.
• The 'default' database connection is our Drupal 8 database.
• Let's define a second database connection with the key 'migrate' in addition to the 'default' database connection in our settings.php. If your database uses table prefixes, add 'prefix' key to the arrays.

$databases['default']['default'] = array (
  'database'  => 'drupal8-database-name',
  'username'  => 'drupal8-database-username',
  'password'  => 'drupal8-database-password',
  'host'      => 'drupal8-database-server',
  'port'      => '3306',
  'namespace' => 'Drupal\\Core\\Database\\Driver\\mysql',
  'driver'    => 'mysql',
);
$databases['migrate']['default'] = array (
  'database'  => 'source-database-name',
  'username'  => 'source-database-username',
  'password'  => 'source-database-password',
  'host'      => 'source-database-server',
  'port'      => '3306',
  'namespace' => 'Drupal\\Core\\Database\\Driver\\mysql',
  'driver'    => 'mysql',
);

Using the custom source plugin in the migration definition

The games can be migrated from the source database to Drupal 8 with the following migration:

id: games
label: 'Games'
source:
  plugin: games
  key: migrate
process:
  title: title
  field_datetime: datetime
  field_place: place
destination:
  plugin: 'entity:node'
  default_bundle: game

Let's have a closer look of the migration YAML.

• The ID of the migration is 'games' and the label of the migration is 'Games'.
• We are using source plugin 'games'.
  • This is the @MigrateSource ID of our custom source plugin described above.
  • The 'key' configuration option defines the source database connection which must be defined in settings.php or settings.local.php. The 'migrate' connection is defaulted for all source plugins that extend SqlBase unless the source plugin explicitly defines otherwise. We define 'migrate' explicitly here for the sake of clarity. 
• In the process phase we map only three fields in this minimalistic example:
  • Drupal 8 node title is mapped 1:1 from the source 'title' property.
  • Drupal 8 'field_place' is mapped from the source 'place' property.
  • Drupal 8 'field_datetime' is mapped from the source 'datetime' property. This property is defined in the prepareRow() method of our source plugin.
• The destination entity in Drupal 8 is 'node' of content type 'game'.

Executing the migration

Importing the migration definition

The contributed Migrate Plus module allows migration plugins to be implemented as configuration entities, allowing them to flexibly be loaded, modified, and saved. Refer to the Executing migrations documentation page on how to import the YAML format migration definition.

Checking the status of the migration, executing the migration, rollbacks

The contributed Migrate Tools module provides the Drush commands for checking the status of a migration, executing the migration and doing a rollback. Refer to the Executing migrations documentation page on detailed instructions.

If you don't see the migration when executing drush migrate-status, verify that:

• you have enabled your custom module which provides the custom source plugin
• you have your source plugin in src/Plugin/migrate/source directory
• your migration definition was imported as configuration
• you have defined the source database connection in your settings.php or settings.local.php and that the database connection parameters are correct.
• the 'key' of your migration configuration source plugin matches to the database connection key defined in settings.php.