How to use Terraform to execute SQL script on RDS MySQL?

Question

I created the aws_db_instance to provision the RDS MySQL database using Terraform configuration. Now my next question is to execute the SQL Script (CREATE TABLE and INSERT statements) on the RDS. I did the following but there is no effect. terraform plan cannot even see my changes on executing the sql. What did I miss here? Thanks.

resource "aws_db_instance" "mydb" {
  # ...

  provisioner "remote-exec" {
    inline = [
      "chmod +x script.sql",
      "script.sql args",
    ]
  }
}

Answer 1

Check out this post: How to apply SQL Scripts on RDS with Terraform

---

If you're just trying to setup user's and permissions (you shouldn't use the root pw you set when you generate the RDS) there is a terraform provider for that:

https://www.terraform.io/docs/providers/mysql/index.html

---

But you're looking for DB schema and seeding. That provider cannot do that.

If you're open to doing it another way, you may want to check out using ssm automation documents and/or lambda. I'd use lambda. Pick a language that you're comfortable with. Set the role of the lambda to have permissions to read the password it needs to do the work. You can save the password in ssm parameter store. Then script your DB work.

Then do a local exec in terraform that simply calls the lambda and pass it the ID of the RDS and the path to the secret in ssm parameter store. That will ensure that the DB operations are done from compute inside the VPC without having to setup an EC2 bastion just for that purpose.

Here's how javascript can get this done, for example: https://www.w3schools.com/nodejs/nodejs_mysql_create_table.asp