MySQL :: MySQL 5.7.12 - Part 3: More Than "Just" SQL?

If you have been following our multi-part blog-post series around MySQL 5.7.12 so far you have heard on term a lot: “X”. So what does X stand for? A very good question, let’s see if Part 3 of the blog-post series on the MySQL Document Store will help you finding more about that.

The X Plugin, the X Protocol and the X DevAPI that we are going to discuss in this post mark a new milestone among MySQLs various query interfaces. Some six years ago alternative query interfaces for MySQL started to emerge. The optional MySQL Server X Plugin is the latest one. It implements its own client server protocol called the X Protocol. Application clients and their drivers feature the alternative protocol and a new X DevAPI programming API. The X DevAPI is an alternative way of acessing MySQL. The CRUD-style API unifies JSON document and table access, and it includes SQL support. The API features a popular fluent interface style and aims to be particularly easy to use yet powerful.

// Create a new Collection object that can hold JSON documents
news_collection = session.getSchema('test_db').createCollection("news");

// Adding documents to the collection
news_collection.add({published:"2016-04-11", title: "MySQL 5.7.12 released"});
news_collection.add({published:"2016-04-11", 
  title: "Forecast: sunny", valid_until: "2016-04-12"});

// Running a find command on the collection
news_collection.find('title like :t').bind('t', 'MySQL%');

// Create a new Collection object that can hold JSON documents

news_collection = session.getSchema('test_db').createCollection("news");

// Adding documents to the collection

news_collection.add({published:"2016-04-11", title: "MySQL 5.7.12 released"});

news_collection.add({published:"2016-04-11",

title: "Forecast: sunny", valid_until: "2016-04-12"});

// Running a find command on the collection

news_collection.find('title like :t').bind('t', 'MySQL%');

A complete offering

Modern, fluent, method chaining, expressive, intuitive, JSON, SQL – that’s at the surface. What puts the X stack miles ahead of all previous alternative query interfaces for MySQL is the combined power of lessons learned in the past. The X DevAPI sees wide cross product support. Developers can use the same syntax and rely on the existance of virtually the same feature set in may products they work with on a day to day basis. If you want to draft a JavaScript or Python code snippet interactively using try and error, run it on the MySQL Shell. Connectors have been extended to include the X DevAPI. Some development tools feature it too.

X DevAPI
\|
MySQL Connector/J, MySQL Connector/.NET, MySQL Connector/Node.js
MySQL Shell (JavaScript, Python), MySQL for Visual Studio (.NET)
\|
X Protocol
X Plugin
MySQL Server

A new release model enables frequent releases of the X stack independent of MySQL server releases. Clients can innovate independently of the server. The stability of the server, a key strength of MySQL, is not impacted by any of the latest developments. What we do is add a client interface in addition to SQL, the well known SQL APIs, all the existing MySQL Connectors and the MySQL Memcache plugins (InnoDB, MySQL Cluster).

X DevAPI	Well known SQL APIs, e.g. JDBC, ODBC, …	Memcache API
\|		\|
MySQL Connectors		3rd party drivers
MySQL Tools
\|	\|	\|
X Protocol	MySQL Client/Server Protocol	Memcache Protocol
X Plugin	(n/a – built-in)	Memcache Plugins

Such a major effort would not have been undertaken if there was not a set of strong beliefs behind.

Cross(X)over between relational and document model

We seek to deliver an API which combines elements of the relational database model with elements of storing JSON documents. Working with schemaless data serialized in JSON has become very popular in recent years. Schema flexibilty allows data-first development models. For example, application developers do not need to define the very details of the data to be stored before it can be saved, customers can define the details as they work with the application developers in an iterative fashion to model an application. In other areas a mode-first development model may fit the brief better. The X DevAPI supports both working with schemaful relational tables and schemaless collections of JSON documents. The following example shows how to use the X DevAPI in the new MySQL Shell.

// Inserting a row into the news table
db.news.insert("published", "title")
  .values("2016-04-12", "X DevAPI");

// Querying data from the news table
db.news.select(["published", "title"])
  .where("published < now()");

// Inserting a row into the news table

db.news.insert("published", "title")

.values("2016-04-12", "X DevAPI");

// Querying data from the news table

db.news.select(["published", "title"])

.where("published < now()");

This alone does not explain why we introduce a new API. In MySQL 5.7.8 we introduced a set of SQL functions to work on JSON values. The functions are modeled after an ANSI/ISO SQL standard proposal for JSON. A decidated SQL data type for JSON values and new indexing methods ensures good performance. Relational database enriched with JSON value support, job done? All the JSON functionality is accessible through SQL as well. The following example shows the new MySQL Shell in SQL mode.

mysql-sql> SELECT c
       ... FROM jemp
       ... WHERE JSON_EXTRACT(c, "$.id") > 1
       ... ORDER BY JSON_EXTRACT(c, "$.name");

mysql-sql> SELECT c

... FROM jemp

... WHERE JSON_EXTRACT(c, "$.id") > 1

... ORDER BY JSON_EXTRACT(c, "$.name");

There is even a short cut syntax for querying JSON:

