Miami and Santo Domingo with MySQL Document Store

The next two weeks are a big change for me compared to the last two weeks.  The last two weeks I was in Belgium and now I am in Miami before heading to the Dominican Republic.  The one thing in common is that I am speaking on the MySQL Document Store.

SunshinePHP has become a favorite conference for many PHP developers for many reasons and this morning I will be running a three hour tutorial on using the PECL MySQL X DevAPI and the MySQL Document Store with PHP and then have a one hour talk later in the show. Plus I am joined by the amazing Kathy Forte who will speak on Driving Innovation With MySQL 8.0 and Docker.

Pycaribbean is a new show hoping to foster a growing developer community into the area.  And i will be showing how to use the MySQL Document Store with Python.

MySQL Books - 2018 has been a very good year

Someone once told me you can tell how healthy a software project is by the number of new books each year.  For the past few years the MySQL community has been blessed with one or two books each year. Part of that was the major shift with MySQL 8 changes but part of it was that the vast majority of the changes were fairly minor and did not need detailed explanations. But this year we have been blessed with four new books.  Four very good books on new facets of MySQL.

Introducing the MySQL 8 Document Store is the latest book from Dr. Charles Bell on MySQL.  If you have read any other of Dr. Chuck's book you know they are well written with lots of examples.  This is more than a simple introduction with many intermediate and advanced concepts covered in detail.

Introducing the MySQL 8 Document Store

MySQL & JSON - A Practical Programming Guide by yours truly is a guide for developers who want to get the most of the JSON data type introduced in MySQL 5.7 and improved in MySQL 8.  While I love MySQL's documentation, I wanted to provide detailed examples on how to use the various functions and features of the JSON data type. 

MySQL and JSON A Practical Programming Guide

Jesper Wisborg Krogh is a busy man at work and somehow found the time to author and co-author two books.  The newest is MySQL Connector/Python Revealed: SQL and NoSQL Data Storage Using MySQL for Python Programmers which I have only just received.  If you are a Python Programmer (or want to be) then you need to order your copy today.  A few chapters in and I am already finding it a great, informative read.

MySQL Connector/Python Revealed

Jesper and Mikiya Okuno produced a definitive guide to the MySQL NDB cluster with Pro MySQL NDB Cluster.  NDB cluster is often confusing and just different enough from 'regular' MySQL to make you want to have a clear, concise guidebook by your side.  And this is that book.

Pro MySQL NDB Cluster

Recommendation

Each of these books have their own primary MySQL niche (Docstore, JSON, Python & Docstore, and NDB Cluster) but also have deeper breath in that they cover material you either will not find in the documentation or have to distill that information for yourself.  They not only provide valuable tools to learn their primary facets of technology but also provide double service as a reference guide. 

PHP and MySQL Without the SQL

Embedding Structured Query Language (SQL) within PHP, or other programming languages, has been problematic for some. Mixing two programming languages together is just plainly not aesthetically pleasing. Especially when you have a declarative language (SQL) mixed with a procedural-object oriented language. But now, with the MySQL XDevAPI PECL extension, PHP developers can now stop mixing the two languages together together.

MySQL Document Store

The MySQL Document Store eliminates the heavy burden for SQL skills. It is designed to be a high speed, schema-less data store and is based on the MySQL JSON data type. This gives you roughly a gigabyte of store in a document format to do with as needed. So you do not need to architect you data before hand when you have no idea how it will evolve. No need to normalize your data. Now behind the scenes is a power MySQL database server but you are no longer writing SQL to use it!

But Is The Code Ugly?

If you looked at previous editions of this blog then you have seen examples of using the MySQL XDevAPI PECL extension. There is another example below of how to search for the information under various keys in a JSON document. The great news is that the code is all very modern looking PHP with no messy SQL statements thumb-tacked onto the code. This should ongoing support by those with little or no SQL skills.

Previous you would have had to stick SELECT JSON_EXTRACT(doc,'Name') AS 'Country', JSON_EXTRACT(doc,geography) as 'Geo', JSON_EXTACT(doc,'geography.Region) FROM world_x WHERE _id = "USA" as a string in the PHP code. If you prefer the -> operator to replace JSON_EXTRACT, the code can be trimmed down to SELECT doc->"$.Name" AS 'Country', doc->"$.geography" AS 'Geo', doc->"$.geography.Region" FROM world_x WHERE _id = "USA".

But the XDevAPI simplifies these queries into $result = $collection->find('_id = "USA"')->fields(['Name as Country','geography as Geo','geography.Region'])->execute();. This is much easier to understand than the previous two queries for most. And this example shows how to chain down the document path as it specifies all of the geography hey's values and also just the data under geography.Region. It also show how to alias columns from the document store to a label of the developers choice.

#!/usr/bin/php
<?PHP
// Connection parameters
  $user = 'root';
  $passwd = 'hidave';
  $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");

// Query the Document Store
  $result = $collection->find('_id = "USA"')->fields(['Name as Country','geography as Geo','geography.Region'])->execute();

// Fetch/Display data
  $data = $result->fetchAll();
  var_dump($data);
?>

And The Output

mysqlx://root:hidave@localhost:33060
array(1) {
  [0]=>
  array(3) {
    ["Geo"]=>
    array(3) {
      ["Region"]=>
      string(13) "North America"
      ["Continent"]=>
      string(13) "North America"
      ["SurfaceArea"]=>
      int(9363520)
    }
    ["Country"]=>
    string(13) "United States"
    ["geography.Region"]=>
    string(13) "North America"
  }
}

User Guide

The MySQL Shell User Guide is a great place to start learning how to interactively start using the Document Store.

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.