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.
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.
No comments:
Post a Comment