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

Chapter Overview

Introduction to the MRS SDK
Querying Data Across Tables
Contextual Fields and Parameters
Read Your Writes Consistency
Checking for NULL Column Values
Working with Spatial Data Types
Authentication
TypeScript Client API Reference
Python Client API Reference

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 REST data mapping view 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 REST data mapping view 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 Contextual fields and parameters

There are different requirements at play when inserting a new row or when updating an existing row in a Table or View using the MySQL REST Service. For starters, since there is currently no support for partial updates, this means that every time an application wants to update a row, it needs to provide a complete representation of the row as it will become. That representation can still determine potential columns which will be “unset” notwithstanding, at least for those cases where the columns do not impose a constraint that prevents such an action (this is in line with what happens on ORDS). On the other hand, even though there is no specific limitation for inserting new rows, an application should still be aware of the underlying column constraints to reduce the friction by requiring the minimum possible set of fields or alternatively, provide better user feedback (e.g. using the type checker) when it comes to missing fields required to perform the operation.

From the MRS SDK standpoint this means that the type definitions used to insert and update rows must be capable of making the distinction between required and optional fields. In practice, a field should always be required unless there is some specific circumstance that allows it to be optional such as the fact that it maps to an auto-generated primary key column, a foreign key column, a nullable column or a column with a default value. Whilst inserting a value, all of these circumstances are valid and should be accounted for, whereas whilst updating a value, due to the limitations described above, it only makes sense for a field to be optional when it maps to a nullable column or column with row ownership.

When it comes to Functions or Procedures, all fields (or input parameters in this case) should be considered optional because they cannot have NOT NULL constraints, which always makes them nullable by nature. Thus, an optional parameter is just a parameter where the value can be NULL.

The examples assume a setup using the Sakila Sample Database where the schema and the corresponding tables and routines are available under a REST service called myService.

3.1 Inserting a new row in a Table/View

Inserting a new row in the actor table does not require either the actorId field or the lastUpdate field because the former is an auto-generated primary key (AUTO_INCREMENT) whereas the latter maps to a column with a default value CURRENT_TIMESTAMP().

3.1.1 TypeScript

myService.sakila.actor.create({ data: { firstName: "FOO", lastName: "BAR" } })

3.1.2 Python

my_service.sakila.actor.create(data={"first_name": "FOO", "last_name": "BAR"})

3.2 Updating an existing row in a Table/View

Updating a row in the actor table requires all fields to be specified because neither firstName nor lastName are nullable. On the other hand, the description column in the film_text table is nullable.

3.2.1 TypeScript

myService.sakila.actor.update({ data: { id: 1, firstName: "PENELOPE", lastName: "CRUZ" } }) // Property 'lastUpdate' is missing in type '{ actorId: number; lastName: string; firstName: string; }' but required in type 'IUpdateMyServiceSakilaActor'.
myService.sakila.filmText.update({ data: { film_id: 1, title: "FOO" } })

3.2.2 Python

my_service.sakila.actor.update(data={"id": 1, "first_name": "PENELOPE", "last_name": "CRUZ"}) # Missing key "last_update" for TypedDict "IUpdateMyServiceSakilaActor"
my_service.sakila.film_text.update(data={"film_id": 1, "title": "FOO"})

3.3 Calling a Function or Procedure

Calling a function or procedure does not require any field to be specified because input parameters are nullable by nature (there is no syntax to add NOT NULL constraints). It is expected that functions and procedures handle NULL values at runtime accordingly. For example, with MySQL Function as follows:

DELIMITER //
CREATE FUNCTION my_db.my_func (x INT, y INT)
RETURNS BIGINT DETERMINISTIC
BEGIN
  DECLARE sum_result BIGINT DEFAULT 0;
  IF y is NULL THEN
    SET sum_result = x;
  ELSE
    SET sum_result = x + y;
  END IF;
  RETURN sum_result;
END //
DELIMITER ;

where the corresponding REST object is created as follows:

CREATE OR REPLACE REST FUNCTION /myFunc ON SERVICE /myService SCHEMA /myDb AS my_db.my_func
  PARAMETERS IMyServiceMyDbMyFuncParams {
    x: x @IN,
    y: y @IN
  }

3.4 TypeScript

myService.myDb.myFunc.call() // null
myService.myDb.myFunc.call({ x: 3 }) // 3
myService.myDb.myFunc.call({ x: 3, y: 2 }) // 5

3.5 Python

my_service.my_db.myFunc() # None
my_service.my_db.myFunc(x=3) # 3
my_service.my_db.myFunc(x=3, y=2) # 5

4 Read Your Writes Consistency

With multiple MySQL server instances running as an InnoDB Cluster/ClusterSet, data read from one instance might be dependent on data written on a different instance, which might not have been yet replicated to the server where the data is being read from. This is a classical concern on distributed systems which alludes to the consistency of the data and the problem has been formalized as a concept called Read Your Writes.

To solve this issue, and ensure an application is always able to read its own writes, MySQL uses a Global Transaction ID (GTID), whose definition, according to the official documentation, is:

A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (the source). This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication topology.

It is, in essence, and in layman’s terms, an identifier that is provided to a client for each “write” operation, which the client can then provide back to the MySQL server cluster which can use it to ensure any subsequent read accounts for all the data written up until the operation that generated that GTID. This usually carries a cost, and for that reason, is a behavior that needs to be explicitly enabled by the end user depending on what kind of topology an application is using.

In the MySQL REST Service, it is possible to ensure an application is able to read its own writes consistently in a cluster of MySQL instances only when retrieving resources or deleting resources. Using the TypeScript SDK, this can be done with the readOwnWrites option available for the following commands:

findFirst()
findFirstOrThrow()
findUnique()
findUniqueOrThrow()
findMany()
findAll()
delete()
deleteMany()

myService.sakila.actor.findFirst({ readOwnWrites: true })

