PostgreSQL client and ORM for Golang

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 not nullable by default and zero values (empty string, 0, zero time, empty map or slice, nil ptr) are marshalled as SQL NULL. sql:",nullable" is used to allow SQL NULL and pg:",zeroable" 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 retries 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.
Pagination and URL filters helpers.
ForEach that calls a function for each row returned by the query without loading all rows into the memory.
Works with PgBouncer in transaction pooling mode.
Migrations.
Sharding.
Model generator from SQL tables.

Get Started

go-pg requires Go version with Modules support and uses import versioning:

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

Look & Feel

package pg_test

import (
    "fmt"

    "github.com/go-pg/pg/v9"
    "github.com/go-pg/pg/v9/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
}

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

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

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

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

func createSchema(db *pg.DB) error {
    for _, model := range []interface{}{(*User)(nil), (*Story)(nil)} {
        err := db.CreateTable(model, &orm.CreateTableOptions{
            Temp: true,
        })
        if err != nil {
            return err
        }
    }
    return nil
}

jawr/pg

PostgreSQL client and ORM for Golang

Features:

Get Started

Look & Feel

See also