This book explains how to install and configure the MySQL REST Service (MRS) as well as how to access the data through REST calls.

Chapter Overview

• MRS Introduction
• MRS Deployment Architecture
• MRS Configuration
• Adding REST Services
• Adding Schemas and Objects to a REST Service
• MRS Examples
  • MRS Notes Examples

Please also see

• MySQL REST Service - SQL Reference - This book discusses the MySQL REST Service SQL Extension.
• MySQL REST Service - Core REST APIs - This book provides examples of using the MySQL REST Service queries and other operations against tables and views after you have REST-enabled them.
• MySQL REST Service - SDK Reference - This book explains how to work with the MRS Software Development Kit and discusses the Client APIs.

1 Introduction to the MySQL REST Service

Welcome to the MySQL REST Service (MRS). It provides a fast and powerful way to serve data to client applications via a HTTPS REST interface.

To start with a hands-on approach, see the MRS Notes Example and follow the instructions for Build and Deployment there.

This section provides an overview of the MySQL REST Service and its features.

About the MySQL REST Service

The MySQL REST Service (MRS) offers HTTPS REST access to selected MySQL schema objects. It is modelled after and supports a subset of the Oracle REST Data Services (ORDS).

For more information, see ORDS

MRS consists of the following components:

• A MySQL Solution (like MySQL Heatwave, MySQL InnoDB Cluster, a standalone MySQL Server, etc.)
  • Serving a metadata schema mysql_rest_service_metadata that holds the MRS configuration
  • Serving the application’s data
• MySQL Router
  • One or many MySQL Router instances to serve the HTTPS REST interface on default port 8443
• MySQL Shell / MySQL Shell for VS Code
  • Support for managing MRS through a graphical user interface (GUI) embedded inside VS Code
  • MRS plugin to configure and manage the MRS setup on the terminal and using scripts

About REST APIs

Representational State Transfer (REST) is a style of software architecture for distributed hypermedia systems such as the World Wide Web. An API is described as RESTful when it conforms to the tenets of REST. Although a full discussion of REST is outside the scope of this document, a REST API has the following characteristics:

• Data is modelled as a set of resources. Resources are identified by URIs.
• A small, uniform set of operations are used to manipulate resources (for example, PUT, POST, GET, DELETE).
• A resource can have multiple representations (for example, a blog might have an HTML representation and an RSS representation).
• Services are stateless and because the client is likely to access related resources, these should be identified in the representation returned, typically by providing hypertext links.

Feature Overview

2 Deployment Architecture

MySQL REST Service (MRS) can be deployed in many different ways depending on the individual project requirements.

Deployments for Development

The smallest possible development environment consists of a single MySQL Server instance and a MySQL Router instance running on the same machine.

The recommended deployment for development consists of an InnoDB Cluster deployed with a minimum of three MySQL Server instances and two MySQL Router instances.

For cloud-based development, a MySQL Database Service instance with the high availability feature enabled and two compute instances with MySQL Router deployments should be used.

Production Deployments

In a production environment, an InnoDB Cluster set up with three or more MySQL Router instances should be used. It is recommended to use a load balancer to expose the HTTPS port of the MySQL Router instances to the public internet.

For cloud-based development in production, a MySQL Database Service instance with the high availability feature enabled and three or more compute instances with MySQL Router deployments should be used. It is recommended to use a load balancer to expose the HTTPS port of the MySQL Routers to the public internet.

3 Configuring MySQL REST Service

To configure the MySQL REST Service (MRS), use these steps:

1. Deploy a MySQL solution.
2. Configure the MRS metadata database schema.
3. Bootstrap and run one or more MySQL Router instances for MRS support.

After performing these steps, MySQL REST Service is fully configured.

The HTTP/S endpoints can then be accessed as configured during the MySQL Router bootstrap process.

Deployment of a MySQL solution

The following MySQL solutions are supported:

• MySQL HeatWave
• MySQL InnoDB Cluster
• MySQL InnoDB ClusterSet
• MySQL InnoDB ReplicaSet
• MySQL Operator

For development purposes, a standalone MySQL Server instance is also supported. Avoid using a standalone setup in a production deployment because it provides no form of High Availability (HA).

• Standalone MySQL Server

See the corresponding documentation about how to deploy and configure the different MySQL solutions.

3.1 Configuring the MRS Metadata Schema

MySQL REST Service stores its configuration in the mysql_rest_service_metadata database schema. To deploy the metadata schema, perform one of the tasks described in this section.

Note: The MySQL user that is used to connect to the MySQL Solution must have MySQL privileges to create database schemas and roles.

3.1.1 MRS Configuration Using MySQL Shell for VS Code

1. Start VS Code, install the MySQL Shell for VS Code extension, and then add a database connection to the MySQL setup.

2. Right-click the connection in the DATABASE CONNECTIONS view and select Configure Instance for MySQL REST Service Support.

The MRS metadata schema has now been configured.

3.1.2 MRS Configuration Using MySQL Shell

Open a terminal, start MySQL Shell, and connect to the MySQL setup.

mysqlsh dba@localhost

Configure the metadata schema using the MRS plugin by executing mrs.configure().

MySQL> localhost:33060+> 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.

The MRS metadata schema has now been configured.

3.2 Bootstrapping and Running MySQL Routers with MRS Support

MySQL Router is an essential part of any MySQL solution and therefore often deployed in the same step as the MySQL Server instances. See the MySQL Router documentation for more details.

