pg: github.com/go-pg/pg Index | Examples | Files | Directories

package pg

import "github.com/go-pg/pg"

Package github.com/go-pg/pg implements a PostgreSQL client.

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

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

Code:

package 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 `?` in queries as placeholders and replaces them
// with parameters when queries are executed. `?` can be escaped with backslash.
// 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 := pgdb.Query(pg.Scan(&num), "SELECT ?", 42)
    if err != nil {
        panic(err)
    }
    fmt.Println("simple:", num)

    // Indexed params.
    _, err = pgdb.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 = pgdb.Query(pg.Scan(&num), "SELECT ?x + ?y + ?Sum", params)
    if err != nil {
        panic(err)
    }
    fmt.Println("named:", num)

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

    // Model params.
    var tableName, tableAlias, tableColumns, columns string
    _, err = pgdb.Model(&Params{}).Query(
        pg.Scan(&tableName, &tableAlias, &tableColumns, &columns),
        "SELECT '?TableName', '?TableAlias', '?TableColumns', '?Columns'",
    )
    if err != nil {
        panic(err)
    }
    fmt.Println("table name:", tableName)
    fmt.Println("table alias:", tableAlias)
    fmt.Println("table columns:", tableColumns)
    fmt.Println("columns:", columns)

}

Index

Examples

Package Files

db.go doc.go error.go hook.go listener.go messages.go options.go pg.go result.go stmt.go time.go tx.go

Variables

var Discard orm.Discard

Discard is used with Query and QueryOne to discard rows.

var ErrMultiRows = internal.ErrMultiRows

ErrMultiRows is returned by QueryOne and ExecOne when query returned multiple rows but exactly one row is expected.

var ErrNoRows = internal.ErrNoRows

ErrNoRows is returned by QueryOne and ExecOne when query returned zero rows but at least one row is expected.

func Array Uses

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

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

For struct fields you can use array tag:

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

Code:

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

Output:

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

func F Uses

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.

Code:

db := modelDB()

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

Output:

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

func Hstore Uses

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

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

- map[string]string

For struct fields you can use hstore tag:

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

Code:

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

Output:

map[hello:world]

func In Uses

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

produces

WHERE id IN (1, 2, 3, 4)

func InMulti Uses

func InMulti(values ...interface{}) types.ValueAppender

InMulti accepts multiple values and returns a wrapper that can be used with PostgreSQL IN operator:

Where("(id1, id2) IN (?)", pg.InMulti([]int{1, 2}, []int{3, 4}))

produces

WHERE (id1, id2) IN ((1, 2), (3, 4))

func Model Uses

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

Model returns new query for the optional model.

func Q Uses

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

Q replaces any placeholders found in the query.

Code:

db := modelDB()

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

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

Output:

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

func Scan Uses

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

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

Code:

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

Output:

foo bar <nil>

func SetLogger Uses

func SetLogger(logger *log.Logger)

type DB Uses

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.

Code:

type Model1 struct {
}

var model1 Model1
_, err := pgdb.QueryOne(&model1, "SELECT 1 AS id")
fmt.Printf("Model1: %v\n", err)

type Model2 struct {
    tableName struct{} `pg:",discard_unknown_columns"`
}

var model2 Model2
_, err = pgdb.QueryOne(&model2, "SELECT 1 AS id")
fmt.Printf("Model2: %v\n", err)

Output:

Model1: pg: can't find column=id in model=Model1 (try discard_unknown_columns)
Model2: <nil>

Code:

type Event struct {
    Id   int
    Data map[string]interface{} `pg:",json_use_number"`
}

db := pg.Connect(pgOptions())
defer db.Close()

err := db.CreateTable((*Event)(nil), &orm.CreateTableOptions{
    Temp: true,
})
if err != nil {
    panic(err)
}

event := &Event{
    Data: map[string]interface{}{
        "price": 1.23,
    },
}
err = db.Insert(event)
if err != nil {
    panic(err)
}

