PQ vs PGX

main.go

package main

import (
	"database/sql"
	"fmt"
	"time"

	"github.com/lib/pq"

	pgx "github.com/jackc/pgx/v4"
	"github.com/jackc/pgx/v4/stdlib"
	sqltrace "gopkg.in/DataDog/dd-trace-go.v1/contrib/database/sql"
)

const (
	PQServiceName        = "pq-example"
	PGXServiceName       = "pgx-example"
	PGXSimpleServiceName = "pgx-simple-example"

	findInJsonVersionSql = "SELECT id FROM users WHERE fields ->> '_version' = $1"
)

// CreateDB is used to create an instrumented connection to the database
// to provide sql tracing using lib pq driver.
func CreatePQDB(url, serviceName string) (*sql.DB, error) {
	sqltrace.Register(serviceName, &pq.Driver{}, sqltrace.WithServiceName(serviceName))
	return sqltrace.Open(serviceName, url)
}

// CreateDB is used to create an instrumented connection to the database
// to provide sql tracing using pgx driver.
func CreatePGXDB(url, serviceName string, preferSimpleProtocol bool) (*sql.DB, error) {
	d := stdlib.GetDefaultDriver()
	sqltrace.Register(serviceName, d, sqltrace.WithServiceName(serviceName))
	cfg, err := pgx.ParseConfig(url)
	if err != nil {
		return nil, err
	}
	cfg.PreferSimpleProtocol = preferSimpleProtocol
	return sqltrace.OpenDB(stdlib.GetConnector(*cfg)), nil
}

func main() {
	dbURL := "postgres://xxx@db:5432/xxx?sslmode=disable"
	maxConnectionLifetime := 300
	version := 2
	id := ""

  
  // testing query with PQ
	fmt.Println("PQ:")
	pqdb, err := CreatePQDB(dbURL, PQServiceName+".database")
	if err != nil {
		panic(err)
	}
	pqdb.SetConnMaxLifetime(time.Duration(maxConnectionLifetime))
	if err := pqdb.Ping(); err != nil {
		panic(err)
	}
	row := pqdb.QueryRow(findInJsonVersionSql, version)
	if row.Err() != nil {
		fmt.Printf("QueryRow error: %s\n", row.Err().Error())
	} else {
		row.Scan(&id)
		fmt.Printf("QueryRow no error, value scanned: %v\n", id)
	}

  // testing query with PGX no SimpleProtocol
	fmt.Println("PGX with cfg.PreferSimpleProtocol set to false:")
	pgxdb, err := CreatePGXDB(dbURL, PGXServiceName+".database", false)
	if err != nil {
		panic(err)
	}
	pgxdb.SetConnMaxLifetime(time.Duration(maxConnectionLifetime))
	if err := pgxdb.Ping(); err != nil {
		panic(err)
	}
	id = ""
	row = pgxdb.QueryRow(findInJsonVersionSql, version)
	if row.Err() != nil {
		fmt.Printf("QueryRow error: %s\n", row.Err().Error())
	} else {
		row.Scan(&id)
		fmt.Printf("QueryRow no error, value scanned: %v\n", id)
	}

  // testing query with PGX with SimpleProtocol
	fmt.Println("PGX with cfg.PreferSimpleProtocol set to true:")
	pgxsimpledb, err := CreatePGXDB(dbURL, PGXSimpleServiceName+".database", true)
	if err != nil {
		panic(err)
	}
	pgxsimpledb.SetConnMaxLifetime(time.Duration(maxConnectionLifetime))
	if err := pgxdb.Ping(); err != nil {
		panic(err)
	}
	id = ""
	row = pgxsimpledb.QueryRow(findInJsonVersionSql, version)
	if row.Err() != nil {
		fmt.Printf("QueryRow error: %s\n", row.Err().Error())
	} else {
		row.Scan(&id)
		fmt.Printf("QueryRow no error, value scanned: %v\n", id)
	}
}

Output is:

PQ:
QueryRow no error, value scanned: xxxxidxxxx

PGX with cfg.PreferSimpleProtocol set to false:
QueryRow error: cannot convert 2 to Text

PGX with cfg.PreferSimpleProtocol set to true:
QueryRow error: ERROR: operator does not exist: text = integer (SQLSTATE 42883)