MySQL REST Service - Reference Manual

#
MySQL REST Service - Reference Manual

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 - Quickstart Guide - This book provides a hands-on approach to learning how to use the MySQL REST service.
- 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, please check out the Quickstart Guide or take a look at the MRS Notes Example PWA 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

MySQL REST Service - Feature Overview

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.

MySQL App Development

### 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

Feature Overview
Feature | Description
--- | ---
REST Service Lifecycle Management | Shared development of new REST services, publishing of production-ready REST services
AutoREST | Enabling REST access to a table, view, or procedure allows it to be accessed through RESTful services. AutoREST is a quick and easy way to expose database tables as REST resources, first introduced by ORDS.
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.
Serving Static Content | In addition to serving dynamic content using AutoREST it is possible to upload static content, like HTML, CSS, and image files. This feature is not meant to replace dedicated HTTP servers that support capabilities like server-side programming. It can aid the quick deployments of prototypes and proof-of-concept efforts that help bring ideas to life.
End User Authentication | MRS supports a number of authentication methods, including MRS REST service specific authentication, native MySQL authentication and OAuth2 authentication (Sign in with FaceBook and Google)
End User Authorization | Built in support for row-level security, role based security, user-hierarchy based security, Group based security, Group-hierarchy based security as well as custom authorization support
REST Service SDK API Generation | Live SDK API updates for interactive prototyping using TypeScript, SDK API generation for application development

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.

## 1.4 Supported MySQL Versions

MySQL REST Service can be configured using MySQL Server versions from 8.0.39 onwards.

A MySQL Router 9.3.1 or later is required to serve the REST endpoints and Progressive Web Apps. The MySQL Shell for VS Code extension includes a recent MySQL Router version for local development deployments.

Alternatively, experimental packages of MySQL Server including the upcoming MySQL REST Service server component can be downloaded from labs.mysql.com.

It is recommended to use the latest available version of MySQL Shell (9.4.0 and higher) to configure and work with the MySQL REST Service, regardless of the MySQL Server and MySQL Router versions.

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

MySQL REST Service Architecture Diagram

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

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.

MRS User Roles
Access Level | MySQL Role Name | Description
--- | --- | ---
Root | - | MySQL Users with `ALL PRIVILEGES`, like the MySQL default `root` user, have full access to all features
REST Service Admin | `mysql_rest_service_admin` | MySQL users that are granted the ‘mysql_rest_service_admin’ role have full access to all features
REST Schema Admin | `mysql_rest_service_schema_admin` | The ‘mysql_rest_service_schema_admin’ role allows MySQL users to add new REST schemas and endpoints to an existing REST service
REST Service Developer | `mysql_rest_service_dev` | REST Service Developers are allowed to define new REST endpoints for existing REST schemas
REST Service User | `mysql_rest_service_user` | Any MySQL user that should be able to access REST endpoints needs to be granted the ‘mysql_rest_service_user’ role.

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.

MRS Provider Roles
Access Level | MySQL Role Name | Description
--- | --- | ---
Metadata Schema Read-Only | `mysql_rest_service_meta_provider` | The metadata provide role is used by the MySQL Router/Server MRS component to identify the REST services that need to be served.

Application Data Access

`mysql_rest_service_data_provider`

The data provide role is used by the MySQL Router/Server MRS component to read(/write) the application data that should be served by the REST services. This applies to all REST users authenticated via the ‘MRS’ `REST AUTH VENDOR` as well as all OAuth2 vendors. REST Users authenticated via the ‘MySQL Internal’ vendor use their own privileges.

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.

Bootstrap and Start MySQL Router

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

MySQL Router MRS Bootstrap Account Options
Option | Description
--- | ---
`--mrs-mysql-metadata-account=USER_NAME` | Setting the MRS metadata user
`--mrs-mysql-data-account=USER_NAME` | Setting the MRS data user

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.

MySQL Router Bootstrap options
Option | Description
--- | ---
`--mrs` | Include MRS configuration
`--mrs-mysql-metadata-account=USER_NAME` | Setting the MRS metadata user
`--mrs-mysql-data-account=USER_NAME` | Setting the MRS data user
`--mrs-global-secret=SECRET` | The global JWT secret that must be the same for every MySQL Router installation
`--mrs-developer MYSQL_USER_NAME` | Switches the MySQL Router to developer mode
`--mrs-developer-debug-port` DEBUG_PORT | The port used for local debugging of MRS Scripts

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

