Back to godoc.org
gopkg.in/pg.v5

package pg

v5.3.3
Latest Go to latest
Published: Mar 4, 2017 | License: BSD-2-Clause | Module: gopkg.in/pg.v5

Overview

Package gopkg.in/pg.v5 implements a PostgreSQL client.

Example (Placeholders)

go-pg recognizes placeholders (`?`) in queries and replaces them with parameters when queries are executed. Parameters are escaped before replacing according to PostgreSQL rules. Specifically:

- all parameters are properly quoted against SQL injections;
- null byte is removed;
- JSON/JSONB gets `\u0000` escaped as `\\u0000`.

Code:

package pg_test

import (
	"fmt"

	"gopkg.in/pg.v5"
)

type Params struct {
	X int
	Y int
}

func (p *Params) Sum() int {
	return p.X + p.Y
}

// go-pg recognizes placeholders (`?`) in queries and replaces them
// with parameters when queries are executed. Parameters are escaped
// before replacing according to PostgreSQL rules. Specifically:
//   - all parameters are properly quoted against SQL injections;
//   - null byte is removed;
//   - JSON/JSONB gets `\u0000` escaped as `\\u0000`.
func Example_placeholders() {
	var num int

	// Simple params.
	_, err := db.Query(pg.Scan(&num), "SELECT ?", 42)
	if err != nil {
		panic(err)
	}
	fmt.Println("simple:", num)

	// Indexed params.
	_, err = db.Query(pg.Scan(&num), "SELECT ?0 + ?0", 1)
	if err != nil {
		panic(err)
	}
	fmt.Println("indexed:", num)

	// Named params.
	params := &Params{
		X: 1,
		Y: 1,
	}
	_, err = db.Query(pg.Scan(&num), "SELECT ?x + ?y + ?Sum", params)
	if err != nil {
		panic(err)
	}
	fmt.Println("named:", num)

	// Global params.
	_, err = db.WithParam("z", 1).Query(pg.Scan(&num), "SELECT ?x + ?y + ?z", params)
	if err != nil {
		panic(err)
	}
	fmt.Println("global:", num)

	// Output: simple: 42
	// indexed: 2
	// named: 4
	// global: 3
}

Output:

simple: 42
indexed: 2
named: 4
global: 3

Index

Examples

Variables

var (
	ErrNoRows    = internal.ErrNoRows
	ErrMultiRows = internal.ErrMultiRows
)
var Discard orm.Discard

Discard is used with Query and QueryOne to discard rows.

func Array

func Array(v interface{}) *types.Array

Array accepts a slice and returns a wrapper for working with PostgreSQL array data type.

For struct fields you can use array tag:

Emails  []string `pg:",array"`
Example

Code:

src := []string{"one@example.com", "two@example.com"}
var dst []string
_, err := db.QueryOne(pg.Scan(pg.Array(&dst)), `SELECT ?`, pg.Array(src))
if err != nil {
	panic(err)
}
fmt.Println(dst)

Output:

[one@example.com two@example.com]

func F

func F(field string) types.ValueAppender

F quotes a SQL identifier such as a table or column name replacing any placeholders found in the field.

Example

Code:

db := modelDB()

var book Book
err := db.Model(&book).Where("? = 1", pg.F("id")).Select()
if err != nil {
	panic(err)
}
fmt.Println(book)

Output:

Book<Id=1 Title="book 1">

func Hstore

func Hstore(v interface{}) *types.Hstore

Hstore accepts a map and returns a wrapper for working with hstore data type. Supported map types are:

- map[string]string

For struct fields you can use hstore tag:

Attrs map[string]string `pg:",hstore"`
Example

Code:

src := map[string]string{"hello": "world"}
var dst map[string]string
_, err := db.QueryOne(pg.Scan(pg.Hstore(&dst)), `SELECT ?`, pg.Hstore(src))
if err != nil {
	panic(err)
}
fmt.Println(dst)

Output:

map[hello:world]

func In

func In(slice interface{}) types.ValueAppender

In accepts a slice and returns a wrapper that can be used with PostgreSQL IN operator:

Where("id IN (?)", pg.In([]int{1, 2, 3}))

func Model

func Model(model ...interface{}) *orm.Query

Model returns new query for the optional model.

func Q

func Q(query string, params ...interface{}) orm.FormatAppender

Q replaces any placeholders found in the query.

Example

Code:

db := modelDB()

cond := fmt.Sprintf("id = %d", 1)

var book Book
err := db.Model(&book).Where("?", pg.Q(cond)).Select()
if err != nil {
	panic(err)
}
fmt.Println(book)

Output:

Book<Id=1 Title="book 1">

func Scan

func Scan(values ...interface{}) orm.ColumnScanner

Scan returns ColumnScanner that copies the columns in the row into the values.

Example

Code:

var s1, s2 string
_, err := db.QueryOne(pg.Scan(&s1, &s2), `SELECT ?, ?`, "foo", "bar")
fmt.Println(s1, s2, err)

Output:

foo bar <nil>

func SetLogger

func SetLogger(logger *log.Logger)

func SetQueryLogger

func SetQueryLogger(logger *log.Logger)

SetQueryLogger sets a logger that will be used to log generated queries.

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

Code:

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

var n int
_, err := db.QueryOne(pg.Scan(&n), "SELECT 1")
if err != nil {
	panic(err)
}
fmt.Println(n)