This option is only relevant when the application is running on top of a MySQL instance cluster where the GTID infrastructure is specifically configured and enabled, otherwise the option will be ignored.

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

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

7 Authentication

When a REST object requires authentication, applications using the SDK should authenticate in the scope of the corresponding REST service beforehand. A client can be authenticated using an existing authentication app and providing a valid username and password.

Currently, the MRS SDK (both for TypeScript and Python) only supports MRS Native and MySQL Internal Authentication apps (more details here).

7.1 MRS Native Authentication

With an authentication app created as follows:

CREATE REST AUTH APP baz ON SERVICE /myService VENDOR MRS;

and a REST user created as follows:

CREATE REST USER "foo"@"baz" IDENTIFIED BY "bar";

7.1.1 TypeScript

myService.authenticate({ username: "foo", password: "bar", authApp: "baz" })

7.1.2 Python

my_service.sakila.authenticate(username="foo", password="bar", auth_app="baz")

7.2 MySQL Internal Authentication

In the same way, with an authentication app created as follows:

CREATE REST AUTH APP qux ON SERVICE /myService VENDOR MYSQL;

and, this time, an actual MySQL server account created as follows:

CREATE USER foo IDENTIFIED BY "bar";

the API is used in the exact same way.

7.2.1 TypeScript

myService.authenticate({ username: "foo", password: "bar", authApp: "qux" })

7.2.2 Python

my_service.sakila.authenticate(username="foo", password="bar", auth_app="baz")

After the authentication succeeds, every valid SDK command that executes on top of a REST object that requires authentication, should also succeed.

8 TypeScript Client API Reference

8.1 authenticate

authenticate is used to authenticate in a given service using a given authentication app.

8.2 Options (authenticate)

Name	Type	Required	Description
username	string	Yes	Username in the scope of the authentication app.
password	string	No	Password in the scope of the authentication app.
authApp	string	Yes	Name of the authentication app.

8.3 Return Type (authenticate)

Nothing (void).

8.4 Reference (authenticate)

async function authenticate (IAuthenticateOptions): Promise<void> {
    // ...
}

interface IAuthenticateOptions {
    username: string
    password: string
    authApp: string
}

8.5 Example (authenticate)

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

const myService = new MyService();

myService.authenticate({ username: 'foo', password: 'bar', authApp: 'baz' });

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

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

8.6.2 Return Type (create)

A JSON object representing the inserted record.

8.6.3 Reference (create)

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

interface ICreateOptions<Type> {
    data: Type
}

8.6.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 });

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

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

8.7.2 Return Type (createMany)

An array of JSON objects representing the inserted records.

8.7.3 Reference (createMany)

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

interface ICreateOptions<Type> {
    data: Type
}

8.7.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] });

8.8 findFirst

findFirst is used to query the first record (in no specific order) that matches a given optional filter.

8.8.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.
readOwnWrites	boolean	No	Ensures read consistency for a cluster of servers.

8.8.2 Return Type (findFirst)

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

8.8.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>;
    readOwnWrites?: boolean;
}

8.8.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%" } } });

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

8.9.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.
readOwnWrites	boolean	No	Ensures read consistency for a cluster of servers.

8.9.2 Return Type (findUnique)

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

8.9.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>;
    readOwnWrites?: boolean;
}

8.9.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 } } });

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

8.11 findMany

findMany is used to query a subset of records in one or more pages, and optionally, those that match a given filter. To find all records see findAll.

8.11.1 Options (findMany)

Name	Type	Required	Description
cursor	object	No	Retrieve records using unique and sequential fields as cursor.
iterator	boolean	No	Enable or disable iterator behavior.
orderBy	object	No	Determines the sort order of 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.
where	object	No	Filtering conditions that apply to specific fields.
take	number	No	Maximum number of records to return.
readOwnWrites	boolean	No	Ensures read consistency for a cluster of servers.

8.11.2 Return Type (findMany)

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

8.11.3 Reference (findMany)

async function findMany ({ cursor, iterator = true, orderBy, select, skip, take, where }: IFindManyOptions<Item, Filterable, Cursors>): Promise<Item[]> {
    // ...
}

interface IFindManyOptions<Item, Filterable, Iterable> {
    cursor?: Cursor<Iterable>;
    iterator?: boolean;
    orderBy?: ColumnOrder<Filterable>;
    select?: BooleanFieldMapSelect<Item> | FieldNameSelect<Item>;
    skip?: number;
    take?: number;
    where?: DataFilter<Filterable>;
    readOwnWrites?: boolean;
}

8.11.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
await myService.mrsNotes.note.findMany({ take: 3 });
// get notes of then first page where the id is greater than 10
await myService.mrsNotes.note.findMany({ where: { id: { $gt: 10 } } });

8.12 findAll

findAll is used to query every record, and optionally, all those that match a given filter. To get a paginated subset of records, see findMany.

8.12.1 Options (findAll)

Name	Type	Required	Description
cursor	object	No	Retrieve records using unique and sequential fields as cursor.
orderBy	object	No	Determines the sort order of specific fields.
progress	function	No	Specifies a function to be called back when reporting progress.
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.
where	object	No	Filtering conditions that apply to specific fields.
readOwnWrites	boolean	No	Ensures read consistency for a cluster of servers.

8.12.2 Return Type (findAll)

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

8.12.3 Reference (findAll)

async function findAll (args?: IFindAllOptions<Item, Filterable>): Promise<Item[]> {
    // ...
}

interface IFindAllOptions<Item, Filterable> {
    cursor?: Cursor<Iterable>;
    orderBy?: ColumnOrder<Filterable>;
    progress?: progress?: (items: Item[]) => Promise<void>;
    select?: BooleanFieldMapSelect<Item> | FieldNameSelect<Item>;
    skip?: number;
    where?: DataFilter<Filterable>;
    readOwnWrites?: boolean;
}

8.12.4 Example (findAll)

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

