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

Today we are announcing nan wide readiness to link and query your existing MySQL and PostgreSQL databases pinch support for AWS Cloud Development Kit (AWS CDK), a caller characteristic to create a real-time, unafraid GraphQL API for your relational database wrong aliases extracurricular Amazon Web Services (AWS). You tin now make nan full API for each relational database operations pinch conscionable your database endpoint and credentials. When your database schema changes, you tin tally a bid to use nan latest array schema changes.

In 2021, we announced AWS Amplify GraphQL Transformer type 2, enabling developers to create much feature-rich, flexible, and extensible GraphQL-based app backends moreover pinch minimal unreality expertise. This caller GraphQL Transformer was redesigned from nan crushed up to make extensible pipeline resolvers to way a GraphQL API request, use business logic, specified arsenic authorization, and pass pinch nan underlying information source, specified arsenic Amazon DynamoDB.

However, customers wanted to usage relational database sources for their GraphQL APIs specified arsenic their Amazon RDS aliases Amazon Aurora databases successful summation to Amazon DynamoDB. You tin now usage @model types of Amplify GraphQL APIs for some relational database and DynamoDB information sources. Relational database accusation is generated to a abstracted schema.sql.graphql file. You tin proceed to usage nan regular schema.graphql files to create and negociate DynamoDB-backed types.

When you simply supply immoderate MySQL aliases PostgreSQL database information, whether down a virtual backstage unreality (VPC) aliases publically accessible connected nan internet, AWS Amplify automatically generates a modifiable GraphQL API that securely connects to your database tables and exposes create, read, update, aliases delete (CRUD) queries and mutations. You tin besides rename your information models to beryllium much idiomatic for nan frontend. For example, a database array is called “todos” (plural, lowercase) but is exposed arsenic “ToDo” (singular, PascalCase) to nan client.

With 1 statement of code, you tin adhd immoderate of nan existing Amplify GraphQL authorization rules to your API, making it seamless to build usage cases specified arsenic owner-based authorization aliases nationalist read-only patterns. Because nan generated API is built connected AWS AppSync‘ GraphQL capabilities, unafraid real-time subscriptions are disposable retired of nan box. You tin subscribe to immoderate CRUD events from immoderate information exemplary pinch a fewer lines of code.

Getting started pinch your MySQL database successful AWS CDK
The AWS CDK lets you build reliable, scalable, cost-effective applications successful nan unreality pinch nan sizeable expressive powerfulness of a programming language. To get started, install nan AWS CDK connected your section machine.

$ npm instal -g aws-cdk

Run nan pursuing bid to verify nan installation is correct and people nan type number of nan AWS CDK.

$ cdk –version

Next, create a caller directory for your app:

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

Initialize a CDK app by utilizing nan cdk init command.

$ cdk init app --language typescript

Install Amplify’s GraphQL API conception successful nan caller CDK project:

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

Open nan main stack record successful your CDK task (usually located successful lib/<your-project-name>-stack.ts). Import nan basal constructs astatine nan apical of nan file:

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

Generate a GraphQL schema for a caller relational database API by executing nan pursuing SQL connection connected your MySQL database. Make judge to output nan results to a .csv file, including file headers, and switch <database-name> pinch nan sanction of your database, schema, aliases 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 nan pursuing command, replacing <path-schema.csv> pinch nan way to nan .csv record created successful nan erstwhile step.

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

You tin unfastened schema.sql.graphql record to spot nan imported information exemplary 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 done so, spell to nan Parameter Store successful nan AWS Systems Manager console and create a parameter for nan relationship specifications of your database, specified arsenic hostname/url, database name, port, username, and password. These will beryllium required successful nan adjacent measurement for Amplify to successfully link to your database and execute GraphQL queries aliases mutations against it.

In nan main stack class, adhd nan pursuing codification to specify a caller GraphQL API. Replace nan dbConnectionConfg options pinch nan parameter paths created successful nan erstwhile step.

