Escaping double quotes in the @sql directive statement

[wizard22-cc]

How did you install the Amplify CLI?

npm

If applicable, what version of Node.js are you using?

v20.8.0

Amplify CLI Version

12.10.3

What operating system are you using?

WSL2

Did you make any manual changes to the cloud resources managed by Amplify? Please describe the changes made.

Followed this guide to manually create GraphQL api through CDK
https://docs.amplify.aws/javascript/build-a-backend/graphqlapi/connect-api-to-existing-database/

Describe the bug

I ran npx @aws-amplify/cli api generate-schema --engine-type postgres command to auto-generate GraphQL schema in schema.sql.graphql
This generated below mentioned type

type DBrand
  @refersTo(name: "<Confidential>")
  @model
  @auth(rules: [{ allow: public }]) {
  brandId: ID!
    @refersTo(name: "brand_id")
    @default(value: "gen_random_uuid()")
    @primaryKey
  brandCode: String @refersTo(name: "brand_code")
  brandName: String! @refersTo(name: "brand_name")
  brandCategory: String @refersTo(name: "brand_category")
}

I added below mentioned custom query in the same file schema.sql.graphql

type Query {
  getDBrandByUsername(userName: String!): [DBrand]
    @sql(reference: "getBrandByUsername")
    @auth(rules: [{ allow: public }])
}

When I call the query using graphQl client in front-end, it throws the following error

"Cannot return null for non-nullable type: 'ID' within parent 'DBrand' (/getDBrandByUsername[0]/brandId)"

However, when I renamed the fields(removed @refersTo mapping) to original DB filed names, it works.
Here is the additional change I made to test this

Added a new type DBrand2

type DBrand2 @model @auth(rules: [{ allow: public }]) {
  brand_id: ID! @default(value: "gen_random_uuid()") @primaryKey
  brand_code: String
  brand_name: String!
  brand_category: String
}

Added duplicate of the original query but returning DBrand2 type instead

type Query {
  getDBrand2ByUsername(userName: String!): [DBrand2]
    @sql(reference: "getBrandByUsername")
    @auth(rules: [{ allow: public }])

  getDBrandByUsername(userName: String!): [DBrand]
    @sql(reference: "getBrandByUsername")
    @auth(rules: [{ allow: public }])
}

Expected behavior

Custom query results should be transformed to type generated by generate-schema command

Reproduction steps

1. Generate graphql schema using npx @aws-amplify/cli api generate-schema --engine-type postgres command for table that doesn't follow camel case attribute names
2. Write a custom query that returns result of generated type(should have @refersTo with renamed attribute name)
3. Call the custom query from UI using graphQL client

Project Identifier

No response

Log output

# Put your logs below this line

Additional information

No response

Before submitting, please confirm:

• I have done my best to include a minimal, self-contained set of instructions for consistently reproducing the issue.
• I have removed any sensitive information from my code snippets and submission.

[wizard22-cc]

Another Bug:
Looks like double quotes in custom SQL query, can't be escaped.
Sample query for Postgres:

-- getAllBrands.sql
Select brand_id as "brandId", brand_name as "brandName" from <Confidential>;

type Query {
  getAllBrands(userName: String!): [DBrand]
    @sql(reference: "getAllBrands")
    @auth(rules: [{ allow: public }])
}

Calling this query from GraphQL client, throws the following error

Encountered \"brandId\" at velocity[line 4, column 53]
Was expecting one of:
    <RPAREN> ...
    <WHITESPACE> ...
    "-" ...
    "+" ...
    "*" ...
    "/" ...
    "%" ...
    <LOGICAL_AND> ...
    <LOGICAL_OR> ...
    <LOGICAL_LT> ...
    <LOGICAL_LE> ...
    <LOGICAL_GT> ...
    <LOGICAL_GE> ...
    <LOGICAL_EQUALS> ...
    <LOGICAL_NOT_EQUALS> ...

[lindan4]

How did you install the Amplify CLI?

npm

If applicable, what version of Node.js are you using?

v20.8.0

Amplify CLI Version

