JMJ CLOUD
  • Home
  • Projects
  • Blog
  • About Us
  • Contact Us

Our Blog

Accessing AWS S3 from APEX with API Gateway & Lambda

10/4/2018

2 Comments

 

Introduction

Simplfying Access to AWS S3 from APEX Using API Gateway and Lambda
​Over the past 6 months, APEX has been the foundation for several innovative solutions provided by JMJ Cloud. Our most recent project leads us to the world of Amazon Web Services (AWS) with API Gateway, Lambda (serverless/function as a service) and S3 object storage. In this post, I will show you how we built a cost-effective solution to simplify posting files to S3 from APEX using API Gateway and Lambda.

​The Requirement

A few months ago, JMJ Cloud built a native mobile iOS App for a major manufacturing client
(Mobilize your Oracle Database with ORDS). Amongst other things, the App allowed shop floor workers to take pictures of images throughout the manufacturing process and post them to Amazon S3 storage. In addition to the iOS App, we built a large APEX application (integrated with Oracle EBS) for front office staff to manage jobs in the plant. The requirement that lead me to this blog post is to provide the ability for the desktop APEX users to post documents and files to S3.

The Challenge

AWS does not have an SDK for S3 and PL/SQL (no surprises there). This makes managing objects in S3 from APEX and or PL/SQL a major challenge. The number of steps AWS make you go through to call their S3 web services are overwhelming and if you are on R11 of the database (with no dbms_crypto to create a SHA-256 hash) it is almost impossible to achieve. Some folks have tried to solve this problem through open source solutions such as Alexandria (link). The problem is that as Amazon evolves its S3 offering, the open source solutions aren’t always able to keep up. We needed a way to simplify the interface with S3 and use this from multiple clients including APEX.

​The Solution

The solution to simplify the process of interfacing with S3 involves two other AWS products, API Gateway and Lambda. In the diagram below, I am illustrating how we use these technologies to get a pre-signed S3 URL. With a pre-signed URL, you can perform an action on a specific S3 object with just the URL (no other keys or hashing required). We can also use a similar approach to directly post files to S3 but both API Gateway and Lambda have payload size limits. API gateway has a limit of 10mb and Lambda a limit of 6mb. By the time you account for base64 encoding the files, this gives you a max file size of about 4mb.
Picture
Let’s step through the diagram to see what is happening:

1 – Calling the API Gateway Service
In APEX we use APEX_WEB_SERVICE to call a simple REST service end point on API Gateway. A small JSON document is constructed with information about the file, bucket and URL expiration. You can include Tags for the S3 file in your headers when posting the file. This will ensure those tags are included in the Pre-signed URL and attached to the S3 object when you come to POST it.

2 – The API Gateway Web Service
The API Gateway end point is secured using a key which is maintained in API Gateway. There are other alternatives (such as  AWS Cognito). Because we are storing the key securely in our Oracle EBS database, using a key is secure enough. As well as securing the service, API Gateway provides additional functionality such as validating the JSON payload. This avoids un-necessary calls to the Lambda when invalid payloads are passed. Other useful features include built in monitoring and throttling to name a few.

The end point for our API Gateway function is a Lambda function. AWS has a tight integration between these two technologies so hooking them up is pretty straight forward.

3 – The Lambda Function
Serverless computing allows you to build and run applications and services without thinking about servers. With serverless computing, your application still runs on servers, but all the server management is done by AWS. At the core of serverless computing is AWS Lambda, which lets you run your code without provisioning or managing servers.

In certain scenarios, serverless technologies such as Lambda offer significant advantages over traditional approaches. It is always available, you only pay for it when you invoke the function and it scales automatically. You can develop Lambda functions in Node, Java, Python, C# and Go. No news on when PL/SQL will be supported :)

We built our function in Node and it has just 30 lines of code. The function imports the AWS S3 tools library, parses the JSON payload passed in by API Gateway and calls the S3 API to get the pre-signed URL. Again, the tight integration between Lambda and S3 comes in handy, the call to the S3 API is extremely simple.

