Best Practices: REST APIs for your Database {Draft!}

Updated 29 July 2022

This DRAFT blog post has been promoted to PRODUCTION. You can find the official, blessed copy of our Best Practices on Oracle.com.

I put {Draft!} into the title of this post because I intend to publish this post (TODAY!), AND I reserve the right to say it’s not finished. Customers have been asking me for these for several years, and I must stop kicking that can already!

Yes, I’m the product manager for Oracle REST Data Services. But, I am only one of many talented people on the ORDS product team here at Oracle. Much of what I will be sharing here are not only our collective experiences with building AND using the product ourselves, but from also working with many successful customer projects and implementations.

Nothing is absolute. Everything can have nuance. Please employ critical thinking and try things in your User Acceptance Testing (UAT) environments before publishing anything to PROD.

This will be slanted heavily for Oracle, but in general would apply to any mid-tier tech being used to access a database using SQL or it’s associated procedural language.

When I say ‘ORDS’ I’m referring to Oracle REST Data Services, our REST API technology for the Oracle Database. It’s free to use with any Oracle Database (and coming soon to MySQL!)

Installation, Deployments, & Configuration

Do not install your webserver on the same machine as your database.

It needs to be CLOSE to your database, but preferably not on the same server as your data. Why? well let’s say your database server is shutdown unexpectedly. Not only have you lost your data, you’ve lost your webserver. Also…why have your webserver share server resources with your databases?

High Availability is not a luxury – plan for disruptions and lots of traffic.

We recommend you have a load balancer in front of not one, not two, but THREE ORDS nodes. If one ORDS goes bad, you still have two more to handle your traffic! This will also come in handy if you want to do ‘rolling upgrades.’ You can take one node down while you do an upgrade/fix, and then move onto the 2nd and 3rd instance, all while having zero downtime from your apps – even though you will be restarting your webserver.

ORDS HA Reference Architecture: This resource was put together for the Oracle Cloud (OCI) – but it 100% applies to your on-premises infrastructures.

ORDS Standalone is totally fine for production. We (Oracle) runs it this way in fact.

Can you use Tomcat or Weblogic? Absolutely. In fact, if you already have existing mid-tier / middleware infrastructure going and it’s a matter of simply dropping ORDS into it – go for it.

Or in other words – if you’re already using Tomcat, keep using it for ORDS. Same for Weblogic.

We do not recommend setting up Weblogic specifically for ORDS – it’s overkill in terms of what ORDS needs. It does however come in very handy for setting up SSO.

Use Oracle Java. Only use Oracle Java.

That’s what we develop on and for. It’s what we certify and support. If you choose to use OpenJDK instead, you will be at the mercy of how well it’s been implemented to emulate the Oracle offering.

If you have problems with ORDS and if you’re not using Oracle Java, the first thing we’ll ask if your issues still reproduces when using Oracle Java 8 or 11.

Container vs PDB Installations

You can most definitely install ORDS once for a Container (CDB) database, and as new Pluggable Databases (PDBs) are created, ORDS will be available.

If, however, you will have different needs/requirements between your PDBs, then I would advise installing ORDS directly for each PDB. Then you can manage the pools separately as necessary. For that matter, each PDB could have it’s own completely different version of ORDS running if necessary.

Obviously you MUST use the CDB installation option if you want to use the DB-API ORDS provides for doing lifecycle management of your PDBs.

One Mid-Tier, Many Databases

I say one, but I mean really 3 – see above note on HA architectures. What I really mean here is, you do NOT need One ORDS per Oracle Database. A single ORDS is more than capable of handling hundreds of Oracle Databases via it’s connection pools (UCP).

Upgrade often.

We have quarterly updates. We routinely fix security and performance bugs in these FREE, quarterly updates. It would behoove you grab the latest and immediately put them through your test/trial environments and ready them for production.

Version of ORDS is not dependent on version of APEX or Database.