mysql-sql> SELECT c
       ... FROM jemp
       ... WHERE c->"$.id" > 1
       .. ORDER BY c->"$.name";

mysql-sql> SELECT c

... FROM jemp

... WHERE c->"$.id" > 1

.. ORDER BY c->"$.name";

The extra bit atop of SQL: developer convenience

Job done, and there is nothing wrong using SQL. SQL is the primary query language for MySQL. However, some developers prefer using a query builder over writing SQL statements, and they have previous experience with the concept of query builders. Fluent APIs are a great match for the task. Fluent APIs produce code that reads like human language. Just like in the following X DevAPI example:

jemp.find("id > 1").sort(["name"]);

1	jemp.find("id > 1").sort(["name"]);

The pure SQL approach also lacks the tiny extra step to make the JSON value appear like a native data type in the client application code. Compare the SQL syntax for JSON path expressions to access JSON values with the more intuitive approach of the X DevAPI for accessing JSON documents. We believe it depends on the job which one is better. It is up to the developer to choose between the slightly higher level abstraction of the X DevAPI, or using SQL. If you like SQL better, no problem. The X DevAPI features SQL execution as well. And, it has to: not all SQL features have a counterpart in the API.

It is not about syntactical sugar

What may seem like syntactical sugar to attract developers has some deep technical advantages. The introduction of a new API supported by many drivers gives us the option to control the features of the query interface. We can, for example, limit features to prevent people using functionality whose performance likely would not meet their expectations.

A SQL inspired new query language would have given us the same benefit. We opted for the API approach to avoid query parsing. Database servers are hard to scale. SQL parsing contributes significantly to the execution time of simple queries. This is one reason why the HANDLER statement is faster than SELECT and why the Memcache interface outperforms SELECT. In either case less or no parsing needs to be done. X DevAPI clients do send abstract query representations to the X Plugin to save some time, however, performance was no major objective for the first release. Using abstract representations it is also good for security. Commands and parameters are seperated from one another making injection attacks more difficult.

That’s why. Let’s talk how, let’s have a walk through the API.

X DevAPI walk through

First things first: connecting to MySQL. Below is C# code snippet for connecting the a MySQL Server with the X Plugin installed and listening to its default port 33060:

string schemaName = "world_x";
// Define the connection string
string connectionString = "server=localhost;port=33060;uid=test;password=test";
XSession session = MySqlX.GetSession(connectionString);
// Get the schema object
Schema schema = session.GetSchema(schemaName);

string schemaName = "world_x";

// Define the connection string

string connectionString = "server=localhost;port=33060;uid=test;password=test";

XSession session = MySqlX.GetSession(connectionString);

// Get the schema object

Schema schema = session.GetSchema(schemaName);

These few lines of code unveil two key concepts of the API.

Behind the scene: design decisions

First, the API abstracts the notion of a connection to that of a session. A session encapsulates physical connections. This concept that is crucial for any environment where the creation, selection and life span of a physical connection shall not be a concern of the application developer.

Second, we made sessions stateless. To get access to the schema/database world_x one does change a default schema on the session. Having a default schema would add state to the session. To get access the schema one requests a schema object through session.GetSchema(). State is an undesired property when scaling components. State needs to be maintained an be available to decide on the outcome of an action. Whereas stateless systems can perform actions without knowing anything about their previous activities. A stateless session can, in theory, easily be replaced with another one should it fail.

JSON values can be stored in tables, or get the little extra and become documents. The X DevAPI introduces dedicated storage container for documents, so called collections. At SQL layer all collections are plain tables but from an application developers point of view they are collections with their own semantics. All documents of a collection have a member _id which serves as their unique identifier, their document id. If no _id field and value has been given by the application, the Connectors will automatically add one to the document. This is the little extra atop of SQL the X DevAPI has to offer today.

// Create a new collection "my_collection"
var myColl = myDb.CreateCollection("my_collection");
// Insert documents
myColl.Add(new { name = "Sakila", age = 15}).Execute();
myColl.Add(new { name = "Susanne", age = 24}).Execute();
myColl.Add(new { name = "Mike", age = 39}).Execute();

// Create a new collection "my_collection"

var myColl = myDb.CreateCollection("my_collection");

// Insert documents

myColl.Add(new { name = "Sakila", age = 15}).Execute();

myColl.Add(new { name = "Susanne", age = 24}).Execute();

myColl.Add(new { name = "Mike", age = 39}).Execute();

Is CRUD good enough?

To work with the data the API features basic CRUD operations: create, read, update and delete. To highlight the semantical difference between working with tables and the logical construct of collections, different method names have been choosen when working with either one.

	Table	Collection
Create	`table.insert()`	`collection.add()`
Read	`table.select()`	`collection.find()`
Update	`table.update()`	`collection.modify()`
Delete	`table.delete()`	`collection.remove()`

A CRUD API can certainly get many basic tasks done but can be feature constrained. The first beta release of the X DevAPI includes:

CRUD operations
transaction support
asynchronous query execution
query parameter binding for CRUD and SQL
query expressions for CRUD
SQL execution

Asynchronous query execution

