Migrating from go-pg

Bun is a rewrite of go-pgopen in new window that works with PostgreSQL, MySQL, and SQLite. It consists of:

• Bun core that provides a query builder and models.
• pgdriver package to connect to PostgreSQL.
• migrate package to run migrations.
• dbfixture to load initial data from YAML files.
• Optional starter kit that provides modern app skeleton.

Bun's query builder tries to be compatible with go-pg's builder, but some rarely used APIs are removed (for example, WhereOrNotGroup). In most cases, you won't need to rewrite your queries.

go-pg is still maintained and there is no urgency in rewriting go-pg apps in Bun, but new projects should prefer Bun over go-pg. And once you are familiar with the updated API, you should be able to migrate a 80-100k lines go-pg app to Bun within a single day.

New features

• *pg.Query is split into smaller structs, for example, bun.SelectQueryopen in new window, bun.InsertQuery, bun.UpdateQuery, bun.DeleteQueryopen in new window and so on. This is one of the reasons Bun inserts/updates data faster than go-pg.

  go-pg API:

  err := db.ModelContext(ctx, &users).Select()
  err := db.ModelContext(ctx, &users).Select(&var1, &var2)
  res, err := db.ModelContext(ctx, &users).Insert()
  res, err := db.ModelContext(ctx, &user).WherePK().Update()
  res, err := db.ModelContext(ctx, &users).WherePK().Delete()
  

  Bun API:

  err := db.NewSelect().Model(&users).Scan(ctx)
  err := db.NewSelect().Model(&users).Scan(ctx, &var1, &var2)
  res, err := db.NewInsert().Model(&users).Exec(ctx)
  res, err := db.NewUpdate().Model(&users).WherePK().Exec(ctx)
  res, err := db.NewDelete().Model(&users).WherePK().Exec(ctx)
  

• To create VALUES (1, 'one') statement, use db.NewValues(&rows).

• Bulk UPDATE queries should be rewrited using CTE and VALUES statement:

  db.NewUpdate().
      With("_data", db.NewValues(&rows)).
      Model((*Model)(nil)).
      Table("_data").
      Set("model.name = _data.name").
      Where("model.id = _data.id").
      Exec(ctx)
  

  Alternatively, you can use UpdateQuery.Bulk helper that does the same:

  err := db.NewUpdate().Model(&rows).Bulk().Exec(ctx)
  

• To create an index, use db.NewCreateIndex().

• To drop an index, use db.NewDropIndex().

• To truncate a table, use db.NewTruncateTable().

• To overwrite model table name, use q.Model((*MyModel)(nil)).ModelTableExpr("my_table_name").

• To provide initial data, use fixtures.

Go zero values and NULL

Unlike go-pg, Bun does not marshal Go zero values as SQL NULLs by default. To get the old behavior, use nullzero tag option:

type User struct {
    Name string `bun:",nullzero"`
}

For time.Time fields you can use bun.NullTime:

type User struct {
    Name      string    `bun:",nullzero"`
    CreatedAt time.Time `bun:",notnull,default:current_timestamp"`
    UpdatedAt bun.NullTime
}

Other changes

• Replace pg struct tags with bun, for example, bun:"my_column_name".
• Replace rel:"has-one" with rel:"belongs-to" and rel:"belongs-to" with rel:"has-one". go-pg used wrong names for these relations.
• Replace tableName struct{} `pg:"mytable`" with bun.BaseModel `bun:"mytable"`. This helps with linters that mark the field as unused.
• To marshal Go zero values as NULLs, use bun:",nullzero" field tag. By default, Bun does not marshal Go zero values as NULL any more.
• Replace pg.ErrNoRows with sql.ErrNoRows.
• Replace db.WithParam with db.WithNamedArg.
• Replace orm.RegisterTable with db.RegisterModel.
• Replace pg.Safe with bun.Safe.
• Replace pg.Ident with bun.Ident.
• Replace pg.Array with pgdialect.Array.
• Replace pg:",discard_unknown_columns" with db.WithDiscardUnknownColumns option.
• Replace q.OnConflict("DO NOTHING") with q.On("CONFLICT DO NOTHING").
• Replace q.OnConflict("(column) DO UPDATE") with q.On("CONFLICT (column) DO UPDATE").
• Replace ForEach with sql.Rows and db.ScanRow.
• Replace WhereIn("foo IN (?)", slice) with Where("foo IN (?)", bun.In(slice)).
• Replace db.RunInTransaction with db.RunInTx.
• Replace db.SelectOrInsert with an upsert:

res, err := db.NewInsert().Model(&model).On("CONFLICT DO NOTHING").Exec(ctx)
res, err := db.NewInsert().Model(&model).On("CONFLICT DO UPDATE").Exec(ctx)

• Bun uses a database/sql pool, so use sql.DBStatsopen in new window instead of pg.PoolStats.

Ignored columns

Unlike go-pg, Bun does not allow scanning into explicitly ignored fields. For example, the following code does not work:

type Model struct {
    Foo string `bun:"-"`
}

But you can fix it by adding scanonly option:

type Model struct {
    Foo string `bun:",scanonly"`
}

pg.Listener

You have 2 options if you need pg.Listener:

• Use pgdriver.Listeneropen in new window.
• Use pgxopen in new window.

Porting migrations

Bun supports migrations via bun/migrate package. Because it uses timestamp-based migration names, you need to rename your migration files, for example, 1_initial.up.sql should be renamed to 20210505110026_initial.up.sql.

After you are done porting migrations, you need to initialize Bun tables (use starter kit):

go run cmd/bun/main.go -env=dev db init

And probably mark existing migrations as completed:

go run cmd/bun/main.go -env=dev db mark_completed

You can check the status of migrations with:

go run cmd/bun/main.go -env=dev db status