Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://docs.amplify.aws/gen1/react/build-a-backend/graphqlapi/connect-api-to-existing-database/ below:

Website Navigation

• Skip to main content

Connect API to existing MySQL or PostgreSQL database - React

The following content requires you to deploy the Amplify GraphQL APIs via AWS Cloud Development Kit (CDK). If you have not yet deployed an Amplify GraphQL API with AWS CDK yet, review Set up GraphQL API.

In this section, you'll learn how to:

• Connect Amplify GraphQL API to an existing MySQL or PostgreSQL database
• Execute SQL statements with custom GraphQL queries and mutations using the new @sql directive
• Generate create, read, update, and delete API operations based on your SQL database schema

Pre-requisites:

• Have an existing MySQL database or PostgreSQL database deployed
• The AWS CDK CLI is installed
• Have an AWS CDK application initialized

This feature is not yet available in the Asia Pacific (Hong Kong, ap-east-1) or Europe (Milan, eu-south-1) regions.

First, place your database connection information (hostname, username, password, port, and database name) into Systems Manager, each as a SecureString.

Go to the Systems Manager console, navigate to Parameter Store, and click "Create Parameter". Create five different SecureStrings: one each for the hostname of your database server, the username and password to connect, the database port, and the database name.

Your Systems Manager configuration should look something like this:

First, place your database connection information (hostname, username, password, port, and database name) into Secrets Manager.

Go to the Secrets Manager console, navigate to Secrets, and click "Store a new secret". You may create the secret in any manner as long as there are username and password keys defined.

Optionally, you can decide whether to encrypt the secret using the KMS key that Secrets Manager creates or a customer managed KMS key that you create.

You can also configure a rotation schedule and create a Lambda function or choose an existing Lambda function from your account to rotate the database credentials automatically.

Install the following package to add the Amplify GraphQL API construct to your dependencies:

Create a new schema.sql.graphql file within your CDK app’s lib/ folder that includes the APIs you want to expose. Define your GraphQL object types, queries, and mutations to match the APIs you wish to expose. For example, define object types for database tables, queries to fetch data from those tables, and mutations to modify those tables.

You can use the :variable notation to reference input variables from the query request.

Learn more

Authorization rules

Amplify’s GraphQL API operates on a deny-by-default basis. The { allow: public } auth rule in the example schema above designates that anyone using an API Key is authorized to execute the query.

Review Authorization rules to limit access to these queries and mutations based on API Key, Amazon Cognito User Pool, OpenID Connect, AWS Identity and Access Management (IAM), or a custom Lambda function.

Next, open the main stack file in your CDK project (usually located in lib/<your-project-name>-stack.ts). Import the necessary constructs at the top of the file:

In the main stack class, add the following code to define a new GraphQL API. Replace stack with the name of your stack instance (often referenced via this):

The API will have an API key enabled for authorization.

Before deploying, make sure to:

• Set a value for name. This will be used to name the AppSync DataSource itself, plus any associated resources like resolver Lambdas. This name must be unique across all schema definitions in a GraphQL API.

• Change the dbType to match your database engine. This is the type of the SQL database used to process model operations for this definition. Supported engines are "MYSQL" or "POSTGRES".

• Update the SSM parameter paths within dbConnectionConfig to point to those existing in your AWS account. These are the parameters the SQL Lambda will use to connect to the database.

• If your database instance exists within a VPC, update the vpcConfiguration properties - vpcId, securityGroupIds, and subnetAvailabilityZoneConfig with your vpc details. This is the configuration of the VPC into which to install the SQL Lambda.

Learn more

Configure VPC settings for your database

If your database exists within a VPC, the RDS instance must be configured to be Publicly accessible. This does not mean the instance needs to accessible from the internet.

The target security group(s) must have two inbound rules set up:

• A rule allowing traffic on port 443 from the security group.

• An inbound rule allowing traffic on the database port from the security group. (Default: 3306 for MySQL. 5432 for PostgreSQL.)

In addition, the target security group(s) must have two outbound rules set up:

• An outbound rule allowing traffic on port 443 to the security group.

• An outbound rule allowing traffic on the database port to the security group. (Default: 3306 for MySQL. 5432 for PostgreSQL.)

NOTE: Make sure to limit the type of inbound traffic your security group allows according to your security needs and/or use cases. For information on security group rules, please refer to the Amazon EC2 Security Group Rules reference.

This feature is currently not supported on Amplify CLI. It requires you to deploy the Amplify GraphQL APIs via AWS Cloud Development Kit (CDK). If you have not yet deployed an Amplify GraphQL API with AWS CDK yet, review Set up GraphQL API.

Learn more

RDS Proxy for improved connectivity

Consider adding an RDS Proxy in front of the cluster to manage database connections.

When using Amplify GraphQL API with a relational database like Amazon RDS, each query from your application needs to open a separate connection to the database.

If there are a large number of queries occurring concurrently, it can exceed the connection limit on the database and result in errors like "Too many connections". To avoid this, Amplify can use an RDS Proxy when connecting your GraphQL API to a database.