err = db.Close()
if err != nil {
	panic(err)
}

Output:

1

func (*DB) Begin

func (db *DB) Begin() (*Tx, error)

Begin starts a transaction. Most callers should use RunInTransaction instead.

Example

Code:

db := txExample()

tx, err := db.Begin()
if err != nil {
	panic(err)
}

var counter int
_, err = tx.QueryOne(pg.Scan(&counter), `SELECT counter FROM tx_test`)
if err != nil {
	tx.Rollback()
	panic(err)
}

counter++

_, err = tx.Exec(`UPDATE tx_test SET counter = ?`, counter)
if err != nil {
	tx.Rollback()
	panic(err)
}

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

fmt.Println(counter)

Output:

1

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(reader io.Reader, query interface{}, params ...interface{}) (*types.Result, error)

CopyFrom copies data from reader to a table.

Example

Code:

_, 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)
}

var buf bytes.Buffer
_, err = db.CopyTo(&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(writer io.Writer, query interface{}, params ...interface{}) (*types.Result, error)

CopyTo copies data from a table to writer.

func (*DB) CreateTable

func (db *DB) CreateTable(model interface{}, opt *orm.CreateTableOptions) error

CreateTable creates table for the model. It recognizes following field tags:

- notnull - sets NOT NULL constraint.
- unique - sets UNIQUE constraint.
Example

Code:

type Model struct {
	Id   int
	Name string
}

err := db.CreateTable(&Model{}, &orm.CreateTableOptions{
	Temp: true, // create temp table
})
if err != nil {
	panic(err)
}

var info []struct {
	ColumnName string
	DataType   string
}
_, err = db.Query(&info, `
		SELECT column_name, data_type
		FROM information_schema.columns
		WHERE table_name = 'models'
	`)
if err != nil {
	panic(err)
}
fmt.Println(info)

Output:

[{id bigint} {name text}]

func (*DB) Delete

func (db *DB) Delete(model interface{}) error

Delete deletes the model by primary key.

Example

Code:

db := modelDB()

book := Book{
	Title:    "title 1",
	AuthorID: 1,
}
err := db.Insert(&book)
if err != nil {
	panic(err)
}

err = db.Delete(&book)
if err != nil {
	panic(err)
}

err = db.Select(&book)
fmt.Println(err)

Output:

pg: no rows in result set
Example (MultipleRows)

Code:

db := modelDB()

ids := pg.In([]int{1, 2, 3})
res, err := db.Model(&Book{}).Where("id IN (?)", ids).Delete()
if err != nil {
	panic(err)
}
fmt.Println("deleted", res.RowsAffected())

count, err := db.Model(&Book{}).Count()
if err != nil {
	panic(err)
}
fmt.Println("left", count)

Output:

deleted 3
left 0

func (*DB) Exec

func (db *DB) Exec(query interface{}, params ...interface{}) (res *types.Result, err error)

Exec executes a query ignoring returned rows. The params are for any placeholders in the query.

Example

Code:

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

Output:

-1 <nil>

func (*DB) ExecOne

func (db *DB) ExecOne(query interface{}, params ...interface{}) (*types.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) FormatQuery

func (db *DB) FormatQuery(dst []byte, query string, params ...interface{}) []byte

func (*DB) Insert

func (db *DB) Insert(model ...interface{}) error

Insert inserts the model updating primary keys if they are empty.

Example

Code:

db := modelDB()

book := Book{
	Title:    "new book",
	AuthorID: 1,
}

err := db.Insert(&book)
if err != nil {
	panic(err)
}
fmt.Println(book)
// Output: Book<Id=4 Title="new book">

err = db.Delete(&book)
if err != nil {
	panic(err)
}

Output:

Book<Id=4 Title="new book">
Example (BulkInsert)

Code:

db := modelDB()

book1 := Book{
	Title: "new book 1",
}
book2 := Book{
	Title: "new book 2",
}
err := db.Insert(&book1, &book2)
if err != nil {
	panic(err)
}
fmt.Println(book1, book2)
// Output: Book<Id=4 Title="new book 1"> Book<Id=5 Title="new book 2">

for _, book := range []*Book{&book1, &book2} {
	err := db.Delete(book)
	if err != nil {
		panic(err)
	}
}

Output:

Book<Id=4 Title="new book 1"> Book<Id=5 Title="new book 2">
Example (BulkInsert2)

Code:

db := modelDB()

books := []Book{{
	Title: "new book 1",
}, {
	Title: "new book 2",
}}
err := db.Insert(&books)
if err != nil {
	panic(err)
}
fmt.Println(books)
// Output: [Book<Id=4 Title="new book 1"> Book<Id=5 Title="new book 2">]

for i := range books {
	err := db.Delete(&books[i])
	if err != nil {
		panic(err)
	}
}

Output:

[Book<Id=4 Title="new book 1"> Book<Id=5 Title="new book 2">]
Example (OnConflictDoNothing)

Code:

db := modelDB()

book := Book{
	Id:    100,
	Title: "book 100",
}

for i := 0; i < 2; i++ {
	res, err := db.Model(&book).OnConflict("DO NOTHING").Insert()
	if err != nil {
		panic(err)
	}
	if res.RowsAffected() > 0 {
		fmt.Println("created")
	} else {
		fmt.Println("did nothing")
	}
}

err := db.Delete(&book)
if err != nil {
	panic(err)
}

Output:

created
did nothing
Example (OnConflictDoUpdate)

Code:

db := modelDB()

var book *Book
for i := 0; i < 2; i++ {
	book = &Book{
		Id:    100,
		Title: fmt.Sprintf("title version #%d", i),
	}
	_, err := db.Model(book).
		OnConflict("(id) DO UPDATE").
		Set("title = ?title").
		Insert()
	if err != nil {
		panic(err)
	}

	err = db.Select(book)
	if err != nil {
		panic(err)
	}
	fmt.Println(book)
}

err := db.Delete(book)
if err != nil {
	panic(err)
}

Output:

Book<Id=100 Title="title version #0">
Book<Id=100 Title="title version #1">
Example (SelectOrInsert)

Code:

db := modelDB()

author := Author{
	Name: "R. Scott Bakker",
}
created, err := db.Model(&author).
	Column("id").
	Where("name = ?name").
	OnConflict("DO NOTHING"). // OnConflict is optional
	Returning("id").
	SelectOrInsert()
if err != nil {
	panic(err)
}
fmt.Println(created, author)

Output:

true Author<ID=2 Name="R. Scott Bakker">

func (*DB) Listen

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

Listen listens for notifications sent with NOTIFY command.

func (*DB) Model

func (db *DB) Model(model ...interface{}) *orm.Query

Model returns new query for the model.

Example

Code:

package pg_test

import (
	"fmt"

	"gopkg.in/pg.v5"
	"gopkg.in/pg.v5/orm"
)

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

func (u User) String() string {
	return fmt.Sprintf("User<%d %s %v>", u.Id, u.Name, u.Emails)
}

type Story struct {
	Id       int64
	Title    string
	AuthorId int64
	Author   *User
}

func (s Story) String() string {
	return fmt.Sprintf("Story<%d %s %s>", s.Id, s.Title, s.Author)
}

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

	err := createSchema(db)
	if err != nil {
		panic(err)
	}

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

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

	story1 := &Story{
		Title:    "Cool story",
		AuthorId: user1.Id,
	}
	err = db.Insert(story1)
	if err != nil {
		panic(err)
	}

	// Select user by primary key.
	user := User{Id: user1.Id}
	err = db.Select(&user)
	if err != nil {
		panic(err)
	}

	// Select all users.
	var users []User
	err = db.Model(&users).Select()
	if err != nil {
		panic(err)
	}

	// Select story and associated author in one query.
	var story Story
	err = db.Model(&story).
		Column("story.*", "Author").
		Where("story.id = ?", story1.Id).
		Select()
	if err != nil {
		panic(err)
	}

	fmt.Println(user)
	fmt.Println(users)
	fmt.Println(story)
	// Output: User<1 admin [admin1@admin admin2@admin]>
	// [User<1 admin [admin1@admin admin2@admin]> User<2 root [root1@root root2@root]>]
	// Story<1 Cool story User<1 admin [admin1@admin admin2@admin]>>
}

