BT
x Share your thoughts on trends and content!

MySQL Offers a REST API

by on Sep 30, 2014 |

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:

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

Rate this Article

Relevance
Style

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

Nice

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:
• SELECT
• 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 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.
www.zazler.com/

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
General Feedback
Bugs
Advertising
Editorial
Marketing
InfoQ.com and all content copyright © 2006-2016 C4Media Inc. InfoQ.com hosted at Contegix, the best ISP we've ever worked with.
Privacy policy
BT

We notice you're using an ad blocker

We understand why you use ad blockers. However to keep InfoQ free we need your support. InfoQ will not provide your data to third parties without individual opt-in consent. We only work with advertisers relevant to our readers. Please consider whitelisting us.