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)

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.