func createSchema(db *pg.DB) error {
	for _, model := range []interface{}{&User{}, &Story{}} {
		err := db.CreateTable(model, &orm.CreateTableOptions{
			Temp: true,
		})
		if err != nil {
			return err
		}
	}
	return nil
}

Output:

User<1 admin [admin1@admin admin2@admin]>
[User<1 admin [admin1@admin admin2@admin]> User<2 root [root1@root root2@root]>]
Story<1 Cool story User<1 admin [admin1@admin admin2@admin]>>
Example (BelongsTo)

Code:

// Profile belongs to User.
type Profile struct {
	Id     int
	Lang   string
	UserId int
}

type User struct {
	Id      int
	Name    string
	Profile *Profile
}

db := connect()
defer db.Close()

qs := []string{
	"CREATE TEMP TABLE users (id int, name text)",
	"CREATE TEMP TABLE profiles (id int, lang text, user_id int)",
	"INSERT INTO users VALUES (1, 'user 1'), (2, 'user 2')",
	"INSERT INTO profiles VALUES (1, 'en', 1), (2, 'ru', 2)",
}
for _, q := range qs {
	_, err := db.Exec(q)
	if err != nil {
		panic(err)
	}
}

// Select users joining their profiles with following query:
//
// SELECT
//   "user".*,
//   "profile"."id" AS "profile__id",
//   "profile"."lang" AS "profile__lang"
// FROM "users" AS "user"
// LEFT JOIN "profiles" AS "profile" ON "profile"."id" = "user"."profile_id"

var users []User
err := db.Model(&users).
	Column("user.*", "Profile").
	Select()
if err != nil {
	panic(err)
}

fmt.Println(len(users), "results")
fmt.Println(users[0].Id, users[0].Name, users[0].Profile)
fmt.Println(users[1].Id, users[1].Name, users[1].Profile)

Output:

2 results
1 user 1 &{1 en 1}
2 user 2 &{2 ru 2}
Example (Count)

Code:

db := modelDB()

count, err := db.Model(&Book{}).Count()
if err != nil {
	panic(err)
}

fmt.Println(count)

Output:

3
Example (CountEstimate)

Code:

db := modelDB()

count, err := db.Model(&Book{}).CountEstimate(0)
if err != nil {
	panic(err)
}

fmt.Println(count)

Output:

3
Example (HasMany)

Code:

type Profile struct {
	Id     int
	Lang   string
	Active bool
	UserId int
}