event2 := new(Event)
err = db.Model(event2).Where("id = ?", event.Id).Select()
if err != nil {
    panic(err)
}

// Check that price is decoded as json.Number.
fmt.Printf("%T", event2.Data["price"])

Output:

json.Number

func Connect Uses

func Connect(opt *Options) *DB

Connect connects to a database using provided options.

The returned DB is safe for concurrent use by multiple goroutines and maintains its own connection pool.

Code:

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

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

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

Output:

1

func (*DB) Begin Uses

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

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

Code:

db := txExample()

incrInTx := func(db *pg.DB) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    // Rollback tx on error.
    defer tx.Rollback()

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

    counter++

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

    return tx.Commit()
}

var wg sync.WaitGroup
for i := 0; i < 10; i++ {
    wg.Add(1)
    go func() {
        defer wg.Done()
        if err := incrInTx(db); err != nil {
            panic(err)
        }
    }()
}
wg.Wait()

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

Output:

10

func (*DB) Close Uses

func (db *DB) Close() error

Close closes the database client, releasing any open resources.

It is rare to Close a DB, as the DB handle is meant to be long-lived and shared between many goroutines.

func (*DB) Context Uses

func (db *DB) Context() context.Context

Context returns DB context.

func (*DB) CopyFrom Uses

func (db *DB) CopyFrom(r io.Reader, query interface{}, params ...interface{}) (orm.Result, error)

CopyFrom copies data from reader to a table.

Code:

_, err := pgdb.Exec(`CREATE TEMP TABLE words(word text, len int)`)
if err != nil {
    panic(err)
}

r := strings.NewReader("hello,5\nfoo,3\n")
_, err = pgdb.CopyFrom(r, `COPY words FROM STDIN WITH CSV`)
if err != nil {
    panic(err)
}

var buf bytes.Buffer
_, err = pgdb.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 Uses

func (db *DB) CopyTo(w io.Writer, query interface{}, params ...interface{}) (orm.Result, error)

CopyTo copies data from a table to writer.

func (*DB) CreateComposite Uses

func (db *DB) CreateComposite(model interface{}, opt *orm.CreateCompositeOptions) error

func (*DB) CreateTable Uses

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.
- default:value - sets default value.

Code:

type Model1 struct {
    Id int
}

type Model2 struct {
    Id   int
    Name string

    Model1Id int `sql:"on_delete:RESTRICT"`
    Model1   *Model1
}

for _, model := range []interface{}{&Model1{}, &Model2{}} {
    err := pgdb.CreateTable(model, &orm.CreateTableOptions{
        Temp:          true, // create temp table
        FKConstraints: true,
    })
    if err != nil {
        panic(err)
    }
}

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

Output:

[{id bigint} {name text} {model1_id bigint}]

func (*DB) Delete Uses

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

Delete deletes the model by primary key.

Code:

db := modelDB()

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

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

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

Output:

pg: no rows in result set

Code:

db := modelDB()

var books []Book
err := db.Model(&books).Select()
if err != nil {
    panic(err)
}

res, err := db.Model(&books).Delete()
if err != nil {
    panic(err)
}
fmt.Println("deleted", res.RowsAffected())

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

Output:

deleted 3
left 0

Code:

db := modelDB()

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

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

Output:

deleted 3
left 0

func (*DB) DropComposite Uses

func (db *DB) DropComposite(model interface{}, opt *orm.DropCompositeOptions) error

func (*DB) DropTable Uses

func (db *DB) DropTable(model interface{}, opt *orm.DropTableOptions) error

DropTable drops table for the model.

func (*DB) Exec Uses

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

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

Code:

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

Output:

-1 <nil>

func (*DB) ExecOne Uses

func (db *DB) ExecOne(query interface{}, params ...interface{}) (orm.Result, error)

ExecOne acts like Exec, but query must affect only one row. It returns ErrNoRows error when query returns zero rows or ErrMultiRows when query returns multiple rows.

