REST APIs, public and OAuth2-protected

Turnout has always had one front door: its pages. This part opens two more. A public API lets anyone's website or phone app list a club's upcoming events. A protected API lets a club's own door scanner check people in, without a person signing in to Turnout.

Both are built with ORDS, Oracle REST Data Services, the same web listener that has been serving your APEX pages since Lab 1. And both call the same TO_ packages as the pages do, so an API can't break a rule the app enforces.

Before you start

You need the Turnout tables, sample data and packages: Parts 2 and 3. The app itself isn't needed for this part, though it's nice to have open to compare. Any part's Finished app from Part 4 on installs them as supporting objects.

Refresh the sample data. In SQL Workshop › SQL Scripts, run turnout_seed.sql again so the sample events are in the future.

How you'll call the API. A browser's address bar is enough for the public GETs. For the organizer routes you need to send a token, and on apex.oracle.com the simplest place to do that is SQL Commands itself: APEX_WEB_SERVICE calls your API over HTTPS exactly as an outside program would. Command-line tools like curl work against your own ORDS server, but apex.oracle.com sits behind a content network that blocks them: curl just hangs, or gets a 403 page. The downloads include the curl versions for when you run ORDS yourself.

Lesson 14.1 Design before you build

Outcome: you can read an API design and say why each URL, verb and status code is what it is.

An API is a promise. Once someone's app calls /events/, you can't rename it without breaking them, so it pays to decide the shape first.

Resources, not actions. A URL names a thing; the HTTP verb says what to do with it.

Three decisions are worth naming:

• v1 in the path. When version 2 changes a field, version 1 keeps working for the apps that use it.
• Slugs in public URLs. /events/tampa-runners/saturday-long-run is readable and stable; this is why Lesson 2.3 made slugs unique per club.
• One prefix for everything private. Every protected route starts with organizer/, so one ORDS privilege can guard them all. A new route under it is protected the day it's added.

Status codes tell the caller what happened, before it reads a word:

The API is another layer-4 caller. Part 6 put the last check in the packages, "so a crafted request, a bug in a page, a future REST API" can't get around it. This is that future API. The check-in handler calls to_rsvp_api.check_in, the same function as the check-in page, and that function checks the caller organizes the club. The API adds nothing it could get wrong.

Lesson 14.2 REST-enable your schema

Outcome: ORDS knows your schema, and you know its base URL.

On apex.oracle.com your schema is REST-enabled already. To see it, open SQL Workshop: the SQL Developer Web tile says "Configure schema REST access". Click it, and the dialog lists your schema with a Configure button. Close it again; you won't need SQL Developer Web for this part.

What you need is the schema's alias, the part of the URL that stands for your schema. In SQL Commands:

select pattern, status from user_ords_schemas

It shows one row, for example yourworkspace and ENABLED. Your base URL is https://oracleapex.com/ords/ plus that alias plus /. Every URL in this part starts there.

On your own database, where nobody has enabled it yet, the query returns no rows. Enable it once:

begin
    ords.enable_schema(p_enabled => true, p_url_mapping_type => 'BASE_PATH',
                       p_url_mapping_pattern => 'turnout');
    commit;
end;

Check: open the base URL plus metadata-catalog/ in a browser. You get a small JSON document, not an error page: ORDS is answering for your schema.

Lesson 14.3 The first endpoint, by hand

Outcome: GET /turnout/v1/events/ returns upcoming events as JSON, and you know where each part of it lives.

First, the shape. The API reads a view, never the tables: the view's columns are the promise, and the tables can change underneath without breaking anyone. Run this in SQL Commands:

