Build a custom HTTP client in Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL: An alternative to Oracle’s UTL_HTTP

Some customers use Oracle UTL_HTTP package to write PL/SQL programs that communicate with web (HTTP) servers and invoke third-party APIs. When migrating to Amazon Aurora PostgreSQL-Compatible Edition or Amazon Relational Database Service (Amazon RDS) for PostgreSQL, these customers need to perform a custom conversion of their SQL code since PostgreSQL does not offer a similar built-in functionality. The UTL_HTTP package in Oracle provides a convenient way to make HTTP callouts directly from PL/SQL.

In this post, we demonstrate how you can use PL/pgSQL custom wrapper functions to convert Oracle UTL_HTTP referenced custom code to the Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL equivalent.

Solution overview

This solution focuses on integrating AWS Lambda with Amazon Aurora PostgreSQL. However, the steps and approach are applicable to Amazon RDS for PostgreSQL as well.

AWS Lambda is a serverless computing service that allows you to run code without provisioning or managing servers. It automatically scales and executes your code in response to events or triggers, such as HTTP requests.

Users send HTTP requests, invoking an AWS Lambda function from the Aurora PostgreSQL database, implemented using the Python Requests module.

Requests is a simple, yet elegant, HTTP library that allows you to send HTTP/1.1 requests with ease.

At a high level, the solution steps are as follows:

1. Create a new schema as utl_http_utility in Amazon Aurora PostgreSQL DB.
2. Create Request and Response objects as user-defined types in Aurora PostgreSQL to represent an HTTP request and response.
3. Deploy PL/pgSQL custom wrapper functions in the Aurora PostgreSQL DB for HTTP operations like beginning a request, setting authentication, setting headers, setting parameters, getting a response, and reading and writing lines to and from an HTTP request. These wrapper functions are used to continuously build a JSON object with all the HTTP parameters (response content, URL, parameters, custom headers) and payload.
4. Install the aws_lambda and aws_commons extensions in Aurora PostgreSQL DB. These extensions enable seamless integration with Lambda functions, offering a more versatile approach to handling API requests and responses.
5. Create the get_response wrapper function in Aurora PostgreSQL DB. This function takes a Request JSON object from the application or database as input and invokes a Lambda function. The Lambda function, implemented using Python and the Requests module, is responsible for sending HTTP requests to the desired API endpoint.
6. The Lambda function performs web service API invocations and returns responses, enabling real-time (synchronous) forwarding of the data back to the Aurora database.

The following diagram illustrates the architecture.

The architecture consists of the following components:

1. An Aurora PostgreSQL database with aws_lambda and custom wrapper functions.
2. A Lambda function takes HTTP requests from the database, invokes APIs, and sends responses back.
3. AWS Identity and Access Management (IAM) role for invoking Lambda functions from the Aurora PostgreSQL DB cluster.

Prerequisites

To implement this solution, you need

1. An Aurora PostgreSQL cluster with the latest minor version available for Aurora PostgreSQL version 14 or above or an RDS for PostgreSQL instance with the latest minor version available for Amazon RDS for PostgreSQL version 14 or above inside a VPC.

2. Install and configure the AWS CLI for Lambda function deployment.

3. Permissions to invoke a Lambda function from an Aurora PostgreSQL DB cluster

• • Refer to the following guides to set up connectivity between your DB cluster and Lambda and then create the aws_lambda extension in the DB cluster

1. 1. • Step 1: Configure your Aurora PostgreSQL DB cluster for outbound connections to AWS Lambda
      • Step 2: Configure IAM for your Aurora PostgreSQL DB cluster and AWS Lambda
      • Step 3: Install the aws_lambda extension for an Aurora PostgreSQL DB cluster

Package and deploy a Lambda function

A key advantage of this solution is the real-time communication between the Lambda function and Aurora PostgreSQL database, enabling the prompt forwarding of API responses.

To use the Lambda function, create a .zip deployment package with all the required dependencies, including the Requests library.

1. Download the source code and deploy it by running the following commands:

git clone https://github.com/aws-samples/wrapper-for-utl-http-with-amazon-aurora

2. Navigate to the project directory containing your lambda_function.py source code file

cd wrapper-for-utl-http-with-amazon-aurora/lambda

3. Create a new directory named package into which you will install your dependencies.

mkdir package

4. Install dependencies in the package directory.

pip install --target ./package requests

5. Create a .zip file with the installed libraries at the root.

cd package
zip -r ../aurora-http-helper.zip .

6. Add the lambda_function.py file to the root of the .zip file

cd ..
zip aurora-http-helper.zip lambda_function.py

