Create database schema with terraform

Question

I created RDS instance using aws_db_instance (main.tf):

resource "aws_db_instance" "default" {
    identifier           = "${module.config.database["db_inst_name"]}"
    allocated_storage    = 20
    storage_type         = "gp2"
    engine               = "mysql"
    engine_version       = "5.7"
    instance_class       = "db.t3.micro"
    name                 = "${module.config.database["db_name_prefix"]}${terraform.workspace}"
    username             = "${module.config.database["db_username"]}"
    password             = "${module.config.database["db_password"]}"
    parameter_group_name = "default.mysql5.7"
    skip_final_snapshot  = true
}

Can I also create database schemas from file schema.sql with terraform apply?

$ tree -L 1                                                                                               
.
├── main.tf
└── schema.sql

Answer 1

You can use a provisioner (https://www.terraform.io/docs/provisioners/index.html) for that:

resource "aws_db_instance" "default" {
  identifier           = module.config.database["db_inst_name"]
  allocated_storage    = 20
  storage_type         = "gp2"
  engine               = "mysql"
  engine_version       = "5.7"
  instance_class       = "db.t3.micro"
  name                 = "${module.config.database["db_name_prefix"]}${terraform.workspace}"
  username             = module.config.database["db_username"]
  password             = module.config.database["db_password"]
  parameter_group_name = "default.mysql5.7"
  skip_final_snapshot  = true

  provisioner "local-exec" {
    command = "mysql --host=${self.address} --port=${self.port} --user=${self.username} --password=${self.password} < ./schema.sql"
    }
  }

#Apply scheme by using bastion host
resource "aws_db_instance" "default_bastion" {
  identifier           = module.config.database["db_inst_name"]
  allocated_storage    = 20
  storage_type         = "gp2"
  engine               = "mysql"
  engine_version       = "5.7"
  instance_class       = "db.t3.micro"
  name                 = "${module.config.database["db_name_prefix"]}${terraform.workspace}"
  username             = module.config.database["db_username"]
  password             = module.config.database["db_password"]
  parameter_group_name = "default.mysql5.7"
  skip_final_snapshot  = true

  provisioner "file" {
      connection {
      user        = "ec2-user"
      host        = "bastion.example.com"
      private_key = file("~/.ssh/ec2_cert.pem")
    }

    source      = "./schema.sql"
    destination = "~"
  }

  provisioner "remote-exec" {
    connection {
      user        = "ec2-user"
      host        = "bastion.example.com"
      private_key = file("~/.ssh/ec2_cert.pem")
    }

    command = "mysql --host=${self.address} --port=${self.port} --user=${self.username} --password=${self.password} < ~/schema.sql"
  }
}

mysql client needs to be installed on your device.

If you don't have direct access to your DB, there is also a remote-exec provisioner, where you can use a bastion host (transfer file to remote place with file provisioner first).

If your schema is not to complex, you could also use the MySQL provider of terraform: https://www.terraform.io/docs/providers/mysql/index.html