REST APIs and table inserts – a mix of HTTPS and SQL, could that be a marriage made in heaven? YES! And I’m here to share all the secrets to your future success!

This tutorial is a collection of related post, all tied up neatly together:

And, I’m going to try to do build a REST API that demonstrates all of our RESTful architectural behaviors and characteristics one would expect, AND takes advantage of some powerful Oracle Database features – hence, the ‘definitive’ label.

First, we need a TABLE.

A boring little EMPS table

CREATE TABLE EMPS 
    ( 
     ID              INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY 
        ( START WITH 1 CACHE 20 ) , 
     NAME            VARCHAR2 (100) , 
     SALARY          INTEGER , 
     JOB             VARCHAR2 (100) , 
     EVERYTHING_ELSE CLOB 
    ) 
    LOGGING 
;
 
ALTER TABLE EMPS 
    ADD CONSTRAINT EMPS_PK PRIMARY KEY ( ID ) 
    USING INDEX LOGGING ;
 
 
ALTER TABLE EMPS
    ADD  CONSTRAINT itsjsonipromise CHECK ( everything_else IS JSON) ;
Or, if you prefer visuals…

The Primary Key (ID)

The ID columns is defined using the ‘AS IDENTITY’ clause – so you’ll need to be on Database 12c or higher to use this. It means no sequence or trigger to maintain – the database does this for us now.

Perhaps the most interesting phrase here is the ‘BY DEFAULT ON NULL.’

That means I can be lazy and let the database give me the ID value, or I can be particular and supply it myself.

Let me demonstrate:

I can include the ID or not – it’s my choice, and I’ve seen customers want it both ways.

The IS JSON CHECK Constraint

EVERYTHING_ELSE is a Character Large Object, or CLOB. I called it a ‘claw-b’, but most folks refer to it as a ‘sea-lob.’ I’m telling the database that I’m going to make sure only valid JSON documents are inserted into that column.

I’ll need to be on 12c or higher for this to work. And the higher you go, the more useful JSON features you get directly out of the database.

These types of columns are useful if you need a flexible data model – it’s not hard coded into the table, or if you’re running out of available columns (the database currently restricts you to 1,000 per table.)

This table design isn’t very good.

Obviously, name should be split out to multiple fields. JOB should be a look-up field to a different table via foreign key, probably. I’m not trying to teach you data modeling in this post, rather, how to get your ROWS inserted, and we need to start with a TABLE. Substitute your own table…and that table can have IDENTITY columns AND a single LOB…and we’ll be able to populate it with a single HTTP(S) POST!

Our REST API

I’m going to have two templates, with a total of 3 handlers:

  • emps/
    • GET all emps
    • POST a new emp
  • emps/:id
    • GET the emp
    • I could add a PUT and DELETE, but I’m going to stop here for brevity/time

I’m going to use a PL/SQL block for doing the POST. That will be a direct INSERT to the table. Yes, I could have it all be done via PL/SQL with a procedure call (which also does an INSERT), and you see that here.

I’m going to show you a picture of the POST code to do the insert before I show the actual code. I hope that tells most of the story, but I’ll add some detail if you want to keep scrolling.

How data is getting from the HTTP(S) request to the columns in a new row in our table.
Some values off of the headers, the JSON doc off of the POST request, which is application/json.
 INSERT INTO emps 
     (id, name, salary, job, everything_else)
    VALUES
     (:id, :name, :salary, :job, :BODY)
    returning id INTO new_id;

It’s a VERY SIMPLE INSERT. But where is the data coming from, and how is it getting there?

We get immediate access to the BODY section in the anon block of the POST Handler via the Implicit bind, :body.

But for the headers, we have to tell ORDS about those in advance. Those are defined in the parameters section.

About those Parameters

HANDLER parameters allow us to interact with the HTTP requests and responses. We can read things from the request header and/or body, AND we can build the response header and/or body.

The parameter name will be the JSON attribute name in the body, or it will be the name of the header. The :bind variable is how the value is referenced in the SQL or PL/SQL block.

You control how the headers are named for the request, & the :binds are named for the handler block.