The following steps shows the deployment of the Lambda function using the AWS CLI. Make sure you have configured the AWS CLI before proceeding with the next steps.

1. Create an IAM role

aws iam create-role --role-name aurora-utl-http-utility-role --assume-role-policy-document "{\"Version\": \"2012-10-17\",\"Statement\": [{ \"Effect\": \"Allow\", \"Principal\": {\"Service\": \"lambda.amazonaws.com\"}, \"Action\": \"sts:AssumeRole\"}]}"

2. Attach an IAM Role Policy

aws iam attach-role-policy --role-name aurora-utl-http-utility-role --policy-arn arn:aws:iam::aws:policy/service-role/AWSLambdaBasicExecutionRole

3. Create a lambda function

aws lambda create-function --function-name aurora-http-helper --runtime python3.11 --zip-file fileb://aurora-http-helper.zip  --handler lambda_function.lambda_handler --role arn:aws:iam::<account-id>:role/aurora-utl-http-utility-role

You should get the following output:

[cloudshell-user@ip-10-x-xx-xx utl_http]$ aws iam create-role --role-name aurora-utl-http-utility-role-1610 --assume-role-policy-document "{\"Version\": \"2012-10-17\",\"Statement\": [{ \"Effect\": \"Allow\", \"Principal\": {\"Service\": \"lambda.amazonaws.com\"}, \"Action\": \"sts:AssumeRole\"}]}"
{
    "Role": {
        "Path": "/",
        "RoleName": "aurora-utl-http-utility-role-1610",
        "RoleId": "AROXXX7XX34XX2XX3XXXX",
        "Arn": "arn:aws:iam::4XXX0XXX1XXX:role/aurora-utl-http-utility-role-1610",
        "CreateDate": "2023-10-16T05:04:42+00:00",
        "AssumeRolePolicyDocument": {
            "Version": "2012-10-17",
            "Statement": [
                {
                    "Effect": "Allow",
                    "Principal": {
                        "Service": "lambda.amazonaws.com"
                    },
                    "Action": "sts:AssumeRole"
                }
            ]
        }
    }
}
[cloudshell-user@ip-10-x-xx-xx utl_http]$ 

[cloudshell-user@ip-10-x-xx-xx utl_http]$ aws iam attach-role-policy --role-name aurora-utl-http-utility-role-1610 --policy-arn arn:aws:iam::aws:policy/service-role/AWSLambdaBasicExecutionRole
[cloudshell-user@ip-10-x-xx-xx utl_http]$ 
[cloudshell-user@ip-10-x-xx-xx utl_http]$ aws lambda create-function --function-name aurora-http-helper-1610 --runtime python3.11 --zip-file fileb://aurora-http-helper.zip  --handler lambda_function.lambda_handler --role arn:aws:iam::4XXX0XXX1XXX:role/aurora-utl-http-utility-role-1610
{
    "FunctionName": "aurora-http-helper-1610",
    "FunctionArn": "arn:aws:lambda:us-west-1:4XXX0XXX1XXX:function:aurora-http-helper-1610",
    "Runtime": "python3.11",
    "Role": "arn:aws:iam::4XXX0XXX1XXX:role/aurora-utl-http-utility-role-1610",
    "Handler": "lambda_function.lambda_handler",
    "CodeSize": 1018,
    "Description": "",
    "Timeout": 3,
    "MemorySize": 128,
    "LastModified": "2023-10-16T05:08:27.512+0000",
    "CodeSha256": "rGrtBX6cuPYTby5hJuoXxxMPx9XXtb4z3I9XXXX3xXx=",
    "Version": "$LATEST",
    "TracingConfig": {
        "Mode": "PassThrough"
    },
    "RevisionId": "1069620d-d939-4a1c-a5f9-683ea203d399",
    "State": "Pending",
    "StateReason": "The function is being created.",
    "StateReasonCode": "Creating",
    "PackageType": "Zip",
    "Architectures": [
        "x86_64"
    ],
    "EphemeralStorage": {
        "Size": 512
    },
    "SnapStart": {
        "ApplyOn": "None",
        "OptimizationStatus": "Off"
    },
    "RuntimeVersionConfig": {
        "RuntimeVersionArn": "arn:aws:lambda:us-west-1::runtime:9c87c21a94b293e1a306aad2c23cfa6928e7a79a3d3356158b15f4cbe880b390"
    }
}
[cloudshell-user@ip-10-x-xx-xx utl_http]$

The lambda_handler function takes event and context parameters, processing various HTTP methods (GET, PUT, POST, DELETE, PATCH, HEAD, OPTIONS). It employs the Requests library to send custom HTTP requests with headers, credentials, and payload, handling timeouts and exceptions. Unsupported methods return 405 (Method Not Allowed), and HTTP errors result in 500 (Internal Server Error) responses with error details.

