Adding Models

Create new database entities in the Go backend.

Overview

Adding a new domain requires creating 6 files in a new package plus a SQL migration, following the standard domain pattern. The steps are:

SQL migration script
Go model struct (model.go)
DTOs (dto.go)
Repository (repository.go)
Service (service.go)
Handler (handler.go)
Route registration (routes.go)
Wire up in router

Step 1: SQL Migration

Create database/migrations/{NNN}_add_entity.sql:

sql

-- Migration: 065_add_entity.sql
-- Description: Add new entity table

CREATE TABLE IF NOT EXISTS entities (
    entity_id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    status_id INTEGER NOT NULL,
    department_id INTEGER,
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),

    CONSTRAINT fk_entities_status
        FOREIGN KEY (status_id)
        REFERENCES lookup_entity_status(id),
    CONSTRAINT fk_entities_department
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
);

CREATE INDEX IF NOT EXISTS idx_entities_status ON entities(status_id);
CREATE INDEX IF NOT EXISTS idx_entities_department ON entities(department_id);

Apply it through the runner, so it is recorded in the migration ledger and the next deploy does not try to apply it again:

powershell

.\scripts\db-migrate.ps1 -Apply

Step 2: Go Model

Create sis_backendgo/internal/domain/{name}/model.go:

package entitydomain

import "time"

type Entity struct {
    EntityID       int32     `db:"entity_id"`
    Name           string    `db:"name"`
    Description    *string   `db:"description"`
    StatusID       int32     `db:"status_id"`
    DepartmentID   *int32    `db:"department_id"`
    IsActive       bool      `db:"is_active"`
    CreatedAt      time.Time `db:"created_at"`
    UpdatedAt      time.Time `db:"updated_at"`

    // Joined fields (from SQL JOINs)
    StatusCode     string  `db:"status_code"`
    StatusDisplay  string  `db:"status_display"`
    DepartmentCode *string `db:"department_code"`
    DepartmentName *string `db:"department_name"`
}

Step 3: DTOs

Create sis_backendgo/internal/domain/{name}/dto.go:

package entitydomain

import "sis-backend-go/internal/shared/db"

// Query parameters
type EntityQueryParams struct {
    db.PaginationParams
    Search       *string `json:"search"`
    StatusID     *int32  `json:"status_id"`
    DepartmentID *int32  `json:"department_id"`
}

// Request DTOs
type CreateEntityRequest struct {
    Name         string  `json:"name" validate:"required,max=200"`
    Description  *string `json:"description" validate:"omitempty,max=4000"`
    StatusID     int32   `json:"status_id" validate:"required,gt=0"`
    DepartmentID *int32  `json:"department_id"`
}

type UpdateEntityRequest struct {
    Name         *string `json:"name" validate:"omitempty,max=200"`
    Description  *string `json:"description" validate:"omitempty,max=4000"`
    StatusID     *int32  `json:"status_id" validate:"omitempty,gt=0"`
    DepartmentID *int32  `json:"department_id"`
}

// Response DTOs
type EntityListDTO struct {
    EntityID       int32   `json:"entity_id"`
    Name           string  `json:"name"`
    StatusCode     string  `json:"status_code"`
    StatusDisplay  string  `json:"status_display"`
    DepartmentName *string `json:"department_name,omitempty"`
    IsActive       bool    `json:"is_active"`
}

type EntityDetailDTO struct {
    EntityID       int32   `json:"entity_id"`
    Name           string  `json:"name"`
    Description    *string `json:"description,omitempty"`
    StatusID       int32   `json:"status_id"`
    StatusCode     string  `json:"status_code"`
    StatusDisplay  string  `json:"status_display"`
    DepartmentID   *int32  `json:"department_id,omitempty"`
    DepartmentCode *string `json:"department_code,omitempty"`
    DepartmentName *string `json:"department_name,omitempty"`
    IsActive       bool    `json:"is_active"`
    CreatedAt      string  `json:"created_at"`
    UpdatedAt      string  `json:"updated_at"`
}

Step 4: Repository

Create sis_backendgo/internal/domain/{name}/repository.go:

package entitydomain

import (
    "context"
    "database/sql"

    "sis-backend-go/internal/shared/db"
    "sis-backend-go/internal/shared/errors"
)

