Pg: [Feature] Google SQL support

[mxpv]

Hey.
Question about using pg on GCP.
There are two ways of using Postgres on Google Cloud:

• via public IP address (which is not recommended for production)
• via Google SQL Proxy.

The Cloud SQL Proxy allows a user with the appropriate permissions to connect to a Second Generation Cloud SQL database without having to deal with IP whitelisting or SSL certificates manually. It works by opening unix/tcp sockets on the local machine and proxying connections to the associated Cloud SQL instances when the sockets are used.

For Go programs SQL proxy can be used as library. Since having one more additional layer is pain in the ass, it would be really nice to support it natively in pg or as extension.

Thanks

[vmihailenco]

I did not try, but something like this may work

import "github.com/GoogleCloudPlatform/cloudsql-proxy/proxy/proxy"

pg.Connect(&pg.Options{
    Dialer: func(network, addr string) (net.Conn, error) {
        return proxy.Dial("project-name:region:instance-name")
    },
})

[mxpv]

wow ) that was easy :) thanks!

[fnkr]

For the record: if you're using App Engine standard environment (not flexible environment) you need to use appengine/cloudsql to connect to your Cloud SQL instance. Here is a simplified example of how my app works:

// +build appengine

package myapp

import (
    "net"
    "os"

    "github.com/go-pg/pg"
    "google.golang.org/appengine"
    "google.golang.org/appengine/cloudsql"
)

func NewDB() *pg.DB {
    // Connect to local instance in development
    // Environment variables can be set via app.yaml
    if appengine.IsDevAppServer() {
        return pg.Connect(&pg.Options{
            Addr:     os.Getenv("LOCALSQL_HOST"),
            User:     os.Getenv("LOCALSQL_PORT"),
            Password: os.Getenv("LOCALSQL_USER"),
            Database: os.Getenv("LOCALSQL_PASS"),
        })
    }

    // Connect to Cloud SQL in production
    // Environment variables can be set via app.yaml
    return pg.Connect(&pg.Options{
        Dialer: func(network, addr string) (net.Conn, error) {
            return cloudsql.Dial(os.Getenv("CLOUDSQL_HOST")) // project-name:region:instance-name
        },
        User:     os.Getenv("CLOUDSQL_USER"),
        Password: os.Getenv("CLOUDSQL_PASS"),
        Database: os.Getenv("CLOUDSQL_NAME"),
    })
}

[vmihailenco]

Can you please add it to the wiki https://github.com/go-pg/pg/wiki/FAQ#how-to-connect-to-google-sql-using-proxy?

BTW var is not needed and code can be simplified to:

        host := os.Getenv("CLOUDSQL_HOST") // project-name:region:instance-name
        user := os.Getenv("CLOUDSQL_USER")
        pass := os.Getenv("CLOUDSQL_PASS")
        name := os.Getenv("CLOUDSQL_NAME")

[fnkr]

Can you please add it to the wiki

Done.

BTW var is not needed and code can be simplified to

Good point, those were leftover from the original code, which is more complex.

[1it]

I did not try, but something like this may work

import "github.com/GoogleCloudPlatform/cloudsql-proxy/proxy/proxy"

pg.Connect(&pg.Options{
    Dialer: func(network, addr string) (net.Conn, error) {
        return proxy.Dial("project-name:region:instance-name")
    },
})

It looks like a magic. Can someone clarify how it works? :)
Not works for me.
cannot use func literal (type func(string, string) (net.Conn, error)) as type func(context.Context, string, string) (net.Conn, error) in field value

go version go1.13.3 linux/amd64

[SoftwareMasteryInstitute]

This took wayyy longer for me to get going than it should have - partially me not reading others comments here properly...

The key was using the Google Proxy project to help wire things up (github.com/GoogleCloudPlatform/cloudsql-proxy/proxy/proxy). Thanks to everyone for all the info above.

Long story short, got AppEngine standard talking to CloudSQL with the following:

package db

import (
    "context"
    "github.com/GoogleCloudPlatform/cloudsql-proxy/proxy/proxy"
    "github.com/go-pg/pg/v10"
    "net"
    "os"
)

const (
    ENVIRONMENT = "ENVIRONMENT"
    DB_HOST     = "DB_HOST" // e.g. myprojectname:asia-southeast2:mycloudsqlinstancename 
    DB_USER     = "DB_USER" // e.g. postgres
    DB_PASS     = "DB_PASS"
    DB_NAME     = "DB_NAME"
)

func MustGetEnv(k string) string {
    v := os.Getenv(k)
    if v == "" {
        log.Fatalf("Warning: %s environment variable not set.\n", k)
    }
    return v
}

func Get() (*pg.DB, error) {
    var opt *pg.Options
    var err error

    if os.Getenv(ENVIRONMENT) == "production" {
        opt = &pg.Options{
            Dialer: func(ctx context.Context, network, addr string) (net.Conn, error) {
                return proxy.Dial(MustGetEnv(DB_HOST))
            },
            User:     MustGetEnv(DB_USER),
            Password: MustGetEnv(DB_PASS),
            Database: MustGetEnv(DB_NAME),
        }
    } else {
        connectionString := MustGetEnv(DATABASE_URL)
        opt, err = pg.ParseURL(connectionString)
    }

    if err != nil {
        return nil, err
    }

    db := pg.Connect(opt)

    return db, nil
}