Some of these parameters are IMPLICIT – meaning, you don’t have to define them, ORDS does that for us. :body is an implicit bind that allows us to work with the HTTP request body as a BLOB variable. That’s why I can do an insert value (:body) without having to declare it in the parameters.

A Quick Detour: INSERT A ROW, NO HEADERS/PARAMETERS

Now, normally to do an INSERT, you wouldn’t need to declare any parameters. The record could be expressed in a JSON doc in the request body. That could look like this, where the green text is the POST HANDLER SQL code. ORDS automatically takes the top level JSON attributes in your PUT or POST, and gives your corresponding :binds, like so –

If we didn’t want to bother with a BLOB or CLOB, no need to use headers at all.

Here’s how that would look, for reals. I created an alternative set of templates:

  • emps_no_lob/
  • emps_no_lob/:id
REST APIs and table inserts, how to do this for your application and Oracle Database

POST up your JSON doc, do your INSERT.

However, I called this post the ‘Definitive Guide…,” so I wanted to be exhaustive and cover this more complicated example with a LOB in the INSERT.

But Jeff, I hear you saying, my table has 13 LOBs. Well, I would say create your POST to create the record, and then have APIs to managed each of those LOBs.

Anyway, back to our normal programming and definitive example!

The Response

Let’s look at our handler code again.

We added a row to our table, or an item to our collection, so 201 is more appropriate than 200.

What does :forward_location do?

Specifies the location where Oracle REST Data Services must forward a GET request to produce the response for this request. Docs

So, our REST API is inserting a record. I may or may not know what the ID is of the new record until I do the insert. So the nice response is both a 201, AND we’ll just take you to the new record.

In the response I get the ‘201 Created’ but also I’m forwarded to the new record so I can see it.

In the handler code, :forward_location is set to the ID value returned from the INSERT, in this case ‘9.’ ORDS appends that to the template, /emps/ to get /emps/9. And /emps/9 maps to the /emps/:id template, which as a GET handler.

I show the GET handlers/templates for 1 or all records, scroll down.

Prepare for when things do NOT work

In general if your SQL or PL/SQL fails, ORDS will respond with a HTTPS 500. 500’s are something to be avoided at all costs. Why? Because they don’t generally help your API consumers from figuring out what they’re doing wrong.

If anything, a 500 indicates the back end (ORDS) is having issues, not that a user is causing problems by exercising the APIs incorrectly.

When using PL/SQL, you ALWAYS want to trap errors – and use some mechanism to return back a meaningful error message back to the user, and give them some sort of recourse for fixing it. In the database, that’s done with Exceptions, and optionally a logging table.

Let’s just look at the Exceptions in our POST handler block of code.

These are VERY different types of responses when something goes wrong.

On the first exception, I’m saying, hey I know that’s happening here. The user is doing X instead of Y, and I’m going to tell them what to do instead.

That’s my message back to the API consumer, not the database’s.

On the second exception, I’m saying, hmmm, who knows else could break, but if it does, send back a 400 and just go ahead and share the DATABASE specific SQL or PL/SQL error. That COULD BE BAD. You may not even want to let your API subscribers know they’re even talking to an Oracle Database.

The Database’s response to that INSERT statement.

So NOW our API consumer knows it’s Oracle. They know the table name. They know the column name. In general, relying on WHEN OTHERS exceptions is not a best practice. Building a web application, I would not disclose this information I don’t think. I would cover all the base exceptions…and in the APP itself, I would do value checking before I even submitted the payload to the API, but that’s a different topic.

If you’ve inserted a row, you probably want to GET it, right?

You’ve seen in the POST we had a forward parameter set to redirect the client to the record once it’s been inserted. But for that to work, you need to define the record template (/emps/:id) and the corresponding GET handler.

One of the fun parts for me when building out APIs is deciding which thing to build first. Do I code the GET before I have any data to retrieve, or do I build the POST, with a worthless response because I haven’t build the GET yet?

I usually build the GET first, and then seed the table with dummy data using SQLcl, but that’s just me.

So let’s GET the record, by it’s ID or primary key.

Two tricks shown here.

