[question] Is pgx compatible with sqlx?

[kokizzu]

Hi, I've been using github.com/lib/pq and github.com/jmoiron/sqlx for a long time now, if I want to use pgx as pq replacement, would it be a simple thing to do?

[jackc]

Yes. Support for pgx was merged into sqlx on November 1, 2014.

Here is an example use of pgx and sqlx

package main

import (
    "fmt"
    _ "github.com/jackc/pgx/stdlib"
    "github.com/jmoiron/sqlx"
    "log"
)

var schema = `
CREATE TABLE person (
    first_name text,
    last_name text,
    email text
)`

type Person struct {
    FirstName string `db:"first_name"`
    LastName  string `db:"last_name"`
    Email     string
}

func main() {
    // this connects & tries a simple 'SELECT 1', panics on error
    // use sqlx.Open() for sql.Open() semantics
    db, err := sqlx.Connect("pgx", "postgres://pgx_md5:secret@localhost:5432/pgx_test")
    if err != nil {
        log.Fatalln(err)
    }

    // exec the schema or fail; multi-statement Exec behavior varies between
    // database drivers;  pq will exec them all, sqlite3 won't, ymmv
    db.MustExec(schema)

    tx := db.MustBegin()
    tx.MustExec("INSERT INTO person (first_name, last_name, email) VALUES ($1, $2, $3)", "Jason", "Moiron", "jmoiron@jmoiron.net")
    tx.MustExec("INSERT INTO person (first_name, last_name, email) VALUES ($1, $2, $3)", "John", "Doe", "johndoeDNE@gmail.net")
    tx.Commit()

    // Selects Mr. Smith from the database
    rows, err := db.NamedQuery(`SELECT * FROM person WHERE first_name=:fn`, map[string]interface{}{"fn": "John"})
    if err != nil {
        fmt.Println(err)
    }
    fmt.Println(rows)
}

[jayd3e]

Just to be clear, is there support for sqlx for pgx's native interface? I'm using the pgx ConnPool for a different library, so it seems I would either need to create two different connections via pgx(one via the native and one via the stdlib version of pgx) in order to use pgx's ConnPool and sqlx at the same time. Thoughts?

[jackc]

No, it is only accessible via the standard interface. If sqlx can accept a already established sql/database *DB then may be possible to create a ConnPool, use stdlib.OpenFromConnPool() and pass that to sqlx. But I don't have any first hand experience and don't know for sure.

[ncantelmo]

I had the same question, tried using stdlib.OpenFromConnPool() as suggested, and it worked without issue.

[AlexDunmow]

@ncantelmo how exactly did you bind the connection pool to sqlx?

[ncantelmo]

@AlexDunmow the following works:

package main

import (
	"log"
	"time"

	"github.com/jackc/pgx"
	"github.com/jackc/pgx/stdlib"
	"github.com/jmoiron/sqlx"
	"github.com/pkg/errors"
)

func main() {
	_, err := OpenSqlxViaPgxConnPool()
	if err != nil {
		log.Fatal(err)
	} else {
		log.Println("Worked!")
	}
}

// OpenSqlxViaPgxConnPool does what its name implies
func OpenSqlxViaPgxConnPool() (*sqlx.DB, error) {
	// First set up the pgx connection pool
	connConfig := pgx.ConnConfig{
		Host:     "localhost",
		Database: "my_local_db",
	}
	connPool, err := pgx.NewConnPool(pgx.ConnPoolConfig{
		ConnConfig:     connConfig,
		AfterConnect:   nil,
		MaxConnections: 20,
		AcquireTimeout: 30 * time.Second,
	})
	if err != nil {
		return nil, errors.Wrap(err, "Call to pgx.NewConnPool failed")
	}

	// Apply any migrations...

	// Then set up sqlx and return the created DB reference
	nativeDB, err := stdlib.OpenFromConnPool(connPool)
	if err != nil {
		connPool.Close()
		return nil, errors.Wrap(err, "Call to stdlib.OpenFromConnPool failed")
	}
	return sqlx.NewDb(nativeDB, "pgx"), nil
}

This is a bit simplified from what I'm doing, because I needed to avoid having the afterConn callback run before migrations had been applied. If you're not setting afterConn, or can jam both the migrations and anything else you want to do into a single afterConn callback, you can ignore the next paragraph.

