REST APIs & modeling items with multiple keys

We know a table can have more than one primary key. Perhaps our PEOPLE table has rows uniquely identified by DOB, FIRST, LAST, MIDDLE. Haha, just joking. We’ll do something more boring:

CREATE TABLE THREE_COL_PK
(ID1 INTEGER,
ID2 INTEGER,
ID3 INTEGER,
WORDS_AND_STUFF VARCHAR2(4000),
CONSTRAINT THREE_COL_PK_PK PRIMARY KEY (ID1, ID2, ID3)
USING INDEX);

I want to create a couple of REST APIs for GETs on this table.

• GET records/
• GET records/:key

Getting all the records

The template is easy, it’s simply whatever I want to call it, plus a slash. I’m not going to get complicated here:

The interesting part is the “$.id” notation. In the docs, “Generating Hyperlinks”, you can see that ORDS provides a mechanism for adding links based on primary keys.

“is aliased as $.id, to produce a hyperlink” – yet you see above we’re doing a variation on that theme. Further on down the page is a section called “Composite Primary Keys”:

Composite Primary Keys

This section describes the support for composite primary keys.

If multiple columns in a query form the primary key of a row, then each of those columns must be aliased by $.id. ORDS combines such values to form the relative path of the item URL.

https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/21.4/aelig/developing-REST-applications.html#GUID-74149754-AC98-4CE3-88E8-03E9C71FD4ED

There is something missing, or incomplete in that description. Hence this post. I’m reviewing the Docs for our upcoming 22.1 release of ORDS, so I’m learning stuff, which is always fun!

Each piece of the composite key starts with “$.id” – but then you can have “$.id.1”, “$.id.2”, “$.id.3”, …

I’ll be logging a note to have the Docs updated to reflect this.

If I call the GET on this resource, here’s my response:

Now, this is only HALF the battle, right? We now need a template for the individual records. I’m show a GET for that, but maybe you need a PUT and a DELETE too.

Getting a specific record

What is our template? It’s also simple, once you understand the pattern. I’ll be updating the docs so that this is more clear as well.

Then we can use the :bind notation for each of the three values, even though it’s shown as a single bind of sorts in the template.

If I do a GET on /multikey/1,1,1, I’ll get my ‘first’ record.

Let’s end on a trick!

Yes, you can use the nice, fancy REST development environment. But, I can also use the ORDS PL/SQL API. If my module already exists, I can quickly build these templates and handlers as such:

begin
  ords.define_template(
      p_module_name    => '101',
      p_pattern        => 'multikey/:id1,id2,id3');
  ords.define_handler(
      p_module_name    => '101',
      p_pattern        => 'multikey/:id1,id2,id3',
      p_source_type    => ords.source_type_collection_item,
      p_source         => 'select * from three_col_pk where id1 = :id1 and id2=:id2 and id3=:id3');
  commit; 
end;

That’s not really a trick…but I don’t want folks to lose sight of the fact that we’re API driven. We build the PL/SQL and REST APIs for the underlying pieces, and then we build the fancy command line interfaces and web applications on top of those APIs.

Don’t forget about AutoREST – this works ‘out of the box’

I’ve talked about this multi-key scenario via a REST enabled table before. That post is itnersting because I cover two ‘extremes’ – no primary keys or primary keys with multiple columns.