The RDS Proxy acts as an intermediary sitting in front of your database. Instead of each application query opening a direct connection to the database, they will connect through the Proxy. The Proxy helps manage and pool these connections to avoid overwhelming your database cluster. This improves the availability of your API, allowing more queries to execute concurrently without hitting connection limits.

However, there is a tradeoff of increased latency - queries may take slightly longer as they wait for an available connection from the Proxy pool. There are also additional costs associated with using RDS Proxy. Please refer to the pricing page for RDS Proxy to learn more.

Amplify GraphQL API for SQL databases introduces the @sql directive, which allows you to define SQL statements in custom GraphQL queries and mutations. This provides more flexibility when the default, auto-generated GraphQL queries and mutations are not sufficient.

There are two ways to specify the SQL statement - inline or by referencing a .sql file.

For getting started, you can embed the SQL statement directly in the schema using the statement argument.

The SQL statement can use parameters in the format :variable, which will be bound to the input variables passed when executing a custom GraphQL query or mutation.

In the example below, a SQL statement is defined, accepting a searchTerm input variable.

For longer, more complex SQL queries, you can specify the statement in separate .sql files rather than inline. Referencing a file keeps your schema clean and allows reuse of SQL statements across fields.

First, update your GraphQL schema file to reference a SQL file name without the .sql extension:

Next, create a new lib/sql-statements folder and add any custom queries or mutations as SQL files. For example, you could create different .sql files for different queries:

Then, you can import the SQLLambdaModelDataSourceStrategyFactory which helps define the datasource strategy from the custom .sql files you've created.

In your lib/<your-project-name>-stack.ts file, read from the sql-statements/ folder and add them as custom SQL statements to your Amplify GraphQL API:

The SQL statements defined in the .sql files will be executed as if they were defined inline in the schema. The same rules apply in terms of using parameters, ensuring valid SQL syntax, and matching the return type to row data.

This feature is currently not supported on Amplify CLI. It requires you to deploy the Amplify GraphQL APIs via AWS Cloud Development Kit (CDK). If you have not yet deployed an Amplify GraphQL API with AWS CDK yet, review Set up GraphQL API.

For reference, you define a GraphQL query by adding a new field under a type Query object:

For reference, you define a GraphQL mutation by adding a new field under a type Mutation object:

SQL statements such as INSERT, UPDATE and DELETE return the number of rows affected.

If you want to return the result of the SQL statement, you can use AWSJSON as the return type.

This will return a JSON response similar to this:

However, you might want to return the actual row data instead.

In MySQL, you can create and call a stored procedure that performs both an UPDATE statement and SELECT query to return a single post.

Create a stored procedure by running the following SQL statement in your MySQL database:

Call the stored procedure from the custom mutation:

In PostgreSQL, you can add a RETURNING clause to an INSERT, UPDATE, or DELETE statement and get the actual modified row data.

Example:

The return type for custom queries and mutations expecting to return row data from SQL statements must be an array of the corresponding model. This is true even for custom get queries, where a single row is expected.

Example

The @auth directive can be used to restrict access to data and operations by specifying authorization rules. It allows granular access control over the GraphQL API based on the user's identity and attributes. You can for example, limit a query or mutation to only logged-in users via an @auth(rules: [{ allow: private }]) rule or limit access to only users of the "Admin" group via an @auth(rules: [{ allow: groups, groups: ["Admin"] }]) rule.

All model-level authorization rules are supported for Amplify GraphQL schemas generated from MySQL and PostgreSQL databases.

In the example below, public users authorized via API Key are granted unrestricted access to all posts.

Add the following auth rule to the Post model within the schema.sql.graphql file:

For more information on each rule please refer to our documentation on Authorization rules.

Field level auth rules are also supported for Amplify GraphQL schemas generated from MySQL and PostgreSQL databases.

In the example below, unauthenticated users can read post data but only the owner of the post can perform operations on the published field.

    { allow: public, operations: [read] },
    @auth(rules: [{ allow: owner }])

For more information on field-level auth rules please refer to our documentation on Field-level authorization rules.

To deploy the API, you can use the cdk deploy command:

This feature is currently not supported on Amplify CLI. It requires you to deploy the Amplify GraphQL APIs via AWS Cloud Development Kit (CDK). If you have not yet deployed an Amplify GraphQL API with AWS CDK yet, review Set up GraphQL API.

Now the API has been deployed and you can start using it!

You can start querying from the AWS AppSync console or integrate it into your application using the AWS Amplify libraries!

You can generate common CRUDL operations for your database tables based on your database schema. This saves time from hand-authoring the GraphQL types, queries, and mutations and SQL statements for common CRUDL use cases. After you generate the operations, you can annotate the @model types with authorization rules.

Create a Ingredients table in your database:

Execute the following SQL statement on your database using a MySQL, PostgreSQL Client, or CLI tool similar to psql and export the output to a CSV file:

You must include column headers when exporting the database schema output to a CSV file.

Replace <database-name> with the name of your database/schema.