A MySQL Router instance needs to be configured to support MRS. This is usually done by using the mysqlrouter_bootstrap command, which queries the user account for the necessary information.

3.2.1 Using MySQL Shell for VS Code to Bootstrap and Run MySQL Router

When working with a local development setup it is common to install the MySQL Router instance on the local development machine.

In this case, MySQL Shell for VS Code can be used to simplify the bootstrap process and to launch the MySQL Router instance as follows:

1. Download and install the MySQL Router package on your local development machine
   • When not using the DMG on macOS or MSI package on Windows to install MySQL Router, be sure that the directory containing the MySQL Router binaries is in the system PATH.
2. Inside MySQL Shell for VS Code, expand a DB Connection in the DATABASE CONNECTIONS view, right-click the MySQL REST Service tree item, and then select Start Local MySQL Router Instance.
   • If the MySQL Router has not been configured yet, the bootstrap operation runs in an integrated VS Code terminal and then starts MySQL Router.
   • MySQL Router debug output can then be inspected in the VS Code terminal.
3. To shut down MySQL Router, set the focus to the VS Code terminal showing the debug output and press Ctrl + C.

Note: The previous task only works for classic MySQL connections that are not using the MySQL SSH tunneling or MDS tunneling feature.

After the MySQL Router has been bootstrapped and started, MRS is available at https://localhost:8443/<service-name>.

You can then proceed and add a REST service.

3.2.2 Bootstrapping MySQL Router From the Command Line

When deploying a new MySQL Router instance, it is advised to use the mysqlrouter_bootstrap command to bootstrap and configure the router, including the MRS configuration. This is also true for reconfiguring an existing MySQL Router instance for MRS support.

mysqlrouter_bootstrap dba@127.0.0.1:3306 --mrs --directory ~/.mysqlrouter

Follow the interactive steps on the command line to configure the router.

Manual Creation of MySQL User Account for MySQL Router Access

When using the mysqlrouter_bootstrap command to configure MySQL Router for MRS, access the user accounts described in this section can be created automatically.

If you want to manage the required MySQL accounts manually, the following steps need to be performed:

1. Create the MySQL user account(or accounts)
   • If only one account is specified, MySQL Router uses it to access both the MRS metadata schema and application schema data. This account must have the mysql_rest_service_meta_provider and mysql_rest_service_data_provider roles.
   • If two accounts are used, MySQL Router, uses one for the MRS metadata schema access and the other one for the application schema data. Assign the mysql_rest_service_meta_provider role to one user and mysql_rest_service_data_provider to the other.
2. Bootstrap the MySQL Routers instance using the created MySQL accounts with the following options:
   • --mrs-mysql-metadata-account used by the router to access the MRS metadata schema
   • --mrs-mysql-data-account used by the router to access the application schema

As part of the MRS metadata schema creation, two SQL ROLEs have been created for MySQL Router to access MySQL:

• The ‘mysql_rest_service_meta_provider’ ROLE grants access to the required MRS metadata schema tables.
• The ‘mysql_rest_service_data_provider’ ROLE grants access to the served schema objects in the application database schemas.

To create the MySQL account, connect to the MySQL setup with MySQL Shell or MySQL Shell for VS Code and execute the following SQL statements:

CREATE USER 'mysqlrouter_mrs_<router_name>'@'<router_host>' IDENTIFIED BY 'password';
GRANT 'mysql_rest_service_meta_provider', 'mysql_rest_service_data_provider' TO 'mysqlrouter_mrs_<router_name>'@'<router_host>';

The user name specified for the account can then be used when calling the mysqlrouter_bootstrap command.

Adding a MRS Configuration to an Existing MySQL Router Configuration

In case your MySQL Router instances are configured already, it is possible to add the MRS configuration later on.

To get the path of the existing configuration file, execute mysqlrouter --help to show the exact location of the router config file.

The following is an example when connecting to a single development server.

[DEFAULT]
logging_folder = /var/log/mysqlrouter
runtime_folder = /var/run/mysqlrouter
config_folder = /etc/mysqlrouter

[logger]
level = DEBUG

