Google Cloud SQL Database using Terraform

Cloud SQL is a fully managed database service that makes it easy to set up, maintain, manage, and administer your relational databases on Google Cloud. Terraform is an Infrastructure as Code (IaC) tool that allows you to define and manage your infrastructure in a declarative way. By using Terraform to create Cloud SQL instances can help you streamline your infrastructure management and reduce the risk of errors.

Before you Begin

Create a project in the Google Cloud Console and set up billing on that project. Any examples in this guide will be part of the GCP “always free” tier.
Enable Cloud SQL API - In your Google Cloud accounts sidebar, you can select APIs & Services and then Dashboard to get an overview of enabled APIs.. You can skip the rest of this section if the list already includes Cloud SQL Admin API. If that’s not the case, click Enable APIs and services.
Create a service account with appropriate IAM permission to provision Cloud SQL - https://cloud.google.com/iam/docs/service-accounts-create
Activate service account https://cloud.google.com/sdk/gcloud/reference/auth/activate-service-account
In case, you would like to create Cloud SQL on a Shared VPC network, please refer to this page for information on how to set up a Service Project, a Shared VPC Network, and Firewall Rules with the necessary IAM permissions for your service account and user account.

https://cloud.google.com/kubernetes-engine/docs/how-to/cluster-shared-vpc

Implementation:

Terraform Tree Structure

terraform:
├── application
 └── databases 
     └── backend.tf
     └── data.tf
     └── database_users.tf
     └── database.tf
     └── locals.tf
     └── provider.tf
     └── secrets.tf
     └── variables.tf
     └── vars.tfvars

provider.tf
```
 terraform:
 ├── application
  └── databases 
      └── backend.tf
```
Create a Terraform configuration file to configure the required version of Terraform and the required providers to connect with Google Cloud

# Declare the connection to the google provider with Terraform required version
terraform {
  required_providers {
    google = {
      version = var.google_provider_version
    }
  }
  required_version = var.terraform_version
}

provider "google" {
  project = var.project_id
  region  = var.region
  impersonate_service_account = "<SERVICE_ACCOUNT_NAME>@<PROJECT_ID>.iam.gserviceaccount.com"
}

Note: This article does not cover how to use Google Cloud Service Account impersonation in your Terraform code. For the production use case, we recommend using a service account access token in the provider block.

Please Refer: https://cloud.google.com/blog/topics/developers-practitioners/using-google-cloud-service-account-impersonation-your-terraform-code

backend.tf

 terraform:
 ├── application
  └── databases 
      └── backend.tf

Using Google Cloud Storage bucket to store Terraform state file.

terraform {
  backend "gcs" {
    bucket = "sql-tfstate"
    prefix = "terraform/state/databases"
  }
}

Note*: Before executing above, please create GCS bucket "sql-state"*

resource "google_storage_bucket" "sql-state" {
  name          = "sql-state"
  location      = "<your-location>"
  force_destroy = true
}

variables.tf

terraform:
├── application
 └── databases 
     └── variables.tf

Define all variables

variable "project" {
  type        = string
  description = "The Google Cloud Platform (GCP) project within which resources are provisioned"
}

variable "region" {
  type        = string
  description = "The Google Cloud Platform (GCP) region in which to provision resources"
}

variable "host_network" {
  type        = string
  description = "The main shared-vpc network within `host_project_id`. This network must have a private services range created."
  default     = ""
}

variable "database_machine_type" {
  type        = string
  description = "The Google Cloud (GCP) compute engine machine type on which to host the database"
  default     = "db-n1-standard-32"
}

variable "deletion_protection" {
  type        = bool
  description = "Whether or not to allow terraform to destroy the instance.  See https://registry.terraform.io/providers/hashicorp/google/latest/docs/resources/sql_database_instance#deletion_protection"
  default     = false
}

variable backup_configuration {
  description = "The backup_configuration settings subblock for the database setings"
  type        = map
  default     = {enabled = true,binary_log_enabled = false,start_time = "00:30"}
}

variable maintenance_window {
  description = "The maintenance_window settings subblock"
  type        = map
  default     = {day = 7,hour = 4,update_track = "stable"}
}

variable ip_configuration {
  description = "The ip_configuration settings subblock"
  type        = map
  default     = {ipv4_enabled: false, require_ssl: false}
}

locals.tf and data.tf

locals.tf is a file where you can define your locals and data.tf is a file where you can define your data sources.

 terraform:
 ├── application
  └── databases 
      └── data.tf
      └── locals.tf

data.tf

// Query the shared VPC network within which the Cloud SQL database will be provisioned
data "google_compute_network" "host" {
  name    = var.host_network
}

locals.tf

locals {
  database_user   = "<DEFINE-DATABASE-USER>"
  host_network                 = "projects/<YOUR-SHARED_VPC_NETWORK_PROJECT_ID>/global/networks/<YOUR-SHARED-VPC-NETWORK-NAME>"
}

database.tf

Let's create Cloud SQL DB at your Google Cloud Service project

terraform:
├── application
 └── databases 
     └── database.tf

// Create a random id to append to the database instance name
resource "random_id" "random_id" {
  byte_length = 4
}

/******************************************
  Primary DB Instance Resource
 *****************************************/
resource "google_sql_database_instance" "sql_instance" {
  name                = "database-${random_id.random_id.hex}"
  project             = var.project
  database_version    = "POSTGRES_15"
  region              = var.region
  deletion_protection = var.deletion_protection

  settings {
    tier              = var.database_machine_type
    activation_policy = "ALWAYS"
    disk_autoresize   = true

    backup_configuration {
      enabled            = var.backup_configuration.enabled
      binary_log_enabled = var.backup_configuration.binary_log_enabled
      start_time         = var.backup_configuration.start_time
    }

    availability_type = "REGIONAL"

    ip_configuration {
      ipv4_enabled = var.ip_configuration.ipv4_enabled
      private_network = "projects/<YOUR-SHARED_VPC_NETWORK_PROJECT_ID>/global/networks/<YOUR-SHARED-VPC-NETWORK-NAME>"
      require_ssl = var.ip_configuration.require_ssl
    }

    maintenance_window {
      day          = var.maintenance_window.day
      hour         = var.maintenance_window.hour
      update_track = var.maintenance_window.update_track
    }
  }

  timeouts {
    create = "60m"
    delete = "60m"
  }
}

/******************************************
  CloudSQL DB Resource
 *****************************************/

resource "google_sql_database" "backend_database" {
  name      = "<DEFINE_DB_NAME>"
  instance  = google_sql_database_instance.sql_instance.name
  charset   = "UTF8"
  collation = "en_US.UTF8"
}

secrets.tf

terraform:
├── application
 └── databases 
     └── secrets.tf

// Generate a password for the database user
resource "random_password" "database_password" {
  length  = 8
  min_lower = 1
  min_upper = 1
  min_special = 1
  min_numeric = 1
  override_special = "*"
}

// The database configuration is read by your application as part of its startup requirements.
// This is stored in Google Secret Manager for later use by the your application compute engine startup script.
resource "google_secret_manager_secret" "database_configuration" {
  secret_id = "database-configuration"
  replication {
    automatic = true
  }
}

// Database configuration required by Looker.
resource "google_secret_manager_secret_version" "database_configuration" {
  secret = google_secret_manager_secret.database_configuration.name
  secret_data = <<EOF
${google_sql_user.database_user.password}
EOF
}

database_users.tf

terraform:
├── application
 └── databases 
     └── database_users.tf

/******************************************
  CloudSQL User Resource
 *****************************************/

// Allocate a database user required for the application.  See root README for commentary on the host parameter.
resource "google_sql_user" "database_user" {
  name     = local.database_user
  instance = google_sql_database_instance.sql_instance.name
  password = random_password.database_password.result
}

vars.tfvars

terraform:
├── application
 └── databases 
     └── vars.tfvars

project = "<PROJECT-NAME>"
region = "us-central1"
database_machine_type = "db-custom-32-122880"

Note*: We may add more variables in this file to further templatize this.*

Apply Terraform

# Initialize environment

### Step 1. Terraform init

Follow conventional terraform workflow to build this solution.
You will be prompted for required variables.
Alternatively, you may create a `vars.tfvars` file and 
apply the `-var-file=vars.tfvars` flag.

Initialize the terraform environment.

```
terraform init
```

### Step 2. IMPORTANT: Initialize workspace

Select or create a terraform workspace before running the configuration **IN THIS
FOLDER**. This ensures that the backend state will be saved according to the 
target environment. Recommended workspace names are `development`, `production` 
to clearly designate the target environment:

```
terraform workspace select development
```

or

```
terraform workspace new development
```

## Terraform plan

Plan the terraform solution.

```
terraform plan
```

or

```
terraform plan -var-file=vars.tfvars
```

## Terraform apply

Apply the terraform solution.

```
terraform apply
```

or

```
terraform apply -var-file=vars.tfvars
```

That's all, you should be able to provision Cloud SQL DB successfully after Terraform Apply!!

Cloud SQL is a good option for those who want to focus on their data and let Cloud SQL handle the database administration tasks. It is also a scalable and flexible solution that supports multiple database options and is easy to set up.

Google Cloud SQL Database using Terraform

Before you Begin

Implementation:

provider.tf

backend.tf

variables.tf

locals.tf and data.tf

database.tf

secrets.tf

database_users.tf

Apply Terraform