Create wrapper functions

The following wrapper function construct JSON objects with HTTP parameters and payload to invoke a Lambda function from an Aurora PostgreSQL database.

From the downloaded source code, run the following commands to deploy the database objects into the Aurora PostgreSQL database:

1. Navigate to the wrapper-for-utl-http-with-amazon-aurora directory

cd wrapper-for-utl-http-with-amazon-aurora

2. Connect to your Aurora PostgreSQL DB instance as a user with privileges to create the schema and deploy the objects. The default postgres user is shown in the following example:

psql -h cluster-instance.444455556666.aws-region.rds.amazonaws.com -U postgres -p 5432

3. Run the file sql to create a utility schema and wrapper objects for constructing a JSON object with HTTP parameters and payload for Lambda invocation:

postgres=> \i install.sql
    CREATE EXTENSION
    CREATE SCHEMA
    CREATE TYPE
    CREATE TYPE
    CREATE TABLE
    INSERT 0 1
    CREATE FUNCTION
    CREATE FUNCTION
    CREATE FUNCTION
    CREATE FUNCTION
    CREATE FUNCTION
    CREATE FUNCTION
    CREATE FUNCTION
    CREATE FUNCTION
    CREATE FUNCTION
    CREATE PROCEDURE
    CREATE PROCEDURE

PostgreSQL’s user-defined composite types, utl_http_utility.req and utl_http_utility.res, encapsulate the HTTP request and response components for convenient parameter passing in database functions and procedures:

• BEGIN_REQUEST – Initializes an HTTP request object with URL, method, and version, and generates a unique handle
• SET_HEADER – Adds custom headers to the request
• WRITE_TEXT – Appends text to the request
• WRITE_LINE – Appends text as a new line
• WRITE_RAW – Appends binary data
• SET_TRANSFER_TIMEOUT – Sets transfer timeout
• SET_AUTHENTICATION – Configures authentication
• SET_PARAMS – Sets URL parameters
• GET_RESPONSE – Invokes a Lambda function, returning a response object
• READ_TEXT – Reads and outputs text data
• READ_RAW – Reads and outputs binary data

You can extend these functions to accommodate additional use cases. The open-source nature of this solution provides the flexibility for customization to meet your specific requirements.

4. Run the AWS CLI command below to get the Lambda function ARN details and region to update them in next step.

aws lambda get-function --function-name aurora-http-helper --query "Configuration.FunctionArn" --output text

5. Update the parameter table to reflect the Lambda function ARN details and Region. The utl_http_utility_params table is a user-defined table in PostgreSQL. It is designed to store configuration parameters related to the utl_http_utility package.

update utl_http_utility.utl_http_utility_params
    set lambda_arn = 'arn:aws:lambda:<region>:<account-id>:function:aurora-http-helper',
    lambda_region = '<region>'
    where lambda_key = 'aurora-http-helper'

Test wrapper functions

We can now use the following anonymous code block to test a sample PL/pgSQL procedure that utilizes various functions from the utl_http_utility package to perform an HTTP POST request with specific headers, payload, and authentication. Additionally, it reads the response and processes it line by line.

For the purpose of testing this module, you can use any valid HTTP endpoint (alternatively, you can use a free HTTP request testing site such as webhook.site).

Make sure to update the l_url variable with the valid API URL to invoke:

do
$$
declare
    l_req   utl_http_utility.req;
    l_url   CHARACTER VARYING(255) := '<https://webhook.site/79999aa70-1z0c-4xx1-qq2d-95956c454zxz>';
    l_resp  utl_http_utility.resp;
    l_chunk TEXT;
    l_len   INTEGER := 2000;
    l_text_line TEXT;
