Serverless GraphQL AppSync API with RDS

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

Serverless GraphQL AppSync & RDS implementation