How do I run mutlple SQL statements in an AppSync resolver?

Question

I have two tables, cuts and holds. In response to an event in my application I want to be able to move the values from a hold entry with a given id across to the cuts table. The naïve way is to do an INSERT and then a DELETE.

How do I run multiple sql statements in an AppSync resolver to achieve that result? I have tried the following (replacing sql by statements and turning it into an array) without success.

{
 "version" : "2017-02-28",
 "operation": "Invoke",

 #set($id = $util.autoId())

"payload": {
    "statements": [
        "INSERT INTO cuts (id, rollId, length, reason, notes, orderId) SELECT '$id', rollId, length, reason, notes, orderId FROM holds WHERE id=:ID",
        "DELETE FROM holds WHERE id=:ID"
    ],
    "variableMapping": {
        ":ID": "$context.arguments.id"
    },
    "responseSQL": "SELECT * FROM cuts WHERE id = '$id'"
}

}

Answer 1

If you're using the "AWS AppSync Using Amazon Aurora as a Data Source via AWS Lambda" found here https://github.com/aws-samples/aws-appsync-rds-aurora-sample, you won't be able to send multiple statements in the sql field

If you are using the AWS AppSync integration with the Aurora Serverless Data API, you can pass up to 2 statements in a statements array such as in the example below:

{
    "version": "2018-05-29",
    "statements": [
        "select * from Pets WHERE id='$ctx.args.input.id'",
        "delete from Pets WHERE id='$ctx.args.input.id'"
    ]
}