Oracle REST Data Services (ORDS) best practices

Installation, Deployment, and Configuration

Web Server Installation

High Availability (HA)

High availability for ORDS can be ensured with proper planning and architecture. Disruptions and traffic spikes are inevitable, so it’s important to have mitigation policies and procedures in place.

ORDS HA architecture can be achieved with:

• A minimum of three ORDS nodes



or

• An application web server, front-ended with a load balancer

HA provides the following two benefits:

• If an ORDS node goes down or becomes unhealthy, your LB still has two nodes to distribute HTTPS requests.
• It provides the ability to implement rolling updates. When it's time to patch or upgrade your machine or ORDS, simply take one out of rotation, while two nodes remain for request serving. When the maintenance cycle is completed for the first node, continue rolling updates for the remaining nodes.

You can deploy Oracle REST Data Services with high availability on OracleCloud Infrastructure (OCI). Created for OCI, these concepts still equally apply to your on-premisesinfrastructure. This HA Architecture can also be deployed automatically in OCI using Oracle CloudInfrastructure Resource Manager.

Stand-alone ORDS

Web Application Servers: Oracle WebLogic Server and Apache Tomcat

Java

Should you deploy ORDS to a pluggable database (PDB) or a container database (CDB)?

ORDS Updates

ORDS, Oracle APEX, and Oracle Database Compatibility and Support

Connection Pools

The connection pools maintained by ORDS need to be properly sized to handle your specific applicationworkloads. ORDS creates connection pools with a default size of 10 connections. However, this defaultconfiguration will not be suitable for production workloads. To find a good balance between the number ofconnections and available database resources, you must do a thorough load testing and iterative tuning usingrepresentative workloads.

Here is an example of improperly sized connection pools:

Configuration Directory

Developing REST APIs

Security

We strongly caution you against building your REST APIs today and implementing security tomorrow. Securityshould be considered from the very beginning. Start every project by planning how you will authenticate andauthorize your requests. Include AUTH as part of your API testing and documentation.

REST API code reviews should include security model inspections. When developing REST APIs with Oracle SQLDeveloper Web, you are forced to explicitly choose “Not protected”, to publish a REST API or REST Enabled Object without a security privilege. This is to encourage you to consider security at the beginning of your project. Avoid the creation of accidental public APIs.

Do not select "Not protected" except for proof of concepts. Even then, you should strive to use privilegesand OAuth 2.0 clients so it becomes routine.

Stand-alone ORDS should be configured for HTTPS. Obtain and supply SSL certificate(s) rather than relying onusing the temporary self-signed certificates that ORDS generates at startup. Validate domains for ORDS soyour mid-tiers are deployed securely from the start.

Do not select "Not protected" except for proof of concepts. Even then, you should strive to useprivileges and OAuth2 Clients so it becomes habit.

Modeling

Modeling of URI templates, handlers, responses, and parameters is strongly encouraged. Do not move directlyinto your handler code, as certain considerations are required when dealing with GET or POST requests.Remember, REST APIs conform to the HATEOAS architecture; you are communicating the path your users will takewith the links shared in your REST responses.

Swagger can be used to develop OpenAPI (V2 or V3) specifications for your REST APIs. Once templates,handlers, and parameters are represented in JSON, they can be imported into ORDS as new modules. Afterward,you can supply the SQL and PL/SQL required for all of your handlers. Operating this way will greatly improveyour workflow and make building Oracle REST APIs with ORDS more efficient.

By using the module page in Oracle SQL Developer Web, you can:

• Reclaim lost time
• Model your templates and handlers
• Generate JSON OpenAPI (V2 or V3) specification
• Take advantage of our new "Import" feature

The Import feature allows you to automatically deploy patterns and verbs; you only have to supply the SQL andPL/SQL code behind the handlers.

RESTful APIs

For an API to be usable, it needs to be friendly and predictable.

ORDS itself will not enforce the architectural styles and patterns associated with REST APIs.

