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", 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", 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{"[email protected]", "[email protected]"},
}
_, err = db.Model(user1).Insert()
if err != nil {
    panic(err)
}

_, err = db.Model(&User{
    Name:   "root",
    Emails: []string{"[email protected]", "[email protected]"},
}).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 [[email protected] [email protected]]>
// [User<1 admin [[email protected] [email protected]]> User<2 root [[email protected] roo[email protected]]>]
// Story<1 Cool story User<1 admin [[email protected] [email protected]]>>
}
// 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