Google Cloud SQL — 6 ways (Golang)

I spent some time yesterday exploring ways to connect from Golang to Google Cloud SQL using the Cloud SQL Proxy. This post summarizes 6 ways to connect Golang code to Cloud SQL:

• 2 databases (MySQL and PostgreSQL);
• Using Cloud SQL Proxy as a library and as a companion (sidecar) process
• Accessing the Proxy using UNIX sockets and using TCP sockets

I recommend you always default to use the Proxy. It removes security hassles and removes the need to expose Cloud SQL database instances to the Internet. The Proxy is customarily run as a “companion process” and accessed from your applications via UNIX or TCP sockets. Because the Proxy is written in Golang, if you’re writing Go code, you can also reference the Proxy as a library directly in your code.

Create Databases

The tutorial assumes you have a working knowledge of Google Cloud Platform (GCP), a Google account and that you’ve got billing setup. Unfortunately the free tier does not include Cloud SQL.

For convenience, I’m going to assume that you have the following constants. When you see these occur in the instructions below, please replace them with your actual values:

PROJECT=gcp-project-id
REGION=us-west1
ROOT=instance
INSTANCE=${PROJECT}:${REGION}:${ROOT}
DBNAME=golang
DBUSER=root
DBPASS=henry

Please ensure you enable the (Cloud) SQL Admin API in your project. Either, from the command-line:

https://console.cloud.google.com/apis/api/sqladmin.googleapis.com/overview?project=${PROJECT}

Or via the Cloud SDK:

gcloud service-management enable sqladmin --project=${PROJECT}

Create a MySQL database instance and a PostgreSQL database instance. You can do this either from the Cloud Console or the Cloud SDK. Here are the Cloud SDK commands for MySQL:

gcloud sql instances create ${ROOT}-m \
--tier=db-g1-small \
--region=${REGION} \
--project=${PROJECT}
gcloud sql users set-password root % \
--instance=${ROOT}-m \
--password=${PASSWORD} \
--project=$PROJECT

and for PostgreSQL:

gcloud sql instances create ${ROOT}-p \
--cpu=1 \
--memory=4192MiB \
--database-version=POSTGRES_9_6 \
--project=${PROJECT}
gcloud sql users set-password postgres no-host \
--instance=${ROOT}-p \
--password=${PASSWORD} \
--project=${PROJECT}

You may use the default databases created with each of the above flavors but, for consistency, I recommend you create a new database in each server called $DBNAME. The Cloud SQL tools do *not* fully support database management as this is database-specific and is functionality well-served by the databases’ own tools. However, you are able to create databases and create users using the Google Cloud Console. Here are the URLs — you’ll need to replace the ${INSTANCE}, ${PROJECT} and the correct “m” for MySQL and “p” for PostgreSQL:

https://console.cloud.google.com/sql/instances/${INSTANCE}-[m|p]/databases?project=${PROJECT}
https://console.cloud.google.com/sql/instances/${INSTANCE}-[m|p]/users?project=${PROJECT}

That’s it!

Install the Cloud SQL Proxy

https://cloud.google.com/sql/docs/mysql/sql-proxy

In order to use the Proxy via UNIX sockets, ensure that you also:

sudo mkdir /cloudsql && sudo chmod 777 /cloudsql

You may use a location other than /cloudsql but, if you do, be careful to ensure you correctly replaces the references to this directory in the following instructions.

Application Default Credentials

Application Default Credentials (ADCs) provide a very useful mechanism for authorization. Using ADCs you can run code unchanged on a local workstation, on Google App Engine, Compute Engine etc.

The following examples assume you are running the Golang code and the proxy on your local workstation and that you have authenticated using ADCs. To authenticate using ADCs, issue the following command and accept the “Google Auth Library” prompt:

gcloud auth application-default login

Golang

The “6 ways” use the following boilerplate Golang code. This code is based on a Spring example “Accessing Relational Data using JDBC with Spring”. More about that in the next post!

