Go database programming
=======================

connect::

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

  var db *sql.DB

  db, err := sql.Open("postgres", os.Getenv("DATABASE_URL"))

sql command with no result::

  _, err := db.Exec(`CREATE TABLE xyz ...`)

sql command, no result, argument::

  _, err := db.Exec(`DELETE FROM xyz WHERE id = $1`, id)

insert and get back new id key::

  var newId int
  err := db.QueryRow(`insert into bookmarks (url) values ($1) returning id`).Scan(&newId)

query rows::

  query := `SELECT id, name FROM xyz WHERE id = $1`
  rows, err := db.Query(query, 3)
  defer rows.Close()

  for rows.Next() {
    var record TypeName
    err := rows.Scan(&record.Id, &record.Name)
    doSomethingWith(record)
  }

insert record including Postgres array. First the table def::

  create table if not exists xyz (
    id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    name text,
    tagarray text[]
  );

insert a record::

  var newId int

  tags := []string{"box", "floor"}
  err := db.QueryRow(
    `insert into xyz (name, tagarray) values ($1, $2) returning id`,
    "somename",
    pq.Array(tags),
  ).Scan(&newId)

retrieve records containing some tag in the array column::

  query := `select id, name, tagarray from xyz where $1 <@ tagarray`
  tags := []string{"box"}
  rows, err = db.Query(joinedQuery, pq.Array(tags))
  defer rows.Close()
  for rows.Next() {
    var record TypeName
    rows.Scan(&record.Id, &record.Name, pq.Array(&record.Tags))
    doSomethingWith(record)
  }