Tricks you say? So what are we doing, it looks simple enough, right?

  • the template /emps/:id = we can access the value in the handler block via a bind, :id
  • EVERYTHING_ELSE is a lob, that’s storing JSON, so I tell ords that via the “{}alias”

Alright, but we might want to also GET all the records, too.

No need for a WHERE clause here, it’s just a SELECT * FROM, right?

Well, almost.

We’re showing the formatted json again with the {}bonus_content column alias.

Do you see the other thing we’re doing? We’re including a LINK to the individual records. That’s a nice thing, you don’t HAVE to do, but SHOULD do. The column aliased with a “$” will automatically be generated as a links [] array item, with the same name. So $id turns into a “rel”:”id” link.

If you’re building these APIs, it’s necessary for you to take these steps. You want the APIs to be reliable, friendly, and to behave in a ‘RESTful’ manner. The more you deviate from these characteristics, the less likely developers will be flocking to your ecosystem.

But Jeff, you say, I’m in a hurry, and need a Hello World app, like, today?

But what about just using AutoREST?

REST Enabling a TABLE or VIEW is great. No code, you just get all the APIs, including the POSTs to add one or more records.

And not just a POST to INSERT a record, you get a PUT for doing the update.

You’re using SQL Developer Web to build your REST APIs, right?

But.

It’s going to work the one way, the way we’ve built it. So it’s ALL fields. There’s no custom exception handling or responses.

And in your applications, adding a ‘Sale’ to the system is probably more than doing an INSERT to a SALES table. There’s probably a stored procedure that does MANY things…and so in your API, substitute my INSERT with your procedure call. If you ask @oraclebase about AutoREST, he’ll basically come down to this issue as a deal-breaker for him.

An example of calling a stored procedure/function vs running raw selects/inserts.

But what about Security?

Finally, one MUST lock down this module or template patterns with a Privilege. And then create an OAuth2 client with the role necessary to access the APIs. It’s more complicated than this, but this post is already too long, and security in general is a bigger topic.

If you don’t do this, whoever can access your webserver (ORDS), can use this REST API.

Last but not least, the Code

This code isn’t product ready or safe. It’s demonstrative of core concepts. Use it to learn how things work, and then apply your own application/experience perspective to it.

-- Generated by ORDS REST Data Services 21.4.0.r3481956
-- Schema: RESTDEV1  Date: Thu Jan 06 10:35:24 2022 
--
 
CREATE TABLE EMPS 
    ( 
     ID              INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY 
        ( START WITH 1 CACHE 20 ) , 
     NAME            VARCHAR2 (100) , 
     SALARY          INTEGER , 
     JOB             VARCHAR2 (100) , 
     EVERYTHING_ELSE CLOB 
    ) 
    LOGGING 
;
 
ALTER TABLE EMPS 
    ADD CONSTRAINT EMPS_PK PRIMARY KEY ( ID ) 
    USING INDEX LOGGING ;
 
 
ALTER TABLE EMPS
    ADD  CONSTRAINT itsjsonipromise CHECK ( everything_else IS JSON) ;
 
 
BEGIN
 
