MySQL Document Store Video Series

I am starting a series of videos on the MySQL Document Store. The Document Store allows those who do not know Structured Query Language (SQL) to use a database without having to know the basics of relational databases, set theory, or data normalization. The goal is to have sort 2-3 minute episodes on the various facets of the Document Store including the basics, using various programming languages (Node.JS, PHP, Python), and materializing free form schemaless, NoSQL data into columns for use with SQL.

The first Episode, Introduction, can be found here.

Please provide feedback and let me know if there are subjects you would want covered in the near future.

MySQL Document Store Concepts for PHP Developers

Docstore versus traditional MySQL

I have had quite a few questions from PHP Developers who are very interested in the new MySQL Document Store versus the traditional use of MySQL with PHP. You used to have to write queries in Structured Query Language (SQL) by outing them in strings withing your PHP code. In the past I have heard many folks complain about having to use a programming language within a programming language. And several of you have just skipped all than an started using an ORM. And roughly two percent of the developers at conferences tell me they have had any formal training in SQL, relational theory, or sets!

So what changes with Document Store?

The first and biggest changing is that you stop writing queries in SQL. SQL is surprisingly hard for many programmers. Part of this is that SQL is a declarative language. CSS is also a declarative language. These languages describe what the desired output looks like. Most other languages assemble the parts to get the desired output.

The second is that you your code stops looking like string handing routines. Compare $result = $mysqli->query("SELECT doc FROM countryinfo WHERE _id='USA'")) of the traditional embedded SQL code embedded in the PHP code to $result = $collection->find('_id = "USA"')->execute() of the Document store.

Traditional

Document Store

<?PHP // Connection parameters $host='127.0.0.1'; $user='root'; $pass='hidave'; $db = 'world_x'; // connect to database server $mysqli = mysqli_connect('localhost','root','hidave'); // Choose schema $mysqli->select_db('world_x'); // send SQL query if ($result = $mysqli->query("SELECT doc FROM countryinfo WHERE _id='USA'")) { $row = mysqli_fetch_row($result); var_dump($row); /* free result set */ $result->close(); } $mysqli->close(); ?>

<?PHP // Connection parameters $user = 'root'; $passwd = 'hidave'; $host = 'localhost'; $port = '33060'; $connection_uri = 'mysqlx://'.$user.':'.$passwd.'@'.$host.':'.$port; // Connect as a Node Session $nodeSession = mysql_xdevapi\getNodeSession($connection_uri); // Choose schema $schema = $nodeSession->getSchema("world_x"); // Specify collection to use $collection = $schema->getCollection("countryinfo"); // Find desired record $result = $collection->find('_id = "USA"')->execute(); // Fetch/Display data $data = $result->fetchAll(); var_dump($data); ?>

Choice

The good old mysqli extension is still a powerful way of getting data in and out of a MySQL server. But now you have another option that just may fit better with your programming paradigm.

How to Use PHP and MySQL Document Store

PHP Developers can now try the MySQL Document Store by using the MySQL X DevAPI for PHP PECL Extension. Developers in other languages have had access for a while but now PHP coders can get in on the action and use the MySQL Document Store.

What Does the Code Look Like?

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

// 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);
?>

Well, PHP code is PHP code. The big changes is that the developer no longer needs to use Structured Query Language to talk with the database. Now one connects to the schema of choice, sets the collection to use used, and then finds the record(s) of choice. Zero SQL involved.

PECL Extension

PECL is the PHP Community Library which houses all sorts of treasures. Now it also houses the MySQL XDevAPI extension. I have had the best luck with downloading the software and building it on my system following the directions in the README file. This is not an easy build but keep plugging and you will get it built.

Output

dstokes@davelaptop:~/phpxdev$ php 001.php
mysqlx://root:hidave@localhost:33060
array(1) {
  [0]=>
  array(7) {
    ["GNP"]=>
    int(8510700)
    ["_id"]=>
    string(3) "USA"
    ["Name"]=>
    string(13) "United States"
    ["IndepYear"]=>
    int(1776)
    ["geography"]=>
    array(3) {
      ["Region"]=>
      string(13) "North America"
      ["Continent"]=>
      string(13) "North America"
      ["SurfaceArea"]=>
      int(9363520)
    }
    ["government"]=>
    array(2) {
      ["HeadOfState"]=>
      string(14) "George W. Bush"
      ["GovernmentForm"]=>
      string(16) "Federal Republic"
    }
    ["demographics"]=>
    array(2) {
      ["Population"]=>
      int(278357000)
      ["LifeExpectancy"]=>
      float(77.099998474121)
    }
  }
}