func (*DB) ForceDelete Uses

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

Delete forces delete of the model with deleted_at column.

func (*DB) FormatQuery Uses

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

func (*DB) Insert Uses

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

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

Code:

db := modelDB()

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

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

Output:

Book<Id=4 Title="new book">

Code:

db := modelDB()

book1 := Book{
    Title: "new book 1",
}
book2 := Book{
    Title: "new book 2",
}
err := db.Insert(&book1, &book2)
if err != nil {
    panic(err)
}
fmt.Println(book1, book2)

Output:

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

Code:

db := modelDB()

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

Output:

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

Code:

db := modelDB()

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

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

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

Output:

created
did nothing

Code:

db := modelDB()

var book *Book
for i := 0; i < 2; i++ {
    book = &Book{
        Id:    100,
        Title: fmt.Sprintf("title version #%d", i),
    }
    _, err := db.Model(book).
        OnConflict("(id) DO UPDATE").
        Set("title = EXCLUDED.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">

Code:

db := modelDB()

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

Output:

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

func (*DB) Listen Uses

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

Listen listens for notifications sent with NOTIFY command.

func (*DB) Model Uses

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

Model returns new query for the model.

Code:

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",
    })
    defer db.Close()

    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.
    story := new(Story)
    err = db.Model(story).
        Relation("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)(nil), (*Story)(nil)} {
        err := db.CreateTable(model, &orm.CreateTableOptions{
            Temp: true,
        })
        if err != nil {
            return err
        }
    }
    return nil
}

Code:

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

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

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

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

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

var users []User
err := db.Model(&users).
    Column("user.*").
    Relation("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}

Code:

package main

import (
    "fmt"

    "github.com/go-pg/pg/orm"
)

type InventoryItem struct {
    Name       string
    SupplierID int
    Price      float64
}

type OnHand struct {
    tableName struct{} `sql:"on_hand"`

    Item  InventoryItem `sql:"composite:inventory_item"`
    Count int
}

func main() {
    db := connect()
    defer db.Close()

    err := db.DropTable((*OnHand)(nil), &orm.DropTableOptions{
        IfExists: true,
        Cascade:  true,
    })
    panicIf(err)

    err = db.DropComposite((*InventoryItem)(nil), &orm.DropCompositeOptions{
        IfExists: true,
    })
    panicIf(err)

    err = db.CreateComposite((*InventoryItem)(nil), nil)
    panicIf(err)

    err = db.CreateTable((*OnHand)(nil), nil)
    panicIf(err)

    err = db.Insert(&OnHand{
        Item: InventoryItem{
            Name:       "fuzzy dice",
            SupplierID: 42,
            Price:      1.99,
        },
        Count: 1000,
    })
    panicIf(err)

    onHand := new(OnHand)
    err = db.Model(onHand).Select()
    panicIf(err)

    fmt.Println(onHand.Item.Name, onHand.Item.Price, onHand.Count)
}

Code:

db := modelDB()

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

fmt.Println(count)

Output:

3

Code:

db := modelDB()

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

fmt.Println(count)

Output:

3

Code:

db := modelDB()

var books []Book
exists, err := db.Model(&books).Where("author_id = ?", 1).Exists()
if err != nil {
    panic(err)
}

fmt.Println(exists)

Output:

true

Code:

err := pgdb.Model((*Book)(nil)).
    OrderExpr("id ASC").
    ForEach(func(b *Book) error {
        fmt.Println(b)
        return nil
    })
if err != nil {
    panic(err)
}

Output:

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

Code:

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

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

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

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

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

var user User
err := db.Model(&user).
    Column("user.*").
    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}

Code:

