look ma, no database connection pools ;-)

Mani
Mani
Jan 15 · 7 min read

TL;DR — IMHO, Using the Data API (Beta) for the Amazon Aurora Serverless database with serverless apps running on top of AWS Fargate or AWS Lambda can create truly serverless apps end to end — and yes, without using database connection pools and executing SQL queries via HTTP !! and yes, conditions apply — read the fine print ;-)

In a previous life, I was an Java/Application architect working as part of Sun professional services at Sun Microsystems in the US. I used to get called into customer situations when there were performance issues in production, or to recommend best practices for Java apps running on Application servers on Solaris. After many such engagements, I usually went about looking for performance issues in the following manner → 1. First, look at the Java application code (usual stuff like static classes/variables which caused java memory heap issues, HTTP session bloating — I have seen huge XML files being stored in HTTP sessions) → 2. Application server settings (thread settings, http listeners, database connection pool settings) → 3. Java VM tuning — this was a black art with so many settings/tunables → 4. Last and finally look at Solaris system settings (typically file descriptors, network settings and a few other common stuff) .. I would usually find the issue in one of the above four areas, but in rare cases, the issue did turn out to be product code (or a JDK bug) — we would then release a hot patches and resolve these issues.

In many case, Database connection pool settings caused a lot of issues especially in internet facing and high traffic apps. This is an area which has spawned an entire ecosystem to address this — database caching like memcache, nosql databases, read replicas all addressed to reduce the load on the main transaction database.

I was so, so very happy when AWS introduced the new Data API (Beta) to access the Amazon Aurora Serverless for MySQL — https://aws.amazon.com/about-aws/whats-new/2018/11/aurora-serverless-data-api-beta/ This means that you can access Aurora Serverless database cluster from web services based applications using an HTTP endpoint rather than native database protocols. As highlighted at https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/data-api.html“The Data API doesn’t require a persistent connection to the DB cluster. Instead, it provides a secure HTTP endpoint and integration with AWS SDKs. You can use the endpoint to execute SQL statements in the same way as you do through the MySQL native protocol, but without managing connections. The API returns query results in JSON format. All calls to the Data API are synchronous. A call times out and is terminated in one minute if it’s not finished processing.”.

The Data API is now in Beta and currently available in the N.Virginia (US-East-1) Region. Imagine now, coupling the Data API with a serverless technology like AWS Lambda or AWS Fargate (compute engine for Amazon ECS that allows you to run containers without having to manage servers or clusters), then this becomes truly revolutionary and all database access will be via HTTP using the AWS SDK and completely serverless !!

I did a quick and dirty POC to showcase an Node JS app running in AWS Fargate and calling the Aurora Serverless database cluster using the Data API. This was more for me to understand how the Data API works and what configurations are required to call the API form within Fargate.

Node JS app running on AWS Fargate → Amazon Aurora Serverless

These are a few resources, before I explain the steps for the POC:

The steps are pretty straightforward — all AWS resources are created in N.Virginia — US-east-1 region where the Data API is available in Beta :

Amazon Aurora Serverless related:

  • Create a database using RDS aurora with mysql 5.6.xx as the database engine, select serverless as the capacity type. Note down the master username, password and db cluster identifier. Create the database and save the RDS ARN like — arn:aws:rds:us-east-1:xxxxxx:cluster:test-aurora-mysql-dataapi
  • Enable the Data API — https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/data-api.html — The Data API (Beta) can be enabled when you modify the database cluster. You can find the Data API option under Network & Security
  • Store a new secret in the AWS secrets manager for RDS Aurora database using the master user name and master password that was created earlier, give a logical name for example: test/dataapi/mysql1 to store the secret. Note down the Secret ARN — like for example arn:aws:secretsmanager:us-east-1:xxxxxx:secret:test/dataapi/mysql1-BuztkC
  • Now, you are ready to test the access to the Data API using AWS CLI commands {Pro tip — make sure the AWS CLI is updated to reflect the new AWS services and updates :-) }.

I used the CLI’s to create the database, table and add a few rows of dummy data ..

