12
Programming with the MySQL Document Store
For decades, developers have needed to embed SQL as strings in their code or use object-relational mapping (ORM) to be able to use a database. The SQL strings are often esthetically objectionable, sitting in the middle of a beautifully constructed modern program written in a modern programming language. ORMs are often another complexity that can be avoided if developers would learn to write SQL properly. However, there is very little training in SQL, the relational model, or even set theory for most programmers. SQL is a powerful computer language, but very few attempt to master it, even if they seek high-performing queries.
Developers can use the MySQL Document Store from many programming languages without requiring embedded SQL strings, ORMs, or intensive study in relational databases. It takes away the esthetic complaints and enables those without SQL skills to use the power of MySQL.
The X DevAPI includes connectors for most languages. The big change that programmers will quickly notice is that there are no messy strings of SQL queries in the code. Much of the approach is the same as the traditional programming methodology—authentication to server, designating a schema, issuing a query, and returning the results—but the code looks much cleaner.
As of this writing, MySQL provides connectors for Java (Connector/J), C++, Node.JS, .Net, and Python. A PHP Extension Community Library (PECL) extension for PHP is available. The MySQL connectors are available from the MySQL web site, and the PHP PECL extension is available from the PECL.PHP.Net web site. More connectors may be available at a later date.
Programming Examples
Learning to program has a steep learning curve. Learning to program with a new paradigm can be as difficult. The MySQL Document Store is a big shift for those who are used to embedding SQL queries in strings in their code. Those who are starting to work with MySQL who never used embedded strings could find the steps in working with a database—connecting, authorization, linking up to a schema, query execution, and the return of the data—a strange new phenomena. To help ease both groups into working with the MySQL Document Store, the following simple programs are provided for the reader to copy and hopefully enhance.
Python Example
Example 12-1 is in Python but is a typical example of the coding style when using the X DevAPI.
Example 12-1 Using the MySQL Document Store with the MySQL X DevAPI Python Connector
Node.JS Example
Similar code in Node.JS will also seem very familiar to you after reading Chapter 11. The language differences between Node.JS and Python are still evident, but the X DevAPI code—getCollection(), find()—remains the same.
Example 12-2 The equivalent code in Node.JS retains the familiar X DevAPI function calls and is very similar to the code written in Python in Example 12-1.
PHP Example
PHP is a very popular web programming language, and once again the code looks similar to previous examples.
Example 12-3 The X DevAPI calls retain a familiar format despite the code now being in PHP.
Traditional SQL vs. MySQL Document Store
The MySQL Document Store also enables developers to choose between the traditional SQL approach and the MySQL Document Store approach. Example 12-4 shows the same PHP code in both formats.
Example 12-4 The same program written in PHP with traditional SQL and MySQL Document Store
Developers can use the traditional SQL, the MySQL Document Store, or both. It would not be a good programming practice to combine both.
The MySQL Shell and JavaScript
The new MySQL Shell (mysqlsh) also has modes for JavaScript and Python. It is very easy to start up the shell and simply enter code. It is also very simple to use the built-in JavaScript or Python interpreters with the MySQL shell to store JSON documents or programmatically access data.
Example 12-5 An example of using the JavaScript interpreter built into the MySQL shell programmatically
Relational Tables
The MySQL Document Store can also be used to access relational tables or to treat collections as tables. The following examples use the world_x database and assume the user has connected successfully to the MySQL Server. The db object still refers to the schema selected.
Example 12-6 Similar in fashion to addressing a collection, relational tables can be accessed from the MySQL Document Store.
But collections and relational tables are not the same. Relational tables have their own set of functions.
Example 12-7 The getTables() function is used to find relational tables, while getCollections() is used to find collections.
The relational functions for CRUD (Create, Replace, Update, and Delete) are named insert(), select(), update(), and delete(). These are analogous to their SQL commands.
Filtering commands are similar to the SQL commands.
Example 12-8 Filtering queries for relational tables are similar to other MySQL Document Store features but look more like their SQL variants.
Both Relational and Document
It is possible to use the MySQL Document Store to access document data in relational tables. This is done very easily with the -> operator or JSON_EXTRACT().
Example 12-9 This query shows how to extract JSON document data from a relational table.
Document as Relational
To complete the treating of document data as relational data, the MySQL Document Store also provides the ability to cast documents as tables.
Example 12-10 The countryinfo collection has been cast as a relational table, and the query treats the data as a relational table with JSON data inside.
