Using the introspection feature (console)Using the introspection feature (API)

RDS introspection

Amazon AppSync makes building APIs from existing relational databases easy. Its introspection utility can discover models from database tables and propose GraphQL types. The Amazon AppSync console's Create API wizard can instantly generate an API from an Aurora MySQL or PostgreSQL database. It automatically creates types and JavaScript resolvers to read and write data.

Amazon AppSync provides direct integration with Amazon Aurora databases through the Amazon RDS Data API. Rather than requiring a persistent database connection, the Amazon RDS Data API offers a secure HTTP endpoint that Amazon AppSync connects to for running SQL statements. You can use this to create a relational database API for your MySQL and PostgreSQL workloads on Aurora.

Building an API for your relational database with Amazon AppSync has several advantages:

Your database is not directly exposed to clients, decoupling the access point from the database itself.
You can build purpose-built APIs tailored to the needs of different applications, removing the need for custom business logic in frontends. This aligns with the Backend-For-Frontend (BFF) pattern.
Authorization and access control can be implemented at the Amazon AppSync layer using various authorization modes to control access. No additional compute resources are required to connect to the database, such as hosting a web server or proxying connections.
Real-time capabilities can be added via subscriptions, with data mutations made through AppSync automatically pushed to connected clients.
Clients can connect to the API over HTTPS using common ports like 443.

Amazon AppSync makes building APIs from existing relational databases easy. Its introspection utility can discover models from database tables and propose GraphQL types. The Amazon AppSync console's Create API wizard can instantly generate an API from an Aurora MySQL or PostgreSQL database. It automatically creates types and JavaScript resolvers to read and write data.

Amazon AppSync provides integrated JavaScript utilities to simplify writing SQL statements in resolvers. You can use Amazon AppSync's sql tag templates for static statements with dynamic values, or the rds module utilities to build statements programmatically. See the resolver function reference for RDS data sources and built-in modules for more.

Using the introspection feature (console)

For a detailed tutorial and getting started guide, see Tutorial: Aurora PostgreSQL Serverless with Data API.

The Amazon AppSync console allows you to create an Amazon AppSync GraphQL API from your existing Aurora database configured with the Data API in just a few minutes. This quickly generates an operational schema based on your database configuration. You can use the API as-is or build on it to add features.

Sign in to the Amazon Web Services Management Console and open the AppSync console.
1. In the Dashboard, choose Create API.
Under API options, choose GraphQL APIs, Start with an Amazon Aurora cluster, then Next.
1. Enter an API name. This will be used as an identifier for the API in the console.
2. For contact details, you can enter a point of contact to identify a manager for the API. This is an optional field.
3. Under Private API configuration, you can enable private API features. A private API can only be accessed from a configured VPC endpoint (VPCE). For more information, see Private APIs.
  
  We don't recommend enabling this feature for this example. Choose Next after reviewing your inputs.
In the Database page, choose Select database.
1. You need to choose your database from your cluster. The first step is to choose the Region in which your cluster exists.
2. Choose the Aurora cluster from the drop-down list. Note that you must have created and enabled a corresponding data API before using the resource.
3. Next, you must add the credentials for your database to the service. This is primarily done using Amazon Secrets Manager. Choose the Region in which your secret exists. For more information on how to retrieve secret information, see Find secrets or Retrieve secrets.
4. Add your secret from the drop-down list. Note that the user must have read permissions for your database.

Choose Import.

Amazon AppSync will start introspecting your database, discovering tables, columns, primary keys, and indexes. It checks that the discovered tables can be supported in a GraphQL API. Note that to support creating new rows, tables need a primary key, which can use multiple columns. Amazon AppSync maps table columns to type fields as follows:

Data type	Field type
VARCHAR	String
CHAR	String
BINARY	String
VARBINARY	String
TINYBLOB	String
TINYTEXT	String
TEXT	String
BLOB	String
MEDIUMTEXT	String
MEDIUMBLOB	String
LONGTEXT	String
LONGBLOB	String
BOOL	Boolean
BOOLEAN	Boolean
BIT	Int
TINYINT	Int
SMALLINT	Int
MEDIUMINT	Int
INT	Int
INTEGER	Int
BIGINT	Int
YEAR	Int
FLOAT	Float
DOUBLE	Float
DECIMAL	Float
DEC	Float
NUMERIC	Float
DATE	AWSDate
TIMESTAMP	String
DATETIME	String
TIME	AWSTime
JSON	AWSJson
ENUM	ENUM

