-
Notifications
You must be signed in to change notification settings - Fork 810
Getting started with pgx
This is a step by step guide to your first database connection with pgx.
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.
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
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!
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)
}