Help us understand the problem. What is going on with this article?

Create a REST API for Blog App with Oracle Autonomous Database's REST Data Services

Background

Around one month ago, REST Services get available for Oracle Autonomous Databases (ATP/ADW), that means you can develop and deploy REST Services for your Autonomous Database with native Oracle REST Data Services (ORDS) support.

We already know that Oracle Autonomous Database is super easy to use, you just need to click several buttons and you will get a fully-featured, robust and performant database for your business.

Is it still the same for the ORDS in ADB? Let's build a typical blog application (REST APIs) to check it out.

Get ready to start (Environment preparation)

Create an ATP Instance

As I mentioned above, it's super easy, just several clicks and minutes. You will get an ATP Instance and ready to go. Don't forget that if your business increases very fast, you can scale up the OCPU and storage at any time without downtime.

image.png

Now we get an ATP Instance.

image.png

Create application schema and enable REST Services

On the ATP instance web console, click the DB Connection button and download the credentials(Wallet) file.

image.png

After download the wallet file, create a new connection with the default user(admin) in SQL Developer

image.png

In the worksheet, create a database user and grant necessary privileges. This user(schema) will be used for our blog application.

-- Create DB user
CREATE USER ORDSBLOG IDENTIFIED BY <PASSWORD>;

-- Grant privileges
GRANT CREATE SESSION TO ORDSBLOG;
GRANT DWROLE TO ORDSBLOG;
GRANT UNLIMITED TABLESPACE TO ORDSBLOG;

After the user is created, create a new connection for this user in SQL Developer.

image.png

After you connect to the new connection, right-click on the connection name and click the "Enable REST Services..." to enable REST service for this schema.

image.png

Select the checkbox "Enable schema".

image.png

Click Next and Finish to make it done.

In the worksheet of the DB user connection we just created, execute the following SQL commands to create two tables, they will be used for the blog application.

image.png

-- Create tables 
CREATE TABLE USERS (
    ID      NUMBER GENERATED ALWAYS AS IDENTITY,
    NAME    VARCHAR2(200)  NOT NULL,
    CONSTRAINT USER_ID_PK PRIMARY KEY (ID)
);

CREATE TABLE BLOGS (
    ID          NUMBER GENERATED ALWAYS AS IDENTITY,
    AUTHOR_ID   NUMBER  NOT NULL CONSTRAINT USER_ID_FK
                REFERENCES USERS(ID),
    TITLE       VARCHAR2(200),
    CONTENT     VARCHAR2(32767),
    CONSTRAINT BLOG_ID_PK PRIMARY KEY (ID)
);

Until now, we have completed all the preparation work. Next, we will start to build REST API for some specific resources(tables).

The easiest way to start using ORDS in ADB (not recommended)

We can just enable REST Service on a table, then you will get REST APIs for this table. But I highly recommend you to use the "REST Data Services" to create Modules/Templates/Handlers to get better fine graded control of the REST Services. We will talk more about how to use the "REST Data Services" later. Here let's have a quick look of the table content through REST API.

image.png

For simplicity, we don't require authorization here. Un-select the "Authorization required" checkbox.

image.png

Review what we are going to create and click Finish.

image.png

Now, the REST API for the table users is ready to use. But what URI endpoint we should use to access the API? We could get this information from the Service Console of the ATP Instance we just created.

Visit the OCI we console and go the page of your ATP Instance, click Service Console

image.png

then click Developer and right click the SQL Developer Web tile and copy the link of it.

image.png
image.png

We should get a link as below. Replace the admin/_sdw/?nav=worksheet part with the schema name you created and the table name you just enabled REST service on.

Old: https://<DB_NAME>.adb.<REGION>.oraclecloudapps.com/ords/admin/_sdw/?nav=worksheet
New: https://<DB_NAME>.adb.<REGION>.oraclecloudapps.com/ords/ordsblog/users/

Since we did not enable authorization check when we enable the REST service, we should be able to access the new link (REST API endpoint) and get a JSON format response directly, as shown in below screenshot.

We use the convenient tool postman to test our REST APIs here. You could also use CURL or any other tools to do so.

No record in the table users, so items is [].

image.png

We could even click the describedby link to check the metadata of the table users.

image.png
image.png

As you can see, as long as we have an ADB Instance, we just need to enable REST service, then we can have REST API access to our tables. So easy, isn't it?

A better way to use ORDS in ADB (fine graded control)

The way introduced above is easy and simple, but we lose control of access to our resources. To have a fine graded control to your resources(tables), I suggest using the "Modules" and "Services" under REST Data Services which is listed in the connection navigator bar.

image.png

Before we move forward, let's disable the REST service we just enabled on the table users. No one wants their data to get exposed without control.

Un-check the "Enable object" checkbox would be fine.

image.png

Once the REST service of the table users is disabled, we would get "404 Not Found" error when we access the same link. That's what we expected.

image.png

First module/template/handler to retrieve the users

Right click the Modules under REST Data Services and click New Module to create a new module.

image.png

Input the Module Name and URI Prefix, select the checkbox "Publish – Make this RESTful Service available for use". That's what we want.

image.png

Click Next and specify a template. We are going to use this URI to provide the registration function of new user.

image.png

Review the summary and click Finish.

image.png

Now, we should see a Module called users and a template called new is created.

image.png

This means we just created a REST API endpoint as below.

https://<DB_NAME>.adb.<REGION>.oraclecloudapps.com/ords/ordsblog/users/new

If you try to access this link you will still get "404 Not Found" error because we have not added any handler for this endpoint.

Although we planned to use this endpoint to provide registration function, for test purpose, we add a GET handler for this endpoint for now.

image.png

Input a simple query "select * from users" to the table users in the handler worksheet and save it.

image.png

Then try to access the endpoint again.

https://<DB_NAME>.adb.<REGION>.oraclecloudapps.com/ords/ordsblog/users/new

Yeah! The API works!

image.png

Register a new user (POST)

Our target is to use this endpoint to register new user, so let's add a POST handler to it.

Right click the template new and add a new POST handler.

image.png

Just click Apply

image.png

To handle the POST request is a little bit complex, we need to take care of the input parameters and the output response contents. We use a procedure to handle the creating of a new user as below.

image.png

create or replace PROCEDURE ADD_USER (
    P_USER_NAME    IN  USERS.name%TYPE,
    P_OUT_ID      OUT USERS.id%TYPE,
    P_OUT_TOTAL   OUT INTEGER
)
AS
BEGIN
    INSERT INTO USERS (name)
    VALUES (P_USER_NAME)
    RETURN id INTO P_OUT_ID;

    SELECT COUNT(id) INTO P_OUT_TOTAL FROM USERS;

EXCEPTION
    WHEN OTHERS
    THEN HTP.print(SQLERRM);
END;

In the POST handler, we call the procedure we just created and pass in the parameters we get from the request.

image.png

BEGIN
    ADD_USER(P_USER_NAME   => :USER_NAME,
            P_OUT_ID       => :NEW_USER_ID,
            P_OUT_TOTAL    => :TOTAL);
commit;
END;

For the response, we want to return the newly created user id so that the client could decide what is the next move, and we also return the total number of all users. Set the Parameters as shown below.

image.png

Now, our POST handler is ready to go. We will post an user information in JSON format to create a new user. Please note that the keys in JSON must be the same as the definition in your handler procedure.

In postman client, select the request method as POST, input the endpoint URI, and input the user info in JSON format in the Body/Raw section. (You can also use an json file and select binary instead of raw)

image.png

{
    "USER_NAME":"user01"
}

We also need to set the "Content-Type" as "application/json" in Headers.

image.png

After we click the Send button we will see the response status is 200 OK and the new user id and the total number of users in the response.

image.png

In SQL Developer we could also verify the newly created user record.

image.png

Do you remember that we have a GET handler to retrieve all users? Let's try with it.

image.png

Cool, we can see the users, although there is only one yet.

Check the user profile (GET)

Once the user is registered, it is an obvious thing that we should provide the user info to show the user's profile.
Let's create a show endpoint to do this.

image.png

Add a GET handler to provide the user info.

image.png

image.png

Query in the handler is quite simple, we just need to provide the user id as a query parameter to complete the query.

image.png

Initiate a query as below, we will get the user info as expected.

https://<DB_NAME>.adb.<REGION>.oraclecloudapps.com/ords/ordsblog/users/show?id=6

image.png

Handle the blogs

You already see how easy it is to enable the REST Data Services for the users table. Let’s move forward to enable all the CRUD behaviors through REST API to the table blogs.

Post the first blog (POST)

I believe you already get familiar with this, similar to the new user handler. Create a procedure to handle the data insert and call the procedure in the POST handler.

image.png

You should not encounter any problem to see expected response.

image.png

List all my own blogs (GET)

