This document explains how to work with the MRS Software Development Kit and discusses the Client API.

Chapter Overview

Please also see

MySQL REST Service - Developer’s Guide - This book explains how to install and configure the MySQL REST Service (MRS) as well as how to access the data through REST calls.
MySQL REST Service - Core REST APIs - This book provides examples of using the MySQL REST Service queries and other operations against tables and views after you have REST-enabled them.
MySQL REST Service - SQL Reference - This book discusses the MySQL REST Service SQL Extension.

1 Introduction to the MRS SDK

The MySQL REST Service offers a Software Development Kit (SDK) that simplifies the process of writing client applications and interacting with the REST service.

The SDK features a Client API that is specifically generated for each MRS REST Service and therefore offers the best possible support for each REST project.

Currently, support for TypeScript is available. Support for other languages is planned.

1.1 Generation of SDK Files

1.1.1 On the Fly Generation of TypeScript SDK

The MySQL Shell for VS Code extension allows interactive execution of TypeScript code inside a DB Notebook. To make working with the MySQL REST Service easier, the TypeScript SDK for the current REST Service is made available directly within the DB Notebooks.

Whenever a REST DB Object has being edited the TypeScript SDK is updated to allow instant prototyping of REST queries using the Client API.

This allows for adjusting and fine tuning the REST DB Objects until they exactly meet the developer’s requirements and to prototype Client API calls for a development project.

1.1.2 Generating the SDK files for a Development Project

To generate the SDK files for a development project, right click on the MRS Service and select Export REST Service SDK Files .... This will allow you to select a destination folder inside your development project the files will be placed in.

The following files will be placed in the selected folder.

TypeScript

MrsBaseClasses.ts - A file that contains the MRS base classes used by the Client API
<RestServiceName>.ts - The Client API for the specific REST Service using the service’s name.
config.json - A configuration file used for the SDK generation

To start using the Client API import the <RestServiceName>.ts file in your project.

2 Querying Data Across Tables

MySQL supports foreign keys, which permit cross-referencing related data across tables, and foreign key constraints to help keep the related data consistent.

A foreign key relationship involves a parent table that holds the initial column values, and a child table with column values that reference the parent column values. A foreign key constraint is defined on the child table. Foreign keys enable establishing one-to-one, one-to-many or many-to-many relationships between rows in those tables.

With the MySQL REST Service, these relationships can be expanded to include related data from different tables embedded in the same result set with the JSON Relational duality feature available for each MRS database object. The client can then select which columns should be expanded using a specific HTTP query syntax to specify and navigate along the nesting path of columns on other tables that are referenced by a root column in the main (or parent) table.

A key feature of the MRS SDK is the ability to query these relations between two database objects and include or exclude specific columns from the query response.

This feature is available using the select option in the following API commands:

findFirst()
findMany()
findUnique()

By default, all the object fields (expanded or not) and their values are returned in the query response. Specific fields can be excluded from the query response using a plain object format in which the properties are the names of the fields to exclude and each value is false.

With a setup using the Sakila Sample Database where the schema is available under a REST service called myService and the relationship between the city and country tables (one-to-one) is expanded via the JSON/Relational duality feature, the lastUpdate and country.lastUpdate fields can be excluded as follows:

myService.sakila.city.findFirst({ select: { lastUpdate: false, country: { lastUpdate: false } } })
{
  "city": "A Coruña (La Coruña)",
  "links": [
    {
      "rel": "self",
      "href": "/myService/sakila/city/1"
    }
  ],
  "cityId": 1,
  "country": {
    "country": "Spain",
    "countryId": 87
  },
  "countryId": 87
}

In the same way, if the relationship between the actor and film tables (many-to-many) is expanded, the following command excludes the identifiers on each nested object:

