MySQL REST Service: A Comprehensive Developer Guide

 

Introduction

The MySQL REST Service (MRS) represents a significant evolution in how developers interact with MySQL databases. Built directly into the MySQL ecosystem, MRS provides a next-generation JSON Document Store solution that enables fast, secure HTTPS access to data stored in MySQL, HeatWave, InnoDB ClusterSet, and InnoDB ReplicaSet.

Unlike third-party solutions or custom-built REST APIs, MRS is a fully integrated MySQL solution that prioritizes ease of use, adherence to standards, and high performance. This guide will walk you through everything you need to know to get started with MRS, from installation to implementation in both Python and C#.

Architecture Overview

The MySQL REST Service consists of four major components:

1. MySQL Server (8.0.39+) - The database server hosting your data and the MRS metadata schema
2. MySQL Router (9.3.1+) - Serves REST endpoints and Progressive Web Apps
3. MySQL Shell (9.4.0+) - Configuration and management tool
4. MySQL Shell for VS Code Extension - Optional GUI for managing MRS

The service operates in two modes:

• Development Mode: For local development with immediate testing
• Production Mode: For serving published REST services

Prerequisites and Installation

Requirements

• MySQL Server 8.0.39 or later
• MySQL Router 9.3.1 or later
• MySQL Shell 9.4.0 or higher
• VS Code with MySQL Shell extension (recommended)

Initial Setup

First, ensure your MySQL Server is running. Then, configure MRS support using MySQL Shell:

-- Connect to your MySQL instance
mysqlsh root@localhost

-- Configure MRS (execute in MySQL Shell)
\sql
CONFIGURE REST METADATA ENABLED UPDATE IF AVAILABLE;

Alternatively, using the VS Code extension:

1. Right-click on your database connection
2. Select "Configuring Instance for MySQL REST Service Support"
3. Provide REST user credentials (minimum 8 characters with mixed case, numbers, and special characters)

Bootstrap MySQL Router

For development:

mysqlrouter --bootstrap root@localhost:3306 --directory=/path/to/router --mrs-development myuser

For production:

mysqlrouter --bootstrap root@localhost:3306 --directory=/path/to/router

Start the router:

mysqlrouter --config=/path/to/router/mysqlrouter.conf

Creating Your First REST Service

Step 1: Create a REST Service

-- Create a new REST service
CREATE REST SERVICE /myService
  COMMENTS "My first REST service"
  ENABLED;

-- Configure authentication
CREATE OR REPLACE REST AUTH APP MRS
  VENDOR "MRS"
  DEFAULT AUTH APP
  SERVICE /myService;

Step 2: Enable a Schema for REST

-- Enable the sakila demo database for REST access
CREATE REST SCHEMA /sakila
  FROM `sakila`
  SERVICE /myService;

Step 3: Enable Database Objects

-- Enable the actor table for REST access
CREATE REST DUALITY VIEW /actor
  ON SERVICE /myService SCHEMA /sakila
  AS `sakila`.`actor` {
    actorId: actor_id @SORTABLE,
    firstName: first_name,
    lastName: last_name,
    lastUpdate: last_update
  };

-- Enable with custom options
CREATE REST DUALITY VIEW /film
  ON SERVICE /myService SCHEMA /sakila
  AS `sakila`.`film` {
    filmId: film_id @SORTABLE,
    title: title @SORTABLE,
    description: description,
    releaseYear: release_year,
    rentalDuration: rental_duration,
    rentalRate: rental_rate,
    length: length,
    rating: rating
  }
  AUTHENTICATION REQUIRED;

Step 4: Create REST Users

-- Add a REST user for authentication
ALTER REST AUTH APP MRS
  ON SERVICE /myService
  ADD USER "developer"
  IDENTIFIED BY "SecurePass123!";

Step 5: Publish the Service

-- Publish the service for production
ALTER REST SERVICE /myService
  PUBLISHED;

Authentication Strategies

MRS supports multiple authentication methods:

1. MRS Native Authentication

CREATE REST AUTH APP MRS
  VENDOR "MRS"
  SERVICE /myService;

