README

PostgreSQL client and ORM for Golang

• Join Discord to ask questions.
• Documentation
• Reference
• Examples
• Example projects:
  • treemux
  • gin
  • go-kit
  • aah framework
• GraphQL Tutorial on YouTube.

Ecosystem

• Migrations by vmihailenco and robinjoseph08.
• Genna - cli tool for generating go-pg models.
• urlstruct to decode url.Values into structs.
• Sharding.
• go-pg-monitor - Prometheus metrics based on go-pg client stats.

Features

• Basic types: integers, floats, string, bool, time.Time, net.IP, net.IPNet.
• sql.NullBool, sql.NullString, sql.NullInt64, sql.NullFloat64 and pg.NullTime.
• sql.Scanner and sql/driver.Valuer interfaces.
• Structs, maps and arrays are marshalled as JSON by default.
• PostgreSQL multidimensional Arrays using array tag and Array wrapper.
• Hstore using hstore tag and Hstore wrapper.
• Composite types.
• All struct fields are nullable by default and zero values (empty string, 0, zero time, empty map or slice, nil ptr) are marshalled as SQL NULL. pg:",notnull" is used to add SQL NOT NULL constraint and pg:",use_zero" to allow Go zero values.
• Transactions.
• Prepared statements.
• Notifications using LISTEN and NOTIFY.
• Copying data using COPY FROM and COPY TO.
• Timeouts and canceling queries using context.Context.
• Automatic connection pooling with circuit breaker support.
• Queries retry on network errors.
• Working with models using ORM and SQL.
• Scanning variables using ORM and SQL.
• SelectOrInsert using on-conflict.
• INSERT ... ON CONFLICT DO UPDATE using ORM.
• Bulk/batch inserts, updates, and deletes.
• Common table expressions using WITH and WrapWith.
• CountEstimate using EXPLAIN to get estimated number of matching rows.
• ORM supports has one, belongs to, has many, and many to many with composite/multi-column primary keys.
• Soft deletes.
• Creating tables from structs.
• ForEach that calls a function for each row returned by the query without loading all rows into the memory.

Installation

go-pg supports 2 last Go versions and requires a Go version with modules support. So make sure to initialize a Go module:

go mod init github.com/my/repo

And then install go-pg (note v10 in the import; omitting it is a popular mistake):

go get github.com/go-pg/pg/v10

Quickstart

package pg_test

import (
    "fmt"

    "github.com/go-pg/pg/v10"
    "github.com/go-pg/pg/v10/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 `pg:"rel:has-one"`
}

func (s Story) String() string {
    return fmt.Sprintf("Story<%d %s %s>", s.Id, s.Title, s.Author)
}

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

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

    story1 := &Story{
        Title:    "Cool story",
        AuthorId: user1.Id,
    }
    _, err = db.Model(story1).Insert()
    if err != nil {
        panic(err)
    }

    // Select user by primary key.
    user := &User{Id: user1.Id}
    err = db.Model(user).WherePK().Select()
    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)
    // 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]>>
}

// createSchema creates database schema for User and Story models.
func createSchema(db *pg.DB) error {
    models := []interface{}{
        (*User)(nil),
        (*Story)(nil),
    }

    for _, model := range models {
        err := db.Model(model).CreateTable(&orm.CreateTableOptions{
            Temp: true,
        })
        if err != nil {
            return err
        }
    }
    return nil
}

See also

• Fast and flexible HTTP router
• Golang msgpack
• Golang message task queue

Documentation

Overview

pg provides PostgreSQL client.

Example (Placeholders)

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

package main import ( "fmt" "github.com/go-pg/pg/v10" ) 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) }

Output:

simple: 42
indexed: 2
named: 4
global: 3
table name: "params"
table alias: "params"
table columns: "params"."x", "params"."y"
columns: "x", "y"

Index

• Variables
• func Array(v interface{}) *types.Array
• func Hstore(v interface{}) *types.Hstore
• func In(slice interface{}) types.ValueAppender
• func InMulti(values ...interface{}) types.ValueAppender
• func SafeQuery(query string, params ...interface{}) *orm.SafeQueryAppender
• func Scan(values ...interface{}) orm.ColumnScanner
• func SetLogger(logger internal.Logging)
• type AfterDeleteHook
• type AfterInsertHook
• type AfterScanHook
• type AfterSelectHook
• type AfterUpdateHook
• type BeforeDeleteHook
• type BeforeInsertHook
• type BeforeScanHook
• type BeforeUpdateHook
• type Conn
  • func (db Conn) AddQueryHook(hook QueryHook)
  • func (db Conn) Begin() (*Tx, error)
  • func (db Conn) BeginContext(ctx context.Context) (*Tx, error)
  • func (db Conn) Close() error
  • func (db *Conn) Context() context.Context
  • func (db Conn) CopyFrom(r io.Reader, query interface{}, params ...interface{}) (res Result, err error)
  • func (db Conn) CopyTo(w io.Writer, query interface{}, params ...interface{}) (res Result, err error)
  • func (db Conn) Exec(query interface{}, params ...interface{}) (res Result, err error)
  • func (db Conn) ExecContext(c context.Context, query interface{}, params ...interface{}) (Result, error)
  • func (db Conn) ExecOne(query interface{}, params ...interface{}) (Result, error)
  • func (db Conn) ExecOneContext(ctx context.Context, query interface{}, params ...interface{}) (Result, error)
  • func (db Conn) Formatter() orm.QueryFormatter
  • func (db Conn) Model(model ...interface{}) *Query
  • func (db Conn) ModelContext(c context.Context, model ...interface{}) *Query
  • func (db Conn) Param(param string) interface{}
  • func (db Conn) Ping(ctx context.Context) error
  • func (db Conn) PoolStats() *PoolStats
  • func (db Conn) Prepare(q string) (*Stmt, error)
  • func (db Conn) Query(model, query interface{}, params ...interface{}) (res Result, err error)
  • func (db Conn) QueryContext(c context.Context, model, query interface{}, params ...interface{}) (Result, error)
  • func (db Conn) QueryOne(model, query interface{}, params ...interface{}) (Result, error)
  • func (db Conn) QueryOneContext(ctx context.Context, model, query interface{}, params ...interface{}) (Result, error)
  • func (db Conn) RunInTransaction(ctx context.Context, fn func(*Tx) error) error
  • func (db *Conn) WithContext(ctx context.Context) *Conn
  • func (db *Conn) WithParam(param string, value interface{}) *Conn
  • func (db *Conn) WithTimeout(d time.Duration) *Conn