Create a database “testdb”
aws rds-data execute-sql — aws-secret-store-arn arn:aws:secretsmanager:us-east-1:xxxxxxxx:secret:test/dataapi/mysql1-BuztkC — db-cluster-or-instance-arn arn:aws:rds:us-east-1:xxxxxxxx:cluster:test-aurora-mysql-dataapi — schema “mysql” — sql-statements “create DATABASE TESTDB” — region us-east-1

Create a table “testdb”
aws rds-data execute-sql — aws-secret-store-arn arn:aws:secretsmanager:us-east-1:xxxxxxxx:secret:test/dataapi/mysql1-BuztkC — db-cluster-or-instance-arn arn:aws:rds:us-east-1:xxxxxxxx:cluster:test-aurora-mysql-dataapi — schema “mysql” — sql-statements “create table Pets(id varchar(200), type varchar(200), price float)” — database “TESTDB” — region us-east-1

Insert some dummy values into the table
aws rds-data execute-sql — aws-secret-store-arn arn:aws:secretsmanager:us-east-1:xxxxxxx:secret:test/dataapi/mysql1-BuztkC — db-cluster-or-instance-arn arn:aws:rds:us-east-1:xxxxxxxxx:cluster:test-aurora-mysql-dataapi — schema “mysql” — sql-statements “insert into Pets(id,type,price) values (‘dog1’,’awesome dog’,2000.00),(‘cat1’,’awesome cat’,1000.00),(‘rat1’,’are you joking’,-290.00),(‘dog2’,’awesomest dog2',9000.00)” — database “TESTDB” — region us-east-1

Query the data that was inserted ..
aws rds-data execute-sql — aws-secret-store-arn arn:aws:secretsmanager:us-east-1:xxxxxxx:secret:test/dataapi/mysql1-BuztkC — db-cluster-or-instance-arn arn:aws:rds:us-east-1:xxxx:cluster:test-aurora-mysql-dataapi — schema “mysql” — sql-statements “select * from Pets” — database “TESTDB” — region us-east-1

AWS Fargate related tasks :

{
“Effect”: “Allow”,
“Action”: “rds-data:ExecuteSql”,
“Resource”: “arn:aws:rds:us-east-1:xxxx:cluster:test-aurora-mysql-dataapi”
},
{
“Effect”: “Allow”,
“Action”: “secretsmanager:GetSecretValue”,
“Resource”: “arn:aws:secretsmanager:us-east-1:xxxxx:secret:test/dataapi/mysql1-BuztkC”
}

The code in server.js is very simple and pretty straightforward:

snippet from server.js showing the RDS execute SQL comman

1. aws ecr create-repository — repository-name fargate-aurora-serverless
2. aws ecr get-login
3. << docker login>>
4. Modify the code with your ARN’s for secret manager and Aurora in server.js
5. docker build -t fargate-aurora-serverless .
6. docker tag fargate-aurora-serverless xxxxx.dkr.ecr.us-east-1.amazonaws.com/fargate-aurora-serverless
7. docker push xxxxxx.dkr.ecr.us-east-1.amazonaws.com/fargate-aurora-serverless
8. aws ecr list-images — repository-name fargate-aurora-serverless

  • Create a Cluster using Amazon ECS console or from the CLI
  • Create a new task definition of Fargate launch type. Make sure you attach the IAM role created before. Make the correct port mapping, 80 in this case and use the ECR container Image that was pushed earlier …
  • Run a new task from the task definition that was created and wait until the task is ready to receive requests.

Call http://<FargateTaskPublicIPaddress>/data and the data from Aurora serverless database shows up magically as JSON !!

Finally …

In Conclusion, the new Data API seems a much cleaner way to call serverless databases like Aurora serverless from within serverless applications. This is still in Beta and not yet available in all regions except us-east-1, stay tuned to the AWS blog announcements for the latest updates.

Also, I have not yet experimented with usecases where latency is of primary importance and dealing with stuff like transactions, you may want to look at using traditional database drivers which use JDBC APIs .. I have not read the documentation completely, I would say RTFM or ping AWS support before jumping into any conclusions. As I said before, your mileage may wary, please evaluate the Data API for your specific use-case.

But these are exciting times to be a serverless developer !!

Mani

Written by

Mani

News freak, Technology geek, hard-core Bangalorean, all things Internet related !! Interested in building a modern, strong & democratic India !!

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade