Moving data from one database format to another with UXDM

Photo by Christopher Burns on Unsplash

This article is aimed at developers looking to migrate existing data from one database schema to another. It should be very useful if you are planning to move from one system to another, such as moving from Drupal to WordPress, or wishing to migrate external data into a new bespoke system.

UXDM (Universal Extensible Data Migrator) is an open source PHP library I created to simplify the process of moving data from any source to any destination. Installing UXDM is easy via composer, as shown below.

composer require divineomega/uxdm

The example below shows a simple migration of data between two databases.

require_once __DIR__.'/vendor/autoload.php';
use DivineOmega\uxdm\Objects\Sources\PDOSource;
use DivineOmega\uxdm\Objects\Destinations\PDODestination;
use DivineOmega\uxdm\Objects\Migrator;
$pdoSource = new PDOSource(new PDO('mysql:dbname=old-test;host=127.0.0.1', 'root', 'password123'), 'users');
$pdoDestination = new PDODestination(new PDO('mysql:dbname=new-test;host=127.0.0.1', 'root', 'password456'), 'new_users');
$migrator = new Migrator;
$migrator->setSource($pdoSource)
         ->setDestination($pdoDestination)
         ->setFieldsToMigrate(['id', 'email', 'name'])
         ->setKeyFields(['id'])
         ->setFieldMap(['name' => 'full_name'])
         ->migrate();

First, the autoload file created by composer must be included. Afterwards, we need to use various UXDM provided classes.

The next two lines create UXDM PDOSource and PDODestination objects, providing them with connection details for the source and destination databases. They also specify the source and destination tables.

The next section creates a new UXDM migrator object, and then configures it.

• The setSource and setDestination methods, as their names suggest, set where data should be sourced from and the destination where data should be migrated to.
• setFieldsToMigration allows you to choose which fields from the source data are migrated. This is optional – if you do not set the fields to migrate, UXDM will attempt to migrate all fields the source exposes.
• If you need to repeat a migration and replace any existing records in the destination, you can use setKeyFields to set one or more key fields that will uniquely identify the record. They will be used to determine if an existing record should be replaced or a new one should be inserted.
• Sometimes the destination field names may differ from the source field names. In this case, the setFieldMap method will allow you to specify how these names should be transformed. In this example, the data in the name source field will be put into the full_name field in the destination. The email field is not mapped, therefore the data in the email source field will still be put in the email destination field.

Finally, the migrate method performs the actual migration.

The following screenshots show the source and destination tables after migration.

Source table

Destination table

UXDM also provides more advanced features, including the following.

• Multiple source and destination objects, such as CSV, XML, PDO, associative array, etc.
• Transferring data to multiple destinations at once.
• Changing individual data items during migration.
• Changing entire data rows during migration (adding & removing data items).
• Skipping data row migration based on arbitrary conditions.
• Caching of source data to speed up repeated migrations.

For more information about UXDM and detailed usage instruction, check out the UXDM GitHub page. There are also various migration examples available which demonstrate some of UXDM various sources, destinations and other features.