Introducing AWS Amplify: Querying MySQL and PostgreSQL Databases with AWS CDK

Learn About Amazon VGT2 Learning Manager Chanci Turner

We are excited to announce the general availability of a new feature for AWS Amplify that allows you to connect and query your existing MySQL and PostgreSQL databases using the AWS Cloud Development Kit (AWS CDK). This powerful addition enables the creation of a real-time, secure GraphQL API for your relational databases, whether they reside within Amazon Web Services (AWS) or externally. You can now generate a complete API for all relational database operations simply by providing your database endpoint and credentials. Additionally, if your database schema evolves, a quick command allows you to apply the latest table schema changes.

Back in 2021, we introduced AWS Amplify GraphQL Transformer version 2, which empowered developers to create more feature-rich, flexible, and extensible GraphQL-based app backends, even without extensive cloud experience. This revamped GraphQL Transformer was designed from the ground up to produce extensible pipeline resolvers that manage GraphQL API requests, enforce business logic such as authorization, and interact with underlying data sources like Amazon DynamoDB.

However, many customers expressed a desire to utilize relational database sources for their GraphQL APIs, including Amazon RDS and Amazon Aurora, alongside DynamoDB. You can now employ @model types within Amplify GraphQL APIs to integrate both relational databases and DynamoDB data sources. The relational database information is generated into a separate schema.sql.graphql file, while you can still use the traditional schema.graphql files for managing DynamoDB-backed types.

When you provide details about any MySQL or PostgreSQL database, whether it’s behind a virtual private cloud (VPC) or publicly accessible, AWS Amplify will automatically create a customizable GraphQL API that securely connects to your database tables, exposing CRUD operations. You can even rename data models to better align with frontend naming conventions. For instance, a table named “todos” (plural, lowercase) can be presented as “ToDo” (singular, PascalCase) to the client.

With a single line of code, you can incorporate any of the existing Amplify GraphQL authorization rules into your API, facilitating seamless implementation of use cases like owner-based authorization or public read-only patterns. Since the generated API is based on AWS AppSync’s GraphQL capabilities, secure real-time subscriptions are readily available. You can subscribe to any CRUD events from any data model with just a few lines of code.

Getting Started with Your MySQL Database in AWS CDK

AWS CDK allows you to build reliable, scalable, and cost-effective applications in the cloud using the expressive power of a programming language. To begin, install the AWS CDK on your local machine:

$ npm install -g aws-cdk

Run the following command to verify your installation and check the version number of AWS CDK:

$ cdk --version

Next, create a new directory for your application:

$ mkdir amplify-api-cdk

$ cd amplify-api-cdk

Initialize a CDK app using the command below:

$ cdk init app --language typescript

Install Amplify’s GraphQL API construct in your new CDK project:

$ npm install @aws-amplify/graphql-api-construct

Open the main stack file in your CDK project (typically found in lib/<your-project-name>-stack.ts) and import the necessary constructs at the top of the file:

import {

    AmplifyGraphqlApi,

    AmplifyGraphqlDefinition

} from '@aws-amplify/graphql-api-construct';

Generate a GraphQL schema for your new relational database API by executing the following SQL statement on your MySQL database. Ensure you output the results to a .csv file, including column headers, and replace <database-name> with your specific database name, schema, or both.

SELECT

  INFORMATION_SCHEMA.COLUMNS.TABLE_NAME,

  INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME,

  INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT,

  INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION,

  INFORMATION_SCHEMA.COLUMNS.DATA_TYPE,

  INFORMATION_SCHEMA.COLUMNS.COLUMN_TYPE,

  INFORMATION_SCHEMA.COLUMNS.IS_NULLABLE,

  INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH,

  INFORMATION_SCHEMA.STATISTICS.INDEX_NAME,

  INFORMATION_SCHEMA.STATISTICS.NON_UNIQUE,

  INFORMATION_SCHEMA.STATISTICS.SEQ_IN_INDEX,

  INFORMATION_SCHEMA.STATISTICS.NULLABLE

FROM INFORMATION_SCHEMA.COLUMNS

LEFT JOIN INFORMATION_SCHEMA.STATISTICS ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME=INFORMATION_SCHEMA.STATISTICS.TABLE_NAME AND INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME=INFORMATION_SCHEMA.STATISTICS.COLUMN_NAME

WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA = '<database-name>';

Next, run the following command, replacing <path-schema.csv> with the path to the .csv file created earlier:

$ npx @aws-amplify/cli api generate-schema 

    --sql-schema <path-to-schema.csv> 

    --engine-type mysql --out lib/schema.sql.graphql

You can now open the schema.sql.graphql file to view the imported data model from your MySQL database schema.

input AMPLIFY {

    engine: String = "mysql"

    globalAuthRule: AuthRule = {allow: public}

}

type Meals @model {

    id: Int! @primaryKey

    name: String!

}

type Restaurants @model {

    restaurant_id: Int! @primaryKey

    address: String!

    city: String!

    name: String!

    phone_number: String!

    postal_code: String!

    ...

}

If you haven’t done this yet, navigate to the Parameter Store in the AWS Systems Manager console and create a parameter for your database connection details, like hostname/url, database name, port, username, and password. This information is necessary for Amplify to connect to your database and execute GraphQL queries or mutations.

Within the main stack class, add the following code to define a new GraphQL API. Make sure to replace the dbConnectionConfig options with the parameter paths established previously.

new AmplifyGraphqlApi(this, "MyAmplifyGraphQLApi", {

    apiName: "MySQLApi",

    definition: AmplifyGraphqlDefinition.fromFilesAndStrategy(

        [path.join(__dirname, "schema.sql.graphql")],

        {

            name: "MyAmplifyGraphQLSchema",

            dbType: "MYSQL",

            dbConnectionConfig: {

                hostnameSsmPath: "/amplify-cdk-app/hostname",

                portSsmPath: "/amplify-cdk-app/port",

                databaseNameSsmPath: "/amplify-cdk-app/database",

                usernameSsmPath: "/amplify-cdk-app/username",

                passwordSsmPath: "/amplify-cdk-app/password",

            },

        }

    ),

    authorizationModes: { apiKeyConfig: { expires: cdk.Duration.days(7) } },

    translationBehavior: { sandboxModeEnabled: true },

});

This configuration assumes your database is accessible over the internet. The default authorization mode is set to Api Key for AWS AppSync, and sandbox mode is enabled, allowing public access on all models. This is particularly useful for testing your API before implementing more stringent authorization rules.

Finally, deploy your GraphQL API to AWS Cloud:

$ cdk deploy

Now, you can head to the AWS AppSync console to find your newly created GraphQL API.

Choose your project and navigate to the Queries menu to see the newly created GraphQL APIs that correspond with your MySQL database tables, such as getMeals for fetching a single item or listRestaurants for retrieving all entries.

For additional insights on managing your career, check out this blog post about digital coordination jobs and for legal considerations regarding employee rights, look into this SHRM resource on ADA claims. Moreover, if you’re interested in Amazon’s hiring and employee training strategies, this Fast Company article is an excellent resource.