AWS CDK database queries in PostgreSQL and MySQL


 With support for the AWS Cloud Development Kit (AWS CDK), AWS are now able to connect to and query your current MySQL and PostgreSQL databases. This is a new feature that allows you to construct a secure, real-time GraphQL API for your relational database, either inside or outside of Amazon Web Services (AWS). With merely your database endpoint and login credentials, you can now construct the full API for all relational database operations. You can use a command to apply the most recent modifications to the table schema whenever your database schema changes.

With the release of AWS Amplify GraphQL Transformer version 2, which was announced in 2021, developers can now create GraphQL-based app backends that are more feature-rich, adaptable, and extensible with little to no prior cloud experience. In order to create extensible pipeline resolvers that can route GraphQL API requests, apply business logic like authorization, and interact with the underlying data source like Amazon DynamoDB, this new GraphQL Transformer was completely redesigned.

But in addition to Amazon DynamoDB, users also desired to leverage relational database sources for their GraphQL APIs, including their Amazon RDS or Amazon Aurora databases. Amplify GraphQL APIs now support @model types for relational and DynamoDB data sources. Data from relational databases is produced into a different file called schema.sql.graphql. You may still build and maintain DynamoDB-backed types with standard schema.graphql files.

Upon receiving any MySQL or PostgreSQL database information, whether it is accessible publicly online or through a virtual private cloud (VPC), AWS Amplify will automatically produce a modifiable GraphQL API that can be used to securely connect to your database tables and expose CRUD (create, read, update, or delete) queries and mutations. To make your data models more frontend-friendly, you may also rename them. For instance, a database table with the name “todos” (plural, lowercase) may be accessible to the client as “ToDo” (single, PascalCase).

Any of the current Amplify GraphQL authorization rules can be added to your API with only one line of code, enabling the smooth development of use cases like owner-based authorization and public read-only patterns. Secure real-time subscriptions are accessible right out of the box because the produced API is based on AWS AppSync’s GraphQL capabilities. With a few lines of code, you can subscribe to any CRUD event from any data model.

Starting up the MySQL database in the AWS CDK

The AWS CDK gives you the significant expressive capability of a programming language to create dependable, scalable, and affordable cloud applications. Install the AWS CDK on your local computer to begin.

To print the AWS CDK version number and confirm that the installation is correct, use the following command.

Next, make your app’s new directory:

Use the cdk init command to set up a CDK application.

Add the GraphQL API construct from Amplify to the newly created CDK project.

Launch your CDK project’s primary stack file, which is often found in lib/<your-project-name>-stack.ts. Add the following imports to the top of the file:

Run the following SQL query on your MySQL database to create a GraphQL schema for a new relational database API.

$ cdk –version

Make sure the results are written to a.csv file with column headers included, and change <database-name> to the name of your schema, database, or both.

Run the following command, substituting the path to the.csv file prepared in the previous step for <path-schema.csv>.

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

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

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

To view the imported data model from your MySQL database schema, open the schema.sql.graphql file.

If you haven’t already, establish a parameter for your database’s connection information, including hostname/url, database name, port, username, and password, in the AWS Systems Manager console’s Parameter Store. To properly connect to your database and run GraphQL queries or modifications against it, Amplify will need these in the following step.

To define a new GraphQL API, add the following code to the main stack class. Put the parameter paths that were made in the previous step in lieu of the dbConnectionConfg options.

This setting assumes that you can access your database online. Additionally, on all models, the sandbox mode is enabled to permit public access, and the default authorization mode is set to Api Key for AWS AppSync. You can use this to test your API before implementing more detailed authorization restrictions.

Lastly, launch your GraphQL API on the Amazon Cloud

Select the Queries menu along with your project. The newly developed GraphQL APIs, like getMeals to retrieve a single item or listRestaurants to list all products, are compatible with your MySQL database tables.

like instance, a new GraphQL query appears when you pick objects that have fields like address, city, name, phone number, and so on. You may view the query results from your MySQL database by selecting the Run button.

You get identical results when you run a query on your MySQL database.

Currently accessible

Any MySQL and PostgreSQL databases hosted anywhere within an Amazon VPC or even outside of the AWS Cloud are now compatible with the relational database support for AWS Amplify.

Post a Comment

0 Comments