Getting started with pgx through database sql

Getting started with pgx through database/sql

This is a step by step guide to your first database connection with pgx through the standard Go database/sql interface.

The database/sql interface should be used when compatibility with non-PostgreSQL databases is required or when using other libraries that require database/sql such as sqlx or gorm. If this is not the case, prefer the direct pgx interface which offers greater performance and features.

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 (
	"database/sql"
	"fmt"
	"os"

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

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

	var greeting string
	err = db.QueryRow("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!