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

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.

CodePudding user response：

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:

Use the proper driver name:
```
    db, err := sqlx.Connect("pgx", s)
```

Import the stdlib compatibility pacakge:

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

CodePudding user response：

Your issue is in the import section.
This import is useless: github.com/jmoiron/sqlx. Moreover, you don't have to force Go to import the pgx/v5 package, simply use it in your code. Below you can find my working solution:
main.go file

package main

import "context"

func main() {
    ctx := context.Background()
    db := PgConnect(ctx, "postgres", "postgres", "localhost", "postgres")
    defer db.Close(ctx)
}

And then the db.go file where I initialize the DB connection:

package main

import (
    "context"
    "fmt"

    "github.com/jackc/pgx/v5"

    log "github.com/sirupsen/logrus"
)

func PgConnect(ctx context.Context, username, password, host, app string) *pgx.Conn {
    s := fmt.Sprintf("postgres://%v:%v@%v:5432/%v", username, password, host, app)
    db, err := pgx.Connect(ctx, s)
    if err != nil {
        log.Panicf("failed to connect to sqlserver://%s:%s@%s err: %s", username, "xxxxxxxxxxxx", host, err)
    }

    return db
}

If you run this code, you should be able to successfully connect to your Postgres instance.
Hope this helps.