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.InsertQueryopen in new window, bun.UpdateQueryopen in new window, 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:

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

Bun API:

1. err := db.NewSelect().Model(&users).Scan(ctx) 2. err := db.NewSelect().Model(&users).Scan(ctx, &var1, &var2) 3. res, err := db.NewInsert().Model(&users).Exec(ctx) 4. res, err := db.NewUpdate().Model(&users).WherePK().Exec(ctx) 5. 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:

1. db.NewUpdate(). 2. With("_data", db.NewValues(&rows)). 3. Model((*Model)(nil)). 4. Table("_data"). 5. Set("model.name = _data.name"). 6. Where("model.id = _data.id"). 7. Exec(ctx)

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

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

1. type User struct {
2. Name string `bun:",nullzero"`
3. }

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

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

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 those 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:

1. res, err := db.NewInsert().Model(&model).On("CONFLICT DO NOTHING").Exec(ctx)
2. 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.
• WrapWith is removed. Use With instead:

1. subq := db.NewSelect()
2. q := db.NewSelect().
3. With("subq", subq).
4. Table("subq")

Ignored columns

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

1. type Model struct {
2. Foo string `bun:"-"`
3. }

But you can fix it by adding scanonly option:

1. type Model struct {
2. Foo string `bun:",scanonly"`
3. }

pg.Listener

You have 2 options if you need pg.Listener:

• Use pgdriver.Listener.
• 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):

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

And probably mark existing migrations as completed:

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

You can check the status of migrations with:

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