const myService = new MyService();

// get all notes
await myService.mrsNotes.note.findAll();
// get all notes after the first 10
await myService.mrsNotes.note.findAll({ skip: 10 });
// get all notes and report the progress
await myService.mrsNotes.note.findMany({ progress: (notes) => {
    console.log(`Retrieved ${notes.length} notes.`);
}});

8.13 delete

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

8.13.1 Options (delete)

Name	Type	Required	Description
where	object	Yes	Filtering conditions that apply to specific fields.
readOwnWrites	boolean	No	Ensures read consistency for a cluster of servers.

8.13.2 Return Type (delete)

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

8.13.3 Reference (delete)

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

interface IDeleteOptions<Filterable> {
    where?: DataFilter<Filterable>;
    readOwnWrites?: boolean;
}

interface IMrsDeleteResult {
    itemsDeleted: 1;
}

8.13.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%" } } });

8.14 deleteMany

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

8.14.1 Options (deleteMany)

Name	Type	Required	Description
where	object	No	Filtering conditions that apply to specific fields.
readOwnWrites	boolean	No	Ensures read consistency for a cluster of servers.

8.14.2 Return Type (deleteMany)

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

8.14.3 Reference (deleteMany)

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

interface IDeleteOptions<Filterable> {
    where?: DataFilter<Filterable>;
    readOwnWrites: boolean;
}

interface IMrsDeleteResult {
    itemsDeleted: number;
}

8.14.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 } });

8.15 update

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

8.15.1 Options (update)

Name	Type	Required	Description
data	object	Yes	Set of fields and corresponding values to update.

8.15.2 Return Type (update)

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

8.15.3 Reference (update)

async function update (args: IUpdateOptions<UpdatableFields>): Promise<Data> {
    // ...
}

type IUpdateOptions<Type> = ICreateOptions<Type>;

8.15.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({ data: { id: 1, title: 'bar' } });

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

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

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

8.16 updateMany

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

8.16.1 Options (updateMany)

Name	Type	Required	Description
data	object	Yes	Set of fields and corresponding values to update.

8.16.2 Return Type (updateMany)

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

8.16.3 Reference (updateMany)

async function updateMany (args: IUpdateOptions<UpdatableFields[]>): Promise<Data[]> {
    // ...
}

type IUpdateOptions<Type> = ICreateOptions<Type>;

8.16.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({ data: [{ id: 1, title: 'bar' }, { id: 2, title: 'bar' }] });

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

const note1 = new Note();
note.id = 1;
note.shared = false;

const note2 = new Note();
note.id = 2;
note.shared = false;

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

9 Python Client API Reference

9.1 REST Services

The MRS Python SDK exposes a type-safe programmable interface for the MySQL REST Service that unifies, under a set of contextual commands, the available HTTP operations to access and use database objects (tables, views and functions) exposed as REST resources.

The following resources can be accessed from a service namespace:

REST Schemas

The following commands can be accessed from a service namespace:

authenticate

9.1.1 authenticate

authenticate is a service-level command that authenticates a user so he/she can work with restricted MySQL REST Services.

9.1.1.1 Options (authenticate)

Name	Type	Required	Description
app_name	str	Yes	Name of the authentication application (as specified by the admin)
user	str	Yes	User name
password	str	No	If not provided, the empty string is assumed as the password

The following authentication application names are supported:

MRS
MySQL Internal

9.1.1.2 Return Type (authenticate)

None.

9.1.1.3 Example (authenticate)

from sdk.python import MyService

my_service = MyService()

# `authenticate` will account for authentication
 await my_service.authenticate(
    app_name="${app_name}",
    user="Lucas",
    password="S3cr3t"
)

# Service is ready and tied database objects can be utilized
# E.g., calling a function
res = await my_service.sakila.hello_func(name="Rui")
# print(res) -> Hello, Rui!

9.2 REST Schemas

In the Python SDK, database objects such as tables and functions are grouped under namespaces that correspond to their schema. Applications can access and use those database objects via the API exposed by each one.

The following REST resources can be accessed from the corresponding schema namespace:

REST Views
REST Documents
REST Functions
REST Procedures

9.3 REST Views

9.3.1 create

create is used to insert a record (a REST document) into the database. The REST document is represented as a typed dictionary object whose fields, or keys, should comply with the interface exposed by the type definition INew${obj_class_name} where ${obj_class_name} is a variable containing a string which is a fully-qualified name composed by the names of the REST Service, REST Schema and REST View themselves.

To insert multiple documents, see create_many.

9.3.1.1 Options (create)

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

9.3.1.2 Return Type (create)

A REST document data class object representing the record that was inserted. For more details about REST documents, check the REST Documents section.

9.3.1.3 Example (create)

from sdk.python.my_service import IMyServiceSakilaActor as Actor, MyService

my_service = MyService()

actor: Actor = await my_service.sakila.actor.create(
    data={
        "first_name": "FOO",
        "last_name": "BAR",
    }
)

print(actor)
# IMyServiceSakilaActor(actor_id=35000, first_name='FOO', last_name='BAR', last_update='2024-06-04 10:14:33.000000')

The actor_id and last_update columns from the sakila table on the sample sakila database are automatically generated on each insert, which means they can be omitted.

9.3.2 create_many

create_many is used to insert one or more records (REST documents) into the database. A record is represented as a typed dictionary object whose fields, or keys, should comply with the interface exposed by the type definition INew${obj_class_name} where ${obj_class_name} is a variable which value depends on the service, schema and table names themselves.

To insert a single record, see create.

9.3.2.1 Options (create_many)

Name	Type	Required	Description
data	Sequence of `TypedDict` - it can be any Python object supporting the iteration protocol, such as lists and tuples	Yes	List of objects containing the mapping between column names and values for the records to be inserted

9.3.2.2 Return Type (create_many)

A list of REST document data class objects representing each record that was inserted. For more details about REST documents, check the REST Documents section.

