DEV Community

Cover image for Bun: SQL-first Golang ORM
Vladimir Mihailenco
Vladimir Mihailenco

Posted on • Updated on

Bun: SQL-first Golang ORM

Bun is a SQL-first Golang ORM for PostgreSQL, MySQL/MariaDB, MSSQL, and SQLite.

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

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

Generates the following SQL:

SELECT *
FROM generate_series(1, 3)
WHERE generate_series = 123
LIMIT 10
Enter fullscreen mode Exit fullscreen mode

SQL is still there, but Bun helps you generate long queries while protecting against SQL injections thanks to ? placeholders:

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'
Enter fullscreen mode Exit fullscreen mode

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

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)
Enter fullscreen mode Exit fullscreen mode

Generates the following 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
Enter fullscreen mode Exit fullscreen mode

Structs and tables

Bun allows you to map Go structs to database tables using struct-based models, for example, the following code:

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

err := db.ResetModel(ctx, &Model{})
Enter fullscreen mode Exit fullscreen mode

Generates the following table:

CREATE TABLE "models" (
  "id" BIGSERIAL NOT NULL,
  "name" VARCHAR NOT NULL,
  "created_at" TIMESTAMPTZ DEFAULT now(),
  PRIMARY KEY ("id"),
)
Enter fullscreen mode Exit fullscreen mode

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

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)
Enter fullscreen mode Exit fullscreen mode

See Bun documentation for details.

Golang ORM

So what about Golang ORM 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:

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

type Author struct {
    ID int64
}
Enter fullscreen mode Exit fullscreen mode

And then use Relation method to join tables:

err := db.NewSelect().
    Model(book).
    Relation("Author").
    Where("id = ?", 123).
    Scan(ctx)
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode

See ORM: Table relationships 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 database:

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())
Enter fullscreen mode Exit fullscreen mode

To connect to a MySQL database:

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())
Enter fullscreen mode Exit fullscreen mode

To log all executed queries, you can install bundebug plugin:

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

db.AddQueryHook(bundebug.NewQueryHook(
    bundebug.WithVerbose(true), // log everything
))
Enter fullscreen mode Exit fullscreen mode

Executing queries

Once you have a model, you can start executing queries:

// 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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

Into scalars:

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

Into a map[string]interface{}:

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

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

What's next?

To get started, see the Bun Golang ORM docs and run examples.

Bun comes with many plugins including OpenTelemetry instrumentation that enables OpenTelemetry tracing and OpenTelemetry metrics.

Using tracing, you can monitor performance using one of the distributed tracing tools that work with OpenTelemetry. Many DataDog competitors also support OpenTelemetry.

Besides, you can export metrics to Prometheus and visualize them using Grafana alternatives.

Top comments (4)

Collapse
 
jhelberg profile image
Joost Helberg

Looks nice wrt mapping structures, but the syntax is cumbersome, where the sql syntax is already fine. Also, the example uses a sub-query in the where-clause. Can't bun do joins? This query looks exactly like the ORM ones where performance hits are huge.

Collapse
 
vmihailenco profile image
Vladimir Mihailenco

where the sql syntax is already fine

I politely disagree, because it is hard to format SQL in Go code and with many placeholders it becomes even worse, .e.g. you have a huge template string with ? or %s and then 10 or so arguments.

I find Bun more readable in such cases.

Can't bun do joins?

It can and it even generates even better joins in some cases than, for example, GORM

This query looks exactly like the ORM ones where performance hits are huge.

If you mean N+1 queries then no, Bun is able to generate joins instead of using sub-queries.

As for the generating queries and scanning, the overhead is very small compared.

Collapse
 
jhelberg profile image
Joost Helberg

I like the column-selection in your ORM: "except" would be a great contribution to the SQL standard.
On syntax we will continue to disagree. $ parameter substitution is fine with me.
ORM's are great when CRUD-api's are not an anti-pattern, I'd consider using Bun then. But CRUD is an anti-pattern in most cases.
From what I see in Bun it is a ORM where one can at least try to do SQL close to 1-on-1, that is good news and absolutely vital for considering it.
I'll look into it further.
Thx for the heads up.

Collapse
 
sharandec22 profile image
Shiva Sharan

This is nice. Kinda reminds me of ActiveRecord in RoR. This is a great lib