You may either create 6 copies of this or — as I’ve been doing — you can comment in/out appropriate sections. You will need to correct the imports, add the correct dns/db lines and correct the Prepared Statement for each way:

package main
import (
 "database/sql"
 "fmt"
 "log"
 "strings"
 ... [Additional imports desribed below]
)
const (
 instanceConnection = ... [described below]
 databaseName = "${DBNAME}"
 user     = ${DBUSER}
 password = ${DBPASS}
)
func main() {
 dsn := ...[dsn described below]
 db, err := ...[db described below]
 if err != nil {
  log.Fatal(err)
 }
 defer db.Close()
 err = db.Ping()
 if err != nil {
  log.Fatal(err)
 }

 _, err = db.Exec(fmt.Sprintf("USE %s", databaseName))
 if err != nil {
  log.Fatal(err)
 }

 _, err = db.Exec("DROP TABLE IF EXISTS customers")
 if err != nil {
  log.Fatal(err)
 }
 _, err = db.Exec("CREATE TABLE customers(id SERIAL, first_name VARCHAR(255), last_name VARCHAR(255))")
 if err != nil {
  log.Fatal(err)
 }
 fullNames := []string{
  "John Woo",
  "Jeff Dean",
  "Josh Bloch",
  "Josh Long"}
 stmt, err := db.Prepare("INSERT INTO customers(first_name, last_name) VALUES (...)")
 if err != nil {
  log.Fatal(err)
 }
 for _, fullName := range fullNames {
  log.Printf("Name: %s", fullName)
  name := strings.Split(fullName, " ")
  _, err := stmt.Query(name[0], name[1])
  if err != nil {
   log.Fatal(err)
  }
 }
 rows, err := db.Query("SELECT id, first_name, last_name FROM customers")
 if err != nil {
  log.Fatal(err)
 }
 defer rows.Close()
 var (
  id        string
  firstName string
  lastName  string
 )
 for rows.Next() {
  err := rows.Scan(&id, &firstName, &lastName)
  if err != nil {
   log.Fatal(err)
  }
  log.Printf("%v: %s %s", id, firstName, lastName)
 }
 err = rows.Err()
 if err != nil {
  log.Fatal(err)
 }
}

Ensure you pull the relevant libraries on first use:

go get ./...

Then you should be able to run the code with:

go run ${GOLANG-FILENAME}.go

MySQL

For Prepared Statements, MySQL uses “?” to denote parameters, please ensure you use this Golang statement:

stmt, err := db.Prepare("INSERT INTO customers(first_name, last_name) VALUES (?,?)")

1. In-process Proxy

When using the Cloud SQL Proxy in-process, your code imports the Cloud SQL Proxy for MySQL:

"github.com/GoogleCloudPlatform/cloudsql-proxy/proxy/dialers/mysql"

In this scenario, you do *not* need to run the cloud_sql_proxy command. That command provides the “companion process” (out-of-process) solution that’s described in “companion process” section below.

Golang:

const (
 ...
 instanceConnection = "${INSTANCE}-m"
)
cfg := mysql.Cfg(instanceConnection, user, password)
cfg.DBName = databaseName
db, err := mysql.DialCfg(cfg)

Run the code!

“companion process”

When using the Cloud SQL Proxy as a “companion process”, your code uses a regular MySQL driver and treats the Cloud SQL MySQL instance as if it were running on localhost. I use this Golang MySQL driver:

"github.com/go-sql-driver/mysql"

2. MySQL and TCP sockets

Proxy:

./cloud_sql_proxy -instances=${INSTANCE}-m=tcp:3306

Golang:

dsn := fmt.Sprintf("%s:%s@tcp(%s)/%s", user, password, "127.0.0.1:3306" databaseName)
db, err := sql.Open("mysql", dsn)

Run the code!

3. MySQL and UNIX sockets

Proxy:

./cloud_sql_proxy -instances=${INSTANCE}-m -dir=/cloudsql

