Specbee: How to Migrate to Drupal 8 from a SQL source in 6 Simple Steps

How to Migrate to Drupal 8 from a SQL source in 6 Simple Steps
Akshay Devadiga
24 Nov, 2020

Much has been spoken about the importance and benefits of migrating to Drupal 8. Drupal migration is the most significant part of a development workflow. We need to ensure that content gets transferred seamlessly without losing out on any critical user information and data. Check this complete guide for a successful Drupal 7 to Drupal 8 migration.  

There are several ways to migrate to Drupal 8 using various sources. We have already written about how to migrate from CSV to Drupal 8. Other sources include SQL, XML, JSON, etc. In this blog, we will be discussing about migrating to Drupal 8 using SQL as a source.
Migratiom

Why SQL?

While choosing the data source largely depends on the source of the existing data required to be migrated, some of the other common reasons for choosing SQL source for a Drupal 8 migration are:  

  • It is easy to write queries to get the required data by connecting to the database.
  • Migrating the data from one server to another will be quicker than any other method.
  • Reduces the usage of many contributed modules.
  • No need of views data export which is used for exporting views CSV data from Drupal 7 sites.
  • We will not need the Views password field which is used for migrating sensitive information (passwords in hash), since we are using DB query.
  • Migrate source CSV module is also not needed since we are using SQL source.

Let the Migration Process begin!

In this blog, we will be migrating users to a Drupal 8 site. Following are the steps we will take to migrate to Drupal 8
1.    Create a custom module for the migration.
2.    Reference the source database.
3.    Define the migration YML and map the identified Drupal fields.
4.    Create a source plugin for migration.
5.    Process single value, multi-value and address fields.
6.    Run migration using the drush command line tool.

Step 1: Create a custom module for the Drupal 8 migration.

First, let’s create a custom module as you create in Drupal 8. Check this detailed blog for creating custom modules in Drupal 8. Here we are creating a module called “company employee migrate”. The module structure is as follows:
custom modulecompany_employee_migrate.info.yml : Consists of basic information regarding the module and its dependencies.

company_employee_migrate.install : This will be used for writing PHP scripts that should trigger while installing and uninstalling the module. In our case, we are deleting the migration configuration when the module is uninstalled. See the below screenshot for the script.
Source databasecompany_employee_migrate.module : This will be used for defining the general hooks for the site. These are the initial things needed for the module. We will be explaining the remaining in the next steps.

Step 2: Reference the source database 

Next, we need to set up the source database from where we are extracting the data. Update the settings.php file for your Drupal site by moving to webroot -> sites -> default – > settings.php

Add the new database connection below the default connection as shown in the below screenshot. “migrate” is key for the source database. 
Drupal Module

Step 3: Define the migration YML and map the Drupal fields.

Now, we need to identify the fields which we want to migrate and map those fields in the migration yml. In this example we are migrating User Id, User Name, Password, Email, Status, Created Timestamp, First Name, Last Name, Job Role, Mailing Address etc.

After the identification of fields, we need to define the migration at migrate_plus.migration.company_employee.yml. Now let’s have a closer look at migration yml and their mappings.
 
Migration YML and Mapid: Unique id for the migration yml.

label: Display name for the migration.

migration_group: Migration group name.

source:  Annotation Name of the migration source plugin. This will be defined in the @MigrateSource annotation in src/Plugin/migrate/source/CompanyEmployee.php.

destination: Name of migration destination plugin. In this case, it’s entity:user since we are migrating user entity.

process: In process, we will be mapping the Drupal fields with the source fields. The left-side values are field machine names and the right-side values are the field names which we pass from the source plugin.

migration_dependencies: This is an optional key. This will be used only if there is any dependency on other migrations.

Step 4: Create a source plugin for migration

Migration source plugin is the heart of the SQL migration. Below is the detailed explanation for the source plugin. 

  • The source plugin can be created at src/Plugin/migrate/source/CompanyEmployee.php 
  • The namespace will be Drupalcompany_employee_migratePluginmigratesource.
  • The @MigrateSource annotations include the source plugin id that we map in the migration definition.
  • Here we are extending the Abstract SqlBase class provided by the core migrate module.

See the below screenshot for reference.
source plugin for migrationThe source plugin must implement the following three methods –

query() : This will return the source fields data by connecting to the source database. See the below screenshot which will return the field data where the user id is greater than 0 and user mail is ending with “@phyins.com”.
source plugin for migration
fields() : This method will return the available fields in the source. Below, I have added the code snippet for the list of available fields along with the baseFields().
basefieldsgetIds() : This method will return a unique ID for the source row. The below code will return a user ID of type integer since uid will be unique for each user.
 
Drupal ModuleApart from these above-mentioned methods we also have:

prepareRow() : This method will be called once for each row. We are loading the data here from different tables and processes according to the requirement. Any property we create using $row->setSourceProperty will be available in the process step. We will be using the Drupal 8 DB query to get the data in prepareRow().

baseFields() : This will contain an array of basic fields from “users_field_data” that can be used by the query() method. Find the code in the below image.
drupal 8 migrate 

Step 5: Process single value, multi-value and address fields.

In Drupal, we have different types of fields and processing some fields can get a little tricky for developers while migrating content. I have added the code snippets for some fields below:

Single value fields : These fields include text fields, boolean fields, email, etc. Find the code snippet below for getting a single value field; here the field_first_name can be set as below.
Process single value
Multi Value fields: These fields include user roles, job roles, multi value select fields, checkboxes, etc. For a multi value field we need to return an associative array. Find below the code snippet for the “roles” field.
Migration
Address Fields: Migrating the address field provided by the address module will be little different from migrating other fields. We need to preprocess the queried data into an associative array with the proper keys as shown below for field_mailing_address.
Migration
Now all set to run the migration by installing the company_employee_migrate module in the Drupal 8 site.

Step 6: Run the migration using drush command line tool

Finally, we are ready for our SQL migration. To run the migration, we need to install drush (if you are not already using it).

List of useful drush commands for Drupal migration:

$drush migrate-status : This will return the status of migration with details.
migrtion using drush$drush migrate-import migration_id : This will import the data from source to the site.
drush migration$drush migrate-reset-status migration_id : If the execution of the script has stopped or paused, the migration status will display as “Importing”. This command will reset the migration status to “Idle” so that we can proceed with the migration import.
drush migration$drush migrate-rollback migration_id : This will be used for rolling back to its previous state.
drush migration

Shefali ShettyApr 05, 2017