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