MySQL REST Service: A Developer’s Guide to Building Modern Data APIs – BizIQ

Introduction

The MySQL REST Service (MRS) represents a paradigm shift in how developers interact with MySQL databases. Rather than writing traditional database connection code, developers can now expose their MySQL data through standardized REST endpoints with minimal configuration. This guide explores the practical aspects of setting up and utilizing MySQL REST Service from a developer’s perspective, with code examples in both Python and C#.

What is MySQL REST Service?

MySQL REST Service is a native component of the MySQL ecosystem that automatically exposes database tables, views, and stored procedures as RESTful API endpoints. Built on MySQL Server 8.0.39+ and MySQL Router 9.3.1+, it eliminates the boilerplate code typically required to create REST APIs while maintaining robust security, performance, and standards compliance.

The service handles the complexity of translating HTTP requests into SQL operations, managing authentication, handling filtering and pagination, and returning properly formatted JSON responses—all without requiring developers to write application code for these common patterns.

Architecture Overview

A complete MySQL REST Service deployment consists of three primary components:

MySQL Solution: This serves as the backend database engine and stores the MRS metadata schema (mysql_rest_service_metadata). In development environments, this can be a standalone MySQL Server instance, while production deployments typically use InnoDB Clusters or HeatWave for high availability.

MySQL Router: Acts as the HTTP/HTTPS gateway that exposes REST endpoints to clients. It handles routing, authentication, and protocol translation. Development setups run Router in developer mode, while production environments run multiple Router instances behind load balancers.

Configuration and Management Layer: The MySQL Shell for VS Code extension provides the graphical interface for configuring services, while the REST SQL extension enables command-line configuration.

Setting Up MySQL REST Service

Prerequisites

Before configuring MySQL REST Service, ensure you have the following installed:

MySQL Server 8.0.39 or later
MySQL Shell 9.4.0 or higher
MySQL Router 9.3.1 or later
For development: VS Code with MySQL Shell for VS Code extension

Initial Configuration

The recommended approach is to use VS Code with the MySQL Shell extension, which streamlines HTTPS certificate installation and Router bootstrapping.

Begin by establishing a database connection in VS Code. Navigate to the DATABASE CONNECTIONS view and create a new connection with appropriate credentials. Once connected, right-click on the connection and select “Configure MySQL REST Service.”

The configuration wizard will:

Create the mysql_rest_service_metadata schema on your MySQL instance
Establish a REST user account with authentication credentials
Initialize Router for local development

The REST user requires a password with minimum 8 characters, including uppercase letters, lowercase letters, numbers, and special characters.

Creating a REST Service

Once the instance is configured, create a new REST service by right-clicking the MySQL REST Service entry and selecting “Add REST Service.”

Configure the following properties:

Service Name: A unique identifier for your service (e.g., /customerAPI)
Comments: Description of the service’s purpose
Host: The hostname where the service is exposed
Port: The HTTPS port (typically 8444 for development)

After creation, the service exists but is not yet published. You must explicitly publish it using SQL commands to make it available through HTTP/HTTPS.

Exposing Database Objects

REST-Enabling Tables

To expose a table through the REST API, the table must have a primary key. Tables without primary keys cannot be REST-enabled due to the inability to uniquely identify rows.

Using the MySQL Shell REST SQL extension, enable a table with:

CREATE OR REPLACE REST DATABASEOBJECT sakila.actor
  PUBLISHED FALSE
  MEDIA_TYPE APPLICATION/JSON
  REQUEST_PATH /actor;

This creates a REST endpoint at SERVICE_URL/sakila/actor/ that supports standard HTTP methods:

GET /actor — Retrieve all records with pagination and filtering support
GET /actor/{id} — Retrieve a specific record by primary key
POST /actor — Insert a new record
PUT /actor/{id} — Update a specific record
DELETE /actor/{id} — Delete a specific record

REST-Enabling Views

Views provide read-only REST endpoints ideal for complex queries, aggregations, or computed fields:

CREATE OR REPLACE REST DATABASEOBJECT sakila.actor_film_count
  PUBLISHED FALSE
  MEDIA_TYPE APPLICATION/JSON
  REQUEST_PATH /actor_film_count;

Views cannot be modified through PUT or POST requests, limiting the endpoint to GET operations.

REST-Enabling Stored Procedures

Procedures expose custom business logic as callable REST endpoints:

CREATE OR REPLACE REST DATABASEOBJECT sakila.get_actor_films
  PUBLISHED FALSE
  MEDIA_TYPE APPLICATION/JSON
  REQUEST_PATH /actor_films;

API Request and Response Format

MySQL REST Service uses JSON for all request and response bodies, employing camelCase field names (converted from snake_case database columns).

Basic GET Request

GET /mrs/sakila/actor/1
Authorization: Basic base64(username:password)

Response:

{
  "links": [
    {
      "rel": "self",
      "href": "/mrs/sakila/actor/1"
    }
  ],
  "actorId": 1,
  "firstName": "PENELOPE",
  "lastName": "GUINESS",
  "lastUpdate": "2006-02-15T03:34:33.000000"
}

POST Request (Insert)

POST /mrs/sakila/actor/
Authorization: Basic base64(username:password)
Content-Type: application/json

{
  "firstName": "JOHN",
  "lastName": "DOE"
}

Response:

{
  "links": [
    {
      "rel": "self",
      "href": "/mrs/sakila/actor/201"
    }
  ],
  "actorId": 201,
  "firstName": "JOHN",
  "lastName": "DOE",
  "lastUpdate": "2024-01-15T10:30:45.000000"
}

Filtering and Pagination

Retrieve filtered results using the q parameter with a JSON filter object:

GET /mrs/sakila/actor/?q={"firstName":"BRUCE","limit":10,"offset":0}

The filter object supports:

Field conditions: {"firstName":"BRUCE"} matches exact values
Operators: {"actorId":{"$gt":50}} for greater than comparisons
Limit and offset: "limit":10,"offset":20 for pagination
Sorting: "orderBy":"firstName ASC"

Python Client Implementation

Python developers can interact with MySQL REST Service using the requests library, which provides a clean interface for HTTP operations.

Basic Setup

import requests
import json
from typing import List, Dict, Optional

class MySQLRESTClient:
    def __init__(self, base_url: str, username: str, password: str):
        self.base_url = base_url
        self.auth = (username, password)
        self.session = requests.Session()
        self.session.auth = self.auth
        self.session.headers.update({'Content-Type': 'application/json'})
    
    def get_all(self, resource: str, limit: int = 10, 
                offset: int = 0) -> Dict:
        """Retrieve multiple records with pagination."""
        filter_obj = {"limit": limit, "offset": offset}
        params = {"q": json.dumps(filter_obj)}
        
        url = f"{self.base_url}/{resource}/"
        response = self.session.get(url, params=params)
        response.raise_for_status()
        return response.json()
    
    def get_by_id(self, resource: str, resource_id: str) -> Dict:
        """Retrieve a single record by ID."""
        url = f"{self.base_url}/{resource}/{resource_id}"
        response = self.session.get(url)
        response.raise_for_status()
        return response.json()
    
    def create(self, resource: str, data: Dict) -> Dict:
        """Create a new record."""
        url = f"{self.base_url}/{resource}/"
        response = self.session.post(url, json=data)
        response.raise_for_status()
        return response.json()
    
    def update(self, resource: str, resource_id: str, 
               data: Dict) -> Dict:
        """Update an existing record."""
        url = f"{self.base_url}/{resource}/{resource_id}"
        response = self.session.put(url, json=data)
        response.raise_for_status()
        return response.json()
    
    def delete(self, resource: str, resource_id: str) -> bool:
        """Delete a record."""
        url = f"{self.base_url}/{resource}/{resource_id}"
        response = self.session.delete(url)
        response.raise_for_status()
        return response.status_code == 204
    
    def filter(self, resource: str, filter_criteria: Dict) -> Dict:
        """Retrieve records matching filter criteria."""
        params = {"q": json.dumps(filter_criteria)}
        url = f"{self.base_url}/{resource}/"
        response = self.session.get(url, params=params)
        response.raise_for_status()
        return response.json()

