The Database Layer Dilemma

Choosing the right data access layer is critical for Go applications. Get it wrong, and you’ll spend countless hours debugging runtime errors, dealing with SQL injection vulnerabilities, and wrestling with inconsistent data models.

Two excellent options have emerged in the Go ecosystem:

  • Ent - Facebook’s entity framework with code generation and type safety
  • Bun - A SQL-first ORM with excellent query building capabilities

This guide explores both, showing you when to use each and how to build reliable data layers.

Ent: The Type-Safe Schema-First Approach

What is Ent?

Ent is an entity framework that uses code generation to provide:

  • ✅ Type-safe database operations
  • ✅ Schema-first development
  • ✅ Automatic migrations
  • ✅ Graph traversal capabilities
  • ✅ Built-in validation and hooks

When to use Ent:

  • Complex domain models with relationships
  • Need strong type safety
  • Want graph-style queries
  • Prefer schema-first development
  • Building APIs with complex data requirements

Getting Started with Ent

Installation:

go get entgo.io/ent/cmd/ent

Step 1: Define Your Schema

// ent/schema/user.go
package schema

import (
    "time"

    "entgo.io/ent"
    "entgo.io/ent/schema/edge"
    "entgo.io/ent/schema/field"
    "entgo.io/ent/schema/index"
)

// User holds the schema definition for the User entity.
type User struct {
    ent.Schema
}

// Fields of the User.
func (User) Fields() []ent.Field {
    return []ent.Field{
        field.String("email").
            Unique().
            NotEmpty(),
        field.String("username").
            Unique().
            MinLen(3).
            MaxLen(50),
        field.String("full_name").
            NotEmpty(),
        field.Enum("status").
            Values("active", "inactive", "suspended").
            Default("active"),
        field.Time("created_at").
            Default(time.Now).
            Immutable(),
        field.Time("updated_at").
            Default(time.Now).
            UpdateDefault(time.Now),
    }
}

// Edges of the User.
func (User) Edges() []ent.Edge {
    return []ent.Edge{
        edge.To("posts", Post.Type),
        edge.To("profile", Profile.Type).
            Unique(),
    }
}

// Indexes of the User.
func (User) Indexes() []ent.Index {
    return []ent.Index{
        index.Fields("email"),
        index.Fields("username"),
    }
}

// ent/schema/post.go
package schema

import (
    "time"

    "entgo.io/ent"
    "entgo.io/ent/schema/edge"
    "entgo.io/ent/schema/field"
    "entgo.io/ent/schema/index"
)

// Post holds the schema definition for the Post entity.
type Post struct {
    ent.Schema
}

func (Post) Fields() []ent.Field {
    return []ent.Field{
        field.String("title").
            NotEmpty().
            MaxLen(200),
        field.Text("content").
            NotEmpty(),
        field.String("slug").
            Unique().
            NotEmpty(),
        field.Enum("status").
            Values("draft", "published", "archived").
            Default("draft"),
        field.Int("view_count").
            Default(0).
            NonNegative(),
        field.Time("published_at").
            Optional().
            Nillable(),
        field.Time("created_at").
            Default(time.Now).
            Immutable(),
        field.Time("updated_at").
            Default(time.Now).
            UpdateDefault(time.Now),
    }
}

func (Post) Edges() []ent.Edge {
    return []ent.Edge{
        edge.From("author", User.Type).
            Ref("posts").
            Unique().
            Required(),
        edge.To("comments", Comment.Type),
        edge.To("tags", Tag.Type),
    }
}

func (Post) Indexes() []ent.Index {
    return []ent.Index{
        index.Fields("slug").Unique(),
        index.Fields("status", "published_at"),
    }
}

// ent/schema/profile.go
package schema

import (
    "entgo.io/ent"
    "entgo.io/ent/schema/edge"
    "entgo.io/ent/schema/field"
)

type Profile struct {
    ent.Schema
}

func (Profile) Fields() []ent.Field {
    return []ent.Field{
        field.String("bio").
            Optional().
            MaxLen(500),
        field.String("avatar_url").
            Optional(),
        field.String("website").
            Optional(),
        field.String("location").
            Optional(),
    }
}

func (Profile) Edges() []ent.Edge {
    return []ent.Edge{
        edge.From("user", User.Type).
            Ref("profile").
            Unique().
            Required(),
    }
}

Step 2: Generate Code

go generate ./ent

This generates all the type-safe code for CRUD operations, queries, and mutations.

Step 3: Implement the Service Layer

// internal/service/user_service.go
package service

import (
    "context"
    "fmt"

    "github.com/example/ent"
    "github.com/example/ent/user"
    "github.com/example/ent/post"
)

type UserService struct {
    client *ent.Client
}

func NewUserService(client *ent.Client) *UserService {
    return &UserService{client: client}
}

// CreateUser creates a new user with profile
func (s *UserService) CreateUser(ctx context.Context, email, username, fullName, bio string) (*ent.User, error) {
    // Transaction ensures atomicity
    tx, err := s.client.Tx(ctx)
    if err != nil {
        return nil, fmt.Errorf("starting transaction: %w", err)
    }

    // Create user with profile in one transaction
    user, err := tx.User.
        Create().
        SetEmail(email).
        SetUsername(username).
        SetFullName(fullName).
        SetStatus(user.StatusActive).
        Save(ctx)
    if err != nil {
        tx.Rollback()
        return nil, fmt.Errorf("creating user: %w", err)
    }

    // Create profile
    _, err = tx.Profile.
        Create().
        SetUser(user).
        SetBio(bio).
        Save(ctx)
    if err != nil {
        tx.Rollback()
        return nil, fmt.Errorf("creating profile: %w", err)
    }

    if err := tx.Commit(); err != nil {
        return nil, fmt.Errorf("committing transaction: %w", err)
    }

    // Reload user with profile
    return s.client.User.
        Query().
        Where(user.ID(user.ID)).
        WithProfile().
        Only(ctx)
}

// GetUserWithPosts retrieves user with all their posts
func (s *UserService) GetUserWithPosts(ctx context.Context, id int) (*ent.User, error) {
    return s.client.User.
        Query().
        Where(user.ID(id)).
        WithProfile().
        WithPosts(func(q *ent.PostQuery) {
            q.Where(post.StatusEQ(post.StatusPublished)).
                Order(ent.Desc(post.FieldPublishedAt))
        }).
        Only(ctx)
}

// UpdateUser updates user details
func (s *UserService) UpdateUser(ctx context.Context, id int, fullName, bio string) (*ent.User, error) {
    tx, err := s.client.Tx(ctx)
    if err != nil {
        return nil, err
    }

    // Update user
    user, err := tx.User.
        UpdateOneID(id).
        SetFullName(fullName).
        Save(ctx)
    if err != nil {
        tx.Rollback()
        return nil, err
    }

    // Update profile
    _, err = tx.Profile.
        Update().
        Where(profile.HasUserWith(user.IDEQ(id))).
        SetBio(bio).
        Save(ctx)
    if err != nil {
        tx.Rollback()
        return nil, err
    }

    if err := tx.Commit(); err != nil {
        return nil, err
    }

    return s.GetUserWithPosts(ctx, id)
}

// ListActiveUsers returns paginated list of active users
func (s *UserService) ListActiveUsers(ctx context.Context, offset, limit int) ([]*ent.User, error) {
    return s.client.User.
        Query().
        Where(user.StatusEQ(user.StatusActive)).
        WithProfile().
        Order(ent.Desc(user.FieldCreatedAt)).
        Limit(limit).
        Offset(offset).
        All(ctx)
}

// SearchUsersByUsername searches users by username
func (s *UserService) SearchUsersByUsername(ctx context.Context, query string) ([]*ent.User, error) {
    return s.client.User.
        Query().
        Where(user.UsernameContains(query)).
        WithProfile().
        Limit(20).
        All(ctx)
}

// GetUserPostStats returns post statistics for a user
func (s *UserService) GetUserPostStats(ctx context.Context, userID int) (map[string]int, error) {
    publishedCount, err := s.client.Post.
        Query().
        Where(
            post.HasAuthorWith(user.IDEQ(userID)),
            post.StatusEQ(post.StatusPublished),
        ).
        Count(ctx)
    if err != nil {
        return nil, err
    }

    draftCount, err := s.client.Post.
        Query().
        Where(
            post.HasAuthorWith(user.IDEQ(userID)),
            post.StatusEQ(post.StatusDraft),
        ).
        Count(ctx)
    if err != nil {
        return nil, err
    }

    return map[string]int{
        "published": publishedCount,
        "draft":     draftCount,
    }, nil
}

Bun: The SQL-First Flexible Approach

What is Bun?

Bun is a lightweight ORM that:

  • ✅ Embraces SQL while providing type safety
  • ✅ Has excellent query building capabilities
  • ✅ Supports PostgreSQL, MySQL, SQLite, MSSQL
  • ✅ Includes migrations and fixtures
  • ✅ Provides great performance

When to use Bun:

  • Need flexibility in queries
  • Want SQL-like syntax
  • Existing database you’re working with
  • Prefer struct-based models
  • Need raw SQL escape hatches

