andersswanson.dev

Simple RESTful services with ORDS: Duality Views

In this article, we’ll create a RESTful webservice that can GET/POST/PUT/DELETE relational JSON documents. The best part? We’ll use just a few lines of PL/SQL and zero lines of backend code to implement our REST API with Oracle REST Data Services (ORDS) “Auto REST”.

New to creating RESTful services with ORDS? Check out my previous post and learn how to create a CRUD service for an Oracle Database table with a few lines of PL/SQL.

We’ll define our JSON document API using JSON Relational Duality Views, or custom read-write JSON views of a relational schema. Related data is sent as a single JSON document, using a single HTTP request.

To follow along with this article, create an Autonomous Database instance in Oracle Cloud’s Always-Free tier. ORDS is available for free with any Oracle Database instance, or bundled in the all-in-one ADB-free container image.

Starting out: Define a relational schema

Our RESTful service needs a schema to operate on – we’ll use a sample university schema with students, enrollments, and courses:

create user university identified by testPWD12345;

GRANT CREATE SESSION TO university;
GRANT UNLIMITED TABLESPACE TO university;
GRANT CONNECT, RESOURCE TO university;

Next, create the following tables in the university schema to represent a student and their course enrollments:

create table students (
    id         number(10) generated always as identity primary key,
    first_name varchar2(50) not null,
    last_name  varchar2(50) not null,
    email      varchar2(100),
    major      varchar2(50) not null,
    credits    number(10),
    gpa        binary_double
);

create table courses (
    id              number(10) generated always as identity primary key,
    name            varchar2(50) not null,
    description     varchar2(250) not null,
    credits         number check (credits between 0 and 10)
);

create table enrollments (
    student_id number(10) not null,
    course_id  number(10) not null,
    constraint student_course_pk primary key (student_id, course_id),
    constraint student_fk foreign key (student_id)
        references students(id) on delete cascade,
    constraint course_fk foreign key (course_id)
        references courses(id) on delete cascade
);

The university schema allows students to enroll in any number of courses, representing a many-to-many relationship joined by the enrollments table. The relationship, including the join table can be visualized like this:

A diagram illustrating the relational schema for a university database, showing the 'Students', 'Enrollments', and 'Courses' tables along with their respective fields and relationships.

Duality view for a many-to-many relationship

We’ll layer a JSON Relational Duality View over the university schema, capturing the many-to-many relationship in a single JSON document. The duality view definition allows read-write access to student data and course enrollments through the @insert and @update annotations: 

create force editionable json relational duality view students_dv as students @insert @update @delete {
  _id : id
  firstName : first_name
  lastName : last_name
  email
  major
  credits
  gpa
  -- join table implicitly links courses to students
  enrollments @insert @update @delete [ {
    -- courses table is unnested to form an array of course enrollments.
    courses @unnest @insert @update {
      _id : id
      name
      description
      credits
    }
  } ]
  };

This view definition also serves as an example: You can create as many duality views as you need for the underlying relational schema. For example, on the university schema, I could define a view specific to courses without student data, or another view with different permissions and column visibility.

Configure the schema and view for REST

Both the schema and view must be enabled for REST using the ORDS PL/SQL package.

First, enable the schema using the ORDS.ENABLE_SCHEMA PL/SQL procedure. This only needs to be done once per schema. Our schema will use /university as the base path for REST access.

begin
   ords.enable_schema(
      p_enabled             => true,
      p_schema              => 'UNIVERSITY',
      p_url_mapping_type    => 'BASE_PATH',
      p_url_mapping_pattern => 'university',
      p_auto_rest_auth      => false
   );
   commit;
end;
/

Next, enable the duality view for Auto REST using the ORDS.ENABLE_OBJECT PL/SQL procedure. The students_dv view will be available on the /university/students path. 

begin
   ords.enable_object(
      p_enabled      => true,
      p_schema       => 'UNIVERSITY',
      p_object       => 'STUDENTS_DV', -- name of table or view
      p_object_type  => 'VIEW', -- also supports 'TABLE'
      p_object_alias => 'students'
   );
   commit;
end;

After you run this PL/SQL statement, the duality view is available for REST access on ORDS!

Time to try it out!

To test things out, you’ll need your ORDS URL from the Tool Configuration -> Web Access (ORDS) section of the Autonomous Database UI. If you’re running ORDS elsewhere, you’ll need to use that HTTP/S endpoint.

Screenshot of Oracle Autonomous Database UI showing tool configuration settings and public access URL for Oracle REST Data Services (ORDS).

Once you have your ORDS URL, set it in your shell environment, so it can be used with cURL.

