$PGHOST & $PGPORT are not respected. about dbmate

[azazeal]

This snippet exhibits the expected behavior described above, when using lib/pq directly:

package main

import (
	"database/sql"
	"os"
	"testing"

	_ "github.com/lib/pq"
	"github.com/stretchr/testify/require"
)

func Test(t *testing.T) {
	// $PGHOST is not defined in either the environment nor DATABASE_URL, 
	// so lib/pq will use a default value in its place.
	t.Setenv("PGPORT", "16431")
	t.Setenv("PGUSER", "some-user")
	t.Setenv("PGPASSWORD", "some-password")
	t.Setenv("DATABASE_URL", "postgres:///postgres?sslmode=disable")

	db, err := sql.Open("postgres", os.Getenv("DATABASE_URL"))
	require.NoError(t, err)
	t.Cleanup(func() { assert.NoError(t, db.Close()) })

	row := db.QueryRow("SELECT current_database(), version()")
	require.NoError(t, row.Err())

	var dbName, version string
	require.NoError(t, row.Scan(&dbName, &version))

	assert.Equal(t, "postgres", dbName)
	assert.NotEmpty(t, version)
}

[dossy]

dbmate's pg driver doesn't use the environment variables as default connection parameter values the way the libpq C client library does, but I think that would be a useful enhancement, if you'd like to contribute it.

I don't know if this is so much a bug as it is a feature request for dbmate, but that's not especially important.

This is how lib/pq handles supporting the environment variables, which may be interesting:

https://github.com/lib/pq/blob/3d613208bca2e74f2a20e04126ed30bcb5c4cc27/conn.go#L2002-L2082

I suppose if we're adding this capability in now, we should keep in mind what the latest Postgres 16 libpq C client library documents as available vs. what the underlying lib/pq Go client library supports, both as environment variables and connection string parameter keys. Ultimately, we can only support what the underlying lib/pq implements, and not what the libpq C client documents, so that's important to be aware of.