pg

package module
v4.1.0+incompatible Latest Latest
Warning

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

 Go to latest Published: May 2, 2016 License: BSD-3-Clause Imports: 16 Imported by: 0

README

PostgreSQL client for Golang Build Status

Supports:

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

Table of contents

Installation

Install:

go get gopkg.in/pg.v4

Quickstart

package pg_test

import (
	"fmt"

	"gopkg.in/pg.v4"
)

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 createSchema(db *pg.DB) error {
	queries := []string{
		`CREATE TEMP TABLE users (id serial, name text, emails jsonb)`,
		`CREATE TEMP TABLE stories (id serial, title text, author_id bigint)`,
	}
	for _, q := range queries {
		_, err := db.Exec(q)
		if err != nil {
			return err
		}
	}
	return nil
}

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.Create(user1)
	if err != nil {
		panic(err)
	}

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

	story1 := &Story{
		Title:    "Cool story",
		AuthorId: user1.Id,
	}
	err = db.Create(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]>>
}

Howto

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

Model definition

Models are defined using Go structs. Order of the struct fields usually does not matter with the only exception being primary key(s) that must defined before any other fields. Otherwise table relationshipts can be recognized incorrectly.

type Genre struct {
	TableName struct{} `sql:"genres"` // specifies custom table name

	Id     int // Id is automatically detected as primary key
	Name   string
	Rating int `sql:"-"` // - is used to ignore field

	Books []Book `pg:",many2many:book_genres"` // many to many relation

	ParentId  int     `sql:",null"`
	Subgenres []Genre `pg:",fk:Parent"` // fk specifies prefix for foreign key (ParentId)
}

func (g Genre) String() string {
	return fmt.Sprintf("Genre<Id=%d Name=%q>", g.Id, g.Name)
}

type Author struct {
	ID    int // both "Id" and "ID" are detected as primary key
	Name  string
	Books []Book // has many relation
}

func (a Author) String() string {
	return fmt.Sprintf("Author<ID=%d Name=%q>", a.ID, a.Name)
}

type BookGenre struct {
	BookId  int `sql:",pk"` // pk tag is used to mark field as primary key
	GenreId int `sql:",pk"`

	Genre_Rating int // belongs to and is copied to Genre model
}

type Book struct {
	Id        int
	Title     string
	AuthorID  int
	Author    *Author // has one relation
	EditorID  int
	Editor    *Author // has one relation
	CreatedAt time.Time

	Genres       []Genre       `pg:",many2many:book_genres" gorm:"many2many:book_genres;"` // many to many relation
	Translations []Translation // has many relation
	Comments     []Comment     `pg:",polymorphic:Trackable"` // has many polymorphic relation
}

func (b Book) String() string {
	return fmt.Sprintf("Book<Id=%d Title=%q>", b.Id, b.Title)
}

type Translation struct {
	Id     int
	BookId int
	Book   *Book // belongs to relation
	Lang   string

	Comments []Comment `pg:",polymorphic:Trackable"` // has many polymorphic relation
}

type Comment struct {
	TrackableId   int    `sql:",pk"` // Book.Id or Translation.Id
	TrackableType string `sql:",pk"` // "book" or "translation"
	Text          string
}

Writing queries

Select
// Select book by primary key.
err := db.Select(&book)
// SELECT * FROM "books" WHERE id = 1

// Select book title and text.
var title, text string
err := db.Model(&Book{}).Column("title", "text").Where("id = ?", 1).Select(&title, &text)
// SELECT "title", "text" FROM "books" WHERE id = 1

// Select book using WHERE.
err := db.Model(&book).
    Where("id > ?", 100).
    Where("title LIKE ?", "my%").
    Limit(1).
    Select()
// SELECT * FROM "books" WHERE (id > 100) AND (title LIKE 'my%') LIMIT 1

// Select first 20 books.
err := db.Model(&books).Order("id ASC").Limit(20).Select()
// SELECT * FROM "books" ORDER BY id ASC LIMIT 20

// Count books.
count, err := db.Model(&Book{}).Count()
// SELECT COUNT(*) FROM "books"

// Select 20 books and count all books.
count, err := db.Model(&books).Limit(20).SelectAndCount()
// SELECT * FROM "books" LIMIT 20
// SELECT COUNT(*) FROM "books"

// Select 20 books and count estimated number of books.
count, err := db.Model(&books).Limit(20).SelectAndCountEstimate(100000)
// SELECT * FROM "books" LIMIT 20
// EXPLAIN SELECT 2147483647 FROM "books"
// SELECT COUNT(*) FROM "books"

// Select author id and number of books.
var res []struct {
    AuthorId  int
    BookCount int
}
err := db.Model(&Book{}).
    Column("author_id").
    ColumnExpr("count(*) AS book_count").
    Group("author_id").
    Order("book_count DESC").
    Select(&res)