• type DB
  • func Connect(opt *Options) *DB
  • func (db DB) AddQueryHook(hook QueryHook)
  • func (db DB) Begin() (*Tx, error)
  • func (db DB) BeginContext(ctx context.Context) (*Tx, error)
  • func (db DB) Close() error
  • func (db *DB) Conn() *Conn
  • func (db *DB) Context() context.Context
  • func (db DB) CopyFrom(r io.Reader, query interface{}, params ...interface{}) (res Result, err error)
  • func (db DB) CopyTo(w io.Writer, query interface{}, params ...interface{}) (res Result, err error)
  • func (db DB) Exec(query interface{}, params ...interface{}) (res Result, err error)
  • func (db DB) ExecContext(c context.Context, query interface{}, params ...interface{}) (Result, error)
  • func (db DB) ExecOne(query interface{}, params ...interface{}) (Result, error)
  • func (db DB) ExecOneContext(ctx context.Context, query interface{}, params ...interface{}) (Result, error)
  • func (db DB) Formatter() orm.QueryFormatter
  • func (db *DB) Listen(ctx context.Context, channels ...string) *Listener
  • func (db DB) Model(model ...interface{}) *Query
  • func (db DB) ModelContext(c context.Context, model ...interface{}) *Query
  • func (db *DB) Options() *Options
  • func (db DB) Param(param string) interface{}
  • func (db DB) Ping(ctx context.Context) error
  • func (db DB) PoolStats() *PoolStats
  • func (db DB) Prepare(q string) (*Stmt, error)
  • func (db DB) Query(model, query interface{}, params ...interface{}) (res Result, err error)
  • func (db DB) QueryContext(c context.Context, model, query interface{}, params ...interface{}) (Result, error)
  • func (db DB) QueryOne(model, query interface{}, params ...interface{}) (Result, error)
  • func (db DB) QueryOneContext(ctx context.Context, model, query interface{}, params ...interface{}) (Result, error)
  • func (db DB) RunInTransaction(ctx context.Context, fn func(*Tx) error) error
  • func (db *DB) String() string
  • 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 DBI
• type Error
• type Ident
• type IntSet
  • func (IntSet) AddColumnScanner(_ orm.ColumnScanner) error
  • func (set *IntSet) Init() error
  • func (set *IntSet) NextColumnScanner() orm.ColumnScanner
  • func (set *IntSet) ScanColumn(col types.ColumnInfo, rd types.Reader, n int) error
• type Ints
  • func (Ints) AddColumnScanner(_ orm.ColumnScanner) error
  • func (ints Ints) AppendValue(dst []byte, quote int) ([]byte, error)
  • func (ints *Ints) Init() error
  • func (ints *Ints) NextColumnScanner() orm.ColumnScanner
  • func (ints *Ints) ScanColumn(col types.ColumnInfo, rd types.Reader, n int) error
• type Listener
  • func (ln *Listener) Channel() <-chan Notification
  • func (ln *Listener) ChannelSize(size int) <-chan Notification
  • func (ln *Listener) Close() error
  • func (ln *Listener) Listen(ctx context.Context, channels ...string) error
  • func (ln *Listener) Receive(ctx context.Context) (channel string, payload string, err error)
  • func (ln *Listener) ReceiveTimeout(ctx context.Context, timeout time.Duration) (channel, payload string, err error)
  • func (ln *Listener) String() string
  • func (ln *Listener) Unlisten(ctx context.Context, channels ...string) error
• type Notification
• type NullTime
• type Options
  • func ParseURL(sURL string) (*Options, error)
• type PoolStats
• type Query
  • func Model(model ...interface{}) *Query
  • func ModelContext(c context.Context, model ...interface{}) *Query
• type QueryEvent
  • func (e *QueryEvent) FormattedQuery() ([]byte, error)
  • func (e *QueryEvent) UnformattedQuery() ([]byte, error)
• type QueryHook
• type Result
• type Safe
• type Stmt
  • func (stmt *Stmt) Close() error
  • func (stmt *Stmt) Exec(params ...interface{}) (Result, error)
  • func (stmt *Stmt) ExecContext(c context.Context, params ...interface{}) (Result, error)
  • func (stmt *Stmt) ExecOne(params ...interface{}) (Result, error)
  • func (stmt *Stmt) ExecOneContext(c context.Context, params ...interface{}) (Result, error)
  • func (stmt *Stmt) Query(model interface{}, params ...interface{}) (Result, error)
  • func (stmt *Stmt) QueryContext(c context.Context, model interface{}, params ...interface{}) (Result, error)
  • func (stmt *Stmt) QueryOne(model interface{}, params ...interface{}) (Result, error)
  • func (stmt *Stmt) QueryOneContext(c context.Context, model interface{}, params ...interface{}) (Result, error)
• type Strings
  • func (Strings) AddColumnScanner(_ orm.ColumnScanner) error
  • func (strings Strings) AppendValue(dst []byte, quote int) ([]byte, error)
  • func (strings *Strings) Init() error
  • func (strings *Strings) NextColumnScanner() orm.ColumnScanner
  • func (strings *Strings) ScanColumn(col types.ColumnInfo, rd types.Reader, n int) error