[routing:mrs_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=10.0.1.135:3306
routing_strategy=round-robin
protocol=classic

To enable MRS support on the router, the configuration file needs to be extended with the [http_server] section and the [rest_mrs] section.

It is advised to use the mysqlrouter_bootstrap command to configure the router for MRS.

mysqlrouter_bootstrap dba@127.0.0.1:13000 --mrs --directory /export/mysql/src/mysql-trunk/boot

These parameters can be used to set the MRS configuration options.

The following example demonstrates connecting to a single development server and serving the REST services using HTTP.

[http_server]
port=8443
ssl=1
ssl_cert=/Users/myUser/.mysqlsh/plugin_data/gui_plugin/web_certs/server.crt
ssl_key=/Users/myUser/.mysqlsh/plugin_data/gui_plugin/web_certs/server.key

[mysql_rest_service]
mysql_read_only_route=bootstrap_ro
mysql_read_write_route=bootstrap_rw
mysql_user=mysql_router_mrs16_250ho3u15n
mysql_user_data_access=
router_id=16

4 Adding REST Services and Database Objects

MRS supports the setup of a large number individual REST services.

Each REST service has its own settings for URL path, host name, supported protocols, authentication options, and other settings. A service can expose a selected list of database schemas and objects.

It is possible to perform an individual setup for each application and it is advised to setup an separate REST service for each application.

Preconditions for Adding a REST Service

Before setting up a new REST service, ensure that the following preconditions are met:

• The MySQL REST Service must be configured on the targeting MySQL Solution. Please see the Configuration section of this manual.
• The MySQL account used to connect to the targeting MySQL Solution needs to be granted the mysql_rest_service_admin MySQL role or a superset of privileges.

To grant the mysql_rest_service_admin MySQL role, execute the following SQL statement.

GRANT 'mysql_rest_service_admin' TO 'user_account'@'%';

-- Please ensure to include all roles in the next statement
-- that should become active when the user connects
ALTER USER 'user_account'@'%' DEFAULT ROLE 'mysql_rest_service_admin';

4.1 Setting Up a New REST Service

A new REST service can be added in one of the following ways:

• MySQL Shell for VS Code provides a GUI dialog to create the REST service.
• MySQL Shell offers the MRS plugin that can be used to create a REST service interactively or with scripts in a terminal.
• When writing a script or plugin for MySQL Shell, the MRS plugin can be used to script the creation in Python or JavaScript.

4.1.1 Adding a REST Service Using MySQL Shell for VS Code

After configuring the MySQL REST Service on the target MySQL instance, the DB Connection in the DATABASE CONNECTIONS view (when expanded) shows a new tree item, MySQL REST Service.

1. Right-click the tree item MySQL REST Service and select Add REST Service... from the list to display the MySQL REST Service dialog.

2. Specify values for the required parameters and click OK to add the new REST service.

4.1.2 Adding a REST Service Using MySQL Shell

For MySQL Shell, the mrs plugin is used to work with the MySQL REST Service. The mrs.add.service() function adds a new REST service.

When started without parameters, an interactive wizard prompt you for the required parameters. For example:

MySQL > localhost:33060+ > Py > mrs.add.service()
Please enter the context path for this service [/mrs]: /myservice
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]:

Service /myservice created successfully.

Execute the following command to get detailed help information about the mrs.add.service() function.

\? mrs.add.service

4.1.3 REST Service Definitions

4.1.3.1 About MRS AutoREST

AutoREST is a quick and easy way to expose database schema tables, views, and procedures as REST resources.

4.1.3.2 REST APIs

Representational State Transfer (REST) is a style of software architecture for distributed hypermedia systems such as the World Wide Web. An API is described as RESTful when it conforms to the tenets of REST. Although a full discussion of REST is outside the scope of this document, a REST API has the following characteristics:

• Data is modelled as a set of resources. Resources are identified by URIs.

• A small, uniform set of operations are used to manipulate resources (for example, PUT, POST, GET, DELETE).

• A resource can have multiple representations (for example, a blog might have an HTML representation and an RSS representation).

• Services are stateless and since it is likely that the client will want to access related resources, these should be identified in the representation returned, typically by providing hypertext links.

4.1.3.3 RESTful Services Terminology

This section introduces some common terms that are used throughout this document:

• RESTful service: An HTTP web service that conforms to the tenets of the RESTful architectural style.

• Resource module: An organizational unit that is used to group related resource templates.

• Resource template: An individual RESTful service that is able to service requests for some set of URIs (Universal Resource Identifiers). The set of URIs is defined by the URI Pattern of the Resource Template

• URI pattern: A pattern for the resource template. Can be either a route pattern or a URI template, although you are encouraged to use route patterns.

• Route pattern: A pattern that focuses on decomposing the path portion of a URI into its component parts. For example, a pattern of /:object/:id? will match /emp/101 (matches a request for the item in the emp resource with id of 101) and will also match /emp/ (matches a request for the emp resource, because the :id parameter is annotated with the ? modifier, which indicates that the id parameter is optional).

• HTTP operation: HTTP (HyperText Transport Protocol) defines standard methods that can be performed on resources: GET (retrieve the resource contents), POST (store a new resource), PUT (update an existing resource), and DELETE (remove a resource).

4.2 Adding a Database Schemas to a REST Service

For each MySQL database schema a corresponding REST schema can be created and added to a REST service. It is possible to add the same MySQL database schema to different REST services by creating several REST schemas for the MySQL database schema.

The REST schema can be created by using MySQL Shell for VS Code or MySQL Shell on the command line.

Note: Adding a database schema as a REST schema is not equivalent to exposing all tables and views in the schema through the RESTful Web service. It just means making the MySQL REST Service aware that the schema exists and that it may have zero or more resources to expose via HTTP/S.

Preconditions for Adding Database Schemas and Objects

Before adding REST schemas and objects, ensure that the following preconditions are met:

• A REST service must be added first (see Adding a REST Service).
• The MySQL account used to connect to the targeting MySQL Solution needs to be granted the mysql_rest_service_schema_admin MySQL role or a superset of privileges.

To grant the mysql_rest_service_schema_admin MySQL role, execute the following SQL statement.

GRANT 'mysql_rest_service_schema_admin' TO 'user_account'@'%';

-- Please ensure to include all roles in the next statement
-- that should become active when the user connects
ALTER USER 'user_account'@'%' DEFAULT ROLE 'mysql_rest_service_schema_admin';

4.2.1 Adding a Schema via MRS DDL

