Create complex argument-driven queries from AWS Lambda?

Question

Look for // HERE IS THE PROBLEM PART sentence to find code that is the problem.

I am trying to implement AppSync using AWS Lambda (that connects to RDS Postgres server) as a data source. I want to create puKnowledgeFile query that will update my KnowledgeFile with optional arguments. If the client only provided htmlText and properties as arguments, then my update query should only update these two fields.

type Mutation {
    putKnowledgeFile(
        id: ID!,
        htmlText: String,
        plainText: String,
        properties: AWSJSON
    ): KnowledgeFile
}

type KnowledgeFile {
    id: ID!
    htmlText: String!
    plainText: String!
    properties: AWSJSON!
    lastDateTimeModified: AWSDateTime!
    dateTimeCreated: AWSDateTime!
}

Here is an piece of AWS Lambda code:

exports.handler = async (event, context, callback) => {
  
  /* Connecting to Postgres */

  let data = null;
  let query = ``;
  let values = [];
  
  switch (event.info.fieldName) {
    case "putKnowledgeFile":
      if(event.arguments.htmlText === undefined &&
         event.arguments.plainText === undefined &&
         event.arguments.properties === undefined) {
        callback(`At least one argument except id should be provided in putKnowledgeFile request`);
      }

      // HERE IS THE PROBLEM PART
      query += `update knowledge_file`

      query += `
      set `;
      
      let index = 0;
      for (let fieldName in event.arguments) {
        if(arguments.hasOwnProperty(fieldName)) {
          const fieldValue = event.arguments[fieldName];
    
          if(index === 0) {
            query += `${fieldName}=$${index+1}`
            values.push(fieldValue);
          } else {
            query += `, ${fieldName}=$${index+1}`
            values.push(fieldValue);
          }
    
          index++;
        }
      }

      query += `
where knowledge_file.id = $${index+1};`;
      values.push(event.arguments.id);
      // HERE IS THE PROBLEM PART

        break;
    default:
      callback(`There is no functionality to process this field: ${event.info.fieldName}`);
      return;
  }
  
  let res = null;
  try {
    res = await client.query(query, values); // just sending created query
  } catch(error) {
    console.log("@client.query");
    console.log(error);
  }

  /* DisConnecting from Postgres */

  callback(null, res.rows);

};

Basically, this algorithm creates my query string through multiple string concatenations. I think it's too complicated and error-prone. Is there a way to create dynamic queries based on the presence / absence of certain arguments easily?

Just in case, here is my PostgreSQL schema:

-- main client object for clients
CREATE TABLE client (
  id bigserial primary key,
  full_name varchar(255)
);

-- knowledge_file
create table knowledge_file (
  id bigserial primary key,
  html_text text,
  plain_text text,
  properties jsonb,
  last_date_modified timestamptz,
  date_created timestamptz,
  word_count varchar(50)
);

-- which client holds which knowledge file
create TABLE client_knowledge_file (
  id bigserial primary key,
  client_id bigint not null references client(id),
  knowledge_file_id bigint not null references knowledge_file(id) unique ON DELETE CASCADE
);

Any specific reason to use Lambda to connect to your RDS instance since you can directly connect your AppSync API to your RDS? — Myz, Sep 01 '20 at 14:19
You mean using Aurora Serverless Data API? Well, then I would need to do similar logic in the mapping templates, and it seems like it will be more convenient to use JS instead of VTL. Am I wrong? — Petro Ivanenko, Sep 01 '20 at 14:21
Yes, that's what I meant. How have you defined these in your RDS? Are these null-able fields? — Myz, Sep 01 '20 at 14:23
Sorry. I mean how have you defined these field (`htmlText` & `properties`) in your RDS table schema? — Myz, Sep 01 '20 at 14:29
I have created a PostgreSQL database using RDS, I aded the schema (i.e. create queries) at the bottom of my asnwer. — Petro Ivanenko, Sep 01 '20 at 14:33

Myz · Answer 1 · 2020-09-01T16:06:38.240

I know this is not an optimum solution and might not completely answer your question but I also ran into similar problem and this is how I solved it.

I created a resolver pipeline.
In one function, I used the select statement to get the current record.
In second function, I checked if the fields (in your case htmlText and properties) are null. If true, then use the ctx.prev.result values otherwise use the new ones).

Practical example

First resolver function:

{
    "version": "2018-05-29",
    "statements": [
        "select id, html_text AS \"htmlText\", plain_text AS \"plainText\", properties, last_date_modified AS \"lastDateTimeModified\", date_created AS \"dateTimeCreated\" from knowledge_file where id = $ctx.args.Id"
    ]
}

Second resolver function:

#set($htmlText = $util.defaultIfNull($ctx.args.htmlText , $ctx.prev.result.htmlText))
#set($properties = $util.defaultIfNull($ctx.args.properties , $ctx.prev.result.properties))


{
    "version": "2018-05-29",
    "statements": [
        "update knowledge_file set html_text = $htmlText, plain_text = $ctx.args.plainText, properties = $properties, last_date_modified = CURRENT_TIMESTAMP, date_created = CURRENT_DATE where id = $ctx.args.Id returning id, html_text AS \"htmlText\", plain_text AS \"plainText\", properties, last_date_modified AS \"lastDateTimeModified\", date_created AS \"dateTimeCreated\""
    ]
}

Create complex argument-driven queries from AWS Lambda?

1 Answers1