Build a Secure Database Driven Web Application with Amplify and React: Part 3 - Connect to a Database
Now that the backend is in place we can connect it to a database. As the Amplify backend is serverless and our business application use case requires a relational database, Aurora is the logical choice.
The classical way to connect to a PostgreSQL database would be a driver like psycopg2 and leveraging a framework like Flask, Jango or FastAPI. The upside of that approach would be full control and that limitations would only be given by the used framework. But the backend would get heavier and that is not the best approach in the context of Lambda functions.
Aurora Data API
An alternate more lightweight solution comes with the Aurora Data API. This will provide us with a lean backend requiring only a few lines of Python code and take the load off the project created by connection pooling considerations etc. There are just a few serious restrictions we need to be aware of:
- Available only for Aurora Serverless v1 (by end of March 23)
- Restricted to specific regions
- HTTP request body size: Max 4MB
- Concurrent requests: Max 500
- Result set size: Max 10 MB
- API requests per second: Max 1000 (per account and region)
We assume that an enterprise grade application with typically a few 1000 concurrent users working on regular web or mobile app use cases will not exceed those limits. The HTTP request and response size limits in particular force the application architect and developer to make the communication lean with small payloads (for example through pagination).
API Configuration
The data API has to be activated in the cluster configuration. We also define a security group for access:
Database Credentials
To avoid that database credentials are exposed in program code, a secret of type rds-db-credentials holds username and password. Use the console and the AWS Secrets Manager.
Accessing the database
It is not possible to assign a public IP address to an Aurora cluster. The easiest and most versatile way to connect a SQL tool like DBeaver to the database is a bastion host. I use a Cloud9 development environment (that spins off a small EC2 instance) to create an SSH tunnel to the database from a command line or from within the database tool.
Extending the Backend Lambda
We need to install the AWS Python SDK
> pip install boto3
and refer it in the pipfile of the project:
[[source]]
name = "pypi"
url = "https://pypi.org/simple"
verify_ssl = true
[dev-packages]
[packages]
src = {editable = true, path = "./src"}
boto3 = "*"
[requires]
python_version = "3.8"
Here's the code of the index.py file of the lambda function:
import json
import boto3
rdsData = boto3.client('rds-data')
def handler(event, context):
config = {"cluster_arn": "arn:aws:rds:us-east-2:573567367888:cluster:aurora-postgres",
"secret_arn": "arn:aws:secretsmanager:us-east-2:573356367888:secret:rds-db-credentials/cluster-L6MIBVWJIVTBOI2SXA/postgres/1669835383475-CUZm30",
"userpool_id": 'us-east-2_vhaWCRSWB'}
print('received event:')
print(event)
id = event["pathParameters"]["id"]
params = []
params.append({'name': 'id', 'value': {'longValue': int(id)}})
whereclause = ' where customer_id = :id'
column_list = 'customer_id, company_name, city'
table = {'table_schema': 'northwind', 'table_name': 'customers'}
response = rdsData.execute_statement(
resourceArn = config['cluster_arn'],
secretArn = config['secret_arn'],
database = 'postgres', formatRecordsAs = 'JSON',
sql = 'select {} from {}.{}'.format(column_list, table['table_schema'],
table['table_name'])
+ whereclause,
parameters = params)
records = json.loads(response["formattedRecords"])
print ('Number of records:', len(records))
statusCode = 200
if len(records) == 0:
statusCode = 404
records = json.loads('{"message": ' + '"No record found.", "statusCode": '
+ str(statusCode) + '}')
return {
'statusCode': statusCode,
'headers': {
'Access-Control-Allow-Headers': '*',
'Access-Control-Allow-Origin': '*',
'Access-Control-Allow-Methods': 'OPTIONS,POST,GET'
},
'body': json.dumps(records)
}
Core of the code is the API call via rdsData. Note that we retrieve database configuration and credentials via the resource ARNs of the database cluster and the secret. More advanced, those ARNs will be retrieved from a configuration or environment variables and will not be hard coded. It happens here for better understanding with everything in just one program file.
The formattedRecords attribute in the response contains the resultset. As we query via primary key, this will deliver at most one record.
Let's test it locally via mock:
> amplify mock function
The event.json file still points to the GET http method and provides '1' as the path parameter. If everything was implemented and configured correctly, the output will look like this:
The response contains the id, name and city of the first record of the customers table. Next step is to push the function to the cloud via
> amplify push
and test it via Postman. This will lead to an internal server error, response code 502. Why? A look into the Cloudwatch log brings clarity:
[
{
"Effect": "Allow",
"Action": [
"secretsmanager:GetSecretValue"
],
"Resource": [
"arn:aws:secretsmanager:us-east-2:573356367888:secret:rds-db-credentials/cluster-L6MIBVWJIVTBOI2SXAVRJNXXDEF/postgres/1669835383475-CUZm30"
]
},
{
"Effect": "Allow",
"Action": [
"rds-data:ExecuteSql",
"rds-data:ExecuteStatement",
"rds-data:BatchExecuteStatement",
"rds-data:BeginTransaction",
"rds-data:CommitTransaction",
"rds-data:RollbackTransaction"
],
"Resource": ["arn:aws:rds:us-east-2:573356367888:cluster:aurora-postgres"]
}
]
With that change deployed via another amplify push command, the API delivers database records:
Or it returns the correct status code 404 for a non-existent resource:
Conclusion
That's it! With just a few lines of code we have a full secure serverless backend that communicates with a serverless RDBS - Aurora Postgres. The next step will be to use this backend on the React frontend,
(There is still no demo video for this part of the blog series, I had no time to record it. Please feel free to give feedback and ask questions to encourage the Cloudrunnr to provide more.)
Comments
Post a Comment