type Item struct {
    Id       int
    Items    []Item `pg:"fk:parent_id"`
    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.*").Relation("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

Code:

type Profile struct {
    Id   int
    Lang string
}

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

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

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

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

var users []User
err := db.Model(&users).
    Column("user.*").
    Relation("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}

Code:

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

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

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

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

Output:

{1 map[hello:world]}

go-pg default convention is that:

- Primary key is called Id, e.g. Model1.Id and Model2.Id.
- Many to many table has columns Model1Id and Model2Id.

If you are not using that convention you have 2 options:

1. Use orm.RegisterTable to register m2m table so go-pg has a chance
   to adopt to your convention.
2. Use `pg:fk:model2_id,joinFK:model1_id` to specify columns.

Code:

package main

import (
    "fmt"

    "github.com/go-pg/pg"
    "github.com/go-pg/pg/orm"
)

func init() {
    // Register many to many model so ORM can better recognize m2m relation.
    // This should be done before dependant models are used.
    orm.RegisterTable((*OrderToItem)(nil))
}

type Order struct {
    Id    int
    Items []Item `pg:"many2many:order_to_items"`
}

type Item struct {
    Id int
}

type OrderToItem struct {
    OrderId int
    ItemId  int
}

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

// go-pg default convention is that:
//   - Primary key is called Id, e.g. Model1.Id and Model2.Id.
//   - Many to many table has columns Model1Id and Model2Id.
//
// If you are not using that convention you have 2 options:
//   1. Use orm.RegisterTable to register m2m table so go-pg has a chance
//      to adopt to your convention.
//   2. Use `pg:fk:model2_id,joinFK:model1_id` to specify columns.
func main() {
    db := connect()
    defer db.Close()

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

    values := []interface{}{
        &Item{Id: 1},
        &Item{Id: 2},
        &Order{Id: 1},
        &OrderToItem{OrderId: 1, ItemId: 1},
        &OrderToItem{OrderId: 1, ItemId: 2},
    }
    for _, v := range values {
        err := db.Insert(v)
        if err != nil {
            panic(err)
        }
    }

    // Select order and all items with following queries:
    //
    // SELECT "order"."id" FROM "orders" AS "order" ORDER BY "order"."id" LIMIT 1
    //
    // SELECT order_to_items.*, "item"."id" FROM "items" AS "item"
    // JOIN order_to_items AS order_to_items ON (order_to_items."order_id") IN (1)
    // WHERE ("item"."id" = order_to_items."item_id")

    order := new(Order)
    err := db.Model(order).Relation("Items").First()
    if err != nil {
        panic(err)
    }
    fmt.Println("Order", order.Id, "Items", order.Items[0].Id, order.Items[1].Id)

    // Select order and all items sorted by id with following queries:
    //
    // SELECT "order"."id" FROM "orders" AS "order" ORDER BY "order"."id" LIMIT 1
    //
    // SELECT order_to_items.*, "item"."id" FROM "items" AS "item"
    // JOIN order_to_items AS order_to_items ON (order_to_items."order_id") IN (1)
    // WHERE ("item"."id" = order_to_items."item_id")
    // ORDER BY item.id DESC

    order = new(Order)
    err = db.Model(order).
        Relation("Items", func(q *orm.Query) (*orm.Query, error) {
            q = q.OrderExpr("item.id DESC")
            return q, nil
        }).
        First()
    if err != nil {
        panic(err)
    }
    fmt.Println("Order", order.Id, "Items", order.Items[0].Id, order.Items[1].Id)

}

Code:

package main

import (
    "fmt"

    "github.com/go-pg/pg"
    "github.com/go-pg/pg/orm"
)

func init() {
    // Register many to many model so ORM can better recognize m2m relation.
    // This should be done before dependant models are used.
    orm.RegisterTable((*ElemToElem)(nil))
}

type Elem struct {
    Id    int
    Elems []Elem `pg:"many2many:elem_to_elems,joinFK:sub_id"`
}

type ElemToElem struct {
    ElemId int
    SubId  int
}

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

func main() {
    db := connect()
    defer db.Close()

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

    values := []interface{}{
        &Elem{Id: 1},
        &Elem{Id: 2},
        &Elem{Id: 3},
        &ElemToElem{ElemId: 1, SubId: 2},
        &ElemToElem{ElemId: 1, SubId: 3},
    }
    for _, v := range values {
        err := db.Insert(v)
        if err != nil {
            panic(err)
        }
    }

    // Select elem and all subelems with following queries:
    //
    // SELECT "elem"."id" FROM "elems" AS "elem" ORDER BY "elem"."id" LIMIT 1
    //
    // SELECT elem_to_elems.*, "elem"."id" FROM "elems" AS "elem"
    // JOIN elem_to_elems AS elem_to_elems ON (elem_to_elems."elem_id") IN (1)
    // WHERE ("elem"."id" = elem_to_elems."sub_id")

    elem := new(Elem)
    err := db.Model(elem).Relation("Elems").First()
    if err != nil {
        panic(err)
    }
    fmt.Println("Elem", elem.Id)
    fmt.Println("Subelems", elem.Elems[0].Id, elem.Elems[1].Id)
}

Code:

type Example struct {
    Hello string
}

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

Output:

false

Code:

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

_, err := pgdb.Exec(`CREATE TEMP TABLE items (id serial, emails text[], numbers int[][])`)
panicIf(err)
defer pgdb.Exec("DROP TABLE items")

item1 := Item{
    Id:      1,
    Emails:  []string{"one@example.com", "two@example.com"},
    Numbers: [][]int{{1, 2}, {3, 4}},
}
err = pgdb.Insert(&item1)
panicIf(err)

item := new(Item)
err = pgdb.Model(item).Where("id = ?", 1).Select()
panicIf(err)
fmt.Println(item)

Output:

&{1 [one@example.com two@example.com] [[1 2] [3 4]]}

Code:

db := modelDB()

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

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

Output:

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

Code:

package main

import (
    "fmt"
    "time"
)

type Flight struct {
    Id        int
    Name      string
    DeletedAt time.Time `pg:",soft_delete"`
}

func main() {
    flight1 := &Flight{
        Id: 1,
    }
    err := pgdb.Insert(flight1)
    panicIf(err)

    // Soft delete.
    err = pgdb.Delete(flight1)
    panicIf(err)

    // Count visible flights.
    count, err := pgdb.Model((*Flight)(nil)).Count()
    panicIf(err)
    fmt.Println("count", count)

    // Count soft deleted flights.
    deletedCount, err := pgdb.Model((*Flight)(nil)).Deleted().Count()
    panicIf(err)
    fmt.Println("deleted count", deletedCount)

    // Actually delete the flight.
    err = pgdb.ForceDelete(flight1)
    panicIf(err)

    // Count soft deleted flights.
    deletedCount, err = pgdb.Model((*Flight)(nil)).Deleted().Count()
    panicIf(err)
    fmt.Println("deleted count", deletedCount)

}

func (*DB) OnQueryProcessed Uses

func (db *DB) OnQueryProcessed(fn func(*QueryProcessedEvent))

OnQueryProcessed calls the fn with QueryProcessedEvent when query is processed.

func (*DB) Options Uses

func (db *DB) Options() *Options

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

func (*DB) Param Uses

func (db *DB) Param(param string) interface{}

Param returns value for the param.

func (*DB) PoolStats Uses

func (db *DB) PoolStats() *PoolStats

PoolStats returns connection pool stats.

func (*DB) Prepare Uses

func (db *DB) Prepare(q string) (*Stmt, error)

Prepare creates a prepared statement for later queries or executions. Multiple queries or executions may be run concurrently from the returned statement.

Code:

stmt, err := pgdb.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 Uses

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

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

Code:

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

func (*DB) QueryOne Uses

func (db *DB) QueryOne(model, 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.

Code:

var user struct {
    Name string
}

res, err := pgdb.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}

func (*DB) RunInTransaction Uses

func (db *DB) RunInTransaction(fn func(*Tx) error) error

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

Code:

db := txExample()

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

        counter++

        _, err = tx.Exec(`UPDATE tx_test SET counter = ?`, counter)
        return err
    })
}

