RDS

RDS is an AWS-managed SQL database that supports various engines and access modes. It eases the operational burden of running, monitoring, updating, and backing up databases. While it's complex to configure and has quite some edge cases, it's the preferred way to run an SQL-based database in the AWS cloud.

SQL allows complex queries, which is preferable when you don't have defined access patterns and you don't expect extreme load. It allows you to structure data in a logical way without worrying about query performance, an aspect that makes working with DynamoDB a challenge.

By default, RDS supports the traditional, connection-based way to send queries. This was designed with a few fixed servers sending queries to the database in mind, which is not the case in a serverless architecture. And since AppSync is serverless, it can not easily use this model.

Additionally, RDS supports the Data API. This is a connection-less way to run SQL statements that fits into the serverless model. This works by encoding all the information in all the requests sent to the API so there is no need to maintain a persistent connection to the database.

AppSync supports only the Data API, and in extension, only supports engines that support the Data API. At the time of writing, this included Aurora Serverless v1, but not Aurora Serverless v2.