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
    })
}