Create a GraphQL API for any existing MySQL and PostgreSQL database

We’re excited to announce a new capability that makes it easy to build scalable, secure GraphQL interfaces over your existing relational databases with AWS Cloud Development Kit (CDK). Provide the AWS Amplify GraphQL API CDK construct with your database credentials stored securely in AWS Systems Manager Parameter Store as a SecureString, and start authoring your GraphQL API to execute SQL statements. This new capability works with any MySQL and PostgreSQL databases on Amazon Relational Database Service (Amazon RDS) or externally-hosted MySQL and PostgreSQL databases.

In October 2023, we announced the new L3 CDK construct for Amplify GraphQL APIs with support to create and connect to DynamoDB databases. With the L3 CDK construct, customers have the ability to author AWS Lambda functions to connect to additional data sources. While customers enjoyed the flexibility of using AWS Lambda, they are looking for more first-class support for MySQL and PostgreSQL databases to remove the undifferentiated heavy-lifting for hand-authoring custom code to manage database connections, SQL statement execution, and networking configurations.

Let’s create a new GraphQL API for existing MySQL and PostgreSQL databases in five steps.

Pre-requisites

To get started you need:

• The CDK CLI via NPM installed
• This guide will assume you have a deployed MySQL and PostgreSQL database deployed with Amazon RDS, but the feature works with any publicly accessible database.

Step 1 – Store Database Credentials Securely in Systems Manager

Place your database connection information (hostname, username, password, port, and database name) into the Systems Manager as secure strings.

Go to the Systems Manager console, navigate to Parameter Store, and click “Create Parameter”. Create five different SecureStrings: one each for the hostname of your database server, the username and password to connect, the database port, and the database name.

Your Systems Manager configuration should look something like this:

Step 2 – Set up a new AWS CDK Project and install the Amplify GraphQL Construct

Create a new AWS CDK application, and install the AWS Amplify GraphQL construct dependency by running the following AWS CDK CLI command in your Terminal:

mkdir sql-api
cd sql-api
cdk init app --language=typescript

Then open your project and run npm install @aws-amplify/graphql-api-construct to add the GraphQL API construct to your dependencies.

Step 3 – Define your API queries and mutations with a GraphQL Schema

Create a new schema.graphql file within the AWS CDK app’s lib/ folder that includes the APIs you want to expose. Define your GraphQL object types, queries, and mutations to match the APIs you wish to expose. For example, define object types for database tables, queries to fetch data from those tables, and mutations to modify those tables.

type Meal {
  id: Int!
  name: String!
}

type Query {
  listMeals(contains: String!): [Meal]
    @sql(statement: "SELECT * FROM Meals WHERE name LIKE CONCAT('%', :contains, '%');")
    @auth(rules: [{ allow: public }])
}

You can use the :notation to reference arguments from the query request. Amplify’s GraphQL API operate on a deny-by-default basis. With the { allow: public } rule, we designate that anyone using an API Key can call this query. Review Authorization rules to limit access to these queries and mutations based on API Key, Amazon Cognito User Pool, OpenID Connect, AWS Identity and Access Management (IAM), or a custom Lambda function.

Step 4 – Configure the GraphQL API construct and, optionally, VPC settings

Create a new instance of the GraphQL API CDK construct within the AWS CDK stack that reference the SSM paths within the database. Import the construct into your sql-api-stack.ts file.

import { AmplifyGraphqlApi, AmplifyGraphqlDefinition } from '@aws-amplify/graphql-api-construct';

In your AWS CDK stack code, create an instance of GraphqlApi. Pass in the SSM parameter paths to your database credentials as props. Also set the schema property to point to your .graphql schema file.

export class RdsStack extends cdk.Stack {
  constructor(scope: Construct, id: string, props?: cdk.StackProps) {
    super(scope, id, props);
    const amplifyApi = new AmplifyGraphqlApi(this, "AmplifyApi", {
      definition: AmplifyGraphqlDefinition.fromFilesAndStrategy(
        path.join(__dirname, "schema.graphql"),
        {
          dbType: 'MYSQL',
          name: 'MySQLSchemaDefinition',
          dbConnectionConfig: {
            databaseNameSsmPath: '/rds-test/database',
            hostnameSsmPath: '/rds-test/host',
            passwordSsmPath: '/rds-test/password',
            portSsmPath: '/rds-test/port',
            usernameSsmPath: '/rds-test/username',
          },
        },
      ),
      authorizationModes: {
        // We recommend to only use API key authorization for development purposes.
        defaultAuthorizationMode: 'API_KEY',
        apiKeyConfig: {
          expires: cdk.Duration.days(30)
        }
      }
    })
  }
}

Because the MySQL database in my case is deployed on Amazon RDS behind an Amazon Virtual Private Cloud (Amazon VPC), I also need to provide the database’s Amazon VPC information to the GraphQL API.

In the GraphqlApi construct, configure the vpcConfiguration property to point to an Amazon VPC, availability zones, and security group ID.

    const amplifyApi = new AmplifyGraphqlApi(this, "AmplifyApi", {
      definition: AmplifyGraphqlDefinition.fromFilesAndStrategy(
        path.join(__dirname, "schema.graphql"), {
        dbType: 'MYSQL',
        name: 'MySQLSchemaDefinition',
        dbConnectionConfig: { ... },
        // Place all VPC Configuration in here. If your database is 
        // publicly reachable over the Internet, you can skip this config.
        vpcConfiguration: {
          // (1) Set the security group ID
          securityGroupIds: ['sg-08XXXXXXX'],
          // (2) Set the VPC ID
          vpcId: 'vpc-c3XXXXXX',
          // (3) Set the available subnet + availability zone config
          subnetAvailabilityZoneConfig: [{
            availabilityZone: 'us-east-1b',
            subnetId: 'subnet-5bXXXXXXX'
          }]
        },
      }),
      authorizationModes: { ... }
    })

Step 5 – Deploy your GraphQL API

Then deploy the AWS CDK app. Run cdk deploy to launch your GraphQL API stack with the connected database from your schema. Now you can go to your AWS AppSync console and start running GraphQL queries and mutations.

Bonus: Refactor inline SQL statements into file references

Writing SQL statements inline with your GraphQL API can become hard to manage, especially as your API grows. To make it easier to manage these queries and mutations, author the SQL statements in a separate .sql file and add them to your GraphQL schema as a reference. Let’s first create a new lib/sql-statements folder and add in a listMeals.sql file with your SQL statement. Your lib/sql-statements/listMeals.sql file should look like this:

SELECT * FROM Meals;

In your lib/sql-api-stack.ts file, read from the sql-statements/ folder and add them as custom SQL statements to your Amplify GraphQL API:

// ...Other imports
import * as fs from 'fs';
import * as path from 'path';
import { AmplifyGraphqlApi, AmplifyGraphqlDefinition, SQLLambdaModelDataSourceStrategyFactory } from '@aws-amplify/graphql-api-construct';

export class RdsStack extends cdk.Stack {
  constructor(scope: Construct, id: string, props?: cdk.StackProps) {
    super(scope, id, props);

    // Define custom SQL statements folder path
    const sqlStatementsPath = path.join(__dirname, 'sql-statements')
    
    // Use the Factory to define the SQL data source strategy
    const sqlStrategy = SQLLambdaModelDataSourceStrategyFactory.fromCustomSqlFiles(
      // File paths to all SQL statements
      fs.readdirSync(sqlStatementsPath).map(file => path.join(sqlStatementsPath, file)),
       // Move your connection information and VPC config into here
      {
        dbType: 'MYSQL',
        name: 'MySQLSchemaDefinition',
        dbConnectionConfig: { ... },
        vpcConfiguration: { ... },
      }
    )

    const amplifyApi = new AmplifyGraphqlApi(this, "AmplifyApi", {
      definition: AmplifyGraphqlDefinition.fromFilesAndStrategy(
        path.join(__dirname, "schema.graphql"),
        sqlStrategy
      ),
      authorizationModes: {
        defaultAuthorizationMode: 'API_KEY',
        apiKeyConfig: {
          expires: cdk.Duration.days(30)
        }
      }
    })
  }
}

Next, update your GraphQL schema to reference the custom SQL statement. You can reference custom SQL statements based on the basename of the file (i.e. the file name without the “.sql” extension):

type Meal {
  id: Int!
  name: String!
}

type Query {
#- listMeals(contains: String!): [Meal] @sql(statement: "SELECT * FROM Meals WHERE name LIKE :contains") @auth(rules: [{ allow: public }])
  listMeals(contains: String!): [Meal] @sql(reference: "listMeals") @auth(rules: [{ allow: public }])
}

Iterating on your API now becomes much faster. For example, let’s add a new Mutation to create a meal by appending the following content to your GraphQL schema:

type Mutation {
   createMeal(id: Int!, name: String!): AWSJSON @sql(reference: "createMeal") @auth(rules: [{ allow: public }])
}

Note: We return an AWSJSON type as a quick way to just get the raw response back from MySQL. This doesn’t offer additional type safety that you can opt-in to. Review our documentation on how to return modified or created items back within the same request.

Next, create a new lib/sql-statements/createMeal.sql file with the following content:

INSERT INTO Meals (id, name) 
VALUES ( :id, :name );

Now, you can deploy your AWS CDK app again, and your API with the new mutation will be available:

cdk deploy

Clean up

To clean up all the generated resources. Run the following AWS CDK CLI command in your terminal:

cdk destroy

Conclusion

In this guide, you’ve created a new GraphQL API using AWS CDK and created queries and mutations that run SQL statements and secure them with authorization rules. To learn more about Amplify GraphQL API’s integration with MySQL and PostgreSQL databases, check out our documentation on Connecting APIs to existing MySQL and PostgreSQL databases.