Cloud Run & Cloud SQL

main.go

package main

import (
	"database/sql"
	"flag"
	"fmt"
	// In-process Proxy
	// _ "github.com/GoogleCloudPlatform/cloudsql-proxy/proxy/dialers/postgres"
	// External Proxy
	_ "github.com/lib/pq"
	"log"
	"net/http"
	"os"
	"strings"
)

var (
	host     = os.Getenv("DBHOST")
	name     = os.Getenv("DBNAME")
	user     = os.Getenv("DBUSER")
	pass     = os.Getenv("DBPASS")
)
var (
	endpoint = flag.String("endpoint", ":8080", "")
)

func healthHandler(w http.ResponseWriter, r *http.Request) {
	fmt.Fprintln(w, "ok")
}
func sqlHandler(w http.ResponseWriter, r *http.Request) {

	// In-process Proxy
	// dsn := fmt.Sprintf("host=%s user=%s password=%s dbname=%s sslmode=disable", host, user, pass, name)
	// db, err := sql.Open("cloudsqlpostgres", dsn)

	// External Proxy
	dsn := fmt.Sprintf("host=/cloudsql/%s user=%s password=%s dbname=%s sslmode=disable", host, user, pass, name)
	db, err := sql.Open("postgres", dsn)
	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)
	}

	stmt, err := db.Prepare("INSERT INTO customers(first_name, last_name) VALUES ($1,$2)")
	if err != nil {
		log.Fatal(err)
	}

	fullNames := []string{
		"John Woo",
		"Sam Dean",
		"Anne Bloch",
		"Chris Long"}
	for _, fullName := range fullNames {
		fmt.Fprintf(w, "Name: %s\n", 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)
		}
		fmt.Fprintf(w, "%v: %s %s\n", id, firstName, lastName)
	}
	err = rows.Err()
	if err != nil {
		log.Fatal(err)
	}
}
func main() {
	flag.Parse()
	http.HandleFunc("/healthz", healthHandler)
	http.HandleFunc("/", sqlHandler)
	log.Fatal(http.ListenAndServe(*endpoint, nil))
}