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.

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.

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.

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.

There is even a short cut syntax for querying JSON:

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:

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:

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.

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 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:

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

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.


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, …
  • 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:

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!

14 thoughts on “MySQL 5.7.12 – Part 3: More Than “Just” SQL?

  1. When all sessions are stateless then all transactions must finish in one round trip. For a lot of workloads that isn’t possible unless there is good support for running server side logic. Is MySQL Stored Procedures going to be the answer for applications that require atomicity across multiple operations with some business logic in between operations?

    Requiring the _id field means we can’t do a composite PK and must do a surrogate PK. What had been a composite PK now becomes a unique secondary index. So you have just increased the number of indexes my app requires. This makes Linkbench much less efficient on MongoDB than on MySQL –

    1. You are correct Mark: you are spotting an error in my blog posting. I should not have put it that way.

      We default to autocommit but do allow standard transactions to spawn multiple commands. There’s a startTransaction(), commit() and rollback().

      What we do avoid, however, is stuff like stored procedures or SQL state variables when working the CRUD API. The CRUD API intentionally cuts you off from some selected functionality.

      Yes, _id as a PK is a limitation. As I write this, I start to wonder if that intentional limitation was required. We shall see wjat the next iteration of the API brings. For now, it is a limitation.

  2. What I don’t understand is, when there is a multi-part blog post, why is there not a link to the earlier articles? I would be interested in reading this starting with the first one but finding it should be a simple click…

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter * Time limit is exhausted. Please reload CAPTCHA.