REST APIs and table inserts, or 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:
- working with IDENTITY columns
- grabbing columns off the header AND body
- catching exceptions and avoiding HTTP 500’s
- getting the primary key value from an INSERT
- changing the status from 200 to 201
- forwarding to the new row (item) in the response
- escaping JSON in our responses
And, I’m going to share with you REST APIs that demonstrate all of our RESTful architectural behaviors and characteristics one would expect. Plus, also taking advantage of some powerful Oracle Database features – hence, the ‘definitive’ label.
Let’s get started!
First things first, we need a simple 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) ;
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:
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.
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.
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 –
Here’s how that would look, for reals. I created an alternative set of templates:
- emps_no_lob/
- emps_no_lob/:id
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.
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 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.
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.
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.
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.
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.
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.
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;
If you recreate this in your local environment, the OpenAPI doc for your new demo REST APIs will look like this –