ALTER REST AUTH APP MRS
  ADD USER "apiuser"
  IDENTIFIED BY "YourSecurePassword123!";

2. OAuth2 (Google, Facebook, OCI)

CREATE REST AUTH APP GoogleAuth
  VENDOR "Google"
  APP_ID "your-google-app-id.apps.googleusercontent.com"
  ACCESS_TOKEN "your-access-token"
  SERVICE /myService;

3. MySQL Database Authentication

CREATE REST AUTH APP MySQLAuth
  VENDOR "MySQL"
  SERVICE /myService;

Using the REST API

Base URL Structure

https://hostname:port/MRS_SERVICE_PATH/MRS_SCHEMA_PATH/MRS_OBJECT_PATH

Example:

https://localhost:8443/myService/sakila/actor

Basic Operations

GET - Retrieve All Records

curl -X GET https://localhost:8443/myService/sakila/actor \
  -H "Authorization: Basic $(echo -n 'developer:SecurePass123!' | base64)"

GET - Retrieve Single Record

curl -X GET https://localhost:8443/myService/sakila/actor/1 \
  -H "Authorization: Basic $(echo -n 'developer:SecurePass123!' | base64)"

POST - Create Record

curl -X POST https://localhost:8443/myService/sakila/actor \
  -H "Content-Type: application/json" \
  -H "Authorization: Basic $(echo -n 'developer:SecurePass123!' | base64)" \
  -d '{
    "firstName": "John",
    "lastName": "Doe"
  }'

PUT - Update Record

curl -X PUT https://localhost:8443/myService/sakila/actor/201 \
  -H "Content-Type: application/json" \
  -H "Authorization: Basic $(echo -n 'developer:SecurePass123!' | base64)" \
  -d '{
    "firstName": "Jane",
    "lastName": "Smith"
  }'

DELETE - Remove Record

curl -X DELETE https://localhost:8443/myService/sakila/actor/201 \
  -H "Authorization: Basic $(echo -n 'developer:SecurePass123!' | base64)"

Advanced Filtering

MRS supports sophisticated filtering using JSON query objects:

# Filter by first name
curl -X GET 'https://localhost:8443/myService/sakila/actor?q={"firstName":"BRUCE"}' \
  -H "Authorization: Basic $(echo -n 'developer:SecurePass123!' | base64)"

# Complex filter with sorting
curl -X GET 'https://localhost:8443/myService/sakila/actor?q={"$orderby":{"lastName":"ASC"},"$where":{"lastName":{"$like":"S%"}}}' \
  -H "Authorization: Basic $(echo -n 'developer:SecurePass123!' | base64)"

Python Implementation

Installation

pip install requests urllib3

Basic Python Client

import requests
import json
from requests.auth import HTTPBasicAuth
import urllib3

# Disable SSL warnings for self-signed certificates (development only)
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)

class MySQLRestClient:
    def __init__(self, base_url, username, password):
        self.base_url = base_url.rstrip('/')
        self.auth = HTTPBasicAuth(username, password)
        self.headers = {
            'Content-Type': 'application/json',
            'Accept': 'application/json'
        }
    
    def get_all(self, resource):
        """Retrieve all records from a resource"""
        url = f"{self.base_url}/{resource}"
        response = requests.get(url, auth=self.auth, headers=self.headers, verify=False)
        response.raise_for_status()
        return response.json()
    
    def get_by_id(self, resource, record_id):
        """Retrieve a single record by ID"""
        url = f"{self.base_url}/{resource}/{record_id}"
        response = requests.get(url, auth=self.auth, headers=self.headers, verify=False)
        response.raise_for_status()
        return response.json()
    
    def create(self, resource, data):
        """Create a new record"""
        url = f"{self.base_url}/{resource}"
        response = requests.post(url, auth=self.auth, headers=self.headers, 
                                json=data, verify=False)
        response.raise_for_status()
        return response.json()
    
    def update(self, resource, record_id, data):
        """Update an existing record"""
        url = f"{self.base_url}/{resource}/{record_id}"
        response = requests.put(url, auth=self.auth, headers=self.headers, 
                               json=data, verify=False)
        response.raise_for_status()
        return response.json()
    
    def delete(self, resource, record_id):
        """Delete a record"""
        url = f"{self.base_url}/{resource}/{record_id}"
        response = requests.delete(url, auth=self.auth, headers=self.headers, verify=False)
        response.raise_for_status()
        return response.status_code == 204 or response.status_code == 200
    
    def query(self, resource, filter_obj):
        """Execute a filtered query"""
        url = f"{self.base_url}/{resource}"
        params = {'q': json.dumps(filter_obj)}
        response = requests.get(url, auth=self.auth, headers=self.headers, 
                               params=params, verify=False)
        response.raise_for_status()
        return response.json()


