Created
March 8, 2022 06:57
-
-
Save jimenez/48a52c4ddad16636869f7b78adf2e527 to your computer and use it in GitHub Desktop.
PQ vs PGX
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Output is: