PostgreSQL client and ORM for Golang

Hello fellow developers 👋

We (@vmihailenco and @anmic) are launching Uptrace at ProductHunt and need your support.

Uptrace is an open source distributed tracing tool. We help developers pinpoint failures and find performance bottlenecks. Uptrace can process billions of spans on a single server and allows to monitor your software at 10x lower cost.

Discuss on ProductHunt

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
GraphQL Tutorial on YouTube.

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{"[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] [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
}