GET requests should result in no changes on the backend. If a PL/SQL block runs a query and returns a REFcursor, it could be implemented as a RESTful API using a GET handler. However, if your API is implementedwith a GET and makes any changes to the underlying database system, it would not be considered RESTful.

Model things, not actions. Customer accounts can be modeled with conventions, such as:

accounts/

New customer account creation should be accomplished with:

curl --location --request POST \ 'http://server:8080/ords/sh/accounts/'

As opposed to:

curl --location --request GET \ 'http://server:8080/ords/sh/create_account'

REST API Tutorial helps you ensurethat APIs can truly be considered RESTful.

HTTP response status codes

When a user creates a new resource, the REST best practice is to respond with both a 201 status code and theaddress (link) to the new resource. An alternative approach would be to redirect the client to the resource.When adding something to a collection, you should know if that action worked; the 201 status code is a wayof communicating success to the user. Building REST APIs that don't adhere to these tenets can be confusingand problematic for your API subscribers because they would not properly broadcast success, failure, orerror.

Example: How to add a row to a table

Avoid 500 status codes by:

• Implementing exception blocks in your handler
• Trapping common Oracle errors
• Returning friendly HTTP responses

It is preferred that you catch an exception and then return a useful 4xx status code with an accompanyingmessage to your API consumers. Failing hard with an HTTP 500 status code should be avoided at all costs.

Example: Trapping Oracle errors andreturning friendly HTTP status and message.

Unlocking ORDS features

Taking advantage of ORDS capabilities means fewer lines of code and faster development cycles. You'll alsobenefit from the following ORDS capabilities:

• Automatic generation of JSON responses
• Automatic pagination of SQL results
• Automatic support for uploading/downloading files

It can be tempting to write code to format your JSON in a specific manner, but we caution users against doingthis as it would add additional complexity with only marginal benefits. Relying on ORDS to format your JSONresponses means that you won't need to modify any PL/SQL code or document anything above what ORDS alreadyprovides. We've found that the trade-offs are well worth it; simplicity and low maintenance are two of thebiggest advantages of ORDS.

There are some cases where creating a unique API is necessary, but we encourage users to squeeze every lastdrop out of ORDS before moving to a more bespoke API.

Example: Manually building a hierarchicalJSON document of managers and their employees.

ORDS pagination

A word of caution about paging and page size: specifically page size 0. A page size of 0 will tell ORDS tostream back an entire result set, which could mean 10, 100, 1,000,000, or more rows. However, when youindicate a page size, the results will stream back incrementally.

When requesting the entire result set, do it sparingly and in very specific use cases. For example, when youwant to approximate a file download for the contents of an entire table. But even in this case, we recommendyou exercise extreme caution. If possible, paginate with larger page sizes.

Always remember the larger the payload, the more resources ORDS will need to return that GET request.

Ordering results

When paginating a SQL-based handler, order your results. When moving from page to page, you can include ORDERBY clauses in SELECT statements to avoid duplicate items in your JSON responses.

Without an ORDER BY clause, the order in which rows are returned between one SELECT statement and another isnot guaranteed.

When ORDS paginates SQL-based responses, a separate and unique query is used for each page request (i.e.offsets and the included "next" URL). Read more about paging and ORDS here.

SELECT * FROM all_objects

SELECT * FROM ( SELECT Q_.*, ROW_NUMBER() OVER( ORDER BY 1 ) RN___ FROM( SELECT * FROM ALL_OBJECTS ) Q_ ) WHERE RN___ BETWEEN :1 AND :2

To ensure objects do not “slip” between pages as the queries are executed, add an ORDER BY OBJECT_ID to yourquery.

Leveraging existing PL/SQL

Many of our database customers have a decade or more's worth of PL/SQL application logic coded in theirdatabase. Repurposing these stored procedures via REST APIs for Oracle Integration is simple; all that isrequired is that you wrap those programs with PL/SQL blocks in your handlers.

An alternative approach is to use the automatic PL/SQL feature to have ORDS do the work for you. For AUTOPL/SQL endpoints, ORDS will automatically take input parameters off your request body (JSON attribute labelsmatch the PL/SQL program argument names), execute your program, and grab as JSON any standard output, OUT,and/or RETURN values.

