postgresqlgosqlxpgx

What driver name do I use to connect Go sqlx to Postgres using the pgx driver?


Or alternatively, what additional imports do I need?

I'd like to start using Postgres as my main DBMS for some development I am doing, and my research suggests that pgx is the database driver of choice at this time. I have setup a connection package:

package database

import (
    "fmt"
    _ "github.com/jackc/pgx/v5"
    "net/url"

    "github.com/jmoiron/sqlx"
    log "github.com/sirupsen/logrus"
)

func PgConnect(username, password, host, app string) *sqlx.DB {
    s := fmt.Sprintf("postgres://%s:%s@%s?app+name=%s&sslmode=allow", url.QueryEscape(username), url.QueryEscape(password), host, app)
    db, err := sqlx.Connect("postgres", s)
    if err != nil {
        log.Panicf("failed to connect to sqlserver://%s:%s@%s err: %s", username, "xxxxxxxxxxxx", host, err)
    }

    return db
}

The URL evaluates to:

postgres://user:password@database-host:5432/database-name?app+name=app-name&sslmode=allow

and I've also tried the prefix of postgresql here because I've seen both in places on the internet.

For the driver name in the sqlx.Connect("postgres", s) call I have tried postgres, postgresql and pgx.

In all cases the call to connect fails with the error:

sql: unknown driver "postgres" (forgotten import?)

The same code (with driver mssql and mssql URL) works connection to Microsoft SQL Server.

Can anyone help me get this going? I've found very little on the internet for this combination of language/driver/sqlx/postgres.


Solution

  • From the documentation, we can see that the driver's name is pgx:

    A database/sql connection can be established through sql.Open.

    db, err := sql.Open("pgx", "postgres://pgx_md5:secret@localhost:5432/pgx_test?sslmode=disable")
    if err != nil {
      return err
    }
    

    So you'll need to do two things:

    1. Use the proper driver name:

          db, err := sqlx.Connect("pgx", s)
      
    2. Import the stdlib compatibility pacakge:

      import (
          _ "github.com/jackc/pgx/v5/stdlib" // Standard library bindings for pgx
      )