• type Tx
  • func (tx *Tx) Begin() (*Tx, error)
  • func (tx *Tx) Close() error
  • func (tx *Tx) CloseContext(ctx context.Context) error
  • func (tx *Tx) Commit() error
  • func (tx *Tx) CommitContext(ctx context.Context) error
  • func (tx *Tx) Context() context.Context
  • func (tx *Tx) CopyFrom(r io.Reader, query interface{}, params ...interface{}) (res Result, err error)
  • func (tx *Tx) CopyTo(w io.Writer, query interface{}, params ...interface{}) (res Result, err error)
  • func (tx *Tx) Exec(query interface{}, params ...interface{}) (Result, error)
  • func (tx *Tx) ExecContext(c context.Context, query interface{}, params ...interface{}) (Result, error)
  • func (tx *Tx) ExecOne(query interface{}, params ...interface{}) (Result, error)
  • func (tx *Tx) ExecOneContext(c context.Context, query interface{}, params ...interface{}) (Result, error)
  • func (tx *Tx) Formatter() orm.QueryFormatter
  • func (tx *Tx) Model(model ...interface{}) *Query
  • func (tx *Tx) ModelContext(c context.Context, model ...interface{}) *Query
  • func (tx *Tx) Prepare(q string) (*Stmt, error)
  • func (tx *Tx) Query(model interface{}, query interface{}, params ...interface{}) (Result, error)
  • func (tx *Tx) QueryContext(c context.Context, model interface{}, query interface{}, params ...interface{}) (Result, error)
  • func (tx *Tx) QueryOne(model interface{}, query interface{}, params ...interface{}) (Result, error)
  • func (tx *Tx) QueryOneContext(c context.Context, model interface{}, query interface{}, params ...interface{}) (Result, error)
  • func (tx *Tx) Rollback() error
  • func (tx *Tx) RollbackContext(ctx context.Context) error
  • func (tx *Tx) RunInTransaction(ctx context.Context, fn func(*Tx) error) error
  • func (tx *Tx) Stmt(stmt *Stmt) *Stmt

Examples

• Package (Placeholders)
• Array
• Connect
• DB (ArrayValueScanner)
• DB (JsonUseNumber)
• DB.Begin
• DB.CopyFrom
• DB.Exec
• DB.Model
• DB.Model (BelongsTo)
• DB.Model (BulkDelete)
• DB.Model (BulkInsert)
• DB.Model (BulkInsertSlice)
• DB.Model (BulkUpdate)
• DB.Model (BulkUpdateSlice)
• DB.Model (CompositeType)
• DB.Model (Count)
• DB.Model (CountEstimate)
• DB.Model (CreateTable)
• DB.Model (CustomType)
• DB.Model (Delete)
• DB.Model (DeleteMultipleRows)
• DB.Model (DiscardUnknownColumns)
• DB.Model (Exists)
• DB.Model (ForEach)
• DB.Model (HasMany)
• DB.Model (HasManySelf)
• DB.Model (HasOne)
• DB.Model (HstoreStructTag)
• DB.Model (Insert)
• DB.Model (InsertDynamicTableName)
• DB.Model (InsertOnConflictDoNothing)
• DB.Model (InsertOnConflictDoUpdate)
• DB.Model (ManyToMany)
• DB.Model (ManyToManySelf)
• DB.Model (NullEmptyValue)
• DB.Model (PostgresArrayStructTag)
• DB.Model (Select)
• DB.Model (SelectAllColumns)
• DB.Model (SelectAndCount)
• DB.Model (SelectApplyFunc)
• DB.Model (SelectFirstRow)
• DB.Model (SelectGroupBy)
• DB.Model (SelectLastRow)
• DB.Model (SelectOrInsert)
• DB.Model (SelectSQLExpression)
• DB.Model (SelectSomeColumns)
• DB.Model (SelectSomeColumnsIntoVars)
• DB.Model (SelectWhereGroup)
• DB.Model (SelectWhereIn)
• DB.Model (SelectWith)
• DB.Model (SelectWrapWith)
• DB.Model (SoftDelete)
• DB.Model (SoftDeleteCustom)
• DB.Model (Update)
• DB.Model (UpdateNotZero)
• DB.Model (UpdateSetValues)
• DB.Model (UpdateSomeColumns)
• DB.Model (UpdateSomeColumns2)
• DB.Model (UpdateUseZeroBool)
• DB.Prepare
• DB.Query
• DB.QueryOne
• DB.QueryOne (Returning_id)
• DB.RunInTransaction
• DB.WithTimeout
• Error
• Hstore
• Ident
• Ints
• Listener
• Safe
• Scan
• Strings

Constants

This section is empty.

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.

var ErrTxDone = errors.New("pg: transaction has already been committed or rolled back")

ErrTxDone is returned by any operation that is performed on a transaction that has already been committed or rolled back.

Functions

func Array

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 `pg:",array"`

Example

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 Hstore

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

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

- map[string]string

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

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

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 SafeQuery

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

SafeQuery replaces any placeholders found in the query.

func Scan

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

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

Example

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

Output:

foo bar

func SetLogger

func SetLogger(logger internal.Logging)

SetLogger sets the logger to the given one.

Types

type AfterDeleteHook

type AfterDeleteHook = orm.AfterDeleteHook

type AfterInsertHook

type AfterInsertHook = orm.AfterInsertHook

type AfterScanHook

type AfterScanHook = orm.AfterScanHook

type AfterSelectHook

type AfterSelectHook = orm.AfterSelectHook

type AfterUpdateHook

type AfterUpdateHook = orm.AfterUpdateHook

type BeforeDeleteHook

type BeforeDeleteHook = orm.BeforeDeleteHook

type BeforeInsertHook

type BeforeInsertHook = orm.BeforeInsertHook

type BeforeScanHook

type BeforeScanHook = orm.BeforeScanHook

type BeforeUpdateHook

type BeforeUpdateHook = orm.BeforeUpdateHook

type Conn

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

Conn represents a single database connection rather than a pool of database connections. Prefer running queries from DB unless there is a specific need for a continuous single database connection.

A Conn must call Close to return the connection to the database pool and may do so concurrently with a running query.

After a call to Close, all operations on the connection fail.

func (Conn) AddQueryHook

func (db Conn) AddQueryHook(hook QueryHook)

AddQueryHook adds a hook into query processing.

func (Conn) Begin

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

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

func (Conn) BeginContext

func (db Conn) BeginContext(ctx context.Context) (*Tx, error)

func (Conn) Close

func (db Conn) 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 (*Conn) Context

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

Context returns DB context.

func (Conn) CopyFrom

func (db Conn) CopyFrom(r io.Reader, query interface{}, params ...interface{}) (res Result, err error)

CopyFrom copies data from reader to a table.

func (Conn) CopyTo

func (db Conn) CopyTo(w io.Writer, query interface{}, params ...interface{}) (res Result, err error)

CopyTo copies data from a table to writer.

func (Conn) Exec

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

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

func (Conn) ExecContext

func (db Conn) ExecContext(c context.Context, query interface{}, params ...interface{}) (Result, error)