On an active MySQL Shell connection execute the CREATE REST SCHEMA DDL statement referencing the corresponding MySQL database schema in the FROM clause. Please see the MRS SQL Reference for more details.

Example

The following example adds a REST schema for the sakila database schema to the REST service /myService.

CREATE OR REPLACE REST SCHEMA /sakila ON SERVICE /myService
    FROM `sakila`
    COMMENTS "The sakila schema";

4.2.2 Adding a Schema using the MySQL Shell for VS Code UI

To add a database schema to a REST service:

1. Right-click the schema in the DATABASE CONNECTIONS view and select Add Schema to REST Service. This will open a dialog where all REST schema parameters can be set.

2. Click OK to add the schema.

4.2.3 Adding a REST Schema with a MySQL Shell Script

To add a database schema to a REST service, call the mrs.add.schema() function.

When started without parameters, an interactive wizard prompts you for the required parameters.

 MySQL > localhost:33060+ > JS > mrs.add.schema()
   1 information_schema
   2 performance_schema
   3 sys
   4 sakila
   5 test
   6 forum
   7 ortho
   8 mrs_notes

Please enter the name or index of a schema: 4
Please enter the request path for this schema [/sakila]: /sakila
Should the schema require authentication [y/N]:
How many items should be listed per page [Schema Default]:
Comments:
Options:

Service with path /sakila created successfully.

Execute the following command to get detailed help information about the mrs.add.schema() function.

\? mrs.add.schema

4.3 Adding a Schema Table, View or Procedure

Adding database schema objects (tables, views, or procedures) to a MySQL REST Service (MRS) allows them to be accessed through RESTful Web services. Before database schema object can be added as REST objects, the database schema containing those objects has to be added as a REST schema.

The following figure shows the a REST schema and its REST objects.

Once a MySQL database schema has been added as a REST schema, its objects can be added. Database schema tables and views are added as REST duality views, stored procedures are added as REST procedures.

Note: REST duality views enabled application developers to take a document centric approach when implementing their applications. Please refer to the JSON duality view section of this document to learn about the advantages of using REST duality views.

The MySQL database schema objects can be added by using MySQL Shell for VS Code or MySQL Shell on the command line.

4.3.1 Adding a Schema Object via MRS DDL

On an active MySQL Shell connection execute the CREATE REST DUALITY VIEW DDL statement to add a database schema table or view as REST object. Please see the MRS SQL Reference for more details.

To add a stored procedure the CREATE REST PROCEDURE DDL statement can be used.

Examples

The following example adds a REST duality view for the sakila.city database schema table.

CREATE REST DUALITY VIEW /city
ON SERVICE /myService SCHEMA /sakila
FROM `sakila`.`city` AS MyServiceSakilaCity {
    cityId: city_id @SORTABLE,
    city: city,
    countryId: country_id,
    lastUpdate: last_update
};

The next example adds a REST procedure for the sakila.film_in_stock database schema stored procedure.

CREATE OR REPLACE REST PROCEDURE /filmInStock
FROM `sakila`.`film_in_stock` AS MyServiceSakilaFilmInStock
PARAMETERS {
    pFilmId: p_film_id @IN,
    pStoreId: p_store_id @IN,
    pFilmCount: p_film_count @OUT
}
RESULT MyServiceSakilaFilmInStock {
    inventoryId: inventory_id @DATATYPE("int")
};

4.3.2 Adding a Schema Object with MySQL Shell for VS Code UI

To add a database schema object to a REST schema:

1. Right-click on the database object in the DATABASE CONNECTIONS view and select Add Database Object to REST Service. This will open the MySQL REST Object dialog.

2. Adjust all REST object settings accordingly.

3. Press OK to add the database schema object.

4.3.3 Adding a Database Object with MySQL Shell

To add a database schema to a REST service call the mrs.add.dbObject() function.

When started without parameters, an interactive wizard prompts you for the required parameters.

MySQL > localhost:33060+ > JS > mrs.add.dbObject()
   1 mrs_notes
   2 sakila

Please enter the name or index of a schema: 2
   1 TABLE
   2 VIEW
   3 PROCEDURE

Please enter the name or index of a database object type [TABLE]:
   1 actor
   2 address
   3 category
   4 city
   5 country
   6 customer
   7 film
   8 film_actor
   9 film_category
  10 film_text
  11 inventory
  12 language
  13 payment
  14 rental
  15 staff
  16 store

Please enter the name or index of an database object: 4
Please enter the request path for this object [/city]:
   1 CREATE
   2 READ
   3 UPDATE
   4 DELETE

Please select the CRUD operations that should be supported, '*' for all [READ]:
   1 FEED
   2 ITEM
   3 MEDIA

Please select the CRUD operation format [FEED]:
Should the db_object require authentication [y/N]:
Should row ownership be required when querying the object [y/N]:
How many items should be listed per page [Schema Default]:
Comments:

Object added successfully.

Execute the following command to get detailed help information about the mrs.add.dbObject() function.

\? mrs.add.dbObject

5 Working Interactively with REST Services

MySQL Shell for VS Code features a live, interactive workflow for designing REST Services.

It allows developers to immediately test their newly created or modified REST duality views and REST procedures by using the MRS SDK TypeScript client API right from within a DB Notebook.

Preconditions for The Interactive Workflow

• A MySQL Router instance needs to be bootstrapped and running with MRS support. When using a local development setup it is also possible to do this directly from within VS Code.

5.1 Switching to TypeScript Mode

