pg

package module
v3.3.4+incompatible Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: May 25, 2015 License: BSD-3-Clause Imports: 25 Imported by: 0

README

PostgreSQL client for Golang Build Status

Supports:

  • Basic types: integers, floats, string, bool, time.Time, and pointers to these types.
  • sql.NullBool, sql.NullString, sql.NullInt64 and sql.Float64.
  • sql.Scanner and sql/driver.Valuer interfaces.
  • Arrays.
  • Partially hstore.
  • Transactions.
  • Prepared statements.
  • Notifications: LISTEN/NOTIFY.
  • COPY FROM and COPY TO.
  • Timeouts. Client sends CancelRequest message on timeout.
  • Connection pool.
  • Queries are retried when possible.
  • PostgreSQL to Go struct mapping.

API docs: http://godoc.org/gopkg.in/pg.v3.

Installation

Install:

go get gopkg.in/pg.v3

Quickstart

package pg_test

import (
	"fmt"

	"gopkg.in/pg.v3"
)

type User struct {
	Id     int64
	Name   string
	Emails []string
}

type Users struct {
	C []User
}

var _ pg.Collection = &Users{}

func (users *Users) NewRecord() interface{} {
	users.C = append(users.C, User{})
	return &users.C[len(users.C)-1]
}

func CreateUser(db *pg.DB, user *User) error {
	_, err := db.QueryOne(user, `
		INSERT INTO users (name, emails) VALUES (?name, ?emails)
		RETURNING id
	`, user)
	return err
}

func GetUser(db *pg.DB, id int64) (*User, error) {
	var user User
	_, err := db.QueryOne(&user, `SELECT * FROM users WHERE id = ?`, id)
	return &user, err
}

func GetUsers(db *pg.DB) ([]User, error) {
	var users Users
	_, err := db.Query(&users, `SELECT * FROM users`)
	return users.C, err
}

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

	_, err := db.Exec(`CREATE TEMP TABLE users (id serial, name text, emails text[])`)
	if err != nil {
		panic(err)
	}

	err = CreateUser(db, &User{
		Name:   "admin",
		Emails: []string{"admin1@admin", "admin2@admin"},
	})
	if err != nil {
		panic(err)
	}

	err = CreateUser(db, &User{
		Name:   "root",
		Emails: []string{"root1@root", "root2@root"},
	})
	if err != nil {
		panic(err)
	}

	user, err := GetUser(db, 1)
	if err != nil {
		panic(err)
	}

	users, err := GetUsers(db)
	if err != nil {
		panic(err)
	}

	fmt.Println(user)
	fmt.Println(users[0], users[1])
	// Output: &{1 admin [admin1@admin admin2@admin]}
	// {1 admin [admin1@admin admin2@admin]} {2 root [root1@root root2@root]}
}

Howto

Please go through examples to get the idea how to use this package.

Changelog

v3 (beta)
  • pg.Factory renamed to pg.Collection. Factory.New renamed to Collection.NewRecord.
  • pg.Loader renamed to pg.ColumnLoader. ColumnLoader.Load renamed to ColumnLoader.LoadColumn.
  • pg.Appender renamed to pg.QueryAppender. QueryAppender.Append renamed to QueryAppender.AppendQuery.
v2
  • Support for named placeholders:
a := &Article{Id: 1, Name: "Hello world"}
_, err := db.ExecOne(`UPDATE articles SET name = ?name WHERE id = ?id`, a)
  • CopyFrom/CopyTo support:
r := strings.NewReader("hello\t5\nworld\t5\nfoo\t3\nbar\t3\n")
res, err := t.db.CopyFrom(r, "COPY test FROM STDIN")
  • Simplify collections:
type Articles []*Article

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

Initial release.

Documentation

Overview

Package github.com/vmihailenco/pg implements a PostgreSQL client.

Example (ComplexQuery) 
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}
Example (Json) 
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]}

Index

Examples

Constants

This section is empty.

Variables

