Golang ORM with focus on PostgreSQL features and performance

Last update: Jan 8, 2023

Comments: 17

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, and SQLite.

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.
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

", 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 } ">

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
}

Fix a bug for sending cancel request to terminate a long running query

Hi All,

Update: The issue this PR tries to solve is that we observed some long running queries did not get cancelled.

~Changes in the PR:~ ~1. Fix a bug for sending cancel request to terminate a long running query~ ~2. Update a var name RetryStatementTimeout to RetryCancelledQuery,~ ~reference:https://www.postgresql.org/docs/current/errcodes-appendix.html~ ~In the table, 57014 is query_canceled.~ ~During a local experiment, the query which is cancelled by sending cancel request also returns 57014.~
Add support for programmatic migrations

I'm using go-pg in a web framework of mine, jargo. The goal of jargo is to have the developer as little as possible about database interactions. Currently, whenever the Application launches, I call db.Model(myModel).CreateTable(IfNotExists). This does not allow for dynamic updating of the database when the model changes at next Application launch.

It would be amazing to have an UpdateTable or AlterTable method updating existing columns. It would even be enough if it would just do an ALTER TABLE x ADD COLUMN IF NOT EXISTS to be able to add new model fields.
How specify schema in queries?

I may specify db schema in TableName (example, TableName struct{} 'sql:web.user') field model, but this case not convenient. Im use same models for different schemes. Solve - when create connection make query db.Exec("SET SCHEMA web"), but... application lost connection to db and you library reconnect? If so, query SET SCHEMA not execute again. How i can specify schema for connection?
Logger or output of raw SQL generated

I've started migrating my project from beego ORM to use pg.v2. So far, it's been fantastic and handles some of few PostgreSQL issues I've had like UUID and Array support. Is there a way to output the raw generated SQL?
Issue With Relation Join With Respect to SQL Tags
So with relation to https://github.com/go-pg/pg/issues/862, took your advice and tried to replicate it with your examples by creating a working example

https://play.golang.org/p/bEA67rTlfOY

I ran the above and I got a similar error - the structs you see above are actually from my working code but modified.

Essentially the error is:

// SELECT blog_place.*, "place"."place_id", "place"."name" FROM place AS "place" JOIN discovr.blog_place AS blog_place ON (blog_place."blog_blog_id") IN (1) WHERE ("place"."place_id" = blog_place."place_id") // panic: ERROR #42703 column blog_place.blog_blog_id does not exist

I am still not understanding as to why it is appending blog_ prefix to my blog_id in my JOIN call for blog_place table..
Method for testing a connection

I am trying to write a piece of code that regularly checks the database connection to test if it is still open.

I can't find any method to do this, is there a way? If not, is there any way to be notified if the database connection is closed?

I think something like https://golang.org/pkg/database/sql/#DB.Ping would be useful

mellium.im/sasl: unrecognized import path

