Golang database/sql ORM

Connecting to a database

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, MySQL, and MSSQL.

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())

To see executed queries in stdout, install a query hook:

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)

Defining models

Bun uses struct-based models to construct 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)
// Drop and create tables.
err := db.ResetModel(ctx, (*User)(nil))

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 relationships

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.

Using Bun with existing code

If you already have code that uses *sql.Tx or *sql.Conn, you can still use Bun query builder without rewriting the existing code:

tx, err := sqldb.Begin()
if err != nil {
    panic(err)
}
res, err := bundb.NewInsert().
    Conn(tx). // run the query using the existing transaction
    Model(&model).
    Exec(ctx)

What’s next

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