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.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.
- All struct fields are nullable by default and zero values (empty string, 0, zero time) are marshalled as SQL
NULL. ```sql:",notnull"` is used to reverse this behaviour. - Transactions.
- Prepared statements.
- Notifications using
LISTENandNOTIFY. - Copying data using
COPY FROMandCOPY TO. - Timeouts.
- Automatic connection pooling.
- Queries retries on network errors.
- Working with models using ORM and SQL.
- Scanning variables using ORM and SQL.
- SelectOrInsert using on-conflict.
- INSERT ... ON CONFLICT DO UPDATE using ORM.
- Common table expressions using WITH and WrapWith.
- CountEstimate using
EXPLAINto get estimated number of matching rows. - HasOne, BelongsTo, HasMany and ManyToMany.
- Migrations.
- Sharding.
API docs: http://godoc.org/gopkg.in/pg.v5. Examples: http://godoc.org/gopkg.in/pg.v5#pkg-examples.
Table of contents
Installation
Install:
go get gopkg.in/pg.v5
Quickstart
package pg_test
import (
"fmt"
"gopkg.in/pg.v5"
)
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.Insert(user1)
if err != nil {
panic(err)
}
err = db.Insert(&User{
Name: "root",
Emails: []string{"root1@root", "root2@root"},
})
if err != nil {
panic(err)
}
story1 := &Story{
Title: "Cool story",
AuthorId: user1.Id,
}
err = db.Insert(story1)
if err != nil {
panic(err)
}
// Select user by primary key.
user := User{Id: user1.Id}
err = db.Select(&user)
if err != nil {
panic(err)
}
// Select all users.
var users []User
err = db.Model(&users).Select()
if err != nil {
panic(err)
}
// Select story and associated author in one query.
var story Story
err = db.Model(&story).
Column("story.*", "Author").
Where("story.id = ?", story1.Id).
Select()
if err != nil {
panic(err)
}
fmt.Println(user)
fmt.Println(users)
fmt.Println(story)
// Output: User<1 admin [admin1@admin admin2@admin]>
// [User<1 admin [admin1@admin admin2@admin]> User<2 root [root1@root root2@root]>]
// Story<1 Cool story User<1 admin [admin1@admin admin2@admin]>>
}
Model definition
Models are defined using Go structs which are mapped to PostgreSQL tables. Exported struct fields are mapped to table columns. Table name and alias are automatically derived from struct name by underscoring it; table name is also pluralized (struct Genre -> table genres AS genre). Default table name can be overrided using tableName field. Column name is derived from struct field name by underscoring it (field ParentId -> column parent_id). Default column name can be overrided using sql tag. Order of struct fields does not matter with the only exception being primary keys that must be defined before any other fields. Otherwise table relationships can be recognized incorrectly.
Please note that most struct tags in the following example have the same values as the defaults 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 struct name.
tableName struct{} `sql:"genres,alias:genre"` // default values 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
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 {
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
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)
}
func (b *Book) BeforeInsert(db orm.DB) error {
if b.CreatedAt.IsZero() {
b.CreatedAt = time.Now()
}
return nil
}
// BookWithCommentCount is like Book model, but has additional CommentCount
// field that is used to select data into it. The use of `pg:",override"` tag
// is essential here and it overrides internal model properties such as table name.
type BookWithCommentCount struct {
Book `pg:",override"`
CommentCount int
}
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 // Book.Id or Translation.Id
TrackableType string // "Book" or "Translation"
Text string
}
Model hooks
Models support optional hooks that accept orm.DB interface which value can be either *pg.DB or *pg.Tx.
// AfterQuery is called after the model is loaded from database.
func (b *Book) AfterQuery(db orm.DB) error {
return updateBookCache(b)
}
// AfterSelect is called after the model and all its relations (e.g. has one)
// are loaded from database.
func (b *Book) AfterSelect(db orm.DB) error {
return updateBookCache(b)
}
func (b *Book) BeforeInsert(db orm.DB) error {
if b.CreatedAt.IsZero() {
b.CreatedAt = time.Now()
}
return nil
}
func (b *Book) AfterInsert(db orm.DB) error {
return updateBookCache(b)
}
func (b *Book) BeforeUpdate(db orm.DB) error {
return nil
}
func (b *Book) AfterUpdate(db orm.DB) error {
return nil
}
func (b *Book) BeforeDelete(db orm.DB) error {
return nil
}
func (b *Book) AfterDelete(db orm.DB) error {
return nil
}
Writing queries
Placeholders
package pg_test
import (
"fmt"
"gopkg.in/pg.v5"
)
type Params struct {
X int
Y int
}
func (p *Params) Sum() int {
return p.X + p.Y
}
// go-pg recognizes placeholders (`?`) in queries and replaces them
// with parameters when queries are executed. Parameters are escaped
// before replacing according to PostgreSQL rules. Specifically:
// - all parameters are properly quoted against SQL injections;
// - null byte is removed;
// - JSON/JSONB gets `\u0000` escaped as `\\u0000`.
func Example_placeholders() {
var num int
// Simple params.
_, err := db.Query(pg.Scan(&num), "SELECT ?", 42)
if err != nil {
panic(err)
}
fmt.Println("simple:", num)
// Indexed params.
_, err = db.Query(pg.Scan(&num), "SELECT ?0 + ?0", 1)
if err != nil {
panic(err)
}
fmt.Println("indexed:", num)
// Named params.
params := &Params{
X: 1,
Y: 1,
}
_, err = db.Query(pg.Scan(&num), "SELECT ?x + ?y + ?Sum", ¶ms)
if err != nil {
panic(err)
}
fmt.Println("named:", num)
// Global params.
_, err = db.WithParam("z", 1).Query(pg.Scan(&num), "SELECT ?x + ?y + ?z", ¶ms)
if err != nil {
panic(err)
}
fmt.Println("global:", num)
// Output: simple: 42
// indexed: 2
// named: 4
// global: 3
}
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 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"
// Same query using WrapWith.
err := db.Model(&books).
Where("author_id = ?", 1).
WrapWith("author_books").
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 sets LIMIT and OFFSET from the URL values:
// - ?limit=10 - sets q.Limit(10), max limit is 1000.
// - ?page=5 - sets q.Offset((page - 1) * limit), max offset is 1000000.
func Pager(urlValues url.Values, defaultLimit int) func(*Query) (*Query, error) {
return func(q *Query) (*Query, error) {
const maxLimit = 1000
const maxOffset = 1e6
limit, err := intParam(urlValues, "limit")
if err != nil {
return nil, err
}
if limit < 1 {
limit = defaultLimit
} else if limit > maxLimit {
return nil, fmt.Errorf("limit can't bigger than %d", maxLimit)
}
if limit > 0 {
q = q.Limit(limit)
}
page, err := intParam(urlValues, "page")
if err != nil {
return nil, err
}
if page > 0 {
offset := (page - 1) * limit
if offset > maxOffset {
return nil, fmt.Errorf("offset can't bigger than %d", maxOffset)
}
q = q.Offset(offset)
}
return q, nil
}
}
var books []Book
err := db.Model(&books).Apply(orm.Pager(req.URL.Query())).Select()
// SELECT * FROM "books" LIMIT 20
Insert
// Insert new book returning primary keys.
err := db.Insert(&book)
// INSERT INTO "books" (title, text) VALUES ('my title', 'my text') RETURNING "id"
// Insert new book returning all columns.
err := db.Model(&book).Returning("*").Insert()
// 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
SelectOrInsert()
// 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
// Insert new book or update existing one.
_, err := db.Model(&book).
OnConflict("(id) DO UPDATE").
Set("title = ?title").
Insert()
// 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
// Upper column "title" and scan it.
var title string
res, err := db.Model(&book).
Set("title = upper(title)").
Where("id = ?", 1).
Returning("title").
Update(&title)
// UPDATE "books" SET title = upper(title) WHERE id = 1 RETURNING title
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.Closeto 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 relationBenchmarkModelHasOneGopg-4 5000 273181 ns/op 62692 B/op 1080 allocs/op BenchmarkModelHasOneGORM-4 500 3320562 ns/op 1528489 B/op 71630 allocs/opgo-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 100GORM:
db.Preload("Author").Limit(100).Find(&books).ErrorSELECT * FROM "books" LIMIT 100 SELECT * FROM "authors" WHERE ("id" IN ('1','2'...'100'))Has many relationBenchmarkModelHasManyGopg-4 500 2825899 ns/op 311556 B/op 5501 allocs/op BenchmarkModelHasManyGORM-4 200 7538012 ns/op 3333023 B/op 71756 allocs/opgo-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).ErrorSELECT * 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 relationBenchmarkModelHasMany2ManyGopg-4 500 3184262 ns/op 397883 B/op 7523 allocs/op BenchmarkModelHasMany2ManyGORM-4 200 8233222 ns/op 3686341 B/op 77236 allocs/opgo-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).ErrorSELECT * 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.v5 implements a PostgreSQL client.
Example (Placeholders) ¶
go-pg recognizes placeholders (`?`) in queries and replaces them with parameters when queries are executed. Parameters are escaped before replacing according to PostgreSQL rules. Specifically:
- all parameters are properly quoted against SQL injections;
- null byte is removed;
- JSON/JSONB gets `\u0000` escaped as `\\u0000`.
package main
import (
"fmt"
"gopkg.in/pg.v5"
)
type Params struct {
X int
Y int
}
func (p *Params) Sum() int {
return p.X + p.Y
}
// go-pg recognizes placeholders (`?`) in queries and replaces them
// with parameters when queries are executed. Parameters are escaped
// before replacing according to PostgreSQL rules. Specifically:
// - all parameters are properly quoted against SQL injections;
// - null byte is removed;
// - JSON/JSONB gets `\u0000` escaped as `\\u0000`.
func main() {
var num int
// Simple params.
_, err := db.Query(pg.Scan(&num), "SELECT ?", 42)
if err != nil {
panic(err)
}
fmt.Println("simple:", num)
// Indexed params.
_, err = db.Query(pg.Scan(&num), "SELECT ?0 + ?0", 1)
if err != nil {
panic(err)
}
fmt.Println("indexed:", num)
// Named params.
params := &Params{
X: 1,
Y: 1,
}
_, err = db.Query(pg.Scan(&num), "SELECT ?x + ?y + ?Sum", ¶ms)
if err != nil {
panic(err)
}
fmt.Println("named:", num)
// Global params.
_, err = db.WithParam("z", 1).Query(pg.Scan(&num), "SELECT ?x + ?y + ?z", ¶ms)
if err != nil {
panic(err)
}
fmt.Println("global:", num)
}
Output: simple: 42 indexed: 2 named: 4 global: 3
Index ¶
- Variables
- func Array(v interface{}) *types.Array
- func F(field string) types.ValueAppender
- func Hstore(v interface{}) *types.Hstore
- func In(slice interface{}) types.ValueAppender
- func Model(model ...interface{}) *orm.Query
- func Q(query string, params ...interface{}) orm.FormatAppender
- func Scan(values ...interface{}) orm.ColumnScanner
- func SetLogger(logger *log.Logger)
- func SetQueryLogger(logger *log.Logger)
- type 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) CreateTable(model interface{}, opt *orm.CreateTableOptions) 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) Insert(model ...interface{}) error
- func (db *DB) Listen(channels ...string) *Listener
- 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) String() string
- func (db *DB) Update(model interface{}) error
- func (db *DB) WithParam(param string, value interface{}) *DB
- func (db *DB) WithTimeout(d time.Duration) *DB
- type Error
- type IntSet
- func (IntSet) AddModel(_ orm.ColumnScanner) error
- func (IntSet) AfterDelete(_ orm.DB) error
- func (IntSet) AfterInsert(_ orm.DB) error
- func (IntSet) AfterQuery(_ orm.DB) error
- func (IntSet) AfterSelect(_ orm.DB) error
- func (IntSet) AfterUpdate(_ orm.DB) error
- func (IntSet) BeforeDelete(_ orm.DB) error
- func (IntSet) BeforeInsert(_ orm.DB) error
- func (IntSet) BeforeUpdate(_ orm.DB) error
- func (set *IntSet) NewModel() orm.ColumnScanner
- func (set *IntSet) Reset() error
- func (setptr *IntSet) ScanColumn(colIdx int, colName string, b []byte) error
- type Ints
- func (Ints) AddModel(_ orm.ColumnScanner) error
- func (Ints) AfterDelete(_ orm.DB) error
- func (Ints) AfterInsert(_ orm.DB) error
- func (Ints) AfterQuery(_ orm.DB) error
- func (Ints) AfterSelect(_ orm.DB) error
- func (Ints) AfterUpdate(_ orm.DB) error
- func (ints Ints) AppendValue(dst []byte, quote int) ([]byte, error)
- func (Ints) BeforeDelete(_ orm.DB) error
- func (Ints) BeforeInsert(_ orm.DB) error
- func (Ints) BeforeUpdate(_ orm.DB) error
- func (ints *Ints) NewModel() orm.ColumnScanner
- func (ints *Ints) Reset() error
- func (ints *Ints) ScanColumn(colIdx int, colName string, b []byte) error
- type Listener
- func (ln *Listener) Channel() <-chan *Notification
- func (ln *Listener) Close() error
- func (ln *Listener) Listen(channels ...string) error
- func (ln *Listener) Receive() (channel string, payload string, err error)
- func (ln *Listener) ReceiveTimeout(timeout time.Duration) (channel, payload string, err error)
- type Notification
- type NullTime
- 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.ColumnScanner) error
- func (Strings) AfterDelete(_ orm.DB) error
- func (Strings) AfterInsert(_ orm.DB) error
- func (Strings) AfterQuery(_ orm.DB) error
- func (Strings) AfterSelect(_ orm.DB) error
- func (Strings) AfterUpdate(_ orm.DB) error
- func (strings Strings) AppendValue(dst []byte, quote int) ([]byte, error)
- func (Strings) BeforeDelete(_ orm.DB) error
- func (Strings) BeforeInsert(_ orm.DB) error
- func (Strings) BeforeUpdate(_ orm.DB) error
- func (strings *Strings) NewModel() orm.ColumnScanner
- func (strings *Strings) Reset() error
- func (strings *Strings) ScanColumn(colIdx int, _ string, b []byte) error
- type Tx
- func (tx *Tx) Begin() (*Tx, error)
- func (tx *Tx) Commit() error
- func (tx *Tx) CopyFrom(r io.Reader, query string, params ...interface{}) (*types.Result, error)
- func (tx *Tx) CreateTable(model interface{}, opt *orm.CreateTableOptions) 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) Insert(model ...interface{}) error
- 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) RunInTransaction(fn func(*Tx) error) error
- func (tx *Tx) Select(model interface{}) error
- func (tx *Tx) Stmt(stmt *Stmt) *Stmt
- func (tx *Tx) Update(model interface{}) error
Examples ¶
- Package (Placeholders)
- Array
- Connect
- DB.Begin
- DB.CopyFrom
- DB.Delete
- DB.Delete (MultipleRows)
- DB.Exec
- DB.Insert
- DB.Insert (BulkInsert)
- DB.Insert (BulkInsert2)
- DB.Insert (OnConflictDoNothing)
- DB.Insert (OnConflictDoUpdate)
- DB.Insert (SelectOrInsert)
- 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 (With)
- DB.Select (WrapWith)
- DB.Update
- DB.Update (SetValues)
- DB.Update (SomeColumns)
- DB.Update (SomeColumns2)
- DB.WithTimeout
- F
- Hstore
- Ints
- Listener
- Q
- Scan
- Strings
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 ¶
Array accepts a slice and returns a wrapper for working with PostgreSQL array data type.
For struct fields you can use array tag:
Emails []string `pg:",array"`
Example ¶
src := []string{"one@example.com", "two@example.com"}
var dst []string
_, err := db.QueryOne(pg.Scan(pg.Array(&dst)), `SELECT ?`, pg.Array(src))
if err != nil {
panic(err)
}
fmt.Println(dst)
Output: [one@example.com two@example.com]
func F ¶
func F(field string) types.ValueAppender
F quotes a SQL identifier such as a table or column name replacing any placeholders found in the field.
Example ¶
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 ¶
Hstore accepts a map and returns a wrapper for working with hstore data type. Supported map types are:
- map[string]string
For struct fields you can use hstore tag:
Attrs map[string]string `pg:",hstore"`
Example ¶
src := map[string]string{"hello": "world"}
var dst map[string]string
_, err := db.QueryOne(pg.Scan(pg.Hstore(&dst)), `SELECT ?`, pg.Hstore(src))
if err != nil {
panic(err)
}
fmt.Println(dst)
Output: map[hello:world]
func In ¶
func In(slice interface{}) types.ValueAppender
In accepts a slice and returns a wrapper that can be used with PostgreSQL IN operator:
Where("id IN (?)", pg.In([]int{1, 2, 3}))
func Q ¶
func Q(query string, params ...interface{}) orm.FormatAppender
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 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 SetQueryLogger ¶
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 ¶
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 ¶
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 ¶
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) CreateTable ¶
func (db *DB) CreateTable(model interface{}, opt *orm.CreateTableOptions) error
CreateTable creates table for the model in db.
func (*DB) Delete ¶
Delete deletes the model by primary key.
Example ¶
db := modelDB()
book := Book{
Title: "title 1",
AuthorID: 1,
}
err := db.Insert(&book)
if err != nil {
panic(err)
}
err = db.Delete(&book)
if err != nil {
panic(err)
}
err = db.Select(&book)
fmt.Println(err)
Output: pg: no rows in result set
Example (MultipleRows) ¶
db := modelDB()
ids := pg.In([]int{1, 2, 3})
res, err := db.Model(&Book{}).Where("id IN (?)", ids).Delete()
if err != nil {
panic(err)
}
fmt.Println("deleted", res.RowsAffected())
count, err := db.Model(&Book{}).Count()
if err != nil {
panic(err)
}
fmt.Println("left", count)
Output: deleted 3 left 0
func (*DB) Exec ¶
Exec executes a query ignoring returned rows. The params are for any placeholders in the query.
Example ¶
res, err := db.Exec(`CREATE TEMP TABLE test()`) fmt.Println(res.RowsAffected(), err)
Output: -1 <nil>
func (*DB) ExecOne ¶
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) Insert ¶
Insert inserts the model updating primary keys if they are empty.
Example ¶
db := modelDB()
book := Book{
Title: "new book",
AuthorID: 1,
}
err := db.Insert(&book)
if err != nil {
panic(err)
}
fmt.Println(book)
Output: Book<Id=4 Title="new book">
Example (BulkInsert) ¶
db := modelDB()
book1 := Book{
Title: "new book 1",
}
book2 := Book{
Title: "new book 2",
}
err := db.Insert(&book1, &book2)
if err != nil {
panic(err)
}
fmt.Println(book1, book2)
Output: Book<Id=4 Title="new book 1"> Book<Id=5 Title="new book 2">
Example (BulkInsert2) ¶
db := modelDB()
books := []Book{{
Title: "new book 1",
}, {
Title: "new book 2",
}}
err := db.Insert(&books)
if err != nil {
panic(err)
}
fmt.Println(books)
Output: [Book<Id=4 Title="new book 1"> Book<Id=5 Title="new book 2">]
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").Insert()
if err != nil {
panic(err)
}
if res.RowsAffected() > 0 {
fmt.Println("created")
} else {
fmt.Println("did nothing")
}
}
err := db.Delete(&book)
if err != nil {
panic(err)
}
Output: created did nothing
Example (OnConflictDoUpdate) ¶
db := modelDB()
var book *Book
for i := 0; i < 2; i++ {
book = &Book{
Id: 100,
Title: fmt.Sprintf("title version #%d", i),
}
_, err := db.Model(book).
OnConflict("(id) DO UPDATE").
Set("title = ?title").
Insert()
if err != nil {
panic(err)
}
err = db.Select(book)
if err != nil {
panic(err)
}
fmt.Println(book)
}
err := db.Delete(book)
if err != nil {
panic(err)
}
Output: Book<Id=100 Title="title version #0"> Book<Id=100 Title="title version #1">
Example (SelectOrInsert) ¶
db := modelDB()
author := Author{
Name: "R. Scott Bakker",
}
created, err := db.Model(&author).
Column("id").
Where("name = ?name").
OnConflict("DO NOTHING"). // OnConflict is optional
Returning("id").
SelectOrInsert()
if err != nil {
panic(err)
}
fmt.Println(created, author)
Output: true Author<ID=2 Name="R. Scott Bakker">
func (*DB) Model ¶
Model returns new query for the model.
Example ¶
package main
import (
"fmt"
"gopkg.in/pg.v5"
)
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.Insert(user1)
if err != nil {
panic(err)
}
err = db.Insert(&User{
Name: "root",
Emails: []string{"root1@root", "root2@root"},
})
if err != nil {
panic(err)
}
story1 := &Story{
Title: "Cool story",
AuthorId: user1.Id,
}
err = db.Insert(story1)
if err != nil {
panic(err)
}
// Select user by primary key.
user := User{Id: user1.Id}
err = db.Select(&user)
if err != nil {
panic(err)
}
// Select all users.
var users []User
err = db.Model(&users).Select()
if err != nil {
panic(err)
}
// Select story and associated author in one query.
var story Story
err = db.Model(&story).
Column("story.*", "Author").
Where("story.id = ?", story1.Id).
Select()
if err != nil {
panic(err)
}
fmt.Println(user)
fmt.Println(users)
fmt.Println(story)
}
Output: User<1 admin [admin1@admin admin2@admin]> [User<1 admin [admin1@admin admin2@admin]> User<2 root [root1@root root2@root]>] Story<1 Cool story User<1 admin [admin1@admin admin2@admin]>>
Example (ApplyFunc) ¶
db := modelDB()
var authorId int
var editorId int
filter := func(q *orm.Query) (*orm.Query, error) {
if authorId != 0 {
q = q.Where("author_id = ?", authorId)
}
if editorId != 0 {
q = q.Where("editor_id = ?", editorId)
}
return q, nil
}
var books []Book
authorId = 1
err := db.Model(&books).
Apply(filter).
Select()
if err != nil {
panic(err)
}
fmt.Println(books)
Output: [Book<Id=1 Title="book 1"> Book<Id=2 Title="book 2">]
Example (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, error) {
return q.Where("active IS TRUE"), nil
}).
First()
if err != nil {
panic(err)
}
fmt.Println(user.Id, user.Name, user.Profiles[0], user.Profiles[1])
Output: 1 user 1 &{1 en true 1} &{2 ru true 1}
Example (HasManySelf) ¶
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.Insert(&item1); err != nil {
panic(err)
}
var item Item
err = db.Model(&item).Where("id = ?", 1).Select()
if err != nil {
panic(err)
}
fmt.Println(item)
Output: {1 map[hello:world]}
Example (ManyToMany) ¶
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
}
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.Insert(&item1); err != nil {
panic(err)
}
var item Item
err = db.Model(&item).Where("id = ?", 1).Select()
if err != nil {
panic(err)
}
fmt.Println(item)
Output: {1 [one@example.com two@example.com] [[1 2] [3 4]]}
Example (SelectAndCount) ¶
db := modelDB()
var books []Book
count, err := db.Model(&books).OrderExpr("id ASC").Limit(2).SelectAndCount()
if err != nil {
panic(err)
}
fmt.Println(count)
fmt.Println(books)
Output: 3 [Book<Id=1 Title="book 1"> Book<Id=2 Title="book 2">]
func (*DB) Prepare ¶
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 ¶
Query executes a query that returns rows, typically a SELECT. The params are for any placeholders in the query.
Example ¶
package main
import (
"fmt"
"gopkg.in/pg.v5"
)
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 ¶
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.RowsAffected())
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 ¶
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 ¶
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").
OrderExpr("book_count DESC").
Select(&res)
if err != nil {
panic(err)
}
fmt.Println("len", len(res))
fmt.Printf("author %d has %d books\n", res[0].AuthorId, res[0].BookCount)
fmt.Printf("author %d has %d books\n", res[1].AuthorId, res[1].BookCount)
Output: len 2 author 1 has 2 books author 11 has 1 books
Example (LastRow) ¶
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").
OrderExpr("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").
OrderExpr("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 (With) ¶
authorBooks := db.Model(&Book{}).Where("author_id = ?", 1)
var books []Book
err := db.Model().
With("author_books", authorBooks).
Table("author_books").
Select(&books)
if err != nil {
panic(err)
}
fmt.Println(books)
Output: [Book<Id=1 Title="book 1"> Book<Id=2 Title="book 2">]
Example (WrapWith) ¶
var books []Book
err := db.Model(&books).
Where("author_id = ?", 1).
WrapWith("author_books").
Table("author_books").
Select(&books)
if err != nil {
panic(err)
}
fmt.Println(books)
Output: [Book<Id=1 Title="book 1"> Book<Id=2 Title="book 2">]
func (*DB) Update ¶
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 ¶
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 IntSet ¶
type IntSet map[int64]struct{}
func (*IntSet) NewModel ¶
func (set *IntSet) NewModel() orm.ColumnScanner
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) NewModel ¶
func (ints *Ints) NewModel() orm.ColumnScanner
type Listener ¶
type Listener struct {
// contains filtered or unexported fields
}
Listener listens for notifications sent with NOTIFY command. It's NOT safe for concurrent use by multiple goroutines except the Channel API.
Example ¶
ln := db.Listen("mychan")
defer ln.Close()
ch := ln.Channel()
go func() {
_, err := db.Exec("NOTIFY mychan, ?", "hello world")
if err != nil {
panic(err)
}
}()
notif := <-ch
fmt.Println(notif)
Output: &{mychan hello world}
func (*Listener) Channel ¶
func (ln *Listener) Channel() <-chan *Notification
Channel returns a channel for concurrently receiving notifications. The channel is closed with Listener.
type Notification ¶
A notification received with LISTEN command.
type NullTime ¶
NullTime is a time.Time wrapper that marshals zero time as JSON null and PostgreSQL NULL.
func (NullTime) MarshalJSON ¶
func (*NullTime) UnmarshalJSON ¶
type Options ¶
type Options struct {
// Network type, either tcp or unix.
// Default is tcp.
Network string
// TCP host:port or Unix socket depending on Network.
Addr string
// Dialer creates new network connection and has priority over
// Network and Addr options.
Dialer func(network, addr string) (net.Conn, error)
User string
Password string
Database string
// TLS config for secure connections.
TLSConfig *tls.Config
// Maximum number of retries before giving up.
// Default is to not retry failed queries.
MaxRetries int
// Whether to retry queries cancelled because of statement_timeout.
RetryStatementTimeout bool
// Dial timeout for establishing new connections.
// Default is 5 seconds.
DialTimeout time.Duration
// Timeout for socket reads. If reached, commands will fail
// with a timeout instead of blocking.
ReadTimeout time.Duration
// Timeout for socket writes. If reached, commands will fail
// with a timeout instead of blocking.
WriteTimeout time.Duration
// Maximum number of socket connections.
// Default is 20 connections.
PoolSize int
// 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
// When true Tx does not issue BEGIN, COMMIT, or ROLLBACK.
// Also underlying database connection is immediately returned to the pool.
// This is primarily useful for running your database tests in one big
// transaction, because PostgreSQL does not support nested transactions.
DisableTransaction bool
}
Database connection options.
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) ExecOne ¶
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.
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) AppendValue ¶
func (*Strings) NewModel ¶
func (strings *Strings) NewModel() orm.ColumnScanner
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) CreateTable ¶
func (tx *Tx) CreateTable(model interface{}, opt *orm.CreateTableOptions) error
CreateTable creates table for the model in db.
func (*Tx) ExecOne ¶
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) Prepare ¶
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) RunInTransaction ¶
RunInTransaction runs a function in the transaction. If function returns an error transaction is rollbacked, otherwise transaction is committed.
Source Files
¶
Directories
Click to show internal directories.
Click to hide internal directories.