Usage Example

# Initialize client
client = MySQLRESTClient(
    base_url="https://localhost:8444/mrs/sakila",
    username="restuser",
    password="SecurePassword123!"
)

# Retrieve all actors with pagination
actors = client.get_all("actor", limit=5, offset=0)
print(f"Retrieved {len(actors)} actors")

# Retrieve specific actor
actor = client.get_by_id("actor", "1")
print(f"Actor: {actor['firstName']} {actor['lastName']}")

# Create new actor
new_actor = client.create("actor", {
    "firstName": "JANE",
    "lastName": "SMITH"
})
print(f"Created actor with ID: {new_actor['actorId']}")

# Update actor
updated = client.update("actor", "201", {
    "firstName": "JANET",
    "lastName": "SMITH"
})
print(f"Updated: {updated['firstName']} {updated['lastName']}")

# Filter with criteria
filtered = client.filter("actor", {
    "firstName": "PENELOPE",
    "limit": 10
})
print(f"Found {len(filtered)} actors named PENELOPE")

# Delete actor
deleted = client.delete("actor", "201")
print(f"Delete successful: {deleted}")

Error Handling

Enhance the client with comprehensive error handling:

import logging
from requests.exceptions import RequestException, HTTPError

logger = logging.getLogger(__name__)

class MySQLRESTClientWithErrorHandling(MySQLRESTClient):
    def _make_request(self, method: str, url: str, 
                      **kwargs) -> requests.Response:
        """Wrapper for all requests with error handling."""
        try:
            response = self.session.request(method, url, **kwargs)
            response.raise_for_status()
            return response
        except HTTPError as e:
            logger.error(f"HTTP error occurred: {e.response.status_code} - {e.response.text}")
            raise
        except RequestException as e:
            logger.error(f"Request error: {e}")
            raise
    
    def get_all(self, resource: str, limit: int = 10, 
                offset: int = 0) -> List[Dict]:
        """Retrieve multiple records with error handling."""
        try:
            filter_obj = {"limit": limit, "offset": offset}
            params = {"q": json.dumps(filter_obj)}
            url = f"{self.base_url}/{resource}/"
            
            response = self._make_request("GET", url, params=params)
            
            # Handle paginated responses
            data = response.json()
            return data.get("items", []) if isinstance(data, dict) else data
        except Exception as e:
            logger.error(f"Failed to retrieve {resource}: {e}")
            return []

C# Client Implementation

C# developers can leverage the HttpClient class for REST API interactions, along with LINQ and JSON serialization libraries.

Basic Setup

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

public class MySQLRESTClient
{
    private readonly HttpClient _httpClient;
    private readonly string _baseUrl;
    private readonly string _username;
    private readonly string _password;
    
    public MySQLRESTClient(string baseUrl, string username, string password)
    {
        _baseUrl = baseUrl.TrimEnd('/');
        _username = username;
        _password = password;
        
        _httpClient = new HttpClient();
        _httpClient.DefaultRequestHeaders.Accept.Add(
            new MediaTypeWithQualityHeaderValue("application/json"));
        
        // Set up basic authentication
        var auth = Convert.ToBase64String(
            Encoding.UTF8.GetBytes($"{username}:{password}"));
        _httpClient.DefaultRequestHeaders.Authorization = 
            new AuthenticationHeaderValue("Basic", auth);
    }
    
    public async Task<T> GetAsync<T>(string resource, string id = null)
    {
        var url = id == null 
            ? $"{_baseUrl}/{resource}/" 
            : $"{_baseUrl}/{resource}/{id}";
        
        using (var response = await _httpClient.GetAsync(url))
        {
            response.EnsureSuccessStatusCode();
            var content = await response.Content.ReadAsStringAsync();
            return JsonSerializer.Deserialize<T>(content);
        }
    }
    