9.3.2.3 Example (create_many)

from sdk.python.my_service import IMyServiceSakilaActor as Actor, MyService

my_service = MyService()

actors: list[Actor] = await my_service.sakila.actor.create_many(
    data=[
        {
            "first_name": "FOO",
            "last_name": "BAR",
        },
        {
            "first_name": "OOF",
            "last_name": "RAB",
        }
    ]
)

print(actors)
# [
#     IMyServiceSakilaActor(
#         actor_id=35000,
#         first_name='FOO',
#         last_name='BAR',
#         last_update='2024-06-04 10:14:33.000000'
#     ),
#     IMyServiceSakilaActor(
#         actor_id=36000,
#         first_name='OOF',
#         last_name='RAB',
#         last_update='2024-08-04 10:14:33.000000'
#     )
# ]

The actor_id and last_update columns from the sakila table on the sample sakila database are automatically generated on each insert, which means they can be omitted.

9.3.3 find_first

find_first is used to query the first REST document (in no specific order) that matches a given optional filter. It returns None if no document is found.

To raise an exception if there are no matches, use find_first_or_throw instead.

To find multiple REST documents, see find_many.

9.3.3.1 Options (find_first)

Name	Type	Required	Description
select	dict or list	No	Specifies which properties to include or exclude on the returned document - works as a field filter
where	dict	No	Applies filtering conditions based on specific fields - works as a document filter
skip	int	No	Specifies how many documents to skip before returning one of the matches
order_by	dict	No	Lets you customize the order (`ASC` or `DESC`) in which the documents are returned based on specific fields
cursor	dict	No	Specifies the position of the first item to include in the result set. A cursor bookmarks a location in a result set and must be a column containing unique and sequential values.
read_own_writes	bool	No	Ensures read consistency for a cluster of servers - `False` is used by default

Cursor-based pagination takes precedence over offset-based pagination, which means that if a cursor is defined, the value of the offset property (skip) will be ignored.

9.3.3.2 Return Type (find_first)

If there is a match, a REST document data class object meeting the filter conditions, otherwise None is returned. For more details about REST documents, check the REST Documents section.

9.3.3.3 Example (find_first)

Consider the following generic usage snippet of find_first:

from sdk.python.my_service import IMyServiceSakilaActor as Actor, MyService

my_service = MyService()

actor: Optional[Actor] = await self.my_service.sakila.actor.find_first(
    select={"last_name": False},
    where={"first_name": {"like": "%%ED%%"}},
    .
    .
)

In the following subsections, a small usage example is included for each option.

9.3.3.3.1 Select (find_first)

# Only include the fields specified (`list` use case)
select=["last_update"]

# Only include the fields specified (`dict` use case)
select={"last_name": True}

# Include all fields but the specified
select={"last_name": False}

9.3.3.3.2 Where (find_first)

# Equality - these two are equivalent
where={"actor_id": 3}
where={"actor_id": {"equals": 3}}

# Difference
where={"last_name": {"ne": "Pacheco"}}

# Greater than
where={"actor_id": {"gt": 3}}

# Greater than or equal
where={"actor_id": {"gte": 3}}

# Lower than
where={"actor_id": {"lt": 3}}

# Lower than or equal
where={"actor_id": {"lte": 3}}

# Leads to a match when the field is not NULL.
# In this case, we would get documents where the
# field `last_update` is not NULL.
where={"last_updated": {"not": None}}

# Pattern
where={"first_name": {"like": "%%ED%%"}}

# Union of conditions
where={
    "AND": [
        {"first_name": "PENELOPE"},
        {"actor_id": {"gte": 3}}
    ]
}

# Intersection of conditions
where={
    "OR": [
        {"first_name": "MICHAEL"},
        {"last_name": {"like": "%%AB%%"}}
    ]
}

9.3.3.3.3 Skip (find_first)

Offset-based pagination is non-inclusive, meaning that if the position marking the offset is a match, it will not be included in the result set.

Suppose the actor table contains a bunch of records as shown in Cursor Example. Then, the skip option can be used as follows:

actor: Optional[Actor] = await self.my_service.sakila.actor.find_first(
    where={"last_name": {"like": "%%HA%%"}}, skip=2
)

if actor is None:
    warnings.warn(f"Actor not found")
else:
    print(actor.actor_id)

The printed actor ID would be 32.

9.3.3.3.4 Order By (find_first)

# Descending order
order_by={"first_name": "DESC"}

# Ascending order
order_by={"actor_id": "ASC"}

9.3.3.3.5 Cursor (find_first)

The position bookmarked by the cursor is non-inclusive, meaning that if the bookmarked position is a match, it will not be included in the result set.

Suppose the actor table contains a bunch of records. In the following snippet, only those records where last_name matches the pattern %%HA%% are shown:

{
    "actorId": 1,
    "lastName": ...,
    "firstName": ...,
    "lastUpdate": ...,
},
...,
{
    "actorId": 3,
    "lastName": "CHASE",
    "firstName": "ED",
    "lastUpdate": "2006-02-15 04:34:33.000000",
},
...,
{
    "actorId": 8,
    "lastName": "JOHANSSON",
    "firstName": "MATTHEW",
    "lastUpdate": "2006-02-15 04:34:33.000000",
},
...,
{
    "actorId": 32,
    "lastName": "HACKMAN",
    "firstName": "TIM",
    "lastUpdate": "2006-02-15 04:34:33.000000",
}

The cursor option can be used as follows:

actor: Optional[Actor] = await self.my_service.sakila.actor.find_first(
    where={"last_name": {"like": "%%HA%%"}},
    cursor={"actor_id": 3},  # cursor is exclusive
)

if actor is None:
    warnings.warn(f"Actor not found")
else:
    print(actor.actor_id)

The printed actor ID would be 8.

9.3.3.3.6 Read Own Writes (find_first)

# ON
read_own_writes=True

