How to Use SODA for REST with OAuth Client Credentials in your Node.js/Web App to CRUD Oracle Autonomous Database?


Use SODA for REST with OAuth Client Credentials in your Node.js or Web application to CRUD Oracle Autonomous Database

Prerequisites

First of all, you need to provision an autonomous Database. You can follow this guide to create an always-free Oracle Autonomous Database.
Once the database is ready, please open the 'Database Actions' webpage to continue.

Prepare JSON database

Create JSON Collection

I created a collection named 'TEST' and new a JSON document of 

{
  "hello": "world!"
}
I am like to learn new things with a "hello world" example. :)







Enable REST

The new JSON collection is also a table, you can view it in the 'SQL' panel under the 'Development' dashboard.
















Right-click the table, you will see the menu to enable REST for this table.











Remember the 'Preview URL', which will be the base URL for your ADT Oracle Rest Data Services.
https://gb155dd5f199f4a-dev.adb.us-sanjose-1.oraclecloudapps.com/ords/


Check the 'AUTOREST' in the 'REST' pannel


 






Since we enable authorization for this REST resource, you can't view it in your browser directly.







You can edit the 'TEST' auto rest object, and temporarily opt-out 'Require Authentication'.
Remember to opt-in again for security.
Then you will be able to view this resource when you click the 'Open in new tab' icon.




As you can see, the 'data' field is a kind of binary string(in OSNS format), you are not able to read.
Don't worry, you will be able to see the 'Hello World!' JSON soon.

Create OAuth Client Credentials













You will have a client id and client secret for this new OAuth client. They will be used to obtain the authorization token to request the above JSON collection.

Grant 'SQL Developer' and 'SODA Developer' roles

Go to the 'SQL' panel and execute the below PL script.








