Moving data from one database format to another with UXDM
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.
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;
->setFieldsToMigrate(['id', 'email', 'name'])
->setFieldMap(['name' => 'full_name'])
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
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.
setDestinationmethods, as their names suggest, set where data should be sourced from and the destination where data should be migrated to.
setFieldsToMigrationallows 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
setKeyFieldsto 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
setFieldMapmethod 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.
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.