PostgreSQL Support in Go

Internal

• Database Support in Go
• PostgreSQL
• jackc/pgx

Libraries

lib/pq (in maintenance mode) is used with the Standard Library, jackc/pgx, go-pg/pg (maintenance mode), bun, etc.

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