README
¶
PostgreSQL client for Golang 
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 FROMandCOPY TO.- Timeouts. Client sends
CancelRequestmessage 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.Factoryrenamed topg.Collection.Factory.Newrenamed toCollection.NewRecord.pg.Loaderrenamed topg.ColumnLoader.ColumnLoader.Loadrenamed toColumnLoader.LoadColumn.pg.Appenderrenamed topg.QueryAppender.QueryAppender.Appendrenamed toQueryAppender.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]}
Example (QueryTimeout) ¶
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)
}
Output:
Index ¶
- Variables
- func AppendQ(dst []byte, src string, params ...interface{}) ([]byte, error)
- func Decode(dst interface{}, b []byte) error
- func DecodeValue(v reflect.Value, b []byte) error
- type Collection
- type ColumnLoader
- type DB
- func (db *DB) Begin() (*Tx, error)
- func (db *DB) Close() error
- func (db *DB) CopyFrom(r io.Reader, q string, args ...interface{}) (*Result, error)
- func (db *DB) CopyTo(w io.WriteCloser, q string, args ...interface{}) (*Result, error)
- func (db *DB) Exec(q string, args ...interface{}) (res *Result, err error)
- func (db *DB) ExecOne(q string, args ...interface{}) (*Result, error)
- func (db *DB) Listen(channels ...string) (*Listener, error)
- func (db *DB) Prepare(q string) (*Stmt, error)
- func (db *DB) Query(coll Collection, q string, args ...interface{}) (res *Result, err error)
- func (db *DB) QueryOne(record interface{}, q string, args ...interface{}) (*Result, error)
- func (db *DB) RunInTransaction(fn func(*Tx) error) error
- func (db *DB) UseTimeout(d time.Duration) *DB
- type Error
- type F
- type IntSet
- type IntegrityError
- type Ints
- type Listener
- type Options
- type Q
- type QueryAppender
- type RawQueryAppender
- type RecordReader
- type Result
- type Stmt
- func (stmt *Stmt) Close() error
- func (stmt *Stmt) Exec(args ...interface{}) (res *Result, err error)
- func (stmt *Stmt) ExecOne(args ...interface{}) (*Result, error)
- func (stmt *Stmt) Query(coll Collection, args ...interface{}) (res *Result, err error)
- func (stmt *Stmt) QueryOne(record interface{}, args ...interface{}) (*Result, error)
- type Strings
- type Tx
- func (tx *Tx) Commit() error
- func (tx *Tx) Exec(q string, args ...interface{}) (*Result, error)
- func (tx *Tx) ExecOne(q string, args ...interface{}) (*Result, error)
- func (tx *Tx) Prepare(q string) (*Stmt, error)
- func (tx *Tx) Query(coll Collection, q string, args ...interface{}) (*Result, error)
- func (tx *Tx) QueryOne(record interface{}, q string, args ...interface{}) (*Result, error)
- func (tx *Tx) Rollback() error
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 ¶
Types ¶
type Collection ¶
type Collection interface {
NewRecord() interface{}
}
type ColumnLoader ¶
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 ¶
Example ¶
db := pg.Connect(&pg.Options{
User: "postgres",
})
err := db.Close()
fmt.Println(err)
Output: <nil>
func (*DB) Begin ¶
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) CopyFrom ¶
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) Exec ¶
Example ¶
res, err := db.Exec(`CREATE TEMP TABLE test()`) fmt.Println(res.Affected(), err)
Output: 0 <nil>
func (*DB) Prepare ¶
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 ¶
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 ¶
RunInTransaction runs a function in a transaction. If function returns an error transaction is rollbacked, otherwise transaction is committed.
type IntegrityError ¶
type IntegrityError struct {
// contains filtered or unexported fields
}
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 ¶
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>
type Options ¶
type Options struct {
// The network type, either tcp or unix.
// Default is tcp.
Network string
Host string
Port string
User string
Password string
Database string
// Whether to use secure TCP/IP connections (TLS).
SSL bool
// Run-time configuration parameters to be set on connection.
Params map[string]interface{}
// The deadline for establishing new connections. If reached,
// dial will fail with a timeout.
// Default is 5 seconds.
DialTimeout time.Duration
// The timeout for socket reads. If reached, commands will fail
// with a timeout error instead of blocking.
// Default is no timeout.
ReadTimeout time.Duration
// The timeout for socket writes. If reached, commands will fail
// with a timeout error instead of blocking.
// Default is no timeout.
WriteTimeout time.Duration
// The maximum number of open socket connections.
// Default is 10 connections.
PoolSize int
// The amount of time client waits for free connection if all
// connections are busy before returning an error.
// Default is 1 second.
PoolTimeout time.Duration
// The amount of time after which client closes idle connections.
// Default is to not close idle connections.
IdleTimeout time.Duration
// The frequency of idle checks.
// Default is 1 minute.
IdleCheckFrequency time.Duration
}
type Q ¶
type Q string
Raw SQL query.
func MustFormatQ ¶
func (Q) AppendQuery ¶
func (Q) AppendRawQuery ¶
type QueryAppender ¶
type RawQueryAppender ¶
type RecordReader ¶
type Stmt ¶
type Stmt struct {
// contains filtered or unexported fields
}
Not thread-safe.
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) LoadColumn ¶
Source Files
¶
Directories
Click to show internal directories.
Click to hide internal directories.