This book contains the documentation for the MySQL REST Service (MRS).

Chapter Overview

• MRS Introduction
• MRS Deployment Architecture
• MRS Configuration
• Adding REST Services and Database Objects
• GUI Interfaces for REST Service Configuration
• Authentication and Authorization
• MRS Examples
  • MRS Notes Example
  • MRS Scripts Example

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. This book features a detailed discussion of the MySQL REST Service, including architecture, configuration and deployment.

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

1.1 What is the MySQL REST Service

The MySQL REST Service is a next-generation JSON Document Store solution, enabling fast and secure HTTPS access for data stored in MySQL, HeatWave, InnoDB ClusterSet and InnoDB ReplicaSet.

Being a fully integrated MySQL solution, it focuses on ease-of-use, support of standards and high performance.

The MySQL REST Service consists of four major building blocks, delivering an integrated solution for JSON Document-based application development.

1. RESTful Web Services
2. REST SQL Extension
3. Powerful Data Mapping
4. Client SDK Generation

Benefits

• Auto-REST endpoints for relational and document oriented data, that can be enable with a few clicks.
• Directly built into MySQL Router, removes need for additional middle-ware.
• High performance web server solution to serve RESTful Web Services as well as Progressive Web Apps (PWAs).
• Excellent vertical scaling (up scaling) and well as horizontal scaling (scaling out) through number of MySQL Routers.

Experience

• Direct VS Code Extension integration featuring point-and-click, WYSIWYG editors and live-querying of REST endpoints via TypeScript.
• Dedicated REST SQL extension support in MySQL Shell for scripting and development process integration.
• Client SDK generation support for popular languages to vastly simplify development process and project integration.
• Support for local development environment & debugging.

Features

• REST endpoints for database tables, views, procedures and function in addition to static data (e.g. PWAs) being served
• Powerful, built-in authentication, authorization (MySQL accounts, MRS accounts, OAuth2) and session management
• New REST SQL extension to be able to define REST services and endpoints directly in SQL scripts
• Client SDK generation with built-in features for authentication, document operations, read-own-write support in distributed MySQL solutions

1.2 Application Use Cases

1.2.1 Which applications should use the MySQL REST Service

The MySQL REST Service exposes RESTful Web Services for interacting with the data stored in MySQL solutions REST endpoints via HTTPS.

This makes the MySQL REST Service an excellent choice for the following use cases.

• Mobile applications, as well as Progressive Web Apps (PWAs), that need to access data across the public internet.
• All modern document-oriented applications that expect to work with JSON documents rather than relational data.
• Extending existing applications with micro-services.
• Offering data REST endpoints to serverless architecture deployments.

1.2.2 Which applications should use a MySQL Connector

Using the MySQL protocol via a MySQL Connector is an established way to build high-performance MySQL database applications. It should be preferred to use this type of MySQL connection for the following use cases.

• Applications that need direct SQL access to the MySQL database.
• Applications that need to work with relational tables rather than JSON documents.
• Applications that do not benefit from an optimistic, ETag based concurrency model.

1.3 Feature Set Overview

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.

2 Architecture

2.1 Building Blocks

The MySQL REST Service consists of the following components:

• A MySQL Solution (Heatwave, MySQL InnoDB ClusterSet, a standalone MySQL Server, etc.)
  • Serving a metadata schema mysql_rest_service_metadata that holds the MRS configuration.
  • Serving the REST applications’ data.
• MySQL Router
  • One or many MySQL Router instances to serve the HTTPS REST interface.
  • Either running in developer or production mode.
• MySQL Shell / MySQL Shell for VS Code
  • Support for the REST SQL extension, to configure and manage REST endpoints via REST SQL commands.
  • Support for managing MRS through a graphical user interface (GUI) embedded inside VS Code.
  • Generation of Client SDKs for given REST service.

2.2 Development Setup

When working with the MySQL REST Service it is important to separate between two different types of setups.

1. A local development setup used to develop new REST services.
   • A local MySQL Shell installation to connect to and execute REST SQL extension commands.
   • A local MySQL Router installation, running in developer mode.