begin
    
    SELECT * FROM utl_http_utility.begin_request(l_url,'POST','HTTP/1.1') INTO l_req;
    
    SELECT * FROM utl_http_utility.set_header(l_req,'Content-Type','text/xml') INTO l_req;
    
    SELECT * FROM utl_http_utility.set_header(l_req,'Content-Length','100') INTO l_req;
    
    SELECT * FROM utl_http_utility.write_text(l_req,'Hello, world!') INTO l_req;
    
    SELECT * FROM utl_http_utility.write_line(l_req,'Hello, world in write_line!') INTO l_req;
    
    SELECT * FROM utl_http_utility.write_raw(l_req,'Hello, world in write_raw!') INTO l_req;
    
    SELECT * FROM utl_http_utility.set_transfer_timeout(l_req,60) INTO l_req;
    
    SELECT * FROM utl_http_utility.set_authentication(l_req,'user','password') INTO l_req;
    
    SELECT * FROM utl_http_utility.set_authentication(l_req,'user1','password1') INTO l_req;
    
    SELECT * FROM utl_http_utility.set_params(l_req,'param1','value1') INTO l_req;
    
    SELECT * FROM utl_http_utility.set_params(l_req,'param2','value2') INTO l_req;
    
    raise notice '%', l_req.payload;
    
    SELECT * FROM utl_http_utility.get_response(l_req) INTO l_resp;
    
    CALL utl_http_utility.read_text(l_resp, l_chunk);
    
    raise notice 'read_text chunk:%', l_chunk;
    
    --  utl_http_utility.read_line
    FOREACH l_text_line IN ARRAY regexp_split_to_array(l_chunk, E'\\n') LOOP
        RAISE NOTICE 'Response line: %', l_text_line;
    END LOOP;
    
end;
$$
language plpgsql;

We get the following output:

NOTICE:  4ed428dc-75d7-4882-af30-3a72d021061f
NOTICE:  {"url": "https://webhook.site/a5524281-1cdf-426f-b2bd-c3ce7225ad60", "auth": {"password": "password1", "username": "user1"}, "body": "Hello, world!\r\n\r\nHello, world in write_line!\r\n\\xSGVsbG8sIHdvcmxkIGluIHdyaXRlX3JhdyE=", "params": {"param1": "value1", "param2": "value2"}, "headers": {"Content-Type": "text/xml", "Content-Length": "100"}, "timeout": 60, "httpMethod": "POST"}
NOTICE:  {"url": "https://webhook.site/a5524281-1cdf-426f-b2bd-c3ce7225ad60", "auth": {"password": "password1", "username": "user1"}, "body": "Hello, world!\r\n\r\nHello, world in write_line!\r\n\\xSGVsbG8sIHdvcmxkIGluIHdyaXRlX3JhdyE=", "params": {"param1": "value1", "param2": "value2"}, "headers": {"Content-Type": "text/xml", "Content-Length": "100"}, "timeout": 60, "httpMethod": "POST"}
NOTICE:  read_text chunk:
NOTICE:  Response line: 
DO

Query returned successfully in 1 secs 694 msec.

The following screenshot shows the POST request sent to the Webhook API.

Clean up

To avoid incurring future charges, clean up the resources created as part of this post:

1. Navigate to the downloaded source code directory wrapper-for-utl-http-with-amazon-aurora

cd wrapper-for-utl-http-with-amazon-aurora

2. Clean up database objects, connect to your Aurora PostgreSQL DB instance as a user with privileges to drop the schema. The default postgres user is shown in the following example:

psql -h cluster-instance.444455556666.aws-region.rds.amazonaws.com -U postgres -p 5432

3. Run the file uninstall.sql to drop utility schema and wrapper objects.
4. Delete the Lambda function that was created using the AWS CLI:

aws lambda delete-function --function-name arn:aws:lambda:<region>:<account-id>:function:aurora-http-helper

5. Delete the IAM role and the database instance if you no longer need to use the resources.

Conclusion

In this post, we showed how you can build a custom HTTP client in Amazon Aurora PostgreSQL, to use as an equivalent to Oracle’s UTL_HTTP package. With this solution, you can now invoke third-party APIs, handle HTTP requests and responses. Remember, the open-source nature of this solution allows for customization and adaptation to your specific requirements. So, dive in, explore, and unlock the full potential of your Aurora PostgreSQL database.

Leave your thoughts or questions in the comments section.

About the Authors

Bhanu Ganesh Gudivada is a Database Consultant on the AWS Professional Services team at AWS and specializes in database migrations. He helps customers build highly available, cost-effective database solutions and migrate their commercial engines to the AWS Cloud. He is curious to learn and implement new technologies around databases, generative AI and orchestrate migrations through automation.

Rajeshkumar Sabankar is a Database Specialty Architect with Amazon Web Services. He works with internal Amazon customers to build secure, scalable and resilient architectures in AWS cloud and help customers perform migrations from on-premise databases to AWS RDS and Aurora Databases.

Vamsi Krishna Jammula is a Database Consultant with the AWS Professional Services team at AWS. He works as a database migration specialist to help Amazon customers in design and implementing scalable, secure, performant, and robust database solutions in the cloud.

Sumana Yanamandra is a Database Consultant with the AWS Professional Services team at AWS. She has been supporting and enabling customers to migrate their database from on-premises data centers to the AWS Cloud and also migrate from commercial database engines to open source databases.