create or replace view to_api_events_v as
select c.slug                                         as club,
       e.slug                                         as event,
       c.name                                         as club_name,
       e.title,
       e.description,
       to_char(e.starts_at at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"') as starts_at,
       to_char(e.ends_at   at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"') as ends_at,
       c.time_zone,
       to_char(e.starts_at at time zone c.time_zone, 'YYYY-MM-DD HH24:MI')   as starts_local,
       v.name                                         as venue,
       e.capacity,
       to_rsvp_api.spots_left(e.id)                   as spots_left,
       e.starts_at                                    as sort_key
  from to_events e
  join to_clubs c       on c.id = e.club_id
  left join to_venues v on v.id = e.venue_id
 where e.status     = 'PUBLISHED'
   and c.status     = 'ACTIVE'
   and c.visibility = 'PUBLIC'
   and e.ends_at    > systimestamp

Times go out in UTC with a Z, the format every programming language reads, plus starts_local in the club's own zone for people. And spots_left comes from the same function as the badges on Discover, so the API and the app can never disagree.

Now the endpoint. ORDS organizes an API in three levels, and the ORDS package has one call for each:

Run this in SQL Commands:

begin
    ords.define_module(
        p_module_name    => 'turnout.v1',
        p_base_path      => '/turnout/v1/',
        p_items_per_page => 25,
        p_status         => 'PUBLISHED',
        p_comments       => 'Turnout public API, version 1');

    ords.define_template(
        p_module_name => 'turnout.v1',
        p_pattern     => 'events/');

    ords.define_handler(
        p_module_name    => 'turnout.v1',
        p_pattern        => 'events/',
        p_method         => 'GET',
        p_source_type    => ords.source_type_collection_feed,
        p_items_per_page => 25,
        p_source         => q'[
select club, event, club_name, title, starts_at, ends_at, time_zone,
       starts_local, venue, capacity, spots_left
  from to_api_events_v
 order by sort_key]');
    commit;
end;

source_type_collection_feed means "run this query and return the rows as a JSON list, a page at a time". ORDS writes the JSON; you write the SQL.

Run it once. A second run stops with ORA-00060 or ORA-00001: the module is already there, and ORDS on apex.oracle.com can't redefine a module in place. The script in Lesson 14.4 checks first, so it's the one to run again.

Check: open your base URL plus turnout/v1/events/ in a browser. You get a JSON document with an items array of events, soonest first, "hasMore":true, and at the bottom a link with "rel":"next" ending in ?offset=25. Follow it and you get the next page.

Lesson 14.4 The rest of the API, and a client for the door

Outcome: the whole API exists, the organizer routes are locked behind OAuth2, and Tampa Runners has a client id and secret for its door scanner.

You've built one endpoint by hand. The others follow the same three levels, so a script makes them: download part14_rest_api.sql and run it in SQL Workshop › SQL Scripts. It finds the module you made in Lesson 14.3 and adds to it, so it's safe to run again. Then see what it made:

select t.uri_template, h.method, h.source_type
  from user_ords_modules m
  join user_ords_templates t on t.module_id = m.id
  join user_ords_handlers h  on h.template_id = t.id
 where m.name = 'turnout.v1'
 order by t.uri_template, h.method

How the protection works. A privilege protects URL patterns; a role is what a caller must have to pass it. ORDS checks the token before your code runs. No token, no handler: just a 401.

A client is a program, not a person. The door scanner signs in with client credentials: an id and a secret, swapped for a token that lasts an hour. It has no email and no APP_USER, so TO_SECURITY would see nobody. That's what TO_API_CLIENTS is for: the client tampa-runners-door is tied to Tampa Runners and acts as Priya, one of its organizers. Each organizer handler starts like this:

l_club := to_api.begin_call(:current_user);  -- look up the client, act as Priya,
                                             -- and check she still organizes the club
...
to_api.end_call;                             -- and stop acting, always

ORDS fills in :current_user from the token: for a client, it's the client's id, the random-looking string, not its name. TO_API.client_name looks the id up in USER_ORDS_CLIENTS to find the name, the key of TO_API_CLIENTS. If Priya stops being an organizer, the client stops working, with no change to the API.

Why end_call is in the exception handler too. ORDS keeps a pool of database sessions and hands the next request whichever is free. Package variables, like "acting as Priya", survive in that session. A handler that forgot to stop acting after an error would hand Priya's rights to the next caller. So the handler's exception block calls to_api.end_call before anything else.

Now make the client. Download part14_oauth_client.sql and run it. Its last statement shows the client id and client secret. Treat the secret as a password for a program. You won't need to copy them for this part: Lesson 14.5's script reads them from ORDS. A real door scanner would keep them in its own settings.

Check: in SQL Commands, select name from user_ords_privileges lists turnout.organizer, and select name from user_ords_roles lists the role of the same name.

Lesson 14.5 Call it with a token

Outcome: you get a token, use it, and see each answer the API can give.

The door scanner is a program, so play one. Download part14_try_it.sql, paste it into SQL Commands and Run. It does what the scanner would, over HTTPS, through ORDS's front door:

1. Calls the RSVP list without a token.
2. Swaps the client id and secret for a token at /oauth/token, with apex_web_service.oauth_authenticate.
3. Calls the RSVP list with the token, in an Authorization: Bearer header.
4. Checks Priya in, then tries the same ticket again.
5. Tries a Bay Chess Club ticket at a Tampa Runners event.

Check-in only works from an hour before the event starts until it ends, as in Lesson 7.12. Before you run the script, move Tuesday Track Intervals to now with the update from that lesson.

Check: the output reads like this.

The 400 messages are the package's own words. The rules are the check-in page's rules, because the code is the check-in page's code.

The same calls with curl. On your own ORDS server (not apex.oracle.com, see Before you start), part14_requests.txt has every call as a curl command, starting with the token:

curl -i -u 'CLIENT_ID:CLIENT_SECRET' -d 'grant_type=client_credentials' BASE/oauth/token

Lesson 14.6 Describe it with OpenAPI

Outcome: a machine-readable description of the API, and a page that shows it.

ORDS writes an OpenAPI description of every published module for you. Open your base URL plus open-api-catalog/turnout/v1/ in a browser: a JSON document listing every path, verb and parameter, with "openapi":"3.0.0" at the top.

To read it as a page:

1. In the browser, select all of that JSON and copy it.
2. Go to editor.swagger.io, clear the sample on the left, and paste. Swagger converts JSON if it asks.
3. The right side lists the four endpoints. Open GET /events/ to see its parameters and the replies it describes.

Try it out in Swagger won't reach your API, and that's on purpose: a page on another website may only call an API that says it allows that website. This is CORS, and ORDS allows nobody by default. To let a site's pages call the public routes, an ORDS module can name the origins it trusts (ords.set_module_origins_allowed).

The description is only as good as what you tell ORDS. The module's comments, "Turnout public API, version 1", became the description's description. Handlers can have comments too: add them, and a developer reading the description knows what spots_left means without asking you.

Checkpoint

Turnout has an API: two public endpoints anyone can call, and two organizer endpoints that only a club's client can, every one of them going through the same packages as the pages.

The app itself didn't change in this part, so there's no new Finished app: Part 13's is still current.

Going further. - Add GET /turnout/v1/clubs/ for active public clubs, with events as a link to each club's events. - Give the door client its own role, turnout.door, that can check in but not read RSVPs. Which privilege pattern do you need? - ORDS can AutoREST-enable a view in one click. Try it on TO_API_EVENTS_V, and compare what you get with what you designed.