Create RDS schema when RDS cluster is part of a AWS Codepipeline

Question

I'm building a system that has a web service(AWS API Gateway + AWS lambda + AWS RDS Aurora MySQL) fully integrated with a CI/CD pipeline(AWS CodePipeline) integrated with a Git WebHook. So, I have a template that provides the gateway, the lambda and the RDS cluster. Additionally, I have a custom resource in my template that creates the database and the tables( not ingesting data for now).

Regarding the architecture previously mentioned, here I have a couple of questions:

1. In this scenario, is a custom resource for creating the schema the best approach according to standards?
2. Regarding data ingestion and schema updates, is it a good practice to manage this within the pipeline, or is it better to do it outside(running incremental scripts manually)?. In case you manage schema changes within the pipeline process... how do you achieve that?

Thanks

Answer 1

For creating the initial schema at this time the best choice is as you said using a custom resource.

Regarding data ingestion/schema updates if you're using version control for managing then having some kind of pipeline is definitely the correct way to go, however, where the difficulties lie are in a rollback scenario (especially with data manipulation).

You could either use a pure Lambda action within CodePipeline (including functionality to test and rollback your changes) or you could integrate the Lambda function with a third party solution for managing rolling updates to your SQL schema.