caller 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: existent }, });

This configuration assums that your database is accessible from nan internet. Also, nan default authorization mode is group to Api Key for AWS AppSync and nan sandbox mode is enabled to let nationalist entree connected each models. This is useful for testing your API earlier adding much fine-grained authorization rules.

Finally, deploy your GraphQL API to AWS Cloud.

$ cdk deploy

You tin now spell to nan AWS AppSync console and find your created GraphQL API.

Choose your task and nan Queries menu. You tin spot recently created GraphQL APIs compatible pinch your tables of MySQL database, specified arsenic getMeals to get 1 point aliases listRestaurants to database each items.

For example, erstwhile you prime items pinch fields of address, city, name, phone_number, and truthful on, you tin spot a caller GraphQL query. Choose nan Run fastener and you tin spot nan query results from your MySQL database.

When you query your MySQL database, you tin spot nan aforesaid results.

How to customize your GraphQL schema for your database
To adhd a civilization query aliases mutation successful your SQL, unfastened nan generated schema.sql.graphql record and usage nan @sql(statement: "") walk successful parameters utilizing nan :<variable> notation.

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

For longer, much analyzable SQL queries, you tin reference SQL statements successful nan customSqlStatements config option. The reference worth must lucifer nan sanction of a spot mapped to a SQL statement. In nan pursuing example, a searchPosts spot connected customSqlStatements is being referenced:

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

Here is really nan SQL connection is mapped successful nan API definition.

caller AmplifyGraphqlApi(this, "MyAmplifyGraphQLApi", { apiName: "MySQLApi", definition: AmplifyGraphqlDefinition.fromFilesAndStrategy( [path.join(__dirname, "schema.sql.graphql")], { name: "MyAmplifyGraphQLSchema", dbType: "MYSQL", dbConnectionConfig: { // ...ssmPaths, }, customSqlStatements: { searchPosts: // spot sanction matches nan reference worth successful schema.sql.graphql "SELECT * FROM posts WHERE contented LIKE CONCAT('%', :searchTerm, '%');", }, } ), //... });

The SQL connection will beryllium executed arsenic if it were defined inline successful nan schema. The aforesaid rules use successful position of utilizing parameters, ensuring valid SQL syntax, and matching return types. Using a reference record keeps your schema cleanable and allows nan reuse of SQL statements crossed fields. It is champion believe for longer, much analyzable SQL queries.

Or you tin alteration a section and exemplary sanction utilizing nan @refersTo directive. If you don’t supply nan @refersTo directive, AWS Amplify assumes that nan exemplary sanction and section sanction precisely lucifer nan database array and file names.

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

When you want to create relationships betwixt 2 database tables, usage nan @hasOne and @hasMany directives to found a 1:1 aliases 1:M relationship. Use nan @belongsTo directive to create a bidirectional narration backmost to nan narration parent. For example, you tin make a 1:M narration betwixt a edifice and its meals 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 immoderate alteration to your GraphQL schema aliases database schema successful your DB instances, you should deploy your changes to nan cloud:

Whenever you make immoderate alteration to your GraphQL schema aliases database schema successful your DB instances, you should re-run nan SQL book and export to .csv measurement mentioned earlier successful this guideline to re-generate your schema.sql.graphql record and past deploy your changes to nan cloud:

$ cdk deploy

To study more, spot Connect API to existing MySQL aliases PostgreSQL database successful nan AWS Amplify documentation.

Now available
The relational database support for AWS Amplify now useful pinch immoderate MySQL and PostgreSQL databases hosted anyplace wrong Amazon VPC aliases moreover extracurricular of AWS Cloud.

Give it a effort and nonstop feedback to AWS re:Post for AWS Amplify, nan GitHub repository of Amplify GraphQL API, aliases done your accustomed AWS Support contacts.

— Channy

P.S. Special acknowledgment to René Huangtian Brandel, a main merchandise head astatine AWS for his publication successful penning sample codes.