var wg sync.WaitGroup
for i := 0; i < 10; i++ {
    wg.Add(1)
    go func() {
        defer wg.Done()
        if err := incrInTx(db); err != nil {
            panic(err)
        }
    }()
}
wg.Wait()

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

Output:

10

func (*DB) Select Uses

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

Select selects the model by primary key.

Code:

db := modelDB()

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

Output:

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

Code:

db := modelDB()

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

Output:

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

Code:

db := modelDB()

var authorId int
var editorId int

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

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

Output:

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

Code:

db := modelDB()

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

Output:

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

Code:

db := modelDB()

var res []struct {
    AuthorId  int
    BookCount int
}

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

Output:

len 2
author 1 has 2 books
author 11 has 1 books

Code:

db := modelDB()

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

Output:

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

Code:

db := modelDB()

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

fmt.Println(book)

Output:

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

Code:

db := modelDB()

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

fmt.Println(id, title)

Output:

1 book 1

Code:

db := modelDB()

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

Output:

[1 2 3]

Code:

db := modelDB()

var books []Book
err := db.Model(&books).
    WhereGroup(func(q *orm.Query) (*orm.Query, error) {
        q = q.WhereOr("id = 1").
            WhereOr("id = 2")
        return q, nil
    }).
    Where("title IS NOT NULL").
    Select()
