Three ways to create Cloud SQL on GCP(Console,gcloud,Terraform)

1 Introduction

Cloud SQL helps to manage RDB on GCP for us. There are 3 ways to create the database:

(1) Create on Console

(2) Create with gcloud

(3) Create with Terraform

For GCP initiation, can go to: How to initiate the GCP project and use gcloud to access (opens new window).

2 GCP Console

Login to the GCP console and go to SQL to create the Cloud SQL. You can choose MySQL, PostgreSQL and SQL Server.

For PostgreSQL, you can speicify the paramters as below:

3 gcloud

We can create Cloud SQL with Google SDK(gcloud) with the command:

$ gcloud sql instances create pkslow-testdb \
--database-version=POSTGRES_13 \
--region=us-west1 \
--cpu=1 \
--memory=3840MB \
--authorized-networks="0.0.0.0/0" 

Creating Cloud SQL instance...done.                                                                                                                             
Created [https://sqladmin.googleapis.com/sql/v1beta4/projects/pkslow/instances/pkslow-testdb].
NAME           DATABASE_VERSION  LOCATION    TIER              PRIMARY_ADDRESS  PRIVATE_ADDRESS  STATUS
pkslow-testdb  POSTGRES_13       us-west1-a  db-custom-1-3840  34.83.146.214    -                RUNNABLE

authorized-networks is important to authorize who can access the database.

Set the password for postgres:

$ gcloud sql users set-password postgres \
--instance=pkslow-testdb \
--password=pkpass

Create the database with the instance:

$ gcloud sql databases create billing --instance=pkslow-testdb

Creating Cloud SQL database...done.                                                                                                                             
Created database [billing].
instance: pkslow-testdb
name: billing
project: pkslow

Connect to the database on local laptop:

After connected, we can create the table and insert some data into the db:

CREATE TABLE pkslow_person (
                               name varchar not null,
                                 age int not null,
                                 city varchar not null
);

insert into pkslow_person(name, age, city) VALUES ('Larry', 1, 'GZ');
insert into pkslow_person(name, age, city) VALUES ('pkslow', 2, 'SZ');
insert into pkslow_person(name, age, city) VALUES ('Deng', 21, 'SZ');
insert into pkslow_person(name, age, city) VALUES ('Eason', 13, 'BJ');
insert into pkslow_person(name, age, city) VALUES ('JJ', 43, 'SH');
insert into pkslow_person(name, age, city) VALUES ('Mi', 28, 'HK');

Check the data:

4 Terraform

We can create the database with Terraform:

provider "google" {
  project     = "pkslow"
}

resource "google_sql_database_instance" "instance" {
  name             = "pkslow-postgresql"
  region           = "us-west1"
  database_version = "POSTGRES_13"
  settings {
    tier = "db-f1-micro"

    ip_configuration {
          ipv4_enabled = "true"

          authorized_networks {
            value           = "0.0.0.0/0"
            name            = "allow-all"
            expiration_time = "2099-01-01T00:00:00.000Z"
          }

        }
  }

  deletion_protection  = "true"
}

resource "google_sql_database" "database" {
  name     = "billing"
  instance = google_sql_database_instance.instance.name
}

resource "google_sql_user" "users" {
  name     = "larry"
  instance = google_sql_database_instance.instance.name
  password = "larry.pass"
}

This will create 3 resources for us: Instance, Database and User.

Run the commands to make it happen:

$ terraform init -plugin-dir=${TERRAFORM_PLUGIN}

$ terraform plan

$ terraform apply -auto-approve

The Terraform will create the Bucket to manage the Terraform state:

After the commands completed, check the instances:

$ gcloud sql instances list
NAME               DATABASE_VERSION  LOCATION    TIER              PRIMARY_ADDRESS  PRIVATE_ADDRESS  STATUS
pkslow-postgresql  POSTGRES_13       us-west1-a  db-f1-micro       34.127.53.161    -                RUNNABLE
pkslow-testdb      POSTGRES_13       us-west1-a  db-custom-1-3840  34.83.146.214    -                RUNNABLE

Connected to database on local:

5 code

Code on GitHub: https://github.com/LarryDpk/pkslow-samples

---

References:

GCP Create PostgreSQL instance (opens new window)

Terraform GCP Cloudsql (opens new window)