# Usage Example
def main():
    # Initialize client
    client = MySQLRestClient(
        base_url='https://localhost:8443/myService/sakila',
        username='developer',
        password='SecurePass123!'
    )
    
    try:
        # Get all actors
        print("Fetching all actors...")
        actors = client.get_all('actor')
        print(f"Found {len(actors.get('items', []))} actors")
        
        # Get specific actor
        print("\nFetching actor with ID 1...")
        actor = client.get_by_id('actor', 1)
        print(f"Actor: {actor.get('firstName')} {actor.get('lastName')}")
        
        # Create new actor
        print("\nCreating new actor...")
        new_actor = client.create('actor', {
            'firstName': 'Tom',
            'lastName': 'Hanks'
        })
        print(f"Created actor with ID: {new_actor.get('actorId')}")
        
        # Update actor
        print("\nUpdating actor...")
        updated_actor = client.update('actor', new_actor.get('actorId'), {
            'firstName': 'Thomas',
            'lastName': 'Hanks'
        })
        print(f"Updated actor: {updated_actor.get('firstName')} {updated_actor.get('lastName')}")
        
        # Query with filter
        print("\nQuerying actors with last name starting with 'S'...")
        filtered = client.query('actor', {
            '$where': {
                'lastName': {'$like': 'S%'}
            },
            '$orderby': {
                'lastName': 'ASC'
            }
        })
        for actor in filtered.get('items', [])[:5]:
            print(f"  - {actor.get('firstName')} {actor.get('lastName')}")
        
        # Delete actor
        print(f"\nDeleting actor {new_actor.get('actorId')}...")
        success = client.delete('actor', new_actor.get('actorId'))
        print(f"Delete successful: {success}")
        
    except requests.exceptions.HTTPError as e:
        print(f"HTTP Error: {e}")
        print(f"Response: {e.response.text}")
    except Exception as e:
        print(f"Error: {e}")


if __name__ == '__main__':
    main()

Advanced Python Example with Connection Pooling

import requests
from requests.adapters import HTTPAdapter
from urllib3.util.retry import Retry
from requests.auth import HTTPBasicAuth
import urllib3

urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)

class AdvancedMySQLRestClient:
    def __init__(self, base_url, username, password, pool_connections=10, pool_maxsize=20):
        self.base_url = base_url.rstrip('/')
        self.auth = HTTPBasicAuth(username, password)
        
        # Configure session with connection pooling and retry strategy
        self.session = requests.Session()
        
        retry_strategy = Retry(
            total=3,
            backoff_factor=1,
            status_forcelist=[429, 500, 502, 503, 504],
            allowed_methods=["HEAD", "GET", "OPTIONS", "POST", "PUT", "DELETE"]
        )
        
        adapter = HTTPAdapter(
            pool_connections=pool_connections,
            pool_maxsize=pool_maxsize,
            max_retries=retry_strategy
        )
        
        self.session.mount("http://", adapter)
        self.session.mount("https://", adapter)
        self.session.auth = self.auth
        self.session.headers.update({
            'Content-Type': 'application/json',
            'Accept': 'application/json'
        })
    
    def batch_create(self, resource, records):
        """Create multiple records efficiently"""
        results = []
        for record in records:
            try:
                url = f"{self.base_url}/{resource}"
                response = self.session.post(url, json=record, verify=False)
                response.raise_for_status()
                results.append({'success': True, 'data': response.json()})
            except Exception as e:
                results.append({'success': False, 'error': str(e)})
        return results
    
    def paginated_query(self, resource, filter_obj=None, limit=100):
        """Retrieve records with pagination"""
        offset = 0
        all_items = []
        
        while True:
            query = filter_obj.copy() if filter_obj else {}
            query['$limit'] = limit
            query['$offset'] = offset
            
            url = f"{self.base_url}/{resource}"
            response = self.session.get(
                url, 
                params={'q': json.dumps(query)}, 
                verify=False
            )
            response.raise_for_status()
            
            data = response.json()
            items = data.get('items', [])
            
            if not items:
                break
                
            all_items.extend(items)
            offset += limit
            
            if len(items) < limit:
                break
        
        return all_items
    
    def close(self):
        """Close the session"""
        self.session.close()


