pg

package module
v3.4.1+incompatible Latest Latest
Warning

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

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

README

PostgreSQL client for Golang Build Status

Supports:

API docs: http://godoc.org/gopkg.in/pg.v3. Examples: http://godoc.org/gopkg.in/pg.v3#pkg-examples.

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
}

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 []User
	_, err := db.Query(&users, `SELECT * FROM users`)
	return users, err
}

func GetUsersByIds(db *pg.DB, ids []int64) ([]User, error) {
	var users []User
	_, err := db.Query(&users, `SELECT * FROM users WHERE id IN (?)`, pg.Ints(ids))
	return users, 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]}
}

Why not database/sql, lib/pq, or GORM

  • No rows.Close to manually manage connections.
  • go-pg can automatically map rows on Go structs.
  • go-pg is at least 3x faster than GORM on querying 100 rows from table.
  • go-pg supports client-side placeholders that allow you to write complex queries and have full power of SQL.

Benchmark

BenchmarkQueryRowsOptimized-4	   10000	    154480 ns/op	   87789 B/op	     624 allocs/op
BenchmarkQueryRowsReflect-4  	   10000	    196261 ns/op	  102224 B/op	     925 allocs/op
BenchmarkQueryRowsStdlibPq-4 	    5000	    236584 ns/op	  166528 B/op	    1324 allocs/op
BenchmarkQueryRowsGORM-4     	    2000	    690532 ns/op	  399661 B/op	    6171 allocs/op

Howto

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

Documentation

Overview

Package gopkg.in/pg.v3 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
}

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 []Article
	_, 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}
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
}

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) {
	var item Item
	_, err := db.QueryOne(&item, `
		SELECT * FROM items WHERE id = ?
	`, id)
	return &item, err
}

func GetItems(db *pg.DB) ([]Item, error) {
	var items []Item
	_, 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]}

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 can be used with Query and QueryOne to discard rows.
	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 returns ColumnLoader or struct that are used to scan
	// columns from the current row.
	NewRecord() interface{}
}

Collection is a set of records mapped to database rows.

type ColumnLoader

type ColumnLoader interface {
	// Scan assigns a column value from a row.
	//
	// An error should be returned if the value can not be stored
	// without loss of information.
	//
	// TODO(vmihailenco): rename to ScanColumn
	LoadColumn(colIdx int, colName string, b []byte) error
}

ColumnLoader is an interface used by LoadColumn.

TODO(vmihailenco): rename to ColumnScanner

func LoadInto

func LoadInto(values ...interface{}) ColumnLoader

LoadInto returns ColumnLoader that copies the columns in the row into the values.

TODO(vmihailenco): rename to Scan

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
}

DB is a database handle representing a pool of zero or more underlying connections. It's safe for concurrent use by multiple goroutines.

func Connect

func Connect(opt *Options) *DB

Connect connects to a database using provided options.

The returned DB is safe for concurrent use by multiple goroutines and maintains its own connection pool.

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

Close closes the database client, releasing any open resources.

It is rare to Close a DB, as the DB handle is meant to be long-lived and shared between many goroutines.

func (*DB) CopyFrom

func (db *DB) CopyFrom(r io.Reader, q string, args ...interface{}) (*Result, error)

CopyFrom copies data from reader to a table.

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)

CopyTo copies data from a table to writer.

func (*DB) Exec

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

Exec executes a query ignoring returned rows. The args are for any placeholder parameters in the query.

Example
res, err := db.Exec(`CREATE TEMP TABLE test()`)
fmt.Println(res.Affected(), err)
Output:

-1 <nil>

func (*DB) ExecOne

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

ExecOne acts like Exec, but query must affect only one row. It returns ErrNoRows error when query returns zero rows or ErrMultiRows when query returns multiple rows.

func (*DB) Listen

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

Listen listens for notifications sent by NOTIFY statement.

func (*DB) Prepare

func (db *DB) Prepare(q string) (*Stmt, error)

Prepare creates a prepared statement for later queries or executions. Multiple queries or executions may be run concurrently from the returned statement.

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 interface{}, q string, args ...interface{}) (res *Result, err error)

Query executes a query that returns rows, typically a SELECT. The args are for any placeholder parameters in the query.

Example
package main

import (
	"fmt"

	"gopkg.in/pg.v3"
)

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

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 []User
	_, err := db.Query(&users, `SELECT * FROM users`)
	return users, err
}

func GetUsersByIds(db *pg.DB, ids []int64) ([]User, error) {
	var users []User
	_, err := db.Query(&users, `SELECT * FROM users WHERE id IN (?)`, pg.Ints(ids))
	return users, 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)

QueryOne acts like Query, but query must return only one row. It returns ErrNoRows error when query returns zero rows or ErrMultiRows when query returns multiple rows.

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.

func (*DB) WithTimeout

func (db *DB) WithTimeout(d time.Duration) *DB

WithTimeout returns a DB that uses d as the read/write timeout.

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

type Error

type Error interface {
	Field(byte) string
}

type F

type F string

F is a QueryAppender that represents 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 (setptr *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 {
	// 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 5 seconds.
	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
}

Database connection options.

type Q

type Q string

Q is a QueryAppender that represents safe 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 Result

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

A Result summarizes an executed SQL command.

func (*Result) Affected

func (r *Result) Affected() int

Affected returns the number of rows affected by SELECT, INSERT, UPDATE, or DELETE queries. It returns -1 when query can't possibly affect any rows, e.g. in case of CREATE or SHOW queries.

type Stmt

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

Stmt is a prepared statement. Stmt is safe for concurrent use by multiple goroutines.

func (*Stmt) Close

func (stmt *Stmt) Close() error

Close closes the statement.

func (*Stmt) Exec

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

Exec executes a prepared statement with the given arguments.

func (*Stmt) ExecOne

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

ExecOne acts like Exec, but query must affect only one row. It returns ErrNoRows error when query returns zero rows or ErrMultiRows when query returns multiple rows.

func (*Stmt) Query

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

Query executes a prepared query statement with the given arguments.

func (*Stmt) QueryOne

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

QueryOne acts like Query, but query must return only one row. It returns ErrNoRows error when query returns zero rows or ErrMultiRows when query returns multiple rows.

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() (err 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 interface{}, 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() (err error)

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