Getting Started with Bun

Installation:

go get github.com/uptrace/bun
go get github.com/uptrace/bun/driver/pgdriver
go get github.com/uptrace/bun/migrate

Step 1: Define Models

// internal/model/user.go
package model

import (
    "time"

    "github.com/uptrace/bun"
)

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

    ID        int64     `bun:"id,pk,autoincrement"`
    Email     string    `bun:"email,notnull,unique"`
    Username  string    `bun:"username,notnull,unique"`
    FullName  string    `bun:"full_name,notnull"`
    Status    string    `bun:"status,notnull,default:'active'"`
    CreatedAt time.Time `bun:"created_at,notnull,default:current_timestamp"`
    UpdatedAt time.Time `bun:"updated_at,notnull,default:current_timestamp"`

    // Relations
    Profile *Profile `bun:"rel:has-one,join:id=user_id"`
    Posts   []*Post  `bun:"rel:has-many,join:id=author_id"`
}

type Profile struct {
    bun.BaseModel `bun:"table:profiles,alias:p"`

    ID        int64  `bun:"id,pk,autoincrement"`
    UserID    int64  `bun:"user_id,notnull,unique"`
    Bio       string `bun:"bio"`
    AvatarURL string `bun:"avatar_url"`
    Website   string `bun:"website"`
    Location  string `bun:"location"`

    // Relations
    User *User `bun:"rel:belongs-to,join:user_id=id"`
}

type Post struct {
    bun.BaseModel `bun:"table:posts,alias:post"`

    ID          int64      `bun:"id,pk,autoincrement"`
    AuthorID    int64      `bun:"author_id,notnull"`
    Title       string     `bun:"title,notnull"`
    Content     string     `bun:"content,notnull"`
    Slug        string     `bun:"slug,notnull,unique"`
    Status      string     `bun:"status,notnull,default:'draft'"`
    ViewCount   int        `bun:"view_count,notnull,default:0"`
    PublishedAt *time.Time `bun:"published_at"`
    CreatedAt   time.Time  `bun:"created_at,notnull,default:current_timestamp"`
    UpdatedAt   time.Time  `bun:"updated_at,notnull,default:current_timestamp"`

    // Relations
    Author *User `bun:"rel:belongs-to,join:author_id=id"`
}

Step 2: Setup Database Connection

// internal/database/database.go
package database

import (
    "database/sql"
    "fmt"

    "github.com/uptrace/bun"
    "github.com/uptrace/bun/dialect/pgdialect"
    "github.com/uptrace/bun/driver/pgdriver"
    "github.com/uptrace/bun/extra/bundebug"
)

type Config struct {
    Host     string
    Port     int
    User     string
    Password string
    Database string
    SSLMode  string
}

func NewDB(cfg Config) (*bun.DB, error) {
    dsn := fmt.Sprintf("postgres://%s:%s@%s:%d/%s?sslmode=%s",
        cfg.User,
        cfg.Password,
        cfg.Host,
        cfg.Port,
        cfg.Database,
        cfg.SSLMode,
    )

    sqldb := sql.OpenDB(pgdriver.NewConnector(pgdriver.WithDSN(dsn)))

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

    // Add query hook for debugging
    db.AddQueryHook(bundebug.NewQueryHook(
        bundebug.WithVerbose(true),
    ))

    return db, nil
}

Step 3: Create Migrations

// migrations/20250117000001_create_users.go
package migrations

import (
    "context"
    "fmt"

    "github.com/uptrace/bun"
)