// User has many profiles.
type User struct {
	Id       int
	Name     string
	Profiles []*Profile
}

db := connect()
defer db.Close()

qs := []string{
	"CREATE TEMP TABLE users (id int, name text)",
	"CREATE TEMP TABLE profiles (id int, lang text, active bool, user_id int)",
	"INSERT INTO users VALUES (1, 'user 1')",
	"INSERT INTO profiles VALUES (1, 'en', TRUE, 1), (2, 'ru', TRUE, 1), (3, 'md', FALSE, 1)",
}
for _, q := range qs {
	_, err := db.Exec(q)
	if err != nil {
		panic(err)
	}
}

// Select user and all his active profiles with following queries:
//
// SELECT "user".* FROM "users" AS "user" ORDER BY "user"."id" LIMIT 1
//
// SELECT "profile".* FROM "profiles" AS "profile"
// WHERE (active IS TRUE) AND (("profile"."user_id") IN ((1)))

var user User
err := db.Model(&user).
	Column("user.*", "Profiles").
	Relation("Profiles", func(q *orm.Query) (*orm.Query, error) {
		return q.Where("active IS TRUE"), nil
	}).
	First()
if err != nil {
	panic(err)
}
fmt.Println(user.Id, user.Name, user.Profiles[0], user.Profiles[1])

Output:

1 user 1 &{1 en true 1} &{2 ru true 1}
Example (HasManySelf)

Code:

type Item struct {
	Id       int
	Items    []Item `pg:",fk:Parent"`
	ParentId int
}

db := connect()
defer db.Close()

qs := []string{
	"CREATE TEMP TABLE items (id int, parent_id int)",
	"INSERT INTO items VALUES (1, NULL), (2, 1), (3, 1)",
}
for _, q := range qs {
	_, err := db.Exec(q)
	if err != nil {
		panic(err)
	}
}

// Select item and all subitems with following queries:
//
// SELECT "item".* FROM "items" AS "item" ORDER BY "item"."id" LIMIT 1
//
// SELECT "item".* FROM "items" AS "item" WHERE (("item"."parent_id") IN ((1)))

var item Item
err := db.Model(&item).Column("item.*", "Items").First()
if err != nil {
	panic(err)
}
fmt.Println("Item", item.Id)
fmt.Println("Subitems", item.Items[0].Id, item.Items[1].Id)

Output:

Item 1
Subitems 2 3
Example (HasOne)

Code:

type Profile struct {
	Id   int
	Lang string
}

// User has one profile.
type User struct {
	Id        int
	Name      string
	ProfileId int
	Profile   *Profile
}

db := connect()
defer db.Close()

qs := []string{
	"CREATE TEMP TABLE users (id int, name text, profile_id int)",
	"CREATE TEMP TABLE profiles (id int, lang text)",
	"INSERT INTO users VALUES (1, 'user 1', 1), (2, 'user 2', 2)",
	"INSERT INTO profiles VALUES (1, 'en'), (2, 'ru')",
}
for _, q := range qs {
	_, err := db.Exec(q)
	if err != nil {
		panic(err)
	}
}

// Select users joining their profiles with following query:
//
// SELECT
//   "user".*,
//   "profile"."id" AS "profile__id",
//   "profile"."lang" AS "profile__lang",
//   "profile"."user_id" AS "profile__user_id"
// FROM "users" AS "user"
// LEFT JOIN "profiles" AS "profile" ON "profile"."user_id" = "user"."id"

var users []User
err := db.Model(&users).
	Column("user.*", "Profile").
	Select()
if err != nil {
	panic(err)
}

fmt.Println(len(users), "results")
fmt.Println(users[0].Id, users[0].Name, users[0].Profile)
fmt.Println(users[1].Id, users[1].Name, users[1].Profile)

Output:

2 results
1 user 1 &{1 en}
2 user 2 &{2 ru}
Example (HstoreStructTag)

Code:

type Item struct {
	Id    int64
	Attrs map[string]string `pg:",hstore"` // marshalled as PostgreSQL hstore
}

_, err := db.Exec(`CREATE TEMP TABLE items (id serial, attrs hstore)`)
if err != nil {
	panic(err)
}
defer db.Exec("DROP TABLE items")

item1 := Item{
	Id:    1,
	Attrs: map[string]string{"hello": "world"},
}
if err := db.Insert(&item1); err != nil {
	panic(err)
}

var item Item
err = db.Model(&item).Where("id = ?", 1).Select()
if err != nil {
	panic(err)
}
fmt.Println(item)

Output:

{1 map[hello:world]}
Example (ManyToMany)

Code:

type Item struct {
	Id    int
	Items []Item `pg:",many2many:item_to_items,joinFK:Sub"`
}

db := connect()
defer db.Close()

qs := []string{
	"CREATE TEMP TABLE items (id int)",
	"CREATE TEMP TABLE item_to_items (item_id int, sub_id int)",
	"INSERT INTO items VALUES (1), (2), (3)",
	"INSERT INTO item_to_items VALUES (1, 2), (1, 3)",
}
for _, q := range qs {
	_, err := db.Exec(q)
	if err != nil {
		panic(err)
	}
}

// Select item and all subitems with following queries:
//
// SELECT "item".* FROM "items" AS "item" ORDER BY "item"."id" LIMIT 1
//
// SELECT * FROM "items" AS "item"
// JOIN "item_to_items" ON ("item_to_items"."item_id") IN ((1))
// WHERE ("item"."id" = "item_to_items"."sub_id")

