Generate GraphQL CRUD APIs for AppSync from existing Aurora Postgresql DB

Goal

You want to build a GraphQL API from a PostgreSQL schema in an automated way. 

Requirements

• PostGraphile - builds a GraphQL API from a PostgreSQL schema in seconds.
• AWS Aurora PostgreSQL - instance with existing DB schema.

Applicable use-cases

• GraphQL APIs for СRUD operations on existing PostgreSQL DB schema.

Implementation

Install Dependencies

CDK tooling, Lambda, Shared Layer:

yarn install --dev \
    pg \
    postgraphile \
    @graphile-contrib/pg-simplify-inflector \
    graphql@14.0.2 \
    @types/graphql@14.2.3

CDK tooling:

yarn install appsync-schema-converter

Generate GraphQL Schema

Please copy and adjust the following snippets of code to the recommended locations.

1. Shared code at: packages/shared/src/codegen/graphql.ts

We recommend to keep it in a shared package so that it could be reused by Lambdas and CDK code.

import SchemaBuilder from 'graphile-build/node8plus/SchemaBuilder';
import { GraphQLInputFieldConfigMap, GraphQLSchema } from 'graphql';
import { GraphQLNonNull } from 'graphql/type/definition';
import { Pool, PoolClient } from 'pg';
import { createPostGraphileSchema } from 'postgraphile';

/**
 * Regex patterns for the Tables that will be ommited in generated schema.
 */
const omitTables = [
    /user_password.+/,
];

/**
 * Postgraphile plugin to omit tables
 */
const omitTablesPlugin = (builder: SchemaBuilder) => {
    builder.hook('build', build => {
        const { pgIntrospectionResultsByKind } = build;
        pgIntrospectionResultsByKind.class
            .filter((table: any) => table.isSelectable && table.namespace)
            .forEach((table: any) => {
                if (omitTables.find(p => p.test(table.name))) {
                    table.tags.omit = true;
                }
            });
        return build;
    });
};

/**
 * Postgraphile plugin which forces `NOT NULL` column to be generated 
 * as Nullable field in graphql schema. Required for Primary Keys that 
 * use auto-generated default values from sequences.
 */
const forceNullablePlugin = (builder: SchemaBuilder) => {
    const forceNullable = (fields: GraphQLInputFieldConfigMap, name: string): void => {
        if (fields[name]) {
            const field = fields[name];
            if (field.type instanceof GraphQLNonNull) {
                fields[name].type = field.type.ofType;
            }
        }
    };
    builder.hook('GraphQLInputObjectType:fields', fields => {
        // Call for each NotNullable field that should be forced as Nullable
        forceNullable(fields, 'id');        
        return fields;
    });
};

/**
 * Generates GraphQL AST for the provided database connection
 */
export async function loadGraphqlSchema(pgConfig: Pool | PoolClient): Promise<GraphQLSchema> {
    const simplifyInflectorPlugin = require('@graphile-contrib/pg-simplify-inflector');
    return await createPostGraphileSchema(pgConfig, ['public'], {
        appendPlugins: [omitTablesPlugin, simplifyInflectorPlugin, forceNullablePlugin],
        graphileBuildOptions: {
            pgOmitListSuffix: true,
            pgShortPk: true,
            pgSimplifyAllRows: true,
        },
        ignoreIndexes: false,
        ignoreRBAC: true,
        legacyRelations: 'omit',
        simpleCollections: 'omit',
        hideIndexWarnings: true,
    });
}

2. Sample tooling script: bin/appsync-schema.ts

Define APPSYNC_SCHEMA_PATH constant containing the path to the schema output file.

import yargs from 'yargs';
import { SecretsManager } from 'aws-sdk';
import { readFileSync, writeFileSync } from 'fs';
import { mkdirpSync } from 'fs-extra';
import { dirname } from 'path';
import { Client, Pool } from 'pg';
import { GraphQLSchema, printSchema } from 'graphql';

// NOTE: see 'Shared Code` part
import { loadGraphqlSchema } from 'shared';

/**
 * Converts schema into AppSync compatible format.
 */
