Entities and queries

Keep Bun table mappings in internal/modules/shared/entities and queries in the owning module's repository. The owning module constructs the repository once and injects it into its service.

Extend the database-free Catalog starter when it needs persisted products. The following entity and repository are tutorial additions, not shipped Product types. Add its migration before executing queries. It does not change the database-free Catalog greeting example.

Define an entity#

Create internal/modules/shared/entities/product.go:

package entities

import "github.com/uptrace/bun"

// Product is owned by the catalog module.
type Product struct {
	bun.BaseModel `bun:"table:products,alias:p"`
	ID            string `bun:"id,pk"`
	Name          string `bun:"name,notnull"`
}

Model tags map fields; they do not create schema. Keep important invariants in database constraints and service validation. This example uses application-assigned string IDs and a non-null name.

The entity carries its Bun mapping and business state together. Keep HTTP request/response shapes in transports. Introduce a private persistence projection only when the shapes differ; a parallel model layer is not the default. See project conventions and shared types.

Create a repository#

Create the public contract in internal/modules/catalog/repository/repository.go:

package repository

import (
	"context"
	"github.com/wisalgroup/labena/internal/modules/shared/entities"
	"github.com/wisalgroup/labena/pkg/pagination"
)

type ProductRepository interface {
	Create(ctx context.Context, product *entities.Product) error
	Get(ctx context.Context, id string) (*entities.Product, error)
	List(ctx context.Context, page pagination.Pagination) ([]entities.Product, int, error)
}

Put the private implementation and its constructor in internal/modules/catalog/repository/product.go:

package repository

import (
	"context"
	"database/sql"
	"errors"

	"github.com/uptrace/bun"

	"github.com/wisalgroup/labena/internal/modules/shared/entities"
	"github.com/wisalgroup/labena/pkg/apperror"
	"github.com/wisalgroup/labena/pkg/database"
	"github.com/wisalgroup/labena/pkg/pagination"
)

var ErrProductNotFound = apperror.NotFound("The product was not found.")

type productRepository struct {
	db *bun.DB
}

func NewProductRepository(db *bun.DB) (ProductRepository, error) {
	if db == nil {
		return nil, errors.New("catalog: database is required")
	}
	return &productRepository{db: db}, nil
}

func (r *productRepository) Create(ctx context.Context, product *entities.Product) error {
	if product == nil {
		return errors.New("catalog: product is required")
	}
	executor, err := database.Executor(ctx, r.db)
	if err != nil {
		return err
	}
	_, err = executor.NewInsert().Model(product).Exec(ctx)
	return err
}

func (r *productRepository) Get(ctx context.Context, id string) (*entities.Product, error) {
	executor, err := database.Executor(ctx, r.db)
	if err != nil {
		return nil, err
	}
	product := new(entities.Product)
	err = executor.NewSelect().Model(product).Where("p.id = ?", id).Scan(ctx)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, ErrProductNotFound
	}
	if err != nil {
		return nil, err
	}
	return product, nil
}

func (r *productRepository) List(ctx context.Context, page pagination.Pagination) ([]entities.Product, int, error) {
	executor, err := database.Executor(ctx, r.db)
	if err != nil {
		return nil, 0, err
	}
	page = pagination.NewPagination(page.Page, page.PerPage)
	products := make([]entities.Product, 0)
	total, err := executor.NewSelect().Model(&products).
		OrderExpr("p.id ASC").Limit(page.PerPage).Offset(page.Offset()).
		ScanAndCount(ctx)
	if err != nil {
		return nil, 0, err
	}
	return products, total, nil
}

All three operations resolve the executor from context. They work outside a transaction and join the caller's transaction when one exists. Calling r.db.NewInsert() directly would bypass that scope.

Get translates a missing row into a module error. Other errors remain available to the caller. Service operations should validate IDs and names before writing; repositories do not replace business validation.

ErrProductNotFound is a safe public application error owned by this repository. If several Catalog behaviors share it, place the sentinel in the module's domain package and reuse it. Do not replace raw database errors with public validation messages. See error ownership.

Return a page#

