PostgreSQL Support in Go

Internal

• Database Support in Go
• PostgreSQL
• jackc/pgx
• Masterminds/squirrel

Overview

PostgreSQL can be accessed from Go either with the database/sql library, which is part of the Standard Library, or with a specialist library like jackc/pgx.

database/sql provides a uniform SQL API but is quite generic since it has to be able to work uniformly with many different database platforms. It has to be configured with a database driver, like lib/pq or the driver that comes with jackc/pgx.

jackc/pgx is a PostgreSQL specialist library that has its own API, and its own driver (which can be used with database/sql).

This article shows how to connect to the database and then perform DDL and DML operations with both database/sql (configured with a jackc/pgx driver) and jackc/pgx library.

Connect to Database

With database/sql API and a pgx Driver

database/sql must be configured with a PostgreSQL driver via a blank import. This examples uses the jackc/pgx driver.

import (
	"database/sql"
	"fmt"

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

role := "postgres"
password := "somepasswd"
host := "localhost"
port := 5432
database := "postgres"
connectionString := fmt.Sprintf("postgres://%s:%s@%s:%d/%s", role, password, host, port, database)
dbHandle, err := sql.Open("pgx", connectionString)
if err != nil {
	panic(err)
}
defer func() {
	if err := dbHandle.Close(); err != nil {
		fmt.Printf("failed to close the connection\n")
	}
}()
if err := dbHandle.Ping(); err != nil {
	fmt.Printf("failed to ping the database: %v\n", err)
} else {
	fmt.Printf("database OK\n")
}

sql.Open("pgx", ...) returns a database handle, and it must be called with a driver name recognized by the driver library initialized with the blank import. In this case is "pgx". The implementation maintains a pool of zero or more underlying connections, and it is safe for concurrent use by multiple goroutines.

The example shows how to automatically close the connection on exit, and also how to ping the database to ensure the connection works.

With database/sql API and a lib/pq Driver

The code is quasi-identical, with three differences:

• the name of the blank import used to initialize the driver
• the name of the driver sql.Open() is invoked with ("postgres" instead of "pgx")
• details of the connection string: the "lib/pg" driver attempts to connect with SSL enabled and if the database is not connected for SSL, it fails.

import (
    ...

	_ "github.com/lib/pq" // different driver
)

...
connectionString := fmt.Sprintf("postgres://%s:%s@%s:%d/%s?sslmode=disable", role, password, host, port, database) // different connection string parameters
dbHandle, err := sql.Open("postgres", connectionString) // different driver name
...

With jackc/pgx API

The API is a bit different when using the specialized library (pgx.Connect() instead of the sql.Open(), etc.), but the overall structure of the database connection sequence is identical:

import (
	"context"
	"fmt"

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

role := "postgres"
password := "somepasswd"
host := "localhost"
port := 5432
database := "postgres"
connectionString := fmt.Sprintf("postgres://%s:%s@%s:%d/%s?sslmode=disable", role, password, host, port, database)
ctx := context.Background()
connectionHandle, err := pgx.Connect(ctx, connectionString)
if err != nil {
	panic(err)
}
defer func() {
	if err := connectionHandle.Close(ctx); err != nil {
		fmt.Printf("failed to close the connection\n")
	}
}()
if err := connectionHandle.Ping(ctx); err != nil {
	fmt.Printf("failed to ping the database: %v\n", err)
} else {
	fmt.Printf("database OK\n")
}

DDL

Create a schema, create a table within the schema, drop the table and the schema.

With database/sql

_, err := dbHandle.Exec("CREATE SCHEMA IF NOT EXISTS someschema")
if err != nil { ... }
_, err = dbHandle.Exec("CREATE TABLE IF NOT EXISTS someschema.sometable (id integer PRIMARY KEY, name text)")
if err != nil { ... }
_, err = dbHandle.Exec("DROP TABLE someschema.sometable")
if err != nil { ... }
_, err = dbHandle.Exec("DROP SCHEMA someschema CASCADE")
if err != nil { ... }

With jackc/pgx

The API is quasi-indentical, the Exec() function requires an additional context.Context:

ctx := context.Background()
_, err := connectionHandle.Exec(ctx, "CREATE SCHEMA IF NOT EXISTS someschema")
if err != nil { ... }
_, err = connectionHandle.Exec(ctx, "CREATE TABLE IF NOT EXISTS someschema.sometable (id integer PRIMARY KEY, name text)")
if err != nil { ... }
_, err = connectionHandle.Exec(ctx, "DROP TABLE someschema.sometable")
if err != nil { ... }
_, err = connectionHandle.Exec(ctx, "DROP SCHEMA someschema CASCADE")
if err != nil { ... }

DML

Insert a row in a table, then update that row, then delete it:

With database/sql

_, err := dbHandle.Exec("INSERT INTO someschema.sometable (id, name) VALUES ($1, $2)", 1, "Alice")
if err != nil { ... }
_, err = dbHandle.Exec("UPDATE someschema.sometable SET name = $1 WHERE id = $2", "Bob", 1)
if err != nil { ... }
_, err = dbHandle.Exec("DELETE FROM someschema.sometable WHERE id = $1", 1)
if err != nil { ... }

Behavior on nil Positional Parameters

If a nil value is provided for a positional parameter, NULL will be inserted in the corresponding column, provided that NULL values are allowed for that column.

With jackc/pgx

The API is quasi-indentical, the Exec() function requires an additional context.Context:

ctx := context.Background()
_, err := connectionHandle.Exec(ctx, "INSERT INTO someschema.sometable (id, name) VALUES ($1, $2)", 1, "Alice")
if err != nil { ... }
_, err = connectionHandle.Exec(ctx, "UPDATE someschema.sometable SET name = $1 WHERE id = $2", "Bob", 1)
if err != nil { ... }
_, err = connectionHandle.Exec(ctx, "DELETE FROM someschema.sometable WHERE id = $1", 1)
if err != nil { ... }

The API has the same behavior on nil as in case of the database/sql API.

Queries

With database/sql

If one single row is expected as result of the query, then QueryRow()/QueryRowContext() can be used. Otherwise Query()/QueryContext() can be used to retrieve multiple rows:

Single Row

row := dbHandle.QueryRow("SELECT id, name, city FROM someschema.sometable WHERE id = $1", 1)
var (
	id   int
	name string
	city sql.NullString
)
err := row.Scan(&id, &name, &city)
if err != nil { ... }

Multiple Rows

rows, err := dbHandle.Query("SELECT id, name, city FROM someschema.sometable WHERE city IS NOT NULL AND id <> $1", 1)
if err != nil { ... }
defer func() {
	if err = rows.Close(); err != nil { ... }
}()
for rows.Next() {
	var (
		id   int
		name string
		city sql.NullString
	)
	err = rows.Scan(&id, &name, &city)
	if err != nil { ... }
    ... 
}

Handling NULL Results

If a column value can be NULL, providing the pointer to a string to Scan() can lead to:

Scan error on column index 2, name "city": converting NULL to string is unsupported

sql.NullString and equivalents should be used instead.

With jackc/pgx

TODO when needed.