After opening a database connection in MySQL Shell for VS Code the DB Notebook will be displayed. Switch the DB Notebook to TypeScript mode with \ts if it is in SQL mode.

5.2 Choosing a REST Service

In order to work with a REST service on a DB Notebook it needs to be set as current REST Service. This is similar to executing a SQL USE db_name statement to set the current database schema.

To get information about the current REST service use the global mrs object and execute the mrs.getStatus() function. It will print information about the MRS status. The current REST service has the property isCurrent set to true.

Example

ts> mrs.getStatus();
{
    "configured": true,
    "info": "2 REST services available.",
    "services": [
        {
            "serviceName": "myService",
            "url": "https://localhost:8443/myService",
            "isCurrent": true
        },
        {
            "serviceName": "myPublicService",
            "url": "https://localhost:8443/myPublicService",
            "isCurrent": false
        }
    ]
}

Once a current REST service is set, the MRS TypeScript Client API for this service is automatically generated on-the-fly and made available to TypeScript code blocks on the DB Notebooks.

The current REST service object be directly accessed via a global variable using the same name as listed by the mrs.getStatus() function in the serviceName property. The serviceName is directly derived from the REST service’s url context root path by converting it to camel case and removing all slashes /. E.g. a REST service with the url context root of /myService will be accessible as myService.

Example

ts> myService.url;
https://localhost:8443/myService

The current REST service can either be set from a DB Notebook via a TypeScript or through the VS Code UI.

5.2.1 Using Typescript to Setting the Current REST Service

The global mrs object automatically holds properties for all available REST services. The naming of the REST service properties matches the serviceName of each REST service as discussed in the previous section.

Execute the mrs.<serviceName>.setAsCurrent() function to make the given REST service the current one. The VS Code auto-completion feature will assist with selecting the serviceName.

Example

ts> mrs.myPublicService.setAsCurrent();

Note: The current REST service is only available after executing the full TypeScript code block with [command] + [Return] on macOS and [Ctrl] + [Return] on Linux and Windows. It uses an async message pipeline which cannot be awaited. For that reason, trying to access specific methods of the current REST service will not work in the same code block where it was changed.

5.2.2 Using VS Code to Setting the Current REST Service

Browse the DATABASE CONNECTIONS View in VS Code’s Primary Sidebar, open the current DB Connection as well as the MySQL REST Service tree items and right click onto the desired MRS service and select Set as Current REST Service from the context menu.

The current REST service is indicated by a solid, filled REST service icon. All other REST services use an icon with outlines only.

5.3 Authentication

If some of the REST objects require authentication and a REST Authentication App has been added to the REST service, use the authenticate() function of the REST service Client API object. This will show an login dialog where the credentials of a user account can be specified.

ts> myService.authenticate();

The authenticate() function only works with the built in MRS authentication vendor. Please make sure to set this vendor when adding the REST Authentication App.

5.4 Querying a REST Object

In the following examples case the sakila.city database table was used, as can be seen in the screenshot above.

ts> myService.sakila.city.findFirst();
{
   "city": "A Corua (La Corua)",
   "links": [
      {
         "rel": "self",
         "href": "/myService/sakila/city/1"
      }
   ],
   "cityId": 1,
   "countryId": 87,
   "lastUpdate": "2006-02-15 04:45:25.000000",
   "_metadata": {
      "etag": "EE93452B41984F3F5BBB0395CCB2CED00F5C748FEEA4A36CCD749CC3F85B7CEA"
   }
}

The fields can be filtered and a conditional where clause can be added. Please refer to the MRS SDK Client API documentation for more information.

ts> myService.sakila.city.findMany({select: ["city", "cityId"], where: {city: {$like: "NE%"}}});
{
    "items": [
        {
            "city": "Newcastle",
            "links": [
                {
                    "rel": "self",
                    "href": "/myService/sakila/city/364"
                }
            ],
            "cityId": 364,
            "_metadata": {}
        },
        {
            "city": "Nezahualcyotl",
            "links": [
                {
                    "rel": "self",
                    "href": "/myService/sakila/city/365"
                }
            ],
            "cityId": 365,
            "_metadata": {
                "etag": "681C34301F6ED6FD1200505C9C2CFB90E3367A267B7AADBD85186D781FEC7C19"
            }
        }
    ],
    "limit": 25,
    "offset": 0,
    "hasMore": false,
    "count": 2,
    "links": [
        {
            "rel": "self",
            "href": "/myService/sakila/city/"
        }
    ]
}

To quickly edit a REST DB Object using the REST Object dialog, the edit() function can be used. Please note that this function is only available on DB Notebooks.

ts> myService.sakila.city.edit()

6 JSON-Relational Duality Views

6.1 Introduction to Duality Views

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.

6.1.1 Use Cases

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.

6.1.2 REST Duality View Workflow

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.

The database automatically detects the changes in the new document and modifies the underlying rows, including all nested tables. All duality views that share the same data immediately reflect this change. This drastically simplifies application development since developers no longer have to worry about inconsistencies, compared to using traditional document databases.

6.2 Lock-Free Optimistic Concurrency Control

Duality Views can be safely updated concurrently without the use of locks. Objects fetched from the database have a checksum computed, which is called ETag and is included in the returned object, in the _metadata.etag field.