func init() {
    Migrations.MustRegister(func(ctx context.Context, db *bun.DB) error {
        _, err := db.ExecContext(ctx, `
            CREATE TABLE IF NOT EXISTS users (
                id BIGSERIAL PRIMARY KEY,
                email VARCHAR(255) NOT NULL UNIQUE,
                username VARCHAR(50) NOT NULL UNIQUE,
                full_name VARCHAR(255) NOT NULL,
                status VARCHAR(20) NOT NULL DEFAULT 'active',
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
            );

            CREATE INDEX idx_users_email ON users(email);
            CREATE INDEX idx_users_username ON users(username);
            CREATE INDEX idx_users_status ON users(status);
        `)
        return err
    }, func(ctx context.Context, db *bun.DB) error {
        _, err := db.ExecContext(ctx, `DROP TABLE IF EXISTS users`)
        return err
    })

    Migrations.MustRegister(func(ctx context.Context, db *bun.DB) error {
        _, err := db.ExecContext(ctx, `
            CREATE TABLE IF NOT EXISTS profiles (
                id BIGSERIAL PRIMARY KEY,
                user_id BIGINT NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
                bio TEXT,
                avatar_url VARCHAR(500),
                website VARCHAR(500),
                location VARCHAR(255)
            );

            CREATE INDEX idx_profiles_user_id ON profiles(user_id);
        `)
        return err
    }, func(ctx context.Context, db *bun.DB) error {
        _, err := db.ExecContext(ctx, `DROP TABLE IF EXISTS profiles`)
        return err
    })

    Migrations.MustRegister(func(ctx context.Context, db *bun.DB) error {
        _, err := db.ExecContext(ctx, `
            CREATE TABLE IF NOT EXISTS posts (
                id BIGSERIAL PRIMARY KEY,
                author_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                title VARCHAR(200) NOT NULL,
                content TEXT NOT NULL,
                slug VARCHAR(255) NOT NULL UNIQUE,
                status VARCHAR(20) NOT NULL DEFAULT 'draft',
                view_count INT NOT NULL DEFAULT 0,
                published_at TIMESTAMP,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
            );

            CREATE INDEX idx_posts_author_id ON posts(author_id);
            CREATE INDEX idx_posts_slug ON posts(slug);
            CREATE INDEX idx_posts_status_published ON posts(status, published_at);
        `)
        return err
    }, func(ctx context.Context, db *bun.DB) error {
        _, err := db.ExecContext(ctx, `DROP TABLE IF EXISTS posts`)
        return err
    })
}

Step 4: Implement Repository Pattern

// internal/repository/user_repository.go
package repository

import (
    "context"
    "database/sql"
    "time"

    "github.com/example/internal/model"
    "github.com/uptrace/bun"
)

type UserRepository struct {
    db *bun.DB
}

func NewUserRepository(db *bun.DB) *UserRepository {
    return &UserRepository{db: db}
}

// CreateUser creates a new user with profile
func (r *UserRepository) CreateUser(ctx context.Context, email, username, fullName, bio string) (*model.User, error) {
    user := &model.User{
        Email:     email,
        Username:  username,
        FullName:  fullName,
        Status:    "active",
        CreatedAt: time.Now(),
        UpdatedAt: time.Now(),
    }

    // Transaction
    err := r.db.RunInTx(ctx, &sql.TxOptions{}, func(ctx context.Context, tx bun.Tx) error {
        // Insert user
        if _, err := tx.NewInsert().
            Model(user).
            Exec(ctx); err != nil {
            return err
        }

        // Insert profile
        profile := &model.Profile{
            UserID: user.ID,
            Bio:    bio,
        }

        if _, err := tx.NewInsert().
            Model(profile).
            Exec(ctx); err != nil {
            return err
        }

        return nil
    })

    if err != nil {
        return nil, err
    }

    // Reload with relations
    return r.GetUserByID(ctx, user.ID)
}

// GetUserByID retrieves user with profile and posts
func (r *UserRepository) GetUserByID(ctx context.Context, id int64) (*model.User, error) {
    user := new(model.User)
    err := r.db.NewSelect().
        Model(user).
        Where("u.id = ?", id).
        Relation("Profile").
        Relation("Posts", func(q *bun.SelectQuery) *bun.SelectQuery {
            return q.Where("status = ?", "published").
                Order("published_at DESC")
        }).
        Scan(ctx)

    if err != nil {
        return nil, err
    }

    return user, nil
}

// UpdateUser updates user and profile
func (r *UserRepository) UpdateUser(ctx context.Context, id int64, fullName, bio string) (*model.User, error) {
    err := r.db.RunInTx(ctx, &sql.TxOptions{}, func(ctx context.Context, tx bun.Tx) error {
        // Update user
        _, err := tx.NewUpdate().
            Model(&model.User{}).
            Set("full_name = ?", fullName).
            Set("updated_at = ?", time.Now()).
            Where("id = ?", id).
            Exec(ctx)
        if err != nil {
            return err
        }

        // Update profile
        _, err = tx.NewUpdate().
            Model(&model.Profile{}).
            Set("bio = ?", bio).
            Where("user_id = ?", id).
            Exec(ctx)

        return err
    })

    if err != nil {
        return nil, err
    }

    return r.GetUserByID(ctx, id)
}

// ListActiveUsers returns paginated active users
func (r *UserRepository) ListActiveUsers(ctx context.Context, offset, limit int) ([]*model.User, error) {
    var users []*model.User
    err := r.db.NewSelect().
        Model(&users).
        Where("status = ?", "active").
        Relation("Profile").
        Order("created_at DESC").
        Limit(limit).
        Offset(offset).
        Scan(ctx)

    return users, err
}