    public async Task<List<T>> GetAllAsync<T>(string resource, 
        int limit = 10, int offset = 0)
    {
        var filter = new { limit = limit, offset = offset };
        var filterJson = JsonSerializer.Serialize(filter);
        var url = $"{_baseUrl}/{resource}/?q={Uri.EscapeDataString(filterJson)}";
        
        using (var response = await _httpClient.GetAsync(url))
        {
            response.EnsureSuccessStatusCode();
            var content = await response.Content.ReadAsStringAsync();
            
            // Parse the response - may be wrapped in pagination metadata
            var jsonDoc = JsonDocument.Parse(content);
            var options = new JsonSerializerOptions { PropertyNameCaseInsensitive = true };
            
            // Assuming the items are in root or under an "items" key
            return JsonSerializer.Deserialize<List<T>>(
                jsonDoc.RootElement.ToString(), options);
        }
    }
    
    public async Task<T> CreateAsync<T>(string resource, object data)
    {
        var url = $"{_baseUrl}/{resource}/";
        var jsonContent = JsonSerializer.Serialize(data);
        var content = new StringContent(jsonContent, Encoding.UTF8, "application/json");
        
        using (var response = await _httpClient.PostAsync(url, content))
        {
            response.EnsureSuccessStatusCode();
            var responseContent = await response.Content.ReadAsStringAsync();
            return JsonSerializer.Deserialize<T>(responseContent);
        }
    }
    
    public async Task<T> UpdateAsync<T>(string resource, string id, object data)
    {
        var url = $"{_baseUrl}/{resource}/{id}";
        var jsonContent = JsonSerializer.Serialize(data);
        var content = new StringContent(jsonContent, Encoding.UTF8, "application/json");
        
        using (var response = await _httpClient.PutAsync(url, content))
        {
            response.EnsureSuccessStatusCode();
            var responseContent = await response.Content.ReadAsStringAsync();
            return JsonSerializer.Deserialize<T>(responseContent);
        }
    }
    
    public async Task<bool> DeleteAsync(string resource, string id)
    {
        var url = $"{_baseUrl}/{resource}/{id}";
        
        using (var response = await _httpClient.DeleteAsync(url))
        {
            return response.IsSuccessStatusCode;
        }
    }
    
    public async Task<List<T>> FilterAsync<T>(string resource, object filterCriteria)
    {
        var filterJson = JsonSerializer.Serialize(filterCriteria);
        var url = $"{_baseUrl}/{resource}/?q={Uri.EscapeDataString(filterJson)}";
        
        using (var response = await _httpClient.GetAsync(url))
        {
            response.EnsureSuccessStatusCode();
            var content = await response.Content.ReadAsStringAsync();
            var options = new JsonSerializerOptions { PropertyNameCaseInsensitive = true };
            return JsonSerializer.Deserialize<List<T>>(content, options);
        }
    }
}

Model Definition

using System;
using System.Text.Json.Serialization;

public class Actor
{
    [JsonPropertyName("actorId")]
    public int ActorId { get; set; }
    
    [JsonPropertyName("firstName")]
    public string FirstName { get; set; }
    
    [JsonPropertyName("lastName")]
    public string LastName { get; set; }
    
    [JsonPropertyName("lastUpdate")]
    public DateTime LastUpdate { get; set; }
}

Usage Example