# Usage
if __name__ == '__main__':
    client = AdvancedMySQLRestClient(
        base_url='https://localhost:8443/myService/sakila',
        username='developer',
        password='SecurePass123!'
    )
    
    try:
        # Batch create
        new_actors = [
            {'firstName': 'Robert', 'lastName': 'Downey'},
            {'firstName': 'Chris', 'lastName': 'Evans'},
            {'firstName': 'Scarlett', 'lastName': 'Johansson'}
        ]
        results = client.batch_create('actor', new_actors)
        print(f"Created {sum(1 for r in results if r['success'])} actors")
        
        # Paginated query
        all_actors = client.paginated_query('actor', limit=50)
        print(f"Retrieved {len(all_actors)} actors total")
        
    finally:
        client.close()

C# Implementation

Installation

dotnet add package Newtonsoft.Json
dotnet add package RestSharp

Basic C# Client

using System;
using System.Collections.Generic;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text;
using System.Threading.Tasks;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

namespace MySQLRestClient
{
    public class MySQLRestClient : IDisposable
    {
        private readonly HttpClient _httpClient;
        private readonly string _baseUrl;

        public MySQLRestClient(string baseUrl, string username, string password)
        {
            _baseUrl = baseUrl.TrimEnd('/');
            _httpClient = new HttpClient();
            
            // Configure authentication
            var authToken = Convert.ToBase64String(
                Encoding.ASCII.GetBytes($"{username}:{password}")
            );
            _httpClient.DefaultRequestHeaders.Authorization = 
                new AuthenticationHeaderValue("Basic", authToken);
            
            // Set headers
            _httpClient.DefaultRequestHeaders.Accept.Add(
                new MediaTypeWithQualityHeaderValue("application/json")
            );
            
            // Ignore SSL certificate errors (development only)
            var handler = new HttpClientHandler
            {
                ServerCertificateCustomValidationCallback = (message, cert, chain, errors) => true
            };
            _httpClient = new HttpClient(handler);
            _httpClient.DefaultRequestHeaders.Authorization = 
                new AuthenticationHeaderValue("Basic", authToken);
            _httpClient.DefaultRequestHeaders.Accept.Add(
                new MediaTypeWithQualityHeaderValue("application/json")
            );
        }

        public async Task<JObject> GetAllAsync(string resource)
        {
            var url = $"{_baseUrl}/{resource}";
            var response = await _httpClient.GetAsync(url);
            response.EnsureSuccessStatusCode();
            
            var content = await response.Content.ReadAsStringAsync();
            return JObject.Parse(content);
        }

        public async Task<JObject> GetByIdAsync(string resource, int id)
        {
            var url = $"{_baseUrl}/{resource}/{id}";
            var response = await _httpClient.GetAsync(url);
            response.EnsureSuccessStatusCode();
            
            var content = await response.Content.ReadAsStringAsync();
            return JObject.Parse(content);
        }

        public async Task<JObject> CreateAsync(string resource, object data)
        {
            var url = $"{_baseUrl}/{resource}";
            var json = JsonConvert.SerializeObject(data);
            var content = new StringContent(json, Encoding.UTF8, "application/json");
            
            var response = await _httpClient.PostAsync(url, content);
            response.EnsureSuccessStatusCode();
            
            var responseContent = await response.Content.ReadAsStringAsync();
            return JObject.Parse(responseContent);
        }