When that object is submitted back to MRS to be updated (via PUT), the ETag of the original object is compared to the current version of the ETag. If the rows corresponding to the object have changed since it was first fetched, the ETag would not match. In that case, the request fails with HTTP status code 412. The client must then fetch the object again and re-submit its update request based on an up-to-date version of the object.

The object checksum includes all fields of the source row as well as any rows joined/included, even filtered fields. Fields can be explicitly excluded using the @nocheck attribute.

Example

If at first, GET /myService/sakila/city/1 returns the following JSON document to the client.

{
    "city": "A Corua (La Corua)",
    "links": [
        {
            "rel": "self",
            "href": "/myService/sakila/city/1"
        }
    ],
    "cityId": 1,
    "country": {
        "country": "Spain",
        "countryId": 87,
        "lastUpdate": "2006-02-15 04:44:00.000000"
    },
    "countryId": 87,
    "lastUpdate": "2006-02-15 04:45:25.000000",
    "_metadata": {
        "etag": "FFA2187AD4B98DF48EC40B3E807E0561A71D02C2F4F5A3B953AA6CB6E41CAD16"
    }
}

Next, the client updates the object and changes the city name to A Coruña (La Coruña) and submits it by calling PUT /myService/sakila/city/1.

{
    "city": "A Coruña (La Coruña)",
    "links": [
        {
            "rel": "self",
            "href": "/myService/sakila/city/1"
        }
    ],
    "cityId": 1,
    "country": {
        "country": "Spain",
        "countryId": 87,
        "lastUpdate": "2006-02-15 04:44:00.000000"
    },
    "countryId": 87,
    "lastUpdate": "2006-02-15 04:45:25.000000",
    "_metadata": {
        "etag": "FFA2187AD4B98DF48EC40B3E807E0561A71D02C2F4F5A3B953AA6CB6E41CAD16"
    }
}

If the target object has been changed (e.g. by another user) between the GET and the PUT requests, the ETag check would fail and the PUT would result in error 412 Precondition Failed.

6.3 Interactive Duality View Design

While REST duality views can be created by manually writing CREATE REST DUALITY VIEW MRS DDL statements, it is often much easier to design REST duality views in a visual editor.

MySQL Shell for VS Code includes the MySQL REST Object dialog which features an advanced JSON/Relational Duality designer. Using this designer it is possible to create even complex, nested REST duality views within seconds.

The DDL Preview button allows to preview the corresponding MRS DDL statement while interactively designing the REST duality view.

6.3.1 Building a JSON/Relational Duality View

Building a REST duality view for a single relational table (or view) is straight forward. Using MySQL Shell for VS Code to add the database schema table automatically creates the corresponding REST duality view containing all columns of the table in a flat JSON object.

Adding the database schema table via VS Code is equal to calling the CREATE REST DUALITY VIEW MRS DDL statement without a graphQlObj definition, which also adds all columns of the table as a flat JSON object.

CREATE OR REPLACE REST DUALITY VIEW /city
FROM `sakila`.`city` AS MyServiceSakilaCity;

SHOW CREATE REST VIEW /city;

+-----------------------------------------------+
| CREATE REST DUALITY VIEW                      |
+-----------------------------------------------+
| CREATE OR REPLACE REST DUALITY VIEW /city     |
|     ON SERVICE /myTestService SCHEMA /sakila  |
|     AUTHENTICATION REQUIRED                   |
|     FROM sakila.city AS MyServiceSakilaCity { |
|         cityId: city_id,                      |
|         city: city,                           |
|         countryId: country_id,                |
|         lastUpdate: last_update               |
|     };                                        |
+-----------------------------------------------+

Note: In order to be able to access the REST object without authentication, the Requires Auth checkbox needs to be unchecked in the MySQL REST Object dialog or the AUTHENTICATION NOT REQUIRED clause needs to be added to the MRS DDL statement. This should only be done during development time or when a REST endpoint should be publicly available.

6.3.1.1 Enabling CRUD Operations

Since only the READ CRUD operation is enabled by default (see the R being highlighted next to the relational object), only read commands will be allowed on the REST object. To change this, toggle each CRUD letter (C - Create, R - Read, U - Update and D - Delete) to enable or disable the corresponding functionality in the MySQL REST Object dialog.

The same can be achieved by using annotations in the MRS DDL statement.

CREATE OR REPLACE REST DUALITY VIEW /city
FROM `sakila`.`city` @INSERT @UPDATE @DELETE AS MyServiceSakilaCity;

The following table shows the mapping between CRUD operations and SQL operations.

6.3.2 Creating a Nested JSON/Relational Duality View

By enabling a referenced table, the columns of that table are included as a nested entry in the JSON result. Please note that this works with 1:1 and 1:n relationships.

This leads to the following result.

myService.sakila.city.findFirst();

{
    "city": "A Corua (La Corua)",
    "links": [
        {
            "rel": "self",
            "href": "/myService/sakila/city/1"
        }
    ],
    "cityId": 1,
    "country": {
        "country": "Spain",
        "countryId": 87,
        "lastUpdate": "2006-02-15 04:44:00.000000"
    },
    "countryId": 87,
    "lastUpdate": "2006-02-15 04:45:25.000000",
    "_metadata": {
        "etag": "FFA2187AD4B98DF48EC40B3E807E0561A71D02C2F4F5A3B953AA6CB6E41CAD16"
    }
}

6.3.3 Creating a JSON/Relational Duality View with an Unnested Referenced Table

