Three steps from SQL to a document database

Tutorial – SQL Database Migration

Article from Issue 266/2023
Author(s):

Use a Python API to migrate a music library from SQL to a NoSQL document database.

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()

Buy this article as PDF

Express-Checkout as PDF
Price $2.95
(incl. VAT)

Buy Linux Magazine

SINGLE ISSUES
 
SUBSCRIPTIONS
 
TABLET & SMARTPHONE APPS
Get it on Google Play

US / Canada

Get it on Google Play

UK / Australia

Related content

  • PostgreSQL

    PostgreSQL, an open source object-relational database management system known for its reliability and extensibility, offers a robust feature set. If you are new to PostgreSQL, we help you get started with some of its most useful features.

  • LibreOffice Music Database

    LibreOffice Calc and Base are all you need to create a simple database for organizing the songs in your music collection.

  • MongoDB NoSQL Database

    MongoDB combines the usual advantages of NoSQL databases with sharding, load balancing, replication, and failover.

  • PHPlattenbau

    Build simple PHP applications quickly with ready-to-use blocks.

  • Usql

    Usql is a useful tool that lets you manage many different databases from one prompt.

comments powered by Disqus
Subscribe to our Linux Newsletters
Find Linux and Open Source Jobs
Subscribe to our ADMIN Newsletters

Support Our Work

Linux Magazine content is made possible with support from readers like you. Please consider contributing when you’ve found an article to be beneficial.

Learn More

News