AWS CloudFormation - Create Tables After RDS Instance Is Ready?

Question

CloudFormation amateur here. Been looking online and can't find any references as to how I would go about creating my tables after my RDS instance is stood up through CloudFormation. Is it possible to specify a Lambda to launch and create all the tables, or maybe specify a SQL file to be applied? What's the standard pattern on this?

Answer 1

There aren't any CloudFormation resources which deal with the 'internals' of an RDS instance once it's been created; it's a black box which you're expected to configure (with users, tables and data) outside of CloudFormation. This is a bit sad, as versioning, managing and deploying database schemas is a classic deployment problem on the borderline between infrastructure and application, which is also exactly where CloudFormation sits (as a tool for versioning, managing and deploying other infrastructure).

What you could do, although it's not a beginner-level feature, is use a custom resource to connect to the RDS instance once it's created, and run appropriate SQL commands to create the users and tables. You define the schema in your CloudFormation template (either as SQL or a more structured description similar to DynamoDB AtributeDefinitions), and that schema is passed to your custom resource lambda function, which then runs the queries in the database.

If you go down this route, you'll probably do a lot of wheel-inventing of how to translate differences in the 'before' and 'after' schemas into ALTER TABLE SQL statements to fire at the database. Your custom resource lambda code needs to be very robust and always send a response back to CloudFormation, even in the case of an error. And don't forget that if your stack update fails for any reason, CloudFormation will call your custom resource again 'in reverse' (ie asking you to alter the database from the post-update schema back to the pre-update schema) as part of the rollback process.

If you do go down this road and come up with something at all robust, do publish it, because I'm sure a lot of people would be interested in it! I had a quick look online but I couldn't find anything obvious pre-existing.

Answer 2

Unfortunately, CF template cannot run the setup script directly. You can try, like what you have mentioned, using Lambda to run the table set up once the RDS has been created successfully by the CF template. DependsOn attribute helps. You should pass the credentials to Lambda to access the RDS.