Comprehensive comparison for Database technology in Software Development applications

package main

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"log"
	"net/http"
	"time"

	_ "github.com/lib/pq"
)

// Order represents an e-commerce order with inventory management
type Order struct {
	ID          string    `json:"id"`
	UserID      string    `json:"user_id"`
	ProductID   string    `json:"product_id"`
	Quantity    int       `json:"quantity"`
	TotalPrice  float64   `json:"total_price"`
	Status      string    `json:"status"`
	CreatedAt   time.Time `json:"created_at"`
}

// OrderService handles order creation with inventory checks
type OrderService struct {
	db *sql.DB
}

// CreateOrder creates a new order with atomic inventory deduction
// This demonstrates CockroachDB's distributed transaction capabilities
func (s *OrderService) CreateOrder(ctx context.Context, userID, productID string, quantity int) (*Order, error) {
	// Begin transaction with serializable isolation for consistency
	tx, err := s.db.BeginTx(ctx, &sql.TxOptions{
		Isolation: sql.LevelSerializable,
	})
	if err != nil {
		return nil, fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer tx.Rollback()

	// Check product availability with SELECT FOR UPDATE (pessimistic locking)
	var availableStock int
	var price float64
	err = tx.QueryRowContext(ctx,
		`SELECT stock_quantity, price FROM products WHERE id = $1 FOR UPDATE`,
		productID,
	).Scan(&availableStock, &price)

	if err == sql.ErrNoRows {
		return nil, fmt.Errorf("product not found")
	}
	if err != nil {
		return nil, fmt.Errorf("failed to check inventory: %w", err)
	}

	// Validate sufficient inventory
	if availableStock < quantity {
		return nil, fmt.Errorf("insufficient inventory: available=%d, requested=%d", availableStock, quantity)
	}

	// Deduct inventory atomically
	_, err = tx.ExecContext(ctx,
		`UPDATE products SET stock_quantity = stock_quantity - $1, updated_at = NOW() WHERE id = $2`,
		quantity, productID,
	)
	if err != nil {
		return nil, fmt.Errorf("failed to update inventory: %w", err)
	}

	// Create order record with generated UUID
	order := &Order{
		UserID:     userID,
		ProductID:  productID,
		Quantity:   quantity,
		TotalPrice: price * float64(quantity),
		Status:     "pending",
	}

	err = tx.QueryRowContext(ctx,
		`INSERT INTO orders (id, user_id, product_id, quantity, total_price, status, created_at) 
		 VALUES (gen_random_uuid(), $1, $2, $3, $4, $5, NOW()) 
		 RETURNING id, created_at`,
		order.UserID, order.ProductID, order.Quantity, order.TotalPrice, order.Status,
	).Scan(&order.ID, &order.CreatedAt)

	if err != nil {
		return nil, fmt.Errorf("failed to create order: %w", err)
	}

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

	return order, nil
}

// HTTP handler for order creation endpoint
func (s *OrderService) HandleCreateOrder(w http.ResponseWriter, r *http.Request) {
	if r.Method != http.MethodPost {
		http.Error(w, "Method not allowed", http.StatusMethodNotAllowed)
		return
	}

	var req struct {
		UserID    string `json:"user_id"`
		ProductID string `json:"product_id"`
		Quantity  int    `json:"quantity"`
	}

	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		http.Error(w, "Invalid request body", http.StatusBadRequest)
		return
	}

	// Create context with timeout for database operations
	ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
	defer cancel()

	order, err := s.CreateOrder(ctx, req.UserID, req.ProductID, req.Quantity)
	if err != nil {
		log.Printf("Order creation failed: %v", err)
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

	w.Header().Set("Content-Type", "application/json")
	w.WriteHeader(http.StatusCreated)
	json.NewEncoder(w).Encode(order)
}

func main() {
	// Connection string with recommended CockroachDB parameters
	connStr := "postgresql://user:password@localhost:26257/ecommerce?sslmode=require&application_name=order_service"
	db, err := sql.Open("postgres", connStr)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// Configure connection pool for production
	db.SetMaxOpenConns(25)
	db.SetMaxIdleConns(5)
	db.SetConnMaxLifetime(5 * time.Minute)

	service := &OrderService{db: db}
	http.HandleFunc("/orders", service.HandleCreateOrder)

	log.Println("Order service running on :8080")
	log.Fatal(http.ListenAndServe(":8080", nil))
}