2. The production deployment that serves REST services that have been published.
   • The MySQL solution serving the metadata schema as well as the REST applications’ data.
   • MySQL Router instance(s) running in production mode.

Each of those setups serves a different set of REST services, depending on the REST services’ current lifecycle states.

The recommended way to configure a MySQL REST Service development setup is to use VS Code or VSCodium with the MySQL Shell for VS Code extension installed. This will simplify things like HTTPS certificate installation and bootstrapping the MySQL Router in development mode.

2.3 Production Deployments

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 DB Connection to the MySQL solution that should be configured for the MySQL REST Service.

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

The MySQL REST Service metadata schema can be configured from the MySQL Shell on the command line after connecting to the MySQL solution.

Please note that a MySQL user with ALL PRIVILEGES and WITH GRANT OPTION needs to be used to configure the MySQL REST Service metadata schema. It is common practice to use the root MySQL user or a dedicated dba MySQL user to perform this operation.

To configure the metadata schema the REST SQL extension CONFIGURE REST METADATA statement is used.

Example

The following example connects to a local MySQL Server instance using a dba MySQL user account and configures the MySQL REST Service metadata schema.

$ mysqlsh dba@localhost
MySQL Shell 9.3.0

MySQL> localhost:3306> SQL> CONFIGURE REST METADATA;
Query OK, 0 rows affected (0.3998 sec)
REST metadata configured successfully.

After executing the CONFIGURE REST METADATA statement the MRS metadata schema has now been configured.

3.1.3 Removing the MRS Metadata Schema

If the MySQL REST Service support should be removed, the MySQL REST Service metadata schema can be dropped using the DROP SCHEMA mysql_rest_service_metadata; statement.

Please note that a MySQL user account with required privileges to drop the mysql_rest_service_metadata has to be used.

Example

The following example connects to a local MySQL Server instance using a dba MySQL user account and drops the MySQL REST Service metadata schema.

$ mysqlsh dba@localhost
MySQL Shell 9.3.0

MySQL> localhost:3306> SQL> DROP SCHEMA mysql_rest_service_metadata;
Query OK, 38 rows affected (0.0770 sec)

3.2 Granting Users Access to the MySQL REST Service

After the MySQL REST Service metadata schema has been configured, access to this schema needs to be granted to all MySQL users who should be able to work with the MySQL REST Service.

In addition, access to application data which should be exposed via REST endpoints needs to be granted to MRS data provider role. This will allow the MySQL REST Service to serve the required data.

3.2.1 MRS User Roles

The MySQL REST Service supports a multi-tiered access model that allows the correct role to be assigned to each MySQL users working with the service.

The following MySQL roles can be assigned to MySQL user accounts.

The MySQL GRANT statement can be used to assign the given MySQL role to a MySQL user.

Please note that the MySQL role needs to be made active for the MySQL user’s current session. This can be done by using the MySQL SET ROLE statement. To properly work with the MySQL Shell for VS Code extension, the MySQL role needs to included in the MySQL user’s DEFAULT roles, that can be set via the SET DEFAULT ROLE statement.

Example

The following example GRANTs the mysql_rest_service_admin role to the dba MySQL user and ensures all MySQL roles, including the new mysql_rest_service_admin role, are made active when the MySQL user connects.

MySQL> localhost:3306> SQL> GRANT 'mysql_rest_service_admin' TO 'dba'@'%';
Query OK, 0 rows affected (0.0010 sec)
MySQL> localhost:3306> SQL> SET DEFAULT ROLE ALL TO 'dba'@'%';
Query OK, 0 rows affected (0.0012 sec)

3.2.2 MRS Provider Roles

In addition to the MRS user roles outline above, two additional roles are part of the MySQL REST Service. They are used by the actual MySQL Router/Server MRS components to operate the MySQL REST Service.

When a REST endpoint has been defined, it is essential to ensure the required privileges to access the database schema objects have been granted to the mysql_rest_service_data_provider role.

