NewApp Digest
search bot

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

August 21, 2024

Some customers utilize the Oracle UTL_HTTP package to develop PL/SQL programs that facilitate communication with web (HTTP) servers and invoke third-party APIs. However, when transitioning to Amazon Aurora PostgreSQL-Compatible Edition or Amazon RDS for PostgreSQL, these users must undertake a custom conversion of their SQL code, as PostgreSQL lacks a direct equivalent of the UTL_HTTP functionality. The UTL_HTTP package in Oracle allows for seamless HTTP callouts directly from PL/SQL, which is a feature that requires adaptation in the PostgreSQL environment.

Solution overview

This solution emphasizes the integration of AWS Lambda with Amazon Aurora PostgreSQL. The methodology is equally applicable to Amazon RDS for PostgreSQL. AWS Lambda is a serverless computing service that enables code execution without the need for server management. It automatically scales and executes code in response to various events or triggers, including HTTP requests.

Users can send HTTP requests to invoke an AWS Lambda function from the Aurora PostgreSQL database, utilizing the Python Requests module. This elegant HTTP library simplifies the process of sending HTTP/1.1 requests.

The high-level steps of the solution are outlined below:

  1. Create a new schema named utl_http_utility in the Amazon Aurora PostgreSQL database.
  2. Define Request and Response objects as user-defined types in Aurora PostgreSQL to represent HTTP requests and responses.
  3. Implement PL/pgSQL custom wrapper functions in the Aurora PostgreSQL database for various HTTP operations, such as initiating a request, setting authentication, configuring headers, and handling responses. These functions continuously build a JSON object encapsulating all HTTP parameters, including response content, URL, parameters, custom headers, and payload.
  4. Install the aws_lambda and aws_commons extensions in the Aurora PostgreSQL database to facilitate seamless integration with Lambda functions, enhancing the handling of API requests and responses.
  5. Create the get_response wrapper function within the Aurora PostgreSQL database. This function accepts a Request JSON object as input and invokes a Lambda function, which is implemented in Python using the Requests module, to send HTTP requests to the desired API endpoint.
  6. The Lambda function executes web service API calls and returns responses, enabling real-time (synchronous) data forwarding back to the Aurora database.

The architecture is illustrated in the accompanying diagram.

The components of this architecture include:

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

Prerequisites

To implement this solution, the following prerequisites are necessary:

  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, all within a VPC.
  2. Install and configure the AWS CLI for Lambda function deployment.
  3. Permissions to invoke a Lambda function from an Aurora PostgreSQL database cluster.
    • Refer to the relevant guides to establish connectivity between your database cluster and Lambda, and subsequently create the aws_lambda extension in the database cluster.

Package and deploy a Lambda function

A significant advantage of this solution is the real-time communication between the Lambda function and the Aurora PostgreSQL database, facilitating prompt API response forwarding.

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

  1. Download the source code and deploy it by executing the following commands:
git clone https://github.com/aws-samples/wrapper-for-utl-http-with-amazon-aurora
  1. Navigate to the project directory containing your lambda_function.py source code file.
cd wrapper-for-utl-http-with-amazon-aurora/lambda
  1. Create a new directory named package to install your dependencies.
mkdir package
  1. Install dependencies in the package directory.
pip install --target ./package requests
  1. Create a .zip file containing the installed libraries at the root.
cd package
zip -r ../aurora-http-helper.zip .
  1. Add the lambda_function.py file to the root of the .zip file.
cd ..
zip aurora-http-helper.zip lambda_function.py

The subsequent steps illustrate the deployment of the Lambda function using the AWS CLI. Ensure that the AWS CLI is configured before proceeding.

  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"}]}"
  1. 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
  1. 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:::role/aurora-utl-http-utility-role

The expected output will confirm the successful creation of the Lambda function.

Create wrapper functions

The following wrapper function constructs JSON objects with HTTP parameters and payload to invoke a Lambda function from an Aurora PostgreSQL database. From the downloaded source code, execute the commands below 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/lambda
  1. Connect to your Aurora PostgreSQL database instance as a user with privileges to create the schema and deploy the objects. The default postgres user is illustrated in the following example:
cd wrapper-for-utl-http-with-amazon-aurora/lambda
  1. Execute the SQL file to create a utility schema and wrapper objects for constructing a JSON object with HTTP parameters and payload for Lambda invocation.
cd wrapper-for-utl-http-with-amazon-aurora/lambda

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, generating 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 – Configures transfer timeout.
  • SET_AUTHENTICATION – Sets up authentication.
  • SET_PARAMS – Configures 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.

These functions can be extended to cater to additional use cases. The open-source nature of this solution allows for customization to meet specific requirements.

  1. Execute the AWS CLI command below to retrieve the Lambda function ARN details and region for updating in the next step.
cd wrapper-for-utl-http-with-amazon-aurora/lambda
  1. 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 designed to store configuration parameters related to the utl_http_utility package.
cd wrapper-for-utl-http-with-amazon-aurora/lambda

Test wrapper functions

We can now utilize the following anonymous code block to test a sample PL/pgSQL procedure that employs various functions from the utl_http_utility package to execute an HTTP POST request with specific headers, payload, and authentication. Additionally, it reads the response and processes it line by line. For testing purposes, any valid HTTP endpoint can be used (alternatively, a free HTTP request testing site such as webhook.site can be utilized).

Ensure to update the l_url variable with the valid API URL you wish to invoke:

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

The expected output will confirm the successful execution of the request:

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

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

Clean up

To prevent incurring future charges, it is advisable to clean up the resources created as part of this process:

  1. Navigate to the downloaded source code directory wrapper-for-utl-http-with-amazon-aurora.
cd wrapper-for-utl-http-with-amazon-aurora/lambda
  1. Remove database objects by connecting to your Aurora PostgreSQL database instance as a user with privileges to drop the schema. The default postgres user is demonstrated in the following example:
cd wrapper-for-utl-http-with-amazon-aurora/lambda
  1. Execute the uninstall.sql file to drop the utility schema and wrapper objects.
  2. Delete the Lambda function that was created using the AWS CLI:
cd wrapper-for-utl-http-with-amazon-aurora/lambda
  1. Remove the IAM role and the database instance if they are no longer needed.

About the Authors

Bhanu Ganesh Gudivada is a Database Consultant on the AWS Professional Services team at AWS, specializing in database migrations. He assists customers in building highly available, cost-effective database solutions and migrating their commercial engines to the AWS Cloud. His curiosity drives him to explore and implement new technologies surrounding databases and generative AI, orchestrating migrations through automation.
Rajeshkumar Sabankar is a Database Specialty Architect with Amazon Web Services. He collaborates with internal Amazon customers to construct secure, scalable, and resilient architectures in the AWS cloud, aiding customers in migrating 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 specializes in database migration, assisting Amazon customers in designing 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 supports and enables customers to migrate their databases from on-premises data centers to the AWS Cloud, as well as transition from commercial database engines to open-source databases.

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