BT

Facilitating the Spread of Knowledge and Innovation in Professional Software Development

Write for InfoQ

Topics

Choose your language

InfoQ Homepage News MySQL Offers a REST API

MySQL Offers a REST API

Leia em Português

Bookmarks

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

Adoption
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.

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

Community comments

  • Wonderful

    by Mark Tetrode,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    Nice

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

  • Re: Wonderful

    by David Perfors,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    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,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    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,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    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,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    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

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

BT