Best Practices for Building ORDS PL/SQL-Based REST APIs

Best Practices for Building ORDS PL/SQL-Based REST APIs

Jon Dixon's photo
Jon Dixon
·

11 min read

Table of contents

Introduction

Oracle REST Data Services (ORDS) is a robust and highly scalable tool for exposing REST APIs on top of the Oracle Database. ORDS has several features that allow you to quickly create REST APIs, not least of which is the ability to create a GET service based on a SQL statement.

This post will explore best practices for building more complex REST APIs based on PL/SQL rather than SQL. I will focus on practical ORDS-specific recommendations as opposed to generic REST principles.

Example Use Case

Throughout the post, I will discuss an example REST API that checks for duplicate customer records as part of a Master Data Management (MDM) solution. Client applications like e-Business Suite, Salesforce, or a consumer APEX Application can pass a customer name to the API and check for duplicate customers in the central customer MDM database. The service uses algorithms to find likely duplicates and returns them to the client with a match score. You can read more about how to do this in Sandra's post on Fuzzy Matching.

The code snippets below represent the ORDS Module, Template, and Handler for my example. I will refer to these throughout the post.

BEGIN  
  ords.define_module
   (p_module_name    => 'cn.customer.v1',
    p_base_path      => 'customer/v1/',
    p_status         => 'PUBLISHED',
    p_comments       => 'Customer APIs Version 1');

  ords.define_template
   (p_module_name => 'cn.customer.v1',
    p_pattern     => 'check_duplicates',
    p_priority    => 0,
    p_etag_type   => 'NONE',
    p_etag_query  => NULL,
    p_comments    => 'Check for Duplicate Customer');

  ords.define_handler
   (p_module_name   => 'cn.customer.v1',
    p_pattern       => 'check_duplicates',
    p_method        => 'POST',
    p_source_type   => 'plsql/block',
    p_mimes_allowed => 'application/json',
    p_source        => q'#
DECLARE
  -- Record type for log table.
  lr_api_log     api_log%ROWTYPE;
BEGIN
  -- Set the Current OAuth2 Client ID
  lr_api_log.current_user := :current_user;
  -- Get the Payload
  lr_api_log.request_payload := :body_text;
  -- Call PL/SQL Procedure.
  api_pk.duplicate_check
   (x_api_log => lr_api_log);
  -- Return the HTTP Status to the Client.
  :status := lr_api_log.http_status_code;
END;
#');

  ords.define_parameter
   (p_module_name        => 'cn.customer.v1',
    p_pattern            => 'check_duplicates',
    p_method             => 'POST',
    p_name               => 'X-ORDS-STATUS-CODE',
    p_bind_variable_name => 'status',
    p_source_type        => 'HEADER',
    p_param_type         => 'INT',
    p_access_method      => 'OUT');

  COMMIT;
END;

Security

Authentication

Unless you have unique security requirements, I recommend using ORDS OAuth2 security. It is faster and more secure than using database credentials and is recognized industry-wide.

Identifying the OAuth Client

In many cases, you must associate the OAuth client calling your API to an entity in your database. In my example, I want to know which system is calling the API and return different results accordingly.

When utilizing ORDS OAuth security, you can identify the OAuth Client ID using the implicit parameter current_user. This implicit parameter contains the value of the client_id column in the table user_ords_clients. You can see that I reference this implicit parameter in the handler in my example use case (see above).

Once you have the client_id, you can perform a lookup in your own custom table to get more information about the OAuth client. This could be looking up a user, customer, or in my case, a system associated with the OAuth client.

I suggest adding a key value to the OAuth client definition. In my example, I am storing the source system code SFDC in the description field of the OAuth client.

  oauth.create_client
   (p_name            => 'Salesforce',
    p_description     => 'SFDC',
    p_grant_type      => 'client_credentials',
    p_support_email   => 'a@bc.com',
    p_privilege_names => NULL);

You can then use the description field from user_ords_clients, in conjunction with the current_user implicit parameter to get more information. In the snippet below, I am getting the system_id related to the OAuth client from a custom table called source_systems.

-- Get the System ID associated with the current OAuth client.
SELECT ss.system_id
FROM   user_ords_clients uoc
,      source_systems ss
WHERE  uoc.description = ss.system_code
AND    uoc.client_id   = :current_user;

Use Different OAuth Clients

You should create different OAuth clients for each system, customer, user, etc., accessing your API. This allows you to control their access individually. In my example, If I suddenly wanted to stop the consumer APEX App from accessing the API, I only have to remove the specific OAuth client, and the other systems will still have access.

Rotate Client Secrets

It is all too easy to generate OAuth clients and then never change the client secret. I recommend rotating the client secrets at least once a year. You can rotate client secrets using the oauth.rotate_client_secret API.

BEGIN
  oauth.rotate_client_secret
   (p_client_id       => 1234,    -- user_ords_clients.id
    p_editing_user    => 'DEMO',  -- Your ORDS Enabled DB Schema
    p_revoke_sessions => TRUE);
END;

Authorization

You may have requirements to limit what specific OAuth clients can do with your API. In my example, I want to restrict the consumer APEX App so that the duplicate check only checks customers of type consumer.

One way to implement authorization is by creating a custom table that associates your OAuth clients with permissions or roles. You can then add code that performs different actions based on the permissions or roles the OAuth client can access.

In my example, I would have a system_permissions table that associates each system_id with a set of roles.

Keep Logic in PL/SQL Packages

Your ORDS Handler should have as little code in it as possible. All of your business logic should be in a PL/SQL package. This is more performant (because the code is compiled) and makes it easier to reuse the code elsewhere in your database.

Logging

Logging is an essential part of a REST API solution. Logs allow you to do the following:

  • Monitor API usage (and optionally enforce usage plans)

  • Monitor for errors

  • Monitor API performance over time

  • Troubleshoot bugs

Logging to a Table

In theory, your web server logs can handle most logging use cases. In practice, I often create a log table that the ORDS Handler populates. This allows you to capture the OAuth client information, the payload sent by the client, the response you send back to the client, error & debug messages, elapsed execution time, and much more.

I typically collect the following information in the API log table:

  • The version of the API that was called (see the section on logging below for more details on ver)

  • A CLOB with the JSON payload sent by the client. This is available via the implicit parameter body_text

  • The date and time the API was called. Captured using the function: CURRENT_TIMESTAMP

  • The client ID that called the API. This is available via the implicit parameter variable current_user

  • The path of the ORDS handler that called the API. This tells us which API was called. This is available via the CGI variable: owa_util.get_cgi_env('X-APEX-PATH')

  • The request method [POST,PUT,PATCH,DELETE,GET]. This is available via the CGI variable: owa_util.get_cgi_env('REQUEST_METHOD')

  • Query String Parameters. These are available via the CGI variable: owa_util.get_cgi_env('QUERY_STRING')

  • HTTP Header Variables. You can get these by calling OWA_UTIL.GET_CGI_ENV, or by defining the HTTP Header in your handler definition using ords.define_parameter

  • The elapsed time for the API execution.

  • The HTTP status code returned to the client [200,201,400, etc.].

  • The JSON response returned to the client.

  • An error or warning code of my own making to indicate if the API was completed successfully.

  • An error or warning message with details of the error or warning if one occurred.

Building the Log Record

You may have noticed in my example ORDS handler that I have a PL/SQL record called lr_api_log. I use this record to capture information that I want to log and populate the fields during the execution of the API.

The code below shows how to populate many of the log table values that I typically capture. The example shows a PL/SQL block as if it were in the ORDS Handler. In reality, these values would be populated in a procedure within your API's PL/SQL package.

DECLARE
  lr_api_log     api_log%ROWTYPE;
BEGIN
  lr_api_log.api_version     := 1;
  lr_api_log.current_user    := :current_user;
  lr_api_log.content_type    := :content_type;
  lr_api_log.request_payload := :body_text;
  x_api_log.request_method   := owa_util.get_cgi_env('REQUEST_METHOD');
  x_api_log.ords_path        := owa_util.get_cgi_env('X-APEX-PATH');
  x_api_log.query_string     := owa_util.get_cgi_env('QUERY_STRING');
  x_api_log.headers          := owa_util.get_cgi_env('X-MY-HEADER');
  x_api_log.status_code      := 'S';
  x_api_log.json_response    := l_response_clob;
  x_api_log.http_status      := 200;
  x_api_log.elapsed_ms       := l_elapsed_time;
  -- Create the log record.
  INSERT INTO api_log VALUES x_api_log;
END;

Read Tim's blog for more details on owa_util.get_cgi_env, ORDS implicit parameters, and ORDS user-defined headers.

Performance

Performance is an important part of building robust ORDS REST APIs. Please read my post Building Performant REST Services with Oracle REST Data Services, for more details on ORDS REST API performance.

Versioning

You need to consider API versioning before you start building your API. REST API versioning is important because it enables you to iterate faster when breaking changes are identified. It allows you to change your API without breaking existing clients that depend on it. Versioning also helps you maintain backward compatibility with existing clients while introducing new features. This can help you avoid conflicts and confusion when multiple versions of your API are being used simultaneously.

There are two common approaches for API versioning:

  • Through a URI path – you include the version number in the URL path of the endpoint, for example, customer/v1/check_duplicates.

  • Through custom HTTP headers or query string parameters. Define a new header/parameter containing the request's version number.

ORDS Versioning

You can use either of the above versioning approaches with ORDS. I typically version the entire ORDS module. In my sample use case ORDS module, I include /v1/ in the ORDS Module Base path to signify the version.

BEGIN
  ords.define_module
   (p_module_name    => 'cn.customer.v1',
    p_base_path      => 'customer/v1/',
    p_status         => 'PUBLISHED',
    p_comments       => 'Customer APIs Version 1');
END;

When you need to create version 2, you can export the v1 module (using SQL Developer or APEX Builder) containing the module, template, and handler definitions, change the module name and base path, make necessary adjustments to ORDS handlers, and import the new module.

Whichever versioning approach you take, you must do something with that version in your PL/SQL code. If you use the HTTP Header/Query String parameter approach, getting the version number is easy. If you go with the URI path approach, you will need to parse out the version number from the value returned from owa_util.get_cgi_env('X-APEX-PATH'). e.g. customer/v1/check_duplicates.

The following code could be used to extract v1 from customer/v1/check_duplicates.

DECLARE
  l_path    VARCHAR2(100);
  l_version VARCHAR2(3);
BEGIN
  -- Get the path of the API that was called.
  -- Returns the ORDS module and ORDS handler path
  -- e.g. customer/v1/duplicate_customer
  l_path := owa_util.get_cgi_env('X-APEX-PATH');
  -- Get the version number from the path.
  l_version := REPLACE(REGEXP_SUBSTR (l_path, '\/(v\d+)\/'),'/',NULL);
END;

Once you have the version, you can write conditional logic in your PL/SQL code to perform different actions based on the API version that was called.

Even though a separate ORDS module for each version duplicates your ORDS Handlers, it gives you more flexibility when creating new versions. For example, you can completely rewrite the SQL for a GET Handler rather than adding complex conditional logic to the SELECT or WHERE clauses based on the version of the API being run.

Cleanup Deprecated Versions

As you deprecate older API versions, remember to go in and remove old ORDS modules and conditional logic in your PL/SQL packages. This avoids confusion for other developers as to what versions are current and can also impact performance.

Usage Plans & Rate Limiting

If you are building a public REST API, you will likely want to define usage plans. Usage plans allow you to restrict usage to your APIs based on the plan your customer purchased. For example, the Gold plan allows customers to call your API 10,000 times per month, whereas the Silver plan only allows 5,000 requests per month.

Of course, you can use a product like OCI API Gateway to enforce usage plans, but maybe you need an approach to use with ORDS alone. I suggest the following:

  • Add a column for your client table (the custom table you create that relates an OAuth Client ID to an entity in your system) containing the usage plan they have purchased.

  • Create a new table summarizing how many requests each client has made for each of your REST APIs.

  • Create an APEX Automation that runs every 15 minutes to update the summary table (see the previous bullet point) with the latest counts queried from the log table. Pre-calculating the usage count in an Automation reduces the time it takes to check the usage when the APIs are executed (see next bullet).

  • Add a function at the start of your REST API PL/SQL procedure to check if the current client has exceeded their usage for the current REST API. If the limit has been exceeded, then return an HTTP status of 429 'too many requests'.

API Responses

All good APIs return helpful information to the caller allowing them to ascertain if the API was completed successfully and if it did not, how the issue can be fixed.

HTTP Status

The HTTP response is the first thing clients will check when you return a response. The response codes you return from PL/SQL-based ORDS APIs should correspond to recognized values and be consistent across all your APIs. I use these values as a guideline.

Response Payload

In addition to the HTTP Status, including detailed status information in the API response body is a good idea. The amount of information you can return in response is limited by the type of request. A handler based on a SQL statement cannot return any additional information in the response payload. If your handler is PL/SQL-based, you can include additional information in a separate JSON object.

I typically include a status object in JSON responses which provides detailed information about the request's status. This section should contain as much information as possible when a request fails to help the caller resolve the issue.

Successful Request Response

A successful request would return an HTTP status of 200 and a response something like this:

{
  "status": {
    "status_code": "S",
    "status_msg": "Success",
    "received_count": 2,
    "failed_count": 0
  },
  "customers": [
    {
      "customer_name_found": "ABC INC.",
      "duplicate_score": 99
    },
    {
      "customer_name_found": "ABC LTD",
      "duplicate_score": 80
    }
  ]
}

Failed Request Response

A failed request would return an HTTP status of 400, and a response something like this:

{
  "status": {
    "status_code": "E",
    "status_msg": "You did not provide any customer names to check. See the documentation for details.",
    "doc_link": "www.example.com/api/docs/payload",
    "received_count": 0,
    "failed_count": 0
  }
}

Conclusion

In this post, we explored best practices for Building ORDS PL/SQL Based REST APIs. These practices include using ORDS OAuth security, keeping logic in PL/SQL packages, logging, versioning, usage plans, and rate limiting. By following these best practices, you can ensure that your APIs are secure, efficient, and scalable.

🔗 Read More

- ORDS Posts

Oracleords