With these two roles, then you can use SODA for REST to fetch the JSON collection.
Or you will get 401 unauthorized errors. :(

Use SODA for REST with OAuth Client in your Node.js/ Web app

Before we write the javascript codes to fetch the JSON collection against Oracle Autonomous Database, let's see we can get from there with the non-require-authorization option first.

https://gb155dd5f199f4a-dev.adb.us-sanjose-1.oraclecloudapps.com/ords/admin/test/
{"items":[{"id":"622335d20a71f2f79701977f","created_on":"2022-03-05T10:05:06.480Z","last_modified":"2022-03-05T10:05:06.480Z","version":"4235C7BBF3814357B88E4B95C9E70C80","data":"/0paASEGAgAKABwAAKuzAAQAAANfaWQFaGVsbG+EAgIBAAgAFn4MYiM10gpx8veXAZd/BXdvcmxk","links":[{"rel":"self","href":"https://gb155dd5f199f4a-dev.adb.us-sanjose-1.oraclecloudapps.com/ords/admin/test/622335d20a71f2f79701977f"}]}],"hasMore":false,"limit":25,"offset":0,"count":1,"links":[{"rel":"self","href":"https://gb155dd5f199f4a-dev.adb.us-sanjose-1.oraclecloudapps.com/ords/admin/test/"},{"rel":"edit","href":"https://gb155dd5f199f4a-dev.adb.us-sanjose-1.oraclecloudapps.com/ords/admin/test/"},{"rel":"describedby","href":"https://gb155dd5f199f4a-dev.adb.us-sanjose-1.oraclecloudapps.com/ords/admin/metadata-catalog/test/"},{"rel":"first","href":"https://gb155dd5f199f4a-dev.adb.us-sanjose-1.oraclecloudapps.com/ords/admin/test/"}]}
https://gb155dd5f199f4a-dev.adb.us-sanjose-1.oraclecloudapps.com/ords/admin/soda/latest/TEST
{"items":[{"id":"622335d20a71f2f79701977f","etag":"4235C7BBF3814357B88E4B95C9E70C80","lastModified":"2022-03-05T10:05:06.480908000Z","created":"2022-03-05T10:05:06.480908000Z","links":[{"rel":"self","href":"https://gb155dd5f199f4a-dev.adb.us-sanjose-1.oraclecloudapps.com:443/ords/admin/soda/latest/TEST/622335d20a71f2f79701977f"}],"value":{"_id":"622335d20a71f2f79701977f","hello":"world"}}],"hasMore":false,"count":1,"offset":0,"limit":100,"totalResults":1,"links":[{"rel":"query","href":"https://gb155dd5f199f4a-dev.adb.us-sanjose-1.oraclecloudapps.com:443/ords/admin/soda/latest/TEST?action=query","method":"POST"},{"rel":"bulk-create","href":"https://gb155dd5f199f4a-dev.adb.us-sanjose-1.oraclecloudapps.com:443/ords/admin/soda/latest/TEST?action=insert","method":"POST"},{"rel":"replace","href":"https://gb155dd5f199f4a-dev.adb.us-sanjose-1.oraclecloudapps.com:443/ords/admin/soda/latest/TEST?action=replace","method":"POST"},{"rel":"delete-matching","href":"https://gb155dd5f199f4a-dev.adb.us-sanjose-1.oraclecloudapps.com:443/ords/admin/soda/latest/TEST?action=delete","method":"POST"},{"rel":"patch-matching","href":"https://gb155dd5f199f4a-dev.adb.us-sanjose-1.oraclecloudapps.com:443/ords/admin/soda/latest/TEST?action=update","method":"POST"},{"rel":"create-index","href":"https://gb155dd5f199f4a-dev.adb.us-sanjose-1.oraclecloudapps.com:443/ords/admin/soda/latest/TEST?action=index","method":"POST"},{"rel":"delete-index","href":"https://gb155dd5f199f4a-dev.adb.us-sanjose-1.oraclecloudapps.com:443/ords/admin/soda/latest/TEST?action=unindex","method":"POST"}]}
You see the "hello": "world", aren't you?
With the `soda/latest` REST, ORDS will return you the JSON object rather than the OSON JSON format string.

Obtain the access token

schema is your username, here I use 'ADMIN' since I set up everything above under the 'ADMIN' account of the database.
ords_url is the base URL of the Oracle Rest Data Service that bridges your JSON database.
https://gb155dd5f199f4a-dev.adb.us-sanjose-1.oraclecloudapps.com/ords/
client_id and client_secret the credentials of the OAuth client we created before.

const url = new URL(
path.join(schema, 'oauth/token'),
ords_url
).toString()
const auth_token = Buffer.from(
`${client_id}:${client_secret}`,
'utf-8'
).toString('base64')
const data = { grant_type: 'client_credentials' }
const config = {
headers: {
'content-type': 'application/x-www-form-urlencoded',
Authorization: `Basic ${auth_token}`
}
}
return axios.post(url, qs.stringify(data), config).then((res) => {
return res.data.access_token
})

CRUD your JSON database with the Bearer access token

In the AutoREST pannel, it provides you the cURL commands to access the protected resources.


Below javascript codes show how to get all data from the 'TEST' table.
access_token is what we get from the previous step.
alias is the JSON collection name.

const url = new URL(
path.join(schema, 'soda/latest', alias),
ords_url
)
const headers = {
'content-type': 'application/json',
Authorization: `Bearer ${access_token}`
}
const test = await axios.get(url.toString(), { headers })
console.log(test?.data?.items?.[0])

Run your code

you should be able to see the below output from your console.






You can pass more query parameters to filter the SODA REST results.

References

Comments

Post a Comment

Popular posts from this blog

How to fix error : no module named sendgrid when try to use sendgrid python lib in PHP.

Image
"no module named sendgrid" error reported with below codes: <?php system("/usr/bin/python sendmail.py 2>&1"); system("/usr/bin/python --version 2>&1", $ret); ?> sendmail.py import sendgrid import os from sendgrid.helpers.mail import * sg = sendgrid.SendGridAPIClient( apikey = os.environ.get( ' SENDGRID_API_KEY ' )) from_email = Email( " test@example.com " ) to_email = Email( " test@example.com " ) subject = " Sending with SendGrid is Fun " content = Content( " text/plain " , " and easy to do anywhere, even with Python " ) mail = Mail(from_email, subject, to_email, content) response = sg.client.mail.send.post( request_body = mail.get()) print (response.status_code) print (response.body) print (response.headers) ....
Read more

react-native run-android : sun.security.provider.cert path.SunCertPathBuilderException : unable to find valid certification path to req uested target

Image
F:\webrowser>react-native run-android Scanning folders for symlinks in F:\webrowser\node_modules (73ms) Starting JS server... Building and installing the app on the device (cd android && gradlew.bat install Debug)... Downloading https://services.gradle.org/distributions/gradle-4.1-all.zip Exception in thread "main" javax.net.ssl.SSLHandshakeException: sun.security.val idator.ValidatorException: PKIX path building failed: sun.security.provider.cert path.SunCertPathBuilderException: unable to find valid certification path to req uested target at sun.security.ssl.Alerts.getSSLException(Alerts.java:192) at sun.security.ssl.SSLSocketImpl.fatal(SSLSocketImpl.java:1959) at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:302) at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:296) at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker. java:1514) at sun.security.ssl.ClientHandshaker.processMessage(ClientHandshaker.jav a:216) at s
Read more

react-native run-android : do not build/update modified code(App.js)

Solution react-native bundle --platform android --dev false --entry-file index.js --bundle-output android/app/src/main/assets/index.android.bundle --assets-dest android/app/src/main/res c:\Users\lenger\Desktop\webrowser>react-native bundle --platform android --dev false --entry-file index.js --bundle-output android/app/src/main/assets/index.android.bundle --assets-dest android/app/src/main/res Scanning folders for symlinks in c:\Users\lenger\Desktop\webrowser\node_modules (43ms) Scanning folders for symlinks in c:\Users\lenger\Desktop\webrowser\node_modules (38ms) Loading dependency graph, done. bundle: start bundle: finish bundle: Writing bundle output to: android/app/src/main/assets/index.android.bundle bundle: Done writing bundle output Run react-native run-android again, you will find your modification work.
Read more