MySQL REST Service

The MySQL REST Service (MRS) enables fast and secure HTTPS access for your MySQL data. Implemented as a MySQL Router feature, MRS provides the ability to publish RESTful Web Services for interacting with the data stored in MySQL solutions.

MySQL Shell for VS Code offers an intuitive way to configure MRS and create RESTful Web Services for your application data. Full GUI integration and the embedded, interactive documentation make it easy to expose databases objects.

The MySQL REST Service is ideally suited for Progressive Web Apps (PWAs) and makes serving your MySQL data as JSON documents easier than ever before.

Install MySQL Shell for VS Code Preview

Fast, Secure HTTPS Access for your MySQL Data

Available for your MySQL HeatWave instances and On-Premise MySQL Solutions

Progressive Web Apps (PWA)

The MySQL REST Service is an ideal choice for serving JSON Documents to your Progressive Web Apps (PWA) or mobile apps. It supports popular OAuth2 authentication services and advanced features like row-level security to simplify building PWAs significantly.

MySQL Shell for VS Code ships with a PWA demo app called MRS Notes that showcases many features of the MySQL REST Service. It can be deployed directly from with VS Code and then be uploaded and served by the MySQL Routers.

MRS Architecture Overview

A MRS setup consists of the following elements.

• MySQL Solution (MySQL HeatWave on OCI, MySQL InnoDB Cluster/Set, Standalone MySQL Server), serving the application data
• MySQL Shell & MySQL Shell for VS Code, for configuring and managing MRS, as well as enabling RESTful Web Service support for selected database schema objects
• MySQL Routers, serving JSON data via RESTful Web Services

The following diagram shows a typical MRS architecture.

Configuring MRS and Creating a MRS Service

To configure a MySQL instance for MRS, open the MySQL Shell for VS Code extension and right click on a Database Connection and select Configure Instance for MySQL REST Service Support from the popup menu. This step will create the MRS metadata schema that will store all metadata information about the REST services.

After MRS has been configured, a new REST Service can be added. Open the Database Connection, right click the MySQL REST Service entry and the select Add REST Service… .

Alternatively this can be performed using the MySQL Shell.

MySQL > JS > mrs.configure()
MySQL Rest Data Service configuration.

Checking MRS metadata schema and version...
Creating MRS metadata schema...
The MRS metadata is well configured, no changes performed.

MySQL > JS > mrs.add.service()
Please enter the context path for this service [/mrs]:
Please enter the host name for this service (e.g. None or localhost) [None]:
   1 HTTP
   2 HTTPS

Please select the protocol(s) the service should support [HTTP,HTTPS]: 2
Comments:
   1 Default Service Options for Development
   2 No options
   3 Custom options

Please select how to initialize the options [Default Service Options for Development]: 1

Service /mrs created successfully.

Adding Database Objects to the MRS Service

To add a MySQL database schema to the MRS Service with MySQL Shell for VS Code, simply right click on the schema in the DATABASE CONNECTIONS view and select Add Schema to REST Service… from the popup menu. To make this schema publicly available, remove the checkmark from the Requires Authentication flag. Click OK and the schema will be added to the MRS Service.

In the next step, a MySQL table can be added. Right click on the table in the DATABASE CONNECTIONS view and select Add Database Object to REST Service … from the popup menu. Ensure the desired CRUD operations are selected (READ is selected by default) and again remove the checkmark from the Requires Authentication flag to make the object publicly available.

Bootstrapping and Starting MySQL Router

The MySQL Shell for VS Code extension ships with the MySQL Router included and can be used to bootstrap and starting the MySQL Router. Right click the MySQL REST Service tree item and select Start Local MySQL Router Instance. This will automatically initiate the bootstrap process if it was not completed before.

Enter the DB Connection’s password and a random JSON Web Token (JWT) secret. Please note that this JWT secret always needs to be the same for every MySQL Router instance when deploying multiple routers for the same MySQL solution.

As soon as the MySQL Router is started the debug output can be seen in the VS Code terminal.

Accessing REST End Points in a Web Browser

Now that a REST Service has been created and the MySQL Router has been started we can access the REST endpoints with a web browser. 

Note: To display the JSON documents in a nice way it is recommended to install a web browser plugin like JSON Formatter on Chromium based browsers or JSON Peep on Safari.

Right click on a REST object and select Open REST Object Request Path in Web Browser. If no host name has been specified for the REST Service, https://localhost:8443 will be added automatically and the default web browser will be opened and the JSON result of the GET request will be displayed.

When looking at the JSON result you will notice that – apart from the actual data – navigation links are included. These allow you to navigate the data quickly.

• Adding a Primary Key value to the request path (e.g. /1) will filter the result to a single data set.
• Adding offset and limit as query parameters allow you to page the full data set. The default page size can be set in the REST Schema and REST Object settings.
• MRS supports a JSON based query language to filter down the result set. Please take a look at the documentation for further details.

Reading the Embedded Documentation

To find out more about the MySQL REST Service please see the embedded MRS Developer’s Guide that is shipping with MySQL Shell for VS Code.

Right click on a DB Connection or the MySQL REST Service tree item and select Browse the MySQL REST Service Documentation. This will open the MRS Developer’s Guide directly within VS Code and you can use interactive links there to directly interact with MySQL Shell for VS Code.

Summary

The MySQL REST Service offers a new, powerful feature set for MySQL developers to work with RESTful Web Services and JSON documents. It is tightly integrated into all MySQL Solutions like MySQL HeatWave and InnoDB Cluster/Set.

The MySQL REST Service is available as a Preview today. Click the download buttons below to get started.