# OFF
read_own_writes=False

9.3.4 find_first_or_throw

find_first_or_throw is used to retrieve the first REST document that matches a given optional filter in the same way as find_first does. However, if the query does not find a document, it raises a MrsDocumentNotFoundError exception.

To not raise an exception and get None if there are no matches, use find_first instead.

To find multiple REST documents, see find_many.

9.3.4.1 Options (find_first_or_throw)

find_first_or_throw and find_first implement the very same options. For more details about these, see Options (find_first).

9.3.4.2 Return Type (find_first_or_throw)

If there is a match, a REST document data class object meeting the filter conditions is returned, otherwise an exception MrsDocumentNotFoundError is raised. For more details about REST documents, check the REST Documents section.

9.3.4.3 Example (find_first_or_throw)

Usage is similar to find_first, however, now you should account for a possible exception:

import warnings
from sdk.python.my_service import IMyServiceSakilaActor as Actor, MyService, MrsDocumentNotFoundError

my_service = MyService()

try:
    actor: Actor = await self.my_service.sakila.actor.find_first_or_throw(
        select={"last_name": False},
        where={"first_name": {"like": "%%ED%%"}},
    )
except MrsDocumentNotFoundError:
    warnings.warn("Ups, no matches found")

See Example (find_first) for additional usage options.

9.3.5 find_unique

find_unique is used to query a single, uniquely identified REST document by:

Primary key column(s)
Unique column(s)

It returns None if no document is found.

To raise an exception if there are no matches, use find_unique_or_throw instead.

9.3.5.1 Options (find_unique)

Name	Type	Required	Description
select	dict or list	No	Specifies which properties to include or exclude on the returned document - works as a field filter
where	dict	Yes	Applies filtering conditions based on specific fields (must be unique) - works as a document filter
read_own_writes	bool	No	Ensures read consistency for a cluster of servers - `False` is used by default

9.3.5.2 Return Type (find_unique)

If there is a match, a REST document data class object meeting the filter conditions, otherwise None is returned. For more details about REST documents, check the REST Documents section.

9.3.5.3 Example (find_unique)

import warnings
from sdk.python.my_service import IMyServiceSakilaActor as Actor, MyService

my_service = MyService()

aid = 3
actor: Actor = await self.my_service.sakila.actor.find_unique(
    where={"actor_id": aid}, select=["last_update"], read_own_writes=False
)
if actor is None:
    warnings.warn(f"Actor with id={aid} not found")

assert actor.actor_id == aid

See Example (find_first) for additional usage options.

9.3.6 find_unique_or_throw

find_unique_or_throw is used to query a single, uniquely identified REST document by:

Primary key column(s)
Unique column(s)

If no document was found matching the given filter conditions, MrsDocumentNotFoundError is raised.

To not raise an exception and get None if there are no matches, use find_unique instead.

9.3.6.1 Options (find_unique_or_throw)

find_unique_or_throw and find_unique implement the very same options. For more details about these, see Options (find_unique).

9.3.6.2 Return Type (find_unique_or_throw)

If there is a match, a REST document data class object meeting the filter conditions, otherwise MrsDocumentNotFoundError is raised. For more details about REST documents, check the REST Documents section.

9.3.6.3 Example (find_unique_or_throw)

import warnings
from sdk.python.my_service import IMyServiceSakilaActor as Actor, MyService, MrsDocumentNotFoundError

my_service = MyService()

aid = 3
try:
    actor: Actor = await self.my_service.sakila.actor.find_unique_or_throw(
        where={"actor_id": aid}, select=["last_update"], read_own_writes=False
    )
except MrsDocumentNotFoundError:
    warnings.warn("Ups, no matches found")

See Example (find_first) for additional usage options.

9.3.7 find_many

find_many is used to query a subset of REST documents in one or more pages, and optionally, those that match a given filter.

To retrieve all documents see find_all.

9.3.7.1 Options (find_many)

Name	Type	Required	Description
select	dict or list	No	Specifies which properties to include or exclude on the returned document - works as a field filter
where	dict	No	Applies filtering conditions based on specific fields - works as a document filter
skip	int	No	Specifies how many documents to skip before returning one of the matches
order_by	dict	No	Lets you customize the order (`ASC` or `DESC`) in which the documents are returned based on specific fields
cursor	dict	No	Specifies the position of the first item to include in the result set. A cursor bookmarks a location in a result set and must be a column containing unique and sequential values.
read_own_writes	bool	No	Ensures read consistency for a cluster of servers - `False` is used by default
take	int	No	Maximum number of documents to return
iterator	bool	No	Enable or disable iterator behavior. It is used by the SDK to reset the internal iterator when consuming paginated data in order to avoid n + 1 requests, the internal iterator stops after the MySQL Router says there are no more items. Default value is `True` (enabled).

Cursor-based pagination takes precedence over offset-based pagination, which means that if a cursor is defined, the value of the offset property (skip) will be ignored.

9.3.7.2 Return Type (find_many)

A list of REST document data class objects representing the records that match the filter. For more details about REST documents, check the REST Documents section.

9.3.7.3 Example (find_many)

from sdk.python.my_service import IMyServiceSakilaActor as Actor, MyService

my_service = MyService()

actors: list[Actor] = await self.my_service.sakila.actor.find_many(
    where={"first_name": "PENELOPE"},
    take=2,
    skip=2,
)

print(actors)
# [
#     IMyServiceSakilaActor(
#         actor_id=35000,
#         first_name='PENELOPE',
#         last_name='BAR',
#         last_update='2024-06-04 10:14:33.000000'
#     ),
#     IMyServiceSakilaActor(
#         actor_id=36000,
#         first_name='PENELOPE',
#         last_name='FOO',
#         last_update='2024-08-04 10:14:33.000000'
#     )
# ]

See Example (find_first) for additional usage options.

9.3.8 find_all