// SELECT "author_id", count(*) AS book_count
// FROM "books" AS "book"
// GROUP BY author_id
// ORDER BY book_count DESC

// Select book ids as PostgreSQL array.
var ids []int
err := db.Model(&Book{}).ColumnExpr("array_agg(id)").Select(pg.Array(&ids))
// SELECT array_agg(id) FROM "books"
Insert
// Insert new book returning primary keys.
err := db.Create(&book)
// INSERT INTO "books" (title, text) VALUES ('my title', 'my text') RETURNING "id"

// Insert new book returning all columns.
err := db.Model(&book).Returning("*").Create()
// INSERT INTO "books" (title, text) VALUES ('my title', 'my text') RETURNING *

// Select existing book by name or create new book.
err := db.Model(&book).
    Where("title = ?title").
    OnConflict("DO NOTHING"). // optional
    SelectOrCreate()
// 1. SELECT * FROM "books" WHERE title = 'my title'
// 2. INSERT INTO "books" (title, text) VALUES ('my title', 'my text') RETURNING "id"
// 3. go to step 1 on error

// Create new book or update existing one.
_, err := db.Model(book).
    OnConflict("(id) DO UPDATE").
    Set("title = ?title").
    Create()
// INSERT INTO "books" ("id", "title") VALUES (100, 'my title')
// ON CONFLICT (id) DO UPDATE SET title = 'title version #1'
Update
// Update all columns except primary keys.
err := db.Update(&book)
// UPDATE "books" SET title = 'my title', text = 'my text' WHERE id = 1

// Update only column "title".
res, err := db.Model(&book).Set("title = ?title").Where("id = ?id").Update()
// UPDATE "books" SET title = 'my title' WHERE id = 1

// Update only column "title".
res, err := db.Model(&book).Column("title").Update()
// UPDATE "books" SET title = 'my title' WHERE id = 1

// Update only column "title".
res, err := db.Model(&book).UpdateValues(map[string]interface{}{
    "title": book.Title,
})
// UPDATE "books" SET title = 'my title' WHERE id = 1
Delete
// Delete book by primary key.
err := db.Delete(&book)
// DELETE FROM "books" WHERE id = 1

// Delete book by title.
res, err := db.Model(&book).Where("title = ?title").Delete()
// DELETE FROM "books" WHERE title = 'my title'

FAQ

Why go-pg

  • No rows.Close to manually manage connections.

  • go-pg automatically maps rows on Go structs and slice.

  • go-pg is 2x-10x faster than GORM on querying 100 rows from table.

    BenchmarkQueryRowsGopgOptimized-4	   10000	    158443 ns/op	   83432 B/op	     625 allocs/op
BenchmarkQueryRowsGopgReflect-4  	   10000	    189014 ns/op	   94759 B/op	     826 allocs/op
BenchmarkQueryRowsGopgORM-4      	   10000	    199685 ns/op	   95024 B/op	     830 allocs/op
BenchmarkQueryRowsStdlibPq-4     	    5000	    242135 ns/op	  161646 B/op	    1324 allocs/op
BenchmarkQueryRowsGORM-4         	    2000	    704366 ns/op	  396184 B/op	    6767 allocs/op

  • go-pg generates much more effecient queries for joins.

    Has one relation
    BenchmarkModelHasOneGopg-4                 	    5000	    313091 ns/op	   78481 B/op	    1290 allocs/op
BenchmarkModelHasOneGORM-4                 	     500	   3849634 ns/op	 1529982 B/op	   71636 allocs/op

    go-pg:

    db.Model(&books).Column("book.*", "Author").Limit(100).Select()

    SELECT "book".*, "author"."id" AS "author__id", "author"."name" AS "author__name"
FROM "books" AS "book"
LEFT JOIN "authors" AS "author" ON "author"."id" = "book"."author_id"
LIMIT 100

    GORM:

    db.Preload("Author").Limit(100).Find(&books).Error

    SELECT  * FROM "books"   LIMIT 100
SELECT  * FROM "authors"  WHERE ("id" IN ('1','2'...'100'))
    Has many relation
    BenchmarkModelHasManyGopg-4                	     500	   3034576 ns/op	  409288 B/op	    8700 allocs/op
BenchmarkModelHasManyGORM-4                	      50	  24677309 ns/op	10121839 B/op	  519411 allocs/op

    go-pg:

    db.Model(&books).Column("book.*", "Translations").Limit(100).Select()

     SELECT "book".* FROM "books" AS "book" LIMIT 100
 SELECT "translation".* FROM "translations" AS "translation"
 WHERE ("translation"."book_id") IN ((100), (101), ... (199));

    GORM:

    db.Preload("Translations").Limit(100).Find(&books).Error

    SELECT * FROM "books" LIMIT 100;
