Bun: SQL-first Golang ORM
Tags: go, database, sql
Published: 2022-04-21 07:12:14

Bun is a SQL-first [Golang ORM](https://bun.uptrace.dev/guide/golang-orm.html) for PostgreSQL, MySQL/MariaDB, MSSQL, and SQLite.

SQL-first means you can write SQL queries in Go, for example, the following Bun query:

```go
var num int
err := db.NewSelect().
    TableExpr("generate_series(1, 3)").
    Where("generate_series = ?", 3).
    Limit(10).
    Scan(ctx, &num)
```

Generates the following SQL:

```sql
SELECT *
FROM generate_series(1, 3)
WHERE generate_series = 123
LIMIT 10
```

SQL is still there, but Bun helps you generate long queries while protecting against SQL injections thanks to `?` [placeholders](https://bun.uptrace.dev/guide/placeholders.html):

```go
Where("id = ?", 123)     // WHERE id = 123
Where("id >= ?", 123)    // WHERE id >= 123
Where("id = ?", "hello") // WHERE id = 'hello'

Where("id IN (?)", bun.In([]int{1, 2, 3})) // WHERE id IN (1, 2, 3)

Where("? = ?", bun.Ident("column"), "value") // WHERE "column" = 'value'
```

Using Bun, you can write really complex queries, for example, the following Bun query:

```go
regionalSales := db.NewSelect().
	ColumnExpr("region").
	ColumnExpr("SUM(amount) AS total_sales").
	TableExpr("orders").
	GroupExpr("region")

topRegions := db.NewSelect().
	ColumnExpr("region").
	TableExpr("regional_sales").
	Where("total_sales > (SELECT SUM(total_sales) / 10 FROM regional_sales)")

var []items map[string]interface{}

err := db.NewSelect().
	With("regional_sales", regionalSales).
	With("top_regions", topRegions).
	ColumnExpr("region").
	ColumnExpr("product").
	ColumnExpr("SUM(quantity) AS product_units").
	ColumnExpr("SUM(amount) AS product_sales").
	TableExpr("orders").
	Where("region IN (SELECT region FROM top_regions)").
	GroupExpr("region").
	GroupExpr("product").
	Scan(ctx, &items)
```

Generates the following SQL:

```sql
WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
), top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product
```

## Structs and tables

Bun allows you to map Go structs to database tables using struct-based [models](https://bun.uptrace.dev/guide/models.html), for example, the following code:

```go
type Model struct {
    ID        int64 `bun:",pk,autoincrement"`
    Name      string `bun:",notnull"`
    CreatedAt time.Time `bun:",nullzero,default:now()"`
}

err := db.ResetModel(ctx, &Model{})
```

Generates the following table:

```sql
CREATE TABLE "models" (
  "id" BIGSERIAL NOT NULL,
  "name" VARCHAR NOT NULL,
  "created_at" TIMESTAMPTZ DEFAULT now(),
  PRIMARY KEY ("id"),
)
```

You can then select/insert/update/delete rows using Go structs:

```go
model := new(Model)
err := db.NewSelect().Model().Where("id = ?", 123).Scan(ctx)

model.ID = 0
res, err := db.NewInsert().Model(model).Exec(ctx)

res, err := db.NewUpdate().
    Model(model).
    Set("name = ?", "updated name").
    WherePK().
    Exec(ctx)

res, err := db.NewDelete().Model(model).WherePK().Exec(ctx)
```

See [Bun documentation](https://bun.uptrace.dev/) for details.

## Golang ORM

So what about [Golang ORM](https://bun.uptrace.dev/guide/golang-orm.html) part? Bun allows you to define common table relations using Go structs, for example, here is how you can define `Author` belongs to `Book` relation:

```go
type Book struct {
	ID		 int64
	AuthorID int64
	Author	 Author `bun:"rel:belongs-to,join:author_id=id"`
}

type Author struct {
	ID int64
}
```

And then use `Relation` method to join tables:

```go
err := db.NewSelect().
	Model(book).
	Relation("Author").
	Where("id = ?", 123).
	Scan(ctx)
```

```go
SELECT
  "book"."id", "book"."title", "book"."text",
  "author"."id" AS "author__id", "author"."name" AS "author__name"
FROM "books"
LEFT JOIN "users" AS "author" ON "author"."id" = "book"."author_id"
WHERE id = 1
```

See [ORM: Table relationships](https://bun.uptrace.dev/guide/relations.html) for details.

## Connecting to a database

Bun works on top of database/sql and supports PostgreSQL, MySQL/MariaDB, MSSQL, and SQLite.

To connect to a [PostgreSQL](https://bun.uptrace.dev/postgres/) database:

```go
import (
	"github.com/uptrace/bun"
	"github.com/uptrace/bun/dialect/pgdialect"
	"github.com/uptrace/bun/driver/pgdriver"
)

dsn := "postgres://postgres:@localhost:5432/test?sslmode=disable"
sqldb := sql.OpenDB(pgdriver.NewConnector(pgdriver.WithDSN(dsn)))

db := bun.NewDB(sqldb, pgdialect.New())
```

To connect to a [MySQL](https://bun.uptrace.dev/guide/drivers.html#mysql) database:

```go
import (
	"github.com/uptrace/bun"
	"github.com/uptrace/bun/dialect/mysqldialect"
	_ "github.com/go-sql-driver/mysql"
)

sqldb, err := sql.Open("mysql", "root:pass@/test")
if err != nil {
	panic(err)
}

db := bun.NewDB(sqldb, mysqldialect.New())
```

To log all executed queries, you can install [bundebug plugin](https://bun.uptrace.dev/guide/debugging.html):

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

db.AddQueryHook(bundebug.NewQueryHook(
	bundebug.WithVerbose(true), // log everything
))
```

## Executing queries

Once you have a [model](https://bun.uptrace.dev/guide/models.html), you can start executing queries:

```go
// 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)
```

When it comes to [scanning](https://bun.uptrace.dev/queries.html#scan-and-exec) query results, Bun is very flexible and allows scanning into structs:

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

Into scalars:

```go
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{}`:

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

And into slices of the types above:

```go
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:

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

## What's next?

To get started, see the Bun [Golang ORM](https://bun.uptrace.dev/guide/golang-orm.html) docs and run [examples](https://github.com/uptrace/bun/tree/master/example).

Bun comes with many plugins including [OpenTelemetry](https://uptrace.dev/opentelemetry/) instrumentation that enables [OpenTelemetry tracing](https://uptrace.dev/opentelemetry/distributed-tracing.html) and [OpenTelemetry metrics](https://uptrace.dev/opentelemetry/metrics.html).

Using tracing, you can [monitor performance](https://bun.uptrace.dev/guide/performance-monitoring.html) using one of the [distributed tracing tools](https://uptrace.dev/blog/distributed-tracing-tools.html) that work with OpenTelemetry. Many [DataDog competitors](https://uptrace.dev/blog/datadog-competitors.html) also support OpenTelemetry.

Besides, you can [export metrics to Prometheus](https://uptrace.dev/opentelemetry/prometheus-metrics.html) and visualize them using [Grafana alternatives](https://uptrace.dev/blog/grafana-alternatives.html).