Today we are excited to announce the general availability of a new feature that allows you to connect and query your existing MySQL and PostgreSQL databases using the AWS Cloud Development Kit (AWS CDK). With this feature, you can create a secure GraphQL API for your relational database within or outside of Amazon Web Services (AWS). By providing your database endpoint and credentials, you can generate the entire API for all relational database operations. Additionally, you can easily apply the latest table schema changes when your database schema changes.
In 2021, we introduced AWS Amplify GraphQL Transformer version 2, which enables developers to build more feature-rich, flexible, and extensible GraphQL-based app backends, even with minimal cloud expertise. This new GraphQL Transformer was redesigned to generate extensible pipeline resolvers that route GraphQL API requests, apply business logic such as authorization, and communicate with the underlying data source, such as Amazon DynamoDB. However, our customers expressed a desire to use relational database sources, such as Amazon RDS or Amazon Aurora databases, for their GraphQL APIs in addition to Amazon DynamoDB.
Now, with the @model types of Amplify GraphQL APIs, you can use both relational databases and DynamoDB data sources. The relational database information is generated to a separate schema.sql.graphql file, while the regular schema.graphql file can still be used to create and manage DynamoDB-backed types. By simply providing your MySQL or PostgreSQL database information, whether it’s behind a virtual private cloud (VPC) or publicly accessible on the internet, AWS Amplify will automatically generate a modifiable GraphQL API that securely connects to your database tables and exposes CRUD queries and mutations. You can also rename your data models to be more idiomatic for the frontend.
Adding authorization rules to your API is also seamless with Amplify GraphQL. With just one line of code, you can add any of the existing Amplify GraphQL authorization rules, making it easy to build use cases such as owner-based authorization or public read-only patterns. Additionally, since the generated API is built on AWS AppSync’s GraphQL capabilities, secure real-time subscriptions are available out of the box. You can subscribe to CRUD events from any data model with just a few lines of code.
To get started with your MySQL database in AWS CDK, follow these steps:
1. Install the AWS CDK on your local machine using the command: `$ npm install -g aws-cdk`
2. Verify the installation using the command: `$ cdk –version`
3. Create a new directory for your app: `$ mkdir amplify-api-cdk` and navigate to it: `$ cd amplify-api-cdk`
4. Initialize a CDK app using the command: `$ cdk init app –language typescript`
5. Install Amplify’s GraphQL API construct in the new CDK project: `$ npm install @aws-amplify/graphql-api-construct`
6. Open the main stack file in your CDK project (usually located in `lib/
“`
import { AmplifyGraphqlApi, AmplifyGraphqlDefinition } from ‘@aws-amplify/graphql-api-construct’;
“`
7. Generate a GraphQL schema for your new relational database API by executing the provided SQL statement on your MySQL database. Save the output to a .csv file with column headers. Replace `
“`
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 = ‘
“`
8. Run the following command, replacing `
“`
$ npx @aws-amplify/cli api generate-schema \
–sql-schema
–engine-type mysql –out lib/schema.sql.graphql
“`
9. Open the `schema.sql.graphql` file to see the imported data model from your MySQL database schema. You can customize this file to match your requirements.
10. If you haven’t already done so, go to the Parameter Store in the AWS Systems Manager console and create a parameter for the connection details of your database, including the hostname/url, database name, port, username, and password. These will be required in the next step for Amplify to connect to your database.
11. In the main stack class, add the following code to define a new GraphQL API. Replace the `dbConnectionConfig` 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 },
});
“`
12. This configuration assumes that your database is accessible from the internet. The default authorization mode is set to API Key for AWS AppSync, and the sandbox mode is enabled to allow public access on all models for testing purposes.
13. Deploy your GraphQL API to AWS Cloud using the command: `$ cdk deploy`
14. You can now go to the AWS AppSync console to find your created GraphQL API. Choose your project and navigate to the Queries menu. You will see the newly created GraphQL APIs that are compatible with your MySQL database tables, such as `getMeals` to get one item or `listRestaurants` to list all items. You can run GraphQL queries and see the results from your MySQL database.
To customize your GraphQL schema for your database, you can add custom queries or mutations in your SQL by opening the generated `schema.sql.graphql` file and using the `@sql(statement: “”)` directive. For longer and more complex SQL queries, you can reference SQL statements in the `customSqlStatements` config option. This allows you to keep your schema clean and reuse SQL statements across fields. You can also create relationships between database tables using the `@hasOne`, `@hasMany`, and `@belongsTo` directives.
Remember to deploy your GraphQL API whenever you make any changes to your GraphQL schema or database schema in your DB instances.
By following these steps, you can easily connect and query your existing MySQL and PostgreSQL databases using AWS CDK and AWS Amplify’s GraphQL capabilities.
Source link