find_all is used to retrieve every MRS document, and optionally, all those that match a given filter.

To get a paginated subset of documents, see find_many.

9.3.8.1 Options (find_all)

Name	Type	Required	Description
select	dict or list	No	Specifies which properties to include or exclude on the returned document - works as a field filter
where	dict	No	Applies filtering conditions based on specific fields - works as a document filter
skip	int	No	Specifies how many documents to skip before returning one of the matches
order_by	dict	No	Lets you customize the order (`ASC` or `DESC`) in which the documents are returned based on specific fields
cursor	dict	No	Specifies the position of the first item to include in the result set. A cursor bookmarks a location in a result set and must be a column containing unique and sequential values.
read_own_writes	bool	No	Ensures read consistency for a cluster of servers - `False` is used by default
progress	function	No	Specifies a function to be called back when reporting progress

Cursor-based pagination takes precedence over offset-based pagination, which means that if a cursor is defined, the value of the offset property (skip) will be ignored.

9.3.8.2 Return Type (find_all)

A list of REST document data class objects representing the records that match the filter. For more details about REST documents, check the REST Documents section.

9.3.8.3 Example (find_all)

import time
from sdk.python.my_service import (
    IMyServiceSakilaActorData as ActorData,
    IMyServiceSakilaActor as Actor,
    MyService
)

my_service = MyService()

def my_progress(data: list[ActorData]) -> None:
    print("Test Progress Option")
    for i, item in enumerate(data):
        print(f"{i+1} of {len(data)}: actor_id={item["actor_id"]}")
        time.sleep(0.1)

# get all documents that first name matches 'PENELOPE'
actors: list[Actor] = await self.my_service.sakila.actor.find_all(
    where={"first_name": "PENELOPE"}, progress=my_progress
)

See Example (find_first) for additional usage options.

9.3.9 delete

delete is used to delete a single, uniquely identified REST document by:

Primary key column(s)
Unique column(s)

To delete multiple documents, see delete_many.

9.3.9.1 Options (delete)

Name	Type	Required	Description
where	dict	Yes	Applies filtering conditions based on specific fields (must be unique) - works as a document filter
read_own_writes	bool	No	Ensures read consistency for a cluster of servers - `False` is used by default

9.3.9.2 Return Type (delete)

True if the document was deleted successfully or False otherwise.

9.3.9.3 Example (delete)

import warnings
from sdk.python.my_service import IMyServiceSakilaActor as Actor, MyService

my_service = MyService()

aid = 3
ans: bool = await self.my_service.sakila.actor.delete(
    where={"actor_id": aid}
)
if ans is False:
    warnings.warn(f"Actor not deleted - actor_id={aid} not found")
else:
    print(f"Actor document with ID={aid} was deleted")

9.3.10 delete_many

delete_many is used to delete all REST documents that match a given filter. To delete a single document, see delete.

9.3.10.1 Options (delete_many)

Name	Type	Required	Description
where	dict	Yes	Applies filtering conditions based on specific fields - works as a document filter
read_own_writes	bool	No	Ensures read consistency for a cluster of servers - `False` is used by default

9.3.10.2 Return Type (delete_many)

An integer indicating the number of deleted documents.

9.3.10.3 Example (delete_many)

Suppose the actor table contains a bunch of records as shown in Cursor Example.

from sdk.python.my_service import IMyServiceSakilaActor as Actor, MyService

my_service = MyService()

num_items_removed = await self.my_service.sakila.actor.delete_many(
    where={"last_name": {"like": "%%HA%%"}}
)
print(num_items_removed)
# 3

9.3.11 update

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

To update multiple documents, see update_many.

9.3.11.1 Options (update)

Name	Type	Required	Description
data	TypedDict	Yes	Set of fields and corresponding values to update. The identifier or primary key must be included

9.3.11.2 Return Type (update)

A REST document data class object representing the up-to-date record. For more details about REST documents, check the REST Documents section.

9.3.11.3 Example (update)

from datetime import datetime
from sdk.python.my_service import IMyServiceSakilaActor as Actor, MyService

my_service = MyService()

# The `actor_id` and `last_update` columns from the `sakila` table on the
# sample [sakila database](https://dev.mysql.com/doc/sakila/en/) are
# automatically generated on each insert, which means they can be omitted.
actor: Actor = await self.my_service.sakila.actor.create(
    data={"first_name": "Foo", "last_name": "Bar", "actor_id": 345}
)

actor_updated: Actor = await self.my_service.sakila.actor.update(
    data={
            "actor_id": cast(int, actor.actor_id),
            "first_name": "Rodolfo",
            "last_name": "Smith",
            "last_update": str(datetime.now()),
        }
    )

assert actor_updated.first_name == "Rodolfo"
assert actor_updated.last_name == "Smith"
assert actor.actor_id == actor_updated.actor_id

9.3.12 update_many

update_many is used to update all REST documents with matching identifiers or primary keys.

To update a single document, see update.

9.3.12.1 Options (update_many)

Name	Type	Required	Description
data	list of `TypedDict`	Yes	A list of set of fields and corresponding values to update. The identifier or primary key must be included for each “set of fields” (document)

9.3.12.2 Return Type (update_many)

A list of REST document data class objects representing the up-to-date records. For more details about REST documents, check the REST Documents section.

9.3.12.3 Example (update_many)

from datetime import datetime
from sdk.python.my_service import IMyServiceSakilaActor as Actor, MyService

my_service = MyService()

# The `actor_id` and `last_update` columns from the `sakila` table on the
# sample [sakila database](https://dev.mysql.com/doc/sakila/en/) are
# automatically generated on each insert, which means they can be omitted.
actors: Actor = await self.my_service.sakila.actor.create(
    data=[
        {"first_name": "Foo", "last_name": "Bar", "actor_id": 345},
        {"first_name": "Bruh", "last_name": "Baz", "actor_id": 346},
    ]
)

