Tutorial – SQL Database Migration

In this article, I will show you how I used a Python application programming interface (API) to migrate my music library from an SQL relational database to a NoSQL document database. Using the Python X DevAPI in the MySQL Shell application, I will highlight some basics about document databases, the Python methods that I used, and the database tool that enables migration. Readers who should get the most out of this article are those that have some basic familiarity with the structured query language (SQL) and with the Python programming language.

Why Migrate from SQL to Document?

I have my existing personal music library in an SQL relational database containing music metadata – artist, song title, album title, track number, genre, release year – that I want to migrate to a document database. For the purpose of this article, I will use a few examples from my library (Figure 1).

Figure 1: Sample table of songs from my SQL library.

SQL relational databases have been dominant for decades, making up 60 percent of the database market in 2019 according to a ScaleGrid Database Trends report. In recent years, use of document databases has increased, largely driven by the requirements of big data. One of the criticisms of relational databases is that their schema is rigid. All data fields must be defined in advance with identical fields in every row in a table, making it difficult to make schema changes later.

By contrast, document store databases, sometimes referred to as NoSQL, do not have a fixed schema. Document databases do not require each document to have the same fields (though they can). In fact, it is possible to have different fields in each document throughout the database. That flexibility is one of the key advantages of a document store over a relational store. It is the reason I decided to migrate, because it allows me to easily add new metadata to my music library. That could include metadata such as artist background information, song credits, and/or other miscellaneous metadata that may not be immediately available.

What Is a JSON Document?

In many document store systems, documents are JavaScript Object Notation (JSON) objects, or JSON-like objects. JSON is becoming increasingly popular as a standard for data interchange and storage and is beginning to replace the Extensible Markup Language (XML) as a dominant data exchange format, particularly for music metadata. JSON documents are lightweight, language-independent, and human readable. In short, JSON documents are elegant in their simplicity. Many popular music APIs provide JSON-formatted metadata. These APIs include Amazon, Apple Music, Spotify, SoundCloud, and others.

The JSON format eases development since it is object-oriented and easier to parse than XML, because JSON documents are comprised of a comma-separated list of one or more key-value pairs. The simplest form of a JSON document is {key:value}. You will note that this is the same form as a Python dictionary. From a software development perspective, JSON is well suited to object-oriented programming languages such as Python, JavaScript, and others.

Let's consider that we have a simple document case containing an artist name and album name. The document instance would be defined as follows:

{"Artist" : "Quincy Jones", "Album" : "Q's Jook Joint"}

A group of related documents is referred to as a collection. As an analogy between a relational database and a document database, a table in a relational database is equivalent to a collection in a document database. Each row in a table is equivalent to a document in the collection, and each field name (column) in a table is equivalent to a key in a document.

API Methods for Database Migration

Now that we know what a document store is, I'll show you how to use a Python API to migrate a relational database to a document database. My source database was created with MySQL, so I used the X DevAPI interface in the MySQL Shell 8.0 application, which allows me to access both tables and documents in a database.

To accomplish my goal, I'll use table methods to access data in tables, and document methods to build and verify my documents. Three steps are required to migrate data: 1) create a collection, 2) fetch rows from a table, and 3) add fetched rows to the document collection.

Table methods used:

# Returns a dataset with rows
table.select()
# Returns a dictionary/json object
result.fetch_one_object()

Document methods used:

# Inserts a document into a collection
collection.add()
# Returns dataset with all documents in a collection
collection.find()