List receives pagination.Pagination and returns rows plus the total. The service constructs the standard page; the handler converts entities into public responses:

// Inside the proposed product service; s.products is ProductRepository.
items, total, err := s.products.List(ctx, page)
if err != nil {
    return nil, err
}
return pagination.NewPage(items, page, total), nil

Normalize programmatic input with pagination.NewPagination and use deterministic ordering. This example orders by unique product ID, following database collation, not creation time. For chronological results, use a timestamp plus unique ID and an index matching that order.

ScanAndCount reads rows and count separately. Concurrent writes can change the total between them; a page is not automatically a consistent snapshot. Stronger snapshot requirements need a deliberately chosen transaction/isolation policy. Deep offsets can also become expensive; measure actual query plans.

For cursor-based traversal, define a separate operation with a stable ordering and an explicit cursor contract. The equivalent query fragment for ID ordering is:

query := executor.NewSelect().Model(&products).OrderExpr("p.id ASC").Limit(limit)
if after != "" {
    query.Where("p.id > ?", after)
}
if err := query.Scan(ctx); err != nil {
    return nil, err
}

Here limit is validated to 1–100, after is the previous final ID, and the method returns the bounded slice. It does not provide a total count or replace the framework's pagination.Pagination convention for normal list endpoints.

Update selected fields#

Add this method to product.go and add its signature to ProductRepository when the service needs this operation:

func (r *productRepository) Rename(ctx context.Context, id, name string) error {
    executor, err := database.Executor(ctx, r.db)
    if err != nil {
        return err
    }
    _, err = executor.NewUpdate().Model((*entities.Product)(nil)).
        Set("name = ?", name).Where("id = ?", id).Exec(ctx)
    return err
}

This method updates only the name. Its contract does not distinguish a missing row from a successful update with no changes. If that distinction matters, design it explicitly: affected-row behavior for unchanged values differs by driver settings. Do not assume every zero count means “not found.”

For concurrent state transitions, include the expected state or version in the update condition and define what a failed condition means. A read followed by an unconditional update is not a concurrency guarantee.

Delete by identity#

Add Delete(context.Context, string) error to the interface if this operation is needed, and put the implementation in product.go.

func (r *productRepository) Delete(ctx context.Context, id string) error {
    executor, err := database.Executor(ctx, r.db)
    if err != nil {
        return err
    }
    _, err = executor.NewDelete().Model((*entities.Product)(nil)).
        Where("id = ?", id).Exec(ctx)
    return err
}

This is a hard delete; a missing row is a successful no-op. The service must decide whether deletion is allowed. Foreign-key constraints remain authoritative. Add soft deletion only when the product needs its retention and query rules.

Load relationships deliberately#

After defining a Bun relationship field named Items on a model, a query can load it with:

err := executor.NewSelect().Model(&order).
    Relation("Items").Where("o.id = ?", id).Scan(ctx)

This fragment requires an Order mapping with alias o, the relation tags, and corresponding schema; the sample project does not contain those persisted models. Load only the relationships an operation needs. Inspect query count and size; avoid issuing a relation query for every result in a loop.

Cross-module joins stay in deliberate repository queries. Central model definitions do not grant permission to write another module's data.

Raw SQL and dynamic input#

Use the same executor for raw SQL inside a transaction:

var count int
err := executor.NewRaw("SELECT count(*) FROM products WHERE name = ?", name).
    Scan(ctx, &count)

Bind values through placeholders. Choose table names, sort columns, and directions from an allowlist; do not concatenate request input into SQL. Keep result sets bounded and add indexes for actual filters and ordering.

Construct once and reuse#

In Catalog's New, borrow Resources.DB, construct NewProductRepository, and pass its interface to NewProductService. The service holds that repository, not the entire resource bundle. Neither package opens or closes the shared pool. See dependency injection for the construction pattern.

Verify queries and diagnose failures#

Test repository behavior against the actual database engine in tests/catalog: create/get, missing records, deterministic pages, update/delete semantics, and joining a transaction that rolls back. Keep tests out of the business repository package itself.

Use request SQL traces to inspect sanitized query structure. Continue with transactions, migrations, and database tests.