actors_updated: Actor = await self.my_service.sakila.actor.update(
    data=[
        {
            "actor_id": cast(int, actors[0].actor_id),
            "first_name": "Rodolfo",
            "last_name": "Smith",
            "last_update": str(datetime.now()),
        },
        {
            "actor_id": cast(int, actors[1].actor_id),
            "first_name": "Ma",
            "last_name": "Yeung",
            "last_update": str(datetime.now()),
        },

    ]
    )

assert actors_updated[0].first_name == "Rodolfo"
assert actors_updated[0].last_name == "Smith"

assert actors_updated[1].first_name == "Ma"
assert actors_updated[1].last_name == "Yeung"

9.4 REST Documents

A REST document behaves like a Python data class instance, and implements an extended interface which includes the upsert and delete methods.

9.4.1 upsert

upsert creates or updates the REST document represented by the data class instance.

If the specified primary key already exists, an update happens, otherwise a create.

After completing the operation, the data class instance fields are updated in-place.

9.4.1.1 Options (upsert)

No options are implemented because the data required to complete the operation is assumed to be already included in the data class instance itself.

9.4.1.2 Return Type (upsert)

None.

9.4.1.3 Example (upsert)

Showcasing a create.

from sdk.python.my_service import IMyServiceSakilaActor as Actor, MyService

my_service = MyService()

# The `actor_id` and `last_update` columns from the `sakila` table on the
# sample [sakila database](https://dev.mysql.com/doc/sakila/en/) are
# automatically generated on each insert, which means they can be omitted.
actor = Actor(
    schema=self.my_service.sakila,
    data={
        "first_name": "CHARLY",
        "last_name": "BROWN"
    },
)
await actor.upsert()

print(actor)
# Actor(actor_id=6753, first_name='CHARLY', last_name='BROWN', last_update='2024-06-04 10:14:33.000000')

Showcasing an update.

from sdk.python.my_service import IMyServiceSakilaActor as Actor, MyService

my_service = MyService()

actor: Optional[Actor] = await my_service.sakila.actor.find_first(
        where={"actor_id": 3}
    )
if actor is None:
    print("Ups, Invalid document ID")
    sys.exit()
print("Before:", actor)

actor.first_name = "RODOLFO"
actor.last_name = "CHASE"
actor.last_update = str(datetime.now())

await actor.upsert()

print(actor)

# Before: Actor(actor_id=3, first_name='ED', last_name='SMITH', last_update='2023-04-13 15:11:22.000000')
# After: Actor(actor_id=3, first_name='RODOLFO', last_name='CHASE', last_update='2024-06-04 10:14:33.000000')

9.4.2 delete (document)

delete deletes the resource represented by the data class instance.

9.4.2.1 Options (delete - document)

No options are implemented because the data required to complete the operation is assumed to be already included in the data class instance itself.

9.4.2.2 Return Type (delete - document)

None.

9.4.2.3 Example (delete - document)

from sdk.python.my_service import IMyServiceSakilaActor as Actor, MyService

my_service = MyService()

actor: Optional[Actor] = await my_service.sakila.actor.find_first(
        where={"actor_id": 3}
    )
if actor is None:
    print("Ups, no document found")
    sys.exit()

print(actor)
await actor.delete()

actor: Optional[Actor] = await my_service.sakila.actor.find_first(
        where={"actor_id": 3}
    )
print("deleted?", actor is None)

# Actor(actor_id=3, first_name='ED', last_name='SMITH', last_update='2023-04-13 15:11:22.000000')
# deleted? True

9.5 REST Functions

9.5.1 Options (function)

The input arguments and respective types accepted and expected by <func_name>(...) depend on the MySQL function declaration. See Example (function) for an example.

The input arguments must be provided as keyword arguments.

9.5.2 Return Type (function)

The Python data type returned by <func_name>(...) depends on the data type returned by the MySQL function.

For instance, the Python data type int must be expected for MySQL functions declared to return TINYINT, SMALLINT, MEDIUMINT, INT and BIGINT.

See Example (function) for an example.

9.5.3 Example (function)

Calling a REST function in the Python SDK is exactly like calling a local Python function.

from sdk.python import MyService

my_service = MyService()

res = await my_service.sakila.hello_func(name="Rui")
# print(res) -> Hello, Rui!

res = await my_service.sakila.sum_func(a=3, b=2)
# print(res) -> 5

res = await my_service.sakila.my_birthday_func()
# print(res) -> 2024-07-18 00:00:00

where hello_func, sum_func and my_birthday_func are:

-- one input
mysql> CREATE FUNCTION hello_func (name CHAR(20))
> RETURNS CHAR(50) DETERMINISTIC
> RETURN CONCAT('Hello, ', name, '!');

-- many input
mysql> CREATE FUNCTION sum_func (a INT, b INT)
> RETURNS INT DETERMINISTIC
> RETURN a + b;

-- no input
mysql> CREATE FUNCTION my_birthday_func ()
> RETURNS DATETIME DETERMINISTIC
> RETURN CURDATE();

9.6 REST Procedures

9.6.1 Options (procedure)

The input arguments and respective types accepted and expected by <proc_name>(...) depend on the MySQL procedure declaration (specifically, IN and INOUT parameters). See Example (procedure) for an example.

Input parameters aren’t mandatory, meaning you are free to not provide them.

In case of being provided, input parameters can also be assigned a null value when calling the procedure, in other words, you can set any parameters to None.

9.6.2 Return Type (procedure)

The Python data type returned by <proc_name>(...) is a REST document data class object. For more details about REST documents, check the REST Documents section.

This data class object includes the following attributes:

out_parameters: Dictionary with fields for each OUT/INOUT parameter declared as part of the MySQL procedure that produces an actual value. If a parameter is not used to return a value, the field will not be present in the dictionary.
result_sets: List of result set types generated when executing one or more SELECT statements as part of the procedure body. Each result set type can include one or more items.