        public async Task<JObject> UpdateAsync(string resource, int id, object data)
        {
            var url = $"{_baseUrl}/{resource}/{id}";
            var json = JsonConvert.SerializeObject(data);
            var content = new StringContent(json, Encoding.UTF8, "application/json");
            
            var response = await _httpClient.PutAsync(url, content);
            response.EnsureSuccessStatusCode();
            
            var responseContent = await response.Content.ReadAsStringAsync();
            return JObject.Parse(responseContent);
        }

        public async Task<bool> DeleteAsync(string resource, int id)
        {
            var url = $"{_baseUrl}/{resource}/{id}";
            var response = await _httpClient.DeleteAsync(url);
            return response.IsSuccessStatusCode;
        }

        public async Task<JObject> QueryAsync(string resource, object filter)
        {
            var filterJson = JsonConvert.SerializeObject(filter);
            var url = $"{_baseUrl}/{resource}?q={Uri.EscapeDataString(filterJson)}";
            
            var response = await _httpClient.GetAsync(url);
            response.EnsureSuccessStatusCode();
            
            var content = await response.Content.ReadAsStringAsync();
            return JObject.Parse(content);
        }

        public void Dispose()
        {
            _httpClient?.Dispose();
        }
    }

    // Usage Example
    class Program
    {
        static async Task Main(string[] args)
        {
            using var client = new MySQLRestClient(
                "https://localhost:8443/myService/sakila",
                "developer",
                "SecurePass123!"
            );

            try
            {
                // Get all actors
                Console.WriteLine("Fetching all actors...");
                var actors = await client.GetAllAsync("actor");
                var items = actors["items"] as JArray;
                Console.WriteLine($"Found {items?.Count ?? 0} actors");

                // Get specific actor
                Console.WriteLine("\nFetching actor with ID 1...");
                var actor = await client.GetByIdAsync("actor", 1);
                Console.WriteLine($"Actor: {actor["firstName"]} {actor["lastName"]}");

                // Create new actor
                Console.WriteLine("\nCreating new actor...");
                var newActor = await client.CreateAsync("actor", new
                {
                    firstName = "Leonardo",
                    lastName = "DiCaprio"
                });
                var actorId = newActor["actorId"].Value<int>();
                Console.WriteLine($"Created actor with ID: {actorId}");

                // Update actor
                Console.WriteLine("\nUpdating actor...");
                var updatedActor = await client.UpdateAsync("actor", actorId, new
                {
                    firstName = "Leo",
                    lastName = "DiCaprio"
                });
                Console.WriteLine($"Updated actor: {updatedActor["firstName"]} {updatedActor["lastName"]}");

                // Query with filter
                Console.WriteLine("\nQuerying actors with last name starting with 'D'...");
                var filtered = await client.QueryAsync("actor", new
                {
                    @where = new
                    {
                        lastName = new { @like = "D%" }
                    },
                    orderby = new
                    {
                        lastName = "ASC"
                    }
                });
                
                var filteredItems = filtered["items"] as JArray;
                foreach (var item in filteredItems.Take(5))
                {
                    Console.WriteLine($"  - {item["firstName"]} {item["lastName"]}");
                }

                // Delete actor
                Console.WriteLine($"\nDeleting actor {actorId}...");
                var success = await client.DeleteAsync("actor", actorId);
                Console.WriteLine($"Delete successful: {success}");
            }
            catch (HttpRequestException ex)
            {
                Console.WriteLine($"HTTP Error: {ex.Message}");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}");
            }
        }
    }
}

Advanced C# Client with Strongly-Typed Models

using System;
using System.Collections.Generic;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text;
using System.Threading.Tasks;
using Newtonsoft.Json;

namespace MySQLRestClient.Models
{
    // Model classes
    public class Actor
    {
        [JsonProperty("actorId")]
        public int ActorId { get; set; }

        [JsonProperty("firstName")]
        public string FirstName { get; set; }

        [JsonProperty("lastName")]
        public string LastName { get; set; }

        [JsonProperty("lastUpdate")]
        public DateTime LastUpdate { get; set; }
    }

    public class ApiResponse<T>
    {
        [JsonProperty("items")]
        public List<T> Items { get; set; }

        [JsonProperty("count")]
        public int Count { get; set; }

