10 mins read

New for AWS Amplify – Querying MySQL and PostgreSQL databases for AWS CDK

Tech4seo0
Spoken by Polly

Today we’re announcing general availability to connect and query your existing MySQL and PostgreSQL databases with support for AWS Cloud Development Kit (AWS CDK), a new feature for building a secure, real-time GraphQL API for your relational database inside or outside of Amazon Web Services (AWS). You can now generate the entire API for all relational database operations using only your database endpoint and credentials. When your database schema changes, you can run a command to apply the latest table schema changes.

In 2021 we announced AWS Amplify GraphQL Transformer version 2This enables developers to develop more feature-rich, flexible and extensible GraphQL-based app backends even with minimal cloud knowledge. This new GraphQL transformer was built from the ground up to generate extensible pipeline resolvers to route a GraphQL API request, apply business logic such as authorization, and communicate with the underlying data source such as: Amazon DynamoDB.

However, customers wanted to use it relational database sources for their GraphQL APIs like yours Amazon RDS or Amazon Aurora Databases in addition to Amazon DynamoDB. You can use it now @model Types of Amplify GraphQL APIs for both relational databases and DynamoDB data sources. Relational database information is generated separately schema.sql.graphql File. You can still use the regular one schema.graphql Files for creating and managing DynamoDB-backed types.

If you simply provide any MySQL or PostgreSQL database information, whether behind a Virtual Private Cloud (VPC) or publicly accessible on the Internet, AWS Amplify automatically generates a modifiable GraphQL API that establishes and exposes a secure connection to your database tables create, read, update or delete (CRUD) queries and mutations. You can also rename your data models to make them more idiomatic for the frontend. For example, a database table is named “todos” (plural, lowercase), but is displayed to the client as “ToDo” (singular, PascalCase).

With one line of code you can add all existing elements Expand GraphQL authorization rules to your API so that use cases such as owner-based authorization or public read-only patterns can be created seamlessly. Because the generated API is built on top of it AWS AppSync“GraphQL capabilities and secure real-time subscriptions are available immediately.” 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 the AWS CDK
The AWS CDK lets you build reliable, scalable, and cost-effective applications in the cloud with the significant expressive power of a programming language. To start, Install the AWS CDK on your local computer.

$ npm install -g aws-cdk

Run the following command to verify that the installation is correct and print out the AWS CDK version number.

$ cdk –version

Next, create a new directory for your app:

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

Initialize a CDK app using cdk init Command.

$ cdk init app --language typescript

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

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

Open the main stack file in your CDK project (usually in lib/<your-project-name>-stack.ts). Import the required constructs at the top of the file:

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

Generate a GraphQL schema for a new relational database API by executing the following SQL statement in your MySQL database. Make sure to output the results to a CSV file, including column headers and replacements <database-name> with the name of your database, your 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>';

Run the following command and replace <path-schema.csv> with the path to the CSV file created in the previous 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 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 already, go to Parameter memory in the AWS Systems Manager console and create a parameter for your database’s connection details, e.g hostname/url, database name, port, usernameAnd password. These will be needed in the next step so that Amplify can successfully connect to your database and perform GraphQL queries or mutations against it.

In the main stack class, add the following code to define a new GraphQL API. Replace that dbConnectionConfg Options with the parameter paths created in the previous step.

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 that your database is accessible via the Internet. Additionally, the default authorization mode is set to Api Key for AWS AppSync and sandbox mode is enabled to enable public access on all models. This is useful for testing your API before adding more detailed authorization rules.

Finally, deploy your GraphQL API to the AWS Cloud.

$ cdk deploy

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

Choose your project and that Interrogate Menu. You can see newly created GraphQL APIs compatible with your MySQL database tables, such as: getMeals get an item or listRestaurants to list all items.

For example, if you select items using the fields address, city, name, phone_numberetc. you can see a new GraphQL query. Select the Run Click the button and you can see the query results from your MySQL database.

If you query your MySQL database you can see the same results.

How to customize your GraphQL schema for your database
To add a custom query or mutation to your SQL, open the generated one schema.sql.graphql file and use the @sql(statement: "") Pass parameters with the :<variable> Notation.

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

For longer, more complex SQL queries, you can refer to SQL statements in the customSqlStatements Configuration option. The reference value must match the name of a property associated with an SQL statement. In the following example a searchPosts property customSqlStatements is referenced:

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

This is how the SQL statement is mapped in the API definition.

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

The SQL statement is executed as if it were defined inline in the schema. The same rules apply to using parameters, ensuring valid SQL syntax, and matching return types. Using a reference file keeps your schema clean and allows you to reuse SQL statements across fields. This is the best practice for longer, more complicated SQL queries.

Or you can change a field and model name with @refersTo Policy. If you don’t specify that @refersTo statement, AWS Amplify assumes that the model name and field name exactly match the database table and column names.

type Todo @model @refersTo(name: "todos") {
     content: String
     done: Boolean
}

If you want to create relationships between two database tables, use the @hasOne And @hasMany Instructions to create a 1:1 or 1:M relationship. Use that @belongsTo Statement to create a two-way relationship back to the parent relationship element. For example, you can create a 1:M relationship between a restaurant and its menus.

type Meals @model {
     id: Int! @primaryKey
     name: String!
     menus: [Restaurants] @hasMany(references: ["restaurant_id"])
}

type Restaurants @model {
     restaurant_id: Int! @primaryKey
     address: String!
     city: String!
     name: String!
     phone_number: String!
     postal_code: String!
     meals: Meals @belongsTo(references: ["restaurant_id"])
     ...
}

Whenever you make changes to your GraphQL schema or database schema in your DB instances, you should deploy your changes to the cloud:

Whenever you make changes to your GraphQL schema or database schema in your DB instances, you should re-run the SQL script and follow the Export to .csv step mentioned earlier in this guide to regenerate your schema schema.sql.graphql File and then deploy your changes to the cloud:

$ cdk deploy

For more information, see Connect the API to the existing MySQL or PostgreSQL database in the AWS Amplify documentation.

Available now
Relational database support for AWS Amplify now works with all MySQL and PostgreSQL databases hosted anywhere within Amazon VPC or even outside of the AWS Cloud.

Try it and send feedback AWS re:Post for AWS AmplifyThe GitHub repository the Amplify GraphQL API or through your usual AWS support contacts.

— Channy

PS: Special thanks goes to René Huangtian Brandela principal product manager at AWS, for his contribution to writing sample code.

Related Posts