How to use tagged templates in AppSync to send queries to RDS

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.

Related

How to use RDS as a Data Source for AppSync

Use an AWS-managed relational database with GraphQL

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

Documentation here

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]).