var item Item
err := db.Model(&item).Column("item.*", "Items").First()
if err != nil {
	panic(err)
}
fmt.Println("Item", item.Id)
fmt.Println("Subitems", item.Items[0].Id, item.Items[1].Id)

Output:

Item 1
Subitems 2 3
Example (NullEmptyValue)

Code:

type Example struct {
	Hello string
}

var str sql.NullString
_, err := db.QueryOne(pg.Scan(&str), "SELECT ?hello", &Example{Hello: ""})
if err != nil {
	panic(err)
}
fmt.Println(str.Valid)

Output:

false
Example (PostgresArrayStructTag)

Code:

type Item struct {
	Id      int64
	Emails  []string `pg:",array"` // marshalled as PostgreSQL array
	Numbers [][]int  `pg:",array"` // marshalled as PostgreSQL array
}

_, err := db.Exec(`CREATE TEMP TABLE items (id serial, emails text[], numbers int[][])`)
if err != nil {
	panic(err)
}
defer db.Exec("DROP TABLE items")

item1 := Item{
	Id:      1,
	Emails:  []string{"one@example.com", "two@example.com"},
	Numbers: [][]int{{1, 2}, {3, 4}},
}
if err := db.Insert(&item1); err != nil {
	panic(err)
}

var item Item
err = db.Model(&item).Where("id = ?", 1).Select()
if err != nil {
	panic(err)
}
fmt.Println(item)

Output:

{1 [one@example.com two@example.com] [[1 2] [3 4]]}
Example (SelectAndCount)

Code:

db := modelDB()

var books []Book
count, err := db.Model(&books).OrderExpr("id ASC").Limit(2).SelectAndCount()
if err != nil {
	panic(err)
}

fmt.Println(count)
fmt.Println(books)

Output:

3
[Book<Id=1 Title="book 1"> Book<Id=2 Title="book 2">]

func (*DB) Options

func (db *DB) Options() *Options

Options returns read-only Options that were used to connect to the DB.

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

Code:

stmt, err := db.Prepare(`SELECT $1::text, $2::text`)
if err != nil {
	panic(err)
}

var s1, s2 string
_, err = stmt.QueryOne(pg.Scan(&s1, &s2), "foo", "bar")
if err != nil {
	panic(err)
}
fmt.Println(s1, s2)

Output:

foo bar

func (*DB) Query

func (db *DB) Query(model, query interface{}, params ...interface{}) (res *types.Result, err error)

Query executes a query that returns rows, typically a SELECT. The params are for any placeholders in the query.

Example

Code:

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

err := createSchema(db)
if err != nil {
	panic(err)
}

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

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

story1 := &Story{
	Title:    "Cool story",
	AuthorId: user1.Id,
}
err = CreateStory(db, story1)

user, err := GetUser(db, user1.Id)
if err != nil {
	panic(err)
}

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

story, err := GetStory(db, story1.Id)
if err != nil {
	panic(err)
}

fmt.Println(user)
fmt.Println(users)
fmt.Println(story)

Output:

User<1 admin [admin1@admin admin2@admin]>
[User<1 admin [admin1@admin admin2@admin]> User<2 root [root1@root root2@root]>]
Story<1 Cool story User<1 admin [admin1@admin admin2@admin]>>

func (*DB) QueryOne

func (db *DB) QueryOne(model, query interface{}, params ...interface{}) (*types.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

Code:

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.RowsAffected())
fmt.Println(user)

Output:

1
{admin}
Example (Returning_id)

Code:

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

var user struct {
	Id   int32
	Name string
}
user.Name = "admin"

_, err = db.QueryOne(&user, `
        INSERT INTO users (name) VALUES (?name) RETURNING id
    `, &user)
if err != nil {
	panic(err)
}
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.

Example

Code:

db := txExample()

var counter int
// Transaction is automatically rollbacked on error.
err := db.RunInTransaction(func(tx *pg.Tx) error {
	_, err := tx.QueryOne(pg.Scan(&counter), `SELECT counter FROM tx_test`)
	if err != nil {
		return err
	}

	counter++

	_, err = tx.Exec(`UPDATE tx_test SET counter = ?`, counter)
	return err
})
if err != nil {
	panic(err)
}

fmt.Println(counter)

Output:

1

func (*DB) Select

func (db *DB) Select(model interface{}) error

Select selects the model by primary key.

Example

Code:

db := modelDB()

book := Book{
	Id: 1,
}
err := db.Select(&book)
if err != nil {
	panic(err)
}
fmt.Println(book)

Output:

Book<Id=1 Title="book 1">
Example (AllColumns)

Code:

db := modelDB()

var book Book
err := db.Model(&book).Column("book.*").First()
if err != nil {
	panic(err)
}
fmt.Println(book, book.AuthorID)

Output:

Book<Id=1 Title="book 1"> 1
Example (ApplyFunc)

Code:

db := modelDB()

var authorId int
var editorId int

filter := func(q *orm.Query) (*orm.Query, error) {
	if authorId != 0 {
		q = q.Where("author_id = ?", authorId)
	}
	if editorId != 0 {
		q = q.Where("editor_id = ?", editorId)
	}
	return q, nil
}

var books []Book
authorId = 1
err := db.Model(&books).
	Apply(filter).
	Select()
