Or How to Stop Embedding Ugly Strings of SQL In Your Beautiful Code

By Dave Stokes

Do you work on projects where you begin coding before knowing what your data looks like? Or are you part of the vast majority of developers who have had little or no training in database theory, relational calculus, Structured Query Language, or sets? Could you be working on a project without a database administrator to set up relational tables, indexes, and schemas? Or are you tired of embedding ugly lines of SQL in your pristine PHP code? There is new hope for you.

The MySQL Document Store

MySQL 5.7 introduced a native JSON data type in version 5.7, and it has been greatly enhanced with version 8. Built on top of the JSON data type is the MySQL Document Store which is a NoSQL JSON document store. It enables you to use a schema-less, flexible storage system. You do not need to define the attributes of the data, setup relations, or normalize data. Just create a collection and populate with data.

You can use the new MySQL Shell to login to a server and create a collection. In the example in Output 1, I used the MySQL Shell to connect to the schema demo, create a new collection, and a record. And yes, you can do this in PHP too!

MySQL JS> \connect root@localhost/demo Creating a session to
'root@localhost/demo'
Enter password: ******
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 9 (X protocol) Server version: 8.0.11
MySQL Community Server - GPL Default schema `demo` accessible
through db.
MySQL [localhost+ ssl/demo] JS> db.createCollection('architect')
 <Collection:architect>
MySQL [localhost+ ssl/demo] JS> db.architect.add(
    {
        'name' : 'PHP Architect'
    }
)
Query OK, 1 item affected (0.1040 sec)
MySQL [localhost+ ssl/demo] JS>

The MySQL Document Store is built on a new protocol with a new API—the X DevAPI. The old MySQL protocol was beginning to show its age after over two decades of use. The new protocol listens on port 33060 as opposed to the traditional MySQL 3306 port. There are several interesting features of the protocol so it can better handle asynchronous queries, management of high availability server clusters, and even make sure instances are ready for upgrade. But for this article we will only look at the functionality of the document store.

What is a Document?

A document is a set of keys and value pairs in a JSON object. The values of the fields can contain other documents, arrays, and lists of documents. The MySQL JSON data type provides roughly a gigabyte of space in a column of a row in a table.

Remember that collection created earlier? If we use SQL to look at the data from the JSON column you will see something like Output 2.

MySQL [localhost+ ssl/demo] SQL> SELECT doc FROM architect;

+------------------------------------------------------------------+
| doc                                                              |
+------------------------------------------------------------------+
| {"_id": "00005b100c730000000000000001", "name": "PHP Architect"} |
+------------------------------------------------------------------+
1 row in set (0.0017 sec)
MySQL [localhost+ ssl/demo] SQL>

Ignore the _id data for a few moments. The key/value data entered earlier for name/architect are easy to spot.

Collections are a container made up of documents. And you can also access relational tables with the MySQL Document Store too but ignore that for now. You can perform CRUD (create, read, update, and delete operations) on collections or documents. The API and the new protocol ensure the calls to the database look pretty much the same regardless of whether you are coding in C++, Java, Node.JS, JavaScript, .NET, or even PHP.

The X DevAPI PECL Extension

The XDevAPI PECl extension is available online and the documentation and installation instructions.

Example

The example in Listing 1 will look familiar to those who use MySQL or any other relational database. First, you need to authenticate into the server. Next, you select your schema (or database) and then the document collection to use. From there we are looking to find a record where the _id field matches a particular value.

Listing 1

#!/usr/bin/php
<?php
// Connection parameters
$user = 'root';
$passwd = 'S3cret#';
$host = 'localhost';
$port = '33060';
$connection_uri = 'mysqlx://' . $user . ':' . $passwd
                . '@' . $host . ':' . $port;


// Connect as a Node Session
$nodeSession = mysql_xdevapi\getNodeSession($connection_uri);
// "USE world_x"
$schema = $nodeSession->getSchema("world_x");
// Specify collection to use
$collection = $schema->getCollection("countryinfo");
// SELECT * FROM world_x WHERE _id = "USA"
$result = $collection->find('_id = "USA"')->execute();
// Fetch/Display data
$data = $result->fetchAll();
var_dump($data);

The next item is the big change. With traditional MySQL, your query would look something like:

SELECT * FROM countryinfo WHERE _id = "USA";

If we only wanted the JSON document, it would really look like:

SELECT doc FROM countryinfo WHERE _id = "USA":

With the document store, the query becomes:

$collection->find('_id = "USA"')->execute();

That has a much cleaner appearance than the corresponding embedded SQL string in our beautiful, pristine PHP code.

Finding Find()

The find function has several parameters that can be passed to it. Besides the search condition (_id = "USA") in the previous example, you can specify which fields you desire, how to group by, a having search condition, sorting options, limits and offsets, parameter binding to variables, and locking on records (exclusive or shared).

The other CRUD functions—add, find, modify, and remove as they are called in the MySQL Document Store—are equally festooned with options. You get a very rich environment to manage your data.

Did I mention you can also use the MySQL Document Store with good ol’ relational tables? There are slightly different CRUD functions for relational tables that map pretty well to their standard SQL counterparts—insert, select, update, and delete—with the main difference being the select function also has an order by optional parameter.

Behind the Scenes

Underneath the cover, a collection from the SQL side is a table with two columns. The first is creatively called doc for the JSON document and the second is _id. InnoDB storage engine tables are much happier with a primary key, and you can supply your own value for the _id field or let the server generate it for you. Since InnoDB is transactional and locks at the row level, you can access the same data via SQL or the document store at the same time, perform transactions, replicate data, and all the other usual things you can do with MySQL.