Pg: Connecting to Heroku

[thinkclay]

I'm curious as to why there is not a convenience method for URL Schemes? Connecting to a remote heroku database, for example, is a bit less than trivial. Would you mind making a quick example of how to structure a valid connection to a simple Hobby-Dev Postgres database? Or consider writing up a URL Scheme handler?

I'd be happy to write up some documentation and contribute towards a nice WIKI with examples for this package in exchange :)

[szubtsovskiy]

This is how I solved it in my app:

url := os.Getenv("DATABASE_URL")
url = strings.TrimPrefix(url, "postgres://")

dbNameStartsAt := strings.LastIndex(url, "/") + 1
database := url[dbNameStartsAt:]
url = url[:dbNameStartsAt - 1]

authAndHost := strings.Split(url, "@")
auth := strings.Split(authAndHost[0], ":")
username := auth[0]
password := auth[1]
hostAndPort := authAndHost[1]

db := pg.Connect(&pg.Options{
    User: username,
    Password: password,
    Database: database,
    Addr: hostAndPort,
})

Quick and dirty solution which solves the problem. Can and should be improved to at least log helpful error messages to diagnose a problem if a DATABASE_URL ever differs from the supported format.

Test output is available here: https://whispering-citadel-99008.herokuapp.com/pg-test

[szubtsovskiy]

Saner and cleaner implementation utilizing net/url package:

parsedUrl, err := url.Parse(os.Getenv("DATABASE_URL"))
if err != nil {
    panic(err)
}

pgOptions := &pg.Options{
    User: parsedUrl.User.Username(),
    Database: parsedUrl.Path[1:],
    Addr: parsedUrl.Host,
}

if password, ok := parsedUrl.User.Password(); ok {
    pgOptions.Password = password
}

db := pg.Connect(pgOptions)

[vmihailenco]

@szubtsovskiy do you want to send a PR since I am not using Heroku and not sure if I can maintain it myself? :) It could be function like

func ParseURL(urlStr string) (*pg.Options, error) {}

You probably can grab some ideas from https://github.com/go-redis/redis/pull/420/files

[szubtsovskiy]

@vmihailenco sure, I'd love to!

[szubtsovskiy]

Created #453

[szubtsovskiy]

Fixed by #453

[thinkclay]

@szubtsovskiy awesome stuff! Works like a charm. Worth noting that I had to still set InsecureSkipVerify = true to get it working with Heroku on a localhost, but at least it get's the job done in a nice and simple way just for getting up and running!

@vmihailenco could you enable Wiki on this repo? I'd like to create some more detailed examples and documentation, and generally I find that Wiki's are more useful for that than a series of Readme's, but ultimately that's your call.

[szubtsovskiy]

@thinkclay Thanks for raising this issue. I liked the opportunity to come with a helpful advice/change. :-) BTW, are you running some local Heroku installation? I am surprised hearing about this InsecureSkipVerify thing because on my local Postgres installation it does not event connect to database if TLSConfig is not nil. Also given example does work in public Heroku... I am now worried if #453 require any change.

[vmihailenco]

@thinkclay wiki enabled.

[thinkclay]

@szubtsovskiy here's the output I get running locally trying to connect to remote Postgres instance (all typical/default heroku setup):

Without any additional configuration, I get this:

tls: either ServerName or InsecureSkipVerify must be specified in the tls.ConfigLog

If I set ServerName I get this:

x509: certificate is valid for ip-10-148-21-39.ec2.internal, not ec2-54-163-234-4.compute-1.amazonaws.com

But setting InsecureSkipVerify works just fine:

func init() {
    options, _ := pg.ParseURL(os.Getenv("DATABASE_URL"))
    // options.TLSConfig.ServerName = "ec2-54-163-234-4.compute-1.amazonaws.com"
    options.TLSConfig.InsecureSkipVerify = true
    DB = pg.Connect(options)
}

[szubtsovskiy]

@thinkclay Oh I see. In the example above I was not setting TLSConfig at all, that's why it's working. But ParseURL does set an empty config if you don't provide sslmode=disable. Which you don't on Heroku... I should've tested this change there.

@vmihailenco Are changes pushed to this repo immediately available for everybody? Don't you need to do a "release"? I am pretty new to Go and still confused how to work with it correctly.

[thinkclay]

@szubtsovskiy it's already pushed out in the 5.2.5 release, but it may be worth updating for another minor (or future) release

[szubtsovskiy]

@thinkclay Ok, then I'll create new PR asap

[szubtsovskiy]

Created #458

[szubtsovskiy]

@thinkclay Would you mind closing this issue?