Accessing AWS S3 from APEX with API Gateway & Lambda

Introduction

​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.

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