Golang:

const (
 ...
 instanceConnection = "${INSTANCE}-m"
)
dsn := fmt.Sprintf("%s:%s@unix(/cloudsql/%s)/%s", user, password, instanceConnection, databaseName)
db, err := sql.Open("mysql", dsn)

Run the code!

PostgreSQL

For Prepared Statements, PostgreSQL uses $ prefixed parameters, please ensure you use this Golang statement:

stmt, err := db.Prepare("INSERT INTO customers(first_name, last_name) VALUES ($1,$2)")

4. In-process Proxy

When using the Cloud SQL Proxy in-process, your code imports the Cloud SQL Proxy for PostgreSQL. Do not omit the prefixing _:

_ "github.com/GoogleCloudPlatform/cloudsql-proxy/proxy/dialers/postgres"

In this scenario, you do *not* need to run the cloud_sql_proxy command. That command provides the “companion process” (out-of-process) solution that’s described in “companion process” section below.

Golang:

const (
 ...
 instanceConnection = "${INSTANCE}-p"
)
dsn := fmt.Sprintf("host=%s user=%s password=%s dbname=%s sslmode=disable",
 instanceConnection,
 user,
 password,
 databaseName)
db, err := sql.Open("cloudsqlpostgres", dsn)

Run the code!

“companion process”

When using the Cloud SQL Proxy as a “companion process”, your code uses a regular PostgreSQL driver and treats the Cloud SQL PostgreSQL instance as if it were running on localhost. I use the pq pure Golang PostgreSQL driver, you must import the following. Don’t forget the prefixing _:

_ "github.com/lib/pq"

5. PostgreSQL and TCP sockets

Proxy:

./cloud_sql_proxy -instances=${INSTANCE}-p=tcp:5432

Golang:

dsn := fmt.Sprintf("user=%s password=%s dbname=%s sslmode=disable", user, password, databaseName)
db, err := sql.Open("postgres", dsn)

Run the code!

6. PostgreSQL and UNIX sockets

Proxy:

./cloud_sql_proxy -instances=${INSTANCE}-p -dir=/cloudsql

Golang:

const (
 ...
 instanceConnection = "${INSTANCE}-p"
)
dsn := fmt.Sprintf("host=/cloudsql/%s user=%s password=%s dbname=%s sslmode=disable", instanceConnection, user, password, databaseName)
db, err := sql.Open("postgres", dsn)

Run the code!

Tear-down

Don’t forget to delete the project or the databases when you’re done investigating. You will be incurring charges. The easiest way is to delete the project:

gcloud projects delete ${PROJECT}

If you choose not to delete the project, you may just delete the databases:

gcloud sql instances delete ${INSTANCE}-m --project=${PROJECT}
gcloud sql instances delete ${INSTANCE}-p --project=${PROJECT}

Conclusion

We demonstrated 6 ways to connect Golang code to Google Cloud SQL.

OK…. so 8 ways if you include running the Proxy as “companion process” in a Docker container. You will need to create a service account and give it the role “Cloud SQL > Cloud SQL Client”. A JSON key file will be produced and stored on your workstation:

JSONKEY=/path/to/the/service.json

MySQL

docker run \
--volume=/cloudsql:/cloudsql \
--volume=$JSONKEY:/config \
--publish=127.0.0.1:3306:3306 \
gcr.io/cloudsql-docker/gce-proxy:1.10 \
/cloud_sql_proxy \
  -instances=${INSTANCE}-m=tcp:0.0.0.0:3306 \
  -credential_file=/config

PostgreSQL

docker run \
--volume=/cloudsql:/cloudsql \
--volume=$JSONKEY:/config \
--publish=127.0.0.1:5432:5432 \
gcr.io/cloudsql-docker/gce-proxy:1.10 \
/cloud_sql_proxy \
  -instances=${INSTANCE}-p=tcp:0.0.0.0:5432 \
  -credential_file=/config

That’s all!