You don’t have to run ORDS 20 to support APEX 20, or ORDS 19.x to run Database 19c. Each release of ORDS supports the currently available/supported versions of both APEX and the Oracle Database at the time of it’s release.

So ORDS 21.2 in 2021 can easily handle Database 19c, but would not have been tested for Database 27c (not yet released in 2021).

Properly Size your Connection Pools

We have a default pool size of 10 connections. This isn’t suitable for almost any production workload. You need to do some testing and trial-and-error work with your actual workloads to find the right balance between connections and available database resources.

We HIGHLY recommend you take in the Real World Performance Team’s Connection Pool Sizing tips & Tricks.

Keep your Configuration Directories separate from your software

If you’re using ORDS, don’t place your configdir in the same directory structure/path as the ORDS binaries. It’s too easy to accidentally overwrite or wipe these to /dev/null.

HTTPS always, HTTP never.

Never say always or never, but this may be the exception. Grab a proper SSL certificate for ORDS, deploy your mid-tiers securely from the ground-up.

Developing REST APIs

Security starts at day 0.

It’s very easy to leave security to the end of your project. Do not do this, plan for how you will authenticate AND authorize your requests from the beginning.

Then include this in your testing, documentation, etc. as you develop your APIs. Security is a mindset, and muscle – exercise it.

Modeling your URI Templates, handlers, responses, parameters…not a bad idea.

It’s tempting to jump straight into your handler code and have your GET or POST working, But, REST APIs are all about HATEOAS…you communicate the path your users will take with the Links shared in your REST Responses.

Stay tuned on this topic as we’re looking into building support for this ‘design before you build’ methodology INTO our REST development interface.

Your APIs need to be friendly, and predictable IF they are to be useable

that’s happening indirectly, so how far down the rabbit hole do you want to go?

Main thing is don’t lie with your APIs. A GET should simply be doing what a GET implies/advertises.

— Jeff Smith 🍻 (@thatjeffsmith) February 10, 2022

CAN you use ORDS to build a REST API that uses PL/SQL on a GET request? YES. But that GET request should be following the rules of REST, that is, there shouldn’t be any changes happening on the back-end! So if your PL/SQL block just runs a query and returns a REF Cursor, that’s fine. But if it inserts a row to a table…that doesn’t sound like a GET to me.

If you create something, issue a 201 and include a link

If you add something to a collection, you expect to be told, hey that worked, we added something. The HTTP 201 Status code does that.

It’s also pretty cool if in addition to that, you include a link to the thing you just added, or even better, redirect TO the item. so you can/use/manipulate it in your application immediately.

It’s VERY easy to execute a SQL INSERT with an Oracle Database REST API, but you want to be following the REST Architectural Style guidelines as closely as possible for your APIs to be considered truly RESTful.

Trap database errors, do NOT let them ‘float out at 500s’

PL/SQL Exception handling is your friend. You can catch an exception and then return a useful 4xx status code and message to your API consumers. Failing hard with a HTTP 500 is something to avoid at all costs.

Use the ORDS Features as much as you can, until you can’t

ORDS gives you features like automatic generation of JSON responses, automatic pagination of SQL results, automatic support for uploading/downloading files…the list goes on.

But at sometime you will hear yourself say, ‘but I need the JSON to look EXACTLY LIKE THIS.’ And then you will be temped to take over that part of the REST API from ORDS. At this point, in your PL/SQL code, you will then need to handle everything. Forever.

I’m not saying that’s wrong or bad, I’m just saying exhaust the core features before you go down this path. And to prove I’m a hypocrite and this is all subjective, here’s an example of me showing you how to generate your own JSON responses.

Speaking of Paging, beware pagesize 0

This tells ORDS not to page the results and just stream back the entire result set – be that 10, 100, or 1,000,000 rows. That last scenario should be limited to very specific use cases. Like, I want to approximate a file download for the contents of my ENTIRE table, and even then that might not be a good idea.

The bigger the payload, the more resources ORDS will need to put that together for you to GET.