// SearchUsers searches users by username
func (r *UserRepository) SearchUsers(ctx context.Context, query string) ([]*model.User, error) {
    var users []*model.User
    err := r.db.NewSelect().
        Model(&users).
        Where("username ILIKE ?", "%"+query+"%").
        Relation("Profile").
        Limit(20).
        Scan(ctx)

    return users, err
}

// GetUserStats returns statistics about user's posts
func (r *UserRepository) GetUserStats(ctx context.Context, userID int64) (map[string]int, error) {
    var result struct {
        Published int `bun:"published"`
        Draft     int `bun:"draft"`
    }

    err := r.db.NewSelect().
        TableExpr("posts").
        ColumnExpr("COUNT(*) FILTER (WHERE status = 'published') as published").
        ColumnExpr("COUNT(*) FILTER (WHERE status = 'draft') as draft").
        Where("author_id = ?", userID).
        Scan(ctx, &result)

    if err != nil {
        return nil, err
    }

    return map[string]int{
        "published": result.Published,
        "draft":     result.Draft,
    }, nil
}

// Complex query with joins
func (r *UserRepository) GetTopAuthors(ctx context.Context, limit int) ([]*model.User, error) {
    var users []*model.User

    err := r.db.NewSelect().
        Model(&users).
        ColumnExpr("u.*").
        ColumnExpr("COUNT(post.id) as post_count").
        Join("INNER JOIN posts AS post ON post.author_id = u.id").
        Where("post.status = ?", "published").
        Group("u.id").
        Order("post_count DESC").
        Limit(limit).
        Scan(ctx)

    return users, err
}

// Raw SQL query example
func (r *UserRepository) GetUserEngagement(ctx context.Context, userID int64) (map[string]interface{}, error) {
    var result map[string]interface{}

    err := r.db.NewRaw(`
        SELECT
            u.username,
            COUNT(DISTINCT p.id) as total_posts,
            SUM(p.view_count) as total_views,
            AVG(p.view_count) as avg_views
        FROM users u
        LEFT JOIN posts p ON p.author_id = u.id
        WHERE u.id = ?
        GROUP BY u.id, u.username
    `, userID).Scan(ctx, &result)

    return result, err
}

Ent vs Bun: Feature Comparison

Feature Ent Bun
Type Safety ✅ Excellent (generated) ✅ Good (struct tags)
Schema Definition Go code Struct tags + SQL
Code Generation ✅ Yes ❌ No
Query Builder Graph-style SQL-style
Raw SQL Support Limited ✅ Excellent
Migrations Auto-generated Manual + helpers
Relations ✅ Excellent (graph traversal) ✅ Good (joins)
Learning Curve Steeper Gentler
Performance Excellent Excellent
Flexibility Structured Very flexible
Best For Complex domains SQL-heavy apps

When to Use Which?

Use Ent When:

  • ✅ Building new applications from scratch
  • ✅ Complex domain models with many relationships
  • ✅ Want automatic schema management
  • ✅ Prefer graph-style queries
  • ✅ Need strong type safety guarantees
  • ✅ Want generated CRUD operations

Use Bun When:

  • ✅ Working with existing databases
  • ✅ Need fine-grained SQL control
  • ✅ Want flexibility in queries
  • ✅ Prefer SQL-like syntax
  • ✅ Need to write complex raw SQL
  • ✅ Want lighter-weight solution

Best Practices

1. Always Use Transactions

// Ent
err := client.Tx(ctx, func(tx *ent.Tx) error {
    // Your operations
    return nil
})

// Bun
err := db.RunInTx(ctx, &sql.TxOptions{}, func(ctx context.Context, tx bun.Tx) error {
    // Your operations
    return nil
})

2. Use Context for Cancellation

ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()

users, err := repo.ListUsers(ctx)

3. Implement Repository Pattern

Keep database logic separate from business logic.

4. Use Migrations

Never modify schema manually in production.

5. Add Proper Indexes

Both support index definitions—use them!

Conclusion

Both Ent and Bun are excellent choices for building reliable data layers in Go:

  • Choose Ent for schema-first development with strong type safety and automatic generation
  • Choose Bun for SQL-first development with flexibility and fine-grained control

Key Takeaways:

  • ✅ Both provide type-safe database operations
  • ✅ Both support transactions and migrations
  • ✅ Ent excels at complex relationships and graph queries
  • ✅ Bun excels at SQL flexibility and existing database integration
  • ✅ Use repository pattern with either
  • ✅ Always use transactions for multi-step operations
  • ✅ Proper indexing is critical for performance

The best choice depends on your specific needs, team preferences, and project constraints. Many teams even use both—Ent for new services and Bun for legacy database integration.

Additional Resources