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 and provides a uniform SQL API, or with a specialist library like jackc/pgx, that provides its own database driver and API. is quite generic since it has to be able to work uniformly with many different database platforms, and it has to be configured with a database driver, like lib/pq or the driver that comes with jackc/pgx.

Libraries

PostgreSQL can be accessed from Go either via the Standard Library database/sql with a driver like lib/pq, or with a specialist driver like jackc/pgx.

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 {
  ...
}