• For REST views exposing a database table or view, the required privileges are automatically granted.
• For REST procedures and REST functions the EXECUTE privilege is automatically granted. Should the database procedure access other procedures or schema objects, a manual GRANT statement for the mysql_rest_service_data_provider role needs to be executed.

Example

The following example shows how to expose a database procedure test.my_procedure that calls a nested database procedure test.my_sub_procedure.

The SQL script first creates the two procedures and then defines the /myService/test/myProcedure REST endpoint. The EXECUTE privilege on test.my_procedure is automatically assigned. But the REST endpoint would still raise an error as it misses the EXECUTE privilege on test.my_sub_procedure.

Finally, the GRANT statement assigns the EXECUTE privilege on the test.my_sub_procedure to the mysql_rest_service_data_provider role. Now, the REST endpoint is fully functional.

CREATE SCHEMA IF NOT EXISTS `test`;

DELIMITER %%
DROP PROCEDURE IF EXISTS `test`.`my_procedure`%%
CREATE PROCEDURE `test`.`my_procedure`(IN arg1 INTEGER, OUT arg2 INTEGER)
SQL SECURITY DEFINER
NOT DETERMINISTIC
BEGIN
    CALL `test`.`my_sub_procedure`(arg1, arg2);
END%%

DROP PROCEDURE IF EXISTS `test`.`my_sub_procedure`%%
CREATE PROCEDURE `test`.`my_sub_procedure`(IN arg1 INTEGER, OUT arg2 INTEGER)
SQL SECURITY DEFINER
NOT DETERMINISTIC
BEGIN
    SET arg2 = arg1 * 2;
END%%
DELIMITER ;

CREATE OR REPLACE REST SERVICE /myService;
CREATE REST SCHEMA /test ON SERVICE /myService FROM test;
CREATE REST PROCEDURE /myProcedure
    ON SERVICE /myService SCHEMA /test
    AS `test`.`my_procedure`;

GRANT EXECUTE ON PROCEDURE `test`.`my_sub_procedure` TO 'mysql_rest_service_data_provider';

3.3 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.3.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.3.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 [email protected]: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 [email protected]: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

The MySQL REST Service supports the creation of a large number individual REST services.

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

It is advised to setup an separate REST service for each application that consumes a set of REST endpoints.

4.1 REST Service Lifecycle Management

REST services can be placed in a number of different states during their lifecycle.

4.1.1 Development State

When a new REST service is created, it will be only visible to developers. During this development state, REST schemas and object can be added, privileges be granted, the REST endpoints can be tested.

In order to access the REST services in development state, a MySQL Router instance needs to be bootstrapped in developer mode. This type of setup is called a MySQL REST Service development setup.

4.1.2 Published State

Once a REST service is ready to be published, the corresponding flag can be set on the REST service. This makes the REST service accessible by all authenticated clients.

4.1.3 Disabled State

Should a REST service be retired, it can be disabled by setting the corresponding flag on the REST service.

4.2 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.3 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.3.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.3.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.3.3 REST Service Definitions

4.3.3.1 About MRS AutoREST

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

