Connect API to existing MySQL or PostgreSQL database

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

Connect your API with an existing MySQL or PostgreSQL database

npm install @aws-amplify/graphql-api-construct

typePost{
id:Int!
title:String!
content:String!
published:Boolean
publishedDate:AWSDate@refersTo(name:"published_date")
}
typeQuery{
searchPosts(contains:String!):[Post]
@sql(
statement:"SELECT * FROM posts WHERE title LIKE CONCAT('%', :contains, '%');"
)
@auth(rules:[{allow:public}])
}
typeMutation{
createPost(title:String!content:String!):AWSJSON
@sql(statement:"INSERT INTO posts (title, content) VALUES (:title, :content);")
@auth(rules:[{allow:public}])
}

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

newAmplifyGraphqlApi(stack,'SqlBoundApi',{
  apiName:'MySqlBoundApi',
  definition: AmplifyGraphqlDefinition.fromFilesAndStrategy(
[path.join(__dirname,'schema.sql.graphql')],
{
      name:'MySQLSchemaDefinition',
      dbType:'MYSQL',
      vpcConfiguration:{
        vpcId:'vpc-123456',
        securityGroupIds:['sg-123','sg-456'],
        subnetAvailabilityZoneConfig:[
{ subnetId:'sn-123456', availabilityZone:'us-east-1a'},
{ subnetId:'sn-987654', availabilityZone:'us-east-1b'}
]
},
      dbConnectionConfig:{
        hostnameSsmPath:
'/path/to/ssm/SecureString/containing/value/of/hostname',
        portSsmPath:'/path/to/ssm/SecureString/containing/value/of/port',
        usernameSsmPath:
'/path/to/ssm/SecureString/containing/value/of/username',
        passwordSsmPath:
'/path/to/ssm/SecureString/containing/value/of/password',
        databaseNameSsmPath:
'/path/to/ssm/SecureString/containing/value/of/databaseName'
}
}
),
  authorizationModes:{
    defaultAuthorizationMode:'API_KEY',
    apiKeyConfig:{
      expires: cdk.Duration.days(30)
}
}
});

newAmplifyGraphqlApi(this,'SqlBoundApi',{
  apiName:'MySqlBoundApi',
  definition: AmplifyGraphqlDefinition.fromFilesAndStrategy(
[path.join(__dirname,'schema.sql.graphql')],
{
      name:'MySQLSchemaDefinition',
      dbType:'MYSQL',
      vpcConfiguration:{
        vpcId:'vpc-123456',
        securityGroupIds:['sg-123','sg-456'],
        subnetAvailabilityZoneConfig:[
{ subnetId:'sn-123456', availabilityZone:'us-east-1a'},
{ subnetId:'sn-987654', availabilityZone:'us-east-1b'},
],
},
      dbConnectionConfig:{
        databaseName:'database',
        port:3306,
        hostname:'database-1-instance-1.id.region.rds.amazonaws.com',
        secretArn:
'arn:aws:secretsmanager:Region1:123456789012:secret:MySecret-a1b2c3',
},
}
),
  authorizationModes:{
    defaultAuthorizationMode:'API_KEY',
    apiKeyConfig:{
      expires: cdk.Duration.days(30),
},
},
});

Create custom queries and mutations

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.

Inline SQL Statement

For getting started, you can embed the SQL statement directly in the schema using thestatement 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 asearchTerm input variable.

typeQuery{
searchPosts(searchTerm:String):[Post]
@sql(statement:"SELECT * FROM posts WHERE title LIKE :searchTerm;")
@auth(rules:[{allow:public}])
}

SQL File Reference

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.

typeQuery{
getPublishedPosts(start:AWSDate,end:AWSDate):[Post]
@sql(reference:"getPublishedPostsByDateRange")
@auth(rules:[{allow:public}])
}

