RDS module with multiple DBs

This message is extracted from a ticket originally emailed to support@gruntwork.io. Names and URLs have been removed where appropriate.

We have developed a new service that requires a database. We were thinking about adding a database to the existing RDS instance. However I browsed your module and having multiple db does not seem to be supported. Are you planning on supporting such an endeavour? What would you advise?

Unfortunately, the underlying AWS APIs (and therefore Terraform) only allow specifying up to 1 (logical) database per instance. The only way I know of to create multiple databases is to use the MySQL/Postgres cli clients.

One could imagine setting up the management of multiple dbs on an instance by creating your RDS instance to have just a ‘management’ database, to which you apply arbitrary sql statements, including those to create databases, roles, and other ‘global’ values like stored proc languages (in postgres, anyway. Things are similar but not identical in mysql) via any one of numerous systems out there for automatically applying database migrations - flyway, liquibase, etc. Line up migrations which create the actual databases you want on the instance (and role hierarchy), and then just do something in terraform to apply the latest migrations.

I’m kind of new to terraform, so I don’t have a suggestion for doing that (if there isn’t a migration tool that is already supported as a provider in terraform), but if you put the content of the migrations script in the parameters of a resource which runs the migration tool, whenever the script is changed, terraform would have to re-run the migration tool. Or just run it every time there’s an update, since the migration tool will only apply migrations that haven’t already been applied to the db - the state about previously applied migrations is stored in tables in the management db that is the original tenant of the RDS instance.

It wouldn’t be hard to plug into the Gruntwork-installer infrastructure in order to launch an EC2 instance inside your VPC which would pull the migrations from github via gruntwork-installer and run them, if that’s a necessary step for network access to your RDS instance. Or write a lambda which fetches migrations from github or an S3 bucket when triggered and then trigger it as needed - after writing a new migrations script to the S3 bucket or adding a tag to github via terraform. There are lots of options for managing it, I suspect.

1 Like

We use the pattern described by @sam use terragrunt/form to provision/manage the ‘main’ aurora rds db and Flyway for db migrations across ~8 databases within that aurora instance/cluster (schemas as sometimes described in the mysql world). And flyway is built into our microservice startup scripts (run-app.sh) for each service/db schema. When a new db/schema comes along, the flyway and run-app.sh conventions we use, create and migrate the new db/schema.

1 Like