If the columns of the referenced table should be added to the level above, the Unnest option can be enabled.

This leads to the following result.

myService.sakila.city.findFirst();

{
    "city": "A Corua (La Corua)",
    "links": [
        {
            "rel": "self",
            "href": "/myService/sakila/city/1"
        }
    ],
    "cityId": 1,
    "country": "Spain",
    "countryId": 87,
    "lastUpdate": "2006-02-15 04:45:25.000000",
    "_metadata": {
        "etag": "48889BABCBBA1491D25DFE0D7A270FA3FDF8A16DA8E44E42C61759DE1F0D6E35"
    }
}

6.3.4 Creating a JSON/Relational Duality View with a Reduced Referenced Table

Instead of having all columns unnested and disabling all columns that are not wanted, the Reduce to... dropdown can be used to select the column that should be selected for the reduce operation.

This leads to the same result as the query above.

myService.sakila.city.findFirst();

{
    "city": "A Corua (La Corua)",
    "links": [
        {
            "rel": "self",
            "href": "/myService/sakila/city/1"
        }
    ],
    "cityId": 1,
    "country": "Spain",
    "countryId": 87,
    "lastUpdate": "2006-02-15 04:45:25.000000",
    "_metadata": {
        "etag": "48889BABCBBA1491D25DFE0D7A270FA3FDF8A16DA8E44E42C61759DE1F0D6E35"
    }
}

7 MRS Dialog Reference

This section discusses the individual MRS UI dialogs offered by MySQL Shell for VS Code.

7.1 MRS Service Dialog

7.1.1 REST Service Properties

Each REST service has a common set of properties.

7.1.2 REST Service Advanced Options

The following advanced options can be set in JSON format:

• headers: Accepts a JSON object with one or more HTTP header names as key and its setting as value.
• http:
  • allowedOrigin: If set to auto, MySQL Router dynamically sets the header Access-Control-Allow-Origin to the domain generating the request. Alternatively, this can be set to a specific domain https://mydomain.com or a list of domains (for example, ["https://mydomain.com", "https://myotherdomain.com"]).
