MySQL Offers a REST API
Learn about the benefits of in-memory data grids along with strategies for using them for data distribution and availability, hybrid transactional/analytical processing, and more.
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
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:
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:
This will open up many exciting possibilities. For example for hackers...
That's a wonderful feature.
Triguna Mutuguppe Sripathi
Re: That's a wonderful feature.
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
• UPDATE, REPLACE, INSERT, DELETE
• 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