Agile Data Modeling with MySQL Document Store

Up until the 2nd half of the 2000s, before the NoSQL databases, the only option was relational databases (RDBMS).

Problem

A RDBMS table has a pre-defined number of named columns with fixed data types. The data has to be structured and all rows of data must include data for each row, albeit null values. As the data model evolves, the data definition could become obsolete quickly. DDL does provide statements to add/remove/modify columns but it could be cumbersome to make such changes frequently.

Solution

Agile data modeling involves a collaborative, iterative, and incremental approach to data modeling. MySQL 5.7+ added support for the JSON data format, which is a flexible, schema-less data format. We also discuss how the X DevAPI introduced with the X protocol plugin enables storing data as JSON documents in collections. The X DevAPI may be accessed from the MySQL Shell and Connectors. In this article we discuss how MySQL Document Store could be used for agile data modeling.

Getting Set Up

We have used MySQL 8. Additionally, you would need to install the X Plugin, which provides the X Dev API. The X Plugin is installed by default with MySQL 8.0.37. Verify by running the following command to list installed plugins, and the mysqlx plugin should be listed.

C:\Program Files\MySQL\MySQL Shell 8.0\bin>mysqlsh -u root --sqlc -e "show plugins"

Please provide the password for 'root@localhost': *****

Name Status Type Library License

binlog ACTIVE STORAGE ENGINE NULL GPL

…

mysqlx_cache_cleaner ACTIVE AUDIT NULL GPL

mysqlx ACTIVE DAEMON NULL GPL



Your First Data Model

Let’s say someone asked you to develop a data model for a magazine catalog. You come up with a rather simple magazine catalog with only one attribute, magazine name. For the data model you create a JSON collection in the MySQL Document store. A collection is a container for JSON documents. The following JavaScript code in MySQL Shell establishes a user session, creates a collection in the test database, and adds a document with only one attribute.

First, require the mysqlx extension.

var mysqlx = require('mysqlx');

Create a Server Session. Note that the X Protocol port is 33060.

var s1 = mysqlx.getSession( {

host: 'localhost', port: 33060,

user: 'root', password: 'mysql'} );

Get a database object.

var db = s1.getSchema('world');

Create a new collection.

var coll1 = db.createCollection('magazine_catalog');

Add a document.

coll1.add({ name: 'PHP Magazine', age: 19 }).execute();

As the output indicates, one document gets added.

MySQL JS > coll1.add({ name: 'PHP Magazine', age: 19 }).execute();

Query OK, 1 item affected (0.0838 sec)



Developing the Data Model

Perhaps you ask others to develop the data model further. A collaborative, iterative, incremental approach is what makes such a data modeling methodology agile. A second developer reviews the first data model and finds it rather lacking; not much information on who the publisher is, the publication frequency, the category of the magazine (general, news, fashion, technology, etc.), and the media type (print, digital). The second developer creates a collection object for the existing collection magazine_catalog, and adds a second document.

Get a collection object.

var coll1 = db.getCollection('magazine_catalog');

Add a document.

MySQL JS > coll1.add({name: 'Java Magazine',publisher: 'Oracle Publishing',frequencyPerYear: 6, category: 'Information Technology', mediaType: 'Print' }).execute();

Query OK, 1 item affected (0.1194 sec)

A third developer adds three other useful attributes: published since, sample cover photo, and content type (articles, product reviews).

MySQL JS > coll1.add({ name: 'Oracle Magazine', publisher: 'Oracle Publishing', frequencyPerYear: 6, category: 'Information Technology', mediaType: 'Print', since: '1980', contentType: 'articles,product reviews,industry spotlight', coverPhoto: "" }).execute();

Query OK, 1 item affected (0.1139 sec)

As the data model evolves new documents may be added with different sets of attributes.

Using the X DevAPI Collection Object

The X DevAPI Collection Object provides functions to perform basic CRUD operations. These functions are:

We already showed examples of using Collection.add(). As another example, find documents. The following JavaScript finds and lists all documents in the magazine_catalog collection.

MySQL JS > var coll1 = db.getCollection('magazine_catalog');

MySQL JS > coll1.find().execute();

{

"_id": "0000637ba38d0000000000000001",

"age": 19,

"name": "PHP Magazine"

}

{

"_id": "0000637ba38d0000000000000002",

"name": "Java Magazine",

"category": "Information Technology",

"mediaType": "Print",

"publisher": "Oracle Publishing",

"frequencyPerYear": 6

}

{

"_id": "0000637ba38d0000000000000003",

"name": "Oracle Magazine",

"since": "1980",

"category": "Information Technology",

"mediaType": "Print",

"publisher": "Oracle Publishing",

"coverPhoto": "",

"contentType": "articles,product reviews,industry spotlight",

"frequencyPerYear": 6

}

3 documents in set (0.0215 sec)



Exploring Data Model Options

After evaluating the data model development, you need to make some selections such as:

• Which data attributes should be included?
• Is a JSON document store a better alternative to a relational database table?

Selecting Data Attributes

Some data attributes are essential, such as magazine name, publisher, category, content type, publication frequency, and media type, while other data attributes may be considered as secondary, or optional; attributes such as sample cover photo, and published since.

Selecting a Data Model

Three data model options are available:

1. Document Store Collection
2. RDBMS table with a column for each attribute
3. JSON type column

We have already explored the Collection option.

RDBMS Table

If you choose a RDBMS data model, your data definition DDL may be something like:

CREATE TABLE magazine_catalog1(name VARCHAR(255),publisher VARCHAR(255), category VARCHAR(255), content_type VARCHAR(255), publication_frequency INT,media_type VARCHAR(255));

Perhaps you find the RDBMS data model too rigid, or inflexible. Would a data modeler have a problem finding data for all data attributes for each of the magazines? You may want to consider a JSON data type based data model.

Using the JSON Data Type

The JSON data type is available since version 5.7. Its main benefits are:

• Adaptive/Flexible
• Schema-less
• Support for several JSON-related built-in functions
• Automatic validation of JSON data
• Optimized binary storage format
• Efficient read access

The following DDL creates a table with a JSON type column.

mysql> CREATE TABLE catalog(id INT, magazine JSON);

Query OK, 0 rows affected (0.23 sec)

Add three sample rows of data:

mysql> INSERT INTO catalog VALUES(1,'{"name": "PHP Magazine"}');

Query OK, 1 row affected (0.05 sec)

mysql>

mysql> INSERT INTO catalog VALUES(2,'{"name": "Java Magazine", "publisher": "Oracle Publishing", "frequencyPerYear": 6, "category": "Information Technology", "mediaType": "Print", "since": "1980"}');

Query OK, 1 row affected (0.02 sec)

mysql>

mysql> INSERT INTO catalog VALUES(3,'{"name": "Oracle Magazine", "publisher": "Oracle Publishing", "frequencyPerYear": 6, "category": "Information Technology", "mediaType": "Print", "since": "1980", "contentType": "articles,product reviews,industry spotlight", "coverPhoto": "" }');

Query OK, 1 row affected (0.02 sec)

Select data added:

mysql> SELECT * from catalog

-> ;

+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| id | magazine |

+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| 1 | {"name": "PHP Magazine"} |

| 2 | {"name": "Java Magazine", "since": "1980", "category": "Information Technology", "mediaType": "Print", "publisher": "Oracle Publishing", "frequencyPerYear": 6} |

| 3 | {"name": "Oracle Magazine", "since": "1980", "category": "Information Technology", "mediaType": "Print", "publisher": "Oracle Publishing", "coverPhoto": "", "contentType": "articles,product reviews,industry spotlight", "frequencyPerYear": 6} |

+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

3 rows in set (0.00 sec)

Using the JSON Functions

MySQL provides several built-in functions that could be used with JSON. The functions may be categorized by their use as follows:

As an example, validate JSON in the catalog table.

mysql> SELECT JSON_VALID(magazine) FROM catalog;

+----------------------+

| JSON_VALID(magazine) |

+----------------------+

| 1 |

| 1 |

| 1 |

+----------------------+

3 rows in set (0.03 sec)

Using the X DevAPI Table Object

The X DevAPI Table object provides SQL CRUD functions for relational tables. These are:

As an example, select data from the catalog table.

MySQL JS >var mysqlx = require('mysqlx');

Create a Server Session.

MySQL JS >var s1 = mysqlx.getSession( {

host: 'localhost', port: 33060,

user: 'root', password: 'mysql'} );

Get a Database object.

MySQL JS >var db = s1.getSchema('world');

Access an existing table.

MySQL JS >var table1 = db.getTable('catalog');

Find a row in the SQL Table.

MySQL JS >var result = table1.select(['id', 'magazine']).execute();

Print result.

MySQL JS >print(result.fetchOne());

The result is:

[

1,

"{\"name\": \"PHP Magazine\"}"

]