Restful API

A long time since I posted. Strange times as we are all in lock down. Not a huge difference for me as I generally work from my home office, but the peace and quiet, no planes and few cars is a pleasant change.

Fortunately, software projects do continue and have had an interesting task to tackle in the last few days. With a little more time I thought it would be great to document this (and others) while in lockdown.

The current project is to provide a MySQL Rest Api in minutes around a database of rain data. Sounds straight forward, though the timescales are very tight, thus am looking for a pragmatic and quick solution rather than, perhaps, a beautiful architecture. The MySQL database provides global weather data across a region or town. The full project is to provide the data on mobile devices, in a mobile-friendly way, rather than lines of data or uninteresting charts. My challenge is purely to offer up the data.

With recent experience of the OpenXava framework which took me along the Hibernate, JPA and Tomcat route I was drawn to a JAX-RS approach, probably using Glassfish and Jersey. But, after a quick Google I came across a RESTful Web Services generator for MySQL. A slight reservation, would it perform and do enough of what we needed. ‘Enough of what we need’ is a good question. What do we need the API to do. A good time to ask the question! After a discussion, I found that we have 8 tables, mainly small, though the main data table has 4.5 million rows, definitely a challenge. The large data table would be filtered by location, date and time and type of variable.

To test the new XMysql RestApi generator I downloaded it and installed on a Linux machine. The requirements are very clear, you need Node a Javascript runtime engine installed. Plenty of articles on installing Node on Ubuntu. The installed worked very well and just needed to execute a script to start the generator and xmysql engine. Like all good tools, just typing xmysql gave a useful guide to the input parameters. The above github site also has a useful video showing how easy it is to setup.

Using mainly defaults I started the service

REST API Creation

xmysql -h localhost -u mysqlusername -p mysqlpassword -d schema &

and the service was immediately available on port 3000 on the localhost. Mapping the service to an external URL is neatly explained in an excellent article by Mike Heavers (many thanks!)

Now to test using the api definition on the xmysql node rest api github site immediately returned a list of the first 20 records in JSON format from the countries table. Quite amazing.

There is a popular, though paid for alternative:

Create a MySQL REST API in Minutes with DreamFactory
MySQL is an open-source relational database management system (RDBMS) that is one of the leading solutions for building relational (SQL) databases. MySQL …

restful api