4.3.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.3.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.4 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.4.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`
    COMMENT "The sakila schema";

4.4.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.4.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.5 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 data mapping views, stored procedures are added as REST procedures.

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

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

4.5.1 Adding a Schema Object via MRS DDL

On an active MySQL Shell connection execute the CREATE REST DATA MAPPING 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 data mapping view for the sakila.city database schema table.

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

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

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

4.5.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.5.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 data mapping 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 REST Data Mapping Views

6.1 Introduction to REST Data Mapping Views

REST data mapping 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 REST data mapping 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 REST data mapping 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 Data Mapping View Workflow

The REST data mapping views can be created using the CREATE REST DATA MAPPING VIEW MRS DDL statement or interactively using the MRS Object Dialog of the MySQL Shell for VS Code extension.

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

• GET a document from the REST data mapping view
• Make any changes needed to the document, including changes to the nested JSON objects
• PUT the document back into the REST data mapping 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 REST data mapping 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

REST data mapping 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 REST View Design

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

MySQL Shell for VS Code includes the MySQL REST Object dialog which features an advanced Data Mapping designer. Using this designer it is possible to create even complex, nested REST data mapping views within seconds.

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

6.3.1 Building a REST Data Mapping View

Building a REST data mapping 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 data mapping 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 DATA MAPPING 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 VIEW /city
AS `sakila`.`city`
AUTHENTICATION REQUIRED;

SHOW CREATE REST VIEW /city;

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

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 VIEW /city
AS `sakila`.`city` @INSERT @UPDATE @DELETE
AUTHENTICATION REQUIRED;

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

6.3.2 Creating a Nested REST Data Mapping 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.

GET /myService/sakila/city/1

{
    "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 REST Data Mapping 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.

GET /myService/sakila/city/1

{
    "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 REST Data Mapping 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.

GET /myService/sakila/city/1

{
    "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.5 REST View Object Identifiers

When a REST View maps to a database table, the primary key(s) specified for that table constitute the identifier of the corresponding REST Documents. If a table has a composite primary key, the identifier is a comma-separated string with the values of each column that composes the primary key.

CREATE TABLE IF NOT EXISTS sakila.my_table (id1 INT, id2 INT, name VARCHAR(3), PRIMARY KEY (id1, id2));
INSERT INTO sakila.my_table VALUES (1, 1, "foo");

CREATE OR REPLACE REST VIEW /myTable
    AS `sakila`.`my_table` @UPDATE;

Retrieving the specific REST document can be done as follows:

GET /myService/sakila/myTable/1,1

{
    "id1": 1,
    "id2": 1,
    "links": [
        {
            "rel": "self",
            "href": "/myService/sakila/myTable/1,1"
        }
    ],
    "name": "foo",
    "_metadata": {
        "etag": "48819BABCBBA1491DBBDFE0D7A270FA3FDF8A16DA8E44E42C61759DE1F0D6A38"
    }
}

Updating a specific REST document can be done as follows:

PUT /myService/sakila/myTable/1,1
{
    "id1": 1,
    "id2": 2,
    "name": "bar"
}

{
    "id1": 1,
    "id2": 1,
    "links": [
        {
            "rel": "self",
            "href": "/myService/sakila/myTable/1,1"
        }
    ],
    "name": "bar",
    "_metadata": {
        "etag": "48819BABCBBA1491DBBDFE0D7A270FA3FDF8A16DA8E44E4AA62559DE1F0D6A42"
    }
}

However, if a database table does not have any primary key, it is no longer possible to access or modify specific documents using the corresponding REST View. The same problem occurs for a database view, where there is no concept of a primary key. In both cases, the REST View requires an explicit mapping between its fields and the underlying database table columns that can/must be used as identifiers. In the former, it should be a direct mapping between one or more REST View fields and the corresponding table columns. In the latter, the mapping must exist for ALL primary key columns of every table used by the database view, which must also be included in the result set produced by that view.

For these specific scenarios, users can manually specify the REST View fields that map to their corresponding document identifiers.

CREATE TABLE IF NOT EXISTS sakila.my_table (id1 INT, id2 INT, name VARCHAR(3));

CREATE OR REPLACE REST VIEW /myTable
AS `sakila`.`my_table` @UPDATE {
    id1: id1 @KEY,
    id2: id2 @KEY,
    name: name
};

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://example.com or a list of domains (for example, ["https://example.com", "https://example.net"]).
• 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
• Data Mapping
  • 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 Overview

As a HTTP REST service, MRS performs its own authentication and authorization checks separately from the MySQL server.

In general, anyone or anything that intends to access a MRS endpoint needs to first authenticate with it as a specific user account. That user must also have specific privileges to access that object and execute the desired HTTP method (GET, POST etc).

MRS defines 5 distinct types of users according to the type of activities they’re allowed to perform in a MRS deployment. These users map to 5 MySQL roles created during MRS configuration, which can be granted in any combination to one or more MySQL user accounts.

All roles have the minimal set of MySQL privileges necessary, mostly restricted to internal MRS metadata tables. By default, they have no access to any other schemas or tables. However, some roles must be granted varying levels of access to user schemas, tables and other DB objects necessary for their purpose.

8.1.1 MRS Administrative Users

Administrative tasks (configuration, creating and managing endpoints etc) are performed through MySQL Shell. The operations that a user is allowed to perform depend on the MySQL user that was used to connect MySQL Shell to MySQL (i.e. the Username that was set when the Connection was created in MySQL Shell for VSCode or passed in the command line of mysqlsh).

Note that any MySQL root or admin users that have full privileges to MySQL will also have full privileges to MRS instances. Therefore, it is recommended that MRS management is done using a dedicated MySQL user with minimal privileges.

8.1.1.1 Service Administrator (mysql_rest_service_admin)

A Service Administrator is allowed to:

• add, manage and remove MRS services
• add, manage and remove endpoint schemas
• add, manage and remove endpoints to tables, views and routines to MRS schemas
• add, manage and remove authentication apps
• add, manage and remove user accounts and roles
• add, manage and remove content sets and files (static files that can be served via HTTP)
• manage and monitor MySQL Router status and logs
• view the MRS audit log

Service administrators must have the mysql_rest_service_admin MySQL role.

8.1.1.2 Schema Administrator (mysql_rest_schema_admin)

The main purpose of a Schema Administrator is to create REST endpoint schemas, so that objects of that schema can be published as endpoints of a MRS service. They need access to both MRS metadata tables and user schemas

A Schema Administrator is allowed to:

• add, manage and remove endpoint schemas
• add, manage and remove endpoints to tables, views and routines to MRS schemas
• add, manage and remove content sets and files
• monitor MySQL Router status and logs
• view the MRS audit log

Schema administrators must have the mysql_rest_schema_admin MySQL role. In addition to that role, Schema Administrator users must be able to view and grant all relevant privileges on objects that they wish to create endpoints for (e.g. SELECT, INSERT, UPDATE, DELETE on tables that will be published and updatable, WITH GRANT OPTION). These privileges will automatically be re-granted by MySQL Shell to the mysql_rest_service_data_provider role, so that MySQL Router can do whatever is required by the endpoint configuration.

8.1.1.3 Developer (mysql_rest_service_dev)

Developers have mostly the same privileges as Schema Administrators, except they cannot add new schemas to a service.

Developers are allowed to:

• use/reference existing endpoints schemas
• add, manage and remove endpoints to tables, views and routines to MRS schemas
• add, manage and remove content sets and files
• monitor MySQL Router status and logs
• view the MRS audit log

Developers must have the mysql_rest_service_dev MySQL role. In addition to that role, Developer users must be able to grant all relevant privileges on objects that they wish to create endpoints for (e.g. SELECT, INSERT, UPDATE, DELETE on tables that will be published and updatable, WITH GRANT OPTION). These privileges will automatically be re-granted by MySQL Shell to the mysql_rest_service_data_provider role, so that MySQL Router can do whatever is required by the endpoint configuration.

8.1.2 MRS Service User

When bootstrapping MySQL Router for MRS, a MySQL user account is automatically created for MRS. That account has these 2 roles granted:

8.1.2.1 Data Access (mysql_rest_service_data_provider)

This is the MySQL role MySQL Router uses to execute SQL necessary to serve HTTP REST requests on behalf of MRS users. This role must have grants on all MySQL objects that are exposed as REST endpoints.

The MySQL Shell automatically manages privileges granted to this role, as DB objects are added as REST endpoints.

Note that access control for MRS users is performed at REST endpoint level by MRS; but all REST requests, regardless of the MRS user they’re originating from, will be executed through the same MySQL user. Care must be taken when exposing views and stored procedures to avoid granting access to objects to unintended users.

8.1.2.2 Metadata Access (mysql_rest_service_meta_provider)

The MySQL Router uses this role when querying the MRS metadata for endpoint configuration, MRS user account information etc. This role only has access to internal metadata tables.

8.2 Authentication Management

MRS currently supports the following authentication methods.

8.2.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.2.2 MySQL Internal Authentication

Authentication is handled my MRS against MySQL server user accounts. Applications send the credentials (username and password) in clear text as part of a JSON request payload to the MySQL Router for authenticating a user.

This authentication method is recommended for HTTPS-only REST services and is most suitable for applications that are not exposed publicly.

8.2.3 OAuth2 Authentication

Several OAuth2 services from 3rd-party vendors are supported by MRS; for example, sign in with FaceBook, Google or the OCI OAuth2 service. 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 APP ID and APP SECRET - need to be configured on the MRS side.

8.2.3.1 Configuring OCI OAuth2

After logging into the OCI web console, select Identity & Security and then Domains from the Navigation Menu or directly go to cloud.oracle.com/identity/domains.

Select the root compartment in the List scope and click on the Default domain.

8.2.3.1.1 Looking Up the URL Option

After the Default domain has been opened, take note of the Domain URL on the Domain information tab. This URL needs to be provided when creating the REST authentication app.

8.2.3.1.2 Creating an OCI OAuth2 Integrated Application

Click on the Integrated applications link on the left hand side, then click the Add application button at the top.

1. Select Confidential Application and confirm by clicking Launch workflow button.
2. Set a Name and Description for your REST application and press Next.
3. Choose Configure this application as a resource server now.
   • Set the Primary audience to MySQL-REST-Service.
4. Choose Configure this application as a client now
   • In the Authorization section, check the Client credentials and Authorization code checkboxes.
   • Enter the correct Redirect URL using the format https://<router-address>/<rest-service>/authentication/login?authApp=<authAppName>&sessionType=<bearer | cookie>.
     • Example: https://rest.example.com/myService/authentication/login?authApp=OCI&sessionType=cookie
   • Ensure the Client type is set to Confidential.
   • In the Allowed operations sections check the Introspect checkbox.
   • Turn the Bypass consent on.
   • Set Client IP address to Anywhere.
   • Set Authorized resourced to All.
5. Select to Skip the Web tier policy and press Finish.

You will be taken to your new Integrated Application. In the OAuth configuration / General Information section the Client ID and the Client secret are show. These need to be provided when creating the REST authentication app.

Please see the CREATE REST AUTH APP section how to create a REST authentication app using the "OCI OAuth2" vendor next.

8.2.3.2 Configuring the Redirection URL of a REST service

After configuring the OAuth2 vendor specific authentication app and creating the corresponding REST authentication app, the redirection URL of the REST service needs to be configured.

The redirection URL tells the MySQL Router which URL it should send the user to after the authentication process against the OAuth2 server has been completed for a specific REST service.

The redirection URL can be set using the ALTER REST SERVICE command. Please see REST service authentication settings for more details.

Alternatively the redirection URL can be set by opening the REST service dialog and switching to the Authentication tab sheet.

8.3 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.

From an endpoint’s perspective, access to REST resources can be controlled at the following levels:

• Service
• Schema
• Object

That is, if a user has read access to a schema, then they will have read access to all objects in that schema of a service.

It is possible to grant CREATE, READ, UPDATE and DELETE privileges at any of these levels.

8.3.1 MRS Roles

A MRS role encapsulates a set of privileges for REST endpoints which can be granted as a whole to individual MRS users of a service. Roles can also be organized hierarchically, or extended into new roles with additional privileges.

For example, in a simple blog application that has an endpoint for /myService/blog/post, we could have 3 roles:

• reader, who can only read posts;
• poster, who can create and update posts, besides reading them and
• editor, which has the same privileges as a poster, but can also delete them

The following snippet creates these 3 roles and grants them to three different user.

# This example assumes a MySQL database schema "blog" and a schema table "post" has been created before.
CREATE SCHEMA IF NOT EXISTS blog;
CREATE TABLE IF NOT EXISTS blog.post(id INT PRIMARY KEY AUTO_INCREMENT, message TEXT);

CREATE REST SERVICE /myTestService;
USE REST SERVICE /myTestService;

CREATE REST SCHEMA /blog FROM blog;
CREATE REST VIEW /post ON SCHEMA /blog AS blog.post;

CREATE REST ROLE "reader";
GRANT REST READ ON SCHEMA /blog OBJECT /post TO "reader";

CREATE REST ROLE "poster" EXTENDS "reader";
GRANT REST CREATE, UPDATE ON SCHEMA /blog OBJECT /post TO "poster";

CREATE REST ROLE "editor" EXTENDS "poster";
GRANT REST DELETE ON SCHEMA /blog OBJECT /post TO "editor";

SHOW REST ROLES;

CREATE REST AUTH APP "TestAuthApp" VENDOR MRS;

CREATE REST USER "ulf"@"TestAuthApp" IDENTIFIED BY "********";
GRANT REST ROLE "reader" TO "ulf"@"TestAuthApp";

CREATE REST USER "alfredo"@"TestAuthApp" IDENTIFIED BY "********";
GRANT REST ROLE "poster" TO "alfredo"@"TestAuthApp";

CREATE REST USER "mike"@"TestAuthApp" IDENTIFIED BY "********";
GRANT REST ROLE "editor" TO "mike"@"TestAuthApp";

# Now, these three users can login with the specified password via MRS authentication.

By default, roles are specific to a service. The service to which a role belongs to can be specified directly in the CREATE REST ROLE in the statement but if omitted, it will be created in the current default service. Role names only need to be unique within a service:

CREATE REST ROLE "myrole" ON SERVICE /myOtherService;

# The role is created in service /myTestService, which is the current default
CREATE REST ROLE "myrole";

SHOW CREATE REST ROLE "myrole" ON SERVICE /myTestService;
SHOW CREATE REST ROLE "myrole" ON SERVICE /myOtherService;

It is also possible to create roles that can be used from any service, by specifying the ON ANY SERVICE clause:

CREATE REST ROLE "globalRole" ON ANY SERVICE;

9 MRS Examples

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

The MRS Notes example implements as simple Progressive Web Apps (PWA) to showcase the features offered by MRS.

The MRS Scripts example includes a set of examples that showcase server side rendering of HTML pages.

9.1 MRS Notes Example

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 MrsNotes project implements a TypeScript demo app that allows to create, manipulate and share 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, navigate to the NPM SCRIPTS View in the sidebar and right-click on package.json to select Run Install. Alternatively, set the focus to the TERMINAL tab and enter npm install to install the required node modules
3. In the NPM SCRIPTS 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 REST Content Set dialog set the Request Path the app should be using, e.g. /app and click OK 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.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

9.2 MRS Scripts Example

The MRS Scripts Example project implements a set of simple MRS scripts, including two examples of how to perform Server Side Rendering with MRS.

9.2.1 MRS Scripts Example 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 Scripts 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_scripts
2. Configure the MySQL REST Service.
3. Create a new MRS service (e.g. /myService).
4. Ensure a bootstrapped MySQL Router instance is running (if not, start it).
5. Build and deploy the MRS Scripts by following the steps below.

9.2.1.1 Deploying the MRS Scripts Examples

The MRS Script Examples are written in TypeScript and need to be built before they can be uploaded to MRS. Please follow these steps to deploy the examples.

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_scripts
2. After the project folder has been opened in VS Code, navigate to the NPM SCRIPTS View in the sidebar and right-click on package.json to select Run Install. Alternatively, set the focus to the TERMINAL tab and enter npm install to install the required node modules
3. In the NPM SCRIPTS View, run the package.json/build command that will create a folder called build that contains all files needed for deployment.
4. Right click on the background below the last file in the Folders view and select Upload Folder to MySQL REST Service from the popup menu.
5. In the REST Content Set dialog make sure that the Enable MRS Scripts checkbox is checked and click OK 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/testScripts/preactTestPage.html

9.2.1.2 Using MySQL Shell to Deploy the MRS Scripts Examples

Apart from using the MySQL Shell for VS Code extension it is also possible to use the MySQL Shell to upload the MRS Scripts to MRS.

~/.mysqlsh-gui/mysqlsh dba@localhost --sql -e 'CREATE OR REPLACE REST CONTENT SET /mrsScriptsContent ON SERVICE /myService FROM "~/path_to_project_folder/mrs_scripts" LOAD SCRIPTS'

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