| Server IP : 185.208.173.17 / Your IP : 87.236.161.98 Web Server : Microsoft-IIS/10.0 System : Windows NT SRV8576125506 10.0 build 26100 (Windows Server 2016) AMD64 User : IUSR ( 0) PHP Version : 7.4.13 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : OFF | Perl : OFF | Python : OFF | Sudo : OFF | Pkexec : OFF Directory : /Program Files/MySQL/MySQL Workbench 8.0/swb/shell/lib/mysqlsh/plugins/mrs_plugin/docs/ |
Upload File : |
<!DOCTYPE html>
<html lang="en" dir="ltr">
<!-- Copyright (c) 2022, 2026, Oracle and/or its affiliates.-->
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>MySQL REST Service - Reference Manual</title>
<link rel="shortcut icon" href="images/favicon.png" type="image/x-icon">
<link rel="stylesheet" href="style/style.css">
<!-- cSpell:ignore pagetitle -->
<title>MySQL REST Service - Reference Manual</title>
<style type="text/css">
code {
white-space: pre;
}
</style>
<script>
window.addEventListener('DOMContentLoaded', () => {
window.visibleItems = [];
window.lastVisible = null;
const observer = new IntersectionObserver((entries) => {
// Add active css class to first match
entries.forEach((entry) => {
const id = entry.target.getAttribute('id');
const querySel = document.querySelector(`nav li a[href="#` + id + `"]`);
if (querySel) {
const el = querySel.parentElement;
if (entry.isIntersecting) {
if (!window.visibleItems.includes(el)) {
if (window.visibleItems.length === 0 && window.lastVisible !== null) {
window.lastVisible.classList.remove('active');
window.lastVisible = el;
}
if (window.lastVisible === null) {
window.lastVisible = el;
}
window.visibleItems.push(el);
}
if (!el.classList.contains('active')) {
el.classList.add('active');
}
history.pushState({}, "", "#" + id);
} else {
if (window.visibleItems.includes(el)) {
if (window.visibleItems.length === 1) {
window.lastVisible = el;
} else {
el.classList.remove('active');
}
const index = window.visibleItems.indexOf(el);
if (index > -1) {
window.visibleItems.splice(index, 1);
}
if (window.lastVisible === el && window.visibleItems.length > 0) {
window.lastVisible === window.visibleItems[0];
}
}
}
}
});
}/*, { threshold: 0.1, rootMargin: '0px 0px -100% 0px', root: document.body }*/);
// Track all h that have an `id` applied
document.querySelectorAll('.content h1[id],h2[id]').forEach((section) => {
observer.observe(section);
});
document.querySelectorAll('a[href^="#"]').forEach(anchor => {
anchor.addEventListener('click', function (e) {
e.preventDefault();
document.querySelector(this.getAttribute('href')).scrollIntoView({
behavior: 'smooth'
});
});
});
if (typeof acquireVsCodeApi === "function") {
document.vscode = acquireVsCodeApi();
const addVsCodeDecorations = () => {
const addVsActionBtn = (e, command, caption) => {
const d = document.createElement("button");
d.innerHTML = '<i class="arrow right"></i><p>' + caption + '</p>';
d.onclick = () => {
document.vscode.postMessage({ command, path: e.innerHTML });
}
e.parentNode.insertBefore(d, e.nextSibling)
}
document.querySelectorAll("code").forEach((e) => {
if (e.innerHTML.endsWith(".sql")) {
addVsActionBtn(e, "openSqlFile", "Open in MySQL Shell");
} else if (e.innerHTML.endsWith(".mrs.json")) {
addVsActionBtn(e, "loadMrsDump", "Load MRS Dump");
} else if (e.innerHTML.startsWith("VSCodeProject:") > 0) {
e.innerHTML = e.innerHTML.slice("VSCodeProject:".length);
addVsActionBtn(e, "saveProject", "Save Project");
}
});
}
addVsCodeDecorations();
} else {
document.querySelectorAll("code").forEach((e) => {
if (e.innerHTML.startsWith("VSCodeProject:") > 0) {
e.innerHTML = e.innerHTML.slice("VSCodeProject:".length);
}
});
}
});
// Handle the message inside the webview
window.addEventListener('message', event => {
const message = event.data; // The JSON data our extension sent
switch (message.command) {
case 'goToId':
if (message.id) {
window.location = "#" + message.id;
}
break;
}
});
</script>
</head>
<body>
<div class="page">
<div class="sidebar">
<div class="bookOverview">
<h1>MySQL REST Service</h1>
<h2>
<div class="icon bookIcon"></div> <a href="index.html#document-top">Reference Manual</a>
</h2>
<h2>
<div class="icon bookIcon"></div> <a href="quickstart.html#document-top">Quickstart Guide</a>
</h2>
<h2>
<div class="icon bookIcon"></div> <a href="sql.html#document-top">SQL Reference</a>
</h2>
<h2>
<div class="icon bookIcon"></div> <a href="restApi.html#document-top">Core REST APIs</a>
</h2>
<h2>
<div class="icon bookIcon"></div> <a href="sdk.html#document-top">SDK Reference</a>
</h2>
</div>
<div class="sidebarContent">
<nav class="section-nav">
<div class="bookToc">
<br>
<h3>MySQL REST Service - Reference
Manual 2026.3.0+9.6.1</h3>
<ul>
<li><a
href="#introduction-to-the-mysql-rest-service"
id="toc-introduction-to-the-mysql-rest-service"><span
class="toc-section-number">1</span>
Introduction to the
MySQL REST Service</a>
<ul>
<li><a
href="#what-is-the-mysql-rest-service"
id="toc-what-is-the-mysql-rest-service"><span
class="toc-section-number">1.1</span>
What is the MySQL REST
Service</a></li>
<li><a
href="#application-use-cases"
id="toc-application-use-cases"><span
class="toc-section-number">1.2</span>
Application Use
Cases</a></li>
<li><a
href="#feature-set-overview"
id="toc-feature-set-overview"><span
class="toc-section-number">1.3</span>
Feature Set
Overview</a></li>
<li><a
href="#supported-mysql-versions"
id="toc-supported-mysql-versions"><span
class="toc-section-number">1.4</span>
Supported MySQL
Versions</a></li>
</ul></li>
<li><a
href="#architecture"
id="toc-architecture"><span
class="toc-section-number">2</span>
Architecture</a>
<ul>
<li><a
href="#building-blocks"
id="toc-building-blocks"><span
class="toc-section-number">2.1</span>
Building Blocks</a></li>
<li><a
href="#development-setup"
id="toc-development-setup"><span
class="toc-section-number">2.2</span>
Development
Setup</a></li>
<li><a
href="#production-deployments"
id="toc-production-deployments"><span
class="toc-section-number">2.3</span>
Production
Deployments</a></li>
</ul></li>
<li><a
href="#configuring-mysql-rest-service"
id="toc-configuring-mysql-rest-service"><span
class="toc-section-number">3</span>
Configuring MySQL REST
Service</a>
<ul>
<li><a
href="#configuring-the-mrs-metadata-schema"
id="toc-configuring-the-mrs-metadata-schema"><span
class="toc-section-number">3.1</span>
Configuring the MRS
Metadata Schema</a></li>
<li><a
href="#granting-users-access-to-the-mysql-rest-service"
id="toc-granting-users-access-to-the-mysql-rest-service"><span
class="toc-section-number">3.2</span>
Granting Users Access to
the MySQL REST
Service</a></li>
<li><a
href="#bootstrapping-and-running-mysql-routers-with-mrs-support"
id="toc-bootstrapping-and-running-mysql-routers-with-mrs-support"><span
class="toc-section-number">3.3</span>
Bootstrapping and
Running MySQL Routers
with MRS
Support</a></li>
<li><a
href="#installing-the-mrs-server-component"
id="toc-installing-the-mrs-server-component"><span
class="toc-section-number">3.4</span>
Installing the MRS
Server
Component</a></li>
</ul></li>
<li><a
href="#adding-rest-services-and-database-objects"
id="toc-adding-rest-services-and-database-objects"><span
class="toc-section-number">4</span>
Adding REST Services and
Database Objects</a>
<ul>
<li><a
href="#rest-service-lifecycle-management"
id="toc-rest-service-lifecycle-management"><span
class="toc-section-number">4.1</span>
REST Service Lifecycle
Management</a></li>
<li><a
href="#preconditions-for-adding-a-rest-service"
id="toc-preconditions-for-adding-a-rest-service"><span
class="toc-section-number">4.2</span>
Preconditions for Adding
a REST Service</a></li>
<li><a
href="#setting-up-a-new-rest-service"
id="toc-setting-up-a-new-rest-service"><span
class="toc-section-number">4.3</span>
Setting Up a New REST
Service</a></li>
<li><a
href="#adding-a-database-schemas-to-a-rest-service"
id="toc-adding-a-database-schemas-to-a-rest-service"><span
class="toc-section-number">4.4</span>
Adding a Database
Schemas to a REST
Service</a></li>
<li><a
href="#adding-a-schema-table-view-or-procedure"
id="toc-adding-a-schema-table-view-or-procedure"><span
class="toc-section-number">4.5</span>
Adding a Schema Table,
View or
Procedure</a></li>
</ul></li>
<li><a
href="#working-interactively-with-rest-services"
id="toc-working-interactively-with-rest-services"><span
class="toc-section-number">5</span>
Working Interactively
with REST Services</a>
<ul>
<li><a
href="#switching-to-typescript-mode"
id="toc-switching-to-typescript-mode"><span
class="toc-section-number">5.1</span>
Switching to TypeScript
Mode</a></li>
<li><a
href="#choosing-a-rest-service"
id="toc-choosing-a-rest-service"><span
class="toc-section-number">5.2</span>
Choosing a REST
Service</a></li>
<li><a
href="#authentication"
id="toc-authentication"><span
class="toc-section-number">5.3</span>
Authentication</a></li>
<li><a
href="#querying-a-rest-object"
id="toc-querying-a-rest-object"><span
class="toc-section-number">5.4</span>
Querying a REST
Object</a></li>
</ul></li>
<li><a
href="#rest-data-mapping-views"
id="toc-rest-data-mapping-views"><span
class="toc-section-number">6</span>
REST Data Mapping
Views</a>
<ul>
<li><a
href="#introduction-to-rest-data-mapping-views"
id="toc-introduction-to-rest-data-mapping-views"><span
class="toc-section-number">6.1</span>
Introduction to REST
Data Mapping
Views</a></li>
<li><a
href="#lock-free-optimistic-concurrency-control"
id="toc-lock-free-optimistic-concurrency-control"><span
class="toc-section-number">6.2</span>
Lock-Free Optimistic
Concurrency
Control</a></li>
<li><a
href="#interactive-rest-view-design"
id="toc-interactive-rest-view-design"><span
class="toc-section-number">6.3</span>
Interactive REST View
Design</a></li>
</ul></li>
<li><a
href="#mrs-dialog-reference"
id="toc-mrs-dialog-reference"><span
class="toc-section-number">7</span>
MRS Dialog Reference</a>
<ul>
<li><a
href="#mrs-service-dialog"
id="toc-mrs-service-dialog"><span
class="toc-section-number">7.1</span>
MRS Service
Dialog</a></li>
<li><a
href="#mrs-schema-dialog"
id="toc-mrs-schema-dialog"><span
class="toc-section-number">7.2</span>
MRS Schema
Dialog</a></li>
<li><a
href="#mrs-object-dialog"
id="toc-mrs-object-dialog"><span
class="toc-section-number">7.3</span>
MRS Object
Dialog</a></li>
</ul></li>
<li><a
href="#authentication-and-authorization"
id="toc-authentication-and-authorization"><span
class="toc-section-number">8</span>
Authentication and
Authorization</a>
<ul>
<li><a href="#overview"
id="toc-overview"><span
class="toc-section-number">8.1</span>
Overview</a></li>
<li><a
href="#authentication-management"
id="toc-authentication-management"><span
class="toc-section-number">8.2</span>
Authentication
Management</a></li>
<li><a
href="#authorization-management"
id="toc-authorization-management"><span
class="toc-section-number">8.3</span>
Authorization
Management</a></li>
</ul></li>
<li><a
href="#mrs-examples"
id="toc-mrs-examples"><span
class="toc-section-number">9</span>
MRS Examples</a>
<ul>
<li><a
href="#mrs-notes-example"
id="toc-mrs-notes-example"><span
class="toc-section-number">9.1</span>
MRS Notes
Example</a></li>
<li><a
href="#mrs-scripts-example"
id="toc-mrs-scripts-example"><span
class="toc-section-number">9.2</span>
MRS Scripts
Example</a></li>
</ul></li>
</ul>
</div>
</nav>
</div>
</div>
<div class="content">
<div class="header" id="document-top">
<h1>
<div class="iconBig bookIcon"></div> MySQL REST
Service - Reference Manual
</h1>
</div>
<div class="main">
<p>This book contains the documentation for the MySQL
REST Service (MRS).</p>
<p><strong>Chapter Overview</strong></p>
<ul>
<li><a
href="#introduction-to-the-mysql-rest-service">MRS
Introduction</a></li>
<li><a href="#deployment-architecture">MRS Deployment
Architecture</a></li>
<li><a href="#configuring-mysql-rest-service">MRS
Configuration</a></li>
<li><a
href="#adding-rest-services-and-database-objects">Adding
REST Services and Database Objects</a></li>
<li><a
href="#working-interactively-with-rest-services">GUI
Interfaces for REST Service Configuration</a></li>
<li><a
href="#authentication-and-authorization">Authentication
and Authorization</a></li>
<li><a href="#mrs-examples">MRS Examples</a>
<ul>
<li><a href="#mrs-notes-example">MRS Notes
Example</a></li>
<li><a href="#mrs-scripts-example">MRS Scripts
Example</a></li>
</ul></li>
</ul>
<hr />
<p>Please also see</p>
<ul>
<li><strong><a href="quickstart.html">MySQL REST Service
- Quickstart Guide</a></strong> - This book provides a
hands-on approach to learning how to use the MySQL REST
service.</li>
<li><strong><a href="sql.html">MySQL REST Service - SQL
Reference</a></strong> - This book discusses the MySQL
REST Service SQL Extension.</li>
<li><strong><a href="restApi.html">MySQL REST Service -
Core REST APIs</a></strong> - This book provides
examples of using the MySQL REST Service queries and
other operations against tables and views after you have
REST-enabled them.</li>
<li><strong><a href="sdk.html">MySQL REST Service - SDK
Reference</a></strong> - This book explains how to work
with the MRS Software Development Kit and discusses the
Client APIs.</li>
</ul>
<hr />
<!-- Copyright (c) 2022, 2026, Oracle and/or its affiliates.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License, version 2.0,
as published by the Free Software Foundation.
This program is designed to work with certain software (including
but not limited to OpenSSL) that is licensed under separate terms, as
designated in a particular file or component or in included license
documentation. The authors of MySQL hereby grant you an additional
permission to link the program and your derivative works with the
separately licensed software that they have either included with
the program or referenced in the documentation.
This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See
the GNU General Public License, version 2.0, for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -->
<h1 data-number="1"
id="introduction-to-the-mysql-rest-service"><span
class="header-section-number">1</span> Introduction to
the MySQL REST Service</h1>
<p>Welcome to the MySQL REST Service. This book features
a detailed discussion of the MySQL REST Service,
including architecture, configuration and
deployment.</p>
<p>To start with a hands-on approach, please check out
the <a href="quickstart.html">Quickstart Guide</a> or
take a look at the <a href="#mrs-notes-example">MRS
Notes Example PWA</a> and follow the instructions for <a
href="#build-and-deployment">Build and Deployment</a>
there.</p>
<h2 data-number="1.1"
id="what-is-the-mysql-rest-service"><span
class="header-section-number">1.1</span> What is the
MySQL REST Service</h2>
<p>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.</p>
<p>Being a fully integrated MySQL solution, it focuses
on ease-of-use, support of standards and high
performance.</p>
<p>The MySQL REST Service consists of four major
building blocks, delivering an integrated solution for
JSON Document-based application development.</p>
<ol type="1">
<li>RESTful Web Services</li>
<li>REST SQL Extension</li>
<li>Powerful Data Mapping</li>
<li>Client SDK Generation</li>
</ol>
<figure>
<img
src="sections/devGuide/../../images/MrsFeatureOverview.svg"
title="MySQL REST Service - Feature Overview"
alt="MySQL REST Service - Feature Overview" />
<figcaption aria-hidden="true">MySQL REST Service -
Feature Overview</figcaption>
</figure>
<p><strong><em>Benefits</em></strong></p>
<ul>
<li>Auto-REST endpoints for relational and document
oriented data, that can be enable with a few
clicks.</li>
<li>Directly built into MySQL Router, removes need for
additional middle-ware.</li>
<li>High performance web server solution to serve
RESTful Web Services as well as Progressive Web Apps
(PWAs).</li>
<li>Excellent vertical scaling (up scaling) and well as
horizontal scaling (scaling out) through number of MySQL
Routers.</li>
</ul>
<p><strong><em>Experience</em></strong></p>
<ul>
<li>Direct VS Code Extension integration featuring
point-and-click, WYSIWYG editors and live-querying of
REST endpoints via TypeScript.</li>
<li>Dedicated REST SQL extension support in MySQL Shell
for scripting and development process integration.</li>
<li>Client SDK generation support for popular languages
to vastly simplify development process and project
integration.</li>
<li>Support for local development environment &
debugging.</li>
</ul>
<p><strong><em>Features</em></strong></p>
<ul>
<li>REST endpoints for database tables, views,
procedures and function in addition to static data
(e.g. PWAs) being served</li>
<li>Powerful, built-in authentication, authorization
(MySQL accounts, MRS accounts, OAuth2) and session
management</li>
<li>New REST SQL extension to be able to define REST
services and endpoints directly in SQL scripts</li>
<li>Client SDK generation with built-in features for
authentication, document operations, read-own-write
support in distributed MySQL solutions</li>
</ul>
<h2 data-number="1.2" id="application-use-cases"><span
class="header-section-number">1.2</span> Application Use
Cases</h2>
<h3 data-number="1.2.1"
id="which-applications-should-use-the-mysql-rest-service"><span
class="header-section-number">1.2.1</span> Which
applications should use the MySQL REST Service</h3>
<p>The MySQL REST Service exposes RESTful Web Services
for interacting with the data stored in MySQL solutions
REST endpoints via HTTPS.</p>
<p>This makes the MySQL REST Service an excellent choice
for the following use cases.</p>
<ul>
<li>Mobile applications, as well as Progressive Web Apps
(PWAs), that need to access data across the public
internet.</li>
<li>All modern document-oriented applications that
expect to work with JSON documents rather than
relational data.</li>
<li>Extending existing applications with
micro-services.</li>
<li>Offering data REST endpoints to serverless
architecture deployments.</li>
</ul>
<figure>
<img
src="sections/devGuide/../../images/MrsForAppDevelopment.svg"
title="MySQL App Development"
alt="MySQL App Development" />
<figcaption aria-hidden="true">MySQL App
Development</figcaption>
</figure>
<h3 data-number="1.2.2"
id="which-applications-should-use-a-mysql-connector"><span
class="header-section-number">1.2.2</span> Which
applications should use a MySQL Connector</h3>
<p>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.</p>
<ul>
<li>Applications that need direct SQL access to the
MySQL database.</li>
<li>Applications that need to work with relational
tables rather than JSON documents.</li>
<li>Applications that do not benefit from an optimistic,
ETag based concurrency model.</li>
</ul>
<h2 data-number="1.3" id="feature-set-overview"><span
class="header-section-number">1.3</span> Feature Set
Overview</h2>
<table>
<caption>Feature Overview</caption>
<colgroup>
<col style="width: 37%" />
<col style="width: 62%" />
</colgroup>
<thead>
<tr class="header">
<th>Feature</th>
<th>Description</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td>REST Service Lifecycle Management</td>
<td>Shared development of new REST services, publishing
of production-ready REST services</td>
</tr>
<tr class="even">
<td>AutoREST</td>
<td>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 <a
href="https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/22.2/orddg/introduction-to-Oracle-REST-Data-Services.html#GUID-A16BCCA2-8081-4062-A635-9F7C36FC394F/">ORDS</a>.</td>
</tr>
<tr class="odd">
<td>REST data mapping Views</td>
<td>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.</td>
</tr>
<tr class="even">
<td>Serving Static Content</td>
<td>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.</td>
</tr>
<tr class="odd">
<td>End User Authentication</td>
<td>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)</td>
</tr>
<tr class="even">
<td>End User Authorization</td>
<td>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</td>
</tr>
<tr class="odd">
<td>REST Service SDK API Generation</td>
<td>Live SDK API updates for interactive prototyping
using TypeScript, SDK API generation for application
development</td>
</tr>
</tbody>
</table>
<p><strong><em>About REST APIs</em></strong></p>
<p>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:</p>
<ul>
<li>Data is modelled as a set of resources. Resources
are identified by URIs.</li>
<li>A small, uniform set of operations are used to
manipulate resources (for example, PUT, POST, GET,
DELETE).</li>
<li>A resource can have multiple representations (for
example, a blog might have an HTML representation and an
RSS representation).</li>
<li>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.</li>
</ul>
<h2 data-number="1.4"
id="supported-mysql-versions"><span
class="header-section-number">1.4</span> Supported MySQL
Versions</h2>
<p>MySQL REST Service can be configured using MySQL
Server versions from 8.0.39 onwards.</p>
<p>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.</p>
<p>Alternatively, experimental packages of MySQL Server
including the upcoming MySQL REST Service server
component can be downloaded from <a
href="https://labs.mysql.com">labs.mysql.com</a>.</p>
<p>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.</p>
<!-- Copyright (c) 2022, 2026, Oracle and/or its affiliates.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License, version 2.0,
as published by the Free Software Foundation.
This program is designed to work with certain software (including
but not limited to OpenSSL) that is licensed under separate terms, as
designated in a particular file or component or in included license
documentation. The authors of MySQL hereby grant you an additional
permission to link the program and your derivative works with the
separately licensed software that they have either included with
the program or referenced in the documentation.
This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See
the GNU General Public License, version 2.0, for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -->
<h1 data-number="2" id="architecture"><span
class="header-section-number">2</span> Architecture</h1>
<h2 data-number="2.1" id="building-blocks"><span
class="header-section-number">2.1</span> Building
Blocks</h2>
<p>The MySQL REST Service consists of the following
components:</p>
<ul>
<li>A MySQL Solution (Heatwave, MySQL InnoDB ClusterSet,
a standalone MySQL Server, etc.)
<ul>
<li>Serving a metadata schema
<code>mysql_rest_service_metadata</code> that holds the
MRS configuration.</li>
<li>Serving the REST applications’ data.</li>
</ul></li>
<li>MySQL Router
<ul>
<li>One or many MySQL Router instances to serve the
HTTPS REST interface.</li>
<li>Either running in developer or production mode.</li>
</ul></li>
<li>MySQL Shell / MySQL Shell for VS Code
<ul>
<li>Support for the REST SQL extension, to configure and
manage REST endpoints via REST SQL commands.</li>
<li>Support for managing MRS through a graphical user
interface (GUI) embedded inside VS Code.</li>
<li>Generation of Client SDKs for given REST
service.</li>
</ul></li>
</ul>
<h2 data-number="2.2" id="development-setup"><span
class="header-section-number">2.2</span> Development
Setup</h2>
<p>When working with the MySQL REST Service it is
important to separate between two different types of
setups.</p>
<ol type="1">
<li>A local development setup used to develop new REST
services.
<ul>
<li>A local MySQL Shell installation to connect to and
execute REST SQL extension commands.</li>
<li>A local MySQL Router installation, running in
developer mode.</li>
</ul></li>
<li>The production deployment that serves REST services
that have been published.
<ul>
<li>The MySQL solution serving the metadata schema as
well as the REST applications’ data.</li>
<li>MySQL Router instance(s) running in production
mode.</li>
</ul></li>
</ol>
<p>Each of those setups serves a different set of REST
services, depending on the REST services’ current <a
href="#rest-service-lifecycle-management">lifecycle</a>
states.</p>
<p>The recommended way to configure a MySQL REST Service
development setup is to use <a
href="https://code.visualstudio.com/">VS Code</a> or <a
href="https://vscodium.com/">VSCodium</a> with the <a
href="https://marketplace.visualstudio.com/items?itemName=Oracle.mysql-shell-for-vs-code">MySQL
Shell for VS Code</a> extension installed. This will
simplify things like HTTPS certificate installation and
bootstrapping the MySQL Router in development mode.</p>
<h2 data-number="2.3" id="production-deployments"><span
class="header-section-number">2.3</span> Production
Deployments</h2>
<p>MySQL REST Service (MRS) can be deployed in many
different ways depending on the individual project
requirements.</p>
<p><strong><em>Deployments for
Development</em></strong></p>
<p>The smallest possible development environment
consists of a single MySQL Server instance and a MySQL
Router instance running on the same machine.</p>
<p>The recommended deployment for development consists
of an InnoDB Cluster deployed with a minimum of three
MySQL Server instances and two MySQL Router
instances.</p>
<p>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.</p>
<p><strong><em>Production Deployments</em></strong></p>
<p>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.</p>
<p>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.</p>
<figure>
<img
src="sections/devGuide/../../images/mrs-architecture.svg"
title="MySQL REST Service Architecture Diagram"
alt="MySQL REST Service Architecture Diagram" />
<figcaption aria-hidden="true">MySQL REST Service
Architecture Diagram</figcaption>
</figure>
<!-- Copyright (c) 2022, 2026, Oracle and/or its affiliates.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License, version 2.0,
as published by the Free Software Foundation.
This program is designed to work with certain software (including
but not limited to OpenSSL) that is licensed under separate terms, as
designated in a particular file or component or in included license
documentation. The authors of MySQL hereby grant you an additional
permission to link the program and your derivative works with the
separately licensed software that they have either included with
the program or referenced in the documentation.
This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See
the GNU General Public License, version 2.0, for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -->
<h1 data-number="3"
id="configuring-mysql-rest-service"><span
class="header-section-number">3</span> Configuring MySQL
REST Service</h1>
<p>To configure the MySQL REST Service (MRS), use these
steps:</p>
<ol type="1">
<li>Deploy a MySQL solution.</li>
<li><a
href="#configuring-the-mrs-metadata-schema">Configure</a>
the MRS metadata database schema.</li>
<li><a
href="#bootstrapping-and-running-mysql-routers-with-mrs-support">Bootstrap</a>
and run one or more MySQL Router instances for MRS
support.</li>
</ol>
<p>After performing these steps, MySQL REST Service is
fully configured.</p>
<p>The HTTP/S endpoints can then be accessed as
configured during the MySQL Router bootstrap
process.</p>
<p><strong><em>Deployment of a MySQL
solution</em></strong></p>
<p>The following MySQL solutions are supported:</p>
<ul>
<li>MySQL HeatWave</li>
<li>MySQL InnoDB Cluster</li>
<li>MySQL InnoDB ClusterSet</li>
<li>MySQL InnoDB ReplicaSet</li>
<li>MySQL Operator</li>
</ul>
<p>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).</p>
<ul>
<li>Standalone MySQL Server</li>
</ul>
<p>See the corresponding documentation about how to
deploy and configure the different MySQL solutions.</p>
<h2 data-number="3.1"
id="configuring-the-mrs-metadata-schema"><span
class="header-section-number">3.1</span> Configuring the
MRS Metadata Schema</h2>
<p>MySQL REST Service stores its configuration in the
<code>mysql_rest_service_metadata</code> database
schema. To deploy the metadata schema, perform one of
the tasks described in this section.</p>
<p>Note: The MySQL user that is used to connect to the
MySQL Solution must have MySQL privileges to create
database schemas and roles.</p>
<h3 data-number="3.1.1"
id="mrs-configuration-using-mysql-shell-for-vs-code"><span
class="header-section-number">3.1.1</span> MRS
Configuration Using MySQL Shell for VS Code</h3>
<ol type="1">
<li><p>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.</p></li>
<li><p>Right-click the connection in the DATABASE
CONNECTIONS view and select Configure Instance for MySQL
REST Service Support.</p></li>
</ol>
<figure>
<img
src="sections/devGuide/../../images/vsc-mrs-configure.png"
title="Configure Instance for MySQL REST Service Support"
alt="Configure Instance for MySQL REST Service Support" />
<figcaption aria-hidden="true">Configure Instance for
MySQL REST Service Support</figcaption>
</figure>
<p>The MRS metadata schema has now been configured.</p>
<h3 data-number="3.1.2"
id="mrs-configuration-using-mysql-shell"><span
class="header-section-number">3.1.2</span> MRS
Configuration Using MySQL Shell</h3>
<p>The MySQL REST Service metadata schema can be
configured from the MySQL Shell on the command line
after connecting to the MySQL solution.</p>
<p>Please note that a MySQL user with
<code>ALL PRIVILEGES</code> and
<code>WITH GRANT OPTION</code> needs to be used to
configure the MySQL REST Service metadata schema. It is
common practice to use the <code>root</code> MySQL user
or a dedicated <code>dba</code> MySQL user to perform
this operation.</p>
<p>To configure the metadata schema the REST SQL
extension <a
href="sql.html#configure-rest-metadata"><code>CONFIGURE REST METADATA</code>
statement</a> is used.</p>
<p><strong><em>Example</em></strong></p>
<p>The following example connects to a local MySQL
Server instance using a <code>dba</code> MySQL user
account and configures the MySQL REST Service metadata
schema.</p>
<div class="sourceCode" id="cb1"><pre
class="sourceCode bash"><code class="sourceCode bash"><span id="cb1-1"><a href="#cb1-1" aria-hidden="true" tabindex="-1"></a><span class="ex">$</span> mysqlsh dba@localhost</span>
<span id="cb1-2"><a href="#cb1-2" aria-hidden="true" tabindex="-1"></a><span class="ex">MySQL</span> Shell 9.3.0</span>
<span id="cb1-3"><a href="#cb1-3" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb1-4"><a href="#cb1-4" aria-hidden="true" tabindex="-1"></a><span class="ex">MySQL</span><span class="op">></span> localhost:3306<span class="op">></span> SQL<span class="op">></span> CONFIGURE REST METADATA<span class="kw">;</span></span>
<span id="cb1-5"><a href="#cb1-5" aria-hidden="true" tabindex="-1"></a><span class="ex">Query</span> OK, 0 rows affected <span class="er">(</span><span class="ex">0.3998</span> sec<span class="kw">)</span></span>
<span id="cb1-6"><a href="#cb1-6" aria-hidden="true" tabindex="-1"></a><span class="ex">REST</span> metadata configured successfully.</span></code></pre></div>
<p>After executing the
<code>CONFIGURE REST METADATA</code> statement the MRS
metadata schema has now been configured.</p>
<h3 data-number="3.1.3"
id="removing-the-mrs-metadata-schema"><span
class="header-section-number">3.1.3</span> Removing the
MRS Metadata Schema</h3>
<p>If the MySQL REST Service support should be removed,
the MySQL REST Service metadata schema can be dropped
using the
<code>DROP SCHEMA mysql_rest_service_metadata;</code>
statement.</p>
<p>Please note that a MySQL user account with required
privileges to drop the
<code>mysql_rest_service_metadata</code> has to be
used.</p>
<p><strong><em>Example</em></strong></p>
<p>The following example connects to a local MySQL
Server instance using a <code>dba</code> MySQL user
account and drops the MySQL REST Service metadata
schema.</p>
<div class="sourceCode" id="cb2"><pre
class="sourceCode bash"><code class="sourceCode bash"><span id="cb2-1"><a href="#cb2-1" aria-hidden="true" tabindex="-1"></a><span class="ex">$</span> mysqlsh dba@localhost</span>
<span id="cb2-2"><a href="#cb2-2" aria-hidden="true" tabindex="-1"></a><span class="ex">MySQL</span> Shell 9.3.0</span>
<span id="cb2-3"><a href="#cb2-3" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb2-4"><a href="#cb2-4" aria-hidden="true" tabindex="-1"></a><span class="ex">MySQL</span><span class="op">></span> localhost:3306<span class="op">></span> SQL<span class="op">></span> DROP SCHEMA mysql_rest_service_metadata<span class="kw">;</span></span>
<span id="cb2-5"><a href="#cb2-5" aria-hidden="true" tabindex="-1"></a><span class="ex">Query</span> OK, 38 rows affected <span class="er">(</span><span class="ex">0.0770</span> sec<span class="kw">)</span></span></code></pre></div>
<h2 data-number="3.2"
id="granting-users-access-to-the-mysql-rest-service"><span
class="header-section-number">3.2</span> Granting Users
Access to the MySQL REST Service</h2>
<p>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.</p>
<p>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.</p>
<h3 data-number="3.2.1" id="mrs-user-roles"><span
class="header-section-number">3.2.1</span> MRS User
Roles</h3>
<p>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.</p>
<p>The following MySQL roles can be assigned to MySQL
user accounts.</p>
<table>
<caption>MRS User Roles</caption>
<colgroup>
<col style="width: 27%" />
<col style="width: 27%" />
<col style="width: 45%" />
</colgroup>
<thead>
<tr class="header">
<th>Access Level</th>
<th>MySQL Role Name</th>
<th>Description</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td>Root</td>
<td>-</td>
<td>MySQL Users with <code>ALL PRIVILEGES</code>, like
the MySQL default <code>root</code> user, have full
access to all features</td>
</tr>
<tr class="even">
<td>REST Service Admin</td>
<td><code>mysql_rest_service_admin</code></td>
<td>MySQL users that are granted the
‘mysql_rest_service_admin’ role have full access to all
features</td>
</tr>
<tr class="odd">
<td>REST Schema Admin</td>
<td><code>mysql_rest_service_schema_admin</code></td>
<td>The ‘mysql_rest_service_schema_admin’ role allows
MySQL users to add new REST schemas and endpoints to an
existing REST service</td>
</tr>
<tr class="even">
<td>REST Service Developer</td>
<td><code>mysql_rest_service_dev</code></td>
<td>REST Service Developers are allowed to define new
REST endpoints for existing REST schemas</td>
</tr>
<tr class="odd">
<td>REST Service User</td>
<td><code>mysql_rest_service_user</code></td>
<td>Any MySQL user that should be able to access REST
endpoints needs to be granted the
‘mysql_rest_service_user’ role.</td>
</tr>
</tbody>
</table>
<p>The MySQL <a
href="https://dev.mysql.com/doc/refman/en/grant.html">GRANT</a>
statement can be used to assign the given MySQL role to
a MySQL user.</p>
<p>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 <a
href="https://dev.mysql.com/doc/refman/en/set-role.html">SET
ROLE</a> 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 <a
href="https://dev.mysql.com/doc/refman/en/set-default-role.html">SET
DEFAULT ROLE</a> statement.</p>
<p><strong><em>Example</em></strong></p>
<p>The following example <a
href="https://dev.mysql.com/doc/refman/en/grant.html">GRANTs</a>
the <code>mysql_rest_service_admin</code> role to the
<code>dba</code> MySQL user and ensures all MySQL roles,
including the new <code>mysql_rest_service_admin</code>
role, are made active when the MySQL user connects.</p>
<div class="sourceCode" id="cb3"><pre
class="sourceCode bash"><code class="sourceCode bash"><span id="cb3-1"><a href="#cb3-1" aria-hidden="true" tabindex="-1"></a><span class="ex">MySQL</span><span class="op">></span> localhost:3306<span class="op">></span> SQL<span class="op">></span> GRANT <span class="st">'mysql_rest_service_admin'</span> TO <span class="st">'dba'</span>@<span class="st">'%'</span><span class="kw">;</span></span>
<span id="cb3-2"><a href="#cb3-2" aria-hidden="true" tabindex="-1"></a><span class="ex">Query</span> OK, 0 rows affected <span class="er">(</span><span class="ex">0.0010</span> sec<span class="kw">)</span></span>
<span id="cb3-3"><a href="#cb3-3" aria-hidden="true" tabindex="-1"></a><span class="ex">MySQL</span><span class="op">></span> localhost:3306<span class="op">></span> SQL<span class="op">></span> SET DEFAULT ROLE ALL TO <span class="st">'dba'</span>@<span class="st">'%'</span><span class="kw">;</span></span>
<span id="cb3-4"><a href="#cb3-4" aria-hidden="true" tabindex="-1"></a><span class="ex">Query</span> OK, 0 rows affected <span class="er">(</span><span class="ex">0.0012</span> sec<span class="kw">)</span></span></code></pre></div>
<h3 data-number="3.2.2" id="mrs-provider-roles"><span
class="header-section-number">3.2.2</span> MRS Provider
Roles</h3>
<p>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.</p>
<table>
<caption>MRS Provider Roles</caption>
<colgroup>
<col style="width: 27%" />
<col style="width: 27%" />
<col style="width: 45%" />
</colgroup>
<thead>
<tr class="header">
<th>Access Level</th>
<th>MySQL Role Name</th>
<th>Description</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td>Metadata Schema Read-Only</td>
<td><code>mysql_rest_service_meta_provider</code></td>
<td>The metadata provide role is used by the MySQL
Router/Server MRS component to identify the REST
services that need to be served.</td>
</tr>
<tr class="even">
<td>Application Data Access</td>
<td><code>mysql_rest_service_data_provider</code></td>
<td>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’ <code>REST AUTH VENDOR</code> as well as
all OAuth2 vendors. REST Users authenticated via the
‘MySQL Internal’ vendor use their own privileges.</td>
</tr>
</tbody>
</table>
<p>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
<code>mysql_rest_service_data_provider</code> role.</p>
<ul>
<li>For REST views exposing a database table or view,
the required privileges are automatically granted.</li>
<li>For REST procedures and REST functions the
<code>EXECUTE</code> privilege is automatically granted.
Should the database procedure access other procedures or
schema objects, a manual GRANT statement for the
<code>mysql_rest_service_data_provider</code> role needs
to be executed.</li>
</ul>
<p><strong><em>Example</em></strong></p>
<p>The following example shows how to expose a database
procedure <code>test.my_procedure</code> that calls a
nested database procedure
<code>test.my_sub_procedure</code>.</p>
<p>The SQL script first creates the two procedures and
then defines the
<code>/myService/test/myProcedure</code> REST endpoint.
The <code>EXECUTE</code> privilege on
<code>test.my_procedure</code> is automatically
assigned. But the REST endpoint would still raise an
error as it misses the <code>EXECUTE</code> privilege on
<code>test.my_sub_procedure</code>.</p>
<p>Finally, the <code>GRANT</code> statement assigns the
<code>EXECUTE</code> privilege on the
<code>test.my_sub_procedure</code> to the
<code>mysql_rest_service_data_provider</code> role. Now,
the REST endpoint is fully functional.</p>
<div class="sourceCode" id="cb4"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb4-1"><a href="#cb4-1" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">SCHEMA</span> <span class="cf">IF</span> <span class="kw">NOT</span> <span class="kw">EXISTS</span> `test`;</span>
<span id="cb4-2"><a href="#cb4-2" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb4-3"><a href="#cb4-3" aria-hidden="true" tabindex="-1"></a>DELIMITER %%</span>
<span id="cb4-4"><a href="#cb4-4" aria-hidden="true" tabindex="-1"></a><span class="kw">DROP</span> <span class="kw">PROCEDURE</span> <span class="cf">IF</span> <span class="kw">EXISTS</span> `test`.`my_procedure`%%</span>
<span id="cb4-5"><a href="#cb4-5" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">PROCEDURE</span> `test`.`my_procedure`(<span class="kw">IN</span> arg1 <span class="dt">INTEGER</span>, <span class="kw">OUT</span> arg2 <span class="dt">INTEGER</span>)</span>
<span id="cb4-6"><a href="#cb4-6" aria-hidden="true" tabindex="-1"></a>SQL SECURITY <span class="kw">DEFINER</span></span>
<span id="cb4-7"><a href="#cb4-7" aria-hidden="true" tabindex="-1"></a><span class="kw">NOT</span> DETERMINISTIC</span>
<span id="cb4-8"><a href="#cb4-8" aria-hidden="true" tabindex="-1"></a><span class="cf">BEGIN</span></span>
<span id="cb4-9"><a href="#cb4-9" aria-hidden="true" tabindex="-1"></a> <span class="kw">CALL</span> `test`.`my_sub_procedure`(arg1, arg2);</span>
<span id="cb4-10"><a href="#cb4-10" aria-hidden="true" tabindex="-1"></a><span class="cf">END</span>%%</span>
<span id="cb4-11"><a href="#cb4-11" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb4-12"><a href="#cb4-12" aria-hidden="true" tabindex="-1"></a><span class="kw">DROP</span> <span class="kw">PROCEDURE</span> <span class="cf">IF</span> <span class="kw">EXISTS</span> `test`.`my_sub_procedure`%%</span>
<span id="cb4-13"><a href="#cb4-13" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">PROCEDURE</span> `test`.`my_sub_procedure`(<span class="kw">IN</span> arg1 <span class="dt">INTEGER</span>, <span class="kw">OUT</span> arg2 <span class="dt">INTEGER</span>)</span>
<span id="cb4-14"><a href="#cb4-14" aria-hidden="true" tabindex="-1"></a>SQL SECURITY <span class="kw">DEFINER</span></span>
<span id="cb4-15"><a href="#cb4-15" aria-hidden="true" tabindex="-1"></a><span class="kw">NOT</span> DETERMINISTIC</span>
<span id="cb4-16"><a href="#cb4-16" aria-hidden="true" tabindex="-1"></a><span class="cf">BEGIN</span></span>
<span id="cb4-17"><a href="#cb4-17" aria-hidden="true" tabindex="-1"></a> <span class="kw">SET</span> arg2 <span class="op">=</span> arg1 <span class="op">*</span> <span class="dv">2</span>;</span>
<span id="cb4-18"><a href="#cb4-18" aria-hidden="true" tabindex="-1"></a><span class="cf">END</span>%%</span>
<span id="cb4-19"><a href="#cb4-19" aria-hidden="true" tabindex="-1"></a>DELIMITER ;</span>
<span id="cb4-20"><a href="#cb4-20" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb4-21"><a href="#cb4-21" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">OR</span> <span class="kw">REPLACE</span> REST SERVICE <span class="op">/</span>myService;</span>
<span id="cb4-22"><a href="#cb4-22" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> REST <span class="kw">SCHEMA</span> <span class="op">/</span>test <span class="kw">ON</span> SERVICE <span class="op">/</span>myService <span class="kw">FROM</span> test;</span>
<span id="cb4-23"><a href="#cb4-23" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> REST <span class="kw">PROCEDURE</span> <span class="op">/</span>myProcedure</span>
<span id="cb4-24"><a href="#cb4-24" aria-hidden="true" tabindex="-1"></a> <span class="kw">ON</span> SERVICE <span class="op">/</span>myService <span class="kw">SCHEMA</span> <span class="op">/</span>test</span>
<span id="cb4-25"><a href="#cb4-25" aria-hidden="true" tabindex="-1"></a> <span class="kw">AS</span> `test`.`my_procedure`;</span>
<span id="cb4-26"><a href="#cb4-26" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb4-27"><a href="#cb4-27" aria-hidden="true" tabindex="-1"></a><span class="kw">GRANT</span> <span class="kw">EXECUTE</span> <span class="kw">ON</span> <span class="kw">PROCEDURE</span> `test`.`my_sub_procedure` <span class="kw">TO</span> <span class="st">'mysql_rest_service_data_provider'</span>;</span></code></pre></div>
<h2 data-number="3.3"
id="bootstrapping-and-running-mysql-routers-with-mrs-support"><span
class="header-section-number">3.3</span> Bootstrapping
and Running MySQL Routers with MRS Support</h2>
<p>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.</p>
<p>A MySQL Router instance needs to be configured to
support MRS. This is usually done by using the
<code>mysqlrouter_bootstrap</code> command, which
queries the user account for the necessary
information.</p>
<h3 data-number="3.3.1"
id="using-mysql-shell-for-vs-code-to-bootstrap-and-run-mysql-router"><span
class="header-section-number">3.3.1</span> Using MySQL
Shell for VS Code to Bootstrap and Run MySQL Router</h3>
<p>When working with a local development setup it is
common to install the MySQL Router instance on the local
development machine.</p>
<p>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:</p>
<ol type="1">
<li>Download and install the MySQL Router package on
your local development machine
<ul>
<li>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.</li>
</ul></li>
<li>Inside MySQL Shell for VS Code, expand a DB
Connection in the DATABASE CONNECTIONS view, right-click
the <code>MySQL REST Service</code> tree item, and then
select <code>Start Local MySQL Router Instance</code>.
<ul>
<li>If the MySQL Router has not been configured yet, the
bootstrap operation runs in an integrated VS Code
terminal and then starts MySQL Router.</li>
<li>MySQL Router debug output can then be inspected in
the VS Code terminal.</li>
</ul></li>
<li>To shut down MySQL Router, set the focus to the VS
Code terminal showing the debug output and press
<code>Ctrl</code> + <code>C.</code></li>
</ol>
<p>Note: The previous task only works for classic MySQL
connections that are not using the MySQL SSH tunneling
or MDS tunneling feature.</p>
<figure>
<img
src="sections/devGuide/../../images/vsc-mrs-start-mysql-router.png"
title="Bootstrap and Start MySQL Router"
alt="Bootstrap and Start MySQL Router" />
<figcaption aria-hidden="true">Bootstrap and Start MySQL
Router</figcaption>
</figure>
<p>After the MySQL Router has been bootstrapped and
started, MRS is available at
<code>https://localhost:8443/<service-name></code>.
You can then proceed and <a
href="#adding-rest-services-and-database-objects">add a
REST service</a>.</p>
<h3 data-number="3.3.2"
id="bootstrapping-mysql-router-from-the-command-line"><span
class="header-section-number">3.3.2</span> Bootstrapping
MySQL Router From the Command Line</h3>
<p>When deploying a new MySQL Router instance, it is
advised to use the <code>mysqlrouter_bootstrap</code>
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.</p>
<div class="sourceCode" id="cb5"><pre
class="sourceCode bash"><code class="sourceCode bash"><span id="cb5-1"><a href="#cb5-1" aria-hidden="true" tabindex="-1"></a><span class="ex">mysqlrouter_bootstrap</span> dba@127.0.0.1:3306 <span class="at">--mrs</span> <span class="at">--directory</span> ~/.mysqlrouter</span></code></pre></div>
<p>Follow the interactive steps on the command line to
configure the router.</p>
<p><strong><em>Manual Creation of MySQL User Account for
MySQL Router Access</em></strong></p>
<p>When using the <code>mysqlrouter_bootstrap</code>
command to configure MySQL Router for MRS, access the
user accounts described in this section can be created
automatically.</p>
<p>If you want to manage the required MySQL accounts
manually, the following steps need to be performed:</p>
<ol type="1">
<li>Create the MySQL user account(or accounts)
<ul>
<li>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
<code>mysql_rest_service_meta_provider</code> and
<code>mysql_rest_service_data_provider</code>
roles.</li>
<li>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
<code>mysql_rest_service_meta_provider</code> role to
one user and
<code>mysql_rest_service_data_provider</code> to the
other.</li>
</ul></li>
<li>Bootstrap the MySQL Routers instance using the
created MySQL accounts with the following options:
<ul>
<li><code>--mrs-mysql-metadata-account</code> used by
the router to access the MRS metadata schema</li>
<li><code>--mrs-mysql-data-account</code> used by the
router to access the application schema</li>
</ul></li>
</ol>
<p>As part of the MRS metadata schema creation, two SQL
ROLEs have been created for MySQL Router to access
MySQL:</p>
<ul>
<li>The ‘mysql_rest_service_meta_provider’ ROLE grants
access to the required MRS metadata schema tables.</li>
<li>The ‘mysql_rest_service_data_provider’ ROLE grants
access to the served schema objects in the application
database schemas.</li>
</ul>
<p>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:</p>
<div class="sourceCode" id="cb6"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb6-1"><a href="#cb6-1" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="fu">USER</span> <span class="st">'mysqlrouter_mrs_<router_name>'</span>@<span class="st">'<router_host>'</span> <span class="kw">IDENTIFIED</span> <span class="kw">BY</span> <span class="st">'password'</span>;</span>
<span id="cb6-2"><a href="#cb6-2" aria-hidden="true" tabindex="-1"></a><span class="kw">GRANT</span> <span class="st">'mysql_rest_service_meta_provider'</span>, <span class="st">'mysql_rest_service_data_provider'</span> <span class="kw">TO</span> <span class="st">'mysqlrouter_mrs_<router_name>'</span>@<span class="st">'<router_host>'</span>;</span></code></pre></div>
<p>The user name specified for the account can then be
used when calling the <code>mysqlrouter_bootstrap</code>
command.</p>
<table>
<caption>MySQL Router MRS Bootstrap Account
Options</caption>
<colgroup>
<col style="width: 37%" />
<col style="width: 62%" />
</colgroup>
<thead>
<tr class="header">
<th>Option</th>
<th>Description</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td><code>--mrs-mysql-metadata-account=USER_NAME</code></td>
<td>Setting the MRS metadata user</td>
</tr>
<tr class="even">
<td><code>--mrs-mysql-data-account=USER_NAME</code></td>
<td>Setting the MRS data user</td>
</tr>
</tbody>
</table>
<p><strong><em>Adding a MRS Configuration to an Existing
MySQL Router Configuration</em></strong></p>
<p>In case your MySQL Router instances are configured
already, it is possible to add the MRS configuration
later on.</p>
<p>To get the path of the existing configuration file,
execute <code>mysqlrouter --help</code> to show the
exact location of the router config file.</p>
<p>The following is an example when connecting to a
single development server.</p>
<div class="sourceCode" id="cb7"><pre
class="sourceCode ini"><code class="sourceCode ini"><span id="cb7-1"><a href="#cb7-1" aria-hidden="true" tabindex="-1"></a><span class="kw">[DEFAULT]</span></span>
<span id="cb7-2"><a href="#cb7-2" aria-hidden="true" tabindex="-1"></a><span class="dt">logging_folder </span><span class="ot">=</span><span class="st"> /var/log/mysqlrouter</span></span>
<span id="cb7-3"><a href="#cb7-3" aria-hidden="true" tabindex="-1"></a><span class="dt">runtime_folder </span><span class="ot">=</span><span class="st"> /var/run/mysqlrouter</span></span>
<span id="cb7-4"><a href="#cb7-4" aria-hidden="true" tabindex="-1"></a><span class="dt">config_folder </span><span class="ot">=</span><span class="st"> /etc/mysqlrouter</span></span>
<span id="cb7-5"><a href="#cb7-5" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb7-6"><a href="#cb7-6" aria-hidden="true" tabindex="-1"></a><span class="kw">[logger]</span></span>
<span id="cb7-7"><a href="#cb7-7" aria-hidden="true" tabindex="-1"></a><span class="dt">level </span><span class="ot">=</span><span class="st"> DEBUG</span></span>
<span id="cb7-8"><a href="#cb7-8" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb7-9"><a href="#cb7-9" aria-hidden="true" tabindex="-1"></a><span class="kw">[routing:mrs_rw]</span></span>
<span id="cb7-10"><a href="#cb7-10" aria-hidden="true" tabindex="-1"></a><span class="dt">bind_address</span><span class="ot">=</span><span class="st">0.0.0.0</span></span>
<span id="cb7-11"><a href="#cb7-11" aria-hidden="true" tabindex="-1"></a><span class="dt">bind_port</span><span class="ot">=</span><span class="dv">6446</span></span>
<span id="cb7-12"><a href="#cb7-12" aria-hidden="true" tabindex="-1"></a><span class="dt">destinations</span><span class="ot">=</span><span class="st">10.0.1.135:3306</span></span>
<span id="cb7-13"><a href="#cb7-13" aria-hidden="true" tabindex="-1"></a><span class="dt">routing_strategy</span><span class="ot">=</span><span class="st">round-robin</span></span>
<span id="cb7-14"><a href="#cb7-14" aria-hidden="true" tabindex="-1"></a><span class="dt">protocol</span><span class="ot">=</span><span class="st">classic</span></span></code></pre></div>
<p>To enable MRS support on the router, the
configuration file needs to be extended with the
[http_server] section and the [rest_mrs] section.</p>
<p>It is advised to use the
<code>mysqlrouter_bootstrap</code> command to configure
the router for MRS.</p>
<div class="sourceCode" id="cb8"><pre
class="sourceCode bash"><code class="sourceCode bash"><span id="cb8-1"><a href="#cb8-1" aria-hidden="true" tabindex="-1"></a><span class="ex">mysqlrouter_bootstrap</span> dba@127.0.0.1:13000 <span class="at">--mrs</span> <span class="at">--directory</span> /export/mysql/src/mysql-trunk/boot</span></code></pre></div>
<p>These parameters can be used to set the MRS
configuration options.</p>
<table>
<caption>MySQL Router Bootstrap options</caption>
<colgroup>
<col style="width: 37%" />
<col style="width: 62%" />
</colgroup>
<thead>
<tr class="header">
<th>Option</th>
<th>Description</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td><code>--mrs</code></td>
<td>Include MRS configuration</td>
</tr>
<tr class="even">
<td><code>--mrs-mysql-metadata-account=USER_NAME</code></td>
<td>Setting the MRS metadata user</td>
</tr>
<tr class="odd">
<td><code>--mrs-mysql-data-account=USER_NAME</code></td>
<td>Setting the MRS data user</td>
</tr>
<tr class="even">
<td><code>--mrs-global-secret=SECRET</code></td>
<td>The global JWT secret that must be the same for
every MySQL Router installation</td>
</tr>
<tr class="odd">
<td><code>--mrs-developer MYSQL_USER_NAME</code></td>
<td>Switches the MySQL Router to developer mode</td>
</tr>
<tr class="even">
<td><code>--mrs-developer-debug-port</code>
DEBUG_PORT</td>
<td>The port used for local debugging of MRS
Scripts</td>
</tr>
</tbody>
</table>
<p>The following example demonstrates connecting to a
single development server and serving the REST services
using HTTP.</p>
<div class="sourceCode" id="cb9"><pre
class="sourceCode ini"><code class="sourceCode ini"><span id="cb9-1"><a href="#cb9-1" aria-hidden="true" tabindex="-1"></a><span class="kw">[http_server]</span></span>
<span id="cb9-2"><a href="#cb9-2" aria-hidden="true" tabindex="-1"></a><span class="dt">port</span><span class="ot">=</span><span class="dv">8443</span></span>
<span id="cb9-3"><a href="#cb9-3" aria-hidden="true" tabindex="-1"></a><span class="dt">ssl</span><span class="ot">=</span><span class="dv">1</span></span>
<span id="cb9-4"><a href="#cb9-4" aria-hidden="true" tabindex="-1"></a><span class="dt">ssl_cert</span><span class="ot">=</span><span class="st">/Users/myUser/.mysqlsh/plugin_data/gui_plugin/web_certs/server.crt</span></span>
<span id="cb9-5"><a href="#cb9-5" aria-hidden="true" tabindex="-1"></a><span class="dt">ssl_key</span><span class="ot">=</span><span class="st">/Users/myUser/.mysqlsh/plugin_data/gui_plugin/web_certs/server.key</span></span>
<span id="cb9-6"><a href="#cb9-6" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb9-7"><a href="#cb9-7" aria-hidden="true" tabindex="-1"></a><span class="kw">[mysql_rest_service]</span></span>
<span id="cb9-8"><a href="#cb9-8" aria-hidden="true" tabindex="-1"></a><span class="dt">mysql_read_only_route</span><span class="ot">=</span><span class="st">bootstrap_ro</span></span>
<span id="cb9-9"><a href="#cb9-9" aria-hidden="true" tabindex="-1"></a><span class="dt">mysql_read_write_route</span><span class="ot">=</span><span class="st">bootstrap_rw</span></span>
<span id="cb9-10"><a href="#cb9-10" aria-hidden="true" tabindex="-1"></a><span class="dt">mysql_user</span><span class="ot">=</span><span class="st">mysql_router_mrs16_250ho3u15n</span></span>
<span id="cb9-11"><a href="#cb9-11" aria-hidden="true" tabindex="-1"></a><span class="dt">mysql_user_data_access</span><span class="ot">=</span></span>
<span id="cb9-12"><a href="#cb9-12" aria-hidden="true" tabindex="-1"></a><span class="dt">router_id</span><span class="ot">=</span><span class="dv">16</span></span></code></pre></div>
<h2 data-number="3.4"
id="installing-the-mrs-server-component"><span
class="header-section-number">3.4</span> Installing the
MRS Server Component</h2>
<blockquote>
<p>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 <a
href="https://labs.mysql.com">labs.mysql.com</a>.</p>
</blockquote>
<p>The MySQL REST Service server component can be
managed the same way as any other <a
href="https://dev.mysql.com/doc/refman/en/components.html">server
component</a>.</p>
<p>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.</p>
<blockquote>
<p>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.</p>
</blockquote>
<p>The MRS server component can be installed before <a
href="#configuring-mysql-rest-service">configuring the
MySQL instance for the MySQL REST Service support</a>.
In this case, the component will remain in a waiting
state until MRS has been configured. No HTTP access is
available during this time.</p>
<p>The following SQL command can be used to install the
MRS server component.</p>
<div class="sourceCode" id="cb10"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb10-1"><a href="#cb10-1" aria-hidden="true" tabindex="-1"></a>INSTALL COMPONENT <span class="ot">"file://component_mysql_rest_service"</span>;</span></code></pre></div>
<p><strong><em>Example</em></strong></p>
<p>The following example installs the MRS server
component and sets related MRS system variables at
install time:</p>
<div class="sourceCode" id="cb11"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb11-1"><a href="#cb11-1" aria-hidden="true" tabindex="-1"></a>INSTALL COMPONENT <span class="ot">"file://component_mysql_rest_service"</span></span>
<span id="cb11-2"><a href="#cb11-2" aria-hidden="true" tabindex="-1"></a> <span class="kw">SET</span> <span class="kw">GLOBAL</span> component_mysql_rest_service.use_ssl <span class="op">=</span> <span class="dv">0</span>,</span>
<span id="cb11-3"><a href="#cb11-3" aria-hidden="true" tabindex="-1"></a> <span class="kw">GLOBAL</span> component_mysql_rest_service.mrs_user <span class="op">=</span> <span class="ot">"miguel"</span>;</span></code></pre></div>
<h3 data-number="3.4.1"
id="uninstalling-the-mrs-server-component"><span
class="header-section-number">3.4.1</span> Uninstalling
the MRS Server Component</h3>
<p>To uninstall the MRS server component, the following
SQL command can be used.</p>
<ol type="1">
<li><code>UNINSTALL COMPONENT "file://component_mysql_rest_service";</code></li>
</ol>
<p>If the MySQL X Plugin was available before, it will
be loaded again when uninstalling the MRS server
component.</p>
<h3 data-number="3.4.2"
id="mrs-server-component-configuration"><span
class="header-section-number">3.4.2</span> MRS Server
Component Configuration</h3>
<p>The following MySQL system variables are available to
configure the MRS server component.</p>
<table>
<caption>MRS System Variables Overview</caption>
<colgroup>
<col style="width: 27%" />
<col style="width: 72%" />
</colgroup>
<thead>
<tr class="header">
<th>System Variable</th>
<th>Description</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td>component_mysql_rest_service.mrs_user</td>
<td>Defines the <a
href="#granting-users-access-to-the-mysql-rest-service">REST
Service User</a>. Automatically generated when component
is installed and the SysVar has not been set at install
time.</td>
</tr>
<tr class="even">
<td>component_mysql_rest_service.metadata_cache_user</td>
<td>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.</td>
</tr>
<tr class="odd">
<td>component_mysql_rest_service.http_port</td>
<td>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.</td>
</tr>
<tr class="even">
<td>component_mysql_rest_service.use_ssl</td>
<td>Enables TLSv1.2 (or later) support. If the server
has it enabled/supported, then it’s enabled by
default.</td>
</tr>
<tr class="odd">
<td>component_mysql_rest_service.ssl_cert</td>
<td>SSL certificate filename. Uses Server default
certificate if not specified.</td>
</tr>
<tr class="even">
<td>component_mysql_rest_service.ssl_key</td>
<td>SSL key filename. Uses Server default key if not
specified.</td>
</tr>
<tr class="odd">
<td>component_mysql_rest_service.developer</td>
<td>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.</td>
</tr>
</tbody>
</table>
<p>To check current values of all related MySQL system
variables, execute the following SHOW statement.</p>
<div class="sourceCode" id="cb12"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb12-1"><a href="#cb12-1" aria-hidden="true" tabindex="-1"></a>SHOW variables <span class="kw">LIKE</span> <span class="st">'%mysql_rest_service%'</span>;</span></code></pre></div>
<p>To set a related MySQL system variable, execute the
following statements that set the variable and then
restart the MRS server component.</p>
<div class="sourceCode" id="cb13"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb13-1"><a href="#cb13-1" aria-hidden="true" tabindex="-1"></a><span class="kw">SET</span> PERSIST component_mysql_rest_service.developer <span class="op">=</span> <span class="st">'mike'</span>;</span>
<span id="cb13-2"><a href="#cb13-2" aria-hidden="true" tabindex="-1"></a><span class="kw">SELECT</span> component_mysql_rest_service_restart();</span></code></pre></div>
<blockquote>
<p>Please note that a restart of the MRS server
component is required to activate the change.</p>
</blockquote>
<h3 data-number="3.4.3"
id="mrs-server-component-status-variables"><span
class="header-section-number">3.4.3</span> MRS Server
Component Status Variables</h3>
<p>The following MySQL status variables are
available:</p>
<table>
<caption>MRS Status Variables Overview</caption>
<colgroup>
<col style="width: 27%" />
<col style="width: 72%" />
</colgroup>
<thead>
<tr class="header">
<th>Status Variable</th>
<th>Description</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td>component_mysql_rest_service.http_port_source</td>
<td>Indicates how the HTTP port was configured. If set
to <code>default</code> port 8543 is used. If set to
<code>xplugin</code> the port was taken from the MySQL X
Plugin. If set to <code>user</code> the port was
individually configured by the user.</td>
</tr>
<tr class="even">
<td>component_mysql_rest_service.ssl_cert_source</td>
<td>Indicates how the SSL-certificate was configured. If
set to <code>mysql</code> the MySQL Server configuration
is used. If set to <code>user</code> the source was
configured by the user.</td>
</tr>
<tr class="odd">
<td>component_mysql_rest_service.ssl_key_source</td>
<td>Indicates how the SSL-key was configured. If set to
<code>mysql</code> the MySQL Server configuration is
used. If set to <code>user</code> the source was
configured by the user.</td>
</tr>
<tr class="even">
<td>component_mysql_rest_service.current_mrs_user</td>
<td>Indicates the <a
href="#granting-users-access-to-the-mysql-rest-service">REST
Service User</a> account currently in use. If the
account was auto-generated, the name is suffixed with
“(auto-generated)”.</td>
</tr>
<tr class="odd">
<td>component_mysql_rest_service.current_metadata_cache_user</td>
<td>Indicates the Metadata Cache account currently in
use. If the account was auto-generated, the name is
suffixed with “(auto-generated)”.</td>
</tr>
</tbody>
</table>
<p>To query a MRS status variable use a SELECT statement
like the following.</p>
<div class="sourceCode" id="cb14"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb14-1"><a href="#cb14-1" aria-hidden="true" tabindex="-1"></a><span class="kw">SELECT</span> VARIABLE_VALUE <span class="kw">FROM</span> performance_schema.global_status</span>
<span id="cb14-2"><a href="#cb14-2" aria-hidden="true" tabindex="-1"></a><span class="kw">WHERE</span> VARIABLE_NAME <span class="op">=</span> <span class="st">'component_mysql_rest_service.http_port_source'</span>;</span></code></pre></div>
<p>To query all Status Variables use a SELECT statement
like the following.</p>
<div class="sourceCode" id="cb15"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb15-1"><a href="#cb15-1" aria-hidden="true" tabindex="-1"></a>SHOW STATUS <span class="kw">LIKE</span> <span class="st">'component_mysql_rest_service%'</span>;</span></code></pre></div>
<h3 data-number="3.4.4"
id="mrs-server-component-udfs"><span
class="header-section-number">3.4.4</span> MRS Server
Component UDFs</h3>
<p>The following UDFs are available to control the MRS
server component:</p>
<table>
<caption>MRS server component UDF Overview</caption>
<colgroup>
<col style="width: 27%" />
<col style="width: 72%" />
</colgroup>
<thead>
<tr class="header">
<th>UDF Call</th>
<th>Description</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td><code>SELECT component_mysql_rest_service_start();</code></td>
<td>Starts the MRS server component after it has been
stopped.</td>
</tr>
<tr class="even">
<td><code>SELECT component_mysql_rest_service_stop();</code></td>
<td>Stops the MRS server component.</td>
</tr>
<tr class="odd">
<td><code>SELECT component_mysql_rest_service_restart();</code></td>
<td>Restarts the MRS server component.</td>
</tr>
</tbody>
</table>
<h3 data-number="3.4.5"
id="mysql-database-architectures-support"><span
class="header-section-number">3.4.5</span> MySQL
Database Architectures Support</h3>
<p>Version 15 of the Experimental package, available
from <a
href="https://labs.mysql.com">labs.mysql.com</a>, adds
support for running the component within MySQL Database
Architectures, enabling it to operate seamlessly in the
following topologies:</p>
<ul>
<li>InnoDB Cluster</li>
<li>InnoDB ReplicaSet</li>
<li>InnoDB ClusterSet</li>
</ul>
<p>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.</p>
<blockquote>
<p>Important: In the current release, the component must
be installed and run only on the
<strong>PRIMARY</strong> member of a managed topology.
Attempting to run it on a <strong>SECONDARY</strong>
member results in an error, and the component stops
gracefully.</p>
<p>To install it across all members, perform a
<strong>switchover</strong> and repeat the installation
on each member until all have it installed.</p>
<p>This is a <strong>temporary limitation</strong>,
future releases will support installing and running the
component on all members of the topology.</p>
</blockquote>
<p>For more information on each topology, refer to the
documentation:</p>
<ul>
<li><a
href="https://dev.mysql.com/doc/mysql-shell/en/mysql-innodb-cluster.html">InnoDB
Cluster Documentation</a></li>
<li><a
href="https://dev.mysql.com/doc/mysql-shell/en/mysql-innodb-replicaset.html">InnoDB
ReplicaSet Documentation</a></li>
<li><a
href="https://dev.mysql.com/doc/mysql-shell/en/innodb-clusterset.html">InnoDB
ClusterSet Documentation</a></li>
</ul>
<!-- Copyright (c) 2022, 2026, Oracle and/or its affiliates.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License, version 2.0,
as published by the Free Software Foundation.
This program is designed to work with certain software (including
but not limited to OpenSSL) that is licensed under separate terms, as
designated in a particular file or component or in included license
documentation. The authors of MySQL hereby grant you an additional
permission to link the program and your derivative works with the
separately licensed software that they have either included with
the program or referenced in the documentation.
This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See
the GNU General Public License, version 2.0, for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -->
<h1 data-number="4"
id="adding-rest-services-and-database-objects"><span
class="header-section-number">4</span> Adding REST
Services and Database Objects</h1>
<p>The MySQL REST Service supports the creation of a
large number individual REST services.</p>
<p>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.</p>
<p>It is advised to setup an separate REST service for
each application that consumes a set of REST
endpoints.</p>
<h2 data-number="4.1"
id="rest-service-lifecycle-management"><span
class="header-section-number">4.1</span> REST Service
Lifecycle Management</h2>
<p>REST services can be placed in a number of different
states during their lifecycle.</p>
<h3 data-number="4.1.1" id="development-state"><span
class="header-section-number">4.1.1</span> Development
State</h3>
<p>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.</p>
<p>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 <a
href="#development-setup">MySQL REST Service development
setup</a>.</p>
<h3 data-number="4.1.2" id="published-state"><span
class="header-section-number">4.1.2</span> Published
State</h3>
<p>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.</p>
<h3 data-number="4.1.3" id="disabled-state"><span
class="header-section-number">4.1.3</span> Disabled
State</h3>
<p>Should a REST service be retired, it can be disabled
by setting the corresponding flag on the REST
service.</p>
<h2 data-number="4.2"
id="preconditions-for-adding-a-rest-service"><span
class="header-section-number">4.2</span> Preconditions
for Adding a REST Service</h2>
<p>Before setting up a new REST service, ensure that the
following preconditions are met:</p>
<ul>
<li>The MySQL REST Service must be configured on the
targeting MySQL Solution. Please see the <a
href="#configuring-mysql-rest-service">Configuration</a>
section of this manual.</li>
<li>The MySQL account used to connect to the targeting
MySQL Solution needs to be granted the
<code>mysql_rest_service_admin</code> MySQL role or a
superset of privileges.</li>
</ul>
<p>To grant the <code>mysql_rest_service_admin</code>
MySQL role, execute the following SQL statement.</p>
<div class="sourceCode" id="cb16"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb16-1"><a href="#cb16-1" aria-hidden="true" tabindex="-1"></a><span class="kw">GRANT</span> <span class="st">'mysql_rest_service_admin'</span> <span class="kw">TO</span> <span class="st">'user_account'</span>@<span class="st">'%'</span>;</span>
<span id="cb16-2"><a href="#cb16-2" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb16-3"><a href="#cb16-3" aria-hidden="true" tabindex="-1"></a><span class="co">-- Please ensure to include all roles in the next statement</span></span>
<span id="cb16-4"><a href="#cb16-4" aria-hidden="true" tabindex="-1"></a><span class="co">-- that should become active when the user connects</span></span>
<span id="cb16-5"><a href="#cb16-5" aria-hidden="true" tabindex="-1"></a><span class="kw">ALTER</span> <span class="fu">USER</span> <span class="st">'user_account'</span>@<span class="st">'%'</span> <span class="kw">DEFAULT</span> <span class="kw">ROLE</span> <span class="st">'mysql_rest_service_admin'</span>;</span></code></pre></div>
<h2 data-number="4.3"
id="setting-up-a-new-rest-service"><span
class="header-section-number">4.3</span> Setting Up a
New REST Service</h2>
<p>A new REST service can be added in one of the
following ways:</p>
<ul>
<li>MySQL Shell for VS Code provides a GUI dialog to
create the REST service.</li>
<li>MySQL Shell offers the MRS plugin that can be used
to create a REST service interactively or with scripts
in a terminal.</li>
<li>When writing a script or plugin for MySQL Shell, the
MRS plugin can be used to script the creation in Python
or JavaScript.</li>
</ul>
<h3 data-number="4.3.1"
id="adding-a-rest-service-using-mysql-shell-for-vs-code"><span
class="header-section-number">4.3.1</span> Adding a REST
Service Using MySQL Shell for VS Code</h3>
<p>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,
<code>MySQL REST Service</code>.</p>
<ol type="1">
<li><p>Right-click the tree item
<code>MySQL REST Service</code> and select
<code>Add REST Service...</code> from the list to
display the MySQL REST Service dialog.</p></li>
<li><p>Specify values for the required parameters and
click <code>OK</code> to add the new REST
service.</p></li>
</ol>
<figure>
<img
src="sections/devGuide/../../images/vsc-mrs-add-service.png"
title="Adding a REST Service"
alt="Adding a REST Service" />
<figcaption aria-hidden="true">Adding a REST
Service</figcaption>
</figure>
<h3 data-number="4.3.2"
id="adding-a-rest-service-using-mysql-shell"><span
class="header-section-number">4.3.2</span> Adding a REST
Service Using MySQL Shell</h3>
<p>For MySQL Shell, the <code>mrs</code> plugin is used
to work with the MySQL REST Service. The
<code>mrs.add.service()</code> function adds a new REST
service.</p>
<p>When started without parameters, an interactive
wizard prompt you for the required parameters. For
example:</p>
<div class="sourceCode" id="cb17"><pre
class="sourceCode bash"><code class="sourceCode bash"><span id="cb17-1"><a href="#cb17-1" aria-hidden="true" tabindex="-1"></a><span class="ex">MySQL</span> <span class="op">></span> localhost:33060+ <span class="op">></span> Py <span class="op">></span> mrs.add.service<span class="er">(</span><span class="kw">)</span></span>
<span id="cb17-2"><a href="#cb17-2" aria-hidden="true" tabindex="-1"></a><span class="ex">Please</span> enter the context path for this service [/mrs]: /myservice</span>
<span id="cb17-3"><a href="#cb17-3" aria-hidden="true" tabindex="-1"></a><span class="ex">Please</span> enter the host name for this service <span class="er">(</span><span class="ex">e.g.</span> None or localhost<span class="kw">)</span> <span class="ex">[None]:</span></span>
<span id="cb17-4"><a href="#cb17-4" aria-hidden="true" tabindex="-1"></a> <span class="ex">1</span> HTTP</span>
<span id="cb17-5"><a href="#cb17-5" aria-hidden="true" tabindex="-1"></a> <span class="ex">2</span> HTTPS</span>
<span id="cb17-6"><a href="#cb17-6" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb17-7"><a href="#cb17-7" aria-hidden="true" tabindex="-1"></a><span class="ex">Please</span> select the protocol<span class="er">(</span><span class="ex">s</span><span class="kw">)</span> <span class="ex">the</span> service should support [HTTP,HTTPS]: 2</span>
<span id="cb17-8"><a href="#cb17-8" aria-hidden="true" tabindex="-1"></a><span class="ex">Comments:</span></span>
<span id="cb17-9"><a href="#cb17-9" aria-hidden="true" tabindex="-1"></a> <span class="ex">1</span> Default Service Options for Development</span>
<span id="cb17-10"><a href="#cb17-10" aria-hidden="true" tabindex="-1"></a> <span class="ex">2</span> No options</span>
<span id="cb17-11"><a href="#cb17-11" aria-hidden="true" tabindex="-1"></a> <span class="ex">3</span> Custom options</span>
<span id="cb17-12"><a href="#cb17-12" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb17-13"><a href="#cb17-13" aria-hidden="true" tabindex="-1"></a><span class="ex">Please</span> select how to initialize the options [Default Service Options for Development]:</span>
<span id="cb17-14"><a href="#cb17-14" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb17-15"><a href="#cb17-15" aria-hidden="true" tabindex="-1"></a><span class="ex">Service</span> /myservice created successfully.</span></code></pre></div>
<p>Execute the following command to get detailed help
information about the <code>mrs.add.service()</code>
function.</p>
<div class="sourceCode" id="cb18"><pre
class="sourceCode js"><code class="sourceCode javascript"><span id="cb18-1"><a href="#cb18-1" aria-hidden="true" tabindex="-1"></a>\<span class="op">?</span> mrs<span class="op">.</span><span class="at">add</span><span class="op">.</span><span class="at">service</span></span></code></pre></div>
<h3 data-number="4.3.3"
id="rest-service-definitions"><span
class="header-section-number">4.3.3</span> REST Service
Definitions</h3>
<h4 data-number="4.3.3.1" id="about-mrs-autorest"><span
class="header-section-number">4.3.3.1</span> About MRS
AutoREST</h4>
<p>AutoREST is a quick and easy way to expose database
schema tables, views, and procedures as REST
resources.</p>
<h4 data-number="4.3.3.2" id="rest-apis"><span
class="header-section-number">4.3.3.2</span> REST
APIs</h4>
<p>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:</p>
<ul>
<li><p>Data is modelled as a set of resources. Resources
are identified by URIs.</p></li>
<li><p>A small, uniform set of operations are used to
manipulate resources (for example, PUT, POST, GET,
DELETE).</p></li>
<li><p>A resource can have multiple representations (for
example, a blog might have an HTML representation and an
RSS representation).</p></li>
<li><p>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.</p></li>
</ul>
<h4 data-number="4.3.3.3"
id="restful-services-terminology"><span
class="header-section-number">4.3.3.3</span> RESTful
Services Terminology</h4>
<p>This section introduces some common terms that are
used throughout this document:</p>
<ul>
<li><p><strong>RESTful service:</strong> An HTTP web
service that conforms to the tenets of the RESTful
architectural style.</p></li>
<li><p><strong>Resource module:</strong> An
organizational unit that is used to group related
resource templates.</p></li>
<li><p><strong>Resource template:</strong> 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</p></li>
<li><p><strong>URI pattern:</strong> A pattern for the
resource template. Can be either a route pattern or a
URI template, although you are encouraged to use route
patterns.</p></li>
<li><p><strong>Route pattern:</strong> 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).</p></li>
<li><p><strong>HTTP operation:</strong> 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).</p></li>
</ul>
<h2 data-number="4.4"
id="adding-a-database-schemas-to-a-rest-service"><span
class="header-section-number">4.4</span> Adding a
Database Schemas to a REST Service</h2>
<p>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.</p>
<p>The REST schema can be created by using MySQL Shell
for VS Code or MySQL Shell on the command line.</p>
<p>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.</p>
<p><strong><em>Preconditions for Adding Database Schemas
and Objects</em></strong></p>
<p>Before adding REST schemas and objects, ensure that
the following preconditions are met:</p>
<ul>
<li>A REST service must be added first (see <a
href="#adding-rest-services-and-database-objects">Adding
a REST Service</a>).</li>
<li>The MySQL account used to connect to the targeting
MySQL Solution needs to be granted the
<code>mysql_rest_service_schema_admin</code> MySQL role
or a superset of privileges.</li>
</ul>
<p>To grant the
<code>mysql_rest_service_schema_admin</code> MySQL role,
execute the following SQL statement.</p>
<div class="sourceCode" id="cb19"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb19-1"><a href="#cb19-1" aria-hidden="true" tabindex="-1"></a><span class="kw">GRANT</span> <span class="st">'mysql_rest_service_schema_admin'</span> <span class="kw">TO</span> <span class="st">'user_account'</span>@<span class="st">'%'</span>;</span>
<span id="cb19-2"><a href="#cb19-2" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb19-3"><a href="#cb19-3" aria-hidden="true" tabindex="-1"></a><span class="co">-- Please ensure to include all roles in the next statement</span></span>
<span id="cb19-4"><a href="#cb19-4" aria-hidden="true" tabindex="-1"></a><span class="co">-- that should become active when the user connects</span></span>
<span id="cb19-5"><a href="#cb19-5" aria-hidden="true" tabindex="-1"></a><span class="kw">ALTER</span> <span class="fu">USER</span> <span class="st">'user_account'</span>@<span class="st">'%'</span> <span class="kw">DEFAULT</span> <span class="kw">ROLE</span> <span class="st">'mysql_rest_service_schema_admin'</span>;</span></code></pre></div>
<h3 data-number="4.4.1"
id="adding-a-schema-via-mrs-ddl"><span
class="header-section-number">4.4.1</span> Adding a
Schema via MRS DDL</h3>
<p>On an active MySQL Shell connection execute the <a
href="sql.html#create-rest-schema">CREATE REST
SCHEMA</a> DDL statement referencing the corresponding
MySQL database schema in the FROM clause. Please see the
MRS SQL Reference for more details.</p>
<p><strong><em>Example</em></strong></p>
<p>The following example adds a REST schema for the
<code>sakila</code> database schema to the REST service
<code>/myService</code>.</p>
<div class="sourceCode" id="cb20"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb20-1"><a href="#cb20-1" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">OR</span> <span class="kw">REPLACE</span> REST <span class="kw">SCHEMA</span> <span class="op">/</span>sakila <span class="kw">ON</span> SERVICE <span class="op">/</span>myService</span>
<span id="cb20-2"><a href="#cb20-2" aria-hidden="true" tabindex="-1"></a> <span class="kw">FROM</span> `sakila`</span>
<span id="cb20-3"><a href="#cb20-3" aria-hidden="true" tabindex="-1"></a> <span class="kw">COMMENT</span> <span class="ot">"The sakila schema"</span>;</span></code></pre></div>
<h3 data-number="4.4.2"
id="adding-a-schema-using-the-mysql-shell-for-vs-code-ui"><span
class="header-section-number">4.4.2</span> Adding a
Schema using the MySQL Shell for VS Code UI</h3>
<p>To add a database schema to a REST service:</p>
<ol type="1">
<li><p>Right-click the schema in the DATABASE
CONNECTIONS view and select
<code>Add Schema to REST Service</code>. This will open
a dialog where all REST schema parameters can be
set.</p></li>
<li><p>Click <code>OK</code> to add the schema.</p></li>
</ol>
<figure>
<img
src="sections/devGuide/../../images/vsc-mrs-add-schema.png"
title="Adding a Database Schema"
alt="Adding a Database Schema" />
<figcaption aria-hidden="true">Adding a Database
Schema</figcaption>
</figure>
<h3 data-number="4.4.3"
id="adding-a-rest-schema-with-a-mysql-shell-script"><span
class="header-section-number">4.4.3</span> Adding a REST
Schema with a MySQL Shell Script</h3>
<p>To add a database schema to a REST service, call the
<code>mrs.add.schema()</code> function.</p>
<p>When started without parameters, an interactive
wizard prompts you for the required parameters.</p>
<div class="sourceCode" id="cb21"><pre
class="sourceCode bash"><code class="sourceCode bash"><span id="cb21-1"><a href="#cb21-1" aria-hidden="true" tabindex="-1"></a> <span class="ex">MySQL</span> <span class="op">></span> localhost:33060+ <span class="op">></span> JS <span class="op">></span> mrs.add.schema<span class="er">(</span><span class="kw">)</span></span>
<span id="cb21-2"><a href="#cb21-2" aria-hidden="true" tabindex="-1"></a> <span class="ex">1</span> information_schema</span>
<span id="cb21-3"><a href="#cb21-3" aria-hidden="true" tabindex="-1"></a> <span class="ex">2</span> performance_schema</span>
<span id="cb21-4"><a href="#cb21-4" aria-hidden="true" tabindex="-1"></a> <span class="ex">3</span> sys</span>
<span id="cb21-5"><a href="#cb21-5" aria-hidden="true" tabindex="-1"></a> <span class="ex">4</span> sakila</span>
<span id="cb21-6"><a href="#cb21-6" aria-hidden="true" tabindex="-1"></a> <span class="ex">5</span> test</span>
<span id="cb21-7"><a href="#cb21-7" aria-hidden="true" tabindex="-1"></a> <span class="ex">6</span> forum</span>
<span id="cb21-8"><a href="#cb21-8" aria-hidden="true" tabindex="-1"></a> <span class="ex">7</span> ortho</span>
<span id="cb21-9"><a href="#cb21-9" aria-hidden="true" tabindex="-1"></a> <span class="ex">8</span> mrs_notes</span>
<span id="cb21-10"><a href="#cb21-10" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb21-11"><a href="#cb21-11" aria-hidden="true" tabindex="-1"></a><span class="ex">Please</span> enter the name or index of a schema: 4</span>
<span id="cb21-12"><a href="#cb21-12" aria-hidden="true" tabindex="-1"></a><span class="ex">Please</span> enter the request path for this schema [/sakila]: /sakila</span>
<span id="cb21-13"><a href="#cb21-13" aria-hidden="true" tabindex="-1"></a><span class="ex">Should</span> the schema require authentication<span class="pp">?</span> [y/N]:</span>
<span id="cb21-14"><a href="#cb21-14" aria-hidden="true" tabindex="-1"></a><span class="ex">How</span> many items should be listed per page<span class="pp">?</span> [Schema Default]:</span>
<span id="cb21-15"><a href="#cb21-15" aria-hidden="true" tabindex="-1"></a><span class="ex">Comments:</span></span>
<span id="cb21-16"><a href="#cb21-16" aria-hidden="true" tabindex="-1"></a><span class="ex">Options:</span></span>
<span id="cb21-17"><a href="#cb21-17" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb21-18"><a href="#cb21-18" aria-hidden="true" tabindex="-1"></a><span class="ex">Service</span> with path /sakila created successfully.</span></code></pre></div>
<p>Execute the following command to get detailed help
information about the <code>mrs.add.schema()</code>
function.</p>
<div class="sourceCode" id="cb22"><pre
class="sourceCode js"><code class="sourceCode javascript"><span id="cb22-1"><a href="#cb22-1" aria-hidden="true" tabindex="-1"></a>\<span class="op">?</span> mrs<span class="op">.</span><span class="at">add</span><span class="op">.</span><span class="at">schema</span></span></code></pre></div>
<h2 data-number="4.5"
id="adding-a-schema-table-view-or-procedure"><span
class="header-section-number">4.5</span> Adding a Schema
Table, View or Procedure</h2>
<p>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.</p>
<p>The following figure shows the a REST schema and its
REST objects.</p>
<figure>
<img
src="sections/devGuide/../../images/vsc-mrs-schema-and-objects.png"
title="REST Schema and its Objects"
alt="REST Schema and its Objects" />
<figcaption aria-hidden="true">REST Schema and its
Objects</figcaption>
</figure>
<p>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 <a
href="#rest-data-mapping-views">REST data mapping
views</a>, stored procedures are added as REST
procedures.</p>
<blockquote>
<p>Note: REST data mapping views enabled application
developers to take a document centric approach when
implementing their applications. Please refer to the <a
href="#rest-data-mapping-views">JSON data mapping
view</a> section of this document to learn about the
advantages of using REST data mapping views.</p>
</blockquote>
<p>The MySQL database schema objects can be added by
using MySQL Shell for VS Code or MySQL Shell on the
command line.</p>
<h3 data-number="4.5.1"
id="adding-a-schema-object-via-mrs-ddl"><span
class="header-section-number">4.5.1</span> Adding a
Schema Object via MRS DDL</h3>
<p>On an active MySQL Shell connection execute the <a
href="sql.html#create-rest-view">CREATE REST DATA
MAPPING VIEW</a> DDL statement to add a database schema
table or view as REST object. Please see the <a
href="sql.html#create-rest-view">MRS SQL Reference</a>
for more details.</p>
<p>To add a stored procedure the <a
href="sql.html#create-rest-procedure">CREATE REST
PROCEDURE</a> DDL statement can be used.</p>
<p><strong><em>Examples</em></strong></p>
<p>The following example adds a REST data mapping view
for the <code>sakila.city</code> database schema
table.</p>
<div class="sourceCode" id="cb23"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb23-1"><a href="#cb23-1" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> REST <span class="kw">VIEW</span> <span class="op">/</span>city</span>
<span id="cb23-2"><a href="#cb23-2" aria-hidden="true" tabindex="-1"></a><span class="kw">ON</span> SERVICE <span class="op">/</span>myService <span class="kw">SCHEMA</span> <span class="op">/</span>sakila</span>
<span id="cb23-3"><a href="#cb23-3" aria-hidden="true" tabindex="-1"></a><span class="kw">AS</span> `sakila`.`city` {</span>
<span id="cb23-4"><a href="#cb23-4" aria-hidden="true" tabindex="-1"></a> cityId: city_id @SORTABLE,</span>
<span id="cb23-5"><a href="#cb23-5" aria-hidden="true" tabindex="-1"></a> city: city,</span>
<span id="cb23-6"><a href="#cb23-6" aria-hidden="true" tabindex="-1"></a> countryId: country_id,</span>
<span id="cb23-7"><a href="#cb23-7" aria-hidden="true" tabindex="-1"></a> lastUpdate: last_update</span>
<span id="cb23-8"><a href="#cb23-8" aria-hidden="true" tabindex="-1"></a>}</span>
<span id="cb23-9"><a href="#cb23-9" aria-hidden="true" tabindex="-1"></a>AUTHENTICATION REQUIRED;</span></code></pre></div>
<p>The next example adds a REST procedure for the
<code>sakila.film_in_stock</code> database schema stored
procedure.</p>
<div class="sourceCode" id="cb24"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb24-1"><a href="#cb24-1" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">OR</span> <span class="kw">REPLACE</span> REST <span class="kw">PROCEDURE</span> <span class="op">/</span>filmInStock</span>
<span id="cb24-2"><a href="#cb24-2" aria-hidden="true" tabindex="-1"></a><span class="kw">AS</span> `sakila`.`film_in_stock`</span>
<span id="cb24-3"><a href="#cb24-3" aria-hidden="true" tabindex="-1"></a><span class="kw">PARAMETERS</span> {</span>
<span id="cb24-4"><a href="#cb24-4" aria-hidden="true" tabindex="-1"></a> pFilmId: p_film_id @IN,</span>
<span id="cb24-5"><a href="#cb24-5" aria-hidden="true" tabindex="-1"></a> pStoreId: p_store_id @IN,</span>
<span id="cb24-6"><a href="#cb24-6" aria-hidden="true" tabindex="-1"></a> pFilmCount: p_film_count @OUT</span>
<span id="cb24-7"><a href="#cb24-7" aria-hidden="true" tabindex="-1"></a>}</span>
<span id="cb24-8"><a href="#cb24-8" aria-hidden="true" tabindex="-1"></a>RESULT MyServiceSakilaFilmInStock {</span>
<span id="cb24-9"><a href="#cb24-9" aria-hidden="true" tabindex="-1"></a> inventoryId: inventory_id @DATATYPE(<span class="ot">"int"</span>)</span>
<span id="cb24-10"><a href="#cb24-10" aria-hidden="true" tabindex="-1"></a>}</span>
<span id="cb24-11"><a href="#cb24-11" aria-hidden="true" tabindex="-1"></a>AUTHENTICATION REQUIRED;</span></code></pre></div>
<h3 data-number="4.5.2"
id="adding-a-schema-object-with-mysql-shell-for-vs-code-ui"><span
class="header-section-number">4.5.2</span> Adding a
Schema Object with MySQL Shell for VS Code UI</h3>
<p>To add a database schema object to a REST schema:</p>
<ol type="1">
<li><p>Right-click on the database object in the
DATABASE CONNECTIONS view and select
<code>Add Database Object to REST Service</code>. This
will open the <a href="#mrs-dialog-reference">MySQL REST
Object dialog</a>.</p></li>
<li><p>Adjust all REST object settings
accordingly.</p></li>
<li><p>Press <code>OK</code> to add the database schema
object.</p></li>
</ol>
<figure>
<img
src="sections/devGuide/../../images/vsc-mrs-add-db-object.png"
title="Adding a Database Object"
alt="Adding a Database Object" />
<figcaption aria-hidden="true">Adding a Database
Object</figcaption>
</figure>
<h3 data-number="4.5.3"
id="adding-a-database-object-with-mysql-shell"><span
class="header-section-number">4.5.3</span> Adding a
Database Object with MySQL Shell</h3>
<p>To add a database schema to a REST service call the
<code>mrs.add.dbObject()</code> function.</p>
<p>When started without parameters, an interactive
wizard prompts you for the required parameters.</p>
<div class="sourceCode" id="cb25"><pre
class="sourceCode bash"><code class="sourceCode bash"><span id="cb25-1"><a href="#cb25-1" aria-hidden="true" tabindex="-1"></a><span class="ex">MySQL</span> <span class="op">></span> localhost:33060+ <span class="op">></span> JS <span class="op">></span> mrs.add.dbObject<span class="er">(</span><span class="kw">)</span></span>
<span id="cb25-2"><a href="#cb25-2" aria-hidden="true" tabindex="-1"></a> <span class="ex">1</span> mrs_notes</span>
<span id="cb25-3"><a href="#cb25-3" aria-hidden="true" tabindex="-1"></a> <span class="ex">2</span> sakila</span>
<span id="cb25-4"><a href="#cb25-4" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb25-5"><a href="#cb25-5" aria-hidden="true" tabindex="-1"></a><span class="ex">Please</span> enter the name or index of a schema: 2</span>
<span id="cb25-6"><a href="#cb25-6" aria-hidden="true" tabindex="-1"></a> <span class="ex">1</span> TABLE</span>
<span id="cb25-7"><a href="#cb25-7" aria-hidden="true" tabindex="-1"></a> <span class="ex">2</span> VIEW</span>
<span id="cb25-8"><a href="#cb25-8" aria-hidden="true" tabindex="-1"></a> <span class="ex">3</span> PROCEDURE</span>
<span id="cb25-9"><a href="#cb25-9" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb25-10"><a href="#cb25-10" aria-hidden="true" tabindex="-1"></a><span class="ex">Please</span> enter the name or index of a database object type [TABLE]:</span>
<span id="cb25-11"><a href="#cb25-11" aria-hidden="true" tabindex="-1"></a> <span class="ex">1</span> actor</span>
<span id="cb25-12"><a href="#cb25-12" aria-hidden="true" tabindex="-1"></a> <span class="ex">2</span> address</span>
<span id="cb25-13"><a href="#cb25-13" aria-hidden="true" tabindex="-1"></a> <span class="ex">3</span> category</span>
<span id="cb25-14"><a href="#cb25-14" aria-hidden="true" tabindex="-1"></a> <span class="ex">4</span> city</span>
<span id="cb25-15"><a href="#cb25-15" aria-hidden="true" tabindex="-1"></a> <span class="ex">5</span> country</span>
<span id="cb25-16"><a href="#cb25-16" aria-hidden="true" tabindex="-1"></a> <span class="ex">6</span> customer</span>
<span id="cb25-17"><a href="#cb25-17" aria-hidden="true" tabindex="-1"></a> <span class="ex">7</span> film</span>
<span id="cb25-18"><a href="#cb25-18" aria-hidden="true" tabindex="-1"></a> <span class="ex">8</span> film_actor</span>
<span id="cb25-19"><a href="#cb25-19" aria-hidden="true" tabindex="-1"></a> <span class="ex">9</span> film_category</span>
<span id="cb25-20"><a href="#cb25-20" aria-hidden="true" tabindex="-1"></a> <span class="ex">10</span> film_text</span>
<span id="cb25-21"><a href="#cb25-21" aria-hidden="true" tabindex="-1"></a> <span class="ex">11</span> inventory</span>
<span id="cb25-22"><a href="#cb25-22" aria-hidden="true" tabindex="-1"></a> <span class="ex">12</span> language</span>
<span id="cb25-23"><a href="#cb25-23" aria-hidden="true" tabindex="-1"></a> <span class="ex">13</span> payment</span>
<span id="cb25-24"><a href="#cb25-24" aria-hidden="true" tabindex="-1"></a> <span class="ex">14</span> rental</span>
<span id="cb25-25"><a href="#cb25-25" aria-hidden="true" tabindex="-1"></a> <span class="ex">15</span> staff</span>
<span id="cb25-26"><a href="#cb25-26" aria-hidden="true" tabindex="-1"></a> <span class="ex">16</span> store</span>
<span id="cb25-27"><a href="#cb25-27" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb25-28"><a href="#cb25-28" aria-hidden="true" tabindex="-1"></a><span class="ex">Please</span> enter the name or index of an database object: 4</span>
<span id="cb25-29"><a href="#cb25-29" aria-hidden="true" tabindex="-1"></a><span class="ex">Please</span> enter the request path for this object [/city]:</span>
<span id="cb25-30"><a href="#cb25-30" aria-hidden="true" tabindex="-1"></a> <span class="ex">1</span> CREATE</span>
<span id="cb25-31"><a href="#cb25-31" aria-hidden="true" tabindex="-1"></a> <span class="ex">2</span> READ</span>
<span id="cb25-32"><a href="#cb25-32" aria-hidden="true" tabindex="-1"></a> <span class="ex">3</span> UPDATE</span>
<span id="cb25-33"><a href="#cb25-33" aria-hidden="true" tabindex="-1"></a> <span class="ex">4</span> DELETE</span>
<span id="cb25-34"><a href="#cb25-34" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb25-35"><a href="#cb25-35" aria-hidden="true" tabindex="-1"></a><span class="ex">Please</span> select the CRUD operations that should be supported, <span class="st">'*'</span> for all [READ]:</span>
<span id="cb25-36"><a href="#cb25-36" aria-hidden="true" tabindex="-1"></a> <span class="ex">1</span> FEED</span>
<span id="cb25-37"><a href="#cb25-37" aria-hidden="true" tabindex="-1"></a> <span class="ex">2</span> ITEM</span>
<span id="cb25-38"><a href="#cb25-38" aria-hidden="true" tabindex="-1"></a> <span class="ex">3</span> MEDIA</span>
<span id="cb25-39"><a href="#cb25-39" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb25-40"><a href="#cb25-40" aria-hidden="true" tabindex="-1"></a><span class="ex">Please</span> select the CRUD operation format [FEED]:</span>
<span id="cb25-41"><a href="#cb25-41" aria-hidden="true" tabindex="-1"></a><span class="ex">Should</span> the db_object require authentication<span class="pp">?</span> [y/N]:</span>
<span id="cb25-42"><a href="#cb25-42" aria-hidden="true" tabindex="-1"></a><span class="ex">Should</span> row ownership be required when querying the object<span class="pp">?</span> [y/N]:</span>
<span id="cb25-43"><a href="#cb25-43" aria-hidden="true" tabindex="-1"></a><span class="ex">How</span> many items should be listed per page<span class="pp">?</span> [Schema Default]:</span>
<span id="cb25-44"><a href="#cb25-44" aria-hidden="true" tabindex="-1"></a><span class="ex">Comments:</span></span>
<span id="cb25-45"><a href="#cb25-45" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb25-46"><a href="#cb25-46" aria-hidden="true" tabindex="-1"></a><span class="ex">Object</span> added successfully.</span></code></pre></div>
<p>Execute the following command to get detailed help
information about the <code>mrs.add.dbObject()</code>
function.</p>
<div class="sourceCode" id="cb26"><pre
class="sourceCode js"><code class="sourceCode javascript"><span id="cb26-1"><a href="#cb26-1" aria-hidden="true" tabindex="-1"></a>\<span class="op">?</span> mrs<span class="op">.</span><span class="at">add</span><span class="op">.</span><span class="at">dbObject</span></span></code></pre></div>
<!-- Copyright (c) 2022, 2026, Oracle and/or its affiliates.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License, version 2.0,
as published by the Free Software Foundation.
This program is designed to work with certain software (including
but not limited to OpenSSL) that is licensed under separate terms, as
designated in a particular file or component or in included license
documentation. The authors of MySQL hereby grant you an additional
permission to link the program and your derivative works with the
separately licensed software that they have either included with
the program or referenced in the documentation.
This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See
the GNU General Public License, version 2.0, for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -->
<h1 data-number="5"
id="working-interactively-with-rest-services"><span
class="header-section-number">5</span> Working
Interactively with REST Services</h1>
<p>MySQL Shell for VS Code features a live, interactive
workflow for designing REST Services.</p>
<p>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.</p>
<p><strong><em>Preconditions for The Interactive
Workflow</em></strong></p>
<ul>
<li>A MySQL Router instance needs to be <a
href="#bootstrapping-and-running-mysql-routers-with-mrs-support">bootstrapped
and running with MRS support</a>. When using a local
development setup it is also possible to do this <a
href="#using-mysql-shell-for-vs-code-to-bootstrap-and-run-mysql-router">directly
from within VS Code</a>.</li>
</ul>
<h2 data-number="5.1"
id="switching-to-typescript-mode"><span
class="header-section-number">5.1</span> Switching to
TypeScript Mode</h2>
<p>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 <code>\ts</code>
if it is in SQL mode.</p>
<figure>
<img
src="sections/devGuide/../../images/msg-db-notebook-switch-to-ts.svg"
title="DB Notebook - Switch to TS mode"
alt="DB Notebook - Switch to TS mode" />
<figcaption aria-hidden="true">DB Notebook - Switch to
TS mode</figcaption>
</figure>
<h2 data-number="5.2" id="choosing-a-rest-service"><span
class="header-section-number">5.2</span> Choosing a REST
Service</h2>
<p>In order to work with a REST service on a DB Notebook
it needs to be set as <code>current</code> REST Service.
This is similar to executing a SQL
<code>USE db_name</code> statement to set the
<code>current</code> database schema.</p>
<p>To get information about the current REST service use
the global <code>mrs</code> object and execute the
<code>mrs.getStatus()</code> function. It will print
information about the MRS status. The current REST
service has the property <code>isCurrent</code> set to
<code>true</code>.</p>
<p><strong><em>Example</em></strong></p>
<div class="sourceCode" id="cb27"><pre
class="sourceCode ts"><code class="sourceCode typescript"><span id="cb27-1"><a href="#cb27-1" aria-hidden="true" tabindex="-1"></a>ts<span class="op">></span> mrs<span class="op">.</span><span class="fu">getStatus</span>()<span class="op">;</span></span>
<span id="cb27-2"><a href="#cb27-2" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb27-3"><a href="#cb27-3" aria-hidden="true" tabindex="-1"></a> <span class="st">"configured"</span><span class="op">:</span> true<span class="op">,</span></span>
<span id="cb27-4"><a href="#cb27-4" aria-hidden="true" tabindex="-1"></a> <span class="st">"info"</span><span class="op">:</span> <span class="st">"2 REST services available."</span><span class="op">,</span></span>
<span id="cb27-5"><a href="#cb27-5" aria-hidden="true" tabindex="-1"></a> <span class="st">"services"</span><span class="op">:</span> [</span>
<span id="cb27-6"><a href="#cb27-6" aria-hidden="true" tabindex="-1"></a> {</span>
<span id="cb27-7"><a href="#cb27-7" aria-hidden="true" tabindex="-1"></a> <span class="st">"serviceName"</span><span class="op">:</span> <span class="st">"myService"</span><span class="op">,</span></span>
<span id="cb27-8"><a href="#cb27-8" aria-hidden="true" tabindex="-1"></a> <span class="st">"url"</span><span class="op">:</span> <span class="st">"https://localhost:8443/myService"</span><span class="op">,</span></span>
<span id="cb27-9"><a href="#cb27-9" aria-hidden="true" tabindex="-1"></a> <span class="st">"isCurrent"</span><span class="op">:</span> true</span>
<span id="cb27-10"><a href="#cb27-10" aria-hidden="true" tabindex="-1"></a> }<span class="op">,</span></span>
<span id="cb27-11"><a href="#cb27-11" aria-hidden="true" tabindex="-1"></a> {</span>
<span id="cb27-12"><a href="#cb27-12" aria-hidden="true" tabindex="-1"></a> <span class="st">"serviceName"</span><span class="op">:</span> <span class="st">"myPublicService"</span><span class="op">,</span></span>
<span id="cb27-13"><a href="#cb27-13" aria-hidden="true" tabindex="-1"></a> <span class="st">"url"</span><span class="op">:</span> <span class="st">"https://localhost:8443/myPublicService"</span><span class="op">,</span></span>
<span id="cb27-14"><a href="#cb27-14" aria-hidden="true" tabindex="-1"></a> <span class="st">"isCurrent"</span><span class="op">:</span> false</span>
<span id="cb27-15"><a href="#cb27-15" aria-hidden="true" tabindex="-1"></a> }</span>
<span id="cb27-16"><a href="#cb27-16" aria-hidden="true" tabindex="-1"></a> ]</span>
<span id="cb27-17"><a href="#cb27-17" aria-hidden="true" tabindex="-1"></a>}</span></code></pre></div>
<p>Once a current REST service is set, the <a
href="sdk.html#document-top">MRS TypeScript Client
API</a> for this service is automatically generated
on-the-fly and made available to TypeScript code blocks
on the DB Notebooks.</p>
<p>The current REST service object be directly accessed
via a global variable using the same name as listed by
the <code>mrs.getStatus()</code> function in the
<code>serviceName</code> property. The
<code>serviceName</code> is directly derived from the
REST service’s <code>url context root</code> path by
converting it to <code>camel case</code> and removing
all slashes <code>/</code>. E.g. a REST service with the
<code>url context root</code> of <code>/myService</code>
will be accessible as <code>myService</code>.</p>
<p><strong><em>Example</em></strong></p>
<div class="sourceCode" id="cb28"><pre
class="sourceCode ts"><code class="sourceCode typescript"><span id="cb28-1"><a href="#cb28-1" aria-hidden="true" tabindex="-1"></a>ts<span class="op">></span> myService<span class="op">.</span><span class="at">url</span><span class="op">;</span></span>
<span id="cb28-2"><a href="#cb28-2" aria-hidden="true" tabindex="-1"></a>https<span class="op">:</span><span class="co">//localhost:8443/myService</span></span></code></pre></div>
<p>The current REST service can either be set from a DB
Notebook via a TypeScript or through the VS Code UI.</p>
<h3 data-number="5.2.1"
id="using-typescript-to-setting-the-current-rest-service"><span
class="header-section-number">5.2.1</span> Using
Typescript to Setting the Current REST Service</h3>
<p>The global <code>mrs</code> object automatically
holds properties for all available REST services. The
naming of the REST service properties matches the
<code>serviceName</code> of each REST service as
discussed in the previous section.</p>
<p>Execute the
<code>mrs.<serviceName>.setAsCurrent()</code>
function to make the given REST service the current one.
The VS Code auto-completion feature will assist with
selecting the <code>serviceName</code>.</p>
<p><strong><em>Example</em></strong></p>
<div class="sourceCode" id="cb29"><pre
class="sourceCode typescript"><code class="sourceCode typescript"><span id="cb29-1"><a href="#cb29-1" aria-hidden="true" tabindex="-1"></a>ts<span class="op">></span> mrs<span class="op">.</span><span class="at">myPublicService</span><span class="op">.</span><span class="fu">setAsCurrent</span>()<span class="op">;</span></span></code></pre></div>
<blockquote>
<p>Note: The current REST service is only available
after executing the full TypeScript code block with
<code>[command] + [Return]</code> on macOS and
<code>[Ctrl] + [Return]</code> 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.</p>
</blockquote>
<h3 data-number="5.2.2"
id="using-vs-code-to-setting-the-current-rest-service"><span
class="header-section-number">5.2.2</span> Using VS Code
to Setting the Current REST Service</h3>
<p>Browse the <code>DATABASE CONNECTIONS</code> View in
VS Code’s <code>Primary Sidebar</code>, open the current
DB Connection as well as the
<code>MySQL REST Service</code> tree items and right
click onto the desired MRS service and select
<code>Set as Current REST Service</code> from the
context menu.</p>
<figure>
<img
src="sections/devGuide/../../images/vsc-mrs-context-menu-set-current-service.svg"
title="Setting the Current REST Service"
alt="Setting the Current REST Service" />
<figcaption aria-hidden="true">Setting the Current REST
Service</figcaption>
</figure>
<p>The current REST service is indicated by a solid,
filled REST service icon. All other REST services use an
icon with outlines only.</p>
<h2 data-number="5.3" id="authentication"><span
class="header-section-number">5.3</span>
Authentication</h2>
<p>If some of the REST objects require authentication
and a REST Authentication App has been added to the REST
service, use the <code>authenticate()</code> function of
the REST service Client API object. This will show an
login dialog where the credentials of a user account can
be specified.</p>
<div class="sourceCode" id="cb30"><pre
class="sourceCode ts"><code class="sourceCode typescript"><span id="cb30-1"><a href="#cb30-1" aria-hidden="true" tabindex="-1"></a>ts<span class="op">></span> myService<span class="op">.</span><span class="fu">authenticate</span>()<span class="op">;</span></span></code></pre></div>
<blockquote>
<p>The <code>authenticate()</code> function only works
with the built in MRS authentication vendor. Please make
sure to set this vendor when adding the REST
Authentication App.</p>
</blockquote>
<h2 data-number="5.4" id="querying-a-rest-object"><span
class="header-section-number">5.4</span> Querying a REST
Object</h2>
<p>In the following examples case the
<code>sakila.city</code> database table was used, as can
be seen in the screenshot above.</p>
<div class="sourceCode" id="cb31"><pre
class="sourceCode ts"><code class="sourceCode typescript"><span id="cb31-1"><a href="#cb31-1" aria-hidden="true" tabindex="-1"></a>ts<span class="op">></span> myService<span class="op">.</span><span class="at">sakila</span><span class="op">.</span><span class="at">city</span><span class="op">.</span><span class="fu">findFirst</span>()<span class="op">;</span></span>
<span id="cb31-2"><a href="#cb31-2" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb31-3"><a href="#cb31-3" aria-hidden="true" tabindex="-1"></a> <span class="st">"city"</span><span class="op">:</span> <span class="st">"A Corua (La Corua)"</span><span class="op">,</span></span>
<span id="cb31-4"><a href="#cb31-4" aria-hidden="true" tabindex="-1"></a> <span class="st">"cityId"</span><span class="op">:</span> 1<span class="op">,</span></span>
<span id="cb31-5"><a href="#cb31-5" aria-hidden="true" tabindex="-1"></a> <span class="st">"countryId"</span><span class="op">:</span> 87<span class="op">,</span></span>
<span id="cb31-6"><a href="#cb31-6" aria-hidden="true" tabindex="-1"></a> <span class="st">"lastUpdate"</span><span class="op">:</span> <span class="st">"2006-02-15 04:45:25.000000"</span><span class="op">,</span></span>
<span id="cb31-7"><a href="#cb31-7" aria-hidden="true" tabindex="-1"></a>}</span></code></pre></div>
<p>The fields can be filtered and a conditional
<code>where</code> clause can be added. Please refer to
the <a href="sdk.html#document-top">MRS SDK Client
API</a> documentation for more information.</p>
<div class="sourceCode" id="cb32"><pre
class="sourceCode ts"><code class="sourceCode typescript"><span id="cb32-1"><a href="#cb32-1" aria-hidden="true" tabindex="-1"></a>ts<span class="op">></span> myService<span class="op">.</span><span class="at">sakila</span><span class="op">.</span><span class="at">city</span><span class="op">.</span><span class="fu">find</span>({select<span class="op">:</span> [<span class="st">"city"</span><span class="op">,</span> <span class="st">"cityId"</span>]<span class="op">,</span> where<span class="op">:</span> {city<span class="op">:</span> {$like<span class="op">:</span> <span class="st">"NE%"</span>}}})<span class="op">;</span></span>
<span id="cb32-2"><a href="#cb32-2" aria-hidden="true" tabindex="-1"></a>[</span>
<span id="cb32-3"><a href="#cb32-3" aria-hidden="true" tabindex="-1"></a> {</span>
<span id="cb32-4"><a href="#cb32-4" aria-hidden="true" tabindex="-1"></a> <span class="st">"city"</span><span class="op">:</span> <span class="st">"Newcastle"</span><span class="op">,</span></span>
<span id="cb32-5"><a href="#cb32-5" aria-hidden="true" tabindex="-1"></a> <span class="st">"cityId"</span><span class="op">:</span> 364<span class="op">,</span></span>
<span id="cb32-6"><a href="#cb32-6" aria-hidden="true" tabindex="-1"></a> }<span class="op">,</span></span>
<span id="cb32-7"><a href="#cb32-7" aria-hidden="true" tabindex="-1"></a> {</span>
<span id="cb32-8"><a href="#cb32-8" aria-hidden="true" tabindex="-1"></a> <span class="st">"city"</span><span class="op">:</span> <span class="st">"Nezahualcyotl"</span><span class="op">,</span></span>
<span id="cb32-9"><a href="#cb32-9" aria-hidden="true" tabindex="-1"></a> <span class="st">"cityId"</span><span class="op">:</span> 365<span class="op">,</span></span>
<span id="cb32-10"><a href="#cb32-10" aria-hidden="true" tabindex="-1"></a> }</span>
<span id="cb32-11"><a href="#cb32-11" aria-hidden="true" tabindex="-1"></a>]</span></code></pre></div>
<p>To quickly edit a REST DB Object using the <a
href="#mrs-object-dialog">REST Object dialog</a>, the
<code>edit()</code> function can be used. Please note
that this function is only available on DB
Notebooks.</p>
<div class="sourceCode" id="cb33"><pre
class="sourceCode ts"><code class="sourceCode typescript"><span id="cb33-1"><a href="#cb33-1" aria-hidden="true" tabindex="-1"></a>ts<span class="op">></span> myService<span class="op">.</span><span class="at">sakila</span><span class="op">.</span><span class="at">city</span><span class="op">.</span><span class="fu">edit</span>()</span></code></pre></div>
<!-- Copyright (c) 2022, 2026, Oracle and/or its affiliates.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License, version 2.0,
as published by the Free Software Foundation.
This program is designed to work with certain software (including
but not limited to OpenSSL) that is licensed under separate terms, as
designated in a particular file or component or in included license
documentation. The authors of MySQL hereby grant you an additional
permission to link the program and your derivative works with the
separately licensed software that they have either included with
the program or referenced in the documentation.
This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See
the GNU General Public License, version 2.0, for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -->
<h1 data-number="6" id="rest-data-mapping-views"><span
class="header-section-number">6</span> REST Data Mapping
Views</h1>
<h2 data-number="6.1"
id="introduction-to-rest-data-mapping-views"><span
class="header-section-number">6.1</span> Introduction to
REST Data Mapping Views</h2>
<p>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.</p>
<h3 data-number="6.1.1" id="use-cases"><span
class="header-section-number">6.1.1</span> Use
Cases</h3>
<p>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).</p>
<ol type="1">
<li>Make a single relational table or view available via
a REST endpoint
<ul>
<li>Exposes the rows of a table as a set of
<strong>flat</strong> JSON documents</li>
<li>Allows the application to use a traditional
relational approach when needed</li>
</ul></li>
<li>Create a single REST endpoint for a set of related
database schema tables
<ul>
<li>Exposes the related tables as
<strong>nested</strong> JSON objects inside a set of
JSON documents</li>
<li>Allows the application to take an document oriented
approach</li>
</ul></li>
</ol>
<p>The following figure visualizes these two use
cases.</p>
<figure>
<img
src="sections/devGuide/../../images/rest-view-use-cases.svg"
title="REST Data Mapping View - Use Cases"
alt="REST Data Mapping View - Use Cases" />
<figcaption aria-hidden="true">REST Data Mapping View -
Use Cases</figcaption>
</figure>
<h3 data-number="6.1.2"
id="rest-data-mapping-view-workflow"><span
class="header-section-number">6.1.2</span> REST Data
Mapping View Workflow</h3>
<p>The REST data mapping views can be created using the
<a href="sql.html#create-rest-view">CREATE REST DATA
MAPPING VIEW</a> MRS DDL statement or <a
href="#interactive-rest-view-design">interactively using
the MRS Object Dialog</a> of the MySQL Shell for VS Code
extension.</p>
<p>Once a REST data mapping view has been created, it is
extremely simple to access it using REST. The following
workflow applies.</p>
<ul>
<li>GET a document from the REST data mapping view</li>
<li>Make any changes needed to the document, including
changes to the nested JSON objects</li>
<li>PUT the document back into the REST data mapping
view</li>
</ul>
<p>The next figure shows a typical JSON document update
cycle.</p>
<figure>
<img
src="sections/devGuide/../../images/rest-view-update-cycle.svg"
title="REST VIEW - Update Cycle"
alt="REST VIEW - Update Cycle" />
<figcaption aria-hidden="true">REST VIEW - Update
Cycle</figcaption>
</figure>
<p>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.</p>
<h2 data-number="6.2"
id="lock-free-optimistic-concurrency-control"><span
class="header-section-number">6.2</span> Lock-Free
Optimistic Concurrency Control</h2>
<p>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 <code>_metadata.etag</code> field.</p>
<p>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.</p>
<p>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
<code>@nocheck</code> attribute.</p>
<p><strong><em>Example</em></strong></p>
<p>If at first,
<code>GET /myService/sakila/city/1</code> returns the
following JSON document to the client.</p>
<div class="sourceCode" id="cb34"><pre
class="sourceCode json"><code class="sourceCode json"><span id="cb34-1"><a href="#cb34-1" aria-hidden="true" tabindex="-1"></a><span class="fu">{</span></span>
<span id="cb34-2"><a href="#cb34-2" aria-hidden="true" tabindex="-1"></a> <span class="dt">"city"</span><span class="fu">:</span> <span class="st">"A Corua (La Corua)"</span><span class="fu">,</span></span>
<span id="cb34-3"><a href="#cb34-3" aria-hidden="true" tabindex="-1"></a> <span class="dt">"links"</span><span class="fu">:</span> <span class="ot">[</span></span>
<span id="cb34-4"><a href="#cb34-4" aria-hidden="true" tabindex="-1"></a> <span class="fu">{</span></span>
<span id="cb34-5"><a href="#cb34-5" aria-hidden="true" tabindex="-1"></a> <span class="dt">"rel"</span><span class="fu">:</span> <span class="st">"self"</span><span class="fu">,</span></span>
<span id="cb34-6"><a href="#cb34-6" aria-hidden="true" tabindex="-1"></a> <span class="dt">"href"</span><span class="fu">:</span> <span class="st">"/myService/sakila/city/1"</span></span>
<span id="cb34-7"><a href="#cb34-7" aria-hidden="true" tabindex="-1"></a> <span class="fu">}</span></span>
<span id="cb34-8"><a href="#cb34-8" aria-hidden="true" tabindex="-1"></a> <span class="ot">]</span><span class="fu">,</span></span>
<span id="cb34-9"><a href="#cb34-9" aria-hidden="true" tabindex="-1"></a> <span class="dt">"cityId"</span><span class="fu">:</span> <span class="dv">1</span><span class="fu">,</span></span>
<span id="cb34-10"><a href="#cb34-10" aria-hidden="true" tabindex="-1"></a> <span class="dt">"country"</span><span class="fu">:</span> <span class="fu">{</span></span>
<span id="cb34-11"><a href="#cb34-11" aria-hidden="true" tabindex="-1"></a> <span class="dt">"country"</span><span class="fu">:</span> <span class="st">"Spain"</span><span class="fu">,</span></span>
<span id="cb34-12"><a href="#cb34-12" aria-hidden="true" tabindex="-1"></a> <span class="dt">"countryId"</span><span class="fu">:</span> <span class="dv">87</span><span class="fu">,</span></span>
<span id="cb34-13"><a href="#cb34-13" aria-hidden="true" tabindex="-1"></a> <span class="dt">"lastUpdate"</span><span class="fu">:</span> <span class="st">"2006-02-15 04:44:00.000000"</span></span>
<span id="cb34-14"><a href="#cb34-14" aria-hidden="true" tabindex="-1"></a> <span class="fu">},</span></span>
<span id="cb34-15"><a href="#cb34-15" aria-hidden="true" tabindex="-1"></a> <span class="dt">"countryId"</span><span class="fu">:</span> <span class="dv">87</span><span class="fu">,</span></span>
<span id="cb34-16"><a href="#cb34-16" aria-hidden="true" tabindex="-1"></a> <span class="dt">"lastUpdate"</span><span class="fu">:</span> <span class="st">"2006-02-15 04:45:25.000000"</span><span class="fu">,</span></span>
<span id="cb34-17"><a href="#cb34-17" aria-hidden="true" tabindex="-1"></a> <span class="dt">"_metadata"</span><span class="fu">:</span> <span class="fu">{</span></span>
<span id="cb34-18"><a href="#cb34-18" aria-hidden="true" tabindex="-1"></a> <span class="dt">"etag"</span><span class="fu">:</span> <span class="st">"FFA2187AD4B98DF48EC40B3E807E0561A71D02C2F4F5A3B953AA6CB6E41CAD16"</span></span>
<span id="cb34-19"><a href="#cb34-19" aria-hidden="true" tabindex="-1"></a> <span class="fu">}</span></span>
<span id="cb34-20"><a href="#cb34-20" aria-hidden="true" tabindex="-1"></a><span class="fu">}</span></span></code></pre></div>
<p>Next, the client updates the object and changes the
city name to <code>A Coruña (La Coruña)</code> and
submits it by calling
<code>PUT /myService/sakila/city/1</code>.</p>
<div class="sourceCode" id="cb35"><pre
class="sourceCode json"><code class="sourceCode json"><span id="cb35-1"><a href="#cb35-1" aria-hidden="true" tabindex="-1"></a><span class="fu">{</span></span>
<span id="cb35-2"><a href="#cb35-2" aria-hidden="true" tabindex="-1"></a> <span class="dt">"city"</span><span class="fu">:</span> <span class="st">"A Coruña (La Coruña)"</span><span class="fu">,</span></span>
<span id="cb35-3"><a href="#cb35-3" aria-hidden="true" tabindex="-1"></a> <span class="dt">"links"</span><span class="fu">:</span> <span class="ot">[</span></span>
<span id="cb35-4"><a href="#cb35-4" aria-hidden="true" tabindex="-1"></a> <span class="fu">{</span></span>
<span id="cb35-5"><a href="#cb35-5" aria-hidden="true" tabindex="-1"></a> <span class="dt">"rel"</span><span class="fu">:</span> <span class="st">"self"</span><span class="fu">,</span></span>
<span id="cb35-6"><a href="#cb35-6" aria-hidden="true" tabindex="-1"></a> <span class="dt">"href"</span><span class="fu">:</span> <span class="st">"/myService/sakila/city/1"</span></span>
<span id="cb35-7"><a href="#cb35-7" aria-hidden="true" tabindex="-1"></a> <span class="fu">}</span></span>
<span id="cb35-8"><a href="#cb35-8" aria-hidden="true" tabindex="-1"></a> <span class="ot">]</span><span class="fu">,</span></span>
<span id="cb35-9"><a href="#cb35-9" aria-hidden="true" tabindex="-1"></a> <span class="dt">"cityId"</span><span class="fu">:</span> <span class="dv">1</span><span class="fu">,</span></span>
<span id="cb35-10"><a href="#cb35-10" aria-hidden="true" tabindex="-1"></a> <span class="dt">"country"</span><span class="fu">:</span> <span class="fu">{</span></span>
<span id="cb35-11"><a href="#cb35-11" aria-hidden="true" tabindex="-1"></a> <span class="dt">"country"</span><span class="fu">:</span> <span class="st">"Spain"</span><span class="fu">,</span></span>
<span id="cb35-12"><a href="#cb35-12" aria-hidden="true" tabindex="-1"></a> <span class="dt">"countryId"</span><span class="fu">:</span> <span class="dv">87</span><span class="fu">,</span></span>
<span id="cb35-13"><a href="#cb35-13" aria-hidden="true" tabindex="-1"></a> <span class="dt">"lastUpdate"</span><span class="fu">:</span> <span class="st">"2006-02-15 04:44:00.000000"</span></span>
<span id="cb35-14"><a href="#cb35-14" aria-hidden="true" tabindex="-1"></a> <span class="fu">},</span></span>
<span id="cb35-15"><a href="#cb35-15" aria-hidden="true" tabindex="-1"></a> <span class="dt">"countryId"</span><span class="fu">:</span> <span class="dv">87</span><span class="fu">,</span></span>
<span id="cb35-16"><a href="#cb35-16" aria-hidden="true" tabindex="-1"></a> <span class="dt">"lastUpdate"</span><span class="fu">:</span> <span class="st">"2006-02-15 04:45:25.000000"</span><span class="fu">,</span></span>
<span id="cb35-17"><a href="#cb35-17" aria-hidden="true" tabindex="-1"></a> <span class="dt">"_metadata"</span><span class="fu">:</span> <span class="fu">{</span></span>
<span id="cb35-18"><a href="#cb35-18" aria-hidden="true" tabindex="-1"></a> <span class="dt">"etag"</span><span class="fu">:</span> <span class="st">"FFA2187AD4B98DF48EC40B3E807E0561A71D02C2F4F5A3B953AA6CB6E41CAD16"</span></span>
<span id="cb35-19"><a href="#cb35-19" aria-hidden="true" tabindex="-1"></a> <span class="fu">}</span></span>
<span id="cb35-20"><a href="#cb35-20" aria-hidden="true" tabindex="-1"></a><span class="fu">}</span></span></code></pre></div>
<p>If the target object has been changed (e.g. by
another user) between the <code>GET</code> and the
<code>PUT</code> requests, the ETag check would fail and
the PUT would result in error
<code>412 Precondition Failed</code>.</p>
<h2 data-number="6.3"
id="interactive-rest-view-design"><span
class="header-section-number">6.3</span> Interactive
REST View Design</h2>
<p>While REST data mapping views can be created by
manually writing <a
href="sql.html#create-rest-view">CREATE REST DATA
MAPPING VIEW</a> MRS DDL statements, it is often much
easier to design REST data mapping views in a visual
editor.</p>
<p><a
href="https://marketplace.visualstudio.com/items?itemName=Oracle.mysql-shell-for-vs-code">MySQL
Shell for VS Code</a> includes the MySQL REST Object
dialog which features an advanced
<code>Data Mapping</code> designer. Using this designer
it is possible to create even complex, nested REST data
mapping views within seconds.</p>
<p>The <code>DDL Preview</code> button allows to preview
the corresponding MRS DDL statement while interactively
designing the REST data mapping view.</p>
<h3 data-number="6.3.1"
id="building-a-rest-data-mapping-view"><span
class="header-section-number">6.3.1</span> Building a
REST Data Mapping View</h3>
<p>Building a REST data mapping view for a single
relational table (or view) is straight forward. Using
MySQL Shell for VS Code to <a
href="adding-a-schema-object-with-mysql-shell-for-vs-code-ui">add
the database schema table</a> automatically creates the
corresponding REST data mapping view containing all
columns of the table in a <strong>flat</strong> JSON
object.</p>
<figure>
<img
src="sections/devGuide/../../images/vsc-mrs-rest-object-editor.svg"
title="REST Object Editor"
alt="JSON Relational Editor" />
<figcaption aria-hidden="true">JSON Relational
Editor</figcaption>
</figure>
<p>Adding the database schema table via VS Code is equal
to calling the <a
href="sql.html#create-rest-view">CREATE REST DATA
MAPPING VIEW</a> MRS DDL statement without a
<code>graphQlObj</code> definition, which also adds all
columns of the table as a <strong>flat</strong> JSON
object.</p>
<div class="sourceCode" id="cb36"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb36-1"><a href="#cb36-1" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">OR</span> <span class="kw">REPLACE</span> REST <span class="kw">VIEW</span> <span class="op">/</span>city</span>
<span id="cb36-2"><a href="#cb36-2" aria-hidden="true" tabindex="-1"></a><span class="kw">AS</span> `sakila`.`city`</span>
<span id="cb36-3"><a href="#cb36-3" aria-hidden="true" tabindex="-1"></a>AUTHENTICATION REQUIRED;</span>
<span id="cb36-4"><a href="#cb36-4" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb36-5"><a href="#cb36-5" aria-hidden="true" tabindex="-1"></a>SHOW <span class="kw">CREATE</span> REST <span class="kw">VIEW</span> <span class="op">/</span>city;</span></code></pre></div>
<div class="sourceCode" id="cb37"><pre
class="sourceCode txt"><code class="sourceCode default"><span id="cb37-1"><a href="#cb37-1" aria-hidden="true" tabindex="-1"></a>+-----------------------------------------------+</span>
<span id="cb37-2"><a href="#cb37-2" aria-hidden="true" tabindex="-1"></a>| CREATE REST VIEW |</span>
<span id="cb37-3"><a href="#cb37-3" aria-hidden="true" tabindex="-1"></a>+-----------------------------------------------+</span>
<span id="cb37-4"><a href="#cb37-4" aria-hidden="true" tabindex="-1"></a>| CREATE OR REPLACE REST VIEW /city |</span>
<span id="cb37-5"><a href="#cb37-5" aria-hidden="true" tabindex="-1"></a>| ON SERVICE /myTestService SCHEMA /sakila |</span>
<span id="cb37-6"><a href="#cb37-6" aria-hidden="true" tabindex="-1"></a>| AS sakila.city { |</span>
<span id="cb37-7"><a href="#cb37-7" aria-hidden="true" tabindex="-1"></a>| cityId: city_id, |</span>
<span id="cb37-8"><a href="#cb37-8" aria-hidden="true" tabindex="-1"></a>| city: city, |</span>
<span id="cb37-9"><a href="#cb37-9" aria-hidden="true" tabindex="-1"></a>| countryId: country_id, |</span>
<span id="cb37-10"><a href="#cb37-10" aria-hidden="true" tabindex="-1"></a>| lastUpdate: last_update |</span>
<span id="cb37-11"><a href="#cb37-11" aria-hidden="true" tabindex="-1"></a>| } |</span>
<span id="cb37-12"><a href="#cb37-12" aria-hidden="true" tabindex="-1"></a>| AUTHENTICATION REQUIRED; |</span>
<span id="cb37-13"><a href="#cb37-13" aria-hidden="true" tabindex="-1"></a>+-----------------------------------------------+</span></code></pre></div>
<blockquote>
<p>Note: In order to be able to access the REST object
without authentication, the <code>Requires Auth</code>
checkbox needs to be unchecked in the MySQL REST Object
dialog or the <code>AUTHENTICATION NOT REQUIRED</code>
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.</p>
</blockquote>
<h4 data-number="6.3.1.1"
id="enabling-crud-operations"><span
class="header-section-number">6.3.1.1</span> Enabling
CRUD Operations</h4>
<p>Since only the <code>READ</code> CRUD operation is
enabled by default (see the <code>R</code> being
highlighted next to the relational object), only read
commands will be allowed on the REST object. To change
this, toggle each <code>CRUD</code> letter
(<code>C</code> - Create, <code>R</code> - Read,
<code>U</code> - Update and <code>D</code> - Delete) to
enable or disable the corresponding functionality in the
MySQL REST Object dialog.</p>
<p>The same can be achieved by using annotations in the
MRS DDL statement.</p>
<div class="sourceCode" id="cb38"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb38-1"><a href="#cb38-1" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">OR</span> <span class="kw">REPLACE</span> REST <span class="kw">VIEW</span> <span class="op">/</span>city</span>
<span id="cb38-2"><a href="#cb38-2" aria-hidden="true" tabindex="-1"></a><span class="kw">AS</span> `sakila`.`city` @INSERT @UPDATE @DELETE</span>
<span id="cb38-3"><a href="#cb38-3" aria-hidden="true" tabindex="-1"></a>AUTHENTICATION REQUIRED;</span></code></pre></div>
<p>The following table shows the mapping between CRUD
operations and SQL operations.</p>
<table>
<caption>CRUD Operation Mapping</caption>
<thead>
<tr class="header">
<th>Letter</th>
<th>CRUD Operation</th>
<th>SQL Operation</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td>C</td>
<td>CREATE</td>
<td>CREATE</td>
</tr>
<tr class="even">
<td>R</td>
<td>READ</td>
<td>SELECT</td>
</tr>
<tr class="odd">
<td>U</td>
<td>UPDATE</td>
<td>UPDATE</td>
</tr>
<tr class="even">
<td>D</td>
<td>DELETE</td>
<td>DELETE</td>
</tr>
</tbody>
</table>
<h3 data-number="6.3.2"
id="creating-a-nested-rest-data-mapping-view"><span
class="header-section-number">6.3.2</span> Creating a
Nested REST Data Mapping View</h3>
<p>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.</p>
<figure>
<img
src="sections/devGuide/../../images/vsc-mrs-rest-object-editor-2-referenced-table.png"
title="Adding a Referenced Table"
alt="Adding a Referenced Table" />
<figcaption aria-hidden="true">Adding a Referenced
Table</figcaption>
</figure>
<p>This leads to the following result.</p>
<div class="sourceCode" id="cb39"><pre
class="sourceCode txt"><code class="sourceCode default"><span id="cb39-1"><a href="#cb39-1" aria-hidden="true" tabindex="-1"></a>GET /myService/sakila/city/1</span></code></pre></div>
<div class="sourceCode" id="cb40"><pre
class="sourceCode json"><code class="sourceCode json"><span id="cb40-1"><a href="#cb40-1" aria-hidden="true" tabindex="-1"></a><span class="fu">{</span></span>
<span id="cb40-2"><a href="#cb40-2" aria-hidden="true" tabindex="-1"></a> <span class="dt">"city"</span><span class="fu">:</span><span class="er"> </span><span class="st">"A Corua (La Corua)"</span><span class="fu">,</span></span>
<span id="cb40-3"><a href="#cb40-3" aria-hidden="true" tabindex="-1"></a> <span class="dt">"links"</span><span class="fu">:</span><span class="er"> </span><span class="ot">[</span></span>
<span id="cb40-4"><a href="#cb40-4" aria-hidden="true" tabindex="-1"></a> <span class="fu">{</span></span>
<span id="cb40-5"><a href="#cb40-5" aria-hidden="true" tabindex="-1"></a> <span class="dt">"rel"</span><span class="fu">:</span><span class="er"> </span><span class="st">"self"</span><span class="fu">,</span></span>
<span id="cb40-6"><a href="#cb40-6" aria-hidden="true" tabindex="-1"></a> <span class="dt">"href"</span><span class="fu">:</span><span class="er"> </span><span class="st">"/myService/sakila/city/1"</span></span>
<span id="cb40-7"><a href="#cb40-7" aria-hidden="true" tabindex="-1"></a> <span class="fu">}</span></span>
<span id="cb40-8"><a href="#cb40-8" aria-hidden="true" tabindex="-1"></a> <span class="ot">]</span><span class="fu">,</span></span>
<span id="cb40-9"><a href="#cb40-9" aria-hidden="true" tabindex="-1"></a> <span class="dt">"cityId"</span><span class="fu">:</span><span class="er"> </span><span class="dv">1</span><span class="fu">,</span></span>
<span id="cb40-10"><a href="#cb40-10" aria-hidden="true" tabindex="-1"></a> <span class="dt">"country"</span><span class="fu">:</span><span class="er"> </span><span class="fu">{</span></span>
<span id="cb40-11"><a href="#cb40-11" aria-hidden="true" tabindex="-1"></a> <span class="dt">"country"</span><span class="fu">:</span><span class="er"> </span><span class="st">"Spain"</span><span class="fu">,</span></span>
<span id="cb40-12"><a href="#cb40-12" aria-hidden="true" tabindex="-1"></a> <span class="dt">"countryId"</span><span class="fu">:</span><span class="er"> </span><span class="dv">87</span><span class="fu">,</span></span>
<span id="cb40-13"><a href="#cb40-13" aria-hidden="true" tabindex="-1"></a> <span class="dt">"lastUpdate"</span><span class="fu">:</span><span class="er"> </span><span class="st">"2006-02-15 04:44:00.000000"</span></span>
<span id="cb40-14"><a href="#cb40-14" aria-hidden="true" tabindex="-1"></a> <span class="fu">},</span></span>
<span id="cb40-15"><a href="#cb40-15" aria-hidden="true" tabindex="-1"></a> <span class="dt">"countryId"</span><span class="fu">:</span><span class="er"> </span><span class="dv">87</span><span class="fu">,</span></span>
<span id="cb40-16"><a href="#cb40-16" aria-hidden="true" tabindex="-1"></a> <span class="dt">"lastUpdate"</span><span class="fu">:</span><span class="er"> </span><span class="st">"2006-02-15 04:45:25.000000"</span><span class="fu">,</span></span>
<span id="cb40-17"><a href="#cb40-17" aria-hidden="true" tabindex="-1"></a> <span class="dt">"_metadata"</span><span class="fu">:</span><span class="er"> </span><span class="fu">{</span></span>
<span id="cb40-18"><a href="#cb40-18" aria-hidden="true" tabindex="-1"></a> <span class="dt">"etag"</span><span class="fu">:</span><span class="er"> </span><span class="st">"FFA2187AD4B98DF48EC40B3E807E0561A71D02C2F4F5A3B953AA6CB6E41CAD16"</span></span>
<span id="cb40-19"><a href="#cb40-19" aria-hidden="true" tabindex="-1"></a> <span class="fu">}</span></span>
<span id="cb40-20"><a href="#cb40-20" aria-hidden="true" tabindex="-1"></a><span class="fu">}</span></span></code></pre></div>
<h3 data-number="6.3.3"
id="creating-a-rest-data-mapping-view-with-an-unnested-referenced-table"><span
class="header-section-number">6.3.3</span> Creating a
REST Data Mapping View with an Unnested Referenced
Table</h3>
<p>If the columns of the referenced table should be
added to the level above, the <code>Unnest...</code>
dropdown can be used to select the column that should be
selected for the reduce operation.</p>
<figure>
<img
src="sections/devGuide/../../images/vsc-mrs-rest-object-editor-4-referenced-table-unnested.png"
title="Unnest a Referenced Table"
alt="Unnest a Referenced Table" />
<figcaption aria-hidden="true">Unnest a Referenced
Table</figcaption>
</figure>
<p>This leads to the following result.</p>
<div class="sourceCode" id="cb41"><pre
class="sourceCode txt"><code class="sourceCode default"><span id="cb41-1"><a href="#cb41-1" aria-hidden="true" tabindex="-1"></a>GET /myService/sakila/city/1</span></code></pre></div>
<div class="sourceCode" id="cb42"><pre
class="sourceCode json"><code class="sourceCode json"><span id="cb42-1"><a href="#cb42-1" aria-hidden="true" tabindex="-1"></a><span class="fu">{</span></span>
<span id="cb42-2"><a href="#cb42-2" aria-hidden="true" tabindex="-1"></a> <span class="dt">"city"</span><span class="fu">:</span><span class="er"> </span><span class="st">"A Corua (La Corua)"</span><span class="fu">,</span></span>
<span id="cb42-3"><a href="#cb42-3" aria-hidden="true" tabindex="-1"></a> <span class="dt">"links"</span><span class="fu">:</span><span class="er"> </span><span class="ot">[</span></span>
<span id="cb42-4"><a href="#cb42-4" aria-hidden="true" tabindex="-1"></a> <span class="fu">{</span></span>
<span id="cb42-5"><a href="#cb42-5" aria-hidden="true" tabindex="-1"></a> <span class="dt">"rel"</span><span class="fu">:</span><span class="er"> </span><span class="st">"self"</span><span class="fu">,</span></span>
<span id="cb42-6"><a href="#cb42-6" aria-hidden="true" tabindex="-1"></a> <span class="dt">"href"</span><span class="fu">:</span><span class="er"> </span><span class="st">"/myService/sakila/city/1"</span></span>
<span id="cb42-7"><a href="#cb42-7" aria-hidden="true" tabindex="-1"></a> <span class="fu">}</span></span>
<span id="cb42-8"><a href="#cb42-8" aria-hidden="true" tabindex="-1"></a> <span class="ot">]</span><span class="fu">,</span></span>
<span id="cb42-9"><a href="#cb42-9" aria-hidden="true" tabindex="-1"></a> <span class="dt">"cityId"</span><span class="fu">:</span><span class="er"> </span><span class="dv">1</span><span class="fu">,</span></span>
<span id="cb42-10"><a href="#cb42-10" aria-hidden="true" tabindex="-1"></a> <span class="dt">"country"</span><span class="fu">:</span><span class="er"> </span><span class="st">"Spain"</span><span class="fu">,</span></span>
<span id="cb42-11"><a href="#cb42-11" aria-hidden="true" tabindex="-1"></a> <span class="dt">"countryId"</span><span class="fu">:</span><span class="er"> </span><span class="dv">87</span><span class="fu">,</span></span>
<span id="cb42-12"><a href="#cb42-12" aria-hidden="true" tabindex="-1"></a> <span class="dt">"lastUpdate"</span><span class="fu">:</span><span class="er"> </span><span class="st">"2006-02-15 04:45:25.000000"</span><span class="fu">,</span></span>
<span id="cb42-13"><a href="#cb42-13" aria-hidden="true" tabindex="-1"></a> <span class="dt">"_metadata"</span><span class="fu">:</span><span class="er"> </span><span class="fu">{</span></span>
<span id="cb42-14"><a href="#cb42-14" aria-hidden="true" tabindex="-1"></a> <span class="dt">"etag"</span><span class="fu">:</span><span class="er"> </span><span class="st">"48889BABCBBA1491D25DFE0D7A270FA3FDF8A16DA8E44E42C61759DE1F0D6E35"</span></span>
<span id="cb42-15"><a href="#cb42-15" aria-hidden="true" tabindex="-1"></a> <span class="fu">}</span></span>
<span id="cb42-16"><a href="#cb42-16" aria-hidden="true" tabindex="-1"></a><span class="fu">}</span></span></code></pre></div>
<h3 data-number="6.3.4"
id="rest-view-object-identifiers"><span
class="header-section-number">6.3.4</span> REST View
Object Identifiers</h3>
<p>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.</p>
<div class="sourceCode" id="cb43"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb43-1"><a href="#cb43-1" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">TABLE</span> <span class="cf">IF</span> <span class="kw">NOT</span> <span class="kw">EXISTS</span> sakila.my_table (id1 <span class="dt">INT</span>, id2 <span class="dt">INT</span>, name <span class="dt">VARCHAR</span>(<span class="dv">3</span>), <span class="kw">PRIMARY</span> <span class="kw">KEY</span> (id1, id2));</span>
<span id="cb43-2"><a href="#cb43-2" aria-hidden="true" tabindex="-1"></a><span class="kw">INSERT</span> <span class="kw">INTO</span> sakila.my_table <span class="kw">VALUES</span> (<span class="dv">1</span>, <span class="dv">1</span>, <span class="ot">"foo"</span>);</span>
<span id="cb43-3"><a href="#cb43-3" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb43-4"><a href="#cb43-4" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">OR</span> <span class="kw">REPLACE</span> REST <span class="kw">VIEW</span> <span class="op">/</span>myTable</span>
<span id="cb43-5"><a href="#cb43-5" aria-hidden="true" tabindex="-1"></a> <span class="kw">AS</span> `sakila`.`my_table` @UPDATE;</span></code></pre></div>
<p>Retrieving the specific REST document can be done as
follows:</p>
<div class="sourceCode" id="cb44"><pre
class="sourceCode txt"><code class="sourceCode default"><span id="cb44-1"><a href="#cb44-1" aria-hidden="true" tabindex="-1"></a>GET /myService/sakila/myTable/1,1</span></code></pre></div>
<div class="sourceCode" id="cb45"><pre
class="sourceCode json"><code class="sourceCode json"><span id="cb45-1"><a href="#cb45-1" aria-hidden="true" tabindex="-1"></a><span class="fu">{</span></span>
<span id="cb45-2"><a href="#cb45-2" aria-hidden="true" tabindex="-1"></a> <span class="dt">"id1"</span><span class="fu">:</span><span class="er"> </span><span class="dv">1</span><span class="fu">,</span></span>
<span id="cb45-3"><a href="#cb45-3" aria-hidden="true" tabindex="-1"></a> <span class="dt">"id2"</span><span class="fu">:</span> <span class="dv">1</span><span class="fu">,</span></span>
<span id="cb45-4"><a href="#cb45-4" aria-hidden="true" tabindex="-1"></a> <span class="dt">"links"</span><span class="fu">:</span><span class="er"> </span><span class="ot">[</span></span>
<span id="cb45-5"><a href="#cb45-5" aria-hidden="true" tabindex="-1"></a> <span class="fu">{</span></span>
<span id="cb45-6"><a href="#cb45-6" aria-hidden="true" tabindex="-1"></a> <span class="dt">"rel"</span><span class="fu">:</span><span class="er"> </span><span class="st">"self"</span><span class="fu">,</span></span>
<span id="cb45-7"><a href="#cb45-7" aria-hidden="true" tabindex="-1"></a> <span class="dt">"href"</span><span class="fu">:</span><span class="er"> </span><span class="st">"/myService/sakila/myTable/1,1"</span></span>
<span id="cb45-8"><a href="#cb45-8" aria-hidden="true" tabindex="-1"></a> <span class="fu">}</span></span>
<span id="cb45-9"><a href="#cb45-9" aria-hidden="true" tabindex="-1"></a> <span class="ot">]</span><span class="fu">,</span></span>
<span id="cb45-10"><a href="#cb45-10" aria-hidden="true" tabindex="-1"></a> <span class="dt">"name"</span><span class="fu">:</span> <span class="st">"foo"</span><span class="fu">,</span></span>
<span id="cb45-11"><a href="#cb45-11" aria-hidden="true" tabindex="-1"></a> <span class="dt">"_metadata"</span><span class="fu">:</span><span class="er"> </span><span class="fu">{</span></span>
<span id="cb45-12"><a href="#cb45-12" aria-hidden="true" tabindex="-1"></a> <span class="dt">"etag"</span><span class="fu">:</span><span class="er"> </span><span class="st">"48819BABCBBA1491DBBDFE0D7A270FA3FDF8A16DA8E44E42C61759DE1F0D6A38"</span></span>
<span id="cb45-13"><a href="#cb45-13" aria-hidden="true" tabindex="-1"></a> <span class="fu">}</span></span>
<span id="cb45-14"><a href="#cb45-14" aria-hidden="true" tabindex="-1"></a><span class="fu">}</span></span></code></pre></div>
<p>Updating a specific REST document can be done as
follows:</p>
<div class="sourceCode" id="cb46"><pre
class="sourceCode txt"><code class="sourceCode default"><span id="cb46-1"><a href="#cb46-1" aria-hidden="true" tabindex="-1"></a>PUT /myService/sakila/myTable/1,1</span>
<span id="cb46-2"><a href="#cb46-2" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb46-3"><a href="#cb46-3" aria-hidden="true" tabindex="-1"></a> "id1": 1,</span>
<span id="cb46-4"><a href="#cb46-4" aria-hidden="true" tabindex="-1"></a> "id2": 2,</span>
<span id="cb46-5"><a href="#cb46-5" aria-hidden="true" tabindex="-1"></a> "name": "bar"</span>
<span id="cb46-6"><a href="#cb46-6" aria-hidden="true" tabindex="-1"></a>}</span></code></pre></div>
<div class="sourceCode" id="cb47"><pre
class="sourceCode json"><code class="sourceCode json"><span id="cb47-1"><a href="#cb47-1" aria-hidden="true" tabindex="-1"></a><span class="fu">{</span></span>
<span id="cb47-2"><a href="#cb47-2" aria-hidden="true" tabindex="-1"></a> <span class="dt">"id1"</span><span class="fu">:</span><span class="er"> </span><span class="dv">1</span><span class="fu">,</span></span>
<span id="cb47-3"><a href="#cb47-3" aria-hidden="true" tabindex="-1"></a> <span class="dt">"id2"</span><span class="fu">:</span> <span class="dv">1</span><span class="fu">,</span></span>
<span id="cb47-4"><a href="#cb47-4" aria-hidden="true" tabindex="-1"></a> <span class="dt">"links"</span><span class="fu">:</span><span class="er"> </span><span class="ot">[</span></span>
<span id="cb47-5"><a href="#cb47-5" aria-hidden="true" tabindex="-1"></a> <span class="fu">{</span></span>
<span id="cb47-6"><a href="#cb47-6" aria-hidden="true" tabindex="-1"></a> <span class="dt">"rel"</span><span class="fu">:</span><span class="er"> </span><span class="st">"self"</span><span class="fu">,</span></span>
<span id="cb47-7"><a href="#cb47-7" aria-hidden="true" tabindex="-1"></a> <span class="dt">"href"</span><span class="fu">:</span><span class="er"> </span><span class="st">"/myService/sakila/myTable/1,1"</span></span>
<span id="cb47-8"><a href="#cb47-8" aria-hidden="true" tabindex="-1"></a> <span class="fu">}</span></span>
<span id="cb47-9"><a href="#cb47-9" aria-hidden="true" tabindex="-1"></a> <span class="ot">]</span><span class="fu">,</span></span>
<span id="cb47-10"><a href="#cb47-10" aria-hidden="true" tabindex="-1"></a> <span class="dt">"name"</span><span class="fu">:</span> <span class="st">"bar"</span><span class="fu">,</span></span>
<span id="cb47-11"><a href="#cb47-11" aria-hidden="true" tabindex="-1"></a> <span class="dt">"_metadata"</span><span class="fu">:</span><span class="er"> </span><span class="fu">{</span></span>
<span id="cb47-12"><a href="#cb47-12" aria-hidden="true" tabindex="-1"></a> <span class="dt">"etag"</span><span class="fu">:</span><span class="er"> </span><span class="st">"48819BABCBBA1491DBBDFE0D7A270FA3FDF8A16DA8E44E4AA62559DE1F0D6A42"</span></span>
<span id="cb47-13"><a href="#cb47-13" aria-hidden="true" tabindex="-1"></a> <span class="fu">}</span></span>
<span id="cb47-14"><a href="#cb47-14" aria-hidden="true" tabindex="-1"></a><span class="fu">}</span></span></code></pre></div>
<p>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 <strong>ALL</strong>
primary key columns of every table used by the database
view, which must also be included in the result set
produced by that view.</p>
<p>For these specific scenarios, users can manually
specify the REST View fields that map to their
corresponding document identifiers.</p>
<div class="sourceCode" id="cb48"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb48-1"><a href="#cb48-1" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">TABLE</span> <span class="cf">IF</span> <span class="kw">NOT</span> <span class="kw">EXISTS</span> sakila.my_table (id1 <span class="dt">INT</span>, id2 <span class="dt">INT</span>, name <span class="dt">VARCHAR</span>(<span class="dv">3</span>));</span>
<span id="cb48-2"><a href="#cb48-2" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb48-3"><a href="#cb48-3" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">OR</span> <span class="kw">REPLACE</span> REST <span class="kw">VIEW</span> <span class="op">/</span>myTable</span>
<span id="cb48-4"><a href="#cb48-4" aria-hidden="true" tabindex="-1"></a><span class="kw">AS</span> `sakila`.`my_table` @UPDATE {</span>
<span id="cb48-5"><a href="#cb48-5" aria-hidden="true" tabindex="-1"></a> id1: id1 @KEY,</span>
<span id="cb48-6"><a href="#cb48-6" aria-hidden="true" tabindex="-1"></a> id2: id2 @KEY,</span>
<span id="cb48-7"><a href="#cb48-7" aria-hidden="true" tabindex="-1"></a> name: name</span>
<span id="cb48-8"><a href="#cb48-8" aria-hidden="true" tabindex="-1"></a>};</span></code></pre></div>
<!-- Copyright (c) 2022, 2026, Oracle and/or its affiliates.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License, version 2.0,
as published by the Free Software Foundation.
This program is designed to work with certain software (including
but not limited to OpenSSL) that is licensed under separate terms, as
designated in a particular file or component or in included license
documentation. The authors of MySQL hereby grant you an additional
permission to link the program and your derivative works with the
separately licensed software that they have either included with
the program or referenced in the documentation.
This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See
the GNU General Public License, version 2.0, for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -->
<h1 data-number="7" id="mrs-dialog-reference"><span
class="header-section-number">7</span> MRS Dialog
Reference</h1>
<p>This section discusses the individual MRS UI dialogs
offered by MySQL Shell for VS Code.</p>
<h2 data-number="7.1" id="mrs-service-dialog"><span
class="header-section-number">7.1</span> MRS Service
Dialog</h2>
<h3 data-number="7.1.1"
id="rest-service-properties"><span
class="header-section-number">7.1.1</span> REST Service
Properties</h3>
<p>Each REST service has a common set of properties.</p>
<table>
<caption>REST Service Properties</caption>
<thead>
<tr class="header">
<th>Option</th>
<th>Description</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td>MRS Service Path</td>
<td>The URL context root of this service</td>
</tr>
<tr class="even">
<td>Comments</td>
<td>Comments to describe this service</td>
</tr>
<tr class="odd">
<td>Host Name</td>
<td>If specified, only requests for this host are
served</td>
</tr>
<tr class="even">
<td>Supported Protocols</td>
<td>The supported protocols (HTTPS by default)</td>
</tr>
<tr class="odd">
<td>Enabled</td>
<td>Specifies if the service is served by MySQL
Router</td>
</tr>
<tr class="even">
<td>Options</td>
<td>Advanced options in JSON format</td>
</tr>
</tbody>
</table>
<h3 data-number="7.1.2"
id="rest-service-advanced-options"><span
class="header-section-number">7.1.2</span> REST Service
Advanced Options</h3>
<p>The following advanced options can be set in JSON
format:</p>
<ul>
<li>headers: Accepts a JSON object with one or more HTTP
header names as key and its setting as value.</li>
<li>http:
<ul>
<li>allowedOrigin: If set to <code>auto</code>, MySQL
Router dynamically sets the header
<code>Access-Control-Allow-Origin</code> to the domain
generating the request. Alternatively, this can be set
to a specific domain <code>https://example.com</code> or
a list of domains (for example,
<code>["https://example.com", "https://example.net"]</code>).</li>
</ul></li>
<li>logging:
<ul>
<li>exceptions: If set to <code>true</code>, exceptions
are logged.</li>
<li>requests:
<ul>
<li>body: If set to <code>true</code>, the full body of
all requests are logged.</li>
<li>headers: If set to <code>true,</code> only the
headers of all requests are logged.</li>
</ul></li>
<li>response:
<ul>
<li>body: If set to <code>true</code>, the full body of
all responses are logged.</li>
<li>headers: If set to <code>true</code>, only the
headers of all responses are logged.</li>
</ul></li>
<li>returnInternalErrorDetails: If set to
<code>true</code>, the cause errors with code 500 are
sent to the client.</li>
<li>includeLinksInResults: If set to `false``, the
results do not include navigation links.</li>
</ul></li>
</ul>
<h4 data-number="7.1.2.1"
id="default-rest-service-options"><span
class="header-section-number">7.1.2.1</span> Default
REST Service Options</h4>
<p>The example that follows shows the options that are
used as default when deploying a new service.</p>
<p>Note: These options are only recommended for
development and must be changed for use in
production.</p>
<p>By setting <code>allowedOrigin</code> to
<code>auto</code> MySQL Router dynamically sets the
header <code>Access-Control-Allow-Origin</code> 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.</p>
<div class="sourceCode" id="cb49"><pre
class="sourceCode json"><code class="sourceCode json"><span id="cb49-1"><a href="#cb49-1" aria-hidden="true" tabindex="-1"></a><span class="fu">{</span></span>
<span id="cb49-2"><a href="#cb49-2" aria-hidden="true" tabindex="-1"></a> <span class="dt">"headers"</span><span class="fu">:</span> <span class="fu">{</span></span>
<span id="cb49-3"><a href="#cb49-3" aria-hidden="true" tabindex="-1"></a> <span class="dt">"Access-Control-Allow-Credentials"</span><span class="fu">:</span> <span class="st">"true"</span><span class="fu">,</span></span>
<span id="cb49-4"><a href="#cb49-4" aria-hidden="true" tabindex="-1"></a> <span class="dt">"Access-Control-Allow-Headers"</span><span class="fu">:</span> <span class="st">"Content-Type, Authorization, X-Requested-With, Origin, X-Auth-Token"</span><span class="fu">,</span></span>
<span id="cb49-5"><a href="#cb49-5" aria-hidden="true" tabindex="-1"></a> <span class="dt">"Access-Control-Allow-Methods"</span><span class="fu">:</span> <span class="st">"GET, POST, PUT, DELETE, OPTIONS"</span></span>
<span id="cb49-6"><a href="#cb49-6" aria-hidden="true" tabindex="-1"></a> <span class="fu">},</span></span>
<span id="cb49-7"><a href="#cb49-7" aria-hidden="true" tabindex="-1"></a> <span class="dt">"http"</span><span class="fu">:</span> <span class="fu">{</span></span>
<span id="cb49-8"><a href="#cb49-8" aria-hidden="true" tabindex="-1"></a> <span class="dt">"allowedOrigin"</span><span class="fu">:</span> <span class="st">"auto"</span></span>
<span id="cb49-9"><a href="#cb49-9" aria-hidden="true" tabindex="-1"></a> <span class="fu">},</span></span>
<span id="cb49-10"><a href="#cb49-10" aria-hidden="true" tabindex="-1"></a> <span class="dt">"logging"</span><span class="fu">:</span> <span class="fu">{</span></span>
<span id="cb49-11"><a href="#cb49-11" aria-hidden="true" tabindex="-1"></a> <span class="dt">"exceptions"</span><span class="fu">:</span> <span class="kw">true</span><span class="fu">,</span></span>
<span id="cb49-12"><a href="#cb49-12" aria-hidden="true" tabindex="-1"></a> <span class="dt">"request"</span><span class="fu">:</span> <span class="fu">{</span></span>
<span id="cb49-13"><a href="#cb49-13" aria-hidden="true" tabindex="-1"></a> <span class="dt">"body"</span><span class="fu">:</span> <span class="kw">true</span><span class="fu">,</span></span>
<span id="cb49-14"><a href="#cb49-14" aria-hidden="true" tabindex="-1"></a> <span class="dt">"headers"</span><span class="fu">:</span> <span class="kw">true</span></span>
<span id="cb49-15"><a href="#cb49-15" aria-hidden="true" tabindex="-1"></a> <span class="fu">},</span></span>
<span id="cb49-16"><a href="#cb49-16" aria-hidden="true" tabindex="-1"></a> <span class="dt">"response"</span><span class="fu">:</span> <span class="fu">{</span></span>
<span id="cb49-17"><a href="#cb49-17" aria-hidden="true" tabindex="-1"></a> <span class="dt">"body"</span><span class="fu">:</span> <span class="kw">true</span><span class="fu">,</span></span>
<span id="cb49-18"><a href="#cb49-18" aria-hidden="true" tabindex="-1"></a> <span class="dt">"headers"</span><span class="fu">:</span> <span class="kw">true</span></span>
<span id="cb49-19"><a href="#cb49-19" aria-hidden="true" tabindex="-1"></a> <span class="fu">}</span></span>
<span id="cb49-20"><a href="#cb49-20" aria-hidden="true" tabindex="-1"></a> <span class="fu">},</span></span>
<span id="cb49-21"><a href="#cb49-21" aria-hidden="true" tabindex="-1"></a> <span class="dt">"returnInternalErrorDetails"</span><span class="fu">:</span> <span class="kw">true</span></span>
<span id="cb49-22"><a href="#cb49-22" aria-hidden="true" tabindex="-1"></a><span class="fu">}</span></span></code></pre></div>
<p>When deploying a REST service in production, the
following settings need to be changed:</p>
<ol type="1">
<li>Change <code>allowedOrigin</code> to the domain, or
domains, the REST service is running on (for example,
<code>"https://mydomain.com"</code> when deploying on a
production server).</li>
<li>Set <code>returnInternalErrorDetails</code> to
<code>false</code>.</li>
<li>Adjust the logging settings as needed.</li>
</ol>
<h2 data-number="7.2" id="mrs-schema-dialog"><span
class="header-section-number">7.2</span> MRS Schema
Dialog</h2>
<h3 data-number="7.2.1"
id="rest-schema-properties"><span
class="header-section-number">7.2.1</span> REST Schema
Properties</h3>
<p>Each REST schema has a common set of properties.</p>
<table>
<caption>REST Schema Properties</caption>
<colgroup>
<col style="width: 37%" />
<col style="width: 62%" />
</colgroup>
<thead>
<tr class="header">
<th>Option</th>
<th>Description</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td>MRS Service Path</td>
<td>The path of the REST service for this REST
schema</td>
</tr>
<tr class="even">
<td>Comments</td>
<td>Comments to describe this MRS schema</td>
</tr>
<tr class="odd">
<td>REST Schema Path</td>
<td>The request path to access the schema (must start
with /)</td>
</tr>
<tr class="even">
<td>Schema Name</td>
<td>The name of the corresponding database schema</td>
</tr>
<tr class="odd">
<td>Items per Page</td>
<td>The default number of items to be returned when
requesting REST objects of this schema</td>
</tr>
<tr class="even">
<td>Enabled</td>
<td>Whether or not the REST objects of this REST schema
are exposed through the REST interface</td>
</tr>
<tr class="odd">
<td>Requires Authentication</td>
<td>Whether or not authentication is required to access
the REST objects of this REST schema</td>
</tr>
<tr class="even">
<td>Options</td>
<td>Additional options in JSON format</td>
</tr>
</tbody>
</table>
<h2 data-number="7.3" id="mrs-object-dialog"><span
class="header-section-number">7.3</span> MRS Object
Dialog</h2>
<p>The following aspects can be set through the
dialog.</p>
<ul>
<li>Basic Settings
<ul>
<li>The database schema object that should be exposed
via MRS</li>
<li>The URL path of how to access the REST object</li>
</ul></li>
<li>Security
<ul>
<li>Whether the object is publicly available or requires
authentication</li>
<li>The CRUD operations that are allowed</li>
<li>The CRUD operations that are allowed on referenced
tables</li>
<li>Whether row ownership should be enforced to enable
row level security</li>
</ul></li>
<li>Data Mapping
<ul>
<li>Which columns of the database schema object should
be exposed and how they should be named</li>
<li>Which referenced tables should be included, either
nested or unnested or reduced to a single field</li>
</ul></li>
</ul>
<figure>
<img
src="sections/devGuide/../../images/vsc-mrs-object-dialog.svg"
title="The MySQL REST Object Dialog"
alt="The MySQL REST Object Dialog" />
<figcaption aria-hidden="true">The MySQL REST Object
Dialog</figcaption>
</figure>
<!-- Copyright (c) 2022, 2026, Oracle and/or its affiliates.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License, version 2.0,
as published by the Free Software Foundation.
This program is designed to work with certain software (including
but not limited to OpenSSL) that is licensed under separate terms, as
designated in a particular file or component or in included license
documentation. The authors of MySQL hereby grant you an additional
permission to link the program and your derivative works with the
separately licensed software that they have either included with
the program or referenced in the documentation.
This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See
the GNU General Public License, version 2.0, for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -->
<h1 data-number="8"
id="authentication-and-authorization"><span
class="header-section-number">8</span> Authentication
and Authorization</h1>
<h2 data-number="8.1" id="overview"><span
class="header-section-number">8.1</span> Overview</h2>
<p>As a HTTP REST service, MRS performs its own
authentication and authorization checks separately from
the MySQL server.</p>
<p>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).</p>
<p>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.</p>
<p>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.</p>
<h3 data-number="8.1.1"
id="mrs-administrative-users"><span
class="header-section-number">8.1.1</span> MRS
Administrative Users</h3>
<p>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 <code>mysqlsh</code>).</p>
<p>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.</p>
<h4 data-number="8.1.1.1"
id="service-administrator-mysql_rest_service_admin"><span
class="header-section-number">8.1.1.1</span> Service
Administrator
(<code>mysql_rest_service_admin</code>)</h4>
<p>A Service Administrator is allowed to:</p>
<ul>
<li>add, manage and remove MRS services</li>
<li>add, manage and remove endpoint schemas</li>
<li>add, manage and remove endpoints to tables, views
and routines to MRS schemas</li>
<li>add, manage and remove authentication apps</li>
<li>add, manage and remove user accounts and roles</li>
<li>add, manage and remove content sets and files
(static files that can be served via HTTP)</li>
<li>manage and monitor MySQL Router status and logs</li>
<li>view the MRS audit log</li>
</ul>
<p>Service administrators must have the
<code>mysql_rest_service_admin</code> MySQL role.</p>
<h4 data-number="8.1.1.2"
id="schema-administrator-mysql_rest_schema_admin"><span
class="header-section-number">8.1.1.2</span> Schema
Administrator
(<code>mysql_rest_schema_admin</code>)</h4>
<p>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</p>
<p>A Schema Administrator is allowed to:</p>
<ul>
<li>add, manage and remove endpoint schemas</li>
<li>add, manage and remove endpoints to tables, views
and routines to MRS schemas</li>
<li>add, manage and remove content sets and files</li>
<li>monitor MySQL Router status and logs</li>
<li>view the MRS audit log</li>
</ul>
<p>Schema administrators must have the
<code>mysql_rest_schema_admin</code> 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. <code>SELECT</code>, <code>INSERT</code>,
<code>UPDATE</code>, <code>DELETE</code> on tables that
will be published and updatable,
<code>WITH GRANT OPTION</code>). These privileges will
automatically be re-granted by MySQL Shell to the
<code>mysql_rest_service_data_provider</code> role, so
that MySQL Router can do whatever is required by the
endpoint configuration.</p>
<h4 data-number="8.1.1.3"
id="developer-mysql_rest_service_dev"><span
class="header-section-number">8.1.1.3</span> Developer
(<code>mysql_rest_service_dev</code>)</h4>
<p>Developers have mostly the same privileges as Schema
Administrators, except they cannot add new schemas to a
service.</p>
<p>Developers are allowed to:</p>
<ul>
<li>use/reference existing endpoints schemas</li>
<li>add, manage and remove endpoints to tables, views
and routines to MRS schemas</li>
<li>add, manage and remove content sets and files</li>
<li>monitor MySQL Router status and logs</li>
<li>view the MRS audit log</li>
</ul>
<p>Developers must have the
<code>mysql_rest_service_dev</code> 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. <code>SELECT</code>,
<code>INSERT</code>, <code>UPDATE</code>,
<code>DELETE</code> on tables that will be published and
updatable, <code>WITH GRANT OPTION</code>). These
privileges will automatically be re-granted by MySQL
Shell to the
<code>mysql_rest_service_data_provider</code> role, so
that MySQL Router can do whatever is required by the
endpoint configuration.</p>
<h3 data-number="8.1.2" id="mrs-service-user"><span
class="header-section-number">8.1.2</span> MRS Service
User</h3>
<p>When bootstrapping MySQL Router for MRS, a MySQL user
account is automatically created for MRS. That account
has these 2 roles granted:</p>
<h4 data-number="8.1.2.1"
id="data-access-mysql_rest_service_data_provider"><span
class="header-section-number">8.1.2.1</span> Data Access
(<code>mysql_rest_service_data_provider</code>)</h4>
<p>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.</p>
<p>The MySQL Shell automatically manages privileges
granted to this role, as DB objects are added as REST
endpoints.</p>
<p>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.</p>
<h4 data-number="8.1.2.2"
id="metadata-access-mysql_rest_service_meta_provider"><span
class="header-section-number">8.1.2.2</span> Metadata
Access
(<code>mysql_rest_service_meta_provider</code>)</h4>
<p>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.</p>
<h2 data-number="8.2"
id="authentication-management"><span
class="header-section-number">8.2</span> Authentication
Management</h2>
<p>MRS currently supports the following authentication
methods.</p>
<h3 data-number="8.2.1"
id="mrs-rest-service-specific-authentication"><span
class="header-section-number">8.2.1</span> MRS REST
Service Specific Authentication</h3>
<p>Authentication is handled my MRS against MRS REST
Service specific accounts. Applications use SCRAM
(Salted Challenge Response Authentication Mechanism) to
securely authenticate a user.</p>
<h3 data-number="8.2.2"
id="mysql-internal-authentication"><span
class="header-section-number">8.2.2</span> MySQL
Internal Authentication</h3>
<p>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.</p>
<p>This authentication method is recommended for
HTTPS-only REST services and is most suitable for
applications that are not exposed publicly.</p>
<h3 data-number="8.2.3" id="oauth2-authentication"><span
class="header-section-number">8.2.3</span> OAuth2
Authentication</h3>
<p>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.</p>
<h4 data-number="8.2.3.1"
id="configuring-oci-oauth2"><span
class="header-section-number">8.2.3.1</span> Configuring
OCI OAuth2</h4>
<p>After logging into the OCI web console, select
<code>Identity & Security</code> and then
<code>Domains</code> from the
<code>Navigation Menu</code> or directly go to <a
href="https://cloud.oracle.com/identity/domains">cloud.oracle.com/identity/domains</a>.</p>
<p>Select the root compartment in the
<code>List scope</code> and click on the
<code>Default</code> domain.</p>
<h5 data-number="8.2.3.1.1"
id="looking-up-the-url-option"><span
class="header-section-number">8.2.3.1.1</span> Looking
Up the URL Option</h5>
<p>After the <code>Default</code> domain has been
opened, take note of the <code>Domain URL</code> on the
<code>Domain information</code> tab. This URL needs to
be provided when creating the REST authentication
app.</p>
<h5 data-number="8.2.3.1.2"
id="creating-an-oci-oauth2-integrated-application"><span
class="header-section-number">8.2.3.1.2</span> Creating
an OCI OAuth2 Integrated Application</h5>
<p>Click on the <code>Integrated applications</code>
link on the left hand side, then click the
<code>Add application</code> button at the top.</p>
<ol type="1">
<li>Select <code>Confidential Application</code> and
confirm by clicking <code>Launch workflow</code>
button.</li>
<li>Set a <code>Name</code> and <code>Description</code>
for your REST application and press
<code>Next</code>.</li>
<li>Choose
<code>Configure this application as a resource server now</code>.
<ul>
<li>Set the <code>Primary audience</code> to
<code>MySQL-REST-Service</code>.</li>
</ul></li>
<li>Choose
<code>Configure this application as a client now</code>
<ul>
<li>In the <code>Authorization</code> section, check the
<code>Client credentials</code> and
<code>Authorization code</code> checkboxes.</li>
<li>Enter the correct <code>Redirect URL</code> using
the format
<code>https://<router-address>/<rest-service>/authentication/login?authApp=<authAppName>&sessionType=<bearer | cookie></code>.
<ul>
<li>Example:
<code>https://rest.example.com/myService/authentication/login?authApp=OCI&sessionType=cookie</code></li>
</ul></li>
<li>Ensure the <code>Client type</code> is set to
<code>Confidential</code>.</li>
<li>In the <code>Allowed operations</code> sections
check the <code>Introspect</code> checkbox.</li>
<li>Turn the <code>Bypass consent</code> on.</li>
<li>Set <code>Client IP address</code> to
<code>Anywhere</code>.</li>
<li>Set <code>Authorized resourced</code> to
<code>All</code>.</li>
</ul></li>
<li>Select to <code>Skip</code> the
<code>Web tier policy</code> and press
<code>Finish</code>.</li>
</ol>
<p>You will be taken to your new
<code>Integrated Application</code>. In the
<code>OAuth configuration / General Information</code>
section the <code>Client ID</code> and the
<code>Client secret</code> are show. These need to be
provided when creating the REST authentication app.</p>
<p>Please see the <a
href="sql.html#create-rest-auth-app">CREATE REST AUTH
APP</a> section how to create a REST authentication app
using the <code>"OCI OAuth2"</code> vendor next.</p>
<h4 data-number="8.2.3.2"
id="configuring-the-redirection-url-of-a-rest-service"><span
class="header-section-number">8.2.3.2</span> Configuring
the Redirection URL of a REST service</h4>
<p>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.</p>
<p>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.</p>
<p>The redirection URL can be set using the <a
href="sql.html#alter-rest-service"><code>ALTER REST SERVICE</code></a>
command. Please see <a
href="sql.html#rest-service-authentication-settings">REST
service authentication settings</a> for more
details.</p>
<p>Alternatively the redirection URL can be set by
opening the REST service dialog and switching to the
<code>Authentication</code> tab sheet.</p>
<h2 data-number="8.3"
id="authorization-management"><span
class="header-section-number">8.3</span> Authorization
Management</h2>
<p>Access to a given REST resource can have several
levels of restrictions when using MRS:</p>
<ul>
<li>Public access - no authorization is needed to access
the REST resource and its data</li>
<li>Full access - after authentication the user has full
access to all data of the REST resource</li>
<li>Limited access - after authentication the user has
only access to a subset of the data of the REST
resource</li>
</ul>
<p>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:</p>
<ul>
<li>User-ownership based - users can see their own
data</li>
<li>Privilege based, managed using roles</li>
<li>User-hierarchy based</li>
<li>Group based</li>
<li>Group-hierarchy based</li>
</ul>
<p>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.</p>
<p>From an endpoint’s perspective, access to REST
resources can be controlled at the following levels:</p>
<ul>
<li>Service</li>
<li>Schema</li>
<li>Object</li>
</ul>
<p>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.</p>
<p>It is possible to grant CREATE, READ, UPDATE and
DELETE privileges at any of these levels.</p>
<h3 data-number="8.3.1" id="mrs-roles"><span
class="header-section-number">8.3.1</span> MRS
Roles</h3>
<p>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.</p>
<p>For example, in a simple blog application that has an
endpoint for <code>/myService/blog/post</code>, we could
have 3 roles:</p>
<ul>
<li><code>reader</code>, who can only read posts;</li>
<li><code>poster</code>, who can create and update
posts, besides reading them and</li>
<li><code>editor</code>, which has the same privileges
as a <code>poster</code>, but can also delete them</li>
</ul>
<p>The following snippet creates these 3 roles and
grants them to three different user.</p>
<div class="sourceCode" id="cb50"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb50-1"><a href="#cb50-1" aria-hidden="true" tabindex="-1"></a># This example assumes a MySQL <span class="kw">database</span> <span class="kw">schema</span> <span class="ot">"blog"</span> <span class="kw">and</span> a <span class="kw">schema</span> <span class="kw">table</span> <span class="ot">"post"</span> has been created <span class="kw">before</span>.</span>
<span id="cb50-2"><a href="#cb50-2" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">SCHEMA</span> <span class="cf">IF</span> <span class="kw">NOT</span> <span class="kw">EXISTS</span> blog;</span>
<span id="cb50-3"><a href="#cb50-3" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">TABLE</span> <span class="cf">IF</span> <span class="kw">NOT</span> <span class="kw">EXISTS</span> blog.post(<span class="kw">id</span> <span class="dt">INT</span> <span class="kw">PRIMARY</span> <span class="kw">KEY</span> AUTO_INCREMENT, message TEXT);</span>
<span id="cb50-4"><a href="#cb50-4" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb50-5"><a href="#cb50-5" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> REST SERVICE <span class="op">/</span>myTestService;</span>
<span id="cb50-6"><a href="#cb50-6" aria-hidden="true" tabindex="-1"></a><span class="kw">USE</span> REST SERVICE <span class="op">/</span>myTestService;</span>
<span id="cb50-7"><a href="#cb50-7" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb50-8"><a href="#cb50-8" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> REST <span class="kw">SCHEMA</span> <span class="op">/</span>blog <span class="kw">FROM</span> blog;</span>
<span id="cb50-9"><a href="#cb50-9" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> REST <span class="kw">VIEW</span> <span class="op">/</span>post <span class="kw">ON</span> <span class="kw">SCHEMA</span> <span class="op">/</span>blog <span class="kw">AS</span> blog.post;</span>
<span id="cb50-10"><a href="#cb50-10" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb50-11"><a href="#cb50-11" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> REST <span class="kw">ROLE</span> <span class="ot">"reader"</span>;</span>
<span id="cb50-12"><a href="#cb50-12" aria-hidden="true" tabindex="-1"></a><span class="kw">GRANT</span> REST <span class="kw">READ</span> <span class="kw">ON</span> <span class="kw">SCHEMA</span> <span class="op">/</span>blog <span class="dt">OBJECT</span> <span class="op">/</span>post <span class="kw">TO</span> <span class="ot">"reader"</span>;</span>
<span id="cb50-13"><a href="#cb50-13" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb50-14"><a href="#cb50-14" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> REST <span class="kw">ROLE</span> <span class="ot">"poster"</span> <span class="kw">EXTENDS</span> <span class="ot">"reader"</span>;</span>
<span id="cb50-15"><a href="#cb50-15" aria-hidden="true" tabindex="-1"></a><span class="kw">GRANT</span> REST <span class="kw">CREATE</span>, <span class="kw">UPDATE</span> <span class="kw">ON</span> <span class="kw">SCHEMA</span> <span class="op">/</span>blog <span class="dt">OBJECT</span> <span class="op">/</span>post <span class="kw">TO</span> <span class="ot">"poster"</span>;</span>
<span id="cb50-16"><a href="#cb50-16" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb50-17"><a href="#cb50-17" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> REST <span class="kw">ROLE</span> <span class="ot">"editor"</span> <span class="kw">EXTENDS</span> <span class="ot">"poster"</span>;</span>
<span id="cb50-18"><a href="#cb50-18" aria-hidden="true" tabindex="-1"></a><span class="kw">GRANT</span> REST <span class="kw">DELETE</span> <span class="kw">ON</span> <span class="kw">SCHEMA</span> <span class="op">/</span>blog <span class="dt">OBJECT</span> <span class="op">/</span>post <span class="kw">TO</span> <span class="ot">"editor"</span>;</span>
<span id="cb50-19"><a href="#cb50-19" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb50-20"><a href="#cb50-20" aria-hidden="true" tabindex="-1"></a>SHOW REST <span class="kw">ROLES</span>;</span>
<span id="cb50-21"><a href="#cb50-21" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb50-22"><a href="#cb50-22" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> REST AUTH APP <span class="ot">"TestAuthApp"</span> VENDOR MRS;</span>
<span id="cb50-23"><a href="#cb50-23" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb50-24"><a href="#cb50-24" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> REST <span class="fu">USER</span> <span class="ot">"ulf"</span>@<span class="ot">"TestAuthApp"</span> <span class="kw">IDENTIFIED</span> <span class="kw">BY</span> <span class="ot">"********"</span>;</span>
<span id="cb50-25"><a href="#cb50-25" aria-hidden="true" tabindex="-1"></a><span class="kw">GRANT</span> REST <span class="kw">ROLE</span> <span class="ot">"reader"</span> <span class="kw">TO</span> <span class="ot">"ulf"</span>@<span class="ot">"TestAuthApp"</span>;</span>
<span id="cb50-26"><a href="#cb50-26" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb50-27"><a href="#cb50-27" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> REST <span class="fu">USER</span> <span class="ot">"alfredo"</span>@<span class="ot">"TestAuthApp"</span> <span class="kw">IDENTIFIED</span> <span class="kw">BY</span> <span class="ot">"********"</span>;</span>
<span id="cb50-28"><a href="#cb50-28" aria-hidden="true" tabindex="-1"></a><span class="kw">GRANT</span> REST <span class="kw">ROLE</span> <span class="ot">"poster"</span> <span class="kw">TO</span> <span class="ot">"alfredo"</span>@<span class="ot">"TestAuthApp"</span>;</span>
<span id="cb50-29"><a href="#cb50-29" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb50-30"><a href="#cb50-30" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> REST <span class="fu">USER</span> <span class="ot">"mike"</span>@<span class="ot">"TestAuthApp"</span> <span class="kw">IDENTIFIED</span> <span class="kw">BY</span> <span class="ot">"********"</span>;</span>
<span id="cb50-31"><a href="#cb50-31" aria-hidden="true" tabindex="-1"></a><span class="kw">GRANT</span> REST <span class="kw">ROLE</span> <span class="ot">"editor"</span> <span class="kw">TO</span> <span class="ot">"mike"</span>@<span class="ot">"TestAuthApp"</span>;</span>
<span id="cb50-32"><a href="#cb50-32" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb50-33"><a href="#cb50-33" aria-hidden="true" tabindex="-1"></a># Now, these three users can login <span class="kw">with</span> <span class="kw">the</span> specified <span class="kw">password</span> via MRS authentication.</span></code></pre></div>
<p>By default, roles are specific to a service. The
service to which a role belongs to can be specified
directly in the <code>CREATE REST ROLE</code> 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:</p>
<div class="sourceCode" id="cb51"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb51-1"><a href="#cb51-1" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> REST <span class="kw">ROLE</span> <span class="ot">"myrole"</span> <span class="kw">ON</span> SERVICE <span class="op">/</span>myOtherService;</span>
<span id="cb51-2"><a href="#cb51-2" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb51-3"><a href="#cb51-3" aria-hidden="true" tabindex="-1"></a># <span class="kw">The</span> <span class="kw">role</span> <span class="kw">is</span> created <span class="kw">in</span> service <span class="op">/</span>myTestService, which <span class="kw">is</span> <span class="kw">the</span> <span class="kw">current</span> <span class="kw">default</span></span>
<span id="cb51-4"><a href="#cb51-4" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> REST <span class="kw">ROLE</span> <span class="ot">"myrole"</span>;</span>
<span id="cb51-5"><a href="#cb51-5" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb51-6"><a href="#cb51-6" aria-hidden="true" tabindex="-1"></a>SHOW <span class="kw">CREATE</span> REST <span class="kw">ROLE</span> <span class="ot">"myrole"</span> <span class="kw">ON</span> SERVICE <span class="op">/</span>myTestService;</span>
<span id="cb51-7"><a href="#cb51-7" aria-hidden="true" tabindex="-1"></a>SHOW <span class="kw">CREATE</span> REST <span class="kw">ROLE</span> <span class="ot">"myrole"</span> <span class="kw">ON</span> SERVICE <span class="op">/</span>myOtherService;</span></code></pre></div>
<p>It is also possible to create roles that can be used
from any service, by specifying the
<code>ON ANY SERVICE</code> clause:</p>
<div class="sourceCode" id="cb52"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb52-1"><a href="#cb52-1" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> REST <span class="kw">ROLE</span> <span class="ot">"globalRole"</span> <span class="kw">ON</span> <span class="kw">ANY</span> SERVICE;</span></code></pre></div>
<!-- Copyright (c) 2022, 2026, Oracle and/or its affiliates.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License, version 2.0,
as published by the Free Software Foundation.
This program is designed to work with certain software (including
but not limited to OpenSSL) that is licensed under separate terms, as
designated in a particular file or component or in included license
documentation. The authors of MySQL hereby grant you an additional
permission to link the program and your derivative works with the
separately licensed software that they have either included with
the program or referenced in the documentation.
This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See
the GNU General Public License, version 2.0, for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -->
<h1 data-number="9" id="mrs-examples"><span
class="header-section-number">9</span> MRS Examples</h1>
<p>The MRS Shell Plugin ships with a set of example
projects that showcase the possibilities of the MySQL
REST Service.</p>
<p>The MRS Notes example implements as simple <a
href="https://en.wikipedia.org/wiki/Progressive_web_app">Progressive
Web Apps (PWA)</a> to showcase the features offered by
MRS.</p>
<p>The MRS Scripts example includes a set of examples
that showcase server side rendering of HTML pages.</p>
<!-- Copyright (c) 2022, 2026, Oracle and/or its affiliates.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License, version 2.0,
as published by the Free Software Foundation.
This program is designed to work with certain software (including
but not limited to OpenSSL) that is licensed under separate terms, as
designated in a particular file or component or in included license
documentation. The authors of MySQL hereby grant you an additional
permission to link the program and your derivative works with the
separately licensed software that they have either included with
the program or referenced in the documentation.
This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See
the GNU General Public License, version 2.0, for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -->
<h2 data-number="9.1" id="mrs-notes-example"><span
class="header-section-number">9.1</span> MRS Notes
Example</h2>
<p>The MRS Notes example implements a simple note taking
application as a <a
href="https://en.wikipedia.org/wiki/Progressive_web_app">Progressive
Web Apps (PWA)</a> that allows for sharing notes between
users.</p>
<figure>
<img
src="./images/mrs-notes-ss-phone.png"
title="mrsNotes App running on a Mobile"
alt="mrsNotes App running on a Mobile" />
<figcaption aria-hidden="true">mrsNotes App running on a
Mobile</figcaption>
</figure>
<h3 data-number="9.1.1"
id="mrs-notes-developer-showcase"><span
class="header-section-number">9.1.1</span> MRS Notes
Developer Showcase</h3>
<p>The following features are showcased in this
example.</p>
<ul>
<li>Accessing MRS REST endpoints from JavaScript and
TypeScript code.</li>
<li>Using MRS service authentication REST endpoints to
support user management</li>
<li>Using <a href="https://jwt.io/">JSON Web Tokens
(JWT)</a> to manage user sessions</li>
</ul>
<h3 data-number="9.1.2" id="mrs-notes-quick-guide"><span
class="header-section-number">9.1.2</span> MRS Notes
Quick Guide</h3>
<p>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.</p>
<p>The following steps need to be taken to setup, build
and deploy the MRS Notes example project on the MySQL
REST Service.</p>
<ol type="1">
<li>Save the MRS Notes Example project to disk and open
it with VS Code
<code>VSCodeProject:examples/mrs_notes</code></li>
<li><a
href="#configuring-mysql-rest-service">Configure</a> the
MySQL REST Service.</li>
<li>Create a new MRS service
(e.g. <code>/myService</code>).</li>
<li>Deploy the mrs_notes MySQL database schema
<code>examples/mrs_notes/db_schema/mrs_notes.sql</code></li>
<li>Load the MRS schema dump into the MRS service
<code>examples/mrs_notes/mrs_schema/mrsNotes.mrs.json</code></li>
<li>Ensure a bootstrapped MySQL Router instance is
running (if not, start it).</li>
<li>Build and deploy the app by following the steps
below.</li>
</ol>
<h4 data-number="9.1.2.1"
id="deploying-the-typescript-example"><span
class="header-section-number">9.1.2.1</span> Deploying
the TypeScript Example</h4>
<p>The MrsNotes project implements a TypeScript demo app
that allows to create, manipulate and share notes
between users.</p>
<ol type="1">
<li>If you have not done so in the previous section,
save the following project to disk and open it with VS
Code <code>VSCodeProject:examples/mrs_notes</code></li>
<li>After the project folder has been opened in VS Code,
navigate to the <code>NPM SCRIPTS</code> View in the
sidebar and right-click on <code>package.json</code> to
select <code>Run Install</code>. Alternatively, set the
focus to the TERMINAL tab and enter
<code>npm install</code> to install the required node
modules</li>
<li>In the <code>NPM SCRIPTS</code> View, run the
<code>package.json/build</code> command that will create
a folder called <code>dist</code> that contains all
files needed for deployment.</li>
<li>Right click on the <code>dist</code> folder in the
Folders view and select
<code>Upload Folder to MySQL REST Service</code> from
the popup menu.</li>
<li>In the REST Content Set dialog set the
<code>Request Path</code> the app should be using,
e.g. <code>/app</code> and click <code>OK</code> to
upload the files to the MRS service.</li>
<li>Open a web browser and access the full path
specified in the previous step to open the app,
e.g. <code>https://localhost:8443/myService/app/index.html</code></li>
</ol>
<h3 data-number="9.1.3"
id="mrs-setup-and-configuration-for-the-mrs-notes-examples"><span
class="header-section-number">9.1.3</span> MRS Setup and
Configuration for the MRS Notes Examples</h3>
<p>Please refer to the MRS documentation on how to setup
and configure a MRS service in detail.</p>
<p>If you are using a local MRS deployment deployment
you can use these simplified steps.</p>
<h3 data-number="9.1.4"
id="deploy-the-mrsnotes-mysql-database-schema"><span
class="header-section-number">9.1.4</span> Deploy the
mrsNotes MySQL database schema</h3>
<p>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.</p>
<p>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.</p>
<ul>
<li><p>If you are browsing this documentation within VS
Code click the button next to the SQL script name
<code>examples/mrs_notes/db_schema/mrs_notes.sql</code></p></li>
<li><p>If you want to use MySQL Shell on the command
line, switch to the mrs_notes plugin directory and run
the following command.</p>
<p>mysqlsh dba@localhost –sql -f
examples/mrs_notes/db_schema/mrs_notes.sql</p></li>
</ul>
<h4 data-number="9.1.4.1"
id="mrsnotes-eer-diagram"><span
class="header-section-number">9.1.4.1</span> mrsNotes
EER Diagram</h4>
<p>The following diagram shows all components of the
mrsNotes schema.</p>
<figure>
<img
src="./images/examples-mrs_notes_schema.svg"
title="mrsNotes MySQL Database Schema"
alt="mrsNotes MySQL Database Schema" />
<figcaption aria-hidden="true">mrsNotes MySQL Database
Schema</figcaption>
</figure>
<p>The most important database table is the
<code>note</code> table. It stores all notes that are
created by the users.</p>
<p>The <code>user</code> table holds the nickname of the
user as well as the email address used for receiving
invitation emails for shared notes.</p>
<p>The <code>user_has_note</code> table is used to
managed the sharing of notes with other users.</p>
<p>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.</p>
<p>In this case the layer consists of one VIEW and four
STORED PROCEDUREs.</p>
<ul>
<li>notes_all … a VIEW of all notes the user is allowed
to see.</li>
<li>note_share … a STORED PROCEDURE to share a note with
another user.</li>
<li>note_accept_share … a STORED PROCEDURE to accept a
shared note.</li>
<li>note_update … a STORED PROCEDURE to update a shared
note</li>
<li>note_delete … a STORED PROCEDURE to delete a shared
note</li>
</ul>
<!-- Copyright (c) 2024, 2026, Oracle and/or its affiliates.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License, version 2.0,
as published by the Free Software Foundation.
This program is designed to work with certain software (including
but not limited to OpenSSL) that is licensed under separate terms, as
designated in a particular file or component or in included license
documentation. The authors of MySQL hereby grant you an additional
permission to link the program and your derivative works with the
separately licensed software that they have either included with
the program or referenced in the documentation.
This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See
the GNU General Public License, version 2.0, for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -->
<h2 data-number="9.2" id="mrs-scripts-example"><span
class="header-section-number">9.2</span> MRS Scripts
Example</h2>
<p>The MRS Scripts Example project implements a set of
simple MRS scripts, including two examples of how to
perform Server Side Rendering with MRS.</p>
<h3 data-number="9.2.1"
id="mrs-scripts-example-quick-guide"><span
class="header-section-number">9.2.1</span> MRS Scripts
Example Quick Guide</h3>
<p>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.</p>
<p>The following steps need to be taken to setup, build
and deploy the MRS Scripts Example project on the MySQL
REST Service.</p>
<ol type="1">
<li>Save the MRS Notes Example project to disk and open
it with VS Code
<code>VSCodeProject:examples/mrs_scripts</code></li>
<li><a
href="#configuring-mysql-rest-service">Configure</a> the
MySQL REST Service.</li>
<li>Create a new MRS service
(e.g. <code>/myService</code>).</li>
<li>Ensure a bootstrapped MySQL Router instance is
running (if not, start it).</li>
<li>Build and deploy the MRS Scripts by following the
steps below.</li>
</ol>
<h4 data-number="9.2.1.1"
id="deploying-the-mrs-scripts-examples"><span
class="header-section-number">9.2.1.1</span> Deploying
the MRS Scripts Examples</h4>
<p>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.</p>
<ol type="1">
<li>If you have not done so in the previous section,
save the following project to disk and open it with VS
Code
<code>VSCodeProject:examples/mrs_scripts</code></li>
<li>After the project folder has been opened in VS Code,
navigate to the <code>NPM SCRIPTS</code> View in the
sidebar and right-click on <code>package.json</code> to
select <code>Run Install</code>. Alternatively, set the
focus to the TERMINAL tab and enter
<code>npm install</code> to install the required node
modules</li>
<li>In the <code>NPM SCRIPTS</code> View, run the
<code>package.json/build</code> command that will create
a folder called <code>build</code> that contains all
files needed for deployment.</li>
<li>Right click on the background below the last file in
the Folders view and select
<code>Upload Folder to MySQL REST Service</code> from
the popup menu.</li>
<li>In the REST Content Set dialog make sure that the
<code>Enable MRS Scripts</code> checkbox is checked and
click <code>OK</code> to upload the files to the MRS
service.</li>
<li>Open a web browser and access the full path
specified in the previous step to open the app,
e.g. <code>https://localhost:8443/myService/testScripts/preactTestPage.html</code></li>
</ol>
<h4 data-number="9.2.1.2"
id="using-mysql-shell-to-deploy-the-mrs-scripts-examples"><span
class="header-section-number">9.2.1.2</span> Using MySQL
Shell to Deploy the MRS Scripts Examples</h4>
<p>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.</p>
<div class="sourceCode" id="cb53"><pre
class="sourceCode bash"><code class="sourceCode bash"><span id="cb53-1"><a href="#cb53-1" aria-hidden="true" tabindex="-1"></a><span class="ex">~/.mysqlsh-gui/mysqlsh</span> dba@localhost <span class="at">--sql</span> <span class="at">-e</span> <span class="st">'CREATE OR REPLACE REST CONTENT SET /mrsScriptsContent ON SERVICE /myService FROM "~/path_to_project_folder/mrs_scripts" LOAD SCRIPTS'</span></span></code></pre></div>
<p>Copyright (c) 2022, 2025, Oracle and/or its
affiliates.</p>
</div>
</div>
</div>
</body>
</html>