Documentation ¶
Overview ¶
Package github.com/go-pg/pg 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" "github.com/go-pg/pg" ) 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", params) if err != nil { panic(err) } fmt.Println("named:", num) // Global params. _, err = db.WithParam("z", 1).Query(pg.Scan(&num), "SELECT ?x + ?y + ?z", params) if err != nil { panic(err) } fmt.Println("global:", num) }
Output: simple: 42 indexed: 2 named: 4 global: 3
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{}) types.ValueAppender
- func Scan(values ...interface{}) orm.ColumnScanner
- func SetLogger(logger *log.Logger)
- type DB
- func (db *DB) Begin() (*Tx, error)
- func (db *DB) Close() error
- func (db *DB) Context() context.Context
- func (db *DB) CopyFrom(reader io.Reader, query interface{}, params ...interface{}) (orm.Result, error)
- func (db *DB) CopyTo(writer io.Writer, query interface{}, params ...interface{}) (orm.Result, error)
- func (db *DB) CreateTable(model interface{}, opt *orm.CreateTableOptions) error
- func (db *DB) Delete(model interface{}) error
- func (db *DB) DropTable(model interface{}, opt *orm.DropTableOptions) error
- func (db *DB) Exec(query interface{}, params ...interface{}) (res orm.Result, err error)
- func (db *DB) ExecOne(query interface{}, params ...interface{}) (orm.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) OnQueryProcessed(fn func(*QueryProcessedEvent))
- func (db *DB) Options() *Options
- func (db *DB) Prepare(q string) (*Stmt, error)
- func (db *DB) Query(model, query interface{}, params ...interface{}) (res orm.Result, err error)
- func (db *DB) QueryOne(model, query interface{}, params ...interface{}) (orm.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) WithContext(ctx context.Context) *DB
- 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 QueryProcessedEvent
- type Stmt
- func (stmt *Stmt) Close() error
- func (stmt *Stmt) Exec(params ...interface{}) (res orm.Result, err error)
- func (stmt *Stmt) ExecOne(params ...interface{}) (orm.Result, error)
- func (stmt *Stmt) Query(model interface{}, params ...interface{}) (res orm.Result, err error)
- func (stmt *Stmt) QueryOne(model interface{}, params ...interface{}) (orm.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{}) (orm.Result, error)
- func (tx *Tx) CreateTable(model interface{}, opt *orm.CreateTableOptions) error
- func (tx *Tx) Delete(model interface{}) error
- func (tx *Tx) DropTable(model interface{}, opt *orm.DropTableOptions) error
- func (tx *Tx) Exec(query interface{}, params ...interface{}) (orm.Result, error)
- func (tx *Tx) ExecOne(query interface{}, params ...interface{}) (orm.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{}) (orm.Result, error)
- func (tx *Tx) QueryOne(model interface{}, query interface{}, params ...interface{}) (orm.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.CreateTable
- 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 (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 (ApplyFunc)
- DB.Select (FirstRow)
- DB.Select (GroupBy)
- DB.Select (LastRow)
- DB.Select (SomeColumns)
- DB.Select (SomeColumnsIntoVars)
- DB.Select (SqlExpression)
- DB.Select (WhereIn)
- DB.Select (With)
- DB.Select (WrapWith)
- DB.Update
- DB.Update (BulkUpdate)
- DB.Update (SetValues)
- DB.Update (SomeColumns)
- DB.Update (SomeColumns2)
- DB.WithTimeout
- F
- Hstore
- Ints
- Listener
- Q
- Scan
- Strings
Constants ¶
This section is empty.
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 ¶
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{}) types.ValueAppender
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 ¶
package main import ( "fmt" "github.com/go-pg/pg" ) var db *pg.DB func main() { var s1, s2 string _, err := db.QueryOne(pg.Scan(&s1, &s2), `SELECT ?, ?`, "foo", "bar") fmt.Println(s1, s2, err) }
Output: foo bar <nil>
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 ¶
package main import ( "fmt" "github.com/go-pg/pg" ) func main() { db := pg.Connect(&pg.Options{ User: "postgres", Password: "", Database: "postgres", }) var n int _, err := db.QueryOne(pg.Scan(&n), "SELECT 1") if err != nil { panic(err) } fmt.Println(n) err = db.Close() if err != nil { panic(err) } }
Output: 1
func (*DB) Begin ¶
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{}) (orm.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{}) (orm.Result, error)
CopyTo copies data from a table to writer.
func (*DB) CreateTable ¶
func (db *DB) CreateTable(model interface{}, opt *orm.CreateTableOptions) error
CreateTable creates table for the model. It recognizes following field tags:
- notnull - sets NOT NULL constraint.
- unique - sets UNIQUE constraint.
Example ¶
type Model struct { Id int Name string } err := db.CreateTable(&Model{}, &orm.CreateTableOptions{ Temp: true, // create temp table }) if err != nil { panic(err) } var info []struct { ColumnName string DataType string } _, err = db.Query(&info, ` SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'models' `) if err != nil { panic(err) } fmt.Println(info)
Output: [{id bigint} {name text}]
func (*DB) Delete ¶
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) DropTable ¶
func (db *DB) DropTable(model interface{}, opt *orm.DropTableOptions) error
DropTable drops table for the model.
func (*DB) Exec ¶
Exec executes a query ignoring returned rows. The params are for any placeholders in the query.
Example ¶
package main import ( "fmt" "github.com/go-pg/pg" ) var db *pg.DB func main() { 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" "github.com/go-pg/pg" "github.com/go-pg/pg/orm" ) type User struct { Id int64 Name string Emails []string } func (u User) String() string { return fmt.Sprintf("User<%d %s %v>", u.Id, u.Name, u.Emails) } type Story struct { Id int64 Title string AuthorId int64 Author *User } func (s Story) String() string { return fmt.Sprintf("Story<%d %s %s>", s.Id, s.Title, s.Author) } func 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) } func createSchema(db *pg.DB) error { for _, model := range []interface{}{&User{}, &Story{}} { err := db.CreateTable(model, &orm.CreateTableOptions{ Temp: true, }) if err != nil { return err } } return nil }
Output: User<1 admin [admin1@admin admin2@admin]> [User<1 admin [admin1@admin admin2@admin]> User<2 root [root1@root root2@root]>] Story<1 Cool story User<1 admin [admin1@admin admin2@admin]>>
Example (BelongsTo) ¶
// Profile belongs to User. type Profile struct { Id int Lang string UserId int } type User struct { Id int Name string Profile *Profile } db := connect() defer db.Close() qs := []string{ "CREATE TEMP TABLE users (id int, name text)", "CREATE TEMP TABLE profiles (id int, lang text, user_id int)", "INSERT INTO users VALUES (1, 'user 1'), (2, 'user 2')", "INSERT INTO profiles VALUES (1, 'en', 1), (2, 'ru', 2)", } for _, q := range qs { _, err := db.Exec(q) if err != nil { panic(err) } } // Select users joining their profiles with following query: // // SELECT // "user".*, // "profile"."id" AS "profile__id", // "profile"."lang" AS "profile__lang" // FROM "users" AS "user" // LEFT JOIN "profiles" AS "profile" ON "profile"."id" = "user"."profile_id" var users []User err := db.Model(&users). Column("user.*", "Profile"). Select() if err != nil { panic(err) } fmt.Println(len(users), "results") fmt.Println(users[0].Id, users[0].Name, users[0].Profile) fmt.Println(users[1].Id, users[1].Name, users[1].Profile)
Output: 2 results 1 user 1 &{1 en 1} 2 user 2 &{2 ru 2}
Example (Count) ¶
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 Profile struct { Id int Lang string } // User has one profile. type User struct { Id int Name string ProfileId int Profile *Profile } db := connect() defer db.Close() qs := []string{ "CREATE TEMP TABLE users (id int, name text, profile_id int)", "CREATE TEMP TABLE profiles (id int, lang text)", "INSERT INTO users VALUES (1, 'user 1', 1), (2, 'user 2', 2)", "INSERT INTO profiles VALUES (1, 'en'), (2, 'ru')", } for _, q := range qs { _, err := db.Exec(q) if err != nil { panic(err) } } // Select users joining their profiles with following query: // // SELECT // "user".*, // "profile"."id" AS "profile__id", // "profile"."lang" AS "profile__lang", // "profile"."user_id" AS "profile__user_id" // FROM "users" AS "user" // LEFT JOIN "profiles" AS "profile" ON "profile"."user_id" = "user"."id" var users []User err := db.Model(&users). Column("user.*", "Profile"). Select() if err != nil { panic(err) } fmt.Println(len(users), "results") fmt.Println(users[0].Id, users[0].Name, users[0].Profile) fmt.Println(users[1].Id, users[1].Name, users[1].Profile)
Output: 2 results 1 user 1 &{1 en} 2 user 2 &{2 ru}
Example (HstoreStructTag) ¶
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) OnQueryProcessed ¶
func (db *DB) OnQueryProcessed(fn func(*QueryProcessedEvent))
OnQueryProcessed calls the fn with QueryProcessedEvent when query is processed.
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") if err != nil { panic(err) } fmt.Println(s1, s2)
Output: foo bar
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" "github.com/go-pg/pg" ) 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 ¶
package main import ( "fmt" "github.com/go-pg/pg" ) var db *pg.DB func main() { 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 (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 (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 (WhereIn) ¶
db := modelDB() var books []Book err := db.Model(&books).WhereIn("id IN (?)", 1, 2).Select() if err != nil { panic(err) } fmt.Println(books)
Output: [Book<Id=1 Title="book 1"> Book<Id=2 Title="book 2">]
Example (With) ¶
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) ¶
// WITH author_books AS ( // SELECT * books WHERE author_id = 1 // ) // SELECT * FROM author_books var books []Book err := db.Model(&books). Where("author_id = ?", 1). WrapWith("author_books"). Table("author_books"). Select(&books) if err != nil { panic(err) } fmt.Println(books)
Output: [Book<Id=1 Title="book 1"> Book<Id=2 Title="book 2">]
func (*DB) 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 (BulkUpdate) ¶
db := modelDB() book1 := &Book{ Id: 1, Title: "updated book 1", } book2 := &Book{ Id: 2, Title: "updated book 2", } // UPDATE "books" AS "book" // SET "title" = _data."title" // FROM (VALUES ('updated book 1', 1), ('updated book 2', 2)) AS _data("title", "id") // WHERE "book"."id" = _data."id" _, err := db.Model(book1, book2).Column("title").Update() if err != nil { panic(err) } var books []Book err = db.Model(&books).Order("id").Select() if err != nil { panic(err) } fmt.Println(books)
Output: [Book<Id=1 Title="updated book 1"> Book<Id=2 Title="updated book 2"> Book<Id=3 Title="book 3">]
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", // only this column is going to be updated AuthorID: 2, } _, err := db.Model(&book).Column("title").Returning("*").Update() if err != nil { panic(err) } fmt.Println(book, book.AuthorID)
Output: Book<Id=1 Title="updated book 1"> 1
Example (SomeColumns2) ¶
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 ¶
package main import ( "time" "github.com/go-pg/pg" ) var db *pg.DB func main() { 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 ¶
package main import ( "fmt" "github.com/go-pg/pg" ) var db *pg.DB func main() { 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 ¶
package main import ( "fmt" "github.com/go-pg/pg" ) var db *pg.DB func main() { 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 // Maximum backoff between each retry. // Default is 4 seconds; -1 disables backoff. MaxRetryBackoff time.Duration // Dial timeout for establishing new connections. // Default is 5 seconds. DialTimeout time.Duration // Timeout for socket reads. If reached, commands will fail // with a timeout instead of blocking. ReadTimeout time.Duration // Timeout for socket writes. If reached, commands will fail // with a timeout instead of blocking. WriteTimeout time.Duration // Maximum number of socket connections. // Default is 20 connections. PoolSize int // Time for which client waits for free connection if all // connections are busy before returning an error. // Default is 5 seconds. PoolTimeout time.Duration // Time after which client closes idle connections. // Default is to not close idle connections. IdleTimeout time.Duration // Connection age at which client retires (closes) the connection. // Primarily useful with proxies like HAProxy. // Default is to not close aged connections. MaxAge time.Duration // Frequency of idle checks. // Default is 1 minute. IdleCheckFrequency time.Duration // When true Tx does not issue BEGIN, COMMIT, or ROLLBACK. // Also underlying database connection is immediately returned to the pool. // This is primarily useful for running your database tests in one big // transaction, because PostgreSQL does not support nested transactions. DisableTransaction bool }
Database connection options.
type QueryProcessedEvent ¶
type QueryProcessedEvent struct { StartTime time.Time Func string File string Line int DB orm.DB Query interface{} Params []interface{} Result orm.Result Error error }
func (*QueryProcessedEvent) FormattedQuery ¶
func (ev *QueryProcessedEvent) FormattedQuery() (string, error)
func (*QueryProcessedEvent) UnformattedQuery ¶
func (ev *QueryProcessedEvent) UnformattedQuery() (string, error)
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 ¶
package main import ( "fmt" "github.com/go-pg/pg" ) var db *pg.DB func main() { 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. It recognizes following field tags:
- notnull - sets NOT NULL constraint.
- unique - sets UNIQUE constraint.
func (*Tx) DropTable ¶
func (tx *Tx) DropTable(model interface{}, opt *orm.DropTableOptions) error
DropTable drops table for the model.
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{}) (orm.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{}) (orm.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.