func (Conn) ExecOne

func (db Conn) ExecOne(query interface{}, params ...interface{}) (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 (Conn) ExecOneContext

func (db Conn) ExecOneContext(ctx context.Context, query interface{}, params ...interface{}) (Result, error)

func (Conn) Formatter

func (db Conn) Formatter() orm.QueryFormatter

func (Conn) Model

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

Model returns new query for the model.

func (Conn) ModelContext

func (db Conn) ModelContext(c context.Context, model ...interface{}) *Query

func (Conn) Param

func (db Conn) Param(param string) interface{}

Param returns value for the param.

func (Conn) Ping

func (db Conn) Ping(ctx context.Context) error

Ping verifies a connection to the database is still alive, establishing a connection if necessary.

func (Conn) PoolStats

func (db Conn) PoolStats() *PoolStats

PoolStats returns connection pool stats.

func (Conn) Prepare

func (db Conn) 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.

func (Conn) Query

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

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

func (Conn) QueryContext

func (db Conn) QueryContext(c context.Context, model, query interface{}, params ...interface{}) (Result, error)

func (Conn) QueryOne

func (db Conn) QueryOne(model, query interface{}, params ...interface{}) (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 (Conn) QueryOneContext

func (db Conn) QueryOneContext(
	ctx context.Context, model, query interface{}, params ...interface{},
) (Result, error)

func (Conn) RunInTransaction

func (db Conn) RunInTransaction(ctx context.Context, fn func(*Tx) error) error

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

func (*Conn) WithContext

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

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

func (*Conn) WithParam

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

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

func (*Conn) WithTimeout

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

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

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.

Example (ArrayValueScanner)

var dst MyArrayValueScanner _, err := pgdb.QueryOne(pg.Scan(pg.Array(&dst)), `SELECT array_agg(id) from generate_series(0, 10) AS id`) panicIf(err) fmt.Println(dst.sum)

Output:

55

Example (JsonUseNumber)

type Event struct { Id int Data map[string]interface{} `pg:",json_use_number"` } db := pg.Connect(pgOptions()) defer db.Close() err := db.Model((*Event)(nil)).CreateTable(&orm.CreateTableOptions{ Temp: true, }) if err != nil { panic(err) } event := &Event{ Data: map[string]interface{}{ "price": 1.23, }, } _, err = db.Model(event).Insert() 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

func Connect(opt *Options) *DB

Connect connects to a database using provided options.

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

Example

db := pg.Connect(&pg.Options{ User: "postgres", Password: "", Database: "postgres", }) defer db.Close() var n int _, err := db.QueryOne(pg.Scan(&n), "SELECT 1") panicIf(err) fmt.Println(n)

Output:

1

func (DB) AddQueryHook

func (db DB) AddQueryHook(hook QueryHook)

AddQueryHook adds a hook into query processing.

func (DB) Begin

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

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

Example

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() err := incrInTx(db) panicIf(err) }() } wg.Wait() var counter int _, err := db.QueryOne(pg.Scan(&counter), `SELECT counter FROM tx_test`) panicIf(err) fmt.Println(counter)

Output:

10

func (DB) BeginContext

func (db DB) BeginContext(ctx context.Context) (*Tx, error)

func (DB) Close

func (db DB) Close() error

Close closes the database client, releasing any open resources.

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

func (*DB) Conn

func (db *DB) Conn() *Conn

Conn returns a single connection from the connection pool. Queries run on the same Conn will be run in the same database session.

Every Conn must be returned to the database pool after use by calling Conn.Close.

func (*DB) Context

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

Context returns DB context.

func (DB) CopyFrom

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

CopyFrom copies data from reader to a table.

Example

_, err := pgdb.Exec(`CREATE TEMP TABLE words(word text, len int)`) panicIf(err) r := strings.NewReader("hello,5\nfoo,3\n") _, err = pgdb.CopyFrom(r, `COPY words FROM STDIN WITH CSV`) panicIf(err) var buf bytes.Buffer _, err = pgdb.CopyTo(&buf, `COPY words TO STDOUT WITH CSV`) panicIf(err) fmt.Println(buf.String())

Output:

hello,5
foo,3

func (DB) CopyTo

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

CopyTo copies data from a table to writer.

func (DB) Exec

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

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

Example

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

Output:

-1

func (DB) ExecContext

func (db DB) ExecContext(c context.Context, query interface{}, params ...interface{}) (Result, error)

func (DB) ExecOne

func (db DB) ExecOne(query interface{}, params ...interface{}) (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) ExecOneContext

func (db DB) ExecOneContext(ctx context.Context, query interface{}, params ...interface{}) (Result, error)

func (DB) Formatter

func (db DB) Formatter() orm.QueryFormatter

func (*DB) Listen

func (db *DB) Listen(ctx context.Context, channels ...string) *Listener

Listen listens for notifications sent with NOTIFY command.

func (DB) Model

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

Model returns new query for the model.

Example