ORDS.DEFINE_MODULE(
      p_module_name    => '101',
      p_base_path      => '/101/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => 'most basic examples');
 
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => '101',
      p_pattern        => 'tables/emps/',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => 'table example, and our favorite type of data, employees...human capital!?!');
 
  ORDS.DEFINE_HANDLER(
      p_module_name    => '101',
      p_pattern        => 'tables/emps/',
      p_method         => 'GET',
      p_source_type    => 'json/collection',
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'select id "$id", job, name, salary, EVERYTHING_ELSE "{}bonus_content"
from EMPS
order by id asc');
 
  ORDS.DEFINE_HANDLER(
      p_module_name    => '101',
      p_pattern        => 'tables/emps/',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'DECLARE
 new_id integer;
BEGIN
    insert into emps 
     (id, name, salary, job, everything_else)
    VALUES
     (:id, :name, :salary, :job, :body)
    returning id into new_id;
    :status_code := 201;
    :forward_location := new_id;
exception
  WHEN VALUE_ERROR
        THEN
            :errmsg := ''ID and SALARY must both be numbers.'';
            :status_code := 400;
 when others then 
	:status_code := 400;
	:errmsg := sqlerrm;
end;');
 
  ORDS.DEFINE_PARAMETER(
      p_module_name        => '101',
      p_pattern            => 'tables/emps/',
      p_method             => 'POST',
      p_name               => 'ErrorMessage',
      p_bind_variable_name => 'errmsg',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'STRING',
      p_access_method      => 'OUT',
      p_comments           => 'exception errmsg from a failed INSERT');
 
  ORDS.DEFINE_PARAMETER(
      p_module_name        => '101',
      p_pattern            => 'tables/emps/',
      p_method             => 'POST',
      p_name               => 'salary',
      p_bind_variable_name => 'salary',
      p_source_type        => 'HEADER',
      p_param_type         => 'INT',
      p_access_method      => 'IN',
      p_comments           => NULL);
 
  ORDS.DEFINE_PARAMETER(
      p_module_name        => '101',
      p_pattern            => 'tables/emps/',
      p_method             => 'POST',
      p_name               => 'id',
      p_bind_variable_name => 'id',
      p_source_type        => 'HEADER',
      p_param_type         => 'INT',
      p_access_method      => 'IN',
      p_comments           => NULL);
 
  ORDS.DEFINE_PARAMETER(
      p_module_name        => '101',
      p_pattern            => 'tables/emps/',
      p_method             => 'POST',
      p_name               => 'job',
      p_bind_variable_name => 'job',
      p_source_type        => 'HEADER',
      p_param_type         => 'STRING',
      p_access_method      => 'IN',
      p_comments           => NULL);
 
  ORDS.DEFINE_PARAMETER(
      p_module_name        => '101',
      p_pattern            => 'tables/emps/',
      p_method             => 'POST',
      p_name               => 'name',
      p_bind_variable_name => 'name',
      p_source_type        => 'HEADER',
      p_param_type         => 'STRING',
      p_access_method      => 'IN',
      p_comments           => NULL);
 
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => '101',
      p_pattern        => 'tables/emps/:id',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
 
  ORDS.DEFINE_HANDLER(
      p_module_name    => '101',
      p_pattern        => 'tables/emps/:id',
      p_method         => 'GET',
      p_source_type    => 'json/item',
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'select id, NAME, job, SALARY, EVERYTHING_ELSE "{}extra"
from EMPS
where ID = :id');
 
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => '101',
      p_pattern        => 'tables/emps_no_lob/',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
 
  ORDS.DEFINE_HANDLER(
      p_module_name    => '101',
      p_pattern        => 'tables/emps_no_lob/',
      p_method         => 'GET',
      p_source_type    => 'json/collection',
      p_items_per_page => 25,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'select id "$id", job, name, salary
from EMPS
order by id asc');
 
  ORDS.DEFINE_HANDLER(
      p_module_name    => '101',
      p_pattern        => 'tables/emps_no_lob/',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'DECLARE
 new_id integer;
BEGIN
    insert into emps 
     (id, name, salary, job)
    VALUES
     (:id, :name, :salary, :job) 
    returning id into new_id;
    :status_code := 201;
    :forward_location := new_id;
exception
  WHEN INVALID_NUMBER	
        THEN
            :errmsg := ''ID and SALARY must both be numbers.'';
            :status_code := 400;
 when others then 
	:status_code := 400;
	:errmsg := sqlerrm;
end;');
 
  ORDS.DEFINE_PARAMETER(
      p_module_name        => '101',
      p_pattern            => 'tables/emps_no_lob/',
      p_method             => 'POST',
      p_name               => 'error',
      p_bind_variable_name => 'errmsg',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'STRING',
      p_access_method      => 'OUT',
      p_comments           => NULL);
 
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => '101',
      p_pattern        => 'tables/emps_no_lob/:id',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
 
  ORDS.DEFINE_HANDLER(
      p_module_name    => '101',
      p_pattern        => 'tables/emps_no_lob/:id',
      p_method         => 'GET',
      p_source_type    => 'json/item',
      p_items_per_page => 25,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'select id, NAME, job, SALARY
from EMPS
where ID = :id');
 
 
 
COMMIT;
 
END;
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Write A Comment