I have a Terraform that creates a AWS RDS, aurora mysql, is there a way to create a table on the DB

Question

I have a Terraform script that creates a AWS RDS, aurora mysql cluster

module "cluster" {
  source  = "terraform-aws-modules/rds-aurora/aws"

  name           = var.cluster_name
  master_username = var.master_username
  master_password = var.master_password
  create_random_password = false
  database_name = var.database_name
  engine         = var.engine
  engine_version = var.engine_version
  instance_class = var.instance_class_r5
  instances = {
    one = {}
    2 = {
      instance_class = var.instance_class_r5_2
    }
  }

  vpc_id  = var.vpc_id
  subnets = ["subnet-XXXX", "subnet-XXXX", "subnet-XXXX"]

  allowed_security_groups = ["sg-XXXXXXXXXXXXXX"]
  allowed_cidr_blocks     = ["10.20.0.0/20", "144.121.18.66/32"]

  storage_encrypted   = true
  apply_immediately   = true
  monitoring_interval = 10

  db_parameter_group_name         = aws_db_parameter_group.credential.id
  db_cluster_parameter_group_name = aws_rds_cluster_parameter_group.credential.id
  publicly_accessible = true

}

resource "aws_db_parameter_group" "credential" {
  name        = "${var.cluster_name}-aurora-db-57-parameter-group"
  family      = "aurora-mysql5.7"
  description = "${var.cluster_name}-aurora-db-57-parameter-group"
  tags        = var.tags_required
}

resource "aws_rds_cluster_parameter_group" "credential" {
  name        = "${var.cluster_name}-aurora-57-cluster-parameter-group"
  family      = "aurora-mysql5.7"
  description = "${var.cluster_name}-aurora-57-cluster-parameter-group"
  tags        = var.tags_required
}

This creates a database I am using springboot, and usually with a databse the entity will create the table

@Entity
@Table(name="credential")
public class CredentialEntity {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    Long credentialId;

In my yml file I have set

spring:
    hibernate:
      ddl-auto: update

But it does not create the table. So is there a way to create the table as part of the terraform script.

score 1 · Accepted Answer · answered Feb 08 '22 at 20:58

I wouldn't recommend doing this, but if you want Terraform to deploy database structures you can try with:

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.cluster.cluster_endpoint} --port=${module.cluster.cluster_port} --user=${module.cluster.cluster_master_username} --password=${module.cluster.cluster_master_password} --database=${module.cluster.cluster_database_name} < ${file(${path.module}/init/db_structure.sql)}"
  }
}

(This snippet is based on this answer where you have a lot more examples)

Just note: Terraform manages infrastructure. When AWS provider does its work you can have MySQL provider to pick up and deploy admin stuff like users, roles, grants, etc. But tables within databases belong to application. There are other tools more suited for managing database objects. See if you could plug Flyway or Liquibase into your pipeline.

Thank you yes Liquibase is a very good idea, – Tony Feb 08 '22 at 21:02 — Tony, Feb 08 '22 at 21:02

I have a Terraform that creates a AWS RDS, aurora mysql, is there a way to create a table on the DB

1 Answers1