Your exported SQL schema should look something like this:

Your exported SQL schema should look something like this:

Next, generate an Amplify GraphQL API schema by running the following command, replacing the --engine-type value with your database engine of mysql or postgres, and the --sql-schema value with the path to the CSV file created in the previous step:

Next, update the first argument of AmplifyGraphqlDefinition.fromFilesAndStrategy to include the schema.sql.graphql file generated in the previous step:

Open your schema.sql.graphql file, you should see something like this. The auto-generated schema automatically changes the casing to better match common GraphQL conventions. Amplify's GraphQL API's operate on a deny-by-default principle, this means you must explicitly add @auth authorization rules in order to make this API accessible to your users. Currently only model-level authorization is supported.

In our example, we'll add a public authorization rule, meaning anyone with an API key can create, read, update, and delete records from the database. Review Customize authorization rules to see the full scope of model-level authorization capabilities.

Finally, remember to deploy your API to the cloud:

To deploy the API, you can use the cdk deploy command:

This feature is currently not supported on Amplify CLI. It requires you to deploy the Amplify GraphQL APIs via AWS Cloud Development Kit (CDK). If you have not yet deployed an Amplify GraphQL API with AWS CDK yet, review Set up GraphQL API.

Now the API has been deployed and you can start using it!

To rename models and fields, you can use the @refersTo directive to map the models in the GraphQL schema to the corresponding table or field by name.

By default, the Amplify CLI singularizes each model name using PascalCase and field names that are either snake_case or kebab-case will be converted to camelCase.

In the example below, the Post model in the GraphQL schema is now mapped to the posts table in the database schema. Also, the isPublished is now mapped to the published column on the posts table.

You can use the @hasOne, @hasMany, and @belongsTo relational directives to create relationships between models. The field named in the references parameter of the relational directives must exist on the child model.

Relationships that query across DynamoDB and SQL data sources are currently not supported. However, you can create relationships across SQL data sources.

Assume that you have users, blogs, and posts tables in your database schema. The following examples demonstrate how you might create different types of relationships between them. Use them as references for creating relationships between the models in your own schema.

Create a one-directional one-to-one relationship between two models using the @hasOne directive.

In the example below, a User has a single Blog.

Create a one-directional one-to-many relationship between two models using the @hasMany directive.

In the example below, a Blog has many Posts.

Make a "has one" or "has many" relationship bi-directional with the @belongsTo directive.

In the example below, a Post belongs to a Blog.

1. Make any adjustments to your SQL statements such as:

1. Regenerate the database schema as a CSV file by following the instructions in Generate GraphQL schema from database schema.

2. Generate an updated schema by running the following command, replacing the --engine-type value with your database engine of mysql or postgres, and the --sql-schema value with the path to the CSV file created in the previous step:

1. Deploy your changes to the cloud:

This feature is currently not supported on Amplify CLI. It requires you to deploy the Amplify GraphQL APIs via AWS Cloud Development Kit (CDK). If you have not yet deployed an Amplify GraphQL API with AWS CDK yet, review Set up GraphQL API.

The Amplify uses AWS Lambda functions to enable features like querying data from your database. To work properly, these Lambda functions need access to common logic and dependencies.

Amplify provides this shared code in the form of Lambda Layers. You can think of Lambda Layers as a package of reusable runtime code that Lambda functions can reference.

When you deploy an Amplify API, it will create two Lambda functions:

This allows you to query and write data to your database from your API.

NOTE: If the database is in a VPC, this Lambda function will be deployed in the same VPC as the database. The usage of Amazon Virtual Private Cloud (VPC) or VPC peering, with AWS Lambda functions will incur additional charges as explained, this comes with an additional cost as explained on the Amazon Elastic Compute Cloud (EC2) on-demand pricing page.

This automatically keeps the SQL Lambda up-to-date by managing its Lambda Layers.

A Lambda layer that includes all the core SQL connection logic lives within the AWS Amplify service account but is executed within your AWS account, when invoked by the SQL Lambda. This allows the Amplify service team to own the ongoing maintenance and security enhancements of the SQL connection logic.

This allows the Amplify team to maintain and enhance the SQL Layer without needing direct access to your Lambdas. If updates to the Layer are needed, the Updater Lambda will receive a signal from Amplify and automatically update the SQL Lambda with the latest Layer.

Note: MySQL does not support time zone offsets in date time or timestamp fields. Instead, we will convert these values to datetime, without the offset.

Unlike MySQL, PostgreSQL does support date time or timestamp values with an offset.

To return the actual SQL error instead of a generic error from GraphQL responses, an environment variable DEBUG_MODE can be set to true on the Amplify-generated SQL Lambda function. You can find this Lambda function in the AWS Lambda console with the naming convention of: <stack-name>-<api-name>-SQLLambdaFunction<hash>.

Our recommended next steps include using the GraphQL API to mutate and query data on app clients or how to customize the authorization rules for your custom queries and mutations. Some resources that will help with this work include:

• Create, update, and delete application data
• Read application data
• Customize Authorization Rules

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4