Example of connecting to Postgres?

[justi...@google.com]

Hi there,

I'm having trouble making a basic connection to a CloudSQL (Postgres) database. Hoping someone can share a really basic, working example of connecting an appengine instance with a Postgres database on CloudSQL. Thanks in advance!

I receive the an error "not an App Engine context" with the following code (credentials and identifiable info replaced with placeholders):

app.yaml

runtime: go

api_version: go1.8

handlers:

- url: /.*

  script: _go_app

env_variables:

  database_connection_string: user=USER password=PASSWORD dbname=DATABASENAME sslmode=disable host=PROJECT:REGION:INSTANCE

beta_settings:

  cloud_sql_instances:PROJECT:REGION:INSTANCE

nobuild_files: commands/.*

skip_files:

- ^.*_test.go$

app.go

package main

import (

    "database/sql"

    "google.golang.org/appengine"

    "github.com/gorilla/mux"

    "github.com/reidreid46/test/cmd/testd/routes/handlers/rest/testcontrollers"

    "log"

    "net/http"

    "os"

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

)

type Application struct {

    Router      *mux.Router

    Database    *sql.DB

}

////////////////////////////////////////////////////////////////////////////////

// unexported

////////////////////////////////////////////////////////////////////////////////

func main() {

    application := Application{}

    application.initialize()

    appengine.Main()

    return

}

func (application *Application) initialize() {

    application.Router = mux.NewRouter()

    application.Router.StrictSlash(false)

    application.initializeDatabase()

    application.initializeRoutes()

    return

}

func (application *Application) initializeDatabase() {

    var err error

    connectionString := os.Getenv("database_connection_string")

   application.Database, err = sql.Open("cloudsqlpostgres", connectionString)

    if err != nil {

        log.Fatal(err)

    }

    return

}

func (application *Application) initializeRoutes() {

    testcontrollers.InitializeRoutes(

        application.Router,

        application.Database,

    )

    http.Handle("/", application.Router)

    return

}

[Eric Fixler]

A couple of notes that may be helpful:

1) The error that you're showing here is an error that I've typically seen when trying call an appengine API in the standard environment and trying to do so with n context that is not fabricated using appengine.WithContext(context, request). 

Your code sample looks like a fragment, but if you're using contexts anywhere, look at the godoc for google.golang.org/appengine

2) In general, with cloudsql, I've had good success using either the cloudsql or unix socket connection methods. You might try setting a mountpoint for unix sockets and then testing your db connection from a postgres client without code.

cloud_sql_proxy -projects [YOUR_PROJECT] -dir [MOUNTPOINT]

The docs use /cloudsql as a sample mount point, and I generally find this pretty sensible.

- Eric

[Steven Buss]

Excellent advice from Eric. Justin, did it help?

--
You received this message because you are subscribed to the Google Groups "google-appengine-go" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-appengin...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-appengine-go/717ce424-1b5d-40ba-b51d-ce63ae90f438%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Justin Reid]

Hi there,

Yeah, definitely shed some light on some things. Thanks! 

Now I'm able to run the cloud_sql_proxy and connect my local computer to my remote CloudSQL instance. So, it seems that i need to figure out how to apply that to my AppEngine Standard env on the remote server. I'm not quite sure how auth with work, I assume through the service account option, but still need to give it a try. I'm still a bit fuzzy on how to start the proxy from within my app on a remote server. Looks like there some documentation on it. i'll give it a try. If I get stuck I'll reply to this tread.

I'm not quite sure what to make of the guidance on "contexts", specifically what this is referring to " trying to do so with n context that is not fabricated using appengine.WithContext(context, request)". i haven't encountered "contexts" before, so I'm not quite sure how they fit in, or if they're a necessary part of the solution. 

Cheers,

Justin

• • • •

Justin Reid

Interaction designer

Google Assistant UX

[Justin Reid]

So, I've gotten a little further, but stuck again.

I've setup the cloudproxy, and I'm able to connect to the Postgres database SSH'd into the machine, but I'm still unable to connect the Go app to it. I've tried connecting through the cloudsql-proxy postgres proxy dialer, and through the regular PQ library. I get different errors...

sql.Open("cloudsqlpostgres", connectionString)

failed to parse addr: "/cloudsql/<INSTANCE_CONNECTION_NAME>/.s.PGSQL.5432". It should conform to the regular expression "^\\[(.+)\\]:[0-9]+$"

sql.Open("postgres", connectionString)

dial unix /cloudsql/<INSTANCE_CONNECTION_NAME>/.s.PGSQL.5432: socket: operation not permitted

• • • •

Justin Reid

Interaction designer

Google Assistant UX

[Nima Jalali]

Justin,

Are you trying to connect App Engine Standard to Postgres CloudSQL? This isn't currently supported using the Go runtime, per the docs here.

You'll have to use App Engine Flexible to connect to Postgres CloudSQL using Go.

Nima

To unsubscribe from this group and stop receiving emails from it, send an email to google-appengine-go+unsub...@googlegroups.com.

[Justin Reid]

Ahhh that might explain it. Thanks for the tip.

-Justin

• • • •

Justin Reid

Interaction designer

Google Assistant UX

To unsubscribe from this group and stop receiving emails from it, send an email to google-appengin...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-appengine-go/717ce424-1b5d-40ba-b51d-ce63ae90f438%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "google-appengine-go" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-appengine-go/Mda-GOlfWZo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-appengin...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-appengine-go/e6a0016f-d776-4412-b328-45cbf29c096a%40googlegroups.com.