class Program
{
    static async Task Main(string[] args)
    {
        var client = new MySQLRESTClient(
            baseUrl: "https://localhost:8444/mrs/sakila",
            username: "restuser",
            password: "SecurePassword123!"
        );
        
        // Retrieve all actors
        var actors = await client.GetAllAsync<Actor>("actor", limit: 5);
        Console.WriteLine($"Retrieved {actors.Count} actors");
        
        // Retrieve specific actor
        var actor = await client.GetAsync<Actor>("actor", "1");
        Console.WriteLine($"Actor: {actor.FirstName} {actor.LastName}");
        
        // Create new actor
        var newActor = new { firstName = "JOHN", lastName = "DOE" };
        var created = await client.CreateAsync<Actor>("actor", newActor);
        Console.WriteLine($"Created actor with ID: {created.ActorId}");
        
        // Update actor
        var updateData = new { firstName = "JANET", lastName = "SMITH" };
        var updated = await client.UpdateAsync<Actor>("actor", "201", updateData);
        Console.WriteLine($"Updated: {updated.FirstName} {updated.LastName}");
        
        // Filter with criteria
        var filter = new { firstName = "PENELOPE", limit = 10 };
        var filtered = await client.FilterAsync<Actor>("actor", filter);
        Console.WriteLine($"Found {filtered.Count} actors named PENELOPE");
        
        // Delete actor
        var deleted = await client.DeleteAsync("actor", "201");
        Console.WriteLine($"Delete successful: {deleted}");
    }
}

Advanced C# with Async/Await and Error Handling

using System;
using System.Net.Http;
using System.Threading.Tasks;

public class MySQLRESTClientWithErrorHandling : MySQLRESTClient
{
    private readonly ILogger<MySQLRESTClient> _logger;
    
    public MySQLRESTClientWithErrorHandling(
        string baseUrl, string username, string password,
        ILogger<MySQLRESTClient> logger) 
        : base(baseUrl, username, password)
    {
        _logger = logger;
    }
    
    public async Task<T> GetAsyncSafe<T>(string resource, string id = null)
    {
        try
        {
            return await GetAsync<T>(resource, id);
        }
        catch (HttpRequestException ex)
        {
            _logger.LogError($"HTTP request failed: {ex.Message}");
            throw;
        }
        catch (JsonException ex)
        {
            _logger.LogError($"JSON deserialization failed: {ex.Message}");
            throw;
        }
        catch (Exception ex)
        {
            _logger.LogError($"Unexpected error: {ex.Message}");
            throw;
        }
    }
}

Best Practices

Use HTTPS in Production: MySQL REST Service supports both HTTP and HTTPS, but production deployments must use HTTPS to protect transmitted credentials and data. The REST service protocol defaults to HTTPS and should not be changed except in specific scenarios with reverse proxies.

Implement Proper Authentication: Use strong passwords containing uppercase, lowercase, numbers, and special characters. Consider OAuth2 authentication for applications requiring enhanced security and user-specific access control.

Enable Filtering and Pagination: Always use the query filter parameter to limit result sets, preventing large data transfers and reducing server load. Implement pagination using limit and offset parameters.

Cache Responses Appropriately: For frequently accessed data that doesn’t change often, implement caching in your client code to reduce API calls and improve performance.

Monitor and Log Activity: Track all API requests and responses in development and production environments to identify bottlenecks, debug issues, and audit access patterns.

Version Your API: Use different service paths for different API versions to enable backward compatibility when making breaking changes to your data model.

Conclusion

MySQL REST Service bridges the gap between traditional database interactions and modern API-driven architectures. By eliminating boilerplate code and providing automatic REST endpoint generation, it enables developers to focus on business logic rather than infrastructure concerns. Whether using Python or C#, the provided client implementations offer a solid foundation for integrating MySQL REST Service into your applications. As you build more sophisticated services, remember to prioritize security, implement proper error handling, and follow RESTful principles to create maintainable and scalable data APIs.

Cookie	Duration	Description
cookielawinfo-checkbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.

Introduction

What is MySQL REST Service?

Architecture Overview

Setting Up MySQL REST Service

Prerequisites

Initial Configuration

Creating a REST Service

Exposing Database Objects

REST-Enabling Tables

REST-Enabling Views

REST-Enabling Stored Procedures

API Request and Response Format

Basic GET Request

POST Request (Insert)

Filtering and Pagination

Python Client Implementation

Basic Setup

Usage Example

Error Handling

C# Client Implementation

Basic Setup

Model Definition

Usage Example

Advanced C# with Async/Await and Error Handling

Best Practices

Conclusion

Comments

Leave a Reply Cancel reply