if err != nil {
	panic(err)
}
fmt.Println(books)

Output:

[Book<Id=1 Title="book 1"> Book<Id=2 Title="book 2">]
Example (FirstRow)

Code:

db := modelDB()

var firstBook Book
err := db.Model(&firstBook).First()
if err != nil {
	panic(err)
}
fmt.Println(firstBook)

Output:

Book<Id=1 Title="book 1">
Example (GroupBy)

Code:

db := modelDB()

var res []struct {
	AuthorId  int
	BookCount int
}

err := db.Model(&Book{}).
	Column("author_id").
	ColumnExpr("count(*) AS book_count").
	Group("author_id").
	OrderExpr("book_count DESC").
	Select(&res)
if err != nil {
	panic(err)
}
fmt.Println("len", len(res))
fmt.Printf("author %d has %d books\n", res[0].AuthorId, res[0].BookCount)
fmt.Printf("author %d has %d books\n", res[1].AuthorId, res[1].BookCount)

Output:

len 2
author 1 has 2 books
author 11 has 1 books
Example (LastRow)

Code:

db := modelDB()

var lastBook Book
err := db.Model(&lastBook).Last()
if err != nil {
	panic(err)
}
fmt.Println(lastBook)

Output:

Book<Id=3 Title="book 3">
Example (SomeColumns)

Code:

db := modelDB()

var book Book
err := db.Model(&book).
	Column("book.id", "book.title").
	OrderExpr("book.id ASC").
	Limit(1).
	Select()
if err != nil {
	panic(err)
}

fmt.Println(book)

Output:

Book<Id=1 Title="book 1">
Example (SomeColumnsIntoVars)

Code:

db := modelDB()

var id int
var title string
err := db.Model(&Book{}).
	Column("book.id", "book.title").
	OrderExpr("book.id ASC").
	Limit(1).
	Select(&id, &title)
if err != nil {
	panic(err)
}

fmt.Println(id, title)

Output:

1 book 1
Example (SqlExpression)

Code:

db := modelDB()

var ids []int
err := db.Model(&Book{}).
	ColumnExpr("array_agg(book.id)").
	Select(pg.Array(&ids))
if err != nil {
	panic(err)
}
fmt.Println(ids)

Output:

[1 2 3]
Example (WhereIn)

Code:

db := modelDB()

var books []Book
err := db.Model(&books).WhereIn("id IN (?)", 1, 2).Select()
if err != nil {
	panic(err)
}
fmt.Println(books)

Output:

[Book<Id=1 Title="book 1"> Book<Id=2 Title="book 2">]
Example (With)

Code:

authorBooks := db.Model(&Book{}).Where("author_id = ?", 1)

var books []Book
err := db.Model().
	With("author_books", authorBooks).
	Table("author_books").
	Select(&books)
if err != nil {
	panic(err)
}
fmt.Println(books)

Output:

[Book<Id=1 Title="book 1"> Book<Id=2 Title="book 2">]
Example (WrapWith)

Code:

// WITH author_books AS (
//     SELECT * books WHERE author_id = 1
// )
// SELECT * FROM author_books
var books []Book
err := db.Model(&books).
	Where("author_id = ?", 1).
	WrapWith("author_books").
	Table("author_books").
	Select(&books)
if err != nil {
	panic(err)
}
fmt.Println(books)

Output:

[Book<Id=1 Title="book 1"> Book<Id=2 Title="book 2">]

func (*DB) String

func (db *DB) String() string

func (*DB) Update

func (db *DB) Update(model interface{}) error

Update updates the model by primary key.

Example

Code:

db := modelDB()

err := db.Update(&Book{
	Id:    1,
	Title: "updated book 1",
})
if err != nil {
	panic(err)
}

var book Book
err = db.Model(&book).Where("id = ?", 1).Select()
if err != nil {
	panic(err)
}

fmt.Println(book)

Output:

Book<Id=1 Title="updated book 1">
Example (SetValues)

Code:

db := modelDB()

var book Book
_, err := db.Model(&book).
	Set("title = concat(?, title, ?)", "prefix ", " suffix").
	Where("id = ?", 1).
	Returning("*").
	Update()
if err != nil {
	panic(err)
}

fmt.Println(book)

Output:

Book<Id=1 Title="prefix book 1 suffix">
Example (SomeColumns)

Code:

db := modelDB()

book := Book{
	Id:       1,
	Title:    "updated book 1", // only this column is going to be updated
	AuthorID: 2,
}
_, err := db.Model(&book).Column("title").Returning("*").Update()
if err != nil {
	panic(err)
}

fmt.Println(book, book.AuthorID)

Output:

Book<Id=1 Title="updated book 1"> 1
Example (SomeColumns2)

Code:

db := modelDB()

book := Book{
	Id:       1,
	Title:    "updated book 1",
	AuthorID: 2, // this column will not be updated
}
_, err := db.Model(&book).Set("title = ?title").Returning("*").Update()
if err != nil {
	panic(err)
}

fmt.Println(book, book.AuthorID)

Output:

Book<Id=1 Title="updated book 1"> 1

func (*DB) WithParam

func (db *DB) WithParam(param string, value interface{}) *DB

WithParam returns a DB that replaces the param with the value in queries.

func (*DB) WithTimeout

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

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

Example

Code:

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

type Error

type Error interface {
	Field(byte) string
	IntegrityViolation() bool
}

type IntSet

