Postgres驱动程序在Go中找不到表

Question

Really strange yet very common error. Relation "users" does not exist. I know what you are saying - that's been asked before! And it has, but work with me because I'm doing a bunch of checks and it still doesn't go through.

First, this is the migration:

CREATE TABLE users (
    id                  serial PRIMARY KEY,
    obfuscated_id       VARCHAR(128) NOT NULL UNIQUE,
    email               VARCHAR(128) NOT NULL UNIQUE,
    encrypted_password  VARCHAR(128) NOT NULL,
    created_at          TIMESTAMP,
    updated_at          TIMESTAMP,
    active              BOOLEAN DEFAULT TRUE
);

And this is the command I'm using to run that migration:

migrate -path ./migrations -database postgres://myname:password@localhost:5432/five_three_one_development?sslmode=disable up

I've manually tested that the db exists:

psql

\c five_three_one_development
\dt 
 Schema |       Name        | Type  |  Owner
--------+-------------------+-------+----------
 public | schema_migrations | table | myname
 public | users             | table | myname

I've manually altered the password on the table using /password and set it to password.

Here are the environment variables:

DB_NAME=five_three_one_development
DB_PASS=password
DB_USER=myname

And when I log those variables I get the same values back:

fmt.Printf("NAME:" + os.Getenv("DB_NAME"))
fmt.Printf("USER:" + os.Getenv("DB_USER"))
fmt.Printf("DB_PASS:" + os.Getenv("DB_PASS"))

I also perform an environment test at the top of my development server to check that the db is reachable.

func (c *ApplicationContext) PerformEnvChecks() {
    err := c.Database.Ping()
    if err != nil {
        log.Fatalf("Database environment check failed: %s", err)
    }

    r, err := c.Database.Exec("SELECT * FROM users")
    if err != nil {
        log.Fatal(err) // --> pq: relation "users" does not exist
    }

    fmt.Printf("Tables: %v", r)
}

And then it fails on the c.Database.Exec("SELECT * FROM users") line which means that it's connected to the right database but it cannot find the table.

Out of ideas on this one - thoughts?

Edit

Feature request idea for postgresql folks: \connection_string -> returns the postgresql connection string given the current user inside of the database connected to.

Answer 1

Golang sql driver isn't clear about the order it expects. Ditch it. Save the whole connection string into a DATABASE URL variable and use that. Annoying but it seems to have solved the problem.

Pass this as a connection string: postgres://myname:password@localhost:5432/five_three_one_development?sslmode=disable and forget trying to build it up.