AUTO PL/SQL feature for packages.

ORDS performance

You'll notice performance considerations are included in many of the sections throughout this guide. However,since performance is a top priority, we’ve included a dedicated section.

SQL or PL/SQL impact on APIs

Your APIs will only be as fast as your SQL or PL/SQL. We spend a lot of time assisting customers inoptimizing and tuning their REST APIs. Reducing SQL runtimes from tenths of a second down to hundredths is ahuge accomplishment. Why? Because a failure to do so would challenge any connection pool as requests scale.Those fractions of a second add up, and eventually mid-tiers and databases (sometimes both) can becomecompletely overwhelmed.

Consider that even a simple mobile or web application will frequently make dozens of API calls to build themost modest of pages. In a best-case scenario, a single user interacts with your application. However,should that application experience a rise in popularity, those dozens of API calls from a single user canquickly turn into dozens of API calls for each of the thousands of users. Which is why we cannot emphasizeenough how you can reduce the need for huge connection pools with super-fast SQL.

You should always be tuning, retuning, and testing. API load testing should be done with:

• Test data that resembles production data
• Volumes of data
• A sufficient amount of requests that mirror production

Using Jmeter to load test your ORDS REST APIs

eTags

When dealing with static or infrequently updated content, we recommend using eTags. In one test, we observedthat ORDS was capable of handling approximately 90 requests/second. But after implementing eTags, throughputincreased nearly threefold—to 270 requests/second.

Reducing network round trips with eTags

An example scenario where implementing eTags makes sense could be when dealing with attribute values, such as"Employee ID," "Salary," and "Address." These attributes are not likely to change frequently, so if yourcurrent workflow requests this data over and over again, change your workflow. Only ask for this data once,unless the eTag suggests otherwise. In that case, only then would you issue another GET request.

eTags aren’t just for speeding up page-loading times. For example, if you have two separate users attemptingto update the same resource, eTags acts like Git (but for users), preventing stale pages from being used toupdate the database. eTags can also be used to simply alert users that what is onscreen no longer representswhat is actually in the database.

Caching REST API definitions

ORDS stores the logic and security definitions of your APIs in the database. For that reason, we recommendyou consider caching the back-end code that powers your REST APIs.

For every ORDS request, its path:

• Determines the database being tasked and borrows a connection from the appropriate pool
• Determines the schema that owns the API
• Performs a LOOKUP for the code and any privileges required to call the API
• Connects (via a proxy) the public user to the API schema
• Executes the code
• Gathers the results
• Releases the connection back to the pool

Take note that Step 3 is the most expensive stop on the REST request and response journey. You can explorethis step further by running a SQL query to observe what this "cost" looks like. With Oracle REST DataService Release 21.2 and later, you can request that ORDS cache API and privilege definitions. While Step 3is the most expensive, the advantage is that this code and any privileges aren't likely to changefrequently.

Caches last a lifetime and then they expire. In cases where you change a security model or REST APIdefinition, the cached copy would continue to be used until the cache expires.

Results from caching ORDSREST API definitions and privileges

Database authentication and APIs

Avoid using database authentication for securing REST APIs. This is not recommended for the following tworeasons:

• Database user names and passwords will be included on every request
• Database connections will be created on every request to validate the username/password

Further, the use of basic authentication has fallen out of favor within the web application community. If youhave no alternative, then it is crucial that you maintain HTTPS throughout the entire stack. We do notrecommend sharing database credentials with consumers, as they would not require direct access to thedatabase.

Additionally, there is a performance hit with basic authentication. Establishing database connections comesat a cost. The only way to verify if a password is correct is to use that password. Adding this step willcause your APIs to be slower, and less performant. A more secure and faster approach is to use the built-inOAuth2 for ORDS. OAuth2 will run a query once to check your client ID/secret key and retrieve your accesstoken.

Using ORDS OAuth2

Compare performance using database authentication vs.OAuth2