type IntSet map[int64]struct{}

func (IntSet) AddModel

func (IntSet) AddModel(_ orm.ColumnScanner) error

func (IntSet) AfterDelete

func (IntSet) AfterDelete(_ orm.DB) error

func (IntSet) AfterInsert

func (IntSet) AfterInsert(_ orm.DB) error

func (IntSet) AfterQuery

func (IntSet) AfterQuery(_ orm.DB) error

func (IntSet) AfterSelect

func (IntSet) AfterSelect(_ orm.DB) error

func (IntSet) AfterUpdate

func (IntSet) AfterUpdate(_ orm.DB) error

func (IntSet) BeforeDelete

func (IntSet) BeforeDelete(_ orm.DB) error

func (IntSet) BeforeInsert

func (IntSet) BeforeInsert(_ orm.DB) error

func (IntSet) BeforeUpdate

func (IntSet) BeforeUpdate(_ orm.DB) error

func (*IntSet) NewModel

func (set *IntSet) NewModel() orm.ColumnScanner

func (*IntSet) Reset

func (set *IntSet) Reset() error

func (*IntSet) ScanColumn

func (setptr *IntSet) ScanColumn(colIdx int, colName string, b []byte) error

type Ints

type Ints []int64
Example

Code:

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) AddModel

func (Ints) AddModel(_ orm.ColumnScanner) error

func (Ints) AfterDelete

func (Ints) AfterDelete(_ orm.DB) error

func (Ints) AfterInsert

func (Ints) AfterInsert(_ orm.DB) error

func (Ints) AfterQuery

func (Ints) AfterQuery(_ orm.DB) error

func (Ints) AfterSelect

func (Ints) AfterSelect(_ orm.DB) error

func (Ints) AfterUpdate

func (Ints) AfterUpdate(_ orm.DB) error

func (Ints) AppendValue

func (ints Ints) AppendValue(dst []byte, quote int) ([]byte, error)

func (Ints) BeforeDelete

func (Ints) BeforeDelete(_ orm.DB) error

func (Ints) BeforeInsert

func (Ints) BeforeInsert(_ orm.DB) error

func (Ints) BeforeUpdate

func (Ints) BeforeUpdate(_ orm.DB) error

func (*Ints) NewModel

func (ints *Ints) NewModel() orm.ColumnScanner

func (*Ints) Reset

func (ints *Ints) Reset() error

func (*Ints) ScanColumn

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

type Listener

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

Listener listens for notifications sent with NOTIFY command. It's NOT safe for concurrent use by multiple goroutines except the Channel API.

Example

Code:

ln := db.Listen("mychan")
defer ln.Close()

ch := ln.Channel()

go func() {
	_, err := db.Exec("NOTIFY mychan, ?", "hello world")
	if err != nil {
		panic(err)
	}
}()

notif := <-ch
fmt.Println(notif)

Output:

&{mychan hello world}

func (*Listener) Channel

func (ln *Listener) Channel() <-chan *Notification

Channel returns a channel for concurrently receiving notifications. The channel is closed with Listener.

func (*Listener) Close

func (ln *Listener) Close() error

Close closes the listener, releasing any open resources.

func (*Listener) Listen

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

Listen starts listening for notifications on channels.

func (*Listener) Receive

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

Receive indefinitely waits for a notification.

func (*Listener) ReceiveTimeout

func (ln *Listener) ReceiveTimeout(timeout time.Duration) (channel, payload string, err error)

ReceiveTimeout waits for a notification until timeout is reached.

type Notification

type Notification struct {
	Channel string
	Payload string
}

A notification received with LISTEN command.

type NullTime

type NullTime struct {
	time.Time
}

NullTime is a time.Time wrapper that marshals zero time as JSON null and PostgreSQL NULL.

func (NullTime) AppendValue

func (tm NullTime) AppendValue(b []byte, quote int) ([]byte, error)

func (NullTime) MarshalJSON

func (tm NullTime) MarshalJSON() ([]byte, error)

func (*NullTime) Scan

func (tm *NullTime) Scan(b interface{}) error

func (*NullTime) UnmarshalJSON

func (tm *NullTime) UnmarshalJSON(b []byte) error

type Options

type Options struct {
	// Network type, either tcp or unix.
	// Default is tcp.
	Network string
	// TCP host:port or Unix socket depending on Network.
	Addr string

	// Dialer creates new network connection and has priority over
	// Network and Addr options.
	Dialer func(network, addr string) (net.Conn, error)

	User     string
	Password string
	Database string

	// TLS config for secure connections.
	TLSConfig *tls.Config

	// Maximum number of retries before giving up.
	// Default is to not retry failed queries.
	MaxRetries int
	// Whether to retry queries cancelled because of statement_timeout.
	RetryStatementTimeout bool

	// Dial timeout for establishing new connections.
	// Default is 5 seconds.
	DialTimeout time.Duration
	// Timeout for socket reads. If reached, commands will fail
	// with a timeout instead of blocking.
	ReadTimeout time.Duration
	// Timeout for socket writes. If reached, commands will fail
	// with a timeout instead of blocking.
	WriteTimeout time.Duration

	// Maximum number of socket connections.
	// Default is 20 connections.
	PoolSize int
	// Time for which client waits for free connection if all
	// connections are busy before returning an error.
	// Default is 5 seconds.
	PoolTimeout time.Duration
	// Time after which client closes idle connections.
	// Default is to not close idle connections.
	IdleTimeout time.Duration
	// Connection age at which client retires (closes) the connection.
	// Primarily useful with proxies like HAProxy.
	// Default is to not close aged connections.
	MaxAge time.Duration
	// Frequency of idle checks.
	// Default is 1 minute.
	IdleCheckFrequency time.Duration

	// When true Tx does not issue BEGIN, COMMIT, or ROLLBACK.
	// Also underlying database connection is immediately returned to the pool.
	// This is primarily useful for running your database tests in one big
	// transaction, because PostgreSQL does not support nested transactions.
	DisableTransaction bool
}

