sql

[KevinNitroG]

Goose squash: pressly/goose#345

---

https://zemfira.me/posts/things-i-wish-i-knew-about-sqlc/#dynamic-update-params

https://github.com/yiplee/sqlc-dynamic-query-example

sqlc-dev/sqlc#3484

sqlc-dev/sqlc#3979

http://tatiyants.com/how-to-navigate-json-trees-in-postgres-using-recursive-ctes/

Sqlx sqlc goose? https://g.co/gemini/share/7ae9a6c3839b

Use gorm, add overlay of sqlc above if complex, but require pg dump schema out: https://g.co/gemini/share/1b7fca4d4306

Otel for pgx: https://github.com/exaring/otelpgx

Orm dynamic: https://www.gmhafiz.com/blog/golang-database-library-orm-example-dynamic-list/

[KevinNitroG]

Gemini said so about application transaction

// internal/domain/repository.go
package domain

import "context"

// Transactor defines the interface for managing database transactions 
// at the Use Case level.
type Transactor interface {
	WithTransaction(ctx context.Context, fn func(ctx context.Context) error) error
}

// UserRepository manages the User Aggregate Root.
type UserRepository interface {
	FindByID(ctx context.Context, id string) (*User, error)
	Save(ctx context.Context, user *User) error
}

// OrderRepository manages the Order Aggregate Root.
type OrderRepository interface {
	Save(ctx context.Context, order *Order) error
}


// internal/application/usecase/order_service.go

type OrderService struct {
    // These are interfaces, not concrete GORM/SQL types
    userRepo   domain.UserRepository
    orderRepo  domain.OrderRepository
    transactor domain.Transactor 
}

// NewOrderService is the constructor used in your main.go
func NewOrderService(u domain.UserRepository, o domain.OrderRepository, t domain.Transactor) *OrderService {
    return &OrderService{
        userRepo:   u,
        orderRepo:  o,
        transactor: t,
    }
}

func (s *OrderService) PlaceOrder(ctx context.Context, cmd PlaceOrderCommand) error {
    // This is where the transactor "ability" is used
    return s.transactor.WithTransaction(ctx, func(txCtx context.Context) error {
        // ... business logic calling multiple repos using txCtx ...
        return nil
    })
}

// internal/infrastructure/persistence/transactor.go

type GormTransactor struct {
    db *gorm.DB
}

func NewGormTransactor(db *gorm.DB) domain.Transactor {
    return &GormTransactor{db: db}
}

func (t *GormTransactor) WithTransaction(ctx context.Context, fn func(context.Context) error) error {
    return t.db.Transaction(func(tx *gorm.DB) error {
        // We put the GORM transaction instance into the context
        // so that repositories can find it later.
        ctxWithTx := context.WithValue(ctx, "tx_ptr", tx)
        return fn(ctxWithTx)
    })
}

func main() {
    // 1. Setup GORM
    gormDB, _ := gorm.Open(...)

    // 2. Setup Infrastructure (The "Ability" providers)
    // Both repos and the transactor get the same gormDB pool
    userRepo   := persistence.NewGormUserRepository(gormDB)
    orderRepo  := persistence.NewGormOrderRepository(gormDB)
    transactor := persistence.NewGormTransactor(gormDB)

    // 3. Setup Application (The Use Case)
    // We "inject" the GormTransactor into the OrderService
    orderService := usecase.NewOrderService(userRepo, orderRepo, transactor)

    // 4. Give the service to your Router/API
    // http.HandleFunc("/order", handler.NewOrderHandler(orderService))
}

[KevinNitroG]

Unit of work in app?

package usecase

// Application Service
// Notice: It handles transactions, repositories, and high-level flow.
type SendMoneyUseCase struct {
    transferService *service.CrossBorderTransferService // Uses the Domain Service
    accountRepo     repository.AccountRepository
    unitOfWork      repository.UnitOfWork // For transactions
}

func (uc *SendMoneyUseCase) Execute(ctx context.Context, req SendMoneyDTO) error {
    // 1. Orchestration: Start a Transaction
    tx, err := uc.unitOfWork.Begin(ctx)
    if err != nil {
        return err
    }
    defer tx.Rollback() // Safety net

    // 2. Load Data (Repositories)
    sender, _ := uc.accountRepo.Get(ctx, req.SenderID)
    receiver, _ := uc.accountRepo.Get(ctx, req.ReceiverID)

    // 3. Delegate to Domain Service for the "Thinking"
    // The App Service doesn't know how to calculate rates or fees. It just asks the Domain Service.
    if err := uc.transferService.CalculateAndApply(sender, receiver, req.Amount); err != nil {
        return err // Business error (e.g., "Insuficient Funds")
    }

    // 4. Persistence
    uc.accountRepo.Save(ctx, sender)
    uc.accountRepo.Save(ctx, receiver)

    // 5. Commit Transaction
    if err := tx.Commit(); err != nil {
        return err
    }
    
    // 6. Side Effects (Non-Domain logic)
    // Emailing the user is an application concern, not a domain rule.
    uc.emailService.SendReceipt(sender.Email, req.Amount)

    return nil
}