go get -u github.com/go-pg/pg
package mellium.im/sasl: unrecognized import path "mellium.im/sasl" (https fetch: Get https://mellium.im/sasl?go-get=1: read tcp 10.10.49.76:63173->198.199.66.189:443: wsarecv: An existing connection was forcibly closed by the remote host.)

This repository has been deleted Our apologies, but the repository "mellium/sasl" has been deleted.

It now lives at https://github.com/mellium/sasl.

Connecting to Heroku

I'm curious as to why there is not a convenience method for URL Schemes? Connecting to a remote heroku database, for example, is a bit less than trivial. Would you mind making a quick example of how to structure a valid connection to a simple Hobby-Dev Postgres database? Or consider writing up a URL Scheme handler?

I'd be happy to write up some documentation and contribute towards a nice WIKI with examples for this package in exchange :)
Feature Request: Add hooks (BeforeUpdate/AfterInsert, etc...)

Are hooks on the roadmap? I can understand if they're omitted for performance concerns, but it would be handy if I had a standard way to perform things like expiring redis cache, updating row counts, or re-indexing elasticsearch results.

I had looked at Notifications, but disregarded those because of the Not thread-safe. text.

Getting error `pg: pool.go:317: Conn has unread data` when using `pg.In()`

following the sample on the website, I have these models defined:

type BaseModel struct {
	Id        string     `json:"id" pg:"cuid,pk,notnull,unique" sql:"index"`
	CreatedAt time.Time  `json:"created_at"`
	UpdatedAt time.Time  `json:"updated_at"`
	DeletedAt *time.Time `json:"deleted_at" sql:"index"`
}

type User struct {
	BaseModel
	Firstname     *string    `json:"firstname" pg:",type:VARCHAR(64)"`
	Lastname      *string    `json:"lastname" pg:",type:VARCHAR(64)"`
	Projects      []*Project `json:"projects" pg:",many2many:project_users"`
	Username      *string    `json:"username" pg:",type:VARCHAR(64)"`
	Roles         []*Role    `json:"roles" pg:",type:VARCHAR(32)[],array"`
}

type CreateProjectInput struct {
	Name           string    `json:"name" `
	Users          []*string `json:"users" `
}

and whenever I try to run the following:

func ResolveCreateProject(ctx *gin.Context, data model.CreateProjectInput) (*model.Project, error) {

	var users []model.User

	err := database.DB.Model(&users).
		Where("cuid IN (?)", pg.In(data.Users)).
		Select()

	return nil, err
}

I get the following error trace:

pg: 2020/06/28 13:42:03 pool.go:317: Conn has unread data
reflect: call of reflect.Value.IsNil on string Value

goroutine 47 [running]:
runtime/debug.Stack(0x0, 0x0, 0x0)
        /usr/local/Cellar/go/1.13.8/libexec/src/runtime/debug/stack.go:24 +0xa1
runtime/debug.PrintStack()
        /usr/local/Cellar/go/1.13.8/libexec/src/runtime/debug/stack.go:16 +0x22
github.com/99designs/gqlgen/graphql.DefaultRecover(0x1e06900, 0xc0003aba70, 0x1bcb040, 0xc000238200, 0x0, 0x0)
        /Users/aien/go/pkg/mod/github.com/99designs/[email protected]/graphql/recovery.go:16 +0xf0
riant.jetbrains.space/p/apollo/code/apollo/apps/mission-control/controller.(*executionContext)._Mutation_createProject.func1(0xc00034c120, 0xc00034d940, 0xc00048c628)
        /Users/aien/Programming/Lo/apollo/apps/mission-control/controller/graphql.go:1299 +0x9e
panic(0x1bcb040, 0xc000238200)
        /usr/local/Cellar/go/1.13.8/libexec/src/runtime/panic.go:679 +0x1e0
reflect.Value.IsNil(0x1b861c0, 0xc00034dab0, 0x198, 0xc000509100)
        /usr/local/Cellar/go/1.13.8/libexec/src/reflect/value.go:1073 +0x198
github.com/go-pg/pg/v10/types.ptrScannerFunc.func1(0x1b861c0, 0xc00034dab0, 0x198, 0x1e0ce60, 0xc0002381c0, 0xb, 0x0, 0x0)
        /Users/aien/go/pkg/mod/github.com/go-pg/pg/[email protected]/types/scan_value.go:151 +0x349
github.com/go-pg/pg/v10/types.ArrayScanner.func1(0x1b71820, 0xc0001ce488, 0x197, 0x1e0ce60, 0xc000384438, 0x31, 0x0, 0x0)
        /Users/aien/go/pkg/mod/github.com/go-pg/pg/[email protected]/types/array_scan.go:103 +0x628
github.com/go-pg/pg/v10/orm.(*Field).ScanValue(0xc0001b4280, 0x1cab000, 0xc0001ce410, 0x199, 0x1e0ce60, 0xc000384438, 0x31, 0x0, 0x0)
        /Users/aien/go/pkg/mod/github.com/go-pg/pg/[email protected]/orm/field.go:113 +0x100
github.com/go-pg/pg/v10/orm.(*structTableModel).scanColumn(0xc0003998c0, 0x7, 0xc0005090f0, 0x5, 0x1e0ce60, 0xc000384438, 0x31, 0xc0003fa200, 0x0, 0x0)
        /Users/aien/go/pkg/mod/github.com/go-pg/pg/[email protected]/orm/model_table_struct.go:278 +0x623
github.com/go-pg/pg/v10/orm.(*structTableModel).ScanColumn(0xc0003998c0, 0x7, 0xc0005090f0, 0x5, 0x1e0ce60, 0xc000384438, 0x31, 0x0, 0x0)
        /Users/aien/go/pkg/mod/github.com/go-pg/pg/[email protected]/orm/model_table_struct.go:236 +0xa4
github.com/go-pg/pg/v10.readDataRow(0x1e06880, 0xc0000c0000, 0xc0003843c0, 0x1df8ce0, 0xc0003998c0, 0xc000101080, 0xe, 0xe, 0x0, 0x0)
        /Users/aien/go/pkg/mod/github.com/go-pg/pg/[email protected]/messages.go:810 +0x3f7
github.com/go-pg/pg/v10.readSimpleQueryData(0x1e06880, 0xc0000c0000, 0xc0003843c0, 0x1cc6bc0, 0xc0003998c0, 0x0, 0x0, 0x0)
        /Users/aien/go/pkg/mod/github.com/go-pg/pg/[email protected]/messages.go:873 +0x427
github.com/go-pg/pg/v10.(*baseDB).simpleQueryData.func1(0xc0003843c0, 0x0, 0x0)
        /Users/aien/go/pkg/mod/github.com/go-pg/pg/[email protected]/base.go:621 +0xbc
github.com/go-pg/pg/v10/internal/pool.(*Conn).WithReader.func1(0x1e06880, 0xc0000c0000, 0x1e0e520, 0x23f7168, 0x0, 0x0)
        /Users/aien/go/pkg/mod/github.com/go-pg/pg/[email protected]/internal/pool/conn.go:80 +0x158
github.com/go-pg/pg/v10/internal.WithSpan(0x1e06880, 0xc0000c0000, 0x1ce789e, 0xb, 0xc00048aa10, 0x0, 0x0)
        /Users/aien/go/pkg/mod/github.com/go-pg/pg/[email protected]/internal/util.go:84 +0x2e4
github.com/go-pg/pg/v10/internal/pool.(*Conn).WithReader(0xc0003ae190, 0x1e06880, 0xc0000c0000, 0x0, 0xc00048aab8, 0x0, 0x0)
        /Users/aien/go/pkg/mod/github.com/go-pg/pg/[email protected]/internal/pool/conn.go:73 +0xbe
github.com/go-pg/pg/v10.(*baseDB).simpleQueryData(0xc0003d2000, 0x1e06880, 0xc0000c0000, 0xc0003ae190, 0x1cc6bc0, 0xc0003998c0, 0xc0003ab9b0, 0x0, 0x0, 0x0)
        /Users/aien/go/pkg/mod/github.com/go-pg/pg/[email protected]/base.go:619 +0x1ed
github.com/go-pg/pg/v10.(*baseDB).query.func1.1(0x1e06880, 0xc0000c0000, 0xc0003ae190, 0x0, 0x0)
        /Users/aien/go/pkg/mod/github.com/go-pg/pg/[email protected]/base.go:324 +0xdf
github.com/go-pg/pg/v10.(*baseDB).withConn.func1(0x1e06880, 0xc0000c0000, 0x1e0e520, 0x23f7168, 0x0, 0x0)
        /Users/aien/go/pkg/mod/github.com/go-pg/pg/[email protected]/base.go:175 +0x29e
github.com/go-pg/pg/v10/internal.WithSpan(0x1e06880, 0xc0000c0000, 0x1ce62b9, 0x9, 0xc00048ae08, 0x0, 0x0)
        /Users/aien/go/pkg/mod/github.com/go-pg/pg/[email protected]/internal/util.go:84 +0x2e4
github.com/go-pg/pg/v10.(*baseDB).withConn(0xc0003d2000, 0x1e06880, 0xc0000c0000, 0xc00048af20, 0x0, 0x0)
        /Users/aien/go/pkg/mod/github.com/go-pg/pg/[email protected]/base.go:142 +0xad
github.com/go-pg/pg/v10.(*baseDB).query.func1(0x1e06880, 0xc0000c0000, 0x1e0e520, 0x23f7168, 0x0, 0x0)
        /Users/aien/go/pkg/mod/github.com/go-pg/pg/[email protected]/base.go:323 +0x34b
github.com/go-pg/pg/v10/internal.WithSpan(0x1e06880, 0xc0000c0000, 0x1ce2056, 0x5, 0xc00048b238, 0x0, 0x0)
        /Users/aien/go/pkg/mod/github.com/go-pg/pg/[email protected]/internal/util.go:84 +0x2e4
github.com/go-pg/pg/v10.(*baseDB).query(0xc0003d2000, 0x1e06880, 0xc0000c0000, 0x1cc6bc0, 0xc0003998c0, 0x1c5a8e0, 0xc0002380a0, 0xc00034da70, 0x1, 0x1, ...)
        /Users/aien/go/pkg/mod/github.com/go-pg/pg/[email protected]/base.go:314 +0x559
github.com/go-pg/pg/v10.(*baseDB).QueryContext(0xc0003d2000, 0x1e06880, 0xc0000c0000, 0x1cc6bc0, 0xc0003998c0, 0x1c5a8e0, 0xc0002380a0, 0xc00034da70, 0x1, 0x1, ...)
        /Users/aien/go/pkg/mod/github.com/go-pg/pg/[email protected]/base.go:293 +0xde
github.com/go-pg/pg/v10/orm.(*Query).query(0xc00000c3c0, 0x1e06880, 0xc0000c0000, 0x1e0dca0, 0xc0003998c0, 0x1c5a8e0, 0xc0002380a0, 0x0, 0x0, 0x0, ...)
        /Users/aien/go/pkg/mod/github.com/go-pg/pg/[email protected]/orm/query.go:865 +0x4c4
github.com/go-pg/pg/v10/orm.(*Query).Select(0xc00000c3c0, 0x0, 0x0, 0x0, 0x0, 0x0)
        /Users/aien/go/pkg/mod/github.com/go-pg/pg/[email protected]/orm/query.go:834 +0x218
riant.jetbrains.space/p/apollo/code/apollo/apps/mission-control/controller.ResolveCreateProject(0xc0003d8270, 0xc000456134, 0xe, 0xc0000d8e18, 0x1, 0x1, 0xc00034d9b0, 0x0, 0x0, 0x0)
        /Users/aien/Programming/Lo/apollo/apps/mission-control/controller/project.go:18 +0x33f
riant.jetbrains.space/p/apollo/code/apollo/apps/mission-control/resolver.(*mutationResolver).CreateProject(0xc0000d8e20, 0x1e06900, 0xc0003aba70, 0xc000456134, 0xe, 0xc0000d8e18, 0x1, 0x1, 0xc00034d9b0, 0x0, ...)
        /Users/aien/Programming/Lo/apollo/apps/mission-control/resolver/schema.resolvers.go:46 +0x149
riant.jetbrains.space/p/apollo/code/apollo/apps/mission-control/controller.(*executionContext)._Mutation_createProject.func2.1(0x1e06900, 0xc0003aba70, 0x0, 0x0, 0x0, 0x0)
        /Users/aien/Programming/Lo/apollo/apps/mission-control/controller/graphql.go:1321 +0x1d1
riant.jetbrains.space/p/apollo/code/apollo/apps/mission-control/router.graphqlHandler.func2(0x1e06900, 0xc0003aba70, 0x0, 0x0, 0xc00032dfe0, 0xc00034d9f0, 0x1, 0x1, 0x0, 0x0, ...)
        /Users/aien/Programming/Lo/apollo/apps/mission-control/router/router.go:93 +0x38b
riant.jetbrains.space/p/apollo/code/apollo/apps/mission-control/controller.(*executionContext)._Mutation_createProject.func2.2(0x1e06900, 0xc0003aba70, 0x0, 0x0, 0x0, 0x0)
        /Users/aien/Programming/Lo/apollo/apps/mission-control/controller/graphql.go:1331 +0x2b6
riant.jetbrains.space/p/apollo/code/apollo/apps/mission-control/controller.(*executionContext)._Mutation_createProject.func2(0x1e06900, 0xc0003aba70, 0x0, 0x0, 0x0, 0x0)
        /Users/aien/Programming/Lo/apollo/apps/mission-control/controller/graphql.go:1334 +0x194
github.com/99designs/gqlgen/graphql/handler.newExecutor.func3(0x1e06900, 0xc0003aba70, 0xc00032dfa0, 0x0, 0x0, 0x0, 0x0)
        /Users/aien/go/pkg/mod/github.com/99designs/[email protected]/graphql/handler/executor.go:36 +0x6d
github.com/99designs/gqlgen/graphql/handler.newExecutor.func6.1(0x1e06900, 0xc0003aba70, 0x0, 0x0, 0x0, 0x0)
        /Users/aien/go/pkg/mod/github.com/99designs/[email protected]/graphql/handler/executor.go:64 +0x94
riant.jetbrains.space/p/apollo/code/apollo/apps/mission-control/router.Introspection.InterceptField(0xc0002f4340, 0x0, 0x1e06900, 0xc0003aba70, 0xc00032dfc0, 0x0, 0x0, 0x0, 0x0)
        /Users/aien/Programming/Lo/apollo/apps/mission-control/router/extension.go:36 +0x84
github.com/99designs/gqlgen/graphql/handler.newExecutor.func6(0x1e06900, 0xc0003aba70, 0xc00032dfa0, 0x0, 0x0, 0x0, 0x0)
        /Users/aien/go/pkg/mod/github.com/99designs/[email protected]/graphql/handler/executor.go:63 +0x11c
riant.jetbrains.space/p/apollo/code/apollo/apps/mission-control/controller.(*executionContext)._Mutation_createProject(0xc00034c120, 0x1e06900, 0xc0003aa990, 0xc0002f6780, 0xc00032c300, 0x2, 0x2, 0x0, 0x0)
        /Users/aien/Programming/Lo/apollo/apps/mission-control/controller/graphql.go:1318 +0x545
riant.jetbrains.space/p/apollo/code/apollo/apps/mission-control/controller.(*executionContext)._Mutation(0xc00034c120, 0x1e06900, 0xc0003aa990, 0xc000507dc0, 0x2, 0x2, 0x0, 0x0)
        /Users/aien/Programming/Lo/apollo/apps/mission-control/controller/graphql.go:4531 +0xf83
riant.jetbrains.space/p/apollo/code/apollo/apps/mission-control/controller.(*executableSchema).Exec.func2.1(0x1e06900, 0xc0003aa960, 0x0, 0x0, 0x0, 0x0)
        /Users/aien/Programming/Lo/apollo/apps/mission-control/controller/graphql.go:499 +0xd9
riant.jetbrains.space/p/apollo/code/apollo/apps/mission-control/controller.(*executionContext)._mutationMiddleware(0xc00034c120, 0x1e06900, 0xc0003aa960, 0xc00015c8c0, 0xc00032c2e0, 0x0, 0x0)
        /Users/aien/Programming/Lo/apollo/apps/mission-control/controller/graphql.go:881 +0x3ea
riant.jetbrains.space/p/apollo/code/apollo/apps/mission-control/controller.(*executableSchema).Exec.func2(0x1e06900, 0xc0003aa960, 0x0)
        /Users/aien/Programming/Lo/apollo/apps/mission-control/controller/graphql.go:498 +0x12a
github.com/99designs/gqlgen/graphql/handler.executor.DispatchOperation.func1.1.1(0x1e06900, 0xc0003aa960, 0x0)
        /Users/aien/go/pkg/mod/github.com/99designs/[email protected]/graphql/handler/executor.go:100 +0x67
github.com/99designs/gqlgen/graphql/handler.newExecutor.func2(0x1e06900, 0xc0003aa960, 0xc00034c130, 0x0)
        /Users/aien/go/pkg/mod/github.com/99designs/[email protected]/graphql/handler/executor.go:33 +0x43
github.com/99designs/gqlgen/graphql/handler.executor.DispatchOperation.func1.1(0x1e06900, 0xc0003aa960, 0x0)
        /Users/aien/go/pkg/mod/github.com/99designs/[email protected]/graphql/handler/executor.go:99 +0x118
github.com/99designs/gqlgen/graphql/handler/transport.POST.Do(0x33661b0, 0xc0003d8270, 0xc0002f8300, 0x1e04d80, 0xc0003d3ae0)
        /Users/aien/go/pkg/mod/github.com/99designs/[email protected]/graphql/handler/transport/http_post.go:52 +0x758
github.com/99designs/gqlgen/graphql/handler.(*Server).ServeHTTP(0xc00045e000, 0x33661b0, 0xc0003d8270, 0xc0002f8300)
        /Users/aien/go/pkg/mod/github.com/99designs/[email protected]/graphql/handler/server.go:140 +0x201
riant.jetbrains.space/p/apollo/code/apollo/apps/mission-control/router.graphqlHandler.func4(0xc0003d8270)
        /Users/aien/Programming/Lo/apollo/apps/mission-control/router/router.go:148 +0xa8
github.com/gin-gonic/gin.(*Context).Next(0xc0003d8270)
        /Users/aien/go/pkg/mod/github.com/gin-gonic/[email protected]/context.go:147 +0x8c
riant.jetbrains.space/p/apollo/code/apollo/apps/mission-control/middleware.GinContextToContextMiddleware.func1(0xc0003d8270)
        /Users/aien/Programming/Lo/apollo/apps/mission-control/middleware/gin.go:12 +0x130
github.com/gin-gonic/gin.(*Context).Next(0xc0003d8270)
        /Users/aien/go/pkg/mod/github.com/gin-gonic/[email protected]/context.go:147 +0x8c
riant.jetbrains.space/p/apollo/code/apollo/apps/mission-control/middleware.JsonHeaderMiddleWare(0xc0003d8270)
        /Users/aien/Programming/Lo/apollo/apps/mission-control/middleware/header.go:9 +0x63
github.com/gin-gonic/gin.(*Context).Next(0xc0003d8270)
        /Users/aien/go/pkg/mod/github.com/gin-gonic/[email protected]/context.go:147 +0x8c
github.com/gin-gonic/gin.RecoveryWithWriter.func1(0xc0003d8270)
        /Users/aien/go/pkg/mod/github.com/gin-gonic/[email protected]/recovery.go:83 +0x74
github.com/gin-gonic/gin.(*Context).Next(0xc0003d8270)
        /Users/aien/go/pkg/mod/github.com/gin-gonic/[email protected]/context.go:147 +0x8c
github.com/toorop/gin-logrus.Logger.func1(0xc0003d8270)
        /Users/aien/go/pkg/mod/github.com/toorop/[email protected]/logger.go:31 +0xcc
github.com/gin-gonic/gin.(*Context).Next(0xc0003d8270)
        /Users/aien/go/pkg/mod/github.com/gin-gonic/[email protected]/context.go:147 +0x8c
github.com/gin-contrib/sessions.Sessions.func1(0xc0003d8270)
        /Users/aien/go/pkg/mod/github.com/gin-contrib/[email protected]/sessions.go:52 +0x233
github.com/gin-gonic/gin.(*Context).Next(0xc0003d8270)
        /Users/aien/go/pkg/mod/github.com/gin-gonic/[email protected]/context.go:147 +0x8c
github.com/gin-gonic/gin.(*Engine).handleHTTPRequest(0xc00044e000, 0xc0003d8270)
        /Users/aien/go/pkg/mod/github.com/gin-gonic/[email protected]/gin.go:403 +0x434
github.com/gin-gonic/gin.(*Engine).ServeHTTP(0xc00044e000, 0x1e049c0, 0xc0003cc2a0, 0xc000462000)
        /Users/aien/go/pkg/mod/github.com/gin-gonic/[email protected]/gin.go:364 +0xd6
net/http.serverHandler.ServeHTTP(0xc0003cc1c0, 0x1e049c0, 0xc0003cc2a0, 0xc000462000)
        /usr/local/Cellar/go/1.13.8/libexec/src/net/http/server.go:2802 +0x20f
net/http.(*conn).serve(0xc0003d0140, 0x1e06840, 0xc0002ec800)
        /usr/local/Cellar/go/1.13.8/libexec/src/net/http/server.go:1890 +0x1716
created by net/http.(*Server).Serve
        /usr/local/Cellar/go/1.13.8/libexec/src/net/http/server.go:2928 +0x931

If I don't use the pg.In() then no results will return, although I'm pretty sure the user ids exist.

I will provide more details if you need.

thanks

Multi-column foreign key?

@vmihailenco what are your thoughts on a multi column foreign key?

I'm using go-pg for a multi tenant application so everything is separated by an account_id column.

I'd like to create certain tables that ensure the same account_id for the items. For example:

type Account struct {
	tableName string `sql:"accounts"`

	AccountID uint64 `json:"accountId" sql:"account_id,pk,type:bigint"`
	Name      string `json:"name" sql:"name"`
}

type Product struct {
	tableName string `sql:"products"`

	ProductID uint64   `json:"productId" sql:"product_id,pk,type:bigint"`
	AccountID uint64   `json:"accountId" sql:"account_id,notnull,on_delete:CASCADE"`
	Account   *Account `json:"account"`
	Title     string   `json:"title" sql:"title"`
}

type Variation struct {
	tableName string `sql:"variations"`

	VariationID uint64   `json:"variationId" sql:"variation_id,pk,type:bigint"`
	AccountID   uint64   `json:"accountId" sql:"account_id,notnull,on_delete:CASCADE"`
	Account     *Account `json:"-" `
	ProductID   uint64   `json:"productId" sql:"product_id,notnull,on_delete:CASCADE"`
	Product     *Product `json:"-"`
	SKU         string   `json:"sku" sql:"sku"`
}

These models will generate the following queries:

CREATE TABLE IF NOT EXISTS accounts
(
    "account_id" bigserial,
    "name"       text,
    PRIMARY KEY ("account_id")
);

CREATE TABLE IF NOT EXISTS products
(
    "product_id" bigserial,
    "account_id" bigint NOT NULL,
    "title"      text,
    PRIMARY KEY ("product_id"),
    FOREIGN KEY ("account_id") REFERENCES accounts ("account_id") ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS variations
(
    "variation_id" bigserial,
    "account_id"   bigint NOT NULL,
    "product_id"   bigint NOT NULL,
    "sku"          text,
    PRIMARY KEY ("variation_id"),
    FOREIGN KEY ("account_id") REFERENCES accounts ("account_id") ON DELETE CASCADE,
    FOREIGN KEY ("product_id") REFERENCES products ("product_id") ON DELETE CASCADE
);

I'd like to make it so that when a variation is inserted it guarantees that the product is for the same account. Would it make sense to add support for a foreign tag? Something like this?

type Variation struct {
	tableName string `sql:"variations"`

	VariationID uint64   `json:"variationId" sql:"variation_id,pk,type:bigint"`
	AccountID   uint64   `json:"accountId" sql:"account_id,notnull,on_delete:CASCADE,foreign:account_product_fk"`
	Account     *Account `json:"-" `
	ProductID   uint64   `json:"productId" sql:"product_id,notnull,on_delete:CASCADE,foreign:account_product_fk"`
	Product     *Product `json:"-"`
	SKU         string   `json:"sku" sql:"sku"`
}

And then the create query would be something like:

CREATE TABLE IF NOT EXISTS variations
(
    "variation_id" bigserial,
    "account_id"   bigint NOT NULL,
    "product_id"   bigint NOT NULL,
    "sku"          text,
    PRIMARY KEY ("variation_id"),
    FOREIGN KEY ("account_id") REFERENCES accounts ("account_id") ON DELETE CASCADE,
    FOREIGN KEY ("product_id", "account_id") REFERENCES products ("product_id", "account_id") ON DELETE CASCADE
);

This would be ideal since it will validate that any record will always need to relate to another record within the same account. This also wouldn't need to affect relations for querying at all, this would only enforce the constraint for writes.

What are your thoughts?

Only one of the Columns that have the same name parsed in map[string]interface{}

When querying an SQL query and parsing the result data into a map[string]interface{}, the columns that have the same name are missing in the query result, only one of them is available.

Steps to Reproduce

Example code:

	db := pg.Connect(&pg.Options{
		User:     "postgres",
		Database: "postgres",
	})

	var data []map[string]interface{}
	_, err := db.Query(&data, "SELECT * FROM a JOIN b ON a.id = b.id")
	if err != nil {
		panic(err)
	}

	fmt.Printf("%+v\n", data)

The database schema and example data

postgres=# \d a
                 Table "public.a"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           |          |
 name   | text    |           |          |

postgres=# \d b
                 Table "public.b"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           |          |
 name   | text    |           |          |
 code   | integer |           |          |

postgres=# select * from a;
 id | name
----+------
  1 | ax
  2 | ay
(2 rows)

postgres=# select * from b;
 id | name | code
----+------+------
  1 | bx   |   99
  2 | by   |  100
(2 rows)

Query result

postgres=# SELECT * FROM a JOIN b ON a.id = b.id;
 id | name | id | name | code
----+------+----+------+------
  1 | ax   |  1 | bx   |   99
  2 | ay   |  2 | by   |  100
(2 rows)

Expected Behavior

The map should contain all the columns which have the same name

[map[code:99 id:1 name:bx id:1 name:ax] map[code:100 id:2 name:by id:2 name:ay]]

Current Behavior

I think due to the map[column_name], only one of columns is available in the query result

[map[code:99 id:1 name:bx] map[code:100 id:2 name:by]]

Possible Solution

Not really efficient, but I think we should add a postfix to the column name to distinguish between the columns. For example

SQL result

id | name | id1 | name1 | code
----+------+----+------+------
 1 | ax   |  1 | bx   |   99
 2 | ay   |  2 | by   |  100

parsed map

[map[code:99 id:1 name:bx id1:1 name1:ax] map[code:100 id:2 name:by id1:2 name1:ay]]

Context (Environment)

Just want to run a raw SQL query and expect no missing data

Detailed Description

Possible Implementation

conn.OnQueryProcessed undefined (type *pg.DB has no field or method OnQueryProcessed)
Issue tracker is used for reporting bugs and discussing new features. Please use Discord or stackoverflow for supporting issues.

Expected Behavior

Current Behavior

Possible Solution

Steps to Reproduce

Context (Environment)

Detailed Description

Possible Implementation
fix buff size comparison & proper error message

When scanning a byte column ReadBytes expect to have exact size of a []byte We should be able to read bytes into a buffer that is bigger than our data size in this column. This cause errors when you have a fixed field size in code but variable size in database. The error message was also misleading like it was checking if we have enough space but it actually expect to have exact size. So, changed the comparison to accept bigger buffers and to properly print the source & destination buffer size.

nil-pointer dereference in getConn

Have no idea how to handle or investigate this panic.

runtime.errorString: runtime error: invalid memory address or nil pointer dereference
  File "/builds/tt/snami/snami/v2/interfaces/rest/middleware/sentry.go", line 158, in recoverWithSentry
  File "/builds/tt/snami/snami/.cache/go/pkg/mod/github.com/go-pg/pg/[email protected]/base.go", line 80, in (*baseDB).getConn
  File "/builds/tt/snami/snami/.cache/go/pkg/mod/github.com/go-pg/pg/[email protected]/base.go", line 136, in (*baseDB).withConn
  File "/builds/tt/snami/snami/.cache/go/pkg/mod/github.com/go-pg/pg/[email protected]/base.go", line 245, in (*baseDB).exec
  File "/builds/tt/snami/snami/.cache/go/pkg/mod/github.com/go-pg/pg/[email protected]/base.go", line 220, in (*baseDB).ExecContext
  File "/builds/tt/snami/snami/.cache/go/pkg/mod/github.com/go-pg/pg/[email protected]/base.go", line 119, in (*baseDB).initConn
  File "/builds/tt/snami/snami/.cache/go/pkg/mod/github.com/go-pg/pg/[email protected]/base.go", line 84, in (*baseDB).getConn
  File "/builds/tt/snami/snami/.cache/go/pkg/mod/github.com/go-pg/pg/[email protected]/base.go", line 136, in (*baseDB).withConn
  File "/builds/tt/snami/snami/.cache/go/pkg/mod/github.com/go-pg/pg/[email protected]/base.go", line 315, in (*baseDB).query
  File "/builds/tt/snami/snami/.cache/go/pkg/mod/github.com/go-pg/pg/[email protected]/base.go", line 290, in (*baseDB).QueryContext

CountEstimate is not safe to be called from transaction context
Expected Behavior

CountEstimate(threshold int) should be safely called from any context, including transaction.

Current Behavior

If a transaction is run containing a CountEstimate() instruction without preliminary run of other query using this function, the transaction will fail.

It is due to the fact that CountEstimate() use an error-based lazy loading in count_estimate.go

if err != nil { if pgerr, ok := err.(internal.PGError); ok && pgerr.Field('C') == "42883" { // undefined_function err = q.createCountEstimateFunc() // ... }

This lazy-loading cause a rollback of the transaction, which fails.

Possible Solution

Several possible solutions:

Change the "lazy init" of this function by checking it's existence whenever we use the function CountEstimate()... But can lead to performance issue...

Handle the specific case of transaction whenever we fails to create this function

Don't create the pgCountEstimateFunc from the query db whenever it's a transaction, but from the baseDB of the transaction...

Make it a feature, add some docs to the function with the workaround ^^

Steps to Reproduce

(Error handling is omitted)

func main(){ dbh, _ := getDBHandler() // return a db handler (*pg.DB) on a "clean" db (without _go_pg_count_estimate_v2 function) tx, _ := db.Begin() _, err := tx.Model(&SomeModel{}).CountEstimate(10) if err != nil { return panic(fmt.Sprintf("countEstimate failure. %s", err)) } }

This code will panic with message: panic: countEstimate failure. ERROR #25P02 current transaction is aborted, commands ignored until end of transaction block

The code below will succeed, since the first query will create the _go_pg_count_estimate_v2, and avoid the error in the transaction

func main(){ dbh, _ := getDBHandler() // return a db handler (*pg.DB) on a "clean" db (without _go_pg_count_estimate_v2 function) // This will create the _go_pg_count_estimate_v2 function, which will make it available in above transaction db.Model(&SomeModel{}).CountEstimate(10) tx, _ := db.Begin() _, err := tx.Model(&SomeModel{}).CountEstimate(10) if err != nil { return panic(fmt.Sprintf("countEstimate failure. %s", err)) } }

Context (Environment)

Posgresql version : 14.1

go-pg version : v10.10.6

"BeforeUpdate" hook not working for individual column updates

type User struct {
	ID        int64
	CreatedAt time.Time `sql:"default:now()"`
	UpdatedAt time.Time `sql:"default:now()"`
	Name      string
	Email    string
}

func (m *User) BeforeUpdate(ctx context.Context) (context.Context, error) {
	m.UpdatedAt = timeNow().Round(time.Microsecond)
	return ctx, nil
}

The following two queries don't update the updated_at column

user := &User{ID:1, Email:"[email protected]"} 
_, err := db.ModelContext(ctx, user).Column("email").Where("id = ?", user.ID).Update()
_, err := db.ModelContext(ctx, user).Set("email = ?",users.Email ).Where("id = ?", user.ID).Update()

The below queries work fine and update the updated_at column

user := &User{ID:1, Email:"[email protected]"} 
_, err := db.ModelContext(ctx, user)..WherePK().UpdateNotZero()
_, err := db.ModelContext(ctx, user).Where("id = ?", id).Update()

Is this intended behavior where BeforeUpdate hook is only triggered and used when the values are updated using struct? Is it possible to have a update tag which updates the updated_at column if it exists. Similar to how soft delete works, where we update deleted_at is set to current time if the column exists and we mention the tag in struct.

Golang ORM with focus on PostgreSQL features and performance

PostgreSQL client and ORM for Golang

Maintenance mode

Ecosystem

Features

Installation

Quickstart

See also

Owner

Comments

Fix a bug for sending cancel request to terminate a long running query

Add support for programmatic migrations

How specify schema in queries?

Logger or output of raw SQL generated

Issue With Relation Join With Respect to SQL Tags

Method for testing a connection

mellium.im/sasl: unrecognized import path

Connecting to Heroku

Feature Request: Add hooks (BeforeUpdate/AfterInsert, etc...)

Getting error `pg: pool.go:317: Conn has unread data` when using `pg.In()`

Multi-column foreign key?

Only one of the Columns that have the same name parsed in map[string]interface{}

Steps to Reproduce

Expected Behavior

Current Behavior

Possible Solution

Context (Environment)

Detailed Description

Possible Implementation

conn.OnQueryProcessed undefined (type *pg.DB has no field or method OnQueryProcessed)

Expected Behavior

Current Behavior

Possible Solution

Steps to Reproduce

Context (Environment)

Detailed Description

Possible Implementation

fix buff size comparison & proper error message

nil-pointer dereference in getConn

CountEstimate is not safe to be called from transaction context

Expected Behavior

Current Behavior

Possible Solution

Steps to Reproduce

Context (Environment)

"BeforeUpdate" hook not working for individual column updates

Related tags

Go-mysql-orm - Golang mysql orm，dedicated to easy use of mysql

100% type-safe ORM for Go (Golang) with code generation and MySQL, PostgreSQL, Sqlite3, SQL Server support. GORM under the hood.

An orm library support nGQL for Golang

golang orm

The fantastic ORM library for Golang, aims to be developer friendly

SQL mapper ORM framework for Golang

Golang mysql orm, a personal learning project, dedicated to easy use of mysql

Mybatis for golang - SQL mapper ORM framework

Simple and Powerful ORM for Go, support mysql,postgres,tidb,sqlite3,mssql,oracle, Moved to https://gitea.com/xorm/xorm

A better ORM for Go, based on non-empty interfaces and code generation.

A better ORM for Go, based on non-empty interfaces and code generation.

Simple and performant ORM for sql.DB

Examples of using various popular database libraries and ORM in Go.

Examples of using various popular database libraries and ORM in Go.

beedb is a go ORM,support database/sql interface，pq/mysql/sqlite

A simple wrapper around sql.DB to help with structs. Not quite an ORM.

ORM-ish library for Go

Simple Go ORM for Google/Firebase Cloud Firestore

Database agnostic ORM for Go