New for AWS Amplify – Query MySQL and PostgreSQL database for AWS CDK

Today we’re asserting the overall availability to attach and question your present MySQL and PostgreSQL databases with help for AWS Cloud Development Kit (AWS CDK), a brand new function to create a real-time, safe GraphQL API to your relational database inside or exterior Amazon Web Services (AWS). You can now generate the whole API for all relational database operations with simply your database endpoint and credentials. When your database schema adjustments, you’ll be able to run a command to use the newest desk schema adjustments.

In 2021, we introduced AWS Amplify GraphQL Transformer model 2, enabling builders to develop extra feature-rich, versatile, and extensible GraphQL-based app backends even with minimal cloud experience. This new GraphQL Transformer was redesigned from the bottom as much as generate extensible pipeline resolvers to route a GraphQL API request, apply enterprise logic, akin to authorization, and talk with the underlying knowledge supply, akin to Amazon DynamoDB.

However, prospects wished to make use of relational database sources for his or her GraphQL APIs akin to their Amazon RDS or Amazon Aurora databases along with Amazon DynamoDB. You can now use @mannequin kinds of Amplify GraphQL APIs for each relational database and DynamoDB knowledge sources. Relational database data is generated to a separate schema.sql.graphql file. You can proceed to make use of the common schema.graphql information to create and handle DynamoDB-backed sorts.

When you merely present any MySQL or PostgreSQL database data, whether or not behind a digital non-public cloud (VPC) or publicly accessible on the web, AWS Amplify mechanically generates a modifiable GraphQL API that securely connects to your database tables and exposes create, learn, replace, or delete (CRUD) queries and mutations. You may also rename your knowledge fashions to be extra idiomatic for the frontend. For instance, a database desk is known as “todos” (plural, lowercase) however is uncovered as “ToDo” (singular, PascalCase) to the shopper.

With one line of code, you’ll be able to add any of the prevailing Amplify GraphQL authorization guidelines to your API, making it seamless to construct use circumstances akin to owner-based authorization or public read-only patterns. Because the generated API is constructed on AWS AppSync‘ GraphQL capabilities, safe real-time subscriptions can be found out of the field. You can subscribe to any CRUD occasions from any knowledge mannequin with a number of strains of code.

Getting began along with your MySQL database in AWS CDK
The AWS CDK helps you to construct dependable, scalable, cost-effective functions within the cloud with the appreciable expressive energy of a programming language. To get began, set up the AWS CDK in your native machine.

$ npm set up -g aws-cdk

Run the next command to confirm the set up is right and print the model variety of the AWS CDK.

$ cdk –model

Next, create a brand new listing to your app:

$ mkdir amplify-api-cdk
$ cd amplify-api-cdk

Initialize a CDK app by utilizing the cdk init command.

$ cdk init app --language typescript

Install Amplify’s GraphQL API assemble within the new CDK venture:

$ npm set up @aws-amplify/graphql-api-construct

Open the principle stack file in your CDK venture (often positioned in lib/<your-project-name>-stack.ts). Import the required constructs on the prime of the file:

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

Generate a GraphQL schema for a brand new relational database API by executing the next SQL assertion in your MySQL database. Make positive to output the outcomes to a .csv file, together with column headers, and substitute <database-name> with the title of your database, schema, or each.

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>';

Run the next command, changing <path-schema.csv> with the trail to the .csv file created within the earlier step.

$ npx @aws-amplify/cli api generate-schema 
    --sql-schema <path-to-schema.csv> 
    --engine-type mysql –out lib/schema.sql.graphql

You can open schema.sql.graphql file to see the imported knowledge mannequin out of your MySQL database schema.

enter AMPLIFY {
     engine: String = "mysql"
     globalAuthRule: AuthRule = {permit: public}
}

sort Meals @mannequin {
     id: Int! @majorKey
     title: String!
}

sort Restaurants @mannequin {
     restaurant_id: Int! @majorKey
     deal with: String!
     metropolis: String!
     title: String!
     phone_number: String!
     postal_code: String!
     ...
}

If you haven’t already finished so, go to the Parameter Store within the AWS Systems Manager console and create a parameter for the connection particulars of your database, akin to hostname/url, database title, port, username, and password. These will probably be required within the subsequent step for Amplify to efficiently hook up with your database and carry out GraphQL queries or mutations towards it.

In the principle stack class, add the next code to outline a brand new GraphQL API. Replace the dbConnectionConfg choices with the parameter paths created within the earlier step.

