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 and a jackc/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 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 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

The API is a bit different when using the specialized library, 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")
}

Database Operations

Open a Connection

import (
  "database/sql"
  _ "github.com/lib/pq"
)

...

role := "ovidiu"
password := "something"
databaseHost := "localhost"
databaseName := "testdb"
connectionString := fmt.Sprintf("postgres://%s:%s@%s/%s?sslmode=disable", role, password, databaseHost, databaseName)
db, err := sql.Open("postgres", connectionString)
if err != nil {
  ...
}
defer func() {
  if err := db.Close(); err != nil {
    ...
  }
}()

err = db.Ping()
if err != nil {
  ...
}

"postgres://<user>:<password>@<host>/<database-name>?...." is called connection string.

sql.Open() returns a database handle. The implementation maintains a pool of zero or more underlying connections, and it is safe for concurrent use by multiple goroutines.

Create a Schema

sql := `CREATE SCHEMA IF NOT EXISTS someschema`
_, err = db.Exec(sql)
if err != nil {
 ...
}

Create a Table

sql := `CREATE TABLE IF NOT EXISTS TEST ("ID" int, "NAME" varchar(10))`
_, err = db.Exec(sql)
if err != nil {
 ...
}

Delete a Table

sql := `DROP TABLE TEST`
_, err = db.Exec(sql)
if err != nil {
 ...
}

Insert a Row

s := `INSERT INTO TEST ("ID", "NAME") VALUES (10, 'Bob')`
_, err = db.Exec(s)
if err != nil {
  ...
}

Alternative:

var ctx context.Context ...
var id title description director string
s := `INSERT INTO movies ("id", "title", "description", "director") VALUES ($1, $2, $3, $4)`
_, err := r.db.ExecContext(ctx, s, id, title, description, director)
if err != nil {
  ...
}

Update a Row

sql := `UPDATE TEST SET "ID"=$1 WHERE "NAME"=$2`
_, err = db.Exec(sql, 20, "Bob")
if err != nil {
  ...
}

Delete a Row

sql := `DELETE FROM TEST WHERE "ID"=$1`
_, err = db.Exec(sql, 20)
if err != nil {
  ...
}

Query

s := `SELECT "ID", "NAME" FROM TEST`
rows, err := db.Query(s)
if err != nil {
  ...
}
defer rows.Close()
for rows.Next() {
  var id int
  var name string
  err = rows.Scan(&id, &name)
  if err != nil {
    ...
  }
}

Other query methods:

s := `SELECT "value", "user_id" FROM ratings WHERE "record_id"=$1 AND "record_type"=$2`
rows, e := r.db.QueryContext(ctx, s, recordID, recordType)
defer func() {
  if e := rows.Close(); e != nil {
    ...
  }
}()
if e != nil {
  ...
}
for rows.Next() {
  var rating int
  var userID string
  if e := rows.Scan(&rating, &userID); e != nil {
    ...
  }
  // do something with each result rating, userID
}

QueryRowContex() is expected to return at most a row:

var title, description, director string
q := `SELECT "title", "description", "director" FROM movies WHERE "id"=$1`
row := r.db.QueryRowContext(ctx, q, id)
if err := row.Scan(&title, &description, &director); err != nil {
  ...
}