Getting started with pgx

Getting Started

This is a step by step guide to your first database connection with pgx.

Prerequisites

pgx requires a recent version of Go with module support. Use the go version command to display your current version of Go.

$ go version
go version go1.19.1 darwin/amd64

The version should be at least 1.18.

pgx also requires a PostgreSQL database that is accessible from your host. Use psql to test your connection.

$ psql
Timing is on.
Null display is "∅".
Line style is unicode.
psql (14.5 (Homebrew))
Type "help" for help.

jack@[local]:5432 jack=#

Only move on to the next step once you have confirmed a working Go install and PostgreSQL connection.

Initializing a Project

pgx uses Go modules. Create a new directory for your project and cd into it.

$ mkdir hello
$ cd hello

Initialize Go modules for the project.

$ go mod init hello
go: creating new go.mod: module hello

Add pgx to your Go modules:

$ go get github.com/jackc/pgx/v5

Hello world from PostgreSQL

Create the file main.go and copy and paste the following:

package main

import (
	"context"
	"fmt"
	"os"

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

func main() {
	conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL"))
	if err != nil {
		fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
		os.Exit(1)
	}
	defer conn.Close(context.Background())

	var greeting string
	err = conn.QueryRow(context.Background(), "select 'Hello, world!'").Scan(&greeting)
	if err != nil {
		fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err)
		os.Exit(1)
	}

	fmt.Println(greeting)
}

Save the file.

This example will use the database URL specified in the environment variable DATABASE_URL. pgx supports standard PostgreSQL environment variables such as PGHOST and PGDATABASE.

Use the same connection settings as were used when testing with psql above. If your psql connection did not require any arguments then you should not need to specify any for pgx (pgx uses similar logic as psql for default connection values).

$ go run main.go
Hello, world!

Using a Connection Pool

The *pgx.Conn returned by pgx.Connect() represents a single connection and is not concurrency safe. This is entirely appropriate for a simple command line example such as above. However, for many uses, such as a web application server, concurrency is required. To use a connection pool replace the import github.com/jackc/pgx/v5 with github.com/jackc/pgx/v5/pgxpool and connect with pgxpool.New() instead of pgx.Connect().

You may need to run go get for the pgxpool package.

$ go get github.com/jackc/pgx/v5/pgxpool

Here is the same program using the connection pool.

package main

import (
	"context"
	"fmt"
	"os"

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

func main() {
	dbpool, err := pgxpool.New(context.Background(), os.Getenv("DATABASE_URL"))
	if err != nil {
		fmt.Fprintf(os.Stderr, "Unable to create connection pool: %v\n", err)
		os.Exit(1)
	}
	defer dbpool.Close()

	var greeting string
	err = dbpool.QueryRow(context.Background(), "select 'Hello, world!'").Scan(&greeting)
	if err != nil {
		fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err)
		os.Exit(1)
	}

	fmt.Println(greeting)
}