migrating go-pg to bun. How to have general query in bun

Question

Orm library bun creates for select a bun.SelectQuery struct which is just specific to select. For update it creates a similar one. Is it possible to create a general query struct pg.Query and define the operation (select, update) later like in go-pg. It seems to me there's no general struct but perhaps there's a workaround to not re-write general query twice.

Simple select written in bun: (https://bun.uptrace.dev/guide/query-select.html#api)

book := new(Book)
err := db.NewSelect().Model(book).Where("id = ?", 123).Scan(ctx)

How to re-write this working example from go-pg to bun without writing general sql logic twice:

package main

import (
    "fmt"

    "github.com/go-pg/pg/v10"
)

type User struct {
    ID   int64
    Name string
}

func herr(e error) { // handle error func
    if e != nil {
        panic(e)
    }
}

func main() {
    db := pg.Connect(&pg.Options{
        User:     "postgres",
        Database: "play",
    })
    var err error
    var name string
    query := db.Model(&User{}).
        Where("TRUE = TRUE") // some general sql logic

    caseLogic := false // comes from some business logic

    if caseLogic { // two different sql logics using same base query
        err = query.Column("name").Where("id = 1").Select(&name)
        herr(err)
        fmt.Println("name 1:", name)
    } else {
        _, err = query.Set("name = 'newname'").Where("id = 2").Returning("name").Update(&name)
        herr(err)
        fmt.Println("name 2:", name)
    }
}

P.S Apparently I didn't find the bun tag for the library on Stackoverflow.

Answer 1

Try:

package main

import (
    "context"
    "fmt"

    "github.com/uptrace/bun"
    "github.com/uptrace/bun/extra/bundebug"
    "github.com/uptrace/bun/extra/bunpostgres"
)

type User struct {
    ID   int64
    Name string
}

type QueryBuilder struct {
    query *bun.SelectQuery
}

func NewQueryBuilder() *QueryBuilder {
    return &QueryBuilder{
        query: bun.NewSelectQuery(),
    }
}

func (qb *QueryBuilder) WithBaseCondition() *QueryBuilder {
    qb.query.Where("TRUE = TRUE")
    return qb
}

func (qb *QueryBuilder) SelectName() *QueryBuilder {
    qb.query.ColumnExpr("name")
    return qb
}

func (qb *QueryBuilder) UpdateName(newName string) *QueryBuilder {
    qb.query.Set("name = ?", newName)
    return qb
}

func (qb *QueryBuilder) WhereID(id int64) *QueryBuilder {
    qb.query.Where("id = ?", id)
    return qb
}

func main() {
    ctx := context.Background()
    db := bun.NewDB(bunpostgres.NewDriver(bunpostgres.WithDSN("postgres://postgres@localhost:5432/play")), bun.DefaultLogger)

    db.AddQueryHook(bundebug.NewQueryHook(bundebug.WithVerbose()))

    var name string
    query := NewQueryBuilder().WithBaseCondition()

    caseLogic := false // comes from some business logic

    if caseLogic { // two different sql logics using same base query
        err := query.SelectName().WhereID(1).Scan(ctx, &name)
        herr(err)
        fmt.Println("name 1:", name)
    } else {
        _, err := query.UpdateName("newname").WhereID(2).Returning("name").Update(ctx, &name)
        herr(err)
        fmt.Println("name 2:", name)
    }
}

func herr(err error) { // handle error func
    if err != nil {
        panic(err)
    }
}

Answer 2

Using QueryBuilder allows general queries.

Working example with bun orm:

package main

import (
    "context"
    "database/sql"
    "fmt"

    "github.com/uptrace/bun"
    "github.com/uptrace/bun/dialect/pgdialect"
    "github.com/uptrace/bun/driver/pgdriver"
    "github.com/uptrace/bun/extra/bundebug"
)

type User struct {
    ID   int64
    Name string
}

func main() {
    db := setup()

    ctx := context.Background()
    var err error
    var name string

    commonQuery := func(q bun.QueryBuilder) bun.QueryBuilder {
        return q.Where("TRUE = TRUE") // some general sql logic
    }

    caseLogic := true // comes from some business logic

    if caseLogic { // two different sql logics using same base query
        err = db.NewSelect().Model(&User{}).Column("name").ApplyQueryBuilder(commonQuery).Where("id = 1").Scan(ctx, &name)
        herr(err)
        fmt.Println("name 1:", name)
    } else {
        _, err = db.NewUpdate().Model(&User{}).Set("name = 'newname'").ApplyQueryBuilder(commonQuery).Where("id = 2").Returning("name").Exec(ctx, &name)
        herr(err)
        fmt.Println("name 2:", name)
    }
}

func setup() *bun.DB {
    const dsn = "postgres://postgres:@localhost:5432/play?sslmode=disable"
    sqldb := sql.OpenDB(pgdriver.NewConnector(pgdriver.WithDSN(dsn)))

    db := bun.NewDB(sqldb, pgdialect.New())
    db.AddQueryHook(bundebug.NewQueryHook(bundebug.WithVerbose(true)))
    return db
}

func herr(e error) { // handle error func
    if e != nil {
        panic(e)
    }
}