Next Time

More PHP and MySQL Document Store code!

Indexing the MySQL Document Store

Indexing and the MySQL Document Store

The MySQL Document Store allows developers who do not know Structured Query Language (SQL) to use MySQL as a high efficient NoSQL document store. It has several great features but databases, NoSQL and SQL, have a problem searching through data efficiently. To help searching, you can add an index on certain fields to go directly to certain records. Traditional databases, like MySQL, allow you to add indexes and NoSQL databases, for example MongoDB, lets you add indexes. The MySQL Document Store also allows indexing.

So lets take a quick look at some simple data and then create an index.

mysql-js> db.foo.find()
[
    {
        "Name": "Carrie",
        "_id": "888881f14651e711940d0800276cdda9",
        "age": 21
    },
    {
        "Name": "Alex",
        "_id": "cc8a81f14651e711940d0800276cdda9",
        "age": 24
    },
    {
        "Last": "Stokes",
        "Name": "Dave",
        "_id": "davestokes"
    }
]
3 documents in set (0.01 sec)

mysql-js> db.foo.createIndex("ageidx").field("age","INTEGER", false).execute()
Query OK (0.01 sec)

The _id field was already indexed by default and I chose the age key for a new index. By the way you can crate UNIQUE and NON UNIQUE indexes. The arguments for the createIndex function are as follows. The first is the key in the JSON data to index. Second comes the index data type and age is an integer. And the third specifies if NOT NULL is supported and setting it to false means the column can contain NULL. BTW note that the last record has no age key which would be noted as a null; so if some of your records do not have the key to be indexed you should have this set to false.

So What Happened?

So lets take a look at what happened behind the scenes, using SQL.

mysql> DESC foo;
+------------------------------------------------+-------------+------+-----+---------+-------------------+
| Field                                          | Type        | Null | Key | Default | Extra             |
+------------------------------------------------+-------------+------+-----+---------+-------------------+
| doc                                            | json        | YES  |     | NULL    |                   |
| _id                                            | varchar(32) | NO   | PRI | NULL    | STORED GENERATED  |
| $ix_i_F177B50B40803DD7D3962E25071AC5CAA3D1139C | int(11)     | YES  | MUL | NULL    | VIRTUAL GENERATED |
+------------------------------------------------+-------------+------+-----+---------+-------------------+
3 rows in set (0.00 sec)

A VIRTUAL generated column was created. You may recall that virtual generated columns are not created until referenced, hence do not take up the space of a stored generated column. So what if $ix_i_F177B50B40803DD7D3962E25071AC5CAA3D1139C is not as human friendly as ageidx.

So lets try a search.

mysql-js> db.foo.find("age = 24")
[
    {
        "Name": "Alex",
        "_id": "cc8a81f14651e711940d0800276cdda9",
        "age": 24
    }
]
1 document in set (0.00 sec)

Sadly there is no corresponding function to the EXPLAIN SQL command. Which means there is no nice and easy way to see how much the index gains us in terms of performance.

Drop the index

But what if you want to remove that new index? Well, it is as simple as creating the index in the first place.

mysql-js> db.foo.dropIndex("ageidx").execute()
Query OK (0.01 sec)

Update on MySQL Document Store and Node.JS

Opps!

I admit my JavaScript skills are rusty and dusty. Plus I am new to Node.JS. So yesterdays blog drew some very helpful comments from two of MySQL's best. Johannes Schlüter and Rui Quelhas let me know there was a better way to code the example. Much thanks to them.

Better Example

// Simple example to grap one record and print it
const mysqlx = require('@mysql/xdevapi');
const options = {
  host: 'localhost',
  port: 33060, // should be a number
  dbUser: 'root',
  dbPassword: 'Hell0Dave!'
};

mysqlx
  .getSession(options)
  .then (session => {
  var schema = session.getSchema('world_x');

//equivilent of SELECT doc FROM countryinfo where _id = 'USA'
  var coll = schema.getCollection('countryinfo');
  var query = "$._id == 'USA'";

      // Print doc 
     return Promise.all([
       coll.find(query).execute(function (doc) {
       console.log(doc);
      }),
      session.close()
     ]);
    })
    .catch(err => {
        console.log(err.message);
        console.log(err.stack);
    });

