6

I created the aws_db_instance to provision the RDS MySQL database using Terraform configuration. I need to execute the SQL Scripts (CREATE TABLE and INSERT statements) on the RDS. I'm stuck on what command to use here?? Anyone has the sample code in my use case? Please advise. Thanks.

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

    provisioner "local-exec" {
      command = "command to execute script.sql"

    }
}
Jack
  • 451
  • 2
  • 6
  • 5

2 Answers2

6

This is possible using a null_resource that depends on the aws_db_instance.my_db. This way the host is available when you run the command, it will only work if there aren't rules preventing you from accessing the DB such as security group ingress or not publicly accessible.

Example:

resource "null_resource" "setup_db" {
  depends_on = ["aws_db_instance.my_db"] #wait for the db to be ready
  provisioner "local-exec" {
    command = "mysql -u ${aws_db_instance.my_db.username} -p${var.my_db_password} -h ${aws_db_instance.my_db.address} < file.sql"
  }
}
Stephen
  • 3,607
  • 1
  • 27
  • 30
0

I don't believe you can use a provisioner with that type of resource. One option you could explore is having an additional step that takes the address of the RDS instance from a Terraform output and runs the SQL script.

So, for instance in a CI environment, you'd have Create Database -> Load Database -> Finished.

Below would be you Terraform to create and output the resource address. 

resource "aws_db_instance" "mydb" {
  # ...
    provisioner "local-exec" {
      command = "command to execute script.sql"
    }
}

output "username" {
    value = "${aws_db_instance.mydb.username}"
}

output "address" {
    value = "${aws_db_instance.mydb.address}"
}

The Load Database step would then run a shell script with the SQL logic and the following to obtain the address of the instance - terraform output address

Nathan Smith
  • 8,271
  • 3
  • 27
  • 44
  • Hi Nate, can you help me understand where did i create the step on `create database -> load database` step? Yes I don't know if I can create the database and immediately execute the SQL in terraform – Jack Apr 04 '18 at 02:56
  • I guess the output `address` is the database instance URL, and then still need to do the database connection with credentials and then execute the SQL scripts – Jack Apr 04 '18 at 02:57
  • @Jack so I'm guessing you have specified the password somewhere unless you're setting up replication or from a snapshot. So you will be able to wrap the create and load into a `bash` script or something like that. – Nathan Smith Apr 04 '18 at 10:03