12.10.3

What operating system are you using?

WSL2

Did you make any manual changes to the cloud resources managed by Amplify? Please describe the changes made.

Followed this guide to manually create GraphQL api through CDK https://docs.amplify.aws/javascript/build-a-backend/graphqlapi/connect-api-to-existing-database/

Describe the bug

I ran npx @aws-amplify/cli api generate-schema --engine-type postgres command to auto-generate GraphQL schema in schema.sql.graphql This generated below mentioned type

type DBrand
  @refersTo(name: "<Confidential>")
  @model
  @auth(rules: [{ allow: public }]) {
  brandId: ID!
    @refersTo(name: "brand_id")
    @default(value: "gen_random_uuid()")
    @primaryKey
  brandCode: String @refersTo(name: "brand_code")
  brandName: String! @refersTo(name: "brand_name")
  brandCategory: String @refersTo(name: "brand_category")
}

I added below mentioned custom query in the same file schema.sql.graphql

type Query {
  getDBrandByUsername(userName: String!): [DBrand]
    @sql(reference: "getBrandByUsername")
    @auth(rules: [{ allow: public }])
}

When I call the query using graphQl client in front-end, it throws the following error

"Cannot return null for non-nullable type: 'ID' within parent 'DBrand' (/getDBrandByUsername[0]/brandId)"

However, when I renamed the fields(removed @refersTo mapping) to original DB filed names, it works. Here is the additional change I made to test this

Added a new type DBrand2

type DBrand2 @model @auth(rules: [{ allow: public }]) {
  brand_id: ID! @default(value: "gen_random_uuid()") @primaryKey
  brand_code: String
  brand_name: String!
  brand_category: String
}

Added duplicate of the original query but returning DBrand2 type instead

type Query {
  getDBrand2ByUsername(userName: String!): [DBrand2]
    @sql(reference: "getBrandByUsername")
    @auth(rules: [{ allow: public }])

  getDBrandByUsername(userName: String!): [DBrand]
    @sql(reference: "getBrandByUsername")
    @auth(rules: [{ allow: public }])
}

Expected behavior

Custom query results should be transformed to type generated by generate-schema command

Reproduction steps

1. Generate graphql schema using npx @aws-amplify/cli api generate-schema --engine-type postgres command for table that doesn't follow camel case attribute names
2. Write a custom query that returns result of generated type(should have @refersTo with renamed attribute name)
3. Call the custom query from UI using graphQL client

Project Identifier

No response

Log output

Additional information

No response

Before submitting, please confirm:

• I have done my best to include a minimal, self-contained set of instructions for consistently reproducing the issue.
• I have removed any sensitive information from my code snippets and submission.

I'm encountering this issue as well

[lindan4]

For the time being, you can modify the response resolver in the AppSync console for a query like the following:

## [Start] ResponseTemplate. **
#if( $ctx.error )
  $util.error($ctx.error.message, $ctx.error.type)
#else
  #set( $mappedResult = [] )
  #foreach( $item in $ctx.result )
  	#set( $mappedItem = {} )
    #foreach($key in $item.keySet())
        #set( $newKey = $key )
        #if($key == "image_url")
          #set( $newKey = "imageUrl" )
        #elseif($key == "published_date_time")
          #set( $newKey = "publishedDateTime" )
        #elseif( $newKey == "user_id" )
          #set( $newKey = "userId" )
        #end
        $util.qr($mappedItem.put($newKey, $item.get($key)))
    #end
    $util.qr($mappedResult.add($mappedItem))
  #end
  $util.toJson($mappedResult)
#end

[phani-srikar]

Hi thanks for reporting this. There are 2 separate things being brought up here:

• Custom query results should match with the GraphQL idiomatic naming and not the original field names in DB. For this case, since you're authoring the SQL statement, you can use the column_name_in_db AS column_name_in_schema format to correctly return the expected names. I don't see this as a bug.
• The issue here is that the inline SQL statement with quotes is not being escaped when stored in VTL resolver. This is preventing you from specifying case sensitive field names by wrapping them in quotes.