        [JsonProperty("offset")]
        public int Offset { get; set; }

        [JsonProperty("limit")]
        public int Limit { get; set; }

        [JsonProperty("hasMore")]
        public bool HasMore { get; set; }
    }

    // Strongly-typed client
    public class TypedMySQLRestClient<T> : IDisposable where T : class
    {
        private readonly HttpClient _httpClient;
        private readonly string _baseUrl;
        private readonly string _resource;

        public TypedMySQLRestClient(string baseUrl, string resource, string username, string password)
        {
            _baseUrl = baseUrl.TrimEnd('/');
            _resource = resource;
            
            var handler = new HttpClientHandler
            {
                ServerCertificateCustomValidationCallback = (message, cert, chain, errors) => true
            };
            
            _httpClient = new HttpClient(handler);
            
            var authToken = Convert.ToBase64String(
                Encoding.ASCII.GetBytes($"{username}:{password}")
            );
            
            _httpClient.DefaultRequestHeaders.Authorization = 
                new AuthenticationHeaderValue("Basic", authToken);
            _httpClient.DefaultRequestHeaders.Accept.Add(
                new MediaTypeWithQualityHeaderValue("application/json")
            );
        }

        public async Task<ApiResponse<T>> GetAllAsync(int limit = 100, int offset = 0)
        {
            var url = $"{_baseUrl}/{_resource}?limit={limit}&offset={offset}";
            var response = await _httpClient.GetAsync(url);
            response.EnsureSuccessStatusCode();
            
            var content = await response.Content.ReadAsStringAsync();
            return JsonConvert.DeserializeObject<ApiResponse<T>>(content);
        }

        public async Task<T> GetByIdAsync(int id)
        {
            var url = $"{_baseUrl}/{_resource}/{id}";
            var response = await _httpClient.GetAsync(url);
            response.EnsureSuccessStatusCode();
            
            var content = await response.Content.ReadAsStringAsync();
            return JsonConvert.DeserializeObject<T>(content);
        }

        public async Task<T> CreateAsync(T entity)
        {
            var url = $"{_baseUrl}/{_resource}";
            var json = JsonConvert.SerializeObject(entity);
            var content = new StringContent(json, Encoding.UTF8, "application/json");
            
            var response = await _httpClient.PostAsync(url, content);
            response.EnsureSuccessStatusCode();
            
            var responseContent = await response.Content.ReadAsStringAsync();
            return JsonConvert.DeserializeObject<T>(responseContent);
        }

        public void Dispose()
        {
            _httpClient?.Dispose();
        }
    }

    // Usage
    class Program
    {
        static async Task Main(string[] args)
        {
            using var actorClient = new TypedMySQLRestClient<Actor>(
                "https://localhost:8443/myService/sakila",
                "actor",
                "developer",
                "SecurePass123!"
            );

            // Get all actors with pagination
            var response = await actorClient.GetAllAsync(limit: 10, offset: 0);
            Console.WriteLine($"Retrieved {response.Items.Count} actors");
            
            foreach (var actor in response.Items)
            {
                Console.WriteLine($"{actor.ActorId}: {actor.FirstName} {actor.LastName}");
            }

            // Create new actor
            var newActor = new Actor
            {
                FirstName = "Brad",
                LastName = "Pitt"
            };
            
            var created = await actorClient.CreateAsync(newActor);
            Console.WriteLine($"Created actor with ID: {created.ActorId}");
        }
    }
}

Best Practices

1. Security

• Always use HTTPS in production
• Never hardcode credentials - use environment variables or secure vaults
• Implement proper authentication - Use OAuth2 for public-facing services
• Validate input data on both client and server side
• Use role-based access control for different user types

2. Performance

• Enable connection pooling in your HTTP clients
• Implement caching for frequently accessed data
• Use pagination for large datasets
• Leverage filtering to reduce data transfer
• Monitor query performance and optimize slow queries

3. Error Handling

# Python example
try:
    result = client.get_by_id('actor', 999)
except requests.exceptions.HTTPError as e:
    if e.response.status_code == 404:
        print("Actor not found")
    elif e.response.status_code == 401:
        print("Authentication failed")
    elif e.response.status_code == 403:
        print("Access forbidden")
    else:
        print(f"HTTP error occurred: {e}")