// internal/domain/repository/unit_of_work.go

type UnitOfWork interface {
    // Begin starts a transaction and returns a new UoW instance bound to it
    Begin(ctx context.Context) (UnitOfWork, error)
    
    // Commit applies all changes
    Commit() error
    
    // Rollback reverts changes
    Rollback() error

    // Accessors for Repositories involved in this UoW
    Accounts() AccountRepository
    AuditLogs() AuditLogRepository
}

// internal/infrastructure/postgres/uow.go

type PostgresUnitOfWork struct {
    db *sql.DB   // Connection pool for starting TX
    tx *sql.Tx   // The active transaction (nil if not started)
}

func (u *PostgresUnitOfWork) Begin(ctx context.Context) (domain.UnitOfWork, error) {
    tx, err := u.db.BeginTx(ctx, nil)
    if err != nil {
        return nil, err
    }
    // Return a NEW instance of UoW that holds this specific TX
    return &PostgresUnitOfWork{
        db: u.db,
        tx: tx,
    }, nil
}

func (u *PostgresUnitOfWork) Commit() error {
    return u.tx.Commit()
}

func (u *PostgresUnitOfWork) Rollback() error {
    return u.tx.Rollback()
}

// Factory method to get a repo that uses THIS transaction
func (u *PostgresUnitOfWork) Accounts() domain.AccountRepository {
    return NewAccountRepository(u.tx) // Inject the TX, not the DB pool
}

// internal/application/usecase/transfer.go

func (uc *TransferUseCase) Execute(ctx context.Context, input Input) error {
    // 1. Start the Transaction Boundary
    // The App Layer decides "This whole function is one atomic unit"
    txUoW, err := uc.uowFactory.Begin(ctx) 
    if err != nil { return err }
    defer txUoW.Rollback() // Safety net

    // 2. Use Repositories *attached* to that transaction
    sender, _ := txUoW.Accounts().Get(ctx, input.FromID)
    receiver, _ := txUoW.Accounts().Get(ctx, input.ToID)

    // 3. Domain Logic (The Domain Service we discussed)
    if err := uc.domainService.Transfer(sender, receiver, input.Amount); err != nil {
        return err // Rollback happens automatically via defer
    }

    // 4. Save State
    txUoW.Accounts().Save(ctx, sender)
    txUoW.Accounts().Save(ctx, receiver)
    
    // 5. Commit - The Point of No Return
    return txUoW.Commit()
}

Closure/callback

// internal/domain/repository/transactor.go

type TransactionManager interface {
    // The callback function receives the transactional context/repos
    RunInTransaction(ctx context.Context, fn func(ctx context.Context) error) error
}

// internal/infrastructure/postgres/transactor.go

func (tm *PostgresTransactionManager) RunInTransaction(ctx context.Context, fn func(ctx context.Context) error) error {
    tx, err := tm.db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }

    // Safety: Ensure Rollback happens if panic or error occurs before commit
    defer func() {
        if p := recover(); p != nil {
            tx.Rollback()
            panic(p) // Re-throw panic after rollback
        } else if err != nil {
            tx.Rollback() // Rollback on error
        } else {
            // Commit if everything went fine
            // Note: If Commit fails, we return that error
            err = tx.Commit() 
        }
    }()

    // Inject the transaction into the context so Repositories can find it
    txCtx := context.WithValue(ctx, txKey, tx)

    // RUN THE USER'S LOGIC
    err = fn(txCtx)
    return err
}

// internal/application/usecase/transfer.go

func (uc *TransferUseCase) Execute(ctx context.Context, req TransferRequest) error {
    // You just define "What to do", not "How to manage the TX"
    return uc.txManager.RunInTransaction(ctx, func(txCtx context.Context) error {
        
        // Inside here, 'txCtx' contains the active transaction
        
        // 1. Get Sender (Repo checks context for TX)
        sender, err := uc.accountRepo.GetByID(txCtx, req.SenderID)
        if err != nil { return err } // Triggers Rollback automatically

        // 2. Get Receiver
        receiver, err := uc.accountRepo.GetByID(txCtx, req.ReceiverID)
        if err != nil { return err } // Triggers Rollback automatically

        // 3. Domain Logic
        if err := sender.TransferTo(receiver, req.Amount); err != nil {
            return err // Triggers Rollback automatically
        }

        // 4. Save
        if err := uc.accountRepo.Save(txCtx, sender); err != nil { return err }
        if err := uc.accountRepo.Save(txCtx, receiver); err != nil { return err }

        return nil // Triggers Commit automatically
    })
}