4 – The Response
When the Lambda function is done, it passes the pre-signed URL back to API Gateway within a JSON document. API Gateway then passes the JSON back to our request in PL/SQL.

5 – Posting the File
Now that we have a pre-signed URL we can post our file directly to S3 without the need for keys and hashing. We again use APEX_WEB_SERVICE to post our BLOB to S3. 

PL/SQL Code

The PL/SQL code to call the API Gateway Service to get the pre-signed URL and then post a file to that URL is about 40 lines of code. Add the 30 lines of code for the Lambda function and we are still under 100 lines of code. Not too shabby!
 
Getting the Pre-Signed URL (includes adding tags)

    

Considerations

  • ​Indexing Files:
    • Even though S3 does allow for tagging, it is fundamentally object storage and you are responsible for indexing the file in your database. In view of this, whenever you post a file to S3 you should also be adding a record to a table in your local database, so you can find it again later.
    • Having said that, you should also tag your files in the event your index fails for some reason.
  • Security:
    • Because we have a pre-signed URL, anyone with that URL can update the associated object in S3. In view of this, be sure to limit the expiration time of the URL to just the amount of time it takes to POST the file. (think minutes not hours).
  • JavaScript:
    • You can also use this approach to post files directly from JavaScript as opposed to using a ‘File Browse’ item and processing the file in PL/SQL. You can use a PL/SQL dynamic action to get the pre-signed URL then post the file directly from JavaScript on your APEX page which avoids a round trip to the database.
  • Wallets:
    • Amazon has different SSL certificates for API Gateway and S3 which means you will need to load both certificates into your database wallet.
  • Lambda Warmup:
    • You pay for each execution of your Lambda function. When your code is inert you don’t pay anything for it. In view of this, AWS only spins up a container for your function when it is executed. This can lead to a lag of a couple of seconds the first time you call the function after a period of inactivity. This is not a big deal for this use case but is worth understanding.

Conclusion

​The case for serverless technologies is building as their capabilities continue to grow. In this post, I have described a relatively simple use case. I hope it has given you some ideas of how this technology can help you to solve similar problems.

Author

Jon Dixon, Co-Founder of JMJ Cloud

2 Comments
Marco Arildo link
4/26/2019 09:21:47 am

Nice Post !!!!
I Have the same situation and i got happy because we built the similar function, because the problem was the same.
I have been wrote the function using python and we can consume using apex oracle and can be used for another programming language.
Sorry about my english, because I am from Brazil and my english is improving.

Reply
Balamurugan
8/30/2019 06:14:59 am

Dear Jon.

Great Post !!

Thanks for sharing your solution. Would you be able to share your node solution like the plsql . Will be inspiring for many of us who suffer with the changes AWS is making now.

Regards,
Bala
bala@metis.co.in

Reply

Your comment will be posted after it is approved.


Leave a Reply.

    RSS Feed

    Popular Posts

    - APEX Dog Food
    - Cloud ERP & APEX Mashup
    - Modernizing EBS with APEX
    - Easy APEX_WEB_SERVICE
    - Running APEX in RDS
    - ORDS What & Why?

    Categories

    All
    APEX
    AWS
    Fusion Cloud ERP
    INTEGRATION
    MS GRAPH
    OCI
    ORDS
    PaaS
    RAD
    REST
    SOAP

    Archives

    October 2021
    February 2021
    January 2021
    October 2020
    September 2020
    June 2020
    May 2020
    April 2020
    February 2020
    January 2020
    October 2019
    September 2019
    August 2019
    July 2019
    June 2019
    April 2019
    March 2019
    February 2019
    January 2019
    December 2018
    November 2018
    October 2018
    September 2018
    August 2018
    July 2018
    June 2018
    May 2018
    April 2018
    March 2018
    February 2018
    January 2018
    September 2017
    August 2017
    July 2017
    June 2017
    February 2017
    January 2017
    December 2016
    November 2016
    October 2016
    September 2016
    August 2016
    July 2016

Company

About
Contact
Blog
  • Home
  • Projects
  • Blog
  • About Us
  • Contact Us