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

Databases tend to be long-lived and require modifications to their schemas over time, so I'd caution against using Terraform to manage the schema unless you're confident it will change rarely or never. When changing database schema we tend to need to be explicit about how to migrate (in order to avoid data loss). That's not a good fit for Terraform's declarative approach, where we tell Terraform the result we want and let Terraform figure out how to make it happen. There are specialized tools tailored to this problem which I'd suggest using after letting Terraform create the empty database. — Martin Atkins
– Martin Atkins, Commented Jan 27, 2020 at 21:57
Project is in very early designing phase. I need that to setup basic environment with dummy data to provide some foundations for the app to work with ;) — Taz
– Taz, Commented Jan 31, 2020 at 14:18

DJAlPee · Accepted Answer · 2021-01-07 10:56:11Z

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

@DJAIPee can you show how would you do it via a bastion host?
@Shell_Leko I updated my config. It is not tested, but should give you a rough idea, how it's supposed to work.

Collectives™ on Stack Overflow

Create database schema with terraform

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related