[KevinNitroG]

Unit of work is good

// 1. Domain Service
type AssignmentService struct {
    taskRepo TaskRepository
}

func (s *AssignmentService) AssignBatch(user User, tasks []Task) error {
    for _, t := range tasks {
        if err := t.AssignTo(user); err != nil {
            return err
        }
    }
    return nil
}

// 2. Application Handler (The "Coordinator")
func (h *AssignHandler) Handle(cmd AssignCommand) error {
    return h.uow.Execute(ctx, func(repo Registry) error {
        user := repo.Users().Find(cmd.UserID)
        tasks := repo.Tasks().FindMany(cmd.TaskIDs)

        // The Domain Service handles the "how"
        if err := h.domainService.AssignBatch(user, tasks); err != nil {
            return err
        }

        // Save all changes in one DB transaction
        return repo.Tasks().SaveMany(tasks) 
    })
}

// internal/domain/common/uow.go
type UnitOfWork interface {
    // Execute wraps a function in a transaction
    Execute(ctx context.Context, fn func(repoRegistry RepositoryRegistry) error) error
}

// RepositoryRegistry gives access to all repos within that transaction
type RepositoryRegistry interface {
    Accounts() domain.AccountRepository
    // Add other repos here
}

// internal/infrastructure/persistence/postgres/uow.go
type PostgresUnitOfWork struct {
    db *sql.DB
}

func (u *PostgresUnitOfWork) Execute(ctx context.Context, fn func(domain.RepositoryRegistry) error) error {
    tx, err := u.db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }

    // Create a registry that uses the active transaction
    registry := &postgresRegistry{tx: tx}

    err = fn(registry)

    if err != nil {
        tx.Rollback()
        return err
    }

    return tx.Commit()
}

type postgresRegistry struct {
    tx *sql.Tx
}

func (r *postgresRegistry) Accounts() domain.AccountRepository {
    return NewAccountRepository(r.tx) // Pass the transaction to the repo
}

// internal/application/account/handlers.go
func (h *TransferHandler) Handle(ctx context.Context, cmd TransferCommand) error {
    return h.uow.Execute(ctx, func(repos domain.RepositoryRegistry) error {
        from := repos.Accounts().Get(cmd.FromID)
        to := repos.Accounts().Get(cmd.ToID)

        if err := from.Send(cmd.Amount, to); err != nil {
            return err
        }

        repos.Accounts().Save(from)
        repos.Accounts().Save(to)
        return nil
    })
}

We should have ability to get up multiple instances and save multiple instances. With that, we no longer need to worry... Actually we might want to use select for update lock db... Nah too much

[KevinNitroG]

Unit of work with domain service

func (uc *DeleteNoteUseCase) Execute(ctx context.Context, noteID uuid.UUID) error {
    // 1. Start the Transaction at the Repository level
    return uc.unitOfWork.Do(ctx, func(tx RepoContext) error {
        
        // 2. Load the Note
        note, _ := uc.noteRepo.GetByID(tx, noteID)
        tagIDs := note.TagIDs
        
        // 3. Perform the primary action (Hard Delete or Trash)
        uc.noteRepo.HardDelete(tx, noteID)

        // 4. Call Domain Service for cross-aggregate logic
        // We pass 'tx' so it stays in the same database session
        for _, tagID := range tagIDs {
            err := uc.tagOrphanService.CleanupIfOrphaned(tx, tagID)
            if err != nil {
                return err // Transaction will rollback automatically
            }
        }

        return nil // Transaction commits
    })
}

type NoteRepository interface {
    GetByID(tx RepoContext, id uuid.UUID) (*Note, error)
    CountAllExistingNotesByTag(tx RepoContext, tagID uuid.UUID) (int, error)
    HardDelete(tx RepoContext, id uuid.UUID) error
}

func (s *TagOrphanService) CleanupIfOrphaned(tx RepoContext, tagID uuid.UUID) error {
    count, _ := s.noteRepo.CountAllExistingNotesByTag(tx, tagID)

    if count == 0 {
        tag, _ := s.tagRepo.GetByID(tx, tagID)
        tag.MarkAsDeleted()
        return s.tagRepo.Save(tx, tag)
    }
    return nil
}