What happens when you create a MySQL Document Store

The MySQL Document Store introduced with version 5.7.12 allows developers to create document collections without have to know Structured Query Language. The new feature also comes with a new set of terminology. So let us create a collection and see what it in it (basically creating a table for us SQL speakin' old timers).

So start the mysqlsh program, connect to the server, change to the world-x schema (database) switch to Python mode, a create a collection (table).

What did the server do for us? Switching to SQL mode, we can use describe to see what the server has done for us.

We have a two column table. The first is named doc and is used to store JSON. And there is also a column named _id and please notice this column is notated as STORED GENERATED.

The generated column extracts values from a JSON document and materializes that information into a new column that then can be indexed. But what did the system extract for us to create this new column?

Lets use SHOW CREATE TABLE to find out.

mysql-sql> SHOW CREATE TABLE foobar;
+--------+----------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------------------------------------+
| Table  | Create Table

                                                                      |
+--------+----------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------------------------------------+
| foobar | CREATE TABLE `foobar` (
  `doc` json DEFAULT NULL,
  `_id` varchar(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,'$._id'
))) STORED NOT NULL,
  UNIQUE KEY `_id` (`_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+--------+----------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql-sql>

So the 5.7.12 document store is creating an index for us on a field named _id in our JSON document. Hmm, what if I do not have an _id field in my data. So I added two records ("Name" : "Dave" and "Name" : "Jack") into my new collection and then took a peek.

mysql> select * from foobar;
+-------------------------------------------------------------+-----------------
-----------------+
| doc                                                         | _id
                 |
+-------------------------------------------------------------+-----------------
-----------------+
| {"_id": "819a19383d9fd111901100059a3c7a00", "Name": "Dave"} | 819a19383d9fd111
901100059a3c7a00 |
| {"_id": "d639274c3d9fd111901100059a3c7a00", "Name": "Jack"} | d639274c3d9fd111
901100059a3c7a00 |
+-------------------------------------------------------------+-----------------
-----------------+
2 rows in set (0.00 sec)

mysql>

But what if i do have a _id of my own?

The system picked up the _id for the Dexter record. Remember that the index on the _id field is marked UNIQUE which means you can not reuse that number.

So we know the document store wants is creating an unique identification number (that we can also use).

Update: The client generates the identification number, the server can not due to possible conflicts in future sharding projects.

The new MySQL X DevAPI Protocol and what it means for PHP

The relational database world just changed and you didn't notice, did you? Rather than speaking SQL to your data, what if you could talk to it in PHP directly? No longer do you have to struggle to remember arcane structured query language syntax and just grab data with PHP. You can still write bad queries full of N+1 errors and a whole host of other problems. But you are speaking PHP to your data. And this started with MySQL 5.7.12 and its new Document Store Functionality.

The Document Store functionality expands on the new JSON data type in MySQL 5.7 with a new server plugin, a new API, and a suite of new components designed to make MySQL accessible for users who are not familiar with the SQL language or prefer to use a schemaless data store. This MySQL Server plugin enables communication using the X Protocol. And clients that implement X DevAPI and enables using MySQL as a document store easily. So you can talk to your data in the language of your choice (say Python, Javascript, or SQL) via a new shell or use a new connector to use these features. For those in the PHP world I must warn you that the new connector to support the new X DevAPI is on the way -- not here yet but on the way.

Trying the XDevAPI

You will need MySQL 5.7.12 with the X plugin enabled and the new mysqlsh. Details on the installation of the shell and plugin can be found under Using MySQL as a Document Store in the MySQL 5.7 Reference Manual. Fire up the shell and you will see something like this:

That mysql-js> prompt lets you know that your shell is currently in Javascript more. Currently the shell has modes for Javascript, Python, or SQL. To connect to a database simply type

db = session.getSchema('world_x')

It is then easy to use Javascript to create, remove, update or delete data.

The X Plugin extends MySQL Server to be able to function as a document store and the X Protocol, which is designed to expose the ACID compliant storage abilities of MySQL as a document store. Documents are stored in JSON format and enable schema-less storage. Using the X DevAPI you can use a NoSQL-like syntax to execute Create, Read, Update, Delete (CRUD) operations against these documents. The server will be listening to port 33060 (configurable) for communications in the new protocol.

And any data you drop into the document store is avail from the document store in JSON format AND is accessible at the same time from SQL. And by using the new connectors (Java, .NET, Node.JS for now) that support the X protocol, your code hits the database without have to fidget and grumble about using SQL. You are talking in your language to a relational database using NoSQL.

And you data you add via the X protocol in Node.JS can be used by someone with Python, or Java, or SQL at the same time.