type EntityRepository interface {
    FindByID(ctx context.Context, id int32) (*Entity, error)
    FindAll(ctx context.Context, params *EntityQueryParams) ([]Entity, int64, error)
    Create(ctx context.Context, entity *Entity) error
    Update(ctx context.Context, entity *Entity) error
    Delete(ctx context.Context, id int32) error
}

type entityRepository struct {
    db *sql.DB
}

func NewEntityRepository(database *sql.DB) EntityRepository {
    return &entityRepository{db: database}
}

func (r *entityRepository) FindByID(ctx context.Context, id int32) (*Entity, error) {
    query := `
        SELECT
            e.entity_id, e.name, e.description,
            e.status_id, les.code as status_code, les.display_value as status_display,
            e.department_id, d.department_code, d.name as department_name,
            e.is_active, e.created_at, e.updated_at
        FROM entities e
        JOIN lookup_entity_status les ON e.status_id = les.id
        LEFT JOIN departments d ON e.department_id = d.department_id
        WHERE e.entity_id = $1
    `
    var entity Entity
    err := r.db.QueryRowContext(ctx, query, id).Scan(
        &entity.EntityID, &entity.Name, &entity.Description,
        &entity.StatusID, &entity.StatusCode, &entity.StatusDisplay,
        &entity.DepartmentID, &entity.DepartmentCode, &entity.DepartmentName,
        &entity.IsActive, &entity.CreatedAt, &entity.UpdatedAt,
    )
    if err == sql.ErrNoRows {
        return nil, errors.NewNotFoundError("entity")
    }
    if err != nil {
        return nil, err
    }
    return &entity, nil
}

func (r *entityRepository) FindAll(ctx context.Context, params *EntityQueryParams) ([]Entity, int64, error) {
    qb := db.NewQueryBuilder("entities e")
    qb.Join("JOIN lookup_entity_status les ON e.status_id = les.id")
    qb.Join("LEFT JOIN departments d ON e.department_id = d.department_id")

    if params.Search != nil && *params.Search != "" {
        search := "%" + *params.Search + "%"
        qb.Where("(e.name ILIKE ? OR e.description ILIKE ?)", search, search)
    }
    if params.StatusID != nil {
        qb.Where("e.status_id = ?", *params.StatusID)
    }
    if params.DepartmentID != nil {
        qb.Where("e.department_id = ?", *params.DepartmentID)
    }

    // Count
    countSQL, countArgs := qb.CountSQL()
    var total int64
    r.db.QueryRowContext(ctx, countSQL, countArgs...).Scan(&total)

    // Paginate
    qb.Offset(params.GetOffset()).Limit(params.GetLimit())
    selectSQL, selectArgs := qb.SelectSQL()
    rows, err := r.db.QueryContext(ctx, selectSQL, selectArgs...)
    if err != nil {
        return nil, 0, err
    }
    defer rows.Close()

    entities, err := db.ScanRows[Entity](rows)
    return entities, total, err
}

func (r *entityRepository) Create(ctx context.Context, entity *Entity) error {
    query := `
        INSERT INTO entities (name, description, status_id, department_id)
        VALUES ($1, $2, $3, $4)
        RETURNING entity_id, created_at, updated_at
    `
    return r.db.QueryRowContext(ctx, query,
        entity.Name, entity.Description, entity.StatusID, entity.DepartmentID,
    ).Scan(&entity.EntityID, &entity.CreatedAt, &entity.UpdatedAt)
}

func (r *entityRepository) Update(ctx context.Context, entity *Entity) error {
    query := `
        UPDATE entities
        SET name = $1, description = $2, status_id = $3,
            department_id = $4, updated_at = NOW()
        WHERE entity_id = $5
    `
    _, err := r.db.ExecContext(ctx, query,
        entity.Name, entity.Description, entity.StatusID,
        entity.DepartmentID, entity.EntityID,
    )
    return err
}

func (r *entityRepository) Delete(ctx context.Context, id int32) error {
    _, err := r.db.ExecContext(ctx, "DELETE FROM entities WHERE entity_id = $1", id)
    return err
}

Step 5: Service

Create sis_backendgo/internal/domain/{name}/service.go:

package entitydomain

import (
    "context"
    "log/slog"
    "time"

    "sis-backend-go/internal/shared/db"
    "sis-backend-go/internal/shared/errors"
)

type EntityService interface {
    GetByID(ctx context.Context, id int32) (*EntityDetailDTO, error)
    List(ctx context.Context, params *EntityQueryParams) (*db.PaginatedResult[EntityListDTO], error)
    Create(ctx context.Context, req *CreateEntityRequest) (*EntityDetailDTO, error)
    Update(ctx context.Context, id int32, req *UpdateEntityRequest) (*EntityDetailDTO, error)
    Delete(ctx context.Context, id int32) error
}

type entityService struct {
    repo   EntityRepository
    logger *slog.Logger
}

func NewEntityService(repo EntityRepository, logger *slog.Logger) EntityService {
    return &entityService{repo: repo, logger: logger}
}

func (s *entityService) GetByID(ctx context.Context, id int32) (*EntityDetailDTO, error) {
    entity, err := s.repo.FindByID(ctx, id)
    if err != nil {
        return nil, err
    }
    return toDetailDTO(entity), nil
}

func (s *entityService) List(ctx context.Context, params *EntityQueryParams) (*db.PaginatedResult[EntityListDTO], error) {
    entities, total, err := s.repo.FindAll(ctx, params)
    if err != nil {
        return nil, errors.NewInternalError("Failed to retrieve entities", err)
    }

    results := make([]EntityListDTO, len(entities))
    for i, e := range entities {
        results[i] = toListDTO(e)
    }

    return &db.PaginatedResult[EntityListDTO]{
        Count:   total,
        Results: results,
    }, nil
}

func (s *entityService) Create(ctx context.Context, req *CreateEntityRequest) (*EntityDetailDTO, error) {
    entity := &Entity{
        Name:         req.Name,
        Description:  req.Description,
        StatusID:     req.StatusID,
        DepartmentID: req.DepartmentID,
    }

    if err := s.repo.Create(ctx, entity); err != nil {
        return nil, err
    }

    return s.GetByID(ctx, entity.EntityID)
}

func (s *entityService) Update(ctx context.Context, id int32, req *UpdateEntityRequest) (*EntityDetailDTO, error) {
    entity, err := s.repo.FindByID(ctx, id)
    if err != nil {
        return nil, err
    }

    if req.Name != nil {
        entity.Name = *req.Name
    }
    if req.Description != nil {
        entity.Description = req.Description
    }
    if req.StatusID != nil {
        entity.StatusID = *req.StatusID
    }
    if req.DepartmentID != nil {
        entity.DepartmentID = req.DepartmentID
    }
    entity.UpdatedAt = time.Now()

    if err := s.repo.Update(ctx, entity); err != nil {
        return nil, err
    }

    return s.GetByID(ctx, id)
}

func (s *entityService) Delete(ctx context.Context, id int32) error {
    return s.repo.Delete(ctx, id)
}

// DTO conversion helpers
func toDetailDTO(e *Entity) *EntityDetailDTO {
    return &EntityDetailDTO{
        EntityID:       e.EntityID,
        Name:           e.Name,
        Description:    e.Description,
        StatusID:       e.StatusID,
        StatusCode:     e.StatusCode,
        StatusDisplay:  e.StatusDisplay,
        DepartmentID:   e.DepartmentID,
        DepartmentCode: e.DepartmentCode,
        DepartmentName: e.DepartmentName,
        IsActive:       e.IsActive,
        CreatedAt:      e.CreatedAt.Format(time.RFC3339),
        UpdatedAt:      e.UpdatedAt.Format(time.RFC3339),
    }
}

func toListDTO(e Entity) EntityListDTO {
    return EntityListDTO{
        EntityID:       e.EntityID,
        Name:           e.Name,
        StatusCode:     e.StatusCode,
        StatusDisplay:  e.StatusDisplay,
        DepartmentName: e.DepartmentName,
        IsActive:       e.IsActive,
    }
}

Step 6: Handler

Create sis_backendgo/internal/domain/{name}/handler.go:

package entitydomain

import (
    "encoding/json"
    "net/http"
    "strconv"

    "sis-backend-go/internal/shared/db"
    "sis-backend-go/internal/shared/response"
    "sis-backend-go/internal/shared/validator"
)

type Handler struct {
    service   EntityService
    validator *validator.Validator
}

func NewHandler(svc EntityService, v *validator.Validator) *Handler {
    return &Handler{service: svc, validator: v}
}

func (h *Handler) ListEntities(w http.ResponseWriter, r *http.Request) {
    params := &EntityQueryParams{
        PaginationParams: *db.ParsePaginationParams(r),
    }
    if s := r.URL.Query().Get("search"); s != "" {
        params.Search = &s
    }

    result, err := h.service.List(r.Context(), params)
    if err != nil {
        response.HandleError(w, err)
        return
    }

    response.OK(w, result)
}

