10 Jan 2019 Serverless GraphQL AppSync API with RDS
AppSync integrates seamlessly with a DynamoDB database. And as demonstrated in my previous article, AWS Amplify CLI can create the DynamoDB tables and generate the API CloudFormation infrastructure from an annotated GraphQL schema. However, using a relational data source with AppSync is more complex as no RDS data source is yet available. A beta version only available in a couple of regions is still being tested and covers only Aurora Serverless instances. The current way is to use a lambda data source to manage the connection to the RDS database. Let us review the most important components of the CloudFormation infrastructure for such an API.
GraphQL Schema
As Amplify CLI is not used for generating the API, so the complete schema has to be defined with the queries and mutations. Below is an example of definition of the schema with a mutation and a query in CloudFormation (for an e-commerce application).
ServerlessWebAPISchema:
Type: "AWS::AppSync::GraphQLSchema"
DependsOn:
- ServerlessWebAPI
Properties:
ApiId: !GetAtt ServerlessWebAPI.ApiId
Definition: |
schema {
query: Query
mutation: Mutation
}
type Mutation {
createProduct(input: CreateProductInput!): Product
}
type Order {
dateOrderPlaced: AWSDateTime!
id: ID!
items: [OrderItem]
orderDetails: String
status: OrderStatus!
}
type OrderItem {
id: ID!
order: Order
otherItemDetails: String
price: Float!
product: Product
quantity: Int!
}
type Product {
description: String
id: ID!
name: String
price: Float!
size: String
}
type Query {
getProduct(id: ID!): Product
}
enum ModelSortDirection {
ASC
DESC
}
enum OrderStatus {
CANCELED
COMPLETED
ONHOLD
PENDING_PAYMENT
PROCESSING
}
input CreateProductInput {
description: String
name: String
price: Float!
size: String
}
Data source
The lambda data source can be defined as a lambda function that takes the connects to the RDS database and execute a query that is sent as parameter. Firstly, the CloudFormation definition of the data source is below:
ServerlessWebRDSLambdaDataSource:
Type: "AWS::AppSync::DataSource"
DependsOn:
- ServerlessWebAPI
Properties:
ApiId: !GetAtt ServerlessWebAPI.ApiId
Name: "ServerlessWebRDSLambdaDataSource"
Description: "Aurora DataSource via Lambda."
Type: "AWS_LAMBDA"
ServiceRoleArn: !GetAtt ServerlessWebRDSServiceRole.Arn
LambdaConfig:
LambdaFunctionArn: !GetAtt ServerlessWebDSLambda.Arn
ServerlessWebDSLambda:
Type: "AWS::Lambda::Function"
Properties:
Role: !GetAtt ServerlessWebLambdaExecutionRole.Arn
Code:
S3Bucket: !Ref ResolverBucket
S3Key: !Join
- '/'
- - !Ref 'ResolverRootKey'
- aurora_lambda_ds.zip
Handler: index.handler
MemorySize: 256
Runtime: "nodejs8.10"
Timeout: "60"
ReservedConcurrentExecutions: 30
Environment:
Variables:
USERNAME: !Sub ${RDSUsername}
PASSWORD: !Sub ${RDSPassword}
ENDPOINT: !GetAtt ServerlessWebRDSCluster.Endpoint.Address
DBNAME: !Sub ${RDSDBName}
VpcConfig:
SecurityGroupIds:
- !GetAtt ServerlessWebRDSVPC.DefaultSecurityGroup
SubnetIds:
- !Ref ServerlessWebRDSSubnetPrivateA
- !Ref ServerlessWebRDSSubnetPrivateB
The nodeJs Lambda function (ServerlessWebDSLambda) that connects to the RDS database is inspired from an example that AWS has made available. It has been improved to use a connection pool which improves the performance and solves a timeout/connection error on the very first request. It takes as input an SQL query to execute, some possible variables to map into the query and a possible response SQL query (in case of a DML query).
const mysql = require('mysql');
const pool = mysql.createPool({
connectionLimit: 10,
host : process.env.ENDPOINT,
user : process.env.USERNAME,
password : process.env.PASSWORD,
database : process.env.DBNAME
});
pool.getConnection((err, connection) => {
if (err) {
if (err.code === 'PROTOCOL_CONNECTION_LOST') {
console.error('Database connection was closed.')
}
if (err.code === 'ER_CON_COUNT_ERROR') {
console.error('Database has too many connections.')
}
if (err.code === 'ECONNREFUSED') {
console.error('Database connection was refused.')
}
}
if (connection) connection.release()
return
})
function executeSQL(pool, sql) {
console.log('Executing SQL:', sql);
return new Promise((resolve,reject) => {
pool.query(sql, (err, result, fields) => {
if (err) {
return reject(err)
}
return resolve(result)
} )
})
}
function populateAndSanitizeSQL(sql, variableMapping, pool) {
Object.entries(variableMapping).forEach(([key, value]) => {
const escapedValue = pool.escape(value);
sql = sql.replace(key, escapedValue);
});
return sql;
}
exports.handler = async (event) => {
console.log('Received event', JSON.stringify(event, null, 3));
const inputSQL = populateAndSanitizeSQL(event.sql, event.variableMapping, pool);
let result = await executeSQL(pool, inputSQL);
if (event.responseSQL) {
const responseSQL =
populateAndSanitizeSQL(event.responseSQL, event.variableMapping, pool);
result = await executeSQL(pool, responseSQL);
}
console.log(JSON.stringify(result, null, 3));
return result;
};
Resolvers
Let us create a resolver for the mutation createProduct defined in the schema. The request template specifies the sql query to create the product, how the input variables are mapped and the response query to return the created product.
ServerlessWebAPiResolverCreateProduct:
Type: "AWS::AppSync::Resolver"
DependsOn: ServerlessWebAPISchema
Properties:
ApiId: !GetAtt ServerlessWebAPI.ApiId
TypeName: "Mutation"
FieldName: "createProduct"
DataSourceName: !GetAtt ServerlessWebRDSLambdaDataSource.Name
RequestMappingTemplate: |
{
"version" : "2017-02-28",
"operation": "Invoke",
#set($id = $util.autoId())
"payload": {
"sql": "insert into PRODUCTS(id, name, description, price, size) VALUES ('$id', :NAME, :DESCRIPTION, :PRICE, :SIZE)",
"variableMapping": {
":NAME": "$context.arguments.input.name",
":DESCRIPTION": "$context.arguments.input.description",
":PRICE": "$context.arguments.input.price",
":SIZE": "$context.arguments.input.size"
},
"responseSQL": "select * from PRODUCTS WHERE id = '$id'"
}
}
ResponseMappingTemplate: |
$util.toJson($context.result[0])
The resolver for the getProduct query is similar. The only difference is that there is no response query needed.
ServerlessWebAPiResolverGetProduct:
Type: "AWS::AppSync::Resolver"
DependsOn: ServerlessWebAPISchema
Properties:
ApiId: !GetAtt ServerlessWebAPI.ApiId
TypeName: "Query"
FieldName: "getProduct"
DataSourceName: !GetAtt ServerlessWebRDSLambdaDataSource.Name
RequestMappingTemplate: |
{
"version": "2018-05-29",
"operation": "Invoke",
#set($id = $util.autoId())
"payload": {
"sql": "select * from PRODUCTS WHERE id = :PRODUCT_ID",
"variableMapping": {
":PRODUCT_ID": "$context.arguments.id"
}
}
}
ResponseMappingTemplate: |
$util.toJson($context.result[0])
Conclusion
Using a lambda function as data source for an App Sync is a flexible solution for integrating App Sync with a RDS database. Lambdas in general are useful for complex logic whether is an RDS or DynamoDB database. This is still a temporary workaround as in the coming months a pure RDS data source for App Sync will be ready.
References
AWS AppSync Using Amazon Aurora as a Data Source via AWS Lambda
Eugene Manuilov
Posted at 20:28h, 23 FebruaryThanks for the article, Defodji.
I would also advice to not keep Graphql schema and resolvers in the Cloudformation template because it can easily blow it away if you have a big project. Much better to keep it schema and resolvers separate files, upload it to S3 bucket and use DefinitionS3Location/RequestMappingTemplateS3Location/ResponseMappingTemplateS3Location properties.
In this case the CloudFormation template would be more slick and maintainable. There is cfpack (https://www.npmjs.com/package/cfpack.js) tool that helps to automate it. Plus it can also combine multiple CloudFormation templates into one if you want to split your big CloudFormation template into logical pieces. So, give it a try.
Defodji Sogbohossou
Posted at 18:55h, 24 FebruaryThanks for the feedback. Indeed the default templates generated by Amplify keep schema and resolvers in separate files which is better for maintenance.