Golang database/sql ORM

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.

1. import (
2. "database/sql"

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

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

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

1. import (
2. "github.com/uptrace/bun"
3. "github.com/uptrace/bun/dialect/sqlitedialect"
4. )

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

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

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

3. db.AddQueryHook(bundebug.NewQueryHook(
4. bundebug.WithVerbose(true),
5. bundebug.FromEnv("BUNDEBUG"),
6. ))

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

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

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

Or using Bun’s query builder:

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

3. var num int
4. 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:

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

4. ID     int64  `bun:",pk,autoincrement"`
5. Name string
6. }

Having a model, you can create and drop tables:

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

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

7. // Drop and create tables.
8. err := db.ResetModel(ctx, (*User)(nil))

Insert rows:

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

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

Update rows:

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

Delete rows:

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

And select rows scanning the results:

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

5. // Select first 10 users.
6. var users []User
7. 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:

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

Into scalars:

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

Into a map[string]interface{}:

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

And into slices of the types above:

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

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

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

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

1. var ids []int64
2. 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:

1. type Story struct {
2. ID       int64
3. Title    string
4. AuthorID int64
5. Author   *User `bun:"rel:belongs-to,join:author_id=id"`
6. }

And Bun will join the story author for you:

1. story := new(Story)
2. err := db.NewSelect().
3. Model(story).
4. Relation("Author").
5. Limit(1).
6. Scan(ctx)

1. SELECT
2. "story"."id", "story"."title", "story"."author_id",
3. "author"."id" AS "author__id",
4. "author"."name" AS "author__name"
5. FROM "stories" AS "story"
6. LEFT JOIN "users" AS "author" ON ("author"."id" = "story"."author_id")
7. 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:

1. tx, err := sqldb.Begin()
2. if err != nil {
3. panic(err)
4. }

6. res, err := bundb.NewInsert().
7. Conn(tx). // run the query using the existing transaction
8. Model(&model).
9. Exec(ctx)

What’s next

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

Getting started