-- lib/sql-statements/getPublishedPostsByDateRange.sql
SELECT p.id, p.title, p.content, p.published_date
FROM posts p
WHERE p.published=1
AND p.published_date> :startDate
AND p.published_date< :endDate
ORDERBY p.published_dateDESC
LIMIT10

-- lib/sql-statements/getPostById.sql
SELECT*FROM postsWHERE id= :id;

import{SQLLambdaModelDataSourceStrategyFactory}from'@aws-amplify/graphql-api-construct';
importpathfrom'path';
importfsfrom'fs';

// Define custom SQL statements folder path
const sqlStatementsPath= path.join(__dirname,'sql-statements');
// Use the Factory to define the SQL data source strategy
const sqlStrategy=SQLLambdaModelDataSourceStrategyFactory.fromCustomSqlFiles(
// File paths to all SQL statements
  fs
.readdirSync(sqlStatementsPath)
.map((file)=> path.join(sqlStatementsPath, file)),
// Move your connection information and VPC config into here
{
dbType:'MYSQL',
name:'MySQLSchemaDefinition',
dbConnectionConfig:{
//...
},
vpcConfiguration:{
//...
}
}
);
const amplifyApi=newAmplifyGraphqlApi(this,'SqlBoundApi',{
definition:AmplifyGraphqlDefinition.fromFilesAndStrategy(
[path.join(__dirname,'schema.sql.graphql')],
    sqlStrategy
),
authorizationModes:{
defaultAuthorizationMode:'API_KEY',
apiKeyConfig:{
expires: cdk.Duration.days(30)
}
}
});

Custom Query

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

typeQuery{
searchPostsByTitle(title:String):[Post]
@sql(
statement:"SELECT * FROM posts WHERE title LIKE CONCAT('%', :title, '%');"
)
@auth(rules:[{allow:public}])
}

Custom Mutation

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

typeMutation{
publishPostById(id:ID!):AWSJSON
@sql(statement:"UPDATE posts SET published = :published WHERE id = :id;")
@auth(rules:[{allow:public}])
}

Returning row data from custom mutations

SQL statements such asINSERT,UPDATE andDELETE return the number of rows affected.

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

typeMutation{
publishPosts:AWSJSON@sql(statement:"UPDATE posts SET published = 1;")
@auth(rules:[{allow:public}])
}

This will return a JSON response similar to this:

{
  "data": {
    "publishPosts": "{\"fieldCount\":0,\"affectedRows\":7,\"insertId\":0,\"info\":\"Rows matched: 7  Changed: 7  Warnings: 0\",\"serverStatus\":34,\"warningStatus\":0,\"changedRows\":7}"
  }
}

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

CREATEPROCEDURE publish_post(IN postIdVARCHAR(255))
BEGIN
UPDATE postsSET published=1WHERE id= postId;
SELECT*FROM postsWHERE id= postIdLIMIT1;
END

typeMutation{
publishPostById(id:String!):[Post]
@sql(statement:"CALL publish_post(:id);")
@auth(rules:[{allow:public}])
}

typeMutation{
publishPostById(id:String!):[Post]
@sql(statement:"UPDATE posts SET price = :id RETURNING *;")
@auth(rules:[{allow:public}])
}

typeQuery{
getPostBySlug(slug:String!):[Post]
@sql(statement:"SELECT * FROM posts WHERE slug = :slug LIMIT 1;")
@auth(rules:[{allow:public}])
}

Apply authorization rules

Model level authorization rules

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 thePost model within theschema.sql.graphql file:

typePost@model@refersTo(name:"posts")@auth(rules:[{allow:public}]){
id:String!@primaryKey
title:String!
content:String!
}

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

Field-level 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 thepublished field.

typePost
@model
@refersTo(name:"posts")
@auth(rules:[
{allow:public,operations:[read]},
{allow:owner}
]){
id:String!@primaryKey
title:String!
content:String!
published:Boolean
Copy highlighted code example
@auth(rules:[{allow:owner}])
}

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