Database connection options.

func ParseURL

func ParseURL(sURL string) (*Options, error)

ParseURL parses an URL into options that can be used to connect to PostgreSQL.

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

Exec executes a prepared statement with the given parameters.

func (*Stmt) ExecOne

func (stmt *Stmt) ExecOne(params ...interface{}) (*types.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(model interface{}, params ...interface{}) (res *types.Result, err error)

Query executes a prepared query statement with the given parameters.

func (*Stmt) QueryOne

func (stmt *Stmt) QueryOne(model interface{}, params ...interface{}) (*types.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

Code:

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) AddModel

func (Strings) AddModel(_ orm.ColumnScanner) error

func (Strings) AfterDelete

func (Strings) AfterDelete(_ orm.DB) error

func (Strings) AfterInsert

func (Strings) AfterInsert(_ orm.DB) error

func (Strings) AfterQuery

func (Strings) AfterQuery(_ orm.DB) error

func (Strings) AfterSelect

func (Strings) AfterSelect(_ orm.DB) error

func (Strings) AfterUpdate

func (Strings) AfterUpdate(_ orm.DB) error

func (Strings) AppendValue

func (strings Strings) AppendValue(dst []byte, quote int) ([]byte, error)

func (Strings) BeforeDelete

func (Strings) BeforeDelete(_ orm.DB) error

func (Strings) BeforeInsert

func (Strings) BeforeInsert(_ orm.DB) error

func (Strings) BeforeUpdate

func (Strings) BeforeUpdate(_ orm.DB) error

func (*Strings) NewModel

func (strings *Strings) NewModel() orm.ColumnScanner

func (*Strings) Reset

func (strings *Strings) Reset() error

func (*Strings) ScanColumn

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

type Tx

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

Tx is an in-progress database transaction.

A transaction must end with a call to Commit or Rollback.

After a call to Commit or Rollback, all operations on the transaction fail with ErrTxDone.

The statements prepared for a transaction by calling the transaction's Prepare or Stmt methods are closed by the call to Commit or Rollback.

func (*Tx) Begin

func (tx *Tx) Begin() (*Tx, error)

Begin returns the transaction.

func (*Tx) Commit

func (tx *Tx) Commit() error

Commit commits the transaction.

func (*Tx) CopyFrom

func (tx *Tx) CopyFrom(r io.Reader, query string, params ...interface{}) (*types.Result, error)

CopyFrom copies data from reader to a table.

func (*Tx) CreateTable

func (tx *Tx) CreateTable(model interface{}, opt *orm.CreateTableOptions) error

CreateTable creates table for the model. It recognizes following field tags:

- notnull - sets NOT NULL constraint.
- unique - sets UNIQUE constraint.

func (*Tx) Delete

func (tx *Tx) Delete(model interface{}) error

Delete deletes the model by primary key.

func (*Tx) Exec

func (tx *Tx) Exec(query interface{}, params ...interface{}) (*types.Result, error)

Exec executes a query with the given parameters in a transaction.

func (*Tx) ExecOne

func (tx *Tx) ExecOne(query interface{}, params ...interface{}) (*types.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 (*Tx) FormatQuery

func (tx *Tx) FormatQuery(dst []byte, query string, params ...interface{}) []byte

func (*Tx) Insert

func (tx *Tx) Insert(model ...interface{}) error

Insert inserts the model updating primary keys if they are empty.

func (*Tx) Model

func (tx *Tx) Model(model ...interface{}) *orm.Query

Model returns new query for the model.

func (*Tx) Prepare

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

Prepare creates a prepared statement for use within a transaction.

The returned statement operates within the transaction and can no longer be used once the transaction has been committed or rolled back.

To use an existing prepared statement on this transaction, see Tx.Stmt.

func (*Tx) Query

func (tx *Tx) Query(model interface{}, query interface{}, params ...interface{}) (*types.Result, error)

Query executes a query with the given parameters in a transaction.

func (*Tx) QueryOne

func (tx *Tx) QueryOne(model interface{}, query interface{}, params ...interface{}) (*types.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.

func (*Tx) Rollback

func (tx *Tx) Rollback() error

Rollback aborts the transaction.

func (*Tx) RunInTransaction

func (tx *Tx) RunInTransaction(fn func(*Tx) error) error

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

func (*Tx) Select

func (tx *Tx) Select(model interface{}) error

Select selects the model by primary key.

func (*Tx) Stmt

func (tx *Tx) Stmt(stmt *Stmt) *Stmt

Stmt returns a transaction-specific prepared statement from an existing statement.

func (*Tx) Update

func (tx *Tx) Update(model interface{}) error

Update updates the model by primary key.

Documentation was rendered with GOOS=linux and GOARCH=amd64.

Jump to identifier

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to identifier