pg

package main

import (
	"fmt"

	"gopkg.in/pg.v3"
)

type ArticleFilter struct {
	Id         int64
	Name       string
	CategoryId int
}

func (f *ArticleFilter) FilterName() pg.Q {
	if f.Name == "" {
		return ""
	}
	return pg.MustFormatQ("AND name = ?", f.Name)
}

func (f *ArticleFilter) FilterCategory() pg.Q {
	if f.CategoryId == 0 {
		return ""
	}
	return pg.MustFormatQ("AND category_id = ?", f.CategoryId)
}

type Article struct {
	Id         int64
	Name       string
	CategoryId int
}

type Articles struct {
	C []Article
}

var _ pg.Collection = &Articles{}

func (articles *Articles) NewRecord() interface{} {
	articles.C = append(articles.C, Article{})
	return &articles.C[len(articles.C)-1]
}

func CreateArticle(db *pg.DB, article *Article) error {
	_, err := db.ExecOne(`
		INSERT INTO articles (name, category_id)
		VALUES (?name, ?category_id)
	`, article)
	return err
}

func GetArticle(db *pg.DB, id int64) (*Article, error) {
	article := &Article{}
	_, err := db.QueryOne(article, `SELECT * FROM articles WHERE id = ?`, id)
	return article, err
}

func GetArticles(db *pg.DB, f *ArticleFilter) ([]Article, error) {
	var articles Articles
	_, err := db.Query(&articles, `
		SELECT * FROM articles WHERE 1=1 ?FilterName ?FilterCategory
	`, f)
	if err != nil {
		return nil, err
	}
	return articles.C, nil
}

func main() {
	db := pg.Connect(&pg.Options{
		User: "postgres",
	})
	defer db.Close()

	_, err := db.Exec(`CREATE TEMP TABLE articles (id serial, name text, category_id int)`)
	if err != nil {
		panic(err)
	}

	err = CreateArticle(db, &Article{Name: "article1", CategoryId: 1})
	if err != nil {
		panic(err)
	}

	err = CreateArticle(db, &Article{Name: "article2", CategoryId: 2})
	if err != nil {
		panic(err)
	}

	articles, err := GetArticles(db, &ArticleFilter{})
	if err != nil {
		panic(err)
	}
	fmt.Printf("%d %v %v\n", len(articles), articles[0], articles[1])

	articles, err = GetArticles(db, &ArticleFilter{CategoryId: 1})
	if err != nil {
		panic(err)
	}
	fmt.Printf("%d %v\n", len(articles), articles[0])

}

Output:

2 {1 article1 1} {2 article2 2}
1 {1 article1 1}

package main

import (
	"database/sql/driver"
	"encoding/json"
	"fmt"

	"gopkg.in/pg.v3"
)

type jsonMap map[string]interface{}

func (m *jsonMap) Scan(value interface{}) error {
	return json.Unmarshal(value.([]byte), m)
}

func (m jsonMap) Value() (driver.Value, error) {
	b, err := json.Marshal(m)
	if err != nil {
		return nil, err
	}
	return string(b), nil
}

type Item struct {
	Id   int64
	Data jsonMap
}

type Items struct {
	C []Item
}

var _ pg.Collection = &Items{}

func (items *Items) NewRecord() interface{} {
	items.C = append(items.C, Item{})
	return &items.C[len(items.C)-1]
}

func CreateItem(db *pg.DB, item *Item) error {
	_, err := db.ExecOne(`INSERT INTO items VALUES (?id, ?data)`, item)
	return err
}

func GetItem(db *pg.DB, id int64) (*Item, error) {
	item := &Item{}
	_, err := db.QueryOne(item, `
		SELECT * FROM items WHERE id = ?
	`, id)
	return item, err
}

func GetItems(db *pg.DB) ([]Item, error) {
	var items Items
	_, err := db.Query(&items, `
		SELECT * FROM items
	`)
	return items.C, err
}

func main() {
	db := pg.Connect(&pg.Options{
		User: "postgres",
	})
	defer db.Close()

	_, err := db.Exec(`CREATE TEMP TABLE items (id serial, data json)`)
	if err != nil {
		panic(err)
	}

	item := &Item{
		Id:   1,
		Data: jsonMap{"hello": "world"},
	}
	if err := CreateItem(db, item); err != nil {
		panic(err)
	}

	item, err = GetItem(db, 1)
	if err != nil {
		panic(err)
	}
	fmt.Println(item)
}

Output:

&{1 map[hello:world]}

var count int
// Use bigger timeout since this query is known to be slow.
_, err := db.UseTimeout(time.Minute).QueryOne(pg.LoadInto(&count), `
		SELECT count(*) FROM big_table
	`)
if err != nil {
	panic(err)
}