Deploy your API

cdk deploy

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!

Auto-generate CRUDL operations for existing tables

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 aIngredients table in your database:

CREATETABLE Ingredients(
  idvarchar(255)NOTNULLPRIMARYKEY,
  namevarchar(255)NOTNULL,
  unit_of_measurementvarchar(255)NOTNULL,
  pricedecimal(10,2)NOTNULL,
  supplier_idint,
);

Step 1 - Export database schema as CSV

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

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

SELECTDISTINCT
  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
LEFTJOIN INFORMATION_SCHEMA.STATISTICSON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME=INFORMATION_SCHEMA.STATISTICS.TABLE_NAMEAND INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME=INFORMATION_SCHEMA.STATISTICS.COLUMN_NAME
WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA='<database-name>';
--                    Replace database name here ^^^^^^^^^^^^^^^

TABLE_NAME,COLUMN_NAME,COLUMN_DEFAULT,ORDINAL_POSITION,DATA_TYPE,COLUMN_TYPE,IS_NULLABLE,CHARACTER_MAXIMUM_LENGTH,INDEX_NAME,NON_UNIQUE,SEQ_IN_INDEX,NULLABLE
Ingredients,id,,1,int,int,NO,,PRIMARY,0,1,""
Ingredients,name,,2,varchar,varchar(100),NO,100,,,,
Ingredients,unit_of_measurement,,3,varchar,varchar(50),NO,50,,,,
Ingredients,price,,4,decimal,"decimal(10,2)",NO,,,,,
Ingredients,supplier_id,,6,int,int,YES,,,,,
Meals,id,,1,int,int,NO,,PRIMARY,0,1,""

SELECTDISTINCT
  INFORMATION_SCHEMA.COLUMNS.table_name,
  enum_name,enum_values,column_name,column_default,ordinal_position,data_type,udt_name,is_nullable,character_maximum_length,indexname,constraint_type,
REPLACE(SUBSTRING(indexdeffrom'\((.*)\)'),'"','')as index_columns
FROM INFORMATION_SCHEMA.COLUMNS
LEFTJOIN pg_indexes
ON
  INFORMATION_SCHEMA.COLUMNS.table_name= pg_indexes.tablename
AND INFORMATION_SCHEMA.COLUMNS.column_name=ANY(STRING_TO_ARRAY(REPLACE(SUBSTRING(indexdeffrom'\((.*)\)'),'"',''),', '))
LEFTJOIN(
SELECT
      t.typnameAS enum_name,
      ARRAY_AGG(e.enumlabel)as enum_values
FROM    pg_type tJOIN
      pg_enum eON t.oid= e.enumtypidJOIN
      pg_catalog.pg_namespace nON n.oid= t.typnamespace
WHERE   n.nspname='public'
GROUPBY enum_name
) enums
ON enums.enum_name= INFORMATION_SCHEMA.COLUMNS.udt_name
LEFTJOIN information_schema.table_constraints
ON INFORMATION_SCHEMA.table_constraints.constraint_name= indexname
AND INFORMATION_SCHEMA.COLUMNS.table_name= INFORMATION_SCHEMA.table_constraints.table_name
WHERE INFORMATION_SCHEMA.COLUMNS.table_schema='public'
AND INFORMATION_SCHEMA.COLUMNS.TABLE_CATALOG='<database-name>';
--                     Replace database name here ^^^^^^^^^^^^^^^

"table_name","enum_name","enum_values","column_name","column_default","ordinal_position","data_type","udt_name","is_nullable","character_maximum_length","indexname","constraint_type","index_columns"
"Ingredients","","","id","","1","bigint","int8","NO","","Ingredients_pkey","PRIMARY KEY","id"
"Ingredients","","","name","","2","text","text","NO","","","",""
"Ingredients","","","unit_of_measurement","","3","text","text","NO","","","",""
"Ingredients","","","price","","4","text","text","NO","","","",""
"Ingredients","","","supplier_id","","5","bigint","int8","NO","","","",""