export ORDS_URL='https://<my database>.adb.<region>.oraclecloudapps.com/ords'

POST: Insert student, enrollment & course

First, let’s try creating a new student. Because we’re have a duality view that joins students, enrollments, and courses, we can create records for every table in a single HTTP request!

Let’s try it out:

curl -X POST $ORDS_URL/university/students/ \
  -H "Content-Type: application/json" \
  -u 'university:testPWD12345' \
  -d '{
    "firstName": "Alice",
    "lastName": "Smith",
    "email": "alice.smith@xyz.edu",
    "major": "Computer Science",
    "credits": 40,
    "gpa": 3.77,
    "enrollments": [
      {
        "name": "Intro to Computer Science",
        "description": "Fundamentals of CS",
        "credits": 4
      }
    ]
}'

In the HTTP response object, we see the created student, enrollment, and course – as well as relevant auto-generated links! Pretty cool stuff. The _metadata field is used to store the version number of the JSON document, and is managed by the database server.

{
  "_id": 1,
  "firstName": "Alice",
  "lastName": "Smith",
  "email": "alice.smith@xyz.edu",
  "major": "Computer Science",
  "credits": 40,
  "gpa": 3.77,
  "enrollments": [
    {
      "_id": 1,
      "name": "Intro to Computer Science",
      "description": "Fundamentals of CS",
      "credits": 4
    }
  ],
  "_metadata": {
    "etag": "6409E232EBBF241D130D2303BF260E2D",
    "asof": "000023C4FD8B6932"
  },
  "links": [
    {
      "rel": "self",
      "href": "<database URL>.adb.us-chicago-1.oraclecloudapps.com/ords/university/students/1"
    },
    {
      "rel": "describedby",
      "href": "<database URL>.adb.us-chicago-1.oraclecloudapps.com/ords/university/metadata-catalog/students/item"
    },
    {
      "rel": "collection",
      "href": "<database URL>.adb.us-chicago-1.oraclecloudapps.com/ords/university/students/"
    }
  ]
}

PUT: Add a course to an existing student

Let’s update the student we created, adding a new course enrollment. Again, we can do this with a single HTTP request:

curl -X PUT $ORDS_URL/university/students/1 \
  -H "Content-Type: application/json" \
  -u 'university:testPWD12345' \
  -d '{
  "_id": 1,
  "firstName": "Alice",
  "lastName": "Smith",
  "email": "alice.smith@xyz.edu",
  "major": "Computer Science",
  "credits": 40,
  "gpa": 3.77,
  "enrollments": [
    {
      "_id": 1,
      "name": "Intro to Computer Science",
      "description": "Fundamentals of CS",
      "credits": 4
    },
    {
      "name": "Calc 1",
      "description": "Derivatives and more!",
      "credits": 4
    }
  ]
}'

In the response object returned from ORDS, you can observe the created “Calc 1” course enrollment as part of the student document.

GET: By ID, page, filter, or all documents

We can fetch all students with a simple GET, which returns an items array of all students in the database:

curl -X GET $ORDS_URL/university/students/ \
  -u 'university:testPWD12345'

We can also GET students by ID, or use query parameters for pagination or filtering:

# Get by ID
curl -X GET $ORDS_URL/university/students/1 \
  -u 'university:testPWD12345'
# Get using query parameters
curl -X GET "$ORDS_URL/university/students/?first_name=Alice" \
  -u 'university:testPWD12345'
# Get with pagination
curl -X GET "$ORDS_URL/university/students/?offset=1&limit=5" \
  -u 'university:testPWD12345'

DELETE: By ID

Let’s delete the student we created by ID:

curl -X DELETE $ORDS_URL/university/students/1 \
  -u 'university:testPWD12345'

The student’s enrollments are also deleted, due to the relational integrity we included in our schema – no additional deletes are required!

Let’s verify this with a SQL query on the enrollments table. There should be zero rows returned by this query if you ran the prior DELETE command:

select * from enrollments where student_id = 1;

That’s all for our fully-functional, relational JSON document REST service! Note that while we used username/password authentication, I recommend securing your endpoints with OAuth 2.0 for production ORDS installations.

Where can I run ORDS?

You can run ORDS anywhere you run Oracle Database. If you’re running Autonomous Database, ORDS is automatically included as part of the managed database server on Oracle Cloud.

If you’re hosting Oracle Database in the cloud or on your own hardware, you can download, install, and configure ORDS to use your specific database instance.

References

Questions? Leave a comment or reach out to me on LinkedIn.

Leave a Reply

Discover more from andersswanson.dev

Subscribe now to keep reading and get access to the full archive.

Continue reading