View Source 
var (
	ErrSSLNotSupported = errorf("pg: SSL is not enabled on the server")

	ErrNoRows    = errorf("pg: no rows in result set")
	ErrMultiRows = errorf("pg: multiple rows in result set")
)
View Source 
var (
	Discard = discardLoader{}
)

Functions

func AppendQ 

func AppendQ(dst []byte, src string, params ...interface{}) ([]byte, error)

func Decode 

func Decode(dst interface{}, b []byte) error

func DecodeValue 

func DecodeValue(v reflect.Value, b []byte) error

Types

type Collection 

type Collection interface {
	NewRecord() interface{}
}

type ColumnLoader 

type ColumnLoader interface {
	LoadColumn(colIdx int, colName string, b []byte) error
}

func LoadInto 

func LoadInto(values ...interface{}) ColumnLoader
Example 
var s1, s2 string
_, err := db.QueryOne(pg.LoadInto(&s1, &s2), `SELECT ?, ?`, "foo", "bar")
fmt.Println(s1, s2, err)

Output:

foo bar <nil>

func NewColumnLoader 

func NewColumnLoader(dst interface{}) (ColumnLoader, error)

type DB 

type DB struct {
	// contains filtered or unexported fields
}

Thread-safe.

func Connect 

func Connect(opt *Options) *DB
Example 
db := pg.Connect(&pg.Options{
	User: "postgres",
})
err := db.Close()
fmt.Println(err)

Output:

<nil>

func (*DB) Begin 

func (db *DB) Begin() (*Tx, error)
Example 
tx, err := db.Begin()
if err != nil {
	panic(err)
}

_, err = tx.Exec(`CREATE TABLE tx_test()`)
if err != nil {
	panic(err)
}

err = tx.Rollback()
if err != nil {
	panic(err)
}

_, err = db.Exec(`SELECT * FROM tx_test`)
fmt.Println(err)

Output:

ERROR #42P01 relation "tx_test" does not exist:

func (*DB) Close 

func (db *DB) Close() error

func (*DB) CopyFrom 

func (db *DB) CopyFrom(r io.Reader, q string, args ...interface{}) (*Result, error)
Example 
_, err := db.Exec(`CREATE TEMP TABLE words(word text, len int)`)
if err != nil {
	panic(err)
}

r := strings.NewReader("hello,5\nfoo,3\n")
_, err = db.CopyFrom(r, `COPY words FROM STDIN WITH CSV`)
if err != nil {
	panic(err)
}

buf := &bytes.Buffer{}
_, err = db.CopyTo(&NopWriteCloser{buf}, `COPY words TO STDOUT WITH CSV`)
if err != nil {
	panic(err)
}
fmt.Println(buf.String())

Output:

hello,5
foo,3

func (*DB) CopyTo 

func (db *DB) CopyTo(w io.WriteCloser, q string, args ...interface{}) (*Result, error)

func (*DB) Exec 

func (db *DB) Exec(q string, args ...interface{}) (res *Result, err error)
Example 
res, err := db.Exec(`CREATE TEMP TABLE test()`)
fmt.Println(res.Affected(), err)

Output:

0 <nil>

func (*DB) ExecOne 

func (db *DB) ExecOne(q string, args ...interface{}) (*Result, error)

func (*DB) Listen 

func (db *DB) Listen(channels ...string) (*Listener, error)

func (*DB) Prepare 

func (db *DB) Prepare(q string) (*Stmt, error)
Example 
stmt, err := db.Prepare(`SELECT $1::text, $2::text`)
if err != nil {
	panic(err)
}

var s1, s2 string
_, err = stmt.QueryOne(pg.LoadInto(&s1, &s2), "foo", "bar")
fmt.Println(s1, s2, err)

Output:

foo bar <nil>

func (*DB) Query 

func (db *DB) Query(coll Collection, q string, args ...interface{}) (res *Result, err error)
Example 
package main

import (
	"fmt"

	"gopkg.in/pg.v3"
)

type User struct {
	Id     int64
	Name   string
	Emails []string
}

type Users struct {
	C []User
}

var _ pg.Collection = &Users{}

func (users *Users) NewRecord() interface{} {
	users.C = append(users.C, User{})
	return &users.C[len(users.C)-1]
}

func CreateUser(db *pg.DB, user *User) error {
	_, err := db.QueryOne(user, `
		INSERT INTO users (name, emails) VALUES (?name, ?emails)
		RETURNING id
	`, user)
	return err
}

func GetUser(db *pg.DB, id int64) (*User, error) {
	var user User
	_, err := db.QueryOne(&user, `SELECT * FROM users WHERE id = ?`, id)
	return &user, err
}

func GetUsers(db *pg.DB) ([]User, error) {
	var users Users
	_, err := db.Query(&users, `SELECT * FROM users`)
	return users.C, err
}

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

	_, err := db.Exec(`CREATE TEMP TABLE users (id serial, name text, emails text[])`)
	if err != nil {
		panic(err)
	}

	err = CreateUser(db, &User{
		Name:   "admin",
		Emails: []string{"admin1@admin", "admin2@admin"},
	})
	if err != nil {
		panic(err)
	}

	err = CreateUser(db, &User{
		Name:   "root",
		Emails: []string{"root1@root", "root2@root"},
	})
	if err != nil {
		panic(err)
	}

	user, err := GetUser(db, 1)
	if err != nil {
		panic(err)
	}

	users, err := GetUsers(db)
	if err != nil {
		panic(err)
	}

	fmt.Println(user)
	fmt.Println(users[0], users[1])
}

Output:

&{1 admin [admin1@admin admin2@admin]}
{1 admin [admin1@admin admin2@admin]} {2 root [root1@root root2@root]}

func (*DB) QueryOne 

func (db *DB) QueryOne(record interface{}, q string, args ...interface{}) (*Result, error)
Example 
var user struct {
	Name string
}

res, err := db.QueryOne(&user, `
        WITH users (name) AS (VALUES (?))
        SELECT * FROM users
    `, "admin")
if err != nil {
	panic(err)
}
fmt.Println(res.Affected())
fmt.Println(user)

Output:

1
{admin}

func (*DB) RunInTransaction 

func (db *DB) RunInTransaction(fn func(*Tx) error) error

RunInTransaction runs a function in a transaction. If function returns an error transaction is rollbacked, otherwise transaction is committed.

type Error 

type Error interface {
	Field(byte) string
}

type F 

type F string

SQL field, e.g. table or column name.

func (F) AppendQuery 

func (f F) AppendQuery(dst []byte) []byte

type IntSet 

type IntSet map[int64]struct{}

func (IntSet) LoadColumn 

func (set IntSet) LoadColumn(colIdx int, colName string, b []byte) error

func (IntSet) NewRecord 

func (set IntSet) NewRecord() interface{}

type IntegrityError 

type IntegrityError struct {
	// contains filtered or unexported fields
}

func (IntegrityError) Error 

func (err IntegrityError) Error() string

func (IntegrityError) Field 

func (err IntegrityError) Field(k byte) string

type Ints 

type Ints []int64
Example 
var nums pg.Ints
_, err := db.Query(&nums, `SELECT generate_series(0, 10)`)
fmt.Println(nums, err)

Output:

[0 1 2 3 4 5 6 7 8 9 10] <nil>

func (Ints) AppendQuery 

func (ints Ints) AppendQuery(dst []byte) []byte

func (*Ints) LoadColumn 

func (ints *Ints) LoadColumn(colIdx int, colName string, b []byte) error

func (*Ints) NewRecord 

func (ints *Ints) NewRecord() interface{}

type Listener 

type Listener struct {
	// contains filtered or unexported fields
}

Not thread-safe.

Example 
ln, err := db.Listen("mychan")
if err != nil {
	panic(err)
}

wait := make(chan struct{}, 2)
go func() {
	wait <- struct{}{}
	channel, payload, err := ln.Receive()
	fmt.Printf("%s %q %v", channel, payload, err)
	wait <- struct{}{}
}()

