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.

SQL over HTTP

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

SQL: SELECT * FROM simple ORDER BY id

URL: http://127.0.0.1:8080/sql//SELECT+%2A+FROM+simple+ORDER+BY+id

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.

CRUD over HTTP

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:

protocol://host:port/crud/database/table/primaryKey

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:

protocol://host:port/doc/database/table

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