func (h *Handler) GetEntity(w http.ResponseWriter, r *http.Request) {
    id, err := strconv.Atoi(r.PathValue("entityID"))
    if err != nil {
        response.BadRequest(w, "Invalid entity ID")
        return
    }

    entity, err := h.service.GetByID(r.Context(), int32(id))
    if err != nil {
        response.HandleError(w, err)
        return
    }

    response.OK(w, entity)
}

func (h *Handler) CreateEntity(w http.ResponseWriter, r *http.Request) {
    var req CreateEntityRequest
    if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
        response.BadRequest(w, "Invalid JSON")
        return
    }

    if err := h.validator.Struct(&req); err != nil {
        response.ValidationError(w, err)
        return
    }

    entity, err := h.service.Create(r.Context(), &req)
    if err != nil {
        response.HandleError(w, err)
        return
    }

    response.Created(w, entity)
}

func (h *Handler) UpdateEntity(w http.ResponseWriter, r *http.Request) {
    id, err := strconv.Atoi(r.PathValue("entityID"))
    if err != nil {
        response.BadRequest(w, "Invalid entity ID")
        return
    }

    var req UpdateEntityRequest
    if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
        response.BadRequest(w, "Invalid JSON")
        return
    }

    if err := h.validator.Struct(&req); err != nil {
        response.ValidationError(w, err)
        return
    }

    entity, err := h.service.Update(r.Context(), int32(id), &req)
    if err != nil {
        response.HandleError(w, err)
        return
    }

    response.OK(w, entity)
}

func (h *Handler) DeleteEntity(w http.ResponseWriter, r *http.Request) {
    id, err := strconv.Atoi(r.PathValue("entityID"))
    if err != nil {
        response.BadRequest(w, "Invalid entity ID")
        return
    }

    if err := h.service.Delete(r.Context(), int32(id)); err != nil {
        response.HandleError(w, err)
        return
    }

    response.NoContent(w)
}

Step 7: Route Registration

Create sis_backendgo/internal/domain/{name}/routes.go:

package entitydomain

import (
    "database/sql"
    "net/http"

    "sis-backend-go/internal/shared/validator"
)

func RegisterRoutes(reg *authz.Registrar, prefix string, database *sql.DB,
    v *validator.Validator) {

    repo := NewEntityRepository(database)
    service := NewEntityService(repo, nil)
    h := NewHandler(service, v)

    p := prefix + "/domain"

    // The node must also be added to authz.KnownNodes and created by a
    // migration, or nodes_test.go fails the build.
    entities := reg.Node("DOMAIN.ENTITIES")

    entities.Handle("GET "+p+"/entities", h.ListEntities)
    entities.Handle("POST "+p+"/entities", h.CreateEntity)
    entities.Handle("GET "+p+"/entities/{entityID}", h.GetEntity)
    entities.Handle("PUT "+p+"/entities/{entityID}", h.UpdateEntity)
    entities.Handle("DELETE "+p+"/entities/{entityID}", h.DeleteEntity)
}

Step 8: Wire Up in Router

Add the domain import and route registration to sis_backendgo/internal/router/router.go:

import (
    // ... existing imports
    entitydomain "github.com/sdasis/sis-backend-go/internal/domain/{name}"
)

// In the New() function, add:
entitydomain.RegisterRoutes(mux, p, deps.DB, deps.Validator, protect)

Verification

Test the new endpoint:

bash

# List
curl -b cookies.txt http://localhost:8000/api/v1/domain/entities

# Create
curl -b cookies.txt -X POST http://localhost:8000/api/v1/domain/entities \
  -H "Content-Type: application/json" \
  -H "X-CSRF-Token: <csrf_token>" \
  -d '{"name": "Test", "status_id": 1}'

# Get by ID
curl -b cookies.txt http://localhost:8000/api/v1/domain/entities/1

TIP

Authentication uses session cookies. For manual testing with curl, first login via POST /api/v1/auth/login and save the cookies, then pass them with -b cookies.txt on subsequent requests.

Adding Models ​

Overview ​

Step 1: SQL Migration ​

Step 2: Go Model ​

Step 3: DTOs ​

Step 4: Repository ​

Step 5: Service ​

Step 6: Handler ​

Step 7: Route Registration ​

Step 8: Wire Up in Router ​

Verification ​