How to apply SQL Scripts on RDS with Terraform

Like this solution, You can also avoid instance setup time/cost by using your own machine with local-exec IF your RDS database is publicly available and you have setup ingress to allow your machine to connect. Then, with credentials stored securely in your environment, you would just do something like:

resource "null_resource" "db_setup" {

  # runs after database and security group providing external access is created
  depends_on = ["aws_db_instance.your_database_instance", "aws_security_group.sg_allowing_external_access"]

    provisioner "local-exec" {
        command = "database connection command goes here"
        environment {
          # for instance, postgres would need the password here:
          PGPASSWORD = "${var.database_admin_password}"
        }
    }
}

Keep in mind that passwords and other sensitive variables can be input into terraform separately.

Building upon ecoe's answer:

For future readers using a Postgres RDS instance, this is what worked for me (you must have psql installed on your machine):

variable "db_username" {
  type = string
}

variable "db_password" {
  type = string
}

resource "null_resource" "db_setup" {

  provisioner "local-exec" {

    command = "psql -h host_name_here -p 5432 -U \"${var.db_username}\" -d database_name_here -f \"path-to-file-with-sql-commands\""

    environment = {
      PGPASSWORD = "${var.db_password}"
    }
  }
}

Elaborating on the previous answers with a MySQL version.

MySQL version with file

resource "null_resource" "db_setup" {
  depends_on = [module.db, aws_security_group.rds_main, aws_default_security_group.default]
  provisioner "local-exec" {
    command = "mysql --host=${module.db.this_db_instance_address} --port=${var.dbport} --user=${var.dbusername} --password=${var.dbpassword} --database=${var.dbname} < ${file(${path.module}/init/db_structure.sql)}"
  }
}

MySQL version with local_file

data "local_file" "sql_script" {
  filename = "${path.module}/init/db_structure.sql"
}

resource "null_resource" "db_setup" {
  depends_on = [module.db, aws_security_group.rds_main, aws_default_security_group.default]
  provisioner "local-exec" {
    command = "mysql --host=${module.db.this_db_instance_address} --port=${var.dbport} --user=${var.dbusername} --password=${var.dbpassword} --database=${var.dbname} < ${data.local_file.sql_script.content}"
  }
}

my database is in private subnets and the security groups allow only the bastion to connect.
I have an SQL file that creates the different users and schemas.
There can be better ways, but this works for my use case.

the SQL file has passwords which I pass through terraform variables -

data "template_file" "init_db" {
  template = file("./init-db.sql")

  vars = {
    account_service_db_password       = var.account_service_db_password
    account_service_db_admin_password = var.account_service_db_admin_password

    question_service_db_password       = var.question_service_db_password
    question_service_db_admin_password = var.question_service_db_admin_password
  }
}

now I have to copy the SQL file and then execute commands -

resource "aws_instance" "bastion" {
  # ...
  instance_type = "t2.micro"
  # ...

  connection {
    type        = "ssh"
    host        = self.public_ip
    user        = "ec2-user"
    private_key = file("./n_virginia.pem")
  }

  provisioner "file" {
    content     = data.template_file.init_db.rendered
    destination = "init-db.sql"
  }

  provisioner "remote-exec" {
    inline = [
      "sudo yum update -y",
      "sudo yum install mysql -y",
      "mysql -u ${var.db_root_username} -h ${aws_db_instance.main.address} -p${var.db_root_password} < init-db.sql"
    ]
  }
}