Getting started with database/sql and Bun

database/sql

Bun works on top of database/sqlopen in new window so the first thing you need to do is to create a sql.DB. In this tutorial we will be using SQLite but Bun also works with PostgreSQL and MySQL.

import (
    "database/sql"

    "github.com/uptrace/bun/driver/sqliteshim"
)

sqldb, err := sql.Open(sqliteshim.ShimName, "file::memory:?cache=shared")
if err != nil {
	panic(err)
}

Having a sql.DB, you can create a bun.DB using the corresponding SQLite dialect that comes with Bun:

import (
	"github.com/uptrace/bun"
	"github.com/uptrace/bun/dialect/sqlitedialect"
)

db := bun.NewDB(sqldb, sqlitedialect.New())

You can also install a query hook to see executed queries in console:

import "github.com/uptrace/bun/extra/bundebug"

db.AddQueryHook(bundebug.NewQueryHook(
	bundebug.WithVerbose(true),
	bundebug.FromEnv("BUNDEBUG"),
))

Now you are ready to execute queries using database/sql API:

res, err := db.ExecContext(ctx, "SELECT 1")

var num int
err := db.QueryRowContext(ctx, "SELECT 1").Scan(&num)

Or using Bun's query builder:

res, err := db.NewSelect().ColumnExpr("1").Exec(ctx)

var num int
err := db.NewSelect().ColumnExpr("1").Scan(ctx, &num)

Models

Bun uses struct-based models to build queries and scan results. A typical Bun model looks like this:

type User struct {
    bun.BaseModel `bun:"table:users,alias:u"`

	ID	 int64  `bun:",pk,autoincrement"`
	Name string
}

Having a model, you can create and drop tables:

// Create users table.
res, err := db.NewCreateTable().Model((*User)(nil)).Exec(ctx)

// Drop users table.
res, err := db.NewDropTable().Model((*User)(nil)).Exec(ctx)

Insert rows:

// Insert a single user.
user := &User{Name: "admin"}
res, err := db.NewInsert().Model(user).Exec(ctx)

// Insert multiple users (bulk-insert).
users := []User{user1, user2}
res, err := db.NewInsert().Model(&users).Exec(ctx)

Update rows:

user := &User{ID: 1, Name: "admin"}
res, err := db.NewUpdate().Model(user).Column("name").WherePK().Exec(ctx)

Delete rows:

user := &User{ID: 1}
res, err := db.NewDelete().Model(user).WherePK().Exec(ctx)

And select rows scanning the results:

// Select a user by a primary key.
user := new(User)
err := db.NewSelect().Model(user).Where("id = ?", 1).Scan(ctx)

// Select first 10 users.
var users []User
err := db.NewSelect().Model(&users).OrderExpr("id ASC").Limit(10).Scan(ctx)

Scanning query results

When it comes to scanning query results, Bun is very flexible and allows scanning into structs:

user := new(User)
err := db.NewSelect().Model(user).Limit(1).Scan(ctx)

Into scalars:

var id int64
var name string
err := db.NewSelect().Model((*User)(nil)).Column("id", "name").Limit(1).Scan(ctx, &id, &name)

Into a map[string]interface{}:

var m map[string]interface{}
err := db.NewSelect().Model((*User)(nil)).Limit(1).Scan(ctx, &m)

And into slices of the types above:

var users []User
err := db.NewSelect().Model(&users).Limit(1).Scan(ctx)

var ids []int64
var names []string
err := db.NewSelect().Model((*User)(nil)).Column("id", "name").Limit(1).Scan(ctx, &ids, &names)

var ms []map[string]interface{}
err := db.NewSelect().Model((*User)(nil)).Scan(ctx, &ms)

You can also return results from insert/update/delete queries and scan them too:

var ids []int64
res, err := db.NewDelete().Model((*User)(nil)).Returning("id").Exec(ctx, &ids)

Table relations

Bun also recognizes common table relationships, for example, you can define a belongs-to relation:

type Story struct {
	ID       int64
	Title    string
	AuthorID int64
	Author   *User `bun:"rel:belongs-to,join:author_id=id"`
}

And Bun will join the story author for you:

story := new(Story)
err := db.NewSelect().
	Model(story).
	Relation("Author").
	Limit(1).
	Scan(ctx)
SELECT
  "story"."id", "story"."title", "story"."author_id",
  "author"."id" AS "author__id",
  "author"."name" AS "author__name"
FROM "stories" AS "story"
LEFT JOIN "users" AS "author" ON ("author"."id" = "story"."author_id")
LIMIT 1

See exampleopen in new window for details.

What's next

By now, you should have basic understanding of Bun API. Next, learn how to define models and write queries.

Get insights and updates in your inbox: