Experimental MySQL HTTP API and Ruby

13 May 2015

This article will take 8 minutes to read
(or maybe less, the algorithm treats code as text)

Yes, MySQL has an HTTP API which is:

an experimental feature
it ships as a native plugin only for 5.7 version

Basically, it allows you to work with your database in the following ways:

as an SQL endpoint
as a CRUD endpoint
as a JSON document endpoint

SQL endpoint

It gives an ability to run queries like

GET http://host:port/sql/:database/:query

For example

GET http://localhost:8080/sql/testdb/SELECT+1

is a synonym of SQL’s SELECT 1.

CRUD endpoint

Interface is:

GET http://host:port/crud/:database/:table/:id

And

GET http://localhost:8080/crud/test_db/test_table/101'

produces:

SELECT * FROM `test_db`.`test_table` WHERE `test_table`.`id` = 101

JSON document endpoint

Basically, with this endpoint your table has only 3 fields:

mysql> describe test_db.test_table;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| _id    | varchar(36)         | NO   | PRI | NULL    |       |
| _rev   | bigint(20) unsigned | NO   | PRI | 0       |       |
| _extra | blob                | NO   |     | NULL    |       |
+--------+---------------------+------+-----+---------+-------+

All the data is stored in _extra column, _id is a regular document id, _rev is a revision of your document. There is no schema for API perspective and the documents are versioned. As for me it looks pretty much like CouchDB (of course, without querying the entire documents data :smile:)

MySQL 5.7 installation

It’s dead simple (until you want to install it from source):

Download package from MySQL site

$ wget http://downloads.mysql.com/snapshots/pb/mysql-5.7.5-labs-http/mysql-5.7.5-labs-http-linux-glibc2.5-x86_64.tar.gz

Extract it

$ tar -zxvf mysql-5.7.5-labs-http-linux-glibc2.5-x86_64.tar.gz

Create `mysql:mysql user:group`

$ groupadd mysql
$ useradd -r -g mysql mysql

Link extracted directory

$ ln -s /root/mysql-5.7.5-labs-http-linux-glibc2.5-x86_64 /usr/local/mysql
$ cd /usr/local/mysql

Run `mysql_install_db`

$ bin/mysql_install_db --user=mysql --datadir=/var/lib/mysql

Run MySQL server

$ bin/mysqld_safe --user=mysql --datadir=/var/lib/mysql &

Change root password

cat ~/.mysql_secret
# Here goes your root password!
mysql -uroot -p
# <Paste your root password from the file>
mysql> SET PASSWORD=PASSWORD('root'); # Yes, I'm mad
mysql> exit

Create configuration files and `init` script

$ cp support-files/my-default.cnf /etc/my.cnf
$ cp support-files/mysql.server /etc/init.d/mysql

And change `basedir` and `datadir` in `/etc/init.d/mysql`

# In my case
basedir=/usr/local/mysql
datadir=/var/lib/mysql

Run MySQL server again with configuration files

$ /etc/init.d/mysql start

HTTP API plugin installation

It’s already included into our MySQL installation:

$ ls -l /usr/local/mysql/lib/plugin/libmyhttp.so

But it’s disabled by default, to enable it run

mysql> INSTALL PLUGIN myhttp SONAME 'libmyhttp.so';
...
mysql> SHOW PLUGINS;

Once plugin is enabled, some MySQL variables become accessible:

mysql> show variables like 'myhttp_%';
+----------------------------------+------------------------------+
| Variable_name                    | Value                        |
+----------------------------------+------------------------------+
| myhttp_basic_auth_user_name      | basic_auth_user              |
| myhttp_basic_auth_user_passwd    | basic_auth_passwd            |
| myhttp_crud_url_prefix           | /crud/                       |
| myhttp_default_db                | myhttp                       |
| myhttp_default_mysql_user_host   | 127.0.0.1                    |
| myhttp_default_mysql_user_name   | username                     |
| myhttp_default_mysql_user_passwd | userpass                     |
| myhttp_document_url_prefix       | /doc/                        |
| myhttp_http_enabled              | ON                           |
| myhttp_http_port                 | 8080                         |
| myhttp_https_enabled             | OFF                          |
| myhttp_https_port                | 8081                         |
| myhttp_https_ssl_key_file        | lib/plugin/myhttp_sslkey.pem |
| myhttp_sql_url_prefix            | /sql/                        |
+----------------------------------+------------------------------+

myhttp_basic_auth_user_name - username that should be used for basic HTTP authentication
myhttp_basic_auth_user_passwd - password that should be used for basic HTTP authentication
myhttp_crud_url_prefix - prefix of CRUD endpoint
myhttp_default_db - database that will be used if no database specified in request
myhttp_default_mysql_user_host - MySQL user that will be used for performing query provided with HTTP request
myhttp_default_mysql_user_passwd - password of this MySQL user
myhttp_document_url_prefix - prefix of JSON document endpoint
myhttp_http_enabled - boolean setting to enable/disable non-SSL HTTP API
myhttp_http_port - port of non-SSL HTTP API
myhttp_https_enabled - boolean setting to enable/disable SSL HTTP API (yes, it’s available)
myhttp_https_port - port of SSL HTTP API
myhttp_https_ssl_key_file - path to SSL certificate for SSL API
myhttp_sql_url_prefix - prefix of SQL endpoint

Let’s try it!

$ curl -v localhost:8080
> GET / HTTP/1.1
> User-Agent: curl/7.35.0
> Host: 127.0.0.1:8080
> Accept: */*
>
< HTTP/1.1 404 Not Found
< Cache-control: must-revalidate
< Connection: Keep-Alive
< Pragma: no-cache
* Server MyHTTP 1.0.0-alpha is not blacklisted
< Server: MyHTTP 1.0.0-alpha
< Content-Length: 36
<
{"error":404, "message":"Not Found"}

Well, at least it returns something (be patient, this feature is experimental)

HTTP API plugin configuration

First of all, we need to create MySQL user and use his credentials in configuration file.

mysql> CREATE USER 'username'@'%' IDENTIFIED BY 'userpass';
mysql> GRANT ALL PRIVILEGES ON * . * TO 'username'@'%';
mysql> FLUSH PRIVILEGES;

Then we can change plugin’s settings in /etc/my.cnf to something like:

[mysqld]
myhttp_default_db = myhttp
myhttp_default_mysql_user_name = username
myhttp_default_mysql_user_passwd = userpass
myhttp_default_mysql_user_host = 127.0.0.1
myhttp_basic_auth_user_name = basic_auth_user
myhttp_basic_auth_user_passwd = basic_auth_passwd

Here is how it should look using curl:

$ curl --user basic_auth_user:basic_auth_passwd --url http://localhost:8080/crud/myhttp/simple/1
 {"id":"1","col_a":"Hello"}

Connecting with Ruby

First of all this is an HTTP API, so it’s available for both server-side and client-side.

Playing with CRUD endpoint

If we need to work with MySQL over HTTP API (let’s imagine for a second that this is the only available way to fetch the data) we need something like ActiveResource.

ActiveResource is a gem for building ActiveRecord-like classes that actually fetch data over HTTP API.

Install it:

$ gem install activeresource

And use:

require 'activeresource'

# We should have a table `users` (yes, Rails convention)
class User < ActiveResource::Base
  self.site = 'http://localhost:8080/crud'
  self.user = 'basic_auth_user'
  self.password = 'basic_auth_passwd'
end

user = User.find(1)
user.first_name
# => 'Sherlock'
user.last_name
# => 'Holmes'
user.first_name = 'John'

User.create(first_name: 'John', last_name: 'Watson)
# => ActiveResource::MethodNotAllowed: Failed.  Response code = 405.  Response message = Method Not Allowed.

By default ActiveResource sends POST request to create a new record. However MySQL HTTP API has some other standards and it uses PUT for creating new record. Moreover, its required to specify primary key (id) of the record you want to insert (even if id has auto-increment)

ActiveResource does not allow us to specify HTTP verbs for its API calls source Without this we can’t even create a single record in the database!

Let’s check for alternatives. Her is a popular (according to stars on GitHub it’s even more popular then ActiveResource) alternative with a nice syntax sugar. Here is an example:

$ gem install her

require 'her'

Her::API.setup url: "http://localhost:8080/crud/testdb/" do |c|
  # Basic auth
  c.use Faraday::Request::BasicAuthentication,
    "basic_auth_user", "basic_auth_passwd"

  # Encode request
  c.use Faraday::Request::UrlEncoded

  # Parse response
  c.use Her::Middleware::DefaultParseJSON

  # Adapter
  c.use Faraday::Adapter::NetHttp
end

class User
  include Her::Model
  method_for :create, :put
end

user = User.find(1)
# => instance of user
user.first_name = 'another-name'
user.save
# => false

No errors messages, just 400 Bad Request. The thing is that body should be JSON-encoded, here is my middleware that encodes it:

class MySqlHttpApiFormatter < Faraday::Middleware
  def call(env)
    if env['body'] # this middleware calls also for 'get' requests
      env['body'].delete(:id) # id is already in the url
      env['body'] = env['body'].to_json
    end
    @app.call(env)
  end
end

Her::API.setup ... do |c|
  c.use MySqlHttpApiFormatter
end

user = User.create(first_name: 'Test', last_name: 'User', id: 25)
# => instance of user

And finally it works! The only thing that I personally don’t like in design of this API is that both INSERT and UPDATE have to be called using PUT because it produces query:

REPLACE INTO db.table SET ..., pk = ...

That’s why we have to pass id, create and update actions are combined into single replace (and I don’t see any explanation for that).

Theoretically, this API can be used for both client-side and server-side (but it’s important to make sure that you can allow users to change the data in your database)

Playing with SQL endpoint

Honestly saying nothing interesting.

This API can’t be used for client-side applications because of potential SQL injections (yes, you can restrict user from deleting/updating data and grant only Select_priv privilege, but it still looks dangerous). Even if you are 100% sure that you are completely secured from any application-related vulnerabilities, CRUD API still looks better then passing raw SQL queries over HTTP.
Yes, it’s possible to use it on the server, but for what? mysql2 gem is still much faster (and can be easily combined with ActiveRecord)… nothing to discuss.

Playing with JSON document endpoint

First of all, it looks quite strange comparing with regular MySQL tables. The database that is compatible with this API must be created using the same API. And as I wrote before, it creates 3 fields:

_id - some unique identifier
_rev - revision of document (I can’t call it ‘record’)
_ext - serialized mapping attribute_name => value

Here is how it looks it MySQL:

mysql> select * from json_types\G
*************************** 1. row ***************************
   _id: 1
  _rev: 1
_extra: {"json_null": null, "json_string": "string", "json_number": 123.456, "json_bool": true}
1 row in set (0.00 sec)

CouchDB has similar interface (HTTP API, schema-less documents, MVCC) but it provides one core feature of all databases: ability to run queries. You can’t run any query when your data is serialized (no, I don’t know what is where _rev LIKE '%"some":json_structure%'"). Moreover MVCC for this table is not real :) It stores only latest revision.

From my experience when you need to store schema-less documents you have two options:

Use real schema-less storage (yes, like MongoDB)
Serialize fields that need to be serialized into single column (and Rails provides functionality for this out-of-box: ActiveRecord::Base.serialize) and query the database using non-serialized column.

JSON document API can be useful when you already have MySQL and don’t want to setup any other databases to store tons of unstructured data. Yes, sounds awful, but looks like a true.

Conclusion

HTTP API is a tool, and it solves a lot of possible issues. It’s not released yet (and I suppose it will not be included into default MySQL 5.7 installation), but I’m waiting for it so much. Today, when Single-Page Applications are so popular and Rails application may have only HTTP API, it can simplify our server code. Let’s give it a chance!

Ilya Bylich