| by Jonathan Allen Follow 641 Followers on Sep 30, 2014. Estimated reading time: 2 minutes |

A prototype of MySQL 5.7 is shipping with an optional component called the MySQL HTTP Plugin. This plugin allows direct access to MySQL via a REST over HTTP interface, eliminating the need for a middle-tier server or database specific drivers.


Actually, there are three interfaces available through this plugin. The first one they talk about in the documentation is the “SQL” endpoint. The endpoint accepts and executes SQL statements passed in via a URL. For example


The results come back in the form of a JSON document. In the “meta” section you’ll get information about each column in the result set. Then there is the “data” section, which is an array of rows. Each row is in the form an array of fields. To save bandwidth and parsing costs, these fields are not named. Instead, you have to infer the names of the fields from the position in the array.


The “CRUD” endpoint is what we would normally think of as REST. For all operations, you must supply the primary key in the URL of the request. This means that you can’t rely on an AUTO_INCREMENT primary key when inserting new rows.

With a PUT request, which is used for insert/update, you must supply a traditional JSON object as the body of the message. Likewise, when you use GET to read a record you will get just a traditional JSON object without any metadata. In theory this reduces the size of the message, but in practice it would probably fit in a single TCP packet either way.

The URL pattern is:


JSON Document over HTTP

The SQL and CRUD interfaces are still based on relational tables. That is to say, there is a well-defined schema of rows and columns. If you need unstructured storage, the “JSON Document” interface is for you.

This interface is based on simple key-value tables. The JSON data is stored in a BLOB column, eliminating the need to map between JSON and MySQL data types. The tables, which are managed by MySQL, can be created by sending a PUT message to a URL using this pattern:


Document tables always use a VARCHAR(36) for the key column. This allows this pattern for CRUD operations:


Rate this Article

Adoption Stage

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Wonderful by Mark Tetrode


This will open up many exciting possibilities. For example for hackers...

Re: Wonderful by David Perfors

That problem is there with every rest api, as long as you secure it correctly, everything will be all right.

That's a wonderful feature. by Triguna Mutuguppe Sripathi

Wow! That's a wonderful feature. Is this applicable for both DDL as well as DML statements or only DML?

Re: That's a wonderful feature. by Jonathan Allen

From the docs...

Most SQL statements that can be executed using a standard MySQL client can also be executed through the HTTP Plugins SQL endpoint. The use of the HTTP protocol causes some limitations. Below is a list of supported statements. The list may not be complete:
• Assorted CREATE, for example but not only, CREATE TABLE
• CALL for functions and procedures. Including procedures that return multiple result sets.
• Specialized statements such as: DO, HANDLER.
• Administrative statements such as: CREATE USER.

SQL as REST software by Kaiko Kaur

If you want something similar but filter out some access (tables, columns or rows) then Zazler is cool tool for that. It allows to send data and query data. It also supports PostgreSQL and SQLite.

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

5 Discuss