except requests.exceptions.ConnectionError:
    print("Failed to connect to the server")
except Exception as e:
    print(f"An unexpected error occurred: {e}")

// C# example
try
{
    var actor = await client.GetByIdAsync("actor", 999);
}
catch (HttpRequestException ex) when (ex.StatusCode == System.Net.HttpStatusCode.NotFound)
{
    Console.WriteLine("Actor not found");
}
catch (HttpRequestException ex) when (ex.StatusCode == System.Net.HttpStatusCode.Unauthorized)
{
    Console.WriteLine("Authentication failed");
}
catch (HttpRequestException ex)
{
    Console.WriteLine($"HTTP error: {ex.Message}");
}
catch (Exception ex)
{
    Console.WriteLine($"Unexpected error: {ex.Message}");
}

4. Development Workflow

1. Start with development mode - Use MRS development router for rapid prototyping
2. Test thoroughly - Write comprehensive tests for all endpoints
3. Use version control - Track MRS service definitions
4. Document your API - Generate and maintain SDK documentation
5. Monitor in production - Use MySQL Router metrics and logs

Advanced Features

Stored Procedures as REST Endpoints

-- Create a stored procedure
DELIMITER //
CREATE PROCEDURE GetActorFilmCount(IN actorId INT)
BEGIN
    SELECT COUNT(*) as filmCount
    FROM film_actor
    WHERE actor_id = actorId;
END //
DELIMITER ;

-- Enable it as REST endpoint
CREATE REST PROCEDURE /getActorFilmCount
  ON SERVICE /myService SCHEMA /sakila
  AS `sakila`.`GetActorFilmCount`;

Call from Python:

# Call stored procedure
result = client.session.post(
    f"{client.base_url}/getActorFilmCount",
    json={"actorId": 1},
    auth=client.auth,
    verify=False
)
print(result.json())

SDK Generation

Generate type-safe SDKs directly from MRS:

# Generate TypeScript SDK
mysqlsh dba@localhost --py -e \
  'mrs.dump.sdk_service_files(
    directory="/path/to/project/sdk",
    options={
      "sdk_language": "TypeScript",
      "service_url": "https://api.example.com/myService"
    }
  )'

# Generate Python SDK
mysqlsh dba@localhost --py -e \
  'mrs.dump.sdk_service_files(
    directory="/path/to/project/sdk",
    options={
      "sdk_language": "Python",
      "service_url": "https://api.example.com/myService"
    }
  )'

Read Your Own Writes (Cluster Consistency)

For MySQL InnoDB Cluster deployments:

# Enable read-your-own-writes consistency
result = client.session.get(
    f"{client.base_url}/actor",
    headers={
        'X-Read-Own-Writes': 'true'
    },
    auth=client.auth,
    verify=False
)

Troubleshooting

Common Issues

1. Connection Refused

# Check if MySQL Router is running
ps aux | grep mysqlrouter

# Check router logs
tail -f /path/to/router/log/mysqlrouter.log

2. Authentication Failures

-- Verify user exists
SELECT * FROM mysql_rest_service_metadata.auth_user;

-- Reset user password
ALTER REST AUTH APP MRS
  ON SERVICE /myService
  ALTER USER "developer"
  IDENTIFIED BY "NewPassword123!";

3. Service Not Found

-- List all services
SHOW REST SERVICES;

-- Verify service is published
SELECT * FROM mysql_rest_service_metadata.service
WHERE url_context_root = '/myService';

Conclusion

The MySQL REST Service represents a powerful, integrated solution for exposing MySQL data through RESTful APIs. By following this guide, you should now be able to:

• Set up and configure MRS in both development and production environments
• Create and manage REST services with proper authentication
• Implement robust clients in Python and C#
• Apply best practices for security, performance, and error handling
• Leverage advanced features like stored procedures and SDK generation

The native integration with MySQL, combined with enterprise-grade features like OAuth2 support, connection pooling, and cluster consistency, makes MRS an excellent choice for modern application development.

For more information, consult the official MySQL REST Service documentation.

---

Happy coding!