SELECT * FROM "translations"
WHERE ("book_id" IN (1, 2, ..., 100));
SELECT * FROM "authors" WHERE ("book_id" IN (1, 2, ..., 100));
    Many to many relation
    BenchmarkModelHasMany2ManyGopg-4           	     500	   3329123 ns/op	  394738 B/op	    9739 allocs/op
BenchmarkModelHasMany2ManyGORM-4           	     200	   7847630 ns/op	 3350304 B/op	   66239 allocs/op

    go-pg:

    db.Model(&books).Column("book.*", "Genres").Limit(100).Select()

    SELECT "book"."id" FROM "books" AS "book" LIMIT 100;
SELECT * FROM "genres" AS "genre"
JOIN "book_genres" AS "book_genre" ON ("book_genre"."book_id") IN ((1), (2), ..., (100))
WHERE "genre"."id" = "book_genre"."genre_id";

    GORM:

    db.Preload("Genres").Limit(100).Find(&books).Error

    SELECT * FROM "books" LIMIT 100;
SELECT * FROM "genres"
INNER JOIN "book_genres" ON "book_genres"."genre_id" = "genres"."id"
WHERE ("book_genres"."book_id" IN ((1), (2), ..., (100)));
How can I view queries this library generates?

You can configure PostgreSQL to log every query by adding following lines to your postgresql.conf (usually /etc/postgresql/9.5/main/postgresql.conf):

log_statement = 'all'
log_min_duration_statement = 0

Then just tail the log file:

tail -f /var/log/postgresql/postgresql-9.5-main.log

Documentation

Overview

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

Index

Examples

Constants

This section is empty.

Variables

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

Discard is used with Query and QueryOne to discard rows.

Functions

func Array 

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

Array returns an Array type that represents PostgreSQL array of any type.

Example 
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, params ...interface{}) types.F

F returns a ValueAppender that represents SQL identifier, e.g. table or column name.

Example 
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 Q 

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

Q returns a ValueAppender that represents safe SQL query.

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

Types

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)

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

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

CopyTo copies data from a table to writer.

func (*DB) Create 

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

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

Example 
db := modelDB()

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

err := db.Create(&book)
if err != nil {
	panic(err)
}
fmt.Println(book)

Output:

Book<Id=4 Title="new book">
Example (OnConflictDoNothing) 
db := modelDB()

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

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

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

Output:

created
did nothing
Example (OnConflictDoUpdate) 
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").
		Create()
	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 (SelectOrCreate) 
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").
	SelectOrCreate()
if err != nil {
	panic(err)
}
fmt.Println(created, author)

Output:

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

func (*DB) Delete 

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

Delete deletes the model by primary key.

Example 
db := modelDB()

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

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

err = db.Delete(book)
fmt.Println(err)

Output:

pg: no rows in result set
Example (MultipleRows) 
db := modelDB()

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

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 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(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) Listen 

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

Listen listens for notifications sent by NOTIFY statement.

func (*DB) Model 

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

Model returns new query for the model.

Example 
package main

import (
	"fmt"

	"gopkg.in/pg.v4"
)

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 createSchema(db *pg.DB) error {
	queries := []string{
		`CREATE TEMP TABLE users (id serial, name text, emails jsonb)`,
		`CREATE TEMP TABLE stories (id serial, title text, author_id bigint)`,
	}
	for _, q := range queries {
		_, err := db.Exec(q)
		if err != nil {
			return err
		}
	}
	return nil
}

func main() {
	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.Create(user1)
	if err != nil {
		panic(err)
	}

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

	story1 := &Story{
		Title:    "Cool story",
		AuthorId: user1.Id,
	}
	err = db.Create(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]>>
Example (Count) 
db := modelDB()

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

fmt.Println(count)

Output:

3
Example (CountEstimate) 
db := modelDB()

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

fmt.Println(count)

Output:

3
Example (HasMany) 
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 using 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) 
type Item struct {
	Id int

	Sub   *Item
	SubId int
}

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

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

// Select items and join subitem using following query:
//
// SELECT "item".*, "sub"."id" AS "sub__id", "sub"."sub_id" AS "sub__sub_id"
// FROM "items" AS "item"
// LEFT JOIN "items" AS "sub" ON "sub"."id" = item."sub_id"
// WHERE (item.sub_id IS NOT NULL)

var items []Item
err := db.Model(&items).
	Column("item.*", "Sub").
	Where("item.sub_id IS NOT NULL").
	Select()
if err != nil {
	panic(err)
}

fmt.Printf("found %d items\n", len(items))
fmt.Printf("item %d, subitem %d\n", items[0].Id, items[0].Sub.Id)
fmt.Printf("item %d, subitem %d\n", items[1].Id, items[1].Sub.Id)

Output:

found 2 items
item 2, subitem 1
item 4, subitem 2
Example (ManyToMany) 
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 using 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) 
type Example struct {
	Hello string `sql:",null"`
}

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

item1 := Item{
	Id:      1,
	Emails:  []string{"one@example.com", "two@example.com"},
	Numbers: []int{123, 321},
}
if err := db.Create(&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] [123 321]}
Example (SelectAndCount) 
db := modelDB()

var books []Book
count, err := db.Model(&books).Order("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 
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")
fmt.Println(s1, s2, err)

Output:

foo bar <nil>

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 placeholder parameters in the query.

Example 
package main

import (
	"fmt"

	"gopkg.in/pg.v4"
)

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 CreateStory(db *pg.DB, story *Story) error {
	_, err := db.QueryOne(story, `
		INSERT INTO stories (title, author_id) VALUES (?title, ?author_id)
		RETURNING id
	`, story)
	return err
}

// GetStory returns story with associated author.
func GetStory(db *pg.DB, id int64) (*Story, error) {
	var story Story
	_, err := db.QueryOne(&story, `
		SELECT s.*,
			u.id AS author__id, u.name AS author__name, u.emails AS author__emails
		FROM stories AS s, users AS u
		WHERE s.id = ? AND u.id = s.author_id
	`, id)
	return &story, err
}

func main() {
	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 
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}
Example (Returning_id) 
_, 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 
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 
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) 
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 (FirstRow) 
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) 
db := modelDB()

var res []struct {
	AuthorId  int
	BookCount int
}

err := db.Model(&Book{}).
	Column("author_id").
	ColumnExpr("count(*) AS book_count").
	Group("author_id").
	Order("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) 
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) 
db := modelDB()

var book Book
err := db.Model(&book).
	Column("book.id").
	First()
if err != nil {
	panic(err)
}

fmt.Println(book)

Output:

Book<Id=1 Title="">
Example (SomeColumnsIntoVars) 
db := modelDB()

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

fmt.Println(id, title)

Output:

1 book 1
Example (SqlExpression) 
db := modelDB()

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

Output:

[1 2 3]

func (*DB) Update 

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

Update updates the model by primary key.

Example 
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 (MultipleRows) 
db := modelDB()

ids := pg.Ints{1, 2}
data := map[string]interface{}{
	"title": pg.Q("concat(?, title, ?)", "prefix ", " suffix"),
}

var books []Book
_, err := db.Model(&books).
	Where("id IN (?)", ids).
	Returning("*").
	UpdateValues(data)
if err != nil {
	panic(err)
}

fmt.Println(books)

Output:

[Book<Id=1 Title="prefix book 1 suffix"> Book<Id=2 Title="prefix book 2 suffix">]
Example (SetValues) 
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) 
db := modelDB()

book := Book{
	Id:       1,
	Title:    "updated book 1",
	AuthorID: 2, // this column will not be updated
}
_, 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) 
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
Example (UpdateValues) 
db := modelDB()

id := 1
data := map[string]interface{}{
	"title": pg.Q("concat(?, title, ?)", "prefix ", " suffix"),
}
var book Book
_, err := db.Model(&book).
	Where("id = ?", id).
	Returning("*").
	UpdateValues(data)
if err != nil {
	panic(err)
}

fmt.Println(book)

Output:

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

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.Scan(&count), `
		SELECT count(*) FROM big_table
	`)
if err != nil {
	panic(err)
}

Output:

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

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

func (*IntSet) ScanColumn 

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

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>
Example (In) 
ids := pg.Ints{1, 2, 3}
q := pg.Q(`SELECT * FROM table WHERE id IN (?)`, ids)
fmt.Println(string(q))

Output:

SELECT * FROM table WHERE id IN (1,2,3)

func (Ints) AddModel 

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

func (Ints) AppendValue 

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

func (*Ints) NewModel 

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

func (*Ints) ScanColumn 

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

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
	// TCP host:port or Unix socket depending on Network.
	Addr     string
	User     string
	Password string
	Database string
	// Whether to use secure TCP/IP connections (TLS).
	SSL bool

	// PostgreSQL run-time configuration parameters to be set on connection.
	Params map[string]interface{}

	// The maximum number of retries before giving up.
	// Default is to not retry failed queries.
	MaxRetries int

	// 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 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 
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) AppendValue 

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

func (*Strings) NewModel 

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

func (*Strings) ScanColumn 

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

type Tx 

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

Not thread-safe.

func (*Tx) Commit 

func (tx *Tx) Commit() (err 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) Create 

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

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

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

TODO(vmihailenco): track and close prepared statements

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() (err error)

Rollback aborts the transaction.

func (*Tx) Select 

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

Select selects the model by primary key.

func (*Tx) Update 

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

Update updates the model by primary key.

Source Files

View all Source files

Directories

Path Synopsis
parser
pool

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.