go-pg/pg

PostgreSQL client and ORM for Golang

Maintenance mode

go-pg is in a maintenance mode and only critical issues are addressed. New development happens in Bun repo which offers similar functionality but works with PostgreSQL, MySQL, MariaDB, and SQLite.

Golang ORM

• Documentation
• Reference
• Examples
• Example projects:
  • monetr - budgeting application focused on planning for recurring expenses
  • bunrouter
  • gin
  • go-kit
  • aah framework

Tutorials

• GraphQL Tutorial on YouTube.
• Modern API design with Golang, PostgreSQL and Docker

Ecosystem

• Migrations by vmihailenco and robinjoseph08.
• Genna - cli tool for generating go-pg models.
• bigint - big.Int type for go-pg.
• 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

• Golang PostgreSQL
• Golang HTTP router
• Golang ClickHouse ORM
• Golang msgpack
• Distributed tracing tools