Oracle REST Data Services (ORDS) best practices
Installation, deployment, and configuration
Web server installation considerations
Web servers should be installed on dedicated machines, separate from where database servers reside. Web servers should be located near databases to ensure network latency does not exceed 10 ms. To ensure the best network performance, always strive to keep application and data close to one another. Latency kills network performance.
When application and database servers are separate, it protects one from becoming unavailable when the other goes down or becomes overwhelmed. Additionally, having separate servers prevents the web server from starving resources from the database.
REST and API are both acronyms meaning “REpresentational State Transfer” and “Application Programming Interface”
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
- An application web server, front-ended with a load balancer
or
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 Oracle Cloud Infrastructure (OCI). Created for OCI, these concepts still equally apply to your on-premises infrastructure. This HA Architecture can also be deployed automatically in OCI using Oracle Cloud Infrastructure Resource Manager.
Stand-alone ORDS
When ORDS is deployed as a stand-alone application (i.e. the ORDS process spins up its own Jetty web server), it is certified and supported for production environments.
When stand-alone ORDS is deployed this way (as in production Oracle Cloud Infrastructure services) it provides the following advantages:
- Easily containerized (Docker)
- Requires only Java
- Minimizes required resources
Web application servers: Oracle WebLogic Server and Apache Tomcat
ORDS supports both Oracle WebLogic Server and Apache Tomcat. Simply drop ORDS into either environment; no changes to ORDS are required. Be sure to deploy ORDS to a version of Tomcat/WebLogic Server that supports and uses Oracle Java, as required for ORDS.
ORDS itself maintains the connection pools to the databases using universal connection pool (UCP) technology. Pool configuration changes are made via ORDS connection pool system properties, not Tomcat or WebLogic Server.
We recommend you use a web application server that your existing IT infrastructure and support teams are most comfortable with. Apache Tomcat is the most popular deployment option for running ORDS amongst our customer base, primarily because of its size and no-cost licensing model.
We don't recommend using Oracle WebLogic Server solely for the purpose of supporting ORDS, unless WebLogic Server is a requirement for your production environments. WebLogic Server is over and above what ORDS requires. However, you may want to consider using it because WebLogic Server provides the following benefits:
- Makes setting up SSO simpler
- Supports Enterprise Java Beans (EJB)
- Can handle transaction management
- Supports HTTP as well as FTP
Java
We recommend that you use Oracle Java and only Oracle Java. ORDS was developed using Java and intended to be used with Oracle Java. Oracle Java is what we approve—and support.
If you experience issues with ORDS while using OpenJDK or any other non-Oracle Java distribution, you will be asked to reproduce the issue using a supported version of Oracle Java. Refer to the README in your ORDS distribution to review a list of supported versions of Oracle Java.
Should you deploy ORDS to a pluggable database (PDB) or a container database (CDB)?
In most cases, it’s best to deploy ORDS directly into each PDB. This allows you to manage your database connection pools separately. Additionally, PDB-level installs allow you to run different versions of ORDS in the same CDB or have different ORDS configurations and options enabled. In other words, PDB installs provide maximum flexibility.
However, if you have:
- A large number of PDBs with uniform requirements
- The need to deploy the DBAPI and multitenant lifecycle management REST APIs
or
You can install ORDS using a CDB. And as new pluggable databases are cloned or created, they will be automatically registered with ORDS via the container—all PDBs in a CDB will share a single universal connection pool.
ORDS updates
ORDS updates are provided quarterly. Minor releases, or “dot(s)” are published as required. To take advantage of our regular security updates and performance-related enhancements, we recommend that you update to the latest ORDS release as often as possible.
Before you update to the latest version, start with your preproduction and test environments so that you can update your production environments with confidence.
See the previous section on HA for tips on rolling updates to prevent downtime.
ORDS, Oracle APEX, and Oracle Database compatibility and support
ORDS supports Oracle APEX and Oracle Database versions that were generally available or supported at the time of its release. We recommended you update to the newest ORDS version as it will include the latest security and performance enhancements. We always recommended that you update to the latest ORDS version as it will include the latest security and performance enhancements.
Connection pools
The connection pools maintained by ORDS need to be properly sized to handle your specific application workloads. ORDS creates connection pools with a default size of 10 connections. However, this default configuration will not be suitable for production workloads. To find a good balance between the number of connections and available database resources, you must do a thorough load testing and iterative tuning using representative workloads.
Here is an example of improperly sized connection pools:
For further guidance on properly sizing connection pools (bigger is not always better), we recommend the Real World Performance Team’s Connection Pool Sizing Tips and Tricks.
Configuration directory considerations
Store your ORDS configuration directories and files separately from your ORDS software. Do not place your configdir in a directory structure/path underneath your ORDS install folder.
ORDS Releases 3 to 21.4 “burn” the location of the configdir into the WAR file. ORDS Release 22.1 and higher in stand-alone mode uses either an ORDS command parameter or environment variable to communicate the location of the configdir. We strongly recommend using an environment variable, to avoid worrying about forgetting the --config flag. Omitting both will result in ORDS defaulting to the current working directory for its configuration.
Developing REST APIs
Security
We strongly caution you against building your REST APIs today and implementing security tomorrow. Security should be considered from the very beginning. Start every project by planning how you will authenticate and authorize 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 SQL Developer 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 privileges and 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 on using the temporary self-signed certificates that ORDS generates at startup. Validate domains for ORDS so your mid-tiers are deployed securely from the start.
Modeling
Modeling of URI templates, handlers, responses, and parameters is strongly encouraged. Do not move directly into 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 take with 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 improve your 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 and PL/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 REF cursor, it could be implemented as a RESTful API using a GET handler. However, if your API is implemented with 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 ensure that 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 the address (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 way of communicating success to the user. Building REST APIs that don't adhere to these tenets can be confusing and problematic for your API subscribers because they would not properly broadcast success, failure, or error.
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 accompanying message to your API consumers. Failing hard with an HTTP 500 status code should be avoided at all costs.
Example: Trapping Oracle errors and returning friendly HTTP status and message.
Unlocking ORDS features
Taking advantage of ORDS capabilities means fewer lines of code and faster development cycles. You'll also benefit 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 doing this as it would add additional complexity with only marginal benefits. Relying on ORDS to format your JSON responses means that you won't need to modify any PL/SQL code or document anything above what ORDS already provides. We've found that the trade-offs are well worth it; simplicity and low maintenance are two of the biggest advantages of ORDS.
There are some cases where creating a unique API is necessary, but we encourage users to squeeze every last drop out of ORDS before moving to a more bespoke API.
Example: Manually building a hierarchical JSON 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 to stream back an entire result set, which could mean 10, 100, 1,000,000, or more rows. However, when you indicate 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 you want to approximate a file download for the contents of an entire table. But even in this case, we recommend you 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 ORDER BY 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 is not 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.
| The SQL in your REST module | The SQL ORDS executes |
|---|---|
| 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 your query.
Leveraging existing PL/SQL
Many of our database customers have a decade or more's worth of PL/SQL application logic coded in their database. Repurposing these stored procedures via REST APIs for Oracle Integration is simple; all that is required 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 AUTO PL/SQL endpoints, ORDS will automatically take input parameters off your request body (JSON attribute labels match the PL/SQL program argument names), execute your program, and grab as JSON any standard output, OUT, and/or RETURN values.
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 in optimizing and tuning their REST APIs. Reducing SQL runtimes from tenths of a second down to hundredths is a huge 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 become completely overwhelmed.
Consider that even a simple mobile or web application will frequently make dozens of API calls to build the most 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 can quickly turn into dozens of API calls for each of the thousands of users. Which is why we cannot emphasize enough 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 observed that ORDS was capable of handling approximately 90 requests/second. But after implementing eTags, throughput increased 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 your current 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 attempting to update the same resource, eTags acts like Git (but for users), preventing stale pages from being used to update the database. eTags can also be used to simply alert users that what is onscreen no longer represents what 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 recommend you 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 explore this step further by running a SQL query to observe what this "cost" looks like. With Oracle REST Data Service Release 21.2 and later, you can request that ORDS cache API and privilege definitions. While Step 3 is the most expensive, the advantage is that this code and any privileges aren't likely to change frequently.
Caches last a lifetime and then they expire. In cases where you change a security model or REST API definition, the cached copy would continue to be used until the cache expires.
Results from caching ORDS REST API definitions and privileges
Database authentication and APIs
Avoid using database authentication for securing REST APIs. This is not recommended for the following two reasons:
- 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 you have no alternative, then it is crucial that you maintain HTTPS throughout the entire stack. We do not recommend sharing database credentials with consumers, as they would not require direct access to the database.
Additionally, there is a performance hit with basic authentication. Establishing database connections comes at a cost. The only way to verify if a password is correct is to use that password. Adding this step will cause your APIs to be slower, and less performant. A more secure and faster approach is to use the built-in OAuth2 for ORDS. OAuth2 will run a query once to check your client ID/secret key and retrieve your access token.
Compare performance using database authentication vs. OAuth2