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)
}