myService.sakila.actor.findFirst({ select: { filmActor: { actorId: false, film: { filmId: false, languageId: false, originalLanguageId: false } } } })
{
  {
  "links": [
    {
      "rel": "self",
      "href": "/myService/sakila/actor/58"
    }
  ],
  "actorId": 58,
  "lastName": "AKROYD",
  "filmActor": [
    {
      "film": {
        "title": "BACKLASH UNDEFEATED",
        "length": 118,
        "rating": "PG-13",
        "lastUpdate": "2006-02-15 05:03:42.000000",
        "rentalRate": 4.99,
        "description": "A Stunning Character Study of a Mad Scientist And a Mad Cow who must Kill a Car in A Monastery",
        "releaseYear": 2006,
        "rentalDuration": 3,
        "replacementCost": 24.99,
        "specialFeatures": "Trailers,Behind the Scenes"
      },
      "filmId": 48,
      "lastUpdate": "2006-02-15 05:05:03.000000"
    },
    // ...
  ],
  "firstName": "CHRISTIAN",
  "lastUpdate": "2006-02-15 04:34:33.000000"
}

On the other hand, fields can be cherry-picked and included in the query response by using either the same object format and setting the value to true, or alternatively, using a list of field names to include.

In the same way, this is possible for one-to-one relationships:

myService.sakila.city.findFirst({ select: { city: true, country: { country: true } } })
{
  "city": "A Coruña (La Coruña)",
  "links": [
    {
      "rel": "self",
      "href": "/myService/sakila/city/1"
    }
  ],
  "country": {
    "country": "Spain",
  }
}

And also for many-to-many relationships:

myService.sakila.actor.findFirst({ select: ['filmActor.film.title'] })
{
  {
  "links": [
    {
      "rel": "self",
      "href": "/myService/sakila/actor/58"
    }
  ],
  "filmActor": [
    {
      "film": {
        "title": "BACKLASH UNDEFEATED"
      }
    },
    {
      "film": {
        "title": "BETRAYED REAR"
      }
    }
    // ...
  ]
}

3 Checking for NULL Column Values

MySQL supports NOT NULL constraints which ensure that the value in a given column cannot be NULL. By omission though, a column can hold NULL values. With the MySQL REST Service, records containing columns with NULL values can be included in or excluded from the result set using the $null or $notnull operators.

The TypeScript MRS SDK provides a special syntax for filtering records in a result set by a given field when it contains (or not) a NULL value. With a setup using the Sakila Sample Database where the schema is available under a REST service called myService, filtering records by NULL column values can be done as follows:

myService.sakila.address.findMany({ select: ["address", "address2"], where: { address2: null } })
{
  "items": [
    {
      "links": [
        {
          "rel": "self",
          "href": "/myService/sakila/address/1"
        }
      ],
      "address": "47 MySakila Drive",
      "address2": null,
      "_metadata": {
        "etag": "44EA44E1541A6A0A24135C4CC4F30E52AA2B4256181DE9BC1960C78A35F33B27"
      }
    },
    {
      "links": [
        {
          "rel": "self",
          "href": "/myService/sakila/address/2"
        }
      ],
      "address": "28 MySQL Boulevard",
      "address2": null,
      "_metadata": {
        "etag": "C5C68338EBF92980E1B8FDAE3FE7E3CE9507C4169C3DEC1BDB4E9AF2D961E00D"
      }
    },
    {
      "links": [
        {
          "rel": "self",
          "href": "/myService/sakila/address/3"
        }
      ],
      "address": "23 Workhaven Lane",
      "address2": null,
      "_metadata": {
        "etag": "7EF99DD02DF9071C8946B6180E74EB11D6B47FDD03A36C9B44B920F2A8D3684B"
      }
    },
    {
      "links": [
        {
          "rel": "self",
          "href": "/myService/sakila/address/4"
        }
      ],
      "address": "1411 Lillydale Drive",
      "address2": null,
      "_metadata": {
        "etag": "5F4F5E570F2AF2BB5E5A7AE41548CE4965F715F7C040A80B42D0DB79BB57336B"
      }
    }
  ],
  "limit": 25,
  "offset": 0,
  "hasMore": false,
  "count": 4,
  "links": [
    {
      "rel": "self",
      "href": "/myService/sakila/address/"
    }
  ]
}

In the same way, filtering records where a given column does not contain NULL can be done as follows:

myService.sakila.actor.findFirst({ select: ["address", "address2"], where: { address2: { not: null } } })
{
  "links": [
    {
      "rel": "self",
      "href": "/myService/sakila/address/5"
    }
  ],
  "address": "1913 Hanoi Way",
  "address2": "",
  "_metadata": {
    "etag": "689439C1F6D1F101E9A146F8DE244F01F0CE40AEBFA92AE5CEABA119F9C1573E"
  }
}

Attempting to apply such a filter to a field that maps to a column with a NOT NULL constraint should yield a TypeScript compilation error:

myService.sakila.actor.findFirst({ where: { address: null } })

Type 'null' is not assignable to type 'string | DataFilterField<IMyServiceSakilaAddressParams, string | undefined> | ComparisonOpExpr<string | undefined>[] | undefined'.

4 Working with Spatial Data Types

MySQL supports an extended SQL environment, based on the conventions established by the OpenGIS Geometry Model, that enables a set of spatial column data types to hold geometry values. Some of them hold single values:

GEOMETRY
POINT
LINESTRING
POLYGON

GEOMETRY can store geometry values of any type. The other single-value types (POINT, LINESTRING, and POLYGON) restrict their values to a particular geometry type.

On the other hand, there are spatial data types that are meant to hold collections of geometry values:

MULTIPOINT
MULTILINESTRING
MULTIPOLYGON
GEOMETRYCOLLECTION

GEOMETRYCOLLECTION can store a collection of objects of any type. The other collection types (MULTIPOINT, MULTILINESTRING, and MULTIPOLYGON) restrict collection members to those having a particular geometry type.

MySQL Rest Service SDK supports two formats for representing, operating on, or manipulating spatial data:

Well-Known Text (WKT)
GeoJSON

Both these formats can be used when inserting or updating a record that contains a field matching a column of a Spatial data type.

For instance, with a setup using the Sakila Sample Database where the schema is available under a REST service called myService, when inserting records into the address table, you can specify the value for the location column, which has a generic GEOMETRY data type, as follows:

// WKT
myService.sakila.address.create({ data: { location: "Point(11.11 12.22)" }})

myService.sakila.address.createMany([{
  data: {
    location: "Point(0 0)"
  }
}, {
  data: {
    location: "Point(11.11 12.22)"
  }
}])

// GeoJSON
myService.sakila.address.create({ data: {
  location: {
    type: "Point",
    coordinates: [11.11, 12.22]
  }
}})

The same convention should also apply when updating records on the same table.

// WKT
myService.sakila.address.update({
  where: {
    address_id: 1
  }
  data: {
    location: "Point(11.11 12.22)"
  }
})

myService.sakila.address.updateMany({
  where: [{
    address_id: 1
  }, {
    address_id: 2
  }],
  data: {
    location: "Point(11.11 12.22)"
  }
})

// GeoJSON
myService.sakila.address.update({
  where: {
    address_id: 1
  }
  data: {
    location: {
      type: "Point",
      coordinates: [11.11, 12.22]
    }
  }
})

myService.sakila.address.updateMany({
  where: [{
    address_id: 1
  }, {
    address_id: 2
  }],
  data: {
    location: {
      type: "Point",
      coordinates: [11.11, 12.22]
    }
  }
})

If the column has a narrow data type such as POINT, instead of the more generic GEOMETRY, specifying an incompatible type on the client-side, should yield a compilation error. For example, assuming a table mrs_tests.spatial_tests created as follows:

CREATE DATABASE IF NOT EXISTS mrs_tests;
CREATE TABLE IF NOT EXISTS mrs_tests.spatial_tests (id INT AUTO_INCREMENT NOT NULL, ls LINESTRING, PRIMARY KEY (id));

With the table (and corresponding schema) available from the same myService REST service, trying to insert a POINT does not work, because the column only accepts a LINESTRING.

myService.mrsTests.spatialTests.create({
  data: {
    ls: {
      type: "Point",
      coordinates: [0, 0]
    }
  }
})

A command like the one above yields a compilation error.

Type ‘Point’ is not assignable to type ‘LineString’.

In the same way, trying to insert or update multiple values for a single field when the column data type only allows a single value, or vice-versa, should also yield a compilation error. For, example, assuming the mrs_tests.spatial_tests table was created as follows:

CREATE TABLE IF NOT EXISTS mrs_tests.wl15912 (id INT AUTO_INCREMENT NOT NULL, ls GEOMETRYCOLLECTION, PRIMARY KEY (id));

Trying to insert a POINT does not work, because the column only accepts either a MULTIPOINT, a MULTILINESTRING or a MULTIPOLYGON.

myService.mrsTests.spatialTests.create({
  data: {
    ls: {
      type: "Point",
      coordinates: [0, 0]
    }
  }
})

In this case, the command yields the following compilation error:

Type ‘Point’ is not assignable to type ‘MultiPoint | MultiLineString | MultiPolygon’.

5 TypeScript Client API Reference

5.1 create

create is used to insert a record in a given table. The record is represented as a plain TypeScript/JavaScript object or, alternatively, as an instance of a particular class that encapsulates the data required to create a new record. To insert multiple records, see createMany[#createMany].

5.1.1 Options (create)

Name	Type	Required	Description
data	object	Yes	Object containing the mapping between column names and values for the record to be inserted.

5.1.2 Return Type (create)

A JSON object representing the inserted record.

5.1.3 Reference (create)

async function create (args: ICreateOptions<Type>): Promise<Type> {
    // ...
}

interface ICreateOptions<Type> {
    data: Type
}

5.1.4 Example (create)

import type { IMyServiceMrsNotesNote } from '/path/to/sdk/myService';
import { MyService } from './myService.mrs.sdk/myService';

const myService = new MyService();

// using a plain object
myService.mrsNotes.note.create({ data: { title: 'foo' } });

// using a custom class instance
class Note implements IMyServiceMrsNotesNote {
    // ...
} 

const note = new Note();
note.title = 'foo';

myService.mrsNotes.note.create({ data: note });

5.2 createMany

createMany inserts one or more records in a given table. The records are represented as plain TypeScript/JavaScript objects, or alternatively, as instances of a particular class that encapsulates the data required to create them.

5.2.1 Options (create)

Name	Type	Required	Description
data	object	Yes	Array of objects containing the mapping between column names and values for the records to be inserted.

5.2.2 Return Type (createMany)

An array of JSON objects representing the inserted records.

5.2.3 Reference (createMany)

async function createMany (args: ICreateOptions<Type[]>): Promise<Type[]> {
    // ...
}

interface ICreateOptions<Type> {
    data: Type
}

5.2.4 Example (createMany)

import type { IMyServiceMrsNotesNote } from '/path/to/sdk/myService';
import { MyService } from './myService.mrs.sdk/myService';

const myService = new MyService();

// using a plain object
myService.mrsNotes.note.createMany({ data: [{ title: 'foo' }, { title: 'bar' }] });

// using a custom class
class Note implements IMyServiceMrsNotesNote {
    // ...
} 

const note1 = new Note();
note1.title = 'foo';

const note2 = new Note({ /* */ });
note1.title = 'bar';

myService.mrsNotes.note.createMany({ data: [note1, note2] });

5.3 findFirst

findFirst is used to query the first record that matches a given optional filter.

5.3.1 Options (findFirst)

Name	Type	Required	Description
where	object	No	Filtering conditions that apply to specific fields.
select	object	No	Specifies which properties to include in the returned object.
skip	number	No	Specifies how many records to skip before returning one of the matches.

5.3.2 Return Type (findFirst)

A JSON object representing the first record that matches the filter or undefined when the record was not found.

5.3.3 Reference (findFirst)

async function findFirst (args?: IFindOptions<Selectable, Filterable>): Promise<Selectable | undefined> {
    // ...
}

export interface IFindOptions<Selectable, Filterable> {
    orderBy?: ColumnOrder<Filterable>,
    select?: BooleanFieldMapSelect<Selectable> | FieldNameSelect<Selectable>,
    skip?: number,
    where?: DataFilter<Filterable>,
}

5.3.4 Example (findFirst)

import { MyService } from './myService.mrs.sdk/myService';

const myService = new MyService();

// get the first note, without any filter
await myService.mrsNotes.note.findFirst();
// get the last note, without any filter
await myService.mrsNotes.note.findFirst({ orderBy: { id: "DESC" } });
// get the second note, without any filter
await myService.mrsNotes.note.findFirst({ skip: 1 });
// get the title and shared fields of the second note
await myService.mrsNotes.note.findFirst({ select: { title: true, shared: true }, skip: 1 });
// get the title and shared fields of the first note
await myService.mrsNotes.note.findFirst({ select: ["title", "shared"] });
// get the first shared note
await myService.mrsNotes.note.findFirst({ where: { shared: true } });
// get the first note whose title includes the string "foo"
await myService.mrsNotes.note.findFirst({ where: { title: { $like: "%foo%" } } });

5.4 findUnique

findUnique is used to query a single, uniquely identified record by:

Primary key column(s)
Unique column(s)

If no record was found matching the given where condition, undefined is returned. To have an exception thrown in this case, see findUniqueOrThrow.

5.4.1 Options (findUnique)

Name	Type	Required	Description
where	object	Yes	Wraps all unique columns so that individual records can be selected.
select	object	No	Specifies which properties to include in the returned object.

5.4.2 Return Type (findUnique)

A JSON object representing the specific record that matches the filter or undefined when the record was not found.

5.4.3 Reference (findUnique)

async function findUnique (args?: IFindUniqueOptions<Selectable, Filterable>): Promise<Selectable | undefined> {
    // ...
}

interface IFindUniqueOptions<Selectable, Filterable> {
    select?: BooleanFieldMapSelect<Selectable> | FieldNameSelect<Selectable>,
    where?: DataFilter<Filterable>,
}

5.4.4 Example (findUnique)

import { MyService } from './myService.mrs.sdk/myService';

const myService = new MyService();

// Get the note with id 4.
// using implicit equality
await myService.mrsNotes.note.findUnique({ where: { id: 4 } });
// or using explicit equality
await myService.mrsNotes.note.findUnique({ where: { id: { $eq: 4 } } });

5.5 findUniqueOrThrow

findUniqueOrThrow retrieves a single data record in the same way as findUnique. However, if the query does not find a record, it throws a NotFoundError.

findUniqueOrThrow differs from findUnique as follows:

Its return type is non-nullable. For example, myService.mrsNotes.note.findUnique() can return a note or undefined, but myService.mrsNotes.note.findUniqueOrThrow() always returns a note.

5.6 findMany

findMany is used to query all records in one or more pages, and optionally, those that match a given filter.

5.6.1 Options (findMany)

Name	Type	Required	Description
where	object	No	Filtering conditions that apply to specific fields.
select	object	No	Specifies which properties to include in the returned object.
skip	number	No	How many records to skip before returning one of the matches.
take	number	No	Maximum number of records to return.
fetchAll	object	boolean	No

5.6.2 Return Type (findMany)

An array of JSON objects representing the records that match the filter.

5.6.3 Reference (findMany)

async function findMany (args?: IFindOptions<Selectable, Filterable>): Promise<IMrsResultList<Selectable>> {
    // ...
}

interface IFindOptions<Selectable, Filterable> {
    fetchAll?: IFindAllOptions<Selectable> | boolean,
    orderBy?: ColumnOrder<Filterable>,
    select?: BooleanFieldMapSelect<Selectable> | FieldNameSelect<Selectable>,
    skip?: number,
    take?: number,
    where?: DataFilter<Filterable>,
}

5.6.4 Example (findMany)

import { MyService } from './myService.mrs.sdk/myService';

const myService = new MyService();

// get all notes of the first page
await myService.mrsNotes.note.findMany();
// get the first 3 notes of the first page
await myService.mrsNotes.note.findMany({ take: 3 });
// get the first 50 notes
await myService.mrsNotes.note.findMany({ fetchAll: true, take: 50 });
// get all notes whose id is greater than 10
await myService.mrsNotes.note.findMany({ fetchAll: true, where: { id: { $gt: 10 } } });

5.7 delete

delete is used to delete the first record that matches a given required filter.

5.7.1 Options (delete)

Name	Type	Required	Description
where	object	Yes	Filtering conditions that apply to specific fields.

5.7.2 Return Type (delete)

A JSON object containing the number of records that were deleted (always 1).

5.7.3 Reference (delete)

async function delete (args: IDeleteOptions<IMyServiceMrsNotesUserParams>): Promise<IMrsDeleteResult> {
    // ...
}

interface IDeleteOptions<Filterable> {
    where?: DataFilter<Filterable>,
}

interface IMrsDeleteResult {
    itemsDeleted: 1,
}

5.7.4 Example (delete)

import { MyService } from './myService.mrs.sdk/myService';

const myService = new MyService();

// delete the first note whose title includes the string "foo"
await myService.mrsNotes.note.delete({ where: { title: { $like: "%foo%" } } });

5.8 deleteMany

delete is used to delete all records that match a given filter.

5.8.1 Options (deleteMany)

Name	Type	Required	Description
where	object	No	Filtering conditions that apply to specific fields.

5.8.2 Return Type (deleteMany)

A JSON object containing the number of records that were deleted.

5.8.3 Reference (deleteMany)

async function deleteMany (args: IDeleteOptions<IMyServiceMrsNotesUserParams>): Promise<IMrsDeleteResult> {
    // ...
}

interface IDeleteOptions<Filterable> {
    where?: DataFilter<Filterable>,
}

interface IMrsDeleteResult {
    itemsDeleted: number,
}

5.8.4 Example (deleteMany)

import { MyService } from './myService.mrs.sdk/myService';

const myService = new MyService();

// delete all notes whose title includes the string "foo"
await myService.mrsNotes.note.deleteMany({ where: { title: { $like: "%foo%" } } });
// delete all shared notes
await myService.mrsNotes.note.deleteMany({ where: { shared: true } });

5.9 update

update is used to update a record with a given identifier or primary key.

5.9.1 Options (update)

Name	Type	Required	Description
data	object	Yes	Set of fields and corresponding values to update.
where	object	Yes	Matching identifier or primary key.

5.9.2 Return Type (update)

A JSON object representing the up-to-date record.

5.9.3 Reference (update)

async function update (args: IUpdateOptions<Data, Filterable, ["id"], { batch: false }>): Promise<Data> {
    // ...
}

interface IUpdateOptions<Data, Filterable, PrimaryKeys extends Array<string & keyof Filterable>, Config> {
    data: Data,
    where: Config extends IBatchConfig ? Array<UpdateMatch<Filterable, PrimaryKeys>> : UpdateMatch<Filterable, PrimaryKeys>
}

interface IBatchConfig {
    batch: true
}

5.9.4 Example (update)

import type { IMyServiceMrsNotesNote } from '/path/to/sdk/myService';
import { MyService } from './myService.mrs.sdk/myService';

const myService = new MyService();

// update the note with id is 1 using a plain object
await myService.mrsNotes.note.update({ where: { id: 1 }, data: { title: 'bar' } } );

// using a custom class instance
class Note implements IMyServiceMrsNotesNote {
    // ...
} 

const note = new Note();
note.shared = false;

// update the note with id 1
await myService.mrsNotes.note.update({ where: { id: 1 }, data: note });

5.10 updateMany

updateMany is used to update all records with matching identifiers or primary keys.

5.10.1 Options (updateMany)

Name	Type	Required	Description
data	object	Yes	Set of fields and corresponding values to update.
where	object	Yes	Matching identifier or primary key.

5.10.2 Return Type (updateMany)

An array of JSON objects representing the up-to-date records.

5.10.3 Reference (updateMany)

async function updateMany (args: IUpdateOptions<Data, Filterable, ["id"], { batch: true }>): Promise<Data[]> {
    // ...
}

interface IUpdateOptions<Data, Filterable, PrimaryKeys extends Array<string & keyof Filterable>, Config> {
    data: Data,
    where: Config extends IBatchConfig ? Array<UpdateMatch<Filterable, PrimaryKeys>> : UpdateMatch<Filterable, PrimaryKeys>
}

interface IBatchConfig {
    batch: true
}

5.10.4 Example (updateMany)

import type { IMyServiceMrsNotesNote } from '/path/to/sdk/myService';
import { MyService } from './myService.mrs.sdk/myService';

const myService = new MyService();

// update the notes with id 1 and 2 using a plain object
await myService.mrsNotes.note.update({ where: [{ id: 1 }, { id: 2 }], data: { title: 'bar' } });

// using a custom class instance
class Note implements IMyServiceMrsNotesNote {
    // ...
} 

const note = new Note();
note.shared = false;

// update the note with id 1 and 2
await myService.mrsNotes.note.update({ where: [{ id: 1 }, { id: 2 }], data: note });

MySQL REST Service

Developer's Guide

SQL Reference

Core REST APIs

SDK Reference

MRS SDK Reference