package main import ( "fmt" "github.com/go-pg/pg/v10" "github.com/go-pg/pg/v10/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 `pg:"rel:has-one"` } 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.Model(user1).Insert() if err != nil { panic(err) } _, err = db.Model(&User{ Name: "root", Emails: []string{"root1@root", "root2@root"}, }).Insert() if err != nil { panic(err) } story1 := &Story{ Title: "Cool story", AuthorId: user1.Id, } _, err = db.Model(story1).Insert() if err != nil { panic(err) } // Select user by primary key. user := &User{Id: user1.Id} err = db.Model(user).WherePK().Select() 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) } // createSchema creates database schema for User and Story models. func createSchema(db *pg.DB) error { models := []interface{}{ (*User)(nil), (*Story)(nil), } for _, model := range models { err := db.Model(model).CreateTable(&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 `pg:"rel:belongs-to"` } 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}

Example (BulkDelete)

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

Example (BulkInsert)

db := modelDB() book1 := &Book{ Title: "new book 1", } book2 := &Book{ Title: "new book 2", } _, err := db.Model(book1, book2).Insert() 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 (BulkInsertSlice)

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

Output:

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

Example (BulkUpdate)

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

Example (BulkUpdateSlice)

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

Example (CompositeType)

package main import ( "fmt" "github.com/go-pg/pg/v10/orm" ) type InventoryItem struct { Name string SupplierID int Price float64 } type OnHand struct { tableName struct{} `pg:"on_hand"` Item InventoryItem `pg:"composite:inventory_item"` Count int } func main() { db := connect() defer db.Close() err := db.Model((*OnHand)(nil)).DropTable(&orm.DropTableOptions{ IfExists: true, Cascade: true, }) panicIf(err) err = db.Model((*InventoryItem)(nil)).DropComposite(&orm.DropCompositeOptions{ IfExists: true, }) panicIf(err) err = db.Model((*InventoryItem)(nil)).CreateComposite(nil) panicIf(err) err = db.Model((*OnHand)(nil)).CreateTable(nil) panicIf(err) _, err = db.Model(&OnHand{ Item: InventoryItem{ Name: "fuzzy dice", SupplierID: 42, Price: 1.99, }, Count: 1000, }).Insert() panicIf(err) onHand := new(OnHand) err = db.Model(onHand).Select() panicIf(err) fmt.Println(onHand.Item.Name, onHand.Item.Price, onHand.Count) }

Output:

fuzzy dice 1.99 1000

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

type Model1 struct { Id int } type Model2 struct { Id int Name string Model1Id int `pg:"on_delete:RESTRICT,on_update: CASCADE"` Model1 *Model1 `pg:"rel:has-one"` } for _, model := range []interface{}{&Model1{}, &Model2{}} { err := pgdb.Model(model).CreateTable(&orm.CreateTableOptions{ Temp: true, // create temp table FKConstraints: true, }) panicIf(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' `) panicIf(err) fmt.Println(info)

Output:

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

Example (CustomType)

package main import ( "fmt" "time" "github.com/go-pg/pg/v10/orm" "github.com/go-pg/pg/v10/types" ) const pgTimeFormat = "15:04:05.999999999" type Time struct { time.Time } var _ types.ValueAppender = (*Time)(nil) func (tm Time) AppendValue(b []byte, flags int) ([]byte, error) { if flags == 1 { b = append(b, '\'') } b = tm.UTC().AppendFormat(b, pgTimeFormat) if flags == 1 { b = append(b, '\'') } return b, nil } var _ types.ValueScanner = (*Time)(nil) func (tm *Time) ScanValue(rd types.Reader, n int) error { if n <= 0 { tm.Time = time.Time{} return nil } tmp, err := rd.ReadFullTemp() if err != nil { return err } tm.Time, err = time.ParseInLocation(pgTimeFormat, string(tmp), time.UTC) if err != nil { return err } return nil } type Event struct { Id int Time Time `pg:"type:time"` } func main() { db := connect() defer db.Close() err := db.Model((*Event)(nil)).CreateTable(&orm.CreateTableOptions{ Temp: true, }) panicIf(err) _, err = db.Model(&Event{ Time: Time{time.Date(0, 0, 0, 12, 00, 00, 00, time.UTC)}, // noon }).Insert() panicIf(err) evt := new(Event) err = db.Model(evt).Select() panicIf(err) fmt.Println(evt.Time) }

Output:

0000-01-01 12:00:00 +0000 UTC

Example (Delete)

db := modelDB() book := &Book{ Title: "title 1", AuthorID: 1, } _, err := db.Model(book).Insert() if err != nil { panic(err) } _, err = db.Model(book).WherePK().Delete() if err != nil { panic(err) } err = db.Model(book).WherePK().Select() fmt.Println(err)

Output:

pg: no rows in result set

Example (DeleteMultipleRows)

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

Example (DiscardUnknownColumns)

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 (prefix the column with underscore or use discard_unknown_columns)
Model2: <nil>

Example (Exists)

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

Example (ForEach)

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

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 `pg:"rel:has-many"` } 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 *pg.Query) (*pg.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:"rel:has-many,join_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

Example (HasOne)

type Profile struct { Id int Lang string } // User has one profile. type User struct { Id int Name string ProfileId int Profile *Profile `pg:"rel:has-one"` } 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}

Example (HstoreStructTag)

type Item struct { Id int64 Attrs map[string]string `pg:",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.Model(&item1).Insert() 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]}

Example (Insert)

db := modelDB() book := &Book{ Title: "new book", AuthorID: 1, } _, err := db.Model(book).Insert() if err != nil { panic(err) } fmt.Println(book)

Output:

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

Example (InsertDynamicTableName)

type NamelessModel struct { tableName struct{} `pg:"_"` // "_" means no name Id int } db := modelDB() err := db.Model((*NamelessModel)(nil)).Table("dynamic_name").CreateTable(nil) panicIf(err) row123 := &NamelessModel{ Id: 123, } _, err = db.Model(row123).Table("dynamic_name").Insert() panicIf(err) row := new(NamelessModel) err = db.Model(row).Table("dynamic_name").First() panicIf(err) fmt.Println("id is", row.Id) err = db.Model((*NamelessModel)(nil)).Table("dynamic_name").DropTable(nil) panicIf(err)

Output:

id is 123

Example (InsertOnConflictDoNothing)

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.Model(book).WherePK().Delete() if err != nil { panic(err) }

Output:

created
did nothing

Example (InsertOnConflictDoUpdate)

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.Model(book).WherePK().Select() if err != nil { panic(err) } fmt.Println(book) } _, err := db.Model(book).WherePK().Delete() if err != nil { panic(err) }

Output:

Book<Id=100 Title="title version #0">
Book<Id=100 Title="title version #1">

Example (ManyToMany)

package main import ( "fmt" "github.com/go-pg/pg/v10" "github.com/go-pg/pg/v10/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 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.Model(v).Insert() 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 *pg.Query) (*pg.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) } func createManyToManyTables(db *pg.DB) error { models := []interface{}{ (*Order)(nil), (*Item)(nil), (*OrderToItem)(nil), } for _, model := range models { err := db.Model(model).CreateTable(&orm.CreateTableOptions{ Temp: true, }) if err != nil { return err } } return nil }

Output:

Order 1 Items 1 2
Order 1 Items 2 1

Example (ManyToManySelf)

package main import ( "fmt" "github.com/go-pg/pg/v10" "github.com/go-pg/pg/v10/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,join_fk: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.Model(model).CreateTable(&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.Model(v).Insert() 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) }

Output:

Elem 1
Subelems 2 3

Example (NullEmptyValue)

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

Example (PostgresArrayStructTag)

type Item struct { Id int64 Emails []string `pg:",array"` // marshalled as PostgreSQL array Numbers [][]int `pg:",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.Model(item1).Insert() 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]]}

Example (Select)

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

Output:

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

Example (SelectAllColumns)

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

Example (SelectApplyFunc)

db := modelDB() var authorId int var editorId int filter := func(q *pg.Query) (*pg.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 (SelectFirstRow)

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

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

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

Example (SelectSQLExpression)

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

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

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

db := modelDB() var books []Book err := db.Model(&books). WhereGroup(func(q *pg.Query) (*pg.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">]

Example (SelectWhereIn)

db := modelDB() var books []Book err := db.Model(&books).WhereIn("id IN (?)", []int{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 (SelectWith)

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

Example (SelectWrapWith)

// 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">]

Example (SoftDelete)

type Flight struct { Id int Name string DeletedAt time.Time `pg:",soft_delete"` } err := pgdb.Model((*Flight)(nil)).CreateTable(&orm.CreateTableOptions{ Temp: true, }) panicIf(err) flight1 := &Flight{ Id: 1, } _, err = pgdb.Model(flight1).Insert() panicIf(err) // Soft delete. _, err = pgdb.Model(flight1).WherePK().Delete() 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.Model(flight1).WherePK().ForceDelete() panicIf(err) // Count soft deleted flights. deletedCount, err = pgdb.Model((*Flight)(nil)).Deleted().Count() panicIf(err) fmt.Println("deleted count", deletedCount)

Output:

count 0
deleted count 1
deleted count 0

Example (SoftDeleteCustom)

package main import ( "fmt" "time" "github.com/go-pg/pg/v10/types" ) type CustomTime struct { Time time.Time } var _ types.ValueScanner = (*CustomTime)(nil) func (tm *CustomTime) ScanValue(rd types.Reader, n int) error { var err error tm.Time, err = types.ScanTime(rd, n) return err } var _ types.ValueAppender = (*CustomTime)(nil) func (tm *CustomTime) AppendValue(b []byte, flags int) ([]byte, error) { return types.AppendTime(b, tm.Time, flags), nil } type Video struct { Id int Name string DeletedAt CustomTime `pg:",soft_delete"` } func main() { video1 := &Video{ Id: 1, } _, err := pgdb.Model(video1).Insert() panicIf(err) // Soft delete. _, err = pgdb.Model(video1).WherePK().Delete() panicIf(err) // Count visible videos. count, err := pgdb.Model((*Video)(nil)).Count() panicIf(err) fmt.Println("count", count) // Count soft deleted videos. deletedCount, err := pgdb.Model((*Video)(nil)).Deleted().Count() panicIf(err) fmt.Println("deleted count", deletedCount) // Actually delete the video. _, err = pgdb.Model(video1).WherePK().ForceDelete() panicIf(err) // Count soft deleted videos. deletedCount, err = pgdb.Model((*Video)(nil)).Deleted().Count() panicIf(err) fmt.Println("deleted count", deletedCount) }

Output:

count 0
deleted count 1
deleted count 0

Example (Update)

db := modelDB() book := &Book{ID: 1} err := db.Model(book).WherePK().Select() if err != nil { panic(err) } book.Title = "updated book 1" _, err = db.Model(book).WherePK().Update() if err != nil { panic(err) } err = db.Model(book).WherePK().Select() if err != nil { panic(err) } fmt.Println(book)

Output:

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

Example (UpdateNotZero)

db := modelDB() book := &Book{ ID: 1, Title: "updated book 1", } _, err := db.Model(book).WherePK().UpdateNotZero() 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">

Example (UpdateSetValues)

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

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

Example (UpdateSomeColumns2)

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

Example (UpdateUseZeroBool)

type Event struct { ID int Active bool `pg:",use_zero"` } db := pg.Connect(pgOptions()) defer db.Close() err := db.Model((*Event)(nil)).CreateTable(&orm.CreateTableOptions{ Temp: true, }) if err != nil { panic(err) } event := &Event{ ID: 1, Active: true, } _, err = db.Model(event).Insert() if err != nil { panic(err) } fmt.Println(event) event.Active = false _, err = db.Model(event).WherePK().UpdateNotZero() if err != nil { panic(err) } event2 := new(Event) err = db.Model(event2).Where("id = ?", 1).Select() if err != nil { panic(err) } fmt.Println(event2)

Output:

&{1 true}
&{1 false}

func (DB) ModelContext

func (db DB) ModelContext(c context.Context, model ...interface{}) *Query

func (*DB) Options

func (db *DB) Options() *Options

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

func (DB) Param

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

Param returns value for the param.

func (DB) Ping

func (db DB) Ping(ctx context.Context) error

Ping verifies a connection to the database is still alive, establishing a connection if necessary.

func (DB) PoolStats

func (db DB) PoolStats() *PoolStats

PoolStats returns connection pool stats.

func (DB) Prepare

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

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

Example

stmt, err := pgdb.Prepare(`SELECT $1::text, $2::text`) panicIf(err) var s1, s2 string _, err = stmt.QueryOne(pg.Scan(&s1, &s2), "foo", "bar") panicIf(err) fmt.Println(s1, s2)

Output:

foo bar

func (DB) Query

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

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/v10" ) 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) panicIf(err) user1 := &User{ Name: "admin", Emails: []string{"admin1@admin", "admin2@admin"}, } err = CreateUser(db, user1) panicIf(err) err = CreateUser(db, &User{ Name: "root", Emails: []string{"root1@root", "root2@root"}, }) panicIf(err) story1 := &Story{ Title: "Cool story", AuthorId: user1.Id, } err = CreateStory(db, story1) panicIf(err) user, err := GetUser(db, user1.Id) panicIf(err) users, err := GetUsers(db) panicIf(err) story, err := GetStory(db, story1.Id) panicIf(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) QueryContext

func (db DB) QueryContext(c context.Context, model, query interface{}, params ...interface{}) (Result, error)

func (DB) QueryOne

func (db DB) QueryOne(model, query interface{}, params ...interface{}) (Result, error)

QueryOne acts like Query, but query must return only one row. It returns ErrNoRows error when query returns zero rows or ErrMultiRows when query returns multiple rows.

Example

var user struct { Name string } res, err := pgdb.QueryOne(&user, ` WITH users (name) AS (VALUES (?)) SELECT * FROM users `, "admin") panicIf(err) fmt.Println(res.RowsAffected()) fmt.Println(user)

Output:

1
{admin}

Example (Returning_id)

_, err := pgdb.Exec(`CREATE TEMP TABLE users(id serial, name varchar(500))`) panicIf(err) var user struct { Id int32 Name string } user.Name = "admin" _, err = pgdb.QueryOne(&user, ` INSERT INTO users (name) VALUES (?name) RETURNING id `, &user) panicIf(err) fmt.Println(user)

Output:

{1 admin}

func (DB) QueryOneContext

func (db DB) QueryOneContext(
	ctx context.Context, model, query interface{}, params ...interface{},
) (Result, error)

func (DB) RunInTransaction

func (db DB) RunInTransaction(ctx context.Context, fn func(*Tx) error) error

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

Example

db := txExample() incrInTx := func(db *pg.DB) error { // Transaction is automatically rolled back on error. return db.RunInTransaction(ctx, 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() err := incrInTx(db) panicIf(err) }() } wg.Wait() var counter int _, err := db.QueryOne(pg.Scan(&counter), `SELECT counter FROM tx_test`) panicIf(err) fmt.Println(counter)

Output:

10

func (*DB) String

func (db *DB) String() string

func (*DB) WithContext

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

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

func (*DB) WithParam

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

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

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

Example

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

type DBI

type DBI interface {
	Model(model ...interface{}) *Query
	ModelContext(c context.Context, model ...interface{}) *Query

	Exec(query interface{}, params ...interface{}) (Result, error)
	ExecContext(c context.Context, query interface{}, params ...interface{}) (Result, error)
	ExecOne(query interface{}, params ...interface{}) (Result, error)
	ExecOneContext(c context.Context, query interface{}, params ...interface{}) (Result, error)
	Query(model, query interface{}, params ...interface{}) (Result, error)
	QueryContext(c context.Context, model, query interface{}, params ...interface{}) (Result, error)
	QueryOne(model, query interface{}, params ...interface{}) (Result, error)
	QueryOneContext(c context.Context, model, query interface{}, params ...interface{}) (Result, error)

	Begin() (*Tx, error)
	RunInTransaction(ctx context.Context, fn func(*Tx) error) error

	CopyFrom(r io.Reader, query interface{}, params ...interface{}) (Result, error)
	CopyTo(w io.Writer, query interface{}, params ...interface{}) (Result, error)
}

DBI is a DB interface implemented by *DB and *Tx.

type Error

type Error interface {
	error

	// Field returns a string value associated with an error field.
	//
	// https://www.postgresql.org/docs/10/static/protocol-error-fields.html
	Field(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

Example

video := &Video{ Id: 123, } _, err := pgdb.Model(video).Insert() panicIf(err) _, err = pgdb.Model(video).Insert() if err != nil { pgErr, ok := err.(pg.Error) if ok && pgErr.IntegrityViolation() { fmt.Println("video already exists:", err) } else if pgErr.Field('S') == "PANIC" { panic(err) } }

Output:

video already exists: ERROR #23505 duplicate key value violates unique constraint "videos_pkey"

type Ident

type Ident = types.Ident

Ident represents a SQL identifier, e.g. table or column name.

Example

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

Output:

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

type IntSet

type IntSet map[int64]struct{}

IntSet is a set of int64 values.

func (IntSet) AddColumnScanner

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

AddColumnScanner ...

func (*IntSet) Init

func (set *IntSet) Init() error

Init initializes the IntSet.

func (*IntSet) NextColumnScanner

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

NextColumnScanner ...

func (*IntSet) ScanColumn

func (set *IntSet) ScanColumn(col types.ColumnInfo, rd types.Reader, n int) error

ScanColumn scans the columns and appends them to `IntSet`.

type Ints

type Ints []int64

Ints is a type alias for a slice of int64 values.

Example

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

Output:

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

func (Ints) AddColumnScanner

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

AddColumnScanner ...

func (Ints) AppendValue

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

AppendValue appends the values from `ints` to the given byte slice.

func (*Ints) Init

func (ints *Ints) Init() error

Init initializes the Int slice.

func (*Ints) NextColumnScanner

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

NewColumnScanner ...

func (*Ints) ScanColumn

func (ints *Ints) ScanColumn(col types.ColumnInfo, rd types.Reader, n int) error

ScanColumn scans the columns and appends them to `ints`.

type Listener

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

Listener listens for notifications sent with NOTIFY command. It's NOT safe for concurrent use by multiple goroutines except the Channel API.

Example

ln := pgdb.Listen(ctx, "mychan") defer ln.Close() ch := ln.Channel() go func() { time.Sleep(time.Millisecond) _, err := pgdb.Exec("NOTIFY mychan, ?", "hello world") panicIf(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. It periodically sends Ping notification to test connection health.

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

func (*Listener) ChannelSize

func (ln *Listener) ChannelSize(size int) <-chan Notification

ChannelSize is like Channel, but creates a Go channel with specified buffer size.

func (*Listener) Close

func (ln *Listener) Close() error

Close closes the listener, releasing any open resources.

func (*Listener) Listen

func (ln *Listener) Listen(ctx context.Context, channels ...string) error

Listen starts listening for notifications on channels.

func (*Listener) Receive

func (ln *Listener) Receive(ctx context.Context) (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

func (ln *Listener) ReceiveTimeout(
	ctx context.Context, 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.

func (*Listener) String

func (ln *Listener) String() string

func (*Listener) Unlisten

func (ln *Listener) Unlisten(ctx context.Context, channels ...string) error

Unlisten stops listening for notifications on channels.

type Notification

type Notification struct {
	Channel string
	Payload string
}

Notification which is received with LISTEN command.

type NullTime

type NullTime = types.NullTime

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

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(ctx context.Context, network, addr string) (net.Conn, error)

	// Hook that is called after new connection is established
	// and user is authenticated.
	OnConnect func(ctx context.Context, cn *Conn) error

	User     string
	Password string
	Database string

	// ApplicationName is the application name. Used in logs on Pg side.
	// Only available from pg-9.0.
	ApplicationName string

	// TLS config for secure connections.
	TLSConfig *tls.Config

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

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

Options contains database connection options.

func ParseURL

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

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

type PoolStats

type PoolStats pool.Stats

PoolStats contains the stats of a connection pool.

type Query

type Query = orm.Query

func Model

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

Model returns a new query for the optional model.

func ModelContext

func ModelContext(c context.Context, model ...interface{}) *Query

ModelContext returns a new query for the optional model with a context.

type QueryEvent

type QueryEvent struct {
	StartTime time.Time
	DB        orm.DB
	Model     interface{}
	Query     interface{}
	Params    []interface{}

	Result Result
	Err    error

	Stash map[interface{}]interface{}
	// contains filtered or unexported fields
}

QueryEvent ...

func (*QueryEvent) FormattedQuery

func (e *QueryEvent) FormattedQuery() ([]byte, error)

FormattedQuery returns the formatted query of a query event. The query is only valid until the query Result is returned to the user.

func (*QueryEvent) UnformattedQuery

func (e *QueryEvent) UnformattedQuery() ([]byte, error)

UnformattedQuery returns the unformatted query of a query event. The query is only valid until the query Result is returned to the user.

type QueryHook

type QueryHook interface {
	BeforeQuery(context.Context, *QueryEvent) (context.Context, error)
	AfterQuery(context.Context, *QueryEvent) error
}

QueryHook ...

type Result

type Result = orm.Result

Result summarizes an executed SQL command.

type Safe

type Safe = types.Safe

Safe represents a safe SQL query.

Example

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

Output:

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

type Stmt

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

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

func (*Stmt) Close

func (stmt *Stmt) Close() error

Close closes the statement.

func (*Stmt) Exec

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

Exec executes a prepared statement with the given parameters.

func (*Stmt) ExecContext

func (stmt *Stmt) ExecContext(c context.Context, params ...interface{}) (Result, error)

ExecContext executes a prepared statement with the given parameters.

func (*Stmt) ExecOne

func (stmt *Stmt) ExecOne(params ...interface{}) (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) ExecOneContext

func (stmt *Stmt) ExecOneContext(c context.Context, params ...interface{}) (Result, error)

ExecOneContext acts like ExecOne but additionally receives a context.

func (*Stmt) Query

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

Query executes a prepared query statement with the given parameters.

func (*Stmt) QueryContext

func (stmt *Stmt) QueryContext(c context.Context, model interface{}, params ...interface{}) (Result, error)

QueryContext acts like Query but additionally receives a context.

func (*Stmt) QueryOne

func (stmt *Stmt) QueryOne(model interface{}, params ...interface{}) (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 (*Stmt) QueryOneContext

func (stmt *Stmt) QueryOneContext(c context.Context, model interface{}, params ...interface{}) (Result, error)

QueryOneContext acts like QueryOne but additionally receives a context.

type Strings

type Strings []string

Strings is a type alias for a slice of strings.

Example

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

Output:

[foo bar]

func (Strings) AddColumnScanner

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

AddColumnScanner ...

func (Strings) AppendValue

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

AppendValue appends the values from `strings` to the given byte slice.

func (*Strings) Init

func (strings *Strings) Init() error

Init initializes the Strings slice.

func (*Strings) NextColumnScanner

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

NextColumnScanner ...

func (*Strings) ScanColumn

func (strings *Strings) ScanColumn(col types.ColumnInfo, rd types.Reader, n int) error

ScanColumn scans the columns and appends them to `strings`.

type Tx

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

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

Begin returns current transaction. It does not start new transaction.

func (*Tx) Close

func (tx *Tx) Close() error

func (*Tx) CloseContext

func (tx *Tx) CloseContext(ctx context.Context) error

Close calls Rollback if the tx has not already been committed or rolled back.

func (*Tx) Commit

func (tx *Tx) Commit() error

func (*Tx) CommitContext

func (tx *Tx) CommitContext(ctx context.Context) error

Commit commits the transaction.

func (*Tx) Context

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

Context returns the context.Context of the transaction.

func (*Tx) CopyFrom

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

CopyFrom is an alias for DB.CopyFrom.

func (*Tx) CopyTo

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

CopyTo is an alias for DB.CopyTo.

func (*Tx) Exec

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

Exec is an alias for DB.Exec.

func (*Tx) ExecContext

func (tx *Tx) ExecContext(c context.Context, query interface{}, params ...interface{}) (Result, error)

ExecContext acts like Exec but additionally receives a context.

func (*Tx) ExecOne

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

ExecOne is an alias for DB.ExecOne.

func (*Tx) ExecOneContext

func (tx *Tx) ExecOneContext(c context.Context, query interface{}, params ...interface{}) (Result, error)

ExecOneContext acts like ExecOne but additionally receives a context.

func (*Tx) Formatter

func (tx *Tx) Formatter() orm.QueryFormatter

Formatter is an alias for DB.Formatter.

func (*Tx) Model

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

Model is an alias for DB.Model.

func (*Tx) ModelContext

func (tx *Tx) ModelContext(c context.Context, model ...interface{}) *Query

ModelContext acts like Model but additionally receives a context.

func (*Tx) Prepare

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

Prepare creates a prepared statement for use within a transaction.

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

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

func (*Tx) Query

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

Query is an alias for DB.Query.

func (*Tx) QueryContext

func (tx *Tx) QueryContext(
	c context.Context,
	model interface{},
	query interface{},
	params ...interface{},
) (Result, error)

QueryContext acts like Query but additionally receives a context.

func (*Tx) QueryOne

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

QueryOne is an alias for DB.QueryOne.

func (*Tx) QueryOneContext

func (tx *Tx) QueryOneContext(
	c context.Context,
	model interface{},
	query interface{},
	params ...interface{},
) (Result, error)

QueryOneContext acts like QueryOne but additionally receives a context.

func (*Tx) Rollback

func (tx *Tx) Rollback() error

func (*Tx) RollbackContext

func (tx *Tx) RollbackContext(ctx context.Context) error

Rollback aborts the transaction.

func (*Tx) RunInTransaction

func (tx *Tx) RunInTransaction(ctx context.Context, fn func(*Tx) error) error

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

func (*Tx) Stmt

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

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