Step 2 - Generate GraphQL schema from database schema

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

npx @aws-amplify/cli api generate-schema --engine-type mysql --sql-schema schema.csv --out schema.sql.graphql

Next, update the first argument ofAmplifyGraphqlDefinition.fromFilesAndStrategy to include theschema.sql.graphql file generated in the previous step:

newAmplifyGraphqlApi(stack,'SqlBoundApi',{
  apiName:'MySqlBoundApi',
  definition: AmplifyGraphqlDefinition.fromFilesAndStrategy(
[path.join(__dirname,'schema.sql.graphql')],// file path
{
// ...strategy options
}
)
});

Step 3 - Apply authorization rules for your generated GraphQL API

Open yourschema.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 adeny-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.

inputAMPLIFY{
engine:String="mysql"
}
typeIngredient@refersTo(name:"Ingredients")@model{
id:Int!@refersTo(name:"ingredient_id")@primaryKey
name:String!
unitOfMeasurement:String!@refersTo(name:"unit_of_measurement")
price:Float!
supplierId:Int@refersTo(name:"supplier_id")
}

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. ReviewCustomize authorization rules to see the full scope of model-level authorization capabilities.

input AMPLIFY {
engine: String = "mysql"
}
- type Ingredient @refersTo(name: "Ingredients") @model {
+ type Ingredient
+   @refersTo(name: "Ingredients")
+   @model
+   @auth(rules: [{ allow: public }]) {
 id: Int! @refersTo(name: "ingredient_id") @primaryKey
 name: String!
 unitOfMeasurement: String! @refersTo(name: "unit_of_measurement")
 price: Float!
 supplierId: Int @refersTo(name: "supplier_id")
}

Finally, remember to deploy your API to the cloud:

cdk deploy

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

Rename & map models to tables

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, theisPublished is now mapped to thepublished column on the posts table.

typePost@refersTo(name:"posts")@model{
id:String!@primaryKey
title:String!
content:String!
isPublished:Boolean@refersTo(name:"published")
publishedDate:AWSDate@refersTo(name:"published_date")
}

Create relationships between models

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

Assume that you haveusers,blogs, andposts 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.

Has One relationship

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.

typeUser
@refersTo(name:"users")
@model
@auth(rules:[{allow:owner},{allow:groups,groups:["Admin"]}]){
id:String!@primaryKey
name:String!
owner:String
blog:Blog@hasOne(references:["userId"])
}

Has Many relationship

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

In the example below, a Blog has many Posts.

typeBlog@model{
id:String!@primaryKey
title:String!
posts:[Post]@hasMany(references:["blogId"])
}
typePost@model{
id:String!@primaryKey
title:String!
content:String!
blogId:String!@refersTo(name:"blog_id")
}

Belongs To relationship

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

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

typePost@model{
id:String!@primaryKey
title:String!
content:String!
blogId:String!@refersTo(name:"blog_id")
blog:Blog@belongsTo(references:["blogId"])
}

Apply iterative changes from the database definition

CREATETABLE posts(
  idvarchar(255)NOTNULLPRIMARYKEY,
  titlevarchar(255)NOTNULL,
  contentvarchar(255)NOTNULL,
  publishedtinyint(1)DEFAULT0NOTNULL
  published_datedateNULL
);

npx @aws-amplify/cli api generate-schema --engine-type mysql --sql-schema schema.csv --out schema.sql.graphql

cdk deploy

How does it work?

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:

SQL Lambda

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

Updater Lambda

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.

Mapping of SQL data types to GraphQL types when auto-generating GraphQL schema

Supported Amplify directives for auto-generated GraphQL schema

Troubleshooting

Debug Mode

To return the actual SQL error instead of a generic error from GraphQL responses, an environment variableDEBUG_MODE can be set totrue 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>.

Next steps

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