Speaking of Paging again, consider ordering your results

If you want to avoid duplicate items in your JSON responses as you go from page to page, you need to include ORDER BY clauses in your SELECT statements.

The order in which rows are returned between one SELECT and the next, aren’t guaranteed without an ORDER BY clause. And as you page your results in ORDS, we’re running your query (again) for each page.

You’ve written a TON of PL/SQL – leverage that for your APIs!

We have customers with literally decades worth of application logic coded as PL/SQL in their database. Making REST APIs for Oracle is often a simple matter of simple wrappers over that existing PL/SQL. Or even easier, it could be you just using the AUTO feature..that’s right NO CODE.

Performance

This topic is the middle part of a lot of Venn diagrams. I’ve decided to make it it’s own topic partly so that it stands out more, but you could easily throw most of these into the config or development sections.

Your APIs will only be as fast as your SQL or PL/SQL

We spend a LOT of time helping customers optimize or tune their REST APIs. This generally comes down to helping someone get their SQL such to run in hundredths vs tenths of a second.

Mind I did not say down to 1-2 seconds, but small fractions of seconds. If you fail to do this, no connection pool will be able to handle large amounts of requests coming in – eventually you’ll overwhelm the mid-tier, the database, or both.

An application for a single user will frequently makes a dozen API calls to build that fancy page you’re looking at in your browser or mobile app. That’s for a single user – now imagine those popular apps that handle tens if not hundreds of thousands of users.

With super fast SQL, you don’t generally need a HUGE connection pool.

Always be tuning. That means your testing should include load testing your APIs with real-world looking data, volumes of data, and number of requests. Kris likes Jmeter for the last bit.

Consider eTags for static content

My employee ID, salary, and address aren’t likely to change very often. So if you have an workflow that’s going to be asking for that over and over…don’t. Ask for it once, and not again until the eTag indicates you need to go physically GET it again.

Kris shows how to accomplish this here.

eTags aren’t just for faster pages. If you have two users about to update the same resource, eTags can prevent stale pages from being used to update your database, or even just alert users the data on-screen no longer represents what’s in the database.

Consider Caching the REST API definitions themselves

What happens when you make an ORDS request?

ORDS follows this path, more or less:

1. figure out the database being tasked, borrow a connection from the appropriate pool
2. figure out the schema that owns the API
3. LOOK UP the code and privs required to call the API
4. proxy connect from the public user to the API Schema
5. run the code
6. get the results
7. give the connection back to the pool (depending on connection re-use config)

The most expensive part of that process is Step 3. We have to run SQL to figure that out. Start with ORDS 21.2, you can tell ORDS to cache those API and privilege definitions. The conceit is that they’re not likely to frequently change.

The results of implementing this cache are quite the whiz-bang.

Try not to use Database Authentication for accessing your APIs

Is it supported? Absolutely. Is it a great idea? Nope. Why not?

1. You’ll be using database usernames and passwords on EVERY request
2. You’ll be making a database connection on every request to check the password

Both points should give you pause. The first is a reflection of BASIC auth. It’s not horrible I suppose if you use HTTPS, but do you really want to give your API consumers the keys to an account in your database? Probably not.

The second point is the performance bit. Making database connections isn’t cheap. The only way to check if a password is right, is to try to use it. Which we’ll do. And it will SLOW DOWN your APIs.

So what should use use instead? OAuth2, probably. OAuth2 will run a query ONCE to check your Client ID/Secret and get you your Access token.

You probably think at this point I’ll point you to another Kris Rice blog post. Nope, I’m S-M-R-T enough to figure this one out on my own.

Other useful ORDS resources

• Kris’ blog – I quote it daily, if not more frequently. Kris is co-inventor of ORDS and VP of DB Dev Tools.
• Oracle-Base – unofficial, but a treasure trove of goodies
• My ORDS Resource Page – general info and LOTS of examples
• Debugging ORDS
• Download ORDS
• ORDS Doc
• ORDS on Twitter!