Once table discovery is complete, the Database section will be populated with your information. In the new Database tables section, the data from the table may already be populated and converted to a type for your schema. If you don't see some of the required data, you can check for it by choosing Add tables, clicking on the checkboxes for those types in the modal that appears, then choosing Add.

To remove a type from the Database tables section, click on the checkbox next to the type you want to remove, then choose Remove. The removed types will be placed in the Add tables modal if you want to add them again later.

Note that Amazon AppSync uses the table names as type names, but you can rename them - for example, changing a plural table name like movies to the type name Movie. To rename a type in the Database tables section, click on the checkbox of the type you want to rename, then click on the pencil icon in the Type name column.

To preview the content of the schema based on your selections, choose Preview schema. Note that this schema cannot be empty, so you'll have to have at least one table converted to a type. Also, this schema cannot exceed 1 MB in size.
1. Under Service role, choose whether to create a new service role specifically for this import or use an existing role.
Choose Next.
Next, choose whether to create a read-only API (queries only) or an API for reading and writing data (with queries and mutations). The latter also supports real-time subscriptions triggered by mutations.
Choose Next.
Review your choices and then choose Create API. Amazon AppSync will create the API and attach resolvers to queries and mutations. The generated API is fully operational and can be extended as needed.

Using the introspection feature (API)

You can use the StartDataSourceIntrospection introspection API to discover models in your database programmatically. For more details on the command, see using the StartDataSourceIntrospection API.

To use StartDataSourceIntrospection, provide your Aurora cluster Amazon Resource Name (ARN), database name, and Amazon Secrets Manager secret ARN. The command starts the introspection process. You can retrieve the results with the GetDataSourceIntrospection command. You can specify whether the command should return the Storage Definition Language (SDL) string for the discovered models. This is useful for generating an SDL schema definition directly from the discovered models.

For example, if you have the following Data definition language (DDL) statement for a simple Todos table:


create table if not exists public.todos  
(  
id serial constraint todos_pk primary key,  
description text,  
due timestamp,  
"createdAt" timestamp default now()  
);

You start the introspection with the following.


aws appsync start-data-source-introspection \ 
  --rds-data-api-config resourceArn=<cluster-arn>,secretArn=<secret-arn>,databaseName=database

Next, use the GetDataSourceIntrospection command to retrieve the result.


aws appsync get-data-source-introspection \
  --introspection-id a1234567-8910-abcd-efgh-identifier \
  --include-models-sdl

This returns the following result.


{
    "introspectionId": "a1234567-8910-abcd-efgh-identifier",
    "introspectionStatus": "SUCCESS",
    "introspectionStatusDetail": null,
    "introspectionResult": {
        "models": [
            {
                "name": "todos",
                "fields": [
                    {
                        "name": "description",
                        "type": {
                            "kind": "Scalar",
                            "name": "String",
                            "type": null,
                            "values": null
                        },
                        "length": 0
                    },
                    {
                        "name": "due",
                        "type": {
                            "kind": "Scalar",
                            "name": "AWSDateTime",
                            "type": null,
                            "values": null
                        },
                        "length": 0
                    },
                    {
                        "name": "id",
                        "type": {
                            "kind": "NonNull",
                            "name": null,
                            "type": {
                                "kind": "Scalar",
                                "name": "Int",
                                "type": null,
                                "values": null
                            },
                            "values": null
                        },
                        "length": 0
                    },
                    {
                        "name": "createdAt",
                        "type": {
                            "kind": "Scalar",
                            "name": "AWSDateTime",
                            "type": null,
                            "values": null
                        },
                        "length": 0
                    }
                ],
                "primaryKey": {
                    "name": "PRIMARY_KEY",
                    "fields": [
                        "id"
                    ]
                },
                "indexes": [],
                "sdl": "type todos\n{\ndescription: String\n\ndue: AWSDateTime\n\nid: Int!\n\ncreatedAt: AW
SDateTime\n}\n"
            }
        ],
        "nextToken": null
    }
}

Javascript is disabled or is unavailable in your browser.

To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

Merged APIs

Building a client application