## 3.4 Installing the MRS Server Component

> Please note that the MRS Server Component is not available in production quality yet. Experimental packages of MySQL Server including the upcoming MySQL REST Service server component can be downloaded from labs.mysql.com.

The MySQL REST Service server component can be managed the same way as any other server component.

During installation, the MRS server component replaces the MySQL X Plugin to serve as the preferred MySQL Document Store solution. Rather than serving the MySQL X Protocol, the MySQL Server then enables clients to access MRS REST endpoints via the HTTPS protocol.

> Important: If any active clients are still using the MySQL X protocol, the MRS server component must not be installed before migrating these clients to the classic MySQL protocol. Alternatively, a MySQL Router instance can be deployed to serve the MRS REST endpoints.

The MRS server component can be installed before configuring the MySQL instance for the MySQL REST Service support. In this case, the component will remain in a waiting state until MRS has been configured. No HTTP access is available during this time.

The following SQL command can be used to install the MRS server component.

    INSTALL COMPONENT "file://component_mysql_rest_service";

Example

The following example installs the MRS server component and sets related MRS system variables at install time:

    INSTALL COMPONENT "file://component_mysql_rest_service"
        SET GLOBAL component_mysql_rest_service.use_ssl = 0,
        GLOBAL component_mysql_rest_service.mrs_user = "miguel";

### 3.4.1 Uninstalling the MRS Server Component

To uninstall the MRS server component, the following SQL command can be used.

1. `UNINSTALL COMPONENT "file://component_mysql_rest_service";`

If the MySQL X Plugin was available before, it will be loaded again when uninstalling the MRS server component.

### 3.4.2 MRS Server Component Configuration

The following MySQL system variables are available to configure the MRS server component.

MRS System Variables Overview
System Variable | Description
--- | ---
component_mysql_rest_service.mrs_user | Defines the REST Service User. Automatically generated when component is installed and the SysVar has not been set at install time.
component_mysql_rest_service.metadata_cache_user | Metadata Cache User. Allows defining the account used by the component to query and access MySQL Database Architectures to handle transparent query routing. The account is automatically generated during installation if the system variable was not set at that time.
component_mysql_rest_service.http_port | Rest Service HTTP Port. If the MySQL X Plugin is enabled on the server, the MySQL X Protocol port will be used and the plugin will be disabled. If the X Plugin is disabled, the default port is 8543.
component_mysql_rest_service.use_ssl | Enables TLSv1.2 (or later) support. If the server has it enabled/supported, then it’s enabled by default.
component_mysql_rest_service.ssl_cert | SSL certificate filename. Uses Server default certificate if not specified.
component_mysql_rest_service.ssl_key | SSL key filename. Uses Server default key if not specified.
component_mysql_rest_service.developer | Rest Service Developer. Allows defining the developer username when the server is used in a development setup. If set, REST services that have not yet been published will be served, as well as REST services owned by the specified developer. Default is empty/disabled.

To check current values of all related MySQL system variables, execute the following SHOW statement.

    SHOW variables LIKE '%mysql_rest_service%';

To set a related MySQL system variable, execute the following statements that set the variable and then restart the MRS server component.

    SET PERSIST component_mysql_rest_service.developer = 'mike';
    SELECT component_mysql_rest_service_restart();

> Please note that a restart of the MRS server component is required to activate the change.

### 3.4.3 MRS Server Component Status Variables

The following MySQL status variables are available:

MRS Status Variables Overview
Status Variable | Description
--- | ---
component_mysql_rest_service.http_port_source | Indicates how the HTTP port was configured. If set to `default` port 8543 is used. If set to `xplugin` the port was taken from the MySQL X Plugin. If set to `user` the port was individually configured by the user.
component_mysql_rest_service.ssl_cert_source | Indicates how the SSL-certificate was configured. If set to `mysql` the MySQL Server configuration is used. If set to `user` the source was configured by the user.
component_mysql_rest_service.ssl_key_source | Indicates how the SSL-key was configured. If set to `mysql` the MySQL Server configuration is used. If set to `user` the source was configured by the user.
component_mysql_rest_service.current_mrs_user | Indicates the REST Service User account currently in use. If the account was auto-generated, the name is suffixed with “(auto-generated)”.
component_mysql_rest_service.current_metadata_cache_user | Indicates the Metadata Cache account currently in use. If the account was auto-generated, the name is suffixed with “(auto-generated)”.

To query a MRS status variable use a SELECT statement like the following.

    SELECT VARIABLE_VALUE FROM performance_schema.global_status
    WHERE VARIABLE_NAME = 'component_mysql_rest_service.http_port_source';

To query all Status Variables use a SELECT statement like the following.

    SHOW STATUS LIKE 'component_mysql_rest_service%';

### 3.4.4 MRS Server Component UDFs

The following UDFs are available to control the MRS server component:

MRS server component UDF Overview
UDF Call | Description
--- | ---
`SELECT component_mysql_rest_service_start();` | Starts the MRS server component after it has been stopped.
`SELECT component_mysql_rest_service_stop();` | Stops the MRS server component.
`SELECT component_mysql_rest_service_restart();` | Restarts the MRS server component.

### 3.4.5 MySQL Database Architectures Support

Version 15 of the Experimental package, available from labs.mysql.com, adds support for running the component within MySQL Database Architectures, enabling it to operate seamlessly in the following topologies:

- InnoDB Cluster
- InnoDB ReplicaSet
- InnoDB ClusterSet

When the component detects that the MySQL instance is part of a managed topology, it automatically starts the metadata-cache plugin and integrates with it.

> Important: In the current release, the component must be installed and run only on the PRIMARY member of a managed topology. Attempting to run it on a SECONDARY member results in an error, and the component stops gracefully.

> To install it across all members, perform a switchover and repeat the installation on each member until all have it installed.

> This is a temporary limitation, future releases will support installing and running the component on all members of the topology.

For more information on each topology, refer to the documentation:

- InnoDB Cluster Documentation
- InnoDB ReplicaSet Documentation
- InnoDB ClusterSet Documentation

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

Adding a 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.

Adding a Database 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.

REST Schema and its 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.

Adding a Database 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.

DB Notebook - Switch to TS 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.

Setting the Current REST Service

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)",
       "cityId": 1,
       "countryId": 87,
       "lastUpdate": "2006-02-15 04:45:25.000000",
    }

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.find({select: ["city", "cityId"], where: {city: {$like: "NE%"}}});
    [
        {
            "city": "Newcastle",
            "cityId": 364,
        },
        {
            "city": "Nezahualcyotl",
            "cityId": 365,
        }
    ]

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.

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

REST VIEW - 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.

JSON Relational Editor

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.

CRUD Operation Mapping
Letter | CRUD Operation | SQL Operation
--- | --- | ---
C | CREATE | CREATE
R | READ | SELECT
U | UPDATE | UPDATE
D | DELETE | DELETE

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

Adding a Referenced Table

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...` dropdown can be used to select the column that should be selected for the reduce operation.

Unnest a Referenced Table

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

REST Service Properties
Option | Description
--- | ---
MRS Service Path | The URL context root of this service
Comments | Comments to describe this service
Host Name | If specified, only requests for this host are served
Supported Protocols | The supported protocols (HTTPS by default)
Enabled | Specifies if the service is served by MySQL Router
Options | Advanced options in JSON format

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

REST Schema Properties
Option | Description
--- | ---
MRS Service Path | The path of the REST service for this REST schema
Comments | Comments to describe this MRS schema
REST Schema Path | The request path to access the schema (must start with /)
Schema Name | The name of the corresponding database schema
Items per Page | The default number of items to be returned when requesting REST objects of this schema
Enabled | Whether or not the REST objects of this REST schema are exposed through the REST interface
Requires Authentication | Whether or not authentication is required to access the REST objects of this REST schema
Options | Additional options in JSON format

## 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

The MySQL REST Object Dialog

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

mrsNotes App running on a Mobile

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

mrsNotes MySQL Database 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.