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=', 'root', 'password123'), 'users');$pdoDestination = new PDODestination(new PDO('mysql:dbname=new-test;host=', '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 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.

🖥️ Full stack developer

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

SSIS Data Flow Components 1.13 Now Support Dynamics CRM OAuth 2.0 Authentication

The Software Testing Spectrum

LockTrip Deploys It’s Flights Marketplace!

2 weeks of Hacktoberfest as a first-time maintainer and contributor

CSS Overflow Problems & its Solutions

Why ‘Eat. Sleep. Code. Repeat.’ is a great t-shirt idea, but a terrible motto…

Eat. Sleep. Code. Repeat. Banner

Searching and Sorting

How to use Snowflake VARIANT in Dataiku

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Jordan Hall

Jordan Hall

🖥️ Full stack developer

More from Medium

How to Solve Spatie/laravel-sitemap installation issue

The basics of creating a website using Hacklang+HHVM

A simple PHP site (2/?) — Docker

How to count number of files in folder and Delete all folder files