It's still a GET request, the only difference is that pass in the user id so that the SQL query can retrieve the blogs belong to this guy.

image.png

image.png

Update my blog (PUT)

What if we want to update a blog we posted previously? Use the PUT method to do so.

Create a update template

image.png

Add a PUT handler to do the update work, although POST also works, but according to the REST API best practices, we should use PUT to indicate our intention, that's update here.

image.png

Procedure to handle the update of a blog is similar to the creating.

image.png

Call the update blog procedure in PUT handler.

image.png

In postman client, set the request method as PUT, input the new blog info in the Body section.

image.png

Verify the updated blog.

image.png

What? You don't have the show blog endpoint? Implement of such a GET handler is quite easy for you now I believe.

Remove a blog (DELETE)

If something in your blog is out of date and you don't want to remain it anymore, you can implement a DELETE handler to remove the blog any time.

Add a delete template

image.png

Create DELETE handler

image.png

Don't forget to pass in the blog id you want to remove.

image.png

In postman client, select request method as DELETE and send request the endpoint you just implemented.

image.png

You can only see the #2 blog now.

image.png

Secure the resources

Basic Auth

Until now, anyone can do anything to our resources (users, blogs). Obviously, we got to protect the resources and we can do so by leveraging the privileges in ORDS.

image.png

right click the Privileges under REST Data Services and create a new privilege. Input the Name, Title and Description. For simplicity, we protect all the modules by one privilege. Add all the modules(users, blogs) to the Protected Modules section.

image.png

We can see the newly created privilege Demo.

image.png

If we try to request for a user's info again, we would get 401 Unauthorized error as below.

image.png

If you are using a web browser to test the API, you could click the sign in and get pass by inputting the DB user credential.

In postman client, under Authorization tab, select TYPE as Basic Auth and input the DB user credential. Send the request you will get the user info.

image.png

Register an OAuth Client Application

Our target of building this REST API is to provide data access capability to the frontend or any other authorized third-party applications, Basic Auth is not the proper way to do so obviously.

OAuth 2.0 is a standard Internet protocol that provides a means for HTTP servers providing REST APIs to give limited access to third party applications on behalf of an end user.

  • The author of the third-party application must register the application to gain client credentials.
  • Using the client credentials the third party application starts a web flow that prompts the end-user to approve access.

As mentioned above, by using OAuth the client application can get access to the REST API by registering and getting approval. And the lucky thing is that this flow is embedded in ORDS by default.

To register a client application, we need to access the following URL:

https://<DB_NAME>.adb.<REGION>.oraclecloudapps.com/ords/ordsblog/oauth/clients/

image.png

Sign in with the DB user credential and we can see that there is no client registered yet.

image.png

Click the New Client, input the necessary information and click the Create New Client.

image.png

The client is registered but not approved yet. Click the Edit icon to show the details of this registered client.

image.png

We can see the generated Client identifier, Unique Value, and Authorization URI.

image.png

Click the Authorization URI and sign in with the DB user credential. You will see the client approval page. Click the Approve button to approve this request. Deny is also available for you.

image.png

Once you approved the request, the web page will be redirected to the URL you specified when you register the client. The key points are the following items in the redirected URL.

http://example.org/redirect#token_type=bearer&access_token=XErqh-jlHNSoXGe9JX5m7Q&expires_in=3600&state=f084f3c8-d567-e1c8-fb83-81aabacc9b54

  • Token_type: bearer
  • Access_token: XErqh-jlHNSoXGe9JX5m7Q
  • Expires_in: 3600
  • State:f084f3c8-d567-e1c8-fb83-81aabacc9b54

Token_type and access_token is the key information when your client application tries to access the REST API. You need to include them in every request to the REST API.

image.png

Now we have everything we need to access a protected REST API, let's try to get a user's profile with the access token.

In postman client, select Authorization Type as Bearer Token and input the access token. Then send your request, you should not see the 401 Unauthorized error anymore.

image.png

Not only the GET handler, as long as you provide the correct access token, you can access all the handlers, such as update blog.

image.png

image.png

In contrast, if the client application cannot provide the correct access token, it will be blocked out of our resources.

image.png

Now we have a secured and fully featured (sample) REST API for our blog application. And I believe you already get familiar with the basic usage of ORDS in Autonomous Database. If you want to know more about ORDS and Oracle Autonomous Database, please refer to the references below.

References

Other language versions

RexZheng
All views expressed on this site are my own and do not represent the opinions of any entity whatsoever with which I have been, am now, or will be affiliated.
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした