How do I insert an optional field as null using AppSync Resolvers and Aurora?

Question

I have an optional String field, notes, that is sometimes empty. If it's empty I want to insert null, otherwise I want to insert the string.

Here is my resolver -

{
    "version" : "2017-02-28",
    "operation": "Invoke",
        #set($id = $util.autoId())
        #set($notes = $util.defaultIfNullOrEmpty($context.arguments.notes, 'null'))

        "payload": {
          "sql":"INSERT INTO things VALUES ('$id', :NOTES)",
          "variableMapping": {
            ":NOTES" : $notes
          },
          "responseSQL": "SELECT * FROM things WHERE id = '$id'"
        }

}

With this graphql

mutation CreateThing{
  createThing() {
    id
    notes
  }
}

I get -

{
  "data": {
    "createRoll": {
      "id": "6af68989-0bdc-44e2-8558-aeb4c8418e93",
     "notes": "null"
   }
 }

}

when I really want null without the quotes.

And with this graphql -

mutation CreateThing{
  createThing(notes: "Here are some notes") {
    id
    notes
  }
}

I get -

{
  "data": {
    "createThing": {
      "id": "6af68989-0bdc-44e2-8558-aeb4c8418e93",
      "notes": "Here are some notes"
    }
  }
}

which is what I want.

How do I get a quoteless null and a quoted string into the same field?

Answer 1

TL;DR you should use $util.toJson() to print the $context.arguments.notes correctly. Replace your $notes assignment with

#set($notes = $util.toJson($util.defaultIfNullOrEmpty($context.arguments.notes, null)))

Explanation:

The reason is VTL prints whatever the toString() method returns and your call to $util.defaultIfNullOrEmpty($context.arguments.notes, 'null') will return the string "null", which will be printed as "null".

If you replace with $util.defaultIfNullOrEmpty($context.arguments.notes, null) then it will return a null string. However, VTL will print $notes because that is the way it handles null references. In order to print null, which is the valid JSON representation of null, we have to serialize it to JSON. So the correct statement is:

#set($notes = $util.toJson($util.defaultIfNullOrEmpty($context.arguments.notes, null)))

Full test:

I'm assuming you started with the RDS sample provided in the AWS AppSync console and modified it. To reproduce, I updated the content field in the Schema to be nullable:

type Mutation {
   ...
   createPost(author: String!, content: String): Post
   ...
}
type Post {
    id: ID!
    author: String!
    content: String
    views: Int
    comments: [Comment]
}

and I modified the posts table schema so content can also be null there: (inside the Lambda function)

function conditionallyCreatePostsTable(connection) {
  const createTableSQL = `CREATE TABLE IF NOT EXISTS posts (
    id        VARCHAR(64) NOT NULL,
    author    VARCHAR(64) NOT NULL,
    content   VARCHAR(2048),
    views     INT NOT NULL,
    PRIMARY KEY(id))`;
  return executeSQL(connection, createTableSQL);
}

This is the request template for the createPost mutation:

    {
    "version" : "2017-02-28",
    "operation": "Invoke",
    #set($id = $util.autoId())   
    "payload": {
      "sql":"INSERT INTO posts VALUES ('$id', :AUTHOR, :CONTENT, 1)",
      "variableMapping": {
        ":AUTHOR" : "$context.arguments.author",
        ":CONTENT" : $util.toJson($util.defaultIfNullOrEmpty($context.arguments.content, null))
      },
      "responseSQL": "SELECT id, author, content, views FROM posts WHERE id = '$id'"
    }
}

and response template:

$util.toJson($context.result[0])

The following query:

mutation CreatePost {
  createPost(author: "Me") {
    id
    author
    content
    views
  }
}

returns:

{
  "data": {
    "createPost": {
      "id": "b42ee08c-956d-4b89-afda-60fe231e86d7",
      "author": "Me",
      "content": null,
      "views": 1
    }
  }
}

and

mutation CreatePost {
  createPost(author: "Me", content: "content") {
    id
    author
    content
    views
  }
}  

returns

{
  "data": {
    "createPost": {
      "id": "c6af0cbf-cf05-4110-8bc2-833bf9fca9f5",
      "author": "Me",
      "content": "content",
      "views": 1
    }
  }
}

Answer 2

We were looking into the same issue. For some reason, the accepted answer does not work for us. Maybe because it's a beta feature and there is a new resolver version (2018-05-29 vs 2017-02-28, changes here: Resolver Mapping Template Changelog).

We use this for the time being using NULLIF():

{
    "version": "2018-05-29",
    "statements": [
        "INSERT INTO sales_customers_addresses (`id`, `customerid`, `type`, `company`, `country`, `email`) VALUES (NULL, :CUSTOMERID, :TYPE, NULLIF(:COMPANY, ''), NULLIF(:COUNTRY, ''), :EMAIL)"
    ],
    "variableMap": {
        ":CUSTOMERID": $customerid,
        ":TYPE": "$type",
        ":COMPANY": "$util.defaultIfNullOrEmpty($context.args.address.company, '')",
        ":COUNTRY": "$util.defaultIfNullOrEmpty($context.args.address.country, '')",
        ":EMAIL": "$context.args.address.email"
    }
}