11
The MySQL Document Store
The MySQL JSON data type is extremely flexible. It may be a temptation for some to use the MySQL JSON data type by itself to provide an easy-to-use, schemaless data storage option. Simply having a table with one column, and that column being of type JSON, would be a very simple solution. Each row could have up to 1GB of JSON data. This idea is very close to the premise of the MySQL Document Store.
The MySQL JSON data type is the foundation of the MySQL Document Store. Relational databases need schemas and columns defined in tables before they can be used. But a document database enables developers to start saving and using data without having predefined data structures. As the data needed evolves for a given application, there is no need to call in a database administrator to redefine tables.
JSON provides for embedded arrays and objects and is a viable solution when the data does not fit into the relational model. There is also no need for an object relational mapping layer to map objects in a modern programming language to a column in a table. And this eliminates the need to embed a string with a Structured Query Language query in a program.
Having the document store built on the JSON data type enables you to use the same data as a document database or as a relational database—at the same time. And you can access collections and tables.
This chapter provides a general introduction to the MySQL Document Store, though this subject is deserving of its own book (or several). Refer to the MySQL documentation for full details.
By default, the MySQL Document Store creates two columns, doc and _id, as per most of the examples in this book. The InnoDB storage engine requires a primary key, and the _id field provides it. You can, of course, create your own collections with your own primary key.
The X DevAPI
MySQL engineers created the new X Protocol to provide functionality that the old MySQL protocol lacks. But what are some of the differences? The first is the network connection. Where the traditional protocol listened to TCP/IP port 3306, the new X Protocol listens to port 33060. The X Protocol has a new session model that enables code to scale from a single server to multiple servers. And the new protocol requires the use of a new shell—mysqlsh.
mysqlsh
The new mysqlsh shell is in some ways similar to the old mysql shell (better known as the MySQL Command Line Interface [CLI]), but in other ways it is a major leap forward. The interface is very similar and familiar, but it offers so much more. It is built upon the new X DevAPI, has multiple modes, and includes built-in language interpreters for JavaScript and Python. And it can also process Structured Query Language. The new API enables applications to scale easily from single to multiple server environments, and it provides nonblocking asynchronous calls common to many common host languages. The new shell was also designed for server administration for services such as InnoDB Cluster. To invoke this program, enter mysqlsh.
The X DevAPI session is a higher-level database session idea than the traditional lower-level MySQL connections. With X DevAPI, sessions can have several MySQL connections and can use either the classic MySQL protocol or the new X Protocol. The ClassicSession class provides a low-level MySQL connection to a single MySQL server instance. Applications taking advantage of the new features in the X DevAPI NodeSession class can be run against one server or a group of MySQL servers without code changes. The NodeSession class provides full support of X DevAPI but limited support of SQL.
After you’ve installed the new shell, enter mysqlsh to start—actually, enter mysqlsh user@host/world_x, using the actual username and hostname. In Example 11-1, the document with the _id equal to USA is specifically requested. Compare the example’s syntax to the SQL equivalent SELECT doc FROM countryinfo WHERE doc->"$._id" = 'USA';.
Example 11-1 Using the mysqlsh shell to access the MySQL Document Store to find a record in the countryinfo collection from the world_x sample database
The new shell has three modes—Python, JavaScript, and SQL—and acts very similarly to the old shell, especially when in SQL mode. In the example, JavaScript mode is in use, as indicated by the JS> prompt. In some of the following examples, the mode is switched to SQL mode.
Connections
The MySQL mysqlsh shell features the ability to connect using the classic MySQL protocol and the new X DevAPI protocol. Specifics for the connections use a Uniform Resource Identifier (URI). X protocol connections are TCP only, while the classic protocol tries to default to UNIX sockets.
The URI can be specified on the line starting the shell, such as mysqlsh –uri user:password@host:33060/schema, or you can simply start the shell and then connect with connect user@host/schema (assuming you want to be prompted for the password and the MySQL server is listening to port 33060).
Session Types
There are two types of sessions under the new MySQL mysqlsh shell. NodeSession is designed for new applications with MySQL servers that support the X DevAPI protocol (MySQL 5.7.12 or more recent), and the ClassicSession is for servers without the X Protocol. All the exciting CRUD (Create, Replace, Update, Delete) and newer features are available only with the NodeSession. At shell invocation, entering –sqln creates a NodeSession, while entering –sqlc creates a ClassicSession.
After the shell has been started, connections will attempt by default to use the X DevAPI protocol. In Example 11-2, user dstokes connects to the localhost to access the world_x schema.
Example 11-2 Connecting after the MySQL mysqlsh shell has been started.
Example 11-3 Connecting with the connection specifics on the command line
Once you have invoked the shell and logged in, you are ready to get to work.
Collections and Documents
Documents are stored in collections. Collections are containers for documents that hopefully share a purpose. It is easy to create a new collection. Ignoring the details of the new mysqlsh shell commands for the moment, Example 11-4 uses the schema test as the working document. The session has already been created (the user already logged in to the server). And the object named db is a global variable assigned to the current active schema. The third bolded command, db.createCollection('demo'), does the actual creation of a new document collection named demo. Lastly, the getCollections() function shows the available collections.
Example 11-4 This example shows connecting to the test schema, referenced as the object db, and then creating a collection named demo.
In Example 11-4, use test tells the server which schema to use. The server uses db as a pointer object to point to the chosen schema, and issuing db by itself confirms it is the selected schema. Next is a collection with the name of demo. And, finally, the getCollections() function reports any available collections in the test schema. This is using MySQL without the SQL.
Behind the scenes, the MySQL server has created the desired collection. To see the work performed is a very simple matter; at the mysqlsh prompt, you enter the SQL mode by entering sql. Note the prompt will change to mysql-sh>. From here on, until the shell is exited or the mode is changed again, traditional SQL commands are accepted.
Example 11-5 Collection created by db.createCollection(‘demo’) as viewed from the SQL side of the server
A JSON column named doc and a VARCHAR column named _id were automatically generated when the collection was created. A table named demo has been created with two columns. The first column is named doc and is in the JSON data type. The second column is a varchar named _id that is a stored generated column. You can see more details by using the SHOW CREATE TABLE demo command.
Example 11-6 More details of the demo collection providing details on how the _id column was created by the use of a generated column
The _id column is generated by extracting and unquoting the _id key (with the use of JSON_EXTRACT and JSON_UNQUOTE) from the JSON document and placing that data in the column. This should be very familiar if you read Chapter 7 on generated columns. Note that the document store will generate a value for the _id value if one is not specified; specifying your own _id will require a string, not a number—that is, "10" not 10. And having _id designated as the primary key fulfills the need of the InnoDB storage engine to have a primary key.
CRUD: Create, Replace, Update, Delete
The MySQL Document Store provides functions for CRUD operations (Create, Replace, Update, and Delete) for documents in a collection: add(), modify(), and remove(). These three functions, when combined with find(), are the core basic operations most developers need on an ongoing basis to take advantage of the document database.
In Example 11-7, note that _id is defined as a VARCHAR(32) and the number "101" has to be a string in quotes. Note the use of js to switch to JavaScript mode from SQL mode.
Example 11-7 Adding a document to a collection
The documents in a collection can be acted upon very easily and by a number of functions. These functions have various ways to improve queries. In Example 11-8, the top or root object is extended with the key/value pair of shoe and 50. Besides objects, arrays can be added to the document.
Example 11-8 Using set to modify a document by adding another key/value pair
You can also add an array value to a document.
Example 11-9 Adding an array value to a document
Arrays can also be added to a document with set. Note that the particular _id to be modified has been specified in Example 11-10. Without denoting the exact record to be modified, all the documents in the collection would be modified.
Example 11-10 Removing a key/value pair from a document
The unset() function requires only the name of the key of the key/value pair to be deleted. Note in Example 11-10 the use of the "_id='101'" qualifier in the modify() function to specify the exact document to be modified; omission of a way to find the exact document to be modified will result in all the records in the collection being affected.
NOTE Be sure to back up your critical data. Retyping documents is laborious, boring, and unproductive.
Arrays can be appended very easily with arrayAppend, and it will append to the end of the array at the key. In Example 11-11, the key feet now has two arrays for values associated with it. To append the array [toes,arch,heel,ankle] to the first array, modify the path from $.feet to $.feet[0] and the result will be [left,right,toe,arch, heel,ankle] if you want both combined.
Example 11-11 Adding and then appending an array to a document
Finally, documents can be removed with the remove() function. Remember to specify the record or records desired for deletion, or all the documents in the collection will head for the bit bucket.
Example 11-12 Removing one record that matches
The add() function can accept valid JSON-formatted documents on a single or on multiple lines. Example 11-13 shows the entry of two records on multiple lines.
Example 11-13 Adding records
Filtering Find
The find() function can be tuned to narrow searches or select only certain fields.
Example 11-14 The find() function can be modified to narrow down searches and to specify only certain parts of the document are in the returned data.
The limit() and skip() functions can also help winnow down returned data.
Example 11-15 Using the limit() and skip() functions to winnow down returned data
Sorting
The sort() function can be postpended to find() to order the returned document. But sort() requires that one or more fields in the document be named as a key. With a key named to sort upon, the server will send back an “Invalid number of arguments in CollectionFind.sort, expected at least 1 but got 0 (ArgumentError)” error message. If you also use the fields() function, you need to specify one or more of the returned document keys as the field on which to sort. Omitting the field name to be sorted will result in an “Illegal number of arguments” error message.
Example 11-16 Sorting can be done, but the fields need to be specified to be passed to the sort() function.
If fields are not specified, you can use any key in the document for the sort key.
Example 11-17 Using any key in the document for the sort key
Binding
Binding values to variables is also possible and highly desirable in scripts when iterating over values. In Example 11-18, a variable is declared by prepending a colon (:) to the name of the variable. The bind() function then replaces the variable with the value before executing the command.
Example 11-18 Passing bound parameters
Indexing Collections
Indexes may be added to speed up the process of finding specific documents in a MySQL Document Store, just like in the “regular old MySQL relational server.” Indexes enable the server to go directly to the record or records desired without having to process every record in the collection. Processing every record is better known as a full table scan, and database administrators work hard to eliminate full scans. Sometimes your application does need to read through all the records in a collection—for processing all the accounts payables, for example—but generally full scans are to be avoided. Generally, we don’t need to read the entire computer manual to find an answer to a single question when the manual has an index that we can use to look up the answer.
Indexes are not a panacea for databases, however. Indexes are a separate table that needs to be read, maintained as records change or are removed, and managed. It is often tempting to novice database developers to index all columns, but the overhead of doing this can greatly slow operations.
Indexes are generally unique or not. Unique indexes will have a pointer to one record in the collection. Nonunique indexes can have multiple entries. Consider a billing system where each customer will have its own unique identification number, but the order collection will hold the identification number of multiple customers.
Example 11-19 Creating indexes
Note that the EXPLAIN command can be used in SQL mode, but not in Python or JavaScript.
Example 11-20 shows how to create a nonunique index, and Example 11-21 creates a unique index.
Example 11-20 Creating a nonunique index
Example 11-21 Unique indexes can also be created, but the field values need to be unique.
If a unique index is created, any attempt at inserting a duplicate value will produce an error, as shown in Example 11-22. Sometimes this is caused when you try to add to a document that instead should be modified; other times this can result from carelessness. Often you will see MySQL relational tables use an AUTO_INCREMENT definer on a column to have unique values supplied for unique identifiers.
Example 11-22 An error occurs if you attempt to insert a duplicate value.
The MySQL Document Store will automatically add a unique _id key if you do not specify a value and use a generated column to build an index. The automatically generated _ids will look something like this: 3019886f8e6fd311640d4851b70943c6 or ac5a657d8e6fd311640d4851b70943c6. You can specify your own _id values (remember they are strings and need quotes around them) and use your own scheme for values.
Dropping a Collection
Data has a lifespan, and it is fairly simple to remove or drop a collection. The name of the schema and the name of the collection must be specified. Once data is dropped, you can recover the data only from a backup or by re-entering the data if you have a very good memory.
Example 11-23 Removing the demo collection from the test schema