function exportSchema(schema: GraphQLSchema): void {
    const { convertSchemas } = require('appsync-schema-converter');
    const origSchema = printSchema(schema, { commentDescriptions: true })
        .replace(/(\s|\[)BigInt/g, '$1Int')
        .replace(/(\s|\[)BigFloat/g, '$1Float')
        .replace(/(\s|\[)Cursor/g, '$1String')
        .replace(/(\s|\[)Time/g, '$1String')
        .replace(/(\s|\[)Datetime/g, '$1String')
        .replace(/scalar .*\n/g, '')
        .replace(/ *#.*\n/g, '')
        .replace(/\n *\n/g, '\n');

    const appSyncSchema = convertSchemas(origSchema, {
        commentDescriptions: true,
        includeDirectives: true,
    });

    mkdirpSync(dirname(APPSYNC_SCHEMA_PATH));
    writeFileSync(APPSYNC_SCHEMA_PATH, appSyncSchema);
    console.info('Generated AppSync schema:', APPSYNC_SCHEMA_PATH);
}

/**
 * Retrieves DB password from AWS Secret Manager
 */
async function readSecret(secretArn: string): Promise<string> {
    const sm = new SecretsManager();
    return await sm
        .getSecretValue({ SecretId: secretArn })
        .promise()
        .then(value => value.SecretString!)
        .catch(reason => {
            throw reason;
        });
}

/**
 * Creates DB connection string using values stored in CDK context
 */
async function createDatabaseUrl(env: string): Promise<string> {
    const ctx = JSON.parse(readFileSync('cdk.context.json').toString());
    const params = ctx.databaseParameters;
    const secret = await readSecret(params.secretArn);
    
    // Prepend database name with environment prefix
    const dbname = `${env}-${params.name}`;
    return `postgres://${params.user}:${secret}@${params.endpointAddress}:${params.port}/${dbname}`;
}

/**
 * Main entrypoint that triggers schema code generation and handles result.
 */
async function generateGraphqlSchema(env: string) {
    const pgPool = new Pool({ 
        connectionString: await createDatabaseUrl(env) 
    });
    loadGraphqlSchema(pgPool)
        .then(exportSchema)
        .then(() => process.exit(0))
        .catch((err: Error) => {
            console.error(err);
            process.exit(1);
        });
}


yargs.command(
    'appsync-schema',
    'Generates GraphQL Schema for AWS AppSync',
    yargs => {
        yargs.option('env', 
            { demandOption: true, describe: 'environment prefix' }
        );
    },
    args => {
        void generateGraphqlSchema(args.env);
    },
)

The CDK context structure used by sample script:

   "databaseParameters": {
      "endpointAddress": "<AURORA_DB_ENDPOINT>",      
      "user": "<AURORA_DB_MASTER_USERNAME>",
      "secretArn": "<SECRET_MANAGER_ARN>",
      "port": 5432,     
      "name": "<YOUR_PROJECT_DATABASE_NAME>"
  },

Execute:

ts-node bin/appsync-schema.ts --env=<ENVIRONMENT_PREFIX>

GraphQL Schema Deployment

Deploy AppSync and GraphQL schema generated in the previous step:

// Create 
this.appSync = new CfnGraphQLApi(this, 'MyGraphqlApi', {
    name: `${Stack.of(this).stackName}-api`,
    // authenticationType: 'AMAZON_COGNITO_USER_POOLS',
    userPoolConfig: { 
        // configure cognito user pool if needed
    },
});

// Read generated graphql schema file
this.appSyncSchemaDef = readFileSync(APPSYNC_SCHEMA_PATH).toString();

// Deploy generated graphql schema 
new CfnGraphQLSchema(this, 'MyGraphqlSchema', {
    apiId: this.graphqlApi.attrApiId,
    definition: this.appSyncSchemaDef,
});

Direct Lambda Resolvers

The reason why we are not using usual request/response VTL mapping templates described in the best way by AWS AppSync Docs:

Superficially, the mapping template bypass operates similarly to using certain mapping templates, as shown in the preceding examples. However, behind the scenes, the evaluation of the mapping templates are circumvented entirely. As the template evaluation step is bypassed, in some scenarios applications might experience less overhead and latency during the response when compared to a Lambda function with a response mapping template that needs to be evaluated.

So it is just enough to create our Lambda Resolver:

import 'source-map-support/register';
import { Callback, Context } from 'aws-lambda';
import { graphql, GraphQLSchema } from 'graphql';
import { Pool } from 'pg';
import { withPostGraphileContext } from 'postgraphile';

import {
    Log,
    createGraphqlSource,
    createDatabaseConnection,
    loadGraphqlSchema,
} from 'shared';

interface Dictionary<T> {
    [key: string]: T;
}

/**
 * Payload structure passed by Direct Lambda Resolver.
 */ 
type AppSyncPayload = {
    identity?: {
        claims: Dictionary<any>;
        username: string;
    };
    request: {
        headers: Dictionary<any>;
    };
    arguments: Dictionary<any>;
    info: {
        selectionSetList?: string[];
        selectionSetGraphQL: string;
        parentTypeName: string;
        fieldName: string;
        variables: Dictionary<any>;
    };
};

/**
 * Creates DB connection from lambda environment variable.
 */
function createDatabaseConnection(): Pool {
    Log.trace('Creating new DB connection');
    return new Pool({
        connectionString: process.env.DATABASE_URL,
        // this ensures the pool can drop a connection if it has gone bad
        min: 0,
        // no need for more since we are in Lambda-land
        max: 1,
    });
}

/**
 * Reconstructs missing graphql query parts that was removed by AppSync.
 */
function createGraphqlSource(params: AppSyncPayload): string {
    let argsIfExist = '';
    if (Object.keys(params.arguments).length > 0) {
        const values = Object.assign({}, params.arguments);
        const append: string[] = [''];

        // special handling for 'orderBy' (convert string to enum)
        const orderBy: string[] = values['orderBy'];
        if (orderBy) {
            delete values.orderBy;
            append.push(`orderBy:[${orderBy.join(',')}]`);
        }

        let args = JSON.stringify(values)
            // remove starting {
            .replace(/^{/, '')
            // remove closing }
            .replace(/}$/, '')
            // remove double-quotes from keys
            .replace(/"([\w\d_]+)":/g, '$1:');

        if (append.length > 1) {
            args += append.join(',');
        }
        argsIfExist = `(${args})`;
    }

    return (
        `${params.info.parentTypeName.toLowerCase()} {\n` +
        `${params.info.fieldName}${argsIfExist} ` +
        `${params.info.selectionSetGraphQL}\n}`
    );
}


let schema: GraphQLSchema;
let pgPool: Pool;

/**
 * GraphQL Resolver Lambda.
 */
export const handler = async (event: AppSyncPayload, context: Context, callback: Callback): Promise<void> => {
    // Skip waiting for event loop and return response immediately
    context.callbackWaitsForEmptyEventLoop = false;
    
    // Cache connection pool between invocations
    if (!pgPool) {
        pgPool = createDatabaseConnection();
    }
    // Cache graphql schema AST between invocations
    if (!schema) {
        schema = await loadGraphqlSchema(pgPool);
    }
    
    const source = createGraphqlSource(event);
    Log.trace('SOURCE:', source);

    const pgCallback = (ctx: any) => graphql(schema, source, event.info.parentTypeName, { ...ctx });
    const result = await withPostGraphileContext({ pgPool }, pgCallback);
    if (result.errors) {
        Log.error('ERROR:', result.errors);
        callback(result.errors[0]);
        return;
    }
    Log.debug('RESULT:', JSON.stringify(result, null, 2));
    callback(null, result.data ? result.data[event.info.fieldName] : null);
};

Deploy Lambdas and Resolvers

Few important notes:

• It will use the AppSync application instance deployed in the previous step
• Resources will be divided into 2 inner stacks to overcome the CloudFormation limitation of 200 resources per stack.
  • mutations stack - for GraphQL Mutation type Resolvers
  • queries stack - for GraphQL Query type Resolvers
• Each stack will deploy its own set of Direct Lambda Resolvers wired up with Resolver Lambda Function and Data Source.

/**
 * Iterates on the GraphQL object type and creates Direct Lambda Resolver for each field.
 */
private createResolvers(stack: NestedStack, type: GraphQLObjectType, dataSource: CfnDataSource): void {
    Object.keys(type.getFields()).forEach(fieldName => {
        const resolver = new CfnResolver(stack, `${fieldName}`, {
            apiId: this.portalAppSync.attrApiId,
            dataSourceName: dataSource.attrName,
            kind: 'UNIT',
            typeName: type.name,
            fieldName: fieldName,
        });
        resolver.addDependsOn(dataSource);
    });
}

/**
 * Helper to configure DB connection string.
 */
private createDatabaseUrl(): string {
    const params = this.node.tryGetContext('databaseParameters')!;
    const secret = Secret.fromSecretAttributes(this, 'DatabaseCredentials', {
        secretArn: params.secretArn,
    });

    const name = `${this.node.tryGetContext('env')}-${PROJECT_NAME}`.toLowerCase();
    return (
        `postgres://${params.user}:${secret.secretValue.toString()}` +
        `@${params.endpointAddress}:${params.port}/${name}`
    );
}

/**
 * Creates nested stacks each having own set of Direct Lambda Resolvers 
 * wired up with Resolver Lambda Function and Data Source.
 */     
private createNestedLambdas(): void {    
    // GraphQL schema AST 
    const schema = buildSchema(this.appSyncSchemaDef, {
        allowLegacySDLEmptyFields: true,
        allowLegacySDLImplementsInterfaces: true,
        assumeValid: true,
        assumeValidSDL: true,
        commentDescriptions: true,
    });
    
    // Common asset with compiled lambda code
    const appSyncAsset = Code.fromAsset(...);
    // Common lambda properties
    const lambdaProps = {
        code: appSyncAsset,
        runtime: Runtime.NODEJS_12_X,
        timeout: Duration.seconds(60),
        vpc: this.vpc,
        layers: [this.nodejsLayer, this.sharedLayer],
        environment: {
            DATABASE_URL: this.dbUrl(),
        },
    };

    const queryStack = new NestedStack(this, `Queries`);
    const queryLambda = new Function(queryStack, 'QueryLambda', {
        handler: 'query.handler',
        ...lambdaProps,
    });
    queryLambda.grantInvoke(this.appSyncRole);

    const queryDataSource = new CfnDataSource(queryStack, 'QueryDataSource', {
        name: 'QueryLambda',
        type: 'AWS_LAMBDA',
        apiId: this.appSync.attrApiId,
        serviceRoleArn: this.appSyncRole.roleArn,
        lambdaConfig: {
            lambdaFunctionArn: queryLambda.functionArn,
        },
    });

    this.createResolvers(queryStack, schema.getQueryType()!, queryDataSource);

    const mutationStack = new NestedStack(this, `Mutations`);
    const mutationLambda = new Function(mutationStack, 'MutationLambda', {
        handler: 'query.handler',
        ...lambdaProps,
    });
    mutationLambda.grantInvoke(this.portalAppSyncRole);

    const mutationDataSource = new CfnDataSource(mutationStack, 'MutationDataSource', {
        name: 'MutationLambda',
        type: 'AWS_LAMBDA',
        apiId: this.appSync.attrApiId,
        serviceRoleArn: this.appSyncRole.roleArn,
        lambdaConfig: {
            lambdaFunctionArn: mutationLambda.functionArn,
        },
    });

    this.createResolvers(mutationStack, schema.getMutationType()!, mutationDataSource);
}

Usage

Sample query using Amplify:

const listTeams = gql`
    query listTeams($customerId: Int!) {
        teams(condition: { customerId: $customerId }, orderBy: [ID_ASC]) {
            id
            customerId
            name
            description
        }
    }
`;


API.graphql(graphqlOperation(listTeams, { customerId: 111 }))
    .then(value => {
            console.debug('Received: ', value);
            return value.data!['teams'] as Team[];
        })

• please check the official Amplify docs for configuration details