<-wait
db.Exec("NOTIFY mychan, ?", "hello world")
<-wait

Output:

mychan "hello world" <nil>

func (*Listener) Close 

func (l *Listener) Close() error

func (*Listener) Listen 

func (l *Listener) Listen(channels ...string) error

func (*Listener) Receive 

func (l *Listener) Receive() (channel string, payload string, err error)

func (*Listener) ReceiveTimeout 

func (l *Listener) ReceiveTimeout(readTimeout time.Duration) (channel, payload string, err error)

type Options 

type Options struct {
	Network  string
	Host     string
	Port     string
	User     string
	Password string
	Database string
	SSL      bool

	// Params specify connection run-time configuration parameters.
	Params map[string]interface{}

	PoolSize    int
	PoolTimeout time.Duration

	DialTimeout  time.Duration
	ReadTimeout  time.Duration
	WriteTimeout time.Duration

	IdleTimeout        time.Duration
	IdleCheckFrequency time.Duration
}

type Q 

type Q string

Raw SQL query.

func FormatQ 

func FormatQ(src string, params ...interface{}) (Q, error)

func MustFormatQ 

func MustFormatQ(src string, params ...interface{}) Q

func (Q) AppendQuery 

func (q Q) AppendQuery(dst []byte) []byte

func (Q) AppendRawQuery 

func (q Q) AppendRawQuery(dst []byte) []byte

type QueryAppender 

type QueryAppender interface {
	AppendQuery([]byte) []byte
}

type RawQueryAppender 

type RawQueryAppender interface {
	AppendRawQuery([]byte) []byte
}

type RecordReader 

type RecordReader interface {
	Read() ([]string, error)
}

type Result 

type Result struct {
	// contains filtered or unexported fields
}

func (*Result) Affected 

func (r *Result) Affected() int

type Stmt 

type Stmt struct {
	// contains filtered or unexported fields
}

Not thread-safe.

func (*Stmt) Close 

func (stmt *Stmt) Close() error

func (*Stmt) Exec 

func (stmt *Stmt) Exec(args ...interface{}) (res *Result, err error)

func (*Stmt) ExecOne 

func (stmt *Stmt) ExecOne(args ...interface{}) (*Result, error)

func (*Stmt) Query 

func (stmt *Stmt) Query(coll Collection, args ...interface{}) (res *Result, err error)

func (*Stmt) QueryOne 

func (stmt *Stmt) QueryOne(record interface{}, args ...interface{}) (*Result, error)

type Strings 

type Strings []string
Example 
var strs pg.Strings
_, err := db.Query(
	&strs, `WITH users AS (VALUES ('foo'), ('bar')) SELECT * FROM users`)
fmt.Println(strs, err)

Output:

[foo bar] <nil>

func (Strings) AppendQuery 

func (strings Strings) AppendQuery(dst []byte) []byte

func (*Strings) LoadColumn 

func (strings *Strings) LoadColumn(colIdx int, _ string, b []byte) error

func (*Strings) NewRecord 

func (strings *Strings) NewRecord() interface{}

type Tx 

type Tx struct {
	// contains filtered or unexported fields
}

Not thread-safe.

func (*Tx) Commit 

func (tx *Tx) Commit() error

func (*Tx) Exec 

func (tx *Tx) Exec(q string, args ...interface{}) (*Result, error)

func (*Tx) ExecOne 

func (tx *Tx) ExecOne(q string, args ...interface{}) (*Result, error)

func (*Tx) Prepare 

func (tx *Tx) Prepare(q string) (*Stmt, error)

TODO(vmihailenco): track and close prepared statements

func (*Tx) Query 

func (tx *Tx) Query(coll Collection, q string, args ...interface{}) (*Result, error)

func (*Tx) QueryOne 

func (tx *Tx) QueryOne(record interface{}, q string, args ...interface{}) (*Result, error)

func (*Tx) Rollback 

func (tx *Tx) Rollback() error

Source Files

View all Source files

Directories

Path Synopsis
Utility functions used in pg package.
 Utility functions used in pg package.

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.