• logging:
  • exceptions: If set to true, exceptions are logged.
  • requests:
    • body: If set to true, the full body of all requests are logged.
    • headers: If set to true, only the headers of all requests are logged.
  • response:
    • body: If set to true, the full body of all responses are logged.
    • headers: If set to true, only the headers of all responses are logged.
  • returnInternalErrorDetails: If set to true, the cause errors with code 500 are sent to the client.
  • includeLinksInResults: If set to `false``, the results do not include navigation links.

7.1.2.1 Default REST Service Options

The example that follows shows the options that are used as default when deploying a new service.

Note: These options are only recommended for development and must be changed for use in production.

By setting allowedOrigin to auto MySQL Router dynamically sets the header Access-Control-Allow-Origin to the domain that generates the request. This is done to work around Cross-origin resource sharing (CORS) checks of web browsers during development time.

{
    "headers": {
        "Access-Control-Allow-Credentials": "true",
        "Access-Control-Allow-Headers": "Content-Type, Authorization, X-Requested-With, Origin, X-Auth-Token",
        "Access-Control-Allow-Methods": "GET, POST, PUT, DELETE, OPTIONS"
    },
    "http": {
        "allowedOrigin": "auto"
    },
    "logging": {
        "exceptions": true,
        "request": {
            "body": true,
            "headers": true
        },
        "response": {
            "body": true,
            "headers": true
        }
    },
    "returnInternalErrorDetails": true
}

When deploying a REST service in production, the following settings need to be changed:

1. Change allowedOrigin to the domain, or domains, the REST service is running on (for example, "https://mydomain.com" when deploying on a production server).
2. Set returnInternalErrorDetails to false.
3. Adjust the logging settings as needed.

7.2 MRS Schema Dialog

7.2.1 REST Schema Properties

Each REST schema has a common set of properties.

7.3 MRS Object Dialog

The following aspects can be set through the dialog.

• Basic Settings
  • The database schema object that should be exposed via MRS
  • The URL path of how to access the REST object
• Security
  • Whether the object is publicly available or requires authentication
  • The CRUD operations that are allowed
  • The CRUD operations that are allowed on referenced tables
  • Whether row ownership should be enforced to enable row level security
• JSON/Relation Duality
  • Which columns of the database schema object should be exposed and how they should be named
  • Which referenced tables should be included, either nested or unnested or reduced to a single field

8 Authentication and Authorization

8.1 Authentication Management

MRS currently supports the following authentication methods.

8.1.1 MRS REST Service Specific Authentication

Authentication is handled my MRS against MRS REST Service specific accounts. Applications use SCRAM (Salted Challenge Response Authentication Mechanism) to securely authenticate a user.

8.1.2 MySQL Internal Authentication

Authentication is handled my MRS against MySQL server user accounts. Applications use SCRAM (Salted Challenge Response Authentication Mechanism) to securely authenticate a user.

This authentication method is suitable for applications that are not exposed publicly.

8.1.3 OAuth2 Authentication

Several OAuth2 services from 3rd-party vendors are supported by MRS; for example, sign in with FaceBook, Twitter, and Google. In order for a MRS service to authenticate against those vendors, one needs to be registered as a developer with those vendors and a vendor specific authentication apps need to be created. Then the OAuth2 specific settings - like access_token and app_id - need to be configured on the MRS side.

8.2 Authorization Management

Access to a given REST resource can have several levels of restrictions when using MRS:

• Public access - no authorization is needed to access the REST resource and its data
• Full access - after authentication the user has full access to all data of the REST resource
• Limited access - after authentication the user has only access to a subset of the data of the REST resource

MRS has built-in support for several authorization models. These authorization models define which data of a given REST resource that end users can see and manipulate:

• User-ownership based - users can see their own data
• Privilege based, managed using roles
• User-hierarchy based
• Group based
• Group-hierarchy based

If the use case of a given project matches one of the offered authorization models, then a custom authorization does not need to be implemented.

9 MRS Examples

The MRS Shell Plugin ships with a set of example projects that showcase the possibilities of the MySQL REST Service.

They are implemented as simple Progressive Web Apps (PWA) to showcase the features offered by MRS.

9.1 MRS Notes Examples

The MRS Notes example implements a simple note taking application as a Progressive Web Apps (PWA) that allows for sharing notes between users.

9.1.1 MRS Notes Developer Showcase

The following features are showcased in this example.

• Accessing MRS REST endpoints from JavaScript and TypeScript code.
• Using MRS service authentication REST endpoints to support user management
• Using JSON Web Tokens (JWT) to manage user sessions

9.1.2 MRS Notes Quick Guide

To quickly get the MRS Notes Examples working, please feel free to follow this guide. If you want to learn more about the examples, please continue reading the chapters below.

The following steps need to be taken to setup, build and deploy the MRS Notes example project on the MySQL REST Service.

1. Save the MRS Notes Example project to disk and open it with VS Code VSCodeProject:examples/mrs_notes
2. Configure the MySQL REST Service.
3. Create a new MRS service (e.g. /myService).
4. Deploy the mrs_notes MySQL database schema examples/mrs_notes/db_schema/mrs_notes.sql
5. Load the MRS schema dump into the MRS service examples/mrs_notes/mrs_schema/mrsNotes.mrs.json
6. Ensure a bootstrapped MySQL Router instance is running (if not, start it).
7. Build and deploy the app by following the steps below.

9.1.2.1 Deploying the TypeScript Example

The MrsNotesTS project implements a full TypeScript demo app that allows sharing of notes between users.

1. If you have not done so in the previous section, save the following project to disk and open it with VS Code VSCodeProject:examples/mrs_notes
2. After the project folder has been opened in VS Code, set the focus to the TERMINAL tab and enter npm install to install the required node modules
3. In the NPM Script View, run the package.json/build command that will create a folder called dist that contains all files needed for deployment.
4. Right click on the dist folder in the Folders view and select Upload Folder to MySQL REST Service from the popup menu.
5. In the MRS Static Content Set dialog set the Request Path the app should be using, e.g. /app and click Upload to upload the files to the MRS service.
6. Open a web browser and access the full path specified in the previous step to open the app, e.g. https://localhost:8443/myService/app/index.html

9.1.2.2 Deploying the JavaScript Example

The MrsNotes project also ships with a minimal JavaScript demo app that allows each user to store his own notes.

1. If you have not done so in the previous section, save the following project to disk and open it with VS Code VSCodeProject:examples/mrs_notes
2. Right click on the js_app folder in the Folders view and select Upload Folder to MySQL REST Service from the popup menu.
3. Set the path the app should be using, e.g. /app_js and click Upload to upload the files.
4. Open a web browser and access the path specified in the previous step to open the app, e.g. https://localhost:8443/myService/app_js/index.html

9.1.3 MRS Setup and Configuration for the MRS Notes Examples

Please refer to the MRS documentation on how to setup and configure a MRS service in detail.

If you are using a local MRS deployment deployment you can use these simplified steps.

9.1.4 Deploy the mrsNotes MySQL database schema

The mrsNotes MySQL database schema is the center of the MRS project. It defines the structure of the data and its database tables store all the information the users enter while using the app.

To create the mrsNotes schema the corresponding SQL script file needs to be executed. This can be done via the MySQL Shell or directly within VS Code using the MySQL Shell for VS Code extension.

• If you are browsing this documentation within VS Code click the button next to the SQL script name examples/mrs_notes/db_schema/mrs_notes.sql

• If you want to use MySQL Shell on the command line, switch to the mrs_notes plugin directory and run the following command.

  mysqlsh dba@localhost –sql -f examples/mrs_notes/db_schema/mrs_notes.sql

9.1.4.1 mrsNotes EER Diagram

The following diagram shows all components of the mrsNotes schema.

The most important database table is the note table. It stores all notes that are created by the users.

The user table holds the nickname of the user as well as the email address used for receiving invitation emails for shared notes.

The user_has_note table is used to managed the sharing of notes with other users.

As soon as selected notes need to be shareable between users it is necessary to add an abstraction layer. This layer then allows selective access to notes written by other users after they accepted the invitation to participate on the shared note.

In this case the layer consists of one VIEW and four STORED PROCEDUREs.

• notes_all … a VIEW of all notes the user is allowed to see.
• note_share … a STORED PROCEDURE to share a note with another user.
• note_accept_share … a STORED PROCEDURE to accept a shared note.
• note_update … a STORED PROCEDURE to update a shared note
• note_delete … a STORED PROCEDURE to delete a shared note

Copyright (c) 2022, 2023, Oracle and/or its affiliates.