How to use pgx

Database Web Database
2026-04-17T16:32:01+00:00
Use `pgx` by importing the `github.com/jackc/pgx/v5` module and initializing a connection pool with `pgxpool.New`, then execute queries using the `Query` or `Exec` methods on the pool or a transaction.

Use pgx by importing the github.com/jackc/pgx/v5 module and initializing a connection pool with pgxpool.New, then execute queries using the Query or Exec methods on the pool or a transaction. Unlike the standard database/sql driver, pgx provides native Go types for PostgreSQL-specific features like arrays, JSONB, and ranges, along with built-in connection pooling and prepared statement management.

Here is a basic example connecting to a database and fetching a single row:

package main

import (
	"context"
	"fmt"
	"log"
	"os"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
)

type User struct {
	ID   int
	Name string
}

func main() {
	// Use environment variable for connection string or hardcode for testing
	connStr := os.Getenv("DATABASE_URL")
	if connStr == "" {
		connStr = "postgres://user:password@localhost:5432/mydb?sslmode=disable"
	}

	// Create a connection pool (recommended for production)
	pool, err := pgxpool.New(context.Background(), connStr)
	if err != nil {
		log.Fatalf("failed to create pool: %v", err)
	}
	defer pool.Close()

	// Verify connection
	if err := pool.Ping(context.Background()); err != nil {
		log.Fatalf("failed to ping database: %v", err)
	}

	// Query a single row
	var u User
	err = pool.QueryRow(context.Background(), "SELECT id, name FROM users WHERE id = $1", 1).Scan(&u.ID, &u.Name)
	if err != nil {
		log.Fatalf("query failed: %v", err)
	}

	fmt.Printf("Found user: %d - %s\n", u.ID, u.Name)

	// Query multiple rows
	rows, err := pool.Query(context.Background(), "SELECT id, name FROM users")
	if err != nil {
		log.Fatalf("query failed: %v", err)
	}
	defer rows.Close()

	for rows.Next() {
		var u User
		if err := rows.Scan(&u.ID, &u.Name); err != nil {
			log.Fatalf("scan failed: %v", err)
		}
		fmt.Printf("User: %d - %s\n", u.ID, u.Name)
	}
}

For more complex scenarios, pgx allows you to use pgx.Tx for transactions with full control over commit and rollback logic. You can also leverage pgxtype to automatically map PostgreSQL types like jsonb directly to Go structs or []byte. When using pgx with database/sql compatibility (via pgxpool), remember that you lose some advanced features like row-level streaming or native type mapping, so prefer the native pgx API when performance and type safety are critical. Always pass a context.Context to query methods to handle timeouts and cancellation properly.