Pgx: [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", "[email protected]")
    tx.MustExec("INSERT INTO person (first_name, last_name, email) VALUES ($1, $2, $3)", "John", "Doe", "[email protected]")
    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.