In practice I connect with a nil afterConn, apply any migrations, and then if I have an afterConn callback to apply (e.g. if I'm using something like que-go for job processing), close and then re-open the connPool with afterConn set. After all that's done, I continue on to stdlib.OpenFromConnPool() as shown above with the reopened pool.

[lzap]

Update 2022: This is no longer possible in v4.

[asiman161]

today you need to import _ "github.com/jackc/pgx/stdlib"

[lzap]

What do you mean, @asiman161, the code no longer works and this import line (which is included by the way) does not change a thing. If you have a solution, please share a full code, thanks!

[ysomad]

today you need to import _ "github.com/jackc/pgx/stdlib"

share working code please.

[fgm]

Any hope of seeing it taken into account for v5, since it's not yet finalized and doesn't seem to require much beyond some do ?

[jackc]

I don't use sqlx, but as pgx/stdlib has a database/sql compatible driver it should work however any other driver does.

But the import needs to include the version. github.com/jackc/pgx/v4/stdlib for the current stable release and github.com/jackc/pgx/v5/stdlib for the soon to be released version.

[dncahyo]

Thanks @jackc for your instruction
For those who wondering this is solution using v5 that works for me

package main

import (
	"context"
	"log"
	"os"
	"time"

	"github.com/jmoiron/sqlx"
	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/stdlib"
)

func main() {
	db, err := GetDB(os.Getenv("PG_URI"))
	if err != nil {
		log.Fatal(err)
		return
	}
	log.Println("Worked!!")

	var id int
	var somekey string
	db.Get(&id, "SELECT id from sometable")
	db.Get(&somekey, "SELECT current_setting('some.key')")
	log.Println("sometable id:", id)
	log.Println("somekey value:", somekey)
}

func GetDB(uri string) (*sqlx.DB, error) {
	// before : directly using sqlx
	// DB, err = sqlx.Connect("postgres", uri)
	// after : using pgx to setup connection
	DB, err := PgxCreateDB(uri)
	if err != nil {
		return nil, err
	}
	DB.SetMaxIdleConns(2)
	DB.SetMaxOpenConns(4)
	DB.SetConnMaxLifetime(time.Duration(30) * time.Minute)

	return DB, nil
}

func PgxCreateDB(uri string) (*sqlx.DB, error) {
	connConfig, _ := pgx.ParseConfig(uri)
	afterConnect := stdlib.OptionAfterConnect(func(ctx context.Context, conn *pgx.Conn) error {
		_, err := conn.Exec(ctx, `
			 SET SESSION "some.key" = 'somekey';
			 CREATE TEMP TABLE IF NOT EXISTS sometable AS SELECT 212 id;
		`)
		if err != nil {
			return err
		}
		return nil
	})

	pgxdb := stdlib.OpenDB(*connConfig, afterConnect)
	return sqlx.NewDb(pgxdb, "pgx"), nil
}

[sedyh]

Thanks @jackc for your instruction For those who wondering this is solution using v5 that works for me

That's strange... db.Ping() is freezing forever in that case, but something like select 1 is working.

It should be implemented, right?
https://github.com/jackc/pgx/blob/master/pgxpool/pool.go#L676

See also: #981 (comment)

[pivaldi]

Thanks @jackc for your instruction For those who wondering this is solution using v5 that works for me

package main

import (
	"context"
	"log"
	"os"
	"time"

	"github.com/jmoiron/sqlx"
	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/stdlib"
)

func main() {
	db, err := GetDB(os.Getenv("PG_URI"))
	if err != nil {
		log.Fatal(err)
		return
	}
	log.Println("Worked!!")

	var id int
	var somekey string
	db.Get(&id, "SELECT id from sometable")
	db.Get(&somekey, "SELECT current_setting('some.key')")
	log.Println("sometable id:", id)
	log.Println("somekey value:", somekey)
}

func GetDB(uri string) (*sqlx.DB, error) {
	// before : directly using sqlx
	// DB, err = sqlx.Connect("postgres", uri)
	// after : using pgx to setup connection
	DB, err := PgxCreateDB(uri)
	if err != nil {
		return nil, err
	}
	DB.SetMaxIdleConns(2)
	DB.SetMaxOpenConns(4)
	DB.SetConnMaxLifetime(time.Duration(30) * time.Minute)

	return DB, nil
}

func PgxCreateDB(uri string) (*sqlx.DB, error) {
	connConfig, _ := pgx.ParseConfig(uri)
	afterConnect := stdlib.OptionAfterConnect(func(ctx context.Context, conn *pgx.Conn) error {
		_, err := conn.Exec(ctx, `
			 SET SESSION "some.key" = 'somekey';
			 CREATE TEMP TABLE IF NOT EXISTS sometable AS SELECT 212 id;
		`)
		if err != nil {
			return err
		}
		return nil
	})

	pgxdb := stdlib.OpenDB(*connConfig, afterConnect)
	return sqlx.NewDb(pgxdb, "pgx"), nil
}

Hi,

From the documentation of pgx :

be sure to set the maximum idle connections of the *sql.DB created with this connector to zero since they must be managed from the *pgxpool.Pool.

So it seems better to use stdlib.OpenDBFromPool instead of stdlib.OpenDB (which is not documented) :

// OpenDBFromPool creates a new *sql.DB from the given *pgxpool.Pool. Note that this method automatically sets the
// maximum number of idle connections in *sql.DB to zero, since they must be managed from the *pgxpool.Pool. This is
// required to avoid acquiring all the connections from the pgxpool and starving any direct users of the pgxpool.
func OpenDBFromPool(pool *pgxpool.Pool, opts ...OptionOpenDB) *sql.DB