JSON/Relational Duality for
MySQL REST Service

The MySQL REST SERVICE (MRS) now adds full support for JSON/Relational Duality to combine the benefits of both relational tables and JSON documents, without the tradeoffs of either model.

MySQL Shell for VS Code offers full SQL support for JSON/Relational Duality REST views and an intuitive, easy-to-use JSON/Relational Duality Editor GUI that makes designing your nested JSON documents a bliss.

A new, live generated MRS SDK Client API allows for instant testing and prototyping the JSON/Relational Duality REST Views, directly within MySQL Shell for VS Code.

Install MySQL Shell for VS Code Preview

MRS JSON/Relational Duality support for MySQL

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

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. The new JSON/Relational Duality feature as well as the new MRS SDK Client API make the MySQL REST Service an even better choice for serving JSON Documents to Progressive Web Apps (PWA) or mobile apps.

To read more about MRS, please follow this link to the announcement blog post.

JSON/Relational Duality

JSON/Relational Duality views combine the advantages of using JSON documents with the advantages of the relational model, while avoiding the limitations of each. This breakthrough innovation was first introduced by Oracle Database 23c. Please watch the Introducing Document Relational Duality keynote to better understand the concept of JSON/Relational Duality views.

Now we are bringing this new technology to the MySQL ecosystem as part of the MySQL REST Service.

Duality views combine the advantages of relational schemas with the ease-of-use of document databases. They give your data a conceptual and an operational duality as it is organized both relationally and hierarchically. You can base different duality views on data stored in one or more of the same tables, providing different JSON hierarchies over the same, shared data. This means that applications can access (create, query, modify) the same data as a collection of JSON documents or as a set of related tables and columns, and both approaches can be employed at the same time.

The MySQL REST Service offers full support for duality views. They are used to cover both, the relational use case (1) as well as the document centric use case (2).

1. Make a single relational table or view available via a REST endpoint
   • Exposes the rows of a table as a set of flat JSON documents
   • Allows the application to use a traditional relational approach when needed
2. Create a single REST endpoint for a set of related database schema tables
   • Exposes the related tables as nested JSON objects inside a set of JSON documents
   • Allows the application to take an document oriented approach

The following figure visualizes these two use cases.

In the scope of the MySQL REST Service, JSON-Relational duality views are exposed as REST duality views. These can be created using the CREATE REST DUALITY VIEW MRS DDL statement or interactively using the MRS Object Dialog of the MySQL Shell for VS Code extension.

Once a REST duality view has been created, it is extremely simple to access it using REST. The following workflow applies.

• GET a document from the REST duality view
• Make any changes needed to the document, including changes to the nested JSON objects
• PUT the document back into the REST duality view

The next figure shows a typical JSON document update cycle.

Now, let’s see the MySQL REST Service in action.

Introducing Full SQL Support for MySQL REST Service

The MySQL REST Service can now be fully configured and managed using SQL statements. This makes it even possible to combine an application’s MySQL database schema definitions and its REST service configuration in a single SQL script, radically simplifying the deployment process.

The following example shows the SQL statements to create a REST service and a REST schema for the sakila example database and set them as current objects.

CONFIGURE REST METADATA;
    
    CREATE REST SERVICE /myService;
    
    CREATE REST SCHEMA /sakila FROM `sakila`;
    

The MRS SQL statements are exposed by the new SQL extension interface of MySQL Shell, providing an alternative to using the MRS scripting API or the MySQL Shell for VS Code GUI.

Creating a REST Duality View using SQL

The usage of the CREATE REST DUALITY VIEW statement is straight forward. The following example creates a REST duality view on the sakila.city database table, exposing all columns of the table in the resulting JSON documents.

USE REST SCHEMA /sakila;
    CREATE REST DUALITY VIEW /city
    AS `sakila`.`city`;
    

To expose only a subset of columns or to include nested, referenced tables, a GraphQL object definition can be added.

CREATE OR REPLACE REST DUALITY VIEW /city
    AS `sakila`.`city` {
        cityId: city_id @SORTABLE,
        city: city,
        countryId: country_id
    };
    

The following clip shows the steps to create the REST duality view via SQL in a MySQL Shell for VS Code DB Notebook.

In the next step we want to add the referenced table sakila.country to the REST duality view. This can either be done via SQL using the full power of the GraphQL syntax or via the JSON/Relational Duality Editor. Let’s look at the SQL statement first.

CREATE OR REPLACE REST DUALITY VIEW /city
    ON SERVICE /myService SCHEMA /sakila
    AS `sakila`.`city` {
        cityId: city_id @SORTABLE,
        city: city,
        countryId: country_id,
        country: sakila.country {
            countryId: country_id @SORTABLE,
            country: country
        }
    };
    

The JSON/Relational Duality Editor GUI

REST duality views can either be written in SQL like shown above or designed interactively with the JSON/Relational Duality Editor when using MySQL Shell for VS Code. The JSON/Relational Duality Editor privides a What-You-See-Is-What-You-Get user interface that makes creating complex REST duality views straight forward.

It features an SQL Preview mode that allows to review the generated SQL statement and can be used to edit and expand previously created REST duality views.

The JSON/Relational Duality Editor can be launched from the VS Code sidebar or directly from the TypeScript prompt of a DB Notebook.

The following clip shows how to use the JSON/Relational Duality Editor to design the /city REST duality view.

Integrated Documentation

The MySQL Shell for VS Code extension offers a comprehensive, integrated documentation that helps to learn all about JSON/Relational Duality Views, the new MRS SQL extension statements and the interactive MRS SDK Client API.

Just click on the book icon next to the MySQL REST Service entry in the DATABASE CONNECTIONS view.

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 button below to get started.