The Process

The three steps that I identified above assume that the relational database exists, that a database connection has already been established, and the source table exists. Before starting the migration process, I'm assuming that a connection to the database has already been made and that the following instances already exist:

# Session instance
my_session = mysqlx.get_session( <URI> )
my_database = my_session.get_schema('my_db')

The first step in the migration process is to create the target collection:

doc_collection1 = my_database.create_collection('my_collection1')

In the statement above, I've created a collection object named my_collection1 using the create_collection() method and assigned it to doc_collection1, which will be the target document collection. I will subsequently add, update, or remove documents as necessary.

In the next step, I need to extract my metadata from the source SQL database. This metadata is in a table named sql_table. To extract data in a table row, I execute the statements below:

table_result = sql_table.select().execute()
table_2document = table_result.fetch_one_object()

In the code above, the select() method is analogous to the SELECT statement in SQL. It returns a result that is a list of rows. Next, I need to fetch each row, convert it to a JSON object, and add it to my collection. The fetch_one_object() method fetches a row from the table as a JSON object.

The table_2document result object shows the key:value strings of the metadata fetched from one row of the table (see Figure 2). While there is a fetch_one() method that could fetch a table row, the result is not a JSON object and therefore cannot be added directly to a document. Note that SQL statements executed through the X DevAPI must end with the execute() function because they are executed only when that function is called. If omitted, the statement will be ignored.

Figure 2: Fetch a JSON document from a SQL table.

In the third step, each table_2document fetched from my_table is then added to doc_collection1 with the add() method which adds a JSON document to a collection:

doc_collection1.add(table_2document).execute()

Build Collection

With these three basic steps, I can add a single document to doc_collection1. To migrate the entire SQL table to a collection, the code below iterates through each row in the table with the add() method (see Listing 1)

    table_result = sql_table.select().execute()
    table_2document = table_result.fetch_one_object()
    while table_2document:
        doc_collection1.add(table_2document).execute()
        table_2document = table_result.fetch_one_object()

The result in Figure 3 shows two of the five documents added to doc_collection1. You will notice that there is an extra _id field that is automatically added to each document. It is a virtual index that MySQL automatically adds to each document in a collection. See the MySQL 8.0 manual [1] for more information on document indexing.

Figure 3: Sample of documents added to collection.

Document Updates

After I have migrated my SQL table to the document collection, I will continue to either build on it with new metadata documents, update incorrect or incomplete documents, or remove documents. As we have already seen, I can use the add() method to add new documents to my library or simply add new fields (key-value pairs), effectively changing the schema on the fly.

If, for example, I need to change the spelling of an artist's name, I can use the doc_collection1.modify() method. Note that the percent (%) wildcard can be used in the search condition string for these methods, as illustrated with the remove() method in the example below. In addition, note that I have used explicit strings in the modify() and set() methods to simplify the examples and to keep the focus on function. It is however, good practice to use parameterized placeholders instead of explicit strings.

doc_collection1.modify("Artist = 'Santana'").set("Artist", "Carlos Santana")
doc_collection1.remove("Artist like 'Quincy%'")

For a more comprehensive list of available create, remove, update, and delete (CRUD) methods, see the MySQL 8.0 Reference Manual.