See Example (procedure) for an example.

9.6.3 Example (procedure)

Consider the following dummy procedures:

-- Assuming the database `mrs_tests` exists
-- You can use the MySQL Client console to run this script

DELIMITER //

CREATE PROCEDURE mrs_tests.mirror_proc (INOUT channel CHAR(4))
BEGIN
    SELECT REVERSE(channel) INTO channel;
END//


CREATE PROCEDURE mrs_tests.twice_proc (IN number INT, OUT number_twice INT)
BEGIN
    SELECT number*2 INTO number_twice;
END//


CREATE PROCEDURE mrs_tests.sample_proc(
    IN arg1 CHAR(5), INOUT arg2 CHAR(5), OUT arg3 FLOAT
)
BEGIN
    SELECT "foo" as name, 42 as age;
    SELECT "bar" as something;
END//

DELIMITER ;

Calling a REST procedure in the Python SDK is exactly like calling a local Python function.

from sdk.python import MyService

my_service = MyService()

procedure_result = await my_service.mrs_tests.mirror_proc(channel="roma")
print(procedure_result)
# IMrsProcedureResponse(
#     result_sets=[],
#     out_parameters={"channel": "amor"}
# )


procedure_result = await my_service.mrs_tests.twice_proc(number=13)
print(procedure_result)
# IMrsProcedureResponse(
#     result_sets=[],
#     out_parameters={"number_twice": 26}
# )


# Note how `arg1` is not provided, and `arg2` is set to null.
procedure_result = await my_service.mrs_tests.sample_proc(arg2=None)
print(procedure_result)
# IMrsProcedureResponse(
#     result_sets=[
#         MrsProcedureResultSet(
#             type="items0",
#             items=[{"name": "foo", "age": 42}],
#         ),
#         MrsProcedureResultSet(
#             type="items1",
#             items=[{"something": "bar"}],
#         ),
#     ],
#     out_parameters={'arg2': None, 'arg3': None}
# )

The first two procedures do not generate result sets, however the third one does. By omission, result sets are untyped meaning generic type names are used for the result sets.

If you want a typed result set, meaning you wish to specify a type, you can do so at the MRS procedure level via the MySQL Shell:

CREATE OR REPLACE REST PROCEDURE /sampleProc
    ON SERVICE /myService SCHEMA /mrsTests
    AS mrs_tests.sample_proc
    RESULT IMyServiceMrsTestsSampleProcResultSet1 {
        name: name @DATATYPE("CHAR(3)"),
        age: age @DATATYPE("TINYINT")
    }
    RESULT IMyServiceMrsTestsSampleProcResultSet2 {
        something: something @DATATYPE("CHAR(3)")
    };

Calling the REST procedure again from the Python SDK leads to:

from sdk.python import MyService

my_service = MyService()


# Note how `arg1` is not provided, and `arg2` is set to null.
procedure_result = await my_service.mrs_tests.sample_proc(arg2=None)
# print(procedure_result.result_sets)
# [
#     MrsProcedureResultSet(
#         type="IMyServiceMrsTestsSampleProcResultSet1",
#         items=[{"name": "foo", "age": 42}],
#     ),
#     MrsProcedureResultSet(
#         type="IMyServiceMrsTestsSampleProcResultSet2",
#         items=[{"something": "bar"}],
#     ),
# ],

MySQL REST Service

Developer's Guide

SQL Reference

Core REST APIs

SDK Reference

MRS SDK Reference

1 Introduction to the MRS SDK

1.1 Generation of SDK Files

1.1.1 On the Fly Generation of TypeScript SDK

1.1.2 Generating the SDK files for a Development Project

2 Querying Data Across Tables

3 Contextual fields and parameters

3.1 Inserting a new row in a Table/View

3.1.1 TypeScript

3.1.2 Python

3.2 Updating an existing row in a Table/View

3.2.1 TypeScript

3.2.2 Python

3.3 Calling a Function or Procedure

3.4 TypeScript

3.5 Python

4 Read Your Writes Consistency

5 Checking for NULL Column Values

6 Working with Spatial Data Types

7 Authentication

7.1 MRS Native Authentication

7.1.1 TypeScript

7.1.2 Python

7.2 MySQL Internal Authentication

7.2.1 TypeScript

7.2.2 Python

8 TypeScript Client API Reference

8.1 authenticate

8.2 Options (authenticate)

8.3 Return Type (authenticate)

8.4 Reference (authenticate)

8.5 Example (authenticate)

8.6 create

8.6.1 Options (create)

8.6.2 Return Type (create)

8.6.3 Reference (create)

8.6.4 Example (create)

8.7 createMany

8.7.1 Options (create)

8.7.2 Return Type (createMany)

8.7.3 Reference (createMany)

8.7.4 Example (createMany)

8.8 findFirst

8.8.1 Options (findFirst)

8.8.2 Return Type (findFirst)

8.8.3 Reference (findFirst)

8.8.4 Example (findFirst)

8.9 findUnique

8.9.1 Options (findUnique)

8.9.2 Return Type (findUnique)

8.9.3 Reference (findUnique)

8.9.4 Example (findUnique)

8.10 findUniqueOrThrow

8.11 findMany

8.11.1 Options (findMany)

8.11.2 Return Type (findMany)

8.11.3 Reference (findMany)

8.11.4 Example (findMany)

8.12 findAll

8.12.1 Options (findAll)

8.12.2 Return Type (findAll)

8.12.3 Reference (findAll)

8.12.4 Example (findAll)

8.13 delete

8.13.1 Options (delete)

8.13.2 Return Type (delete)

8.13.3 Reference (delete)

8.13.4 Example (delete)

8.14 deleteMany

8.14.1 Options (deleteMany)

8.14.2 Return Type (deleteMany)

8.14.3 Reference (deleteMany)

8.14.4 Example (deleteMany)

8.15 update

8.15.1 Options (update)