A standout feature is the asynchronous query execution. Clients are not blocked waiting for the result of an execution. All MySQL Connectors that feature the X DevAPI provide the same core feature set which includes asynchronous query execution. The overall syntax is identical for all of them but the very syntax details follow the language native patterns. Java developers use Futures:

// execute the query asynchronously, obtain a future
CompletableFuture rowsFuture = employees.select("name", "age")
.where("name like :name")
.orderBy("name")
.bind("name", "m%").executeAsync();

// execute the query asynchronously, obtain a future

CompletableFuture rowsFuture = employees.select("name", "age")

.where("name like :name")

.orderBy("name")

.bind("name", "m%").executeAsync();

JavaScript developers use Callbacks or Promises. Callbacks get called whenever a row or document is received. Promises return when all results have been fetched:

var employees = db.getTable('employee');
employees.select('name', 'age')
  .where('name like :name')
  .orderBy('name')
  .bind('m%')
  .execute(function (row) {
    // do something with a row
  });

var employees = db.getTable('employee');

employees.select('name', 'age')

.where('name like :name')

.orderBy('name')

.bind('m%')

.execute(function (row) {

// do something with a row

});

C# users can choose between two language native pattern one of them is using await.

Parameter binding

Parameter binding is a must have for any of todays database APIs to hinder injection attacks. CRUD operations support binding named parameters. Recall that we tackled the problem from ground up and seperate the command from the bound parameters also on the low X Protocol level. Below is a JavaScript example for binding two parameter to a find() operation, fetching the documents and printing them.

var find = myColl.find('name = :param1 AND age = :param2');
find.bind('param1','jack').bind('param2', 17);
var res = finds.execute();
while (doc = res.fetchOne()) {
  print(doc);
}

var find = myColl.find('name = :param1 AND age = :param2');

find.bind('param1','jack').bind('param2', 17);

var res = finds.execute();

while (doc = res.fetchOne()) {

print(doc);

}

Whether you choose to write the entire find() operation in one line or prefer splitting it over multiple lines is mostly a matter of taste. If you do, you can pass the find object as an argument to other methods. If you like to write it like one long sentence, just like this one, you can do so because all languages support method chaining:

var res = myColl.find('name = :param1 AND age = :param2').bind('param1','jack').bind('param2', 17).execute();

Expression syntax

Basic query expressions such as name = :param1 AND age = :param2 have been shown already. A complete discussion is beyond the scope of this blog post because they are not far from the SQL feature set!

Comparison operators: =, !=, …
Mathematical operators: <,>, …
Logical operators: AND, OR, NOT, …
IS NULL|TRUE|FALSE
IN, BETWEEN
LIKE, REGEXP
Function calls
Document path access
…

SQL wins the feature comparison – at a price

The X DevAPI may not get all jobs done, and it does not have to. For example, the X DevAPI CRUD operations cannot call SQL stored procedures. Should you need this functionality, you can fallback to SQL. Below is Java example how to do that:

// Connect to server on localhost
String url = "mysql:x://localhost:33060/test?user=mike&amp;password=s3cr3t!"
NodeSession mySession = new MysqlxSessionFactory().getNodeSession(url);
// Switch to use schema 'test'
mySession.executeSql("USE test");

// In a NodeSession context the full SQL language can be used
mySession.sql("CREATE PROCEDURE my_add_one_procedure " +
  " (INOUT incr_param INT) " +
  "BEGIN " +
  " SET incr_param = incr_param + 1;" +
  "END").execute();
mySession.sql("SET @my_var = ?").bind(10).execute();
mySession.sql("CALL my_add_one_procedure(@my_var)").execute();

// Use a SQL query to get the result
SqlResult myResult = mySession.sql("SELECT @my_var").execute();

// Gets the row and prints the first column
Row row = myResult.fetchOne();
System.out.println(row.getInt(0));

// Connect to server on localhost

String url = "mysql:x://localhost:33060/test?user=mike&password=s3cr3t!"

NodeSession mySession = new MysqlxSessionFactory().getNodeSession(url);

// Switch to use schema 'test'

mySession.executeSql("USE test");

// In a NodeSession context the full SQL language can be used

mySession.sql("CREATE PROCEDURE my_add_one_procedure " +

" (INOUT incr_param INT) " +

"BEGIN " +

" SET incr_param = incr_param + 1;" +

"END").execute();

mySession.sql("SET @my_var = ?").bind(10).execute();

mySession.sql("CALL my_add_one_procedure(@my_var)").execute();

// Use a SQL query to get the result

SqlResult myResult = mySession.sql("SELECT @my_var").execute();

// Gets the row and prints the first column

Row row = myResult.fetchOne();

System.out.println(row.getInt(0));

If you study it closely, you will find a special type of session being used. SQL is statefull and the X DevAPI tries to avoid session state as a design principle. If you want or have to use SQL, you may do so. All the API asks for is establishing a different contract that includes session state by using a special type of session to execute SQL.

Next up is a posting that may say a word or two on the fluent style of the API, and why we think it is such sweet syntactical sugar.

Happy hacking – using the MySQL Document Store!