Go Bun ORM with PostgreSQL Quickly Example

Install dependencies and environment variable

Replace the values from database connection with yours.

export DB_USER=admin
export DB_PASSWORD=admin123
export DB_NAME=code_bucket
export DB_HOST=localhost
export DB_PORT=5432

go get github.com/uptrace/bun@latest
go get github.com/uptrace/bun/driver/pgdriver
go get github.com/uptrace/bun/dialect/pgdialect

Create table

Bun have a migration system, but I think it will be required its own post. For this quickly example

create table customers(
    id serial not null,
    first_name varchar(80) not null ,
    last_name varchar(80) not null ,
    email varchar(50) not null unique,
    age int not null default 1,
    created_at timestamp not null DEFAULT now(),
    updated_at timestamp null,
    deleted_at timestamp null
);

Manager DB

Create a file to manage.go This will contain a method to get the connection db for instance in other modules and services.

import (
    "database/sql"
    "fmt"
    "github.com/uptrace/bun"
    "github.com/uptrace/bun/dialect/pgdialect"
    "github.com/uptrace/bun/driver/pgdriver"
    "os"
    "sync"
)

var (
    dbConnOnce sync.Once
    conn       *bun.DB
)

func Db() *bun.DB {
    dbConnOnce.Do(func() {
        dsn := postgresqlDsn()
        hsqldb := sql.OpenDB(pgdriver.NewConnector(pgdriver.WithDSN(dsn)))
        conn = bun.NewDB(hsqldb, pgdialect.New())
    })
    return conn
}

func postgresqlDsn() string {
    dsn := fmt.Sprintf("postgres://%s:%s@%s:%s/%s?sslmode=disable",
        os.Getenv("DB_USER"),
        os.Getenv("DB_PASSWORD"),
        os.Getenv("DB_HOST"),
        os.Getenv("DB_PORT"),
        os.Getenv("DB_NAME"),
    )
    return dsn
}

Create Struct entity

On Customers entity add the tags for mapped with bun fields.

type Customers struct {
    bun.BaseModel `bun:"table:customers,alias:cs"`
    ID            uint      `bun:"id,pk,autoincrement"`
    FirstName     string    `bun:"first_name"`
    LastName      string    `bun:"last_name"`
    Email         string    `bun:"email"`
    Age           uint      `bun:"age"`
    CreatedAt     time.Time `bun:"-"`
    UpdatedAt     time.Time `bun:"-"`
    DeletedAt     time.Time `bun:",soft_delete,nullzero"`
}

the bun:"-" omit the field in queries. You can review all tags option on the official bun documentation. Bun tags

Create Struct for manage the entity (customer) repository

Create a struct for manage the db connection and Get all methods to interact with the database entity (CRUD operations and queries)
With this struct, any time we need to access the entity (customer) data, we can instance and start to use it as a repository pattern.

type CustomersRepo struct {
    Repo *bun.DB
}

func NewCustomerRepo() (*CustomersRepo, error) {
    db := Db()
    return &CustomersRepo{
        Repo: db,
    }, nil
}

CRUD methods

A method example to store, update or get information from the entity.
These methods are used from the CustomersRepo entity.
They received a customer entity with the information and depending on the operation return the result.

Save a new customer

func (cs CustomersRepo) Save(customer *models.Customers) (*models.Customers, error) {
    err := cs.Repo.NewInsert().Model(customer).Scan(context.TODO(), customer)
    if err != nil {
        return nil, err
    }
    return customer, nil
}

Update a customer data (Required the field ID)

func (cs CustomersRepo) Update(customer *models.Customers) (*models.Customers, error) {
    _, err := cs.Repo.NewUpdate().Model(customer).Where("id = ? ", customer.ID).Exec(context.TODO())
    return customer, err
}

Get a customer from one field

func (cs CustomersRepo) GetByEmail(email string) (*models.Customers, error) {
    var customer models.Customers
    err := cs.Repo.NewSelect().Model(&customer).Where("email = ?", email).Scan(context.TODO(), &customer)
    return &customer, err
}

Get a list of customers

func (cs CustomersRepo) GetCustomers() ([]*models.Customers, error) {
    customers := make([]*models.Customers, 0)
    err := cs.Repo.NewSelect().Model(&models.Customers{}).Scan(context.TODO(), &customers)
    return customers, err
}

Delete a customer (soft deleted)

func (cs CustomersRepo) Delete(customer *models.Customers) error {
    _, err := cs.Repo.NewDelete().Model(customer).Where("id = ?", customer.ID).Exec(context.TODO())
    return err
}

Review the code example

BUN CRUD Example

Blog