if err != nil {
    panic(err)
}
fmt.Println(books)

Output:

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

Code:

db := modelDB()

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

Output:

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

Code:

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

var books []Book
err := pgdb.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">]

Code:

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

Output:

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

func (*DB) String Uses

func (db *DB) String() string

func (*DB) Update Uses

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

Update updates the model by primary key.

Code:

db := modelDB()

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

book.Title = "updated book 1"
err = db.Update(book)
if err != nil {
    panic(err)
}

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

fmt.Println(book)

Output:

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

Code:

db := modelDB()

book1 := &Book{
    Id:        1,
    Title:     "updated book 1",
    UpdatedAt: time.Now(),
}
book2 := &Book{
    Id:        2,
    Title:     "updated book 2",
    UpdatedAt: time.Now(),
}

// 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", "updated_at").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">]

Code:

db := modelDB()

books := []Book{{
    Id:        1,
    Title:     "updated book 1",
    UpdatedAt: time.Now(),
}, {
    Id:        2,
    Title:     "updated book 2",
    UpdatedAt: time.Now(),
}}

// 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(&books).Column("title", "updated_at").Update()
if err != nil {
    panic(err)
}

books = nil
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">]

Code:

db := modelDB()

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

book = new(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">

Code:

db := modelDB()

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

fmt.Println(book)

Output:

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

Code:

db := modelDB()

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

fmt.Println(book, book.AuthorID)

Output:

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

Code:

db := modelDB()

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

fmt.Println(book, book.AuthorID)

Output:

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

func (*DB) WithContext Uses

func (db *DB) WithContext(ctx context.Context) *DB

WithContext returns a copy of the DB that uses the ctx.

func (*DB) WithParam Uses

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

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

func (*DB) WithTimeout Uses

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

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

Code:

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

type Error Uses

type Error interface {
    // Field returns a string value associated with an error code.
    //
    // https://www.postgresql.org/docs/10/static/protocol-error-fields.html
    Field(byte) string

    // IntegrityViolation reports whether an error is a part of
    // Integrity Constraint Violation class of errors.
    //
    // https://www.postgresql.org/docs/10/static/errcodes-appendix.html
    IntegrityViolation() bool
}

Error represents an error returned by PostgreSQL server using PostgreSQL ErrorResponse protocol.

https://www.postgresql.org/docs/10/static/protocol-message-formats.html

type IntSet Uses

type IntSet map[int64]struct{}

func (IntSet) AddModel Uses

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

func (*IntSet) Init Uses

func (set *IntSet) Init() error

func (*IntSet) NewModel Uses

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

func (*IntSet) ScanColumn Uses

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

type Ints Uses

type Ints []int64

Code:

var nums pg.Ints
_, err := pgdb.Query(&nums, `SELECT generate_series(0, 10)`)
fmt.Println(nums, err)

Output:

[0 1 2 3 4 5 6 7 8 9 10] <nil>

func (Ints) AddModel Uses

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

func (Ints) AppendValue Uses

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

func (*Ints) Init Uses

func (ints *Ints) Init() error

func (*Ints) NewModel Uses

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

func (*Ints) ScanColumn Uses

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

type Listener Uses

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.

Code:

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

ch := ln.Channel()

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

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

Output:

&{mychan hello world}

func (*Listener) Channel Uses

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

Channel returns a channel for concurrently receiving notifications. It periodically sends Ping messages to test connection health.

The channel is closed with Listener. Receive* APIs can not be used after channel is created.

func (*Listener) Close Uses

func (ln *Listener) Close() error

Close closes the listener, releasing any open resources.

func (*Listener) Listen Uses

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

Listen starts listening for notifications on channels.

func (*Listener) Receive Uses

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

Receive indefinitely waits for a notification. This is low-level API and in most cases Channel should be used instead.

func (*Listener) ReceiveTimeout Uses

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

ReceiveTimeout waits for a notification until timeout is reached. This is low-level API and in most cases Channel should be used instead.

type Notification Uses

type Notification struct {
    Channel string
    Payload string
}

A notification received with LISTEN command.

type NullTime Uses

type NullTime struct {
    time.Time
}

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

func (NullTime) AppendValue Uses

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

func (NullTime) MarshalJSON Uses

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

func (*NullTime) Scan Uses

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

func (*NullTime) UnmarshalJSON Uses

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

type Options Uses

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)

    // Hook that is called when new connection is established.
    OnConnect func(*DB) error

    User     string
    Password string
    Database string

    // ApplicationName is the application name. Used in logs on Pg side.
    // Only availaible from pg-9.0.
    ApplicationName 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
    // Minimum backoff between each retry.
    // Default is 250 milliseconds; -1 disables backoff.
    MinRetryBackoff time.Duration
    // 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 10 connections per every CPU as reported by runtime.NumCPU.
    PoolSize int
    // Minimum number of idle connections which is useful when establishing
    // new connection is slow.
    MinIdleConns int
    // Connection age at which client retires (closes) the connection.
    // It is useful with proxies like PgBouncer and HAProxy.
    // Default is to not close aged connections.
    MaxConnAge time.Duration
    // Time for which client waits for free connection if all
    // connections are busy before returning an error.
    // Default is 30 seconds if ReadTimeOut is not defined, otherwise,
    // ReadTimeout + 1 second.
    PoolTimeout time.Duration
    // Amount of time after which client closes idle connections.
    // Should be less than server's timeout.
    // Default is 5 minutes. -1 disables idle timeout check.
    IdleTimeout time.Duration
    // Frequency of idle checks made by idle connections reaper.
    // Default is 1 minute. -1 disables idle connections reaper,
    // but idle connections are still discarded by the client
    // if IdleTimeout is set.
    IdleCheckFrequency time.Duration
}

