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

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
}