new AmplifyGraphqlApi(this, "MyAmplifyGraphQLApi", {
  apiName: "MySQLApi",
  definition: AmplifyGraphqlDefinition.fromFilesAndStrategy(
    [path.join(__dirname, "schema.sql.graphql")],
    {
      title: "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 assums that your database is accessible from the web. Also, the default authorization mode is about to Api Key for AWS AppSync and the sandbox mode is enabled to permit public entry on all fashions. This is helpful for testing your API earlier than including extra fine-grained authorization guidelines.

Finally, deploy your GraphQL API to AWS Cloud.

$ cdk deploy

You can now go to the AWS AppSync console and discover your created GraphQL API.

Choose your venture and the Queries menu. You can see newly created GraphQL APIs appropriate along with your tables of MySQL database, akin to getMeals to get one merchandise or listingRestaurants to listing all objects.

For instance, when you choose objects with fields of deal with, metropolis, title, phone_number, and so forth, you’ll be able to see a brand new GraphQL question. Choose the Run button and you may see the question outcomes out of your MySQL database.

When you question your MySQL database, you’ll be able to see the identical outcomes.

How to customise your GraphQL schema to your database
To add a customized question or mutation in your SQL, open the generated schema.sql.graphql file and use the @sql(assertion: "") go in parameters utilizing the :<variable> notation.

sort Query {
     listingRestaurantsInState(state: String): Restaurants @sql("SELECT * FROM Restaurants WHERE state = :state;”)
}

For longer, extra complicated SQL queries, you’ll be able to reference SQL statements within the customSqlStatements config possibility. The reference worth should match the title of a property mapped to a SQL assertion. In the next instance, a searchPosts property on customSqlStatements is being referenced:

sort Query {
      searchPosts(searchTerm: String): [Post]
      @sql(reference: "searchPosts")
}

Here is how the SQL assertion is mapped within the API definition.

new AmplifyGraphqlApi(this, "MyAmplifyGraphQLApi", { 
    apiName: "MySQLApi",
    definition: AmplifyGraphqlDefinition.fromFilesAndStrategy( [path.join(__dirname, "schema.sql.graphql")],
    {
        title: "MyAmplifyGraphQLSchema",
        dbType: "MYSQL",
        dbConnectionConfig: {
        //	...ssmPaths,
     }, customSqlStatements: {
        searchPosts: // property title matches the reference worth in schema.sql.graphql 
        "SELECT * FROM posts WHERE content material LIKE CONCAT('%', :searchTerm, '%');",
     },
    }
  ),
//...
});

The SQL assertion will probably be executed as if it had been outlined inline within the schema. The identical guidelines apply by way of utilizing parameters, making certain legitimate SQL syntax, and matching return sorts. Using a reference file retains your schema clear and permits the reuse of SQL statements throughout fields. It is greatest apply for longer, extra difficult SQL queries.

Or you’ll be able to change a subject and mannequin title utilizing the @refersTo directive. If you don’t present the @refersTo directive, AWS Amplify assumes that the mannequin title and subject title precisely match the database desk and column names.

sort Todo @mannequin @refersTo(title: "todos") {
     content material: String
     finished: Boolean
}

When you need to create relationships between two database tables, use the @hasOne and @hasMany directives to ascertain a 1:1 or 1:M relationship. Use the @belongsTo directive to create a bidirectional relationship again to the connection father or mother. For instance, you can also make a 1:M relationship between a restaurant and its meals menus.

sort Meals @mannequin {
     id: Int! @majorKey
     title: String!
     menus: [Restaurants] @hasMany(references: ["restaurant_id"])
}

sort Restaurants @mannequin {
     restaurant_id: Int! @majorKey
     deal with: String!
     metropolis: String!
     title: String!
     phone_number: String!
     postal_code: String!
     meals: Meals @belongsTo(references: ["restaurant_id"])
     ...
}

Whenever you make any change to your GraphQL schema or database schema in your DB situations, you must deploy your adjustments to the cloud:

Whenever you make any change to your GraphQL schema or database schema in your DB situations, you must re-run the SQL script and export to .csv step talked about earlier on this information to re-generate your schema.sql.graphql file after which deploy your adjustments to the cloud:

$ cdk deploy

To be taught extra, see Connect API to present MySQL or PostgreSQL database within the AWS Amplify documentation.

Now obtainable
The relational database help for AWS Amplify now works with any MySQL and PostgreSQL databases hosted anyplace inside Amazon VPC and even exterior of AWS Cloud.

Give it a try to ship suggestions to AWS re:Post for AWS Amplify, the GitHub repository of Amplify GraphQL API, or by means of your regular AWS Support contacts.

— Channy

P.S. Specially due to René Huangtian Brandel, a principal product supervisor at AWS for his contribution to write down pattern codes.