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 []*Article

var _ pg.Collection = &Articles{}

func (articles *Articles) NewRecord() interface{} {
	a := &Article{}
	*articles = append(*articles, a)
	return a
}

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, 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 []*Item

var _ pg.Collection = &Items{}

func (items *Items) NewRecord() interface{} {
	i := &Item{}
	*items = append(*items, i)
	return i
}

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, 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]}