Database connection options.

func ParseURL Uses

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

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

type PoolStats Uses

type PoolStats pool.Stats

type QueryProcessedEvent Uses

type QueryProcessedEvent struct {
    StartTime time.Time
    Func      string
    File      string
    Line      int

    DB      orm.DB
    Query   interface{}
    Params  []interface{}
    Attempt int
    Result  orm.Result
    Error   error
}

func (*QueryProcessedEvent) FormattedQuery Uses

func (ev *QueryProcessedEvent) FormattedQuery() (string, error)

func (*QueryProcessedEvent) UnformattedQuery Uses

func (ev *QueryProcessedEvent) UnformattedQuery() (string, error)

type Stmt Uses

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

Stmt is a prepared statement. Stmt is safe for concurrent use by multiple goroutines.

func (*Stmt) Close Uses

func (stmt *Stmt) Close() error

Close closes the statement.

func (*Stmt) Exec Uses

func (stmt *Stmt) Exec(params ...interface{}) (res orm.Result, err error)

Exec executes a prepared statement with the given parameters.

func (*Stmt) ExecOne Uses

func (stmt *Stmt) ExecOne(params ...interface{}) (orm.Result, error)

ExecOne acts like Exec, but query must affect only one row. It returns ErrNoRows error when query returns zero rows or ErrMultiRows when query returns multiple rows.

func (*Stmt) Query Uses

func (stmt *Stmt) Query(model interface{}, params ...interface{}) (res orm.Result, err error)

Query executes a prepared query statement with the given parameters.

func (*Stmt) QueryOne Uses

func (stmt *Stmt) QueryOne(model 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.

type Strings Uses

type Strings []string

Code:

var strs pg.Strings
_, err := pgdb.Query(&strs, `
		WITH users AS (VALUES ('foo'), ('bar')) SELECT * FROM users
	`)
fmt.Println(strs, err)

Output:

[foo bar] <nil>

func (Strings) AddModel Uses

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

func (Strings) AppendValue Uses

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

func (*Strings) Init Uses

func (strings *Strings) Init() error

func (*Strings) NewModel Uses

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

func (*Strings) ScanColumn Uses

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

type Tx Uses

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

Tx is an in-progress database transaction. It is safe for concurrent use by multiple goroutines.

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

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

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

func (*Tx) Begin Uses

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

Begin returns the transaction.

func (*Tx) Commit Uses

func (tx *Tx) Commit() error

Commit commits the transaction.

func (*Tx) Context Uses

func (tx *Tx) Context() context.Context

func (*Tx) CopyFrom Uses

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

CopyFrom is an alias for DB.CopyFrom.

func (*Tx) CopyTo Uses

func (tx *Tx) CopyTo(w io.Writer, query interface{}, params ...interface{}) (orm.Result, error)

CopyTo is an alias for DB.CopyTo.

func (*Tx) CreateTable Uses

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

CreateTable is an alias for DB.CreateTable.

func (*Tx) DB Uses

func (tx *Tx) DB() *DB

DB returns a DB which started the Tx.

func (*Tx) Delete Uses

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

Delete is an alias for DB.Delete.

func (*Tx) DropTable Uses

func (tx *Tx) DropTable(model interface{}, opt *orm.DropTableOptions) error

DropTable is an alias for DB.DropTable.

func (*Tx) Exec Uses

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

Exec is an alias for DB.Exec.

func (*Tx) ExecOne Uses

func (tx *Tx) ExecOne(query interface{}, params ...interface{}) (orm.Result, error)

ExecOne is an alias for DB.ExecOne.

func (*Tx) ForceDelete Uses

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

Delete forces delete of the model with deleted_at column.

func (*Tx) FormatQuery Uses

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

func (*Tx) Insert Uses

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

Insert is an alias for DB.Insert.

func (*Tx) Model Uses

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

Model is an alias for DB.Model.

func (*Tx) Prepare Uses

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

Prepare creates a prepared statement for use within a transaction.

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

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

func (*Tx) Query Uses

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

Query is an alias for DB.Query.

func (*Tx) QueryOne Uses

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

QueryOne is an alias for DB.QueryOne.

func (*Tx) Rollback Uses

func (tx *Tx) Rollback() error

Rollback aborts the transaction.

func (*Tx) RunInTransaction Uses

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

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

func (*Tx) Select Uses

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

Select is an alias for DB.Select.

func (*Tx) Stmt Uses

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

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

func (*Tx) Update Uses

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

Update is an alias for DB.Update.

Directories

PathSynopsis
internal
internal/parser
internal/pool
orm
types

Package pg imports 24 packages (graph) and is imported by 58 packages. Updated 2018-09-22. Refresh now. Tools for package owners.