Modern Code

This cleans up the error catching and the Promise.all helps put all the heavy lifting together.

And the PHP XDevAPI is in PECL

And you can access the XDevAPI from PHP using the PECL Extension!

MySQL Document Store and Node.JS

JavaScript and MySQL

Last time I looked at using the new MySQL Shell in JavaScript mode to access documents. I promised that I would look at indexes this time but a few folks wanted to see how to use the MySQL Document Store with Javascript -- the language not the MySQL Shell mode-- specifically with the Node.JS connector. And they ask for a very simple example. So Indexes in the future, Node.JS now.

So this is a simple on using the MySQL Document Store from Node.JS.

Install

Download the Node.JS connector and then follow the very simple installation instructions. And use your OS's instictions for installing Node.JS.

The documentation on using the Node.JS Connector with the XDevAPI is not written for folks just starting with either the API or Node.JS.

Example

const mysqlx = require('@mysql/xdevapi');

mysqlx.getSession({             // Create session
  host: 'localhost',
  port: '33060',
  dbUser: 'root',
  dbPassword: 'Hell0Dave!'
}).then(function (session) {    // USE world_x
  var schema = session.getSchema('world_x');

//equivilent of SELECT doc FROM countryinfo where _id = 'USA'
  var coll = schema.getCollection('countryinfo');
  var query = "$._id == 'USA'";

      // Print doc 
  coll.find(query).execute(function (doc) {
    console.log(doc);
  })
  .catch(function (err) {
    console.log(err.message);
    console.log(err.stack);
  });
  session.close();
}).catch(function (err) {
  console.log(err.message);
  console.log(err.stack);
});

Output

node countryinfo.js
{ GNP: 8510700,
  _id: 'USA',
  Name: 'United States',
  IndepYear: 1776,
  geography: 
   { Region: 'North America',
     Continent: 'North America',
     SurfaceArea: 9363520 },
  government: 
   { HeadOfState: 'George W. Bush',
     GovernmentForm: 'Federal Republic' },
  demographics: { Population: 278357000, LifeExpectancy: 77.0999984741211 } }

Wrap-up

So that is a simple of an example that I could think of. I plan to explore more of using the XDevAPI (and fingers crossed the PHP version appears soon) and let me know if there are anything you want to see. And I will get to indexes while I am in Glasgow next week.

MySQL Document Store: Getting Started

MySQL's new NoSQL database is quite a big change for all used to using a SQL speaking relational database. The Document Store makes use of the new MySQL Shell, the new X Protocol, and the new X Devapi to provide a NoSQL document storage service. This provides a way for developers to have a schema-less, NoSQL data store to build services and applications is a quick fashion without having to design a schema, normalize data, develop queries in SQL, or any of the rest of the overhead needed to create a relational databases. Behind the scenes and invisible to the developer is that the schema-less data is stores in a JSON type column in a MySQL instance.

Please note that I am using JavaScript Mode (instead of SQL or Python) for these examples. No special reason.

First Step

The new MySQL Shell is a separate install from the server software. Once installed it will provide the ability to use MySQL as a document store.

The db object is how we can communicate to the server and the world_x data. Now we can create a simple collection.

Whoa! Something is wrong, isn't it?

The Document Store adds an _id column automatically. You can specify your own _id but be warned it is defined by default as an UNIQUE index, so no duplicates. The InnoDB storage engine desires a index and will pick a column to index if you do not specify one (usually not a great choice in a column BTW). But JSON columns can not be directly indexed. But do not worry about the details but do note that the _id column is there.

Also notice the order of the data returned. The MySQL JSON data type organizes the data in a special binary format for faster look ups and the side effect is the alphabetizing of the keys in the key/value pairs.

Filters

Filters can be added to find specific records and to return just the desired information (default is like a SELECT *).

You can bin values to variables to separate the data from the condition.

Modify Data

If you are used to UPDATE set x=y WHERE z=n then the next part may look a little odd.

You can add more than simple key/value pairs. Here is an example of adding an array.

And of course you need the compliment to set, unset.

We can also add to or remove items for arrays within the data. The $ is short hand for the 'current document'.

And of course db.foobar.remove() would delete all the records in the collection.

Next Time: Indexes and Documents

This time we skimmed the surface of the MySQL Document Store but next time we will look at getting some extra performance by using indexes.