pg

README

PostgreSQL client for Golang

Supports:

• Basic types: integers, floats, string, bool, time.Time.
• sql.NullBool, sql.NullString, sql.NullInt64, sql.NullFloat64 and pg.NullTime.
• sql:",null" struct tag which marshalls zero struct fields as SQL NULL and completely omits them from INSERT queries.
• sql.Scanner and sql/driver.Valuer interfaces.
• Structs, maps and arrays are marshalled as JSON by default.
• PostgreSQL multidimensional Arrays using array tag and Array wrapper.
• Hstore using hstore tag and Hstore wrapper.
• Transactions.
• Prepared statements.
• Notifications using LISTEN and NOTIFY.
• Copying data using COPY FROM and COPY TO.
• Timeouts.
• Automatic connection pooling.
• Queries retries on network errors.
• Working with models using ORM and SQL.
• Scanning variables using ORM and SQL.
• SelectOrCreate using upserts.
• INSERT ... ON CONFLICT DO UPDATE using ORM.
• CountEstimate using EXPLAIN to get estimated number of matching rows.
• HasOne, BelongsTo, HasMany and ManyToMany.
• Migrations.
• Sharding.

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

Table of contents

• Installation
• Quickstart
• Model definition
• Writing queries
  • Select
  • Reusing queries
  • Insert
  • Update
  • Delete
  • Has one
  • Belongs to
  • Has many
  • Has many to many
• Howto
• FAQ

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

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 be defined before any other fields. Otherwise table relationships can be recognized incorrectly.

Please note that most struct tags in following example are not needed and are included only for demonstration purposes.

type Genre struct {
	// TableName is an optional field that specifies custom table name and alias.
	// By default go-pg generates table name and alias from the struct name.
	TableName struct{} `sql:"genres,alias:genre"` // default name and alias are the same

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

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

type BookGenre struct {
	TableName struct{} `sql:",alias:bg"` // custom table alias

	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 `sql:",null"`

	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
}

type Translation struct {
	TableName struct{} `sql:",alias:tr"` // custom table alias

	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 only book title and text.
err := db.Model(&book).Column("title", "text").Where("id = ?", 1).Select()
// SELECT "title", "text" FROM "books" WHERE id = 1

// Select only book title and text into variables.
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 book using WHERE OR.
err := db.Model(&book).
    WhereOr(
        pg.SQL("id > ?", 100),
        pg.SQL("title LIKE ?", "my%"),
    ).
    Limit(1).
    Select()
// SELECT * FROM "books" WHERE (id > 100 OR 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"

// Select books using WITH statement.
authorBooks := db.Model(&Book{}).Where("author_id = ?", 1)
err := db.Model(nil).
    With("author_books", authorBooks).
    Table("author_books").
    Select(&books)
// WITH "author_books" AS (SELECT "book".* FROM "books" AS "book" WHERE (author_id = 1))
// SELECT * FROM "author_books"

Reusing queries

// pager retrieves page number from the req and sets query LIMIT and OFFSET.
func pager(req *http.Request) func(*orm.Query) *orm.Query {
    const pageSize = 20
    return func(q *orm.Query) *orm.Query {
        q = q.Limit(pageSize)
        param := req.URL.Query().Get("page")
        if param == "" {
            return q
        }
        page, err := strconv.Atoi(param)
        if err != nil {
            // Set the query error.
            return q.Err(err)
        }
        return q.Offset((page - 1) * pageSize)
    }
}

var books []Book
err := db.Model(&books).Apply(pager(req)).Select()
// SELECT * FROM "books" LIMIT 20

// OR using DB and model late binding

query := pg.Model().Apply(pager(req))
err := query.DB(db).Model(&books).Select()
// SELECT * FROM "books" LIMIT 20

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

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'

Has one

Following example selects all items and their subitems using LEFT JOIN and sub_id column.

type Item struct {
    Id int

    Sub   *Item
    SubId int
}

var items []Item
err := db.Model(&items).
    Column("item.*", "Sub").
    Where("item.sub_id IS NOT NULL").
    Select()
// 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)

Belongs to

Following examples selects users joining their profiles:

// 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",
//   "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 1}
// 2 user 2 &{2 ru 2}

Has many

Following example selects first user and all his active profiles:

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 using 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 {
        return q.Where("active IS TRUE")
    }).
    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}

Has many to many

Following example selects one item and all subitems using itermediary item_to_items table.

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

err := db.Model(&item).Column("item.*", "Items").First()
// 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")

Howto

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

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	    122138 ns/op	   83472 B/op	     625 allocs/op
  BenchmarkQueryRowsGopgReflect-4            	   10000	    137208 ns/op	   87488 B/op	     736 allocs/op
  BenchmarkQueryRowsGopgORM-4                	   10000	    142029 ns/op	   87920 B/op	     741 allocs/op
  BenchmarkQueryRowsStdlibPq-4               	   10000	    162664 ns/op	  161631 B/op	    1324 allocs/op
  BenchmarkQueryRowsGORM-4                   	    2000	    569147 ns/op	  415272 B/op	    6966 allocs/op
  

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

  Has one relation

  BenchmarkModelHasOneGopg-4                 	    5000	    273181 ns/op	   62692 B/op	    1080 allocs/op
  BenchmarkModelHasOneGORM-4                 	     500	   3320562 ns/op	 1528489 B/op	   71630 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	   2825899 ns/op	  311556 B/op	    5501 allocs/op
  BenchmarkModelHasManyGORM-4                	     200	   7538012 ns/op	 3333023 B/op	   71756 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	   3184262 ns/op	  397883 B/op	    7523 allocs/op
  BenchmarkModelHasMany2ManyGORM-4           	     200	   8233222 ns/op	 3686341 B/op	   77236 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 setup query logger like this:

pg.SetQueryLogger(log.New(os.Stdout, "", log.LstdFlags))

Or 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

• Variables
• func Array(v interface{}) *types.Array
• func F(field string, params ...interface{}) types.F
• func Hstore(v interface{}) *types.Hstore
• func In(slice interface{}) types.ValueAppender
• func Model(model ...interface{}) *orm.Query
• func Q(query string, params ...interface{}) types.Q
• func SQL(query string, params ...interface{}) *orm.SQL
• func Scan(values ...interface{}) orm.ColumnScanner
• func SetLogger(logger *log.Logger)
• func SetQueryLogger(logger *log.Logger)
• type DB
  • func Connect(opt *Options) *DB
  • func (db *DB) Begin() (*Tx, error)
  • func (db *DB) Close() error
  • func (db *DB) CopyFrom(reader io.Reader, query interface{}, params ...interface{}) (*types.Result, error)
  • func (db *DB) CopyTo(writer io.Writer, query interface{}, params ...interface{}) (*types.Result, error)
  • func (db *DB) Create(model ...interface{}) error
  • func (db *DB) Delete(model interface{}) error
  • func (db *DB) Exec(query interface{}, params ...interface{}) (res *types.Result, err error)
  • func (db *DB) ExecOne(query interface{}, params ...interface{}) (*types.Result, error)
  • func (db *DB) FormatQuery(dst []byte, query string, params ...interface{}) []byte
  • func (db *DB) Listen(channels ...string) (*Listener, error)
  • func (db *DB) Model(model ...interface{}) *orm.Query
  • func (db *DB) Options() *Options
  • func (db *DB) Prepare(q string) (*Stmt, error)
  • func (db *DB) Query(model, query interface{}, params ...interface{}) (res *types.Result, err error)
  • func (db *DB) QueryOne(model, query interface{}, params ...interface{}) (*types.Result, error)
  • func (db *DB) RunInTransaction(fn func(*Tx) error) error
  • func (db *DB) Select(model interface{}) error
  • func (db *DB) Update(model interface{}) error
  • func (db *DB) WithTimeout(d time.Duration) *DB
• type Error
• type IntSet
  • func (IntSet) AddModel(_ orm.DB, _ orm.ColumnScanner) error
  • func (set *IntSet) NewModel(_ orm.DB) orm.ColumnScanner
  • func (setptr *IntSet) ScanColumn(colIdx int, colName string, b []byte) error
• type Ints
  • func (Ints) AddModel(_ orm.DB, _ orm.ColumnScanner) error
  • func (ints Ints) AppendValue(dst []byte, quote int) ([]byte, error)
  • func (ints *Ints) NewModel(_ orm.DB) orm.ColumnScanner
  • func (ints *Ints) ScanColumn(colIdx int, colName string, b []byte) error
• type Listener
  • func (l *Listener) Close() error
  • func (l *Listener) Listen(channels ...string) error
  • func (l *Listener) Receive() (channel string, payload string, err error)
  • func (l *Listener) ReceiveTimeout(readTimeout time.Duration) (channel, payload string, err error)
• type NullTime
  • func (tm NullTime) AppendValue(b []byte, quote int) ([]byte, error)
  • func (tm NullTime) MarshalJSON() ([]byte, error)
  • func (tm *NullTime) Scan(b interface{}) error
  • func (tm *NullTime) UnmarshalJSON(b []byte) error
• type Options
• type Stmt
  • func (stmt *Stmt) Close() error
  • func (stmt *Stmt) Exec(params ...interface{}) (res *types.Result, err error)
  • func (stmt *Stmt) ExecOne(params ...interface{}) (*types.Result, error)
  • func (stmt *Stmt) Query(model interface{}, params ...interface{}) (res *types.Result, err error)
  • func (stmt *Stmt) QueryOne(model interface{}, params ...interface{}) (*types.Result, error)
• type Strings
  • func (Strings) AddModel(_ orm.DB, _ orm.ColumnScanner) error
  • func (strings Strings) AppendValue(dst []byte, quote int) ([]byte, error)
  • func (strings *Strings) NewModel(_ orm.DB) orm.ColumnScanner
  • func (strings *Strings) ScanColumn(colIdx int, _ string, b []byte) error
• type Tx
  • func (tx *Tx) Commit() error
  • func (tx *Tx) CopyFrom(r io.Reader, query string, params ...interface{}) (*types.Result, error)
  • func (tx *Tx) Create(model ...interface{}) error
  • func (tx *Tx) Delete(model interface{}) error
  • func (tx *Tx) Exec(query interface{}, params ...interface{}) (*types.Result, error)
  • func (tx *Tx) ExecOne(query interface{}, params ...interface{}) (*types.Result, error)
  • func (tx *Tx) FormatQuery(dst []byte, query string, params ...interface{}) []byte
  • func (tx *Tx) Model(model ...interface{}) *orm.Query
  • func (tx *Tx) Prepare(q string) (*Stmt, error)
  • func (tx *Tx) Query(model interface{}, query interface{}, params ...interface{}) (*types.Result, error)
  • func (tx *Tx) QueryOne(model interface{}, query interface{}, params ...interface{}) (*types.Result, error)
  • func (tx *Tx) Rollback() error
  • func (tx *Tx) Select(model interface{}) error
  • func (tx *Tx) Stmt(stmt *Stmt) *Stmt
  • func (tx *Tx) Update(model interface{}) error

Examples

• Array
• Connect
• DB.Begin
• DB.CopyFrom
• DB.Create
• DB.Create (BulkInsert)
• DB.Create (BulkInsert2)
• DB.Create (OnConflictDoNothing)
• DB.Create (OnConflictDoUpdate)
• DB.Create (SelectOrCreate)
• DB.Delete
• DB.Delete (MultipleRows)
• DB.Exec
• DB.Model
• DB.Model (ApplyFunc)
• DB.Model (BelongsTo)
• DB.Model (Count)
• DB.Model (CountEstimate)
• DB.Model (HasMany)
• DB.Model (HasManySelf)
• DB.Model (HasOne)
• DB.Model (HstoreStructTag)
• DB.Model (ManyToMany)
• DB.Model (NullEmptyValue)
• DB.Model (PostgresArrayStructTag)
• DB.Model (SelectAndCount)
• DB.Prepare
• DB.Query
• DB.QueryOne
• DB.QueryOne (Returning_id)
• DB.RunInTransaction
• DB.Select
• DB.Select (AllColumns)
• DB.Select (FirstRow)
• DB.Select (GroupBy)
• DB.Select (LastRow)
• DB.Select (SomeColumns)
• DB.Select (SomeColumnsIntoVars)
• DB.Select (SqlExpression)
• DB.Select (TableAlias)
• DB.Select (WhereOr)
• DB.Update
• DB.Update (SetValues)
• DB.Update (SomeColumns)
• DB.Update (SomeColumns2)
• DB.WithTimeout
• F
• Hstore
• In
• Ints
• Listener
• Q
• Scan
• Strings

Variables

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

var Discard orm.Discard

Discard is used with Query and QueryOne to discard rows.

Functions

func Array

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

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

Note that for struct fields you should use array tag:

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

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 quotes a SQL identifier such as a table or column name replacing any placeholders found in the field.

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

Note that for struct fields you should use hstore tag:

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

Example

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

Example

ids := pg.In([]int{1, 2, 3})
q := pg.SQL(`SELECT * FROM table WHERE id IN (?)`, ids)
fmt.Println(q)

Output:

SELECT * FROM table WHERE id IN (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{}) types.Q

Q replaces any placeholders found in the 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 SQL

func SQL(query string, params ...interface{}) *orm.SQL

SQL returns a SQL query with params that are formatted on query execution.

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)

func SetQueryLogger

func SetQueryLogger(logger *log.Logger)

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

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

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

db := modelDB()

book1 := Book{
	Title: "new book 1",
}
book2 := Book{
	Title: "new book 2",
}
err := db.Create(&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">

Example (BulkInsert2)

db := modelDB()

books := []Book{{
	Title: "new book 1",
}, {
	Title: "new book 2",
}}
err := db.Create(&books)
if err != nil {
	panic(err)
}
fmt.Println(books)

Output:

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

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.In([]int{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 (ApplyFunc)

db := modelDB()

var authorId int
var editorId int

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

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

// 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",
//   "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 1}
2 user 2 &{2 ru 2}

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 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 {
		return q.Where("active IS TRUE")
	}).
	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)

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)

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 with 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("%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:

2 items
item 2, subitem 1
item 4, subitem 2

Example (HstoreStructTag)

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.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 map[hello:world]}

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

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)
}
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.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] [[1 2] [3 4]]}

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.In(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", "book.title").
	Order("book.id ASC").
	Limit(1).
	Select()
if err != nil {
	panic(err)
}

fmt.Println(book)

Output:

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

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(book.id)").
	Select(pg.Array(&ids))
if err != nil {
	panic(err)
}
fmt.Println(ids)

Output:

[1 2 3]

Example (TableAlias)

db := modelDB()

var book Book
err := db.Model(&book).
	Alias("b").
	Order("b.id ASC").
	Limit(1).
	Select()
if err != nil {
	panic(err)
}

fmt.Println(book)

Output:

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

Example (WhereOr)

authorIdOrEditorId := 1

var books []Book
err := db.Model(&books).
	WhereOr(
		pg.SQL("author_id = ?", authorIdOrEditorId),
		pg.SQL("editor_id = ?", authorIdOrEditorId),
	).
	Select()
if err != nil {
	panic(err)
}
fmt.Println(books)

Output:

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

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

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

type Error

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

type IntSet

type IntSet map[int64]struct{}

func (IntSet) AddModel

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

func (*IntSet) NewModel

func (set *IntSet) NewModel(_ orm.DB) 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>

func (Ints) AddModel

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

func (Ints) AppendValue

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

func (*Ints) NewModel

func (ints *Ints) NewModel(_ orm.DB) 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 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
	User     string
	Password string
	Database string

	// Whether to use secure TCP/IP connections (TLS).
	// TODO: deprecated in favor of TLSConfig
	SSL bool
	// TLS config for secure connections.
	TLSConfig *tls.Config

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

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

	// 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
	// Amount of time client waits for free connection if all
	// connections are busy before returning an error.
	// Default is 5 seconds.
	PoolTimeout time.Duration
	// Amount of time after which client closes idle connections.
	// Default is to not close idle connections.
	IdleTimeout time.Duration
	// 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.DB, _ orm.ColumnScanner) error

func (Strings) AppendValue

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

func (*Strings) NewModel

func (strings *Strings) NewModel(_ orm.DB) 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
}

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

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