How to use tagged templates in AppSync to send queries to RDS
Using the new resolver structure is now the recommended way to write SQL queries
Integrating AppSync with RDS
Originally, the AppSync RDS data source supported specifying a statements
and a variableMap
fields in its request mapping template that the JS resolvers
just adopted silently. For a long time that was the only managed way to integrate AppSync with RDS
and it was terrible and unsafe to use
due to it not doing automatic escaping of the values passed in the variableMap
.
Fortunately, we now have a better way to interface with SQL databases thanks to the new SQL tagged templates. This not only provides a more concise way to write SQL statements thanks to JavaScript's tagged templates feature, it also automatically escapes the inserted variables. This makes it the recommended way of using RDS with AppSync and I consider the old way is now deprecated.
What did not change is that the RDS data source still requires the data API enabled for the cluster and its availability depends on the region. See this table to find out which version you can use.
Tagged templates
To run a SELECT
to fetch some data from a table, use:
import {util} from "@aws-appsync/utils";
import {sql, createPgStatement, toJsonObject} from "@aws-appsync/utils/rds";
export function request(ctx) {
return createPgStatement(sql`
SELECT * FROM "user_group" WHERE id = ${ctx.args.id}
`);
}
The magic is the sql
part where you can define the statement and you can also define the variables. Here, the ctx.args.id
will be automatically
escaped and that provides an easy way to insert parameters.
The createPgStatement
formats the query to a Postgres database. If you use a MySQL-compatible one, use createMySQLStatement
instead.
The result is in the same format as before, so the easiest is to use the toJsonObject
helper:
export function response(ctx) {
if (ctx.error) {
return util.error(ctx.error.message, ctx.error.type);
}
return toJsonObject(ctx.result)[0][0];
}
The above code returns the first row (second index) of the first statement (first index).
If you need to return a list, use the same structure but don't define the second index:
import {util} from "@aws-appsync/utils";
import {sql, createPgStatement, toJsonObject} from '@aws-appsync/utils/rds';
export function request(ctx) {
return createPgStatement(sql`
SELECT * FROM "user" WHERE group_id = ${ctx.source.id}
`);
}
export function response(ctx) {
if (ctx.error) {
return util.error(ctx.error.message, ctx.error.type);
}
return toJsonObject(ctx.result)[0];
}
Inserting data
Insertion works similarly to the original data source: you can pass 2 statements to the create*Statement
as seen in the
types:
export declare function createPgStatement(statement1: Statement, statement2?: Statement | null): RDSRequest;
This makes it possible to have an INSERT INTO
followed by a SELECT
:
export function request(ctx) {
const id = util.autoId();
return createPgStatement(
sql`
INSERT INTO "user" (id, name, group_id) VALUES (${id}, ${ctx.args.name}, ${ctx.args.groupId})
`,
sql`
SELECT * FROM "user" WHERE id = ${id}
`
);
}
export function response(ctx) {
return toJsonObject(ctx.result)[1][0];
}
An alternative here for Postgres is to use the returning
keyword that defines what the
INSERT INTO
should return so that there is no need for an extra SELECT
:
export function request(ctx) {
return createPgStatement(
sql`
INSERT INTO "user_group" (id, name) VALUES (${util.autoId()}, ${ctx.args.name}) RETURNING *
`,
);
}
export function response(ctx) {
return toJsonObject(ctx.result)[0][0];
}
Note that in this case the first result will be returned (toJsonObject(...)[0]
).