Golang CRUD应用Postman请求报错Socket hang up及数据库连接拒绝问题

问题现象 #

按照教程构建Golang CRUD应用后，通过PgAdmin成功连接PostgreSQL并创建了表，用Docker Compose启动所有服务后，Postman请求返回Error: socket hang up，应用服务器日志报错：dial tcp 127.0.0.1:5432: connect: connection refused。

核心文件 #

docker-compose.yml #

version: '3'

services:
  dicane-service:
    build:
      context: ./../dicane-service
      dockerfile: ./../dicane-service/dicane-service.dockerfile
    restart: always
    ports:
      - "8000:8000"
    deploy:
      mode: replicated
      replicas: 1
    environment:
      DSN: "host=postgres port=5432 user=postgres password=password dbname=postgres sslmode=disable timezone=UTC connect_timeout=5"
    depends_on:
      - postgres
      
  postgres:
    image: 'postgres:14.0'
    ports:
      - "5432:5432"
    restart: always
    deploy:
      mode: replicated
      replicas: 1
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: password
      POSTGRES_DB: postgres
    volumes:
      - ./db-data/postgres/:/var/lib/postgresql/data/
  
  pg-admin:
    image: dpage/pgadmin4:7
    environment:
      - PGADMIN_DEFAULT_EMAIL=igorlacourt@gmail.com
      - PGADMIN_DEFAULT_PASSWORD=password
      - PGADMIN_LISTEN_PORT=5050
    ports:
      - "5050:5050"

main.go #

package main

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

    "github.com/gorilla/mux"
    _ "github.com/lib/pq"
)

type Sale struct {
    Sale_id                    string    `json:"sale_id"`
    // 省略其他字段...
    Images                     []string  `json:"images"`
}

func main() {
    // connect to database
    db, err := sql.Open("postgres", os.Getenv("DATABASE_URL"))
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    //create router
    router := mux.NewRouter()
    router.HandleFunc("/sales", getSales(db)).Methods("GET")
    router.HandleFunc("/sales/{id}", getSale(db)).Methods("GET")
    router.HandleFunc("/sales", createSale(db)).Methods("POST")
    router.HandleFunc("/sales/{id}", updateSale(db)).Methods("PUT")
    router.HandleFunc("/sales/{id}", deleteSale(db)).Methods("DELETE")

    // start server
    log.Fatal(http.ListenAndServe(":8000", jsonContentTypeMiddleware(router)))

}

func jsonContentTypeMiddleware(next http.Handler) http.Handler {
    return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
        w.Header().Set("Content-Type", "application/json")
        next.ServeHTTP(w, r)
    })
}

func getSales(db *sql.DB) http.HandlerFunc {
    return func(w http.ResponseWriter, r *http.Request) {
        rows, err := db.Query("SELECT * FROM sales")
        if err != nil {
            log.Fatal(err)
        }
        defer rows.Close()

        sales := []Sale{}
        for rows.Next() {
            var sale Sale
            if err := rows.Scan(
                &sale.Sale_id,
                // 省略其他字段扫描...
                &sale.Images,
            ); err != nil {
                log.Fatal(err)
            }
            sales = append(sales, sale)
        }
        if err := rows.Err(); err != nil {
            log.Fatal(err)
        }

        json.NewEncoder(w).Encode(sales)
    }
}

func getSale(db *sql.DB) http.HandlerFunc {
    print("getSale")
    return func(w http.ResponseWriter, r *http.Request) {
        vars := mux.Vars(r)
        id := vars["sale_id"]

        var sale Sale
        err := db.QueryRow("SELECT * FROM sales WHERE sale_id = $1", id).Scan(
            &sale.Sale_id,
            // 省略其他字段扫描...
            &sale.Images,
        )
        if err != nil {
            log.Fatal(err)
        }

        json.NewEncoder(w).Encode(sale)
    }
}

func createSale(db *sql.DB) http.HandlerFunc {
    return func(w http.ResponseWriter, r *http.Request) {
        var sale Sale
        json.NewDecoder(r.Body).Decode(&sale)

        err := db.QueryRow("INSERT INTO sales ("+
            // 省略其他字段...
            " images)"+
            " VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $21, $22, $23)"+
            " RETURNING sale_id",
            // 省略其他参数...
            ).Scan(&sale.Sale_id)
        if err != nil {
            log.Fatal(err)
        }
        json.NewEncoder(w).Encode(sale)
    }
}

func updateSale(db *sql.DB) http.HandlerFunc {
    return func(w http.ResponseWriter, r *http.Request) {
        var sale Sale
        json.NewDecoder(r.Body).Decode(&sale)

        vars := mux.Vars(r)
        saleId := vars["sale_id"]

        _, err := db.Exec("UPDATE sales SET"+
            // 省略其他字段设置...
            )+"WHERE id = $24",
            // 省略其他参数...
            saleId,
        )
        if err != nil {
            log.Fatal(err)
        }

        json.NewEncoder(w).Encode(sale)
    }
}

func deleteSale(db *sql.DB) http.HandlerFunc {
    return func(w http.ResponseWriter, r *http.Request) {
        vars := mux.Vars(r)
        saleId := vars["sale_id"]

        var sale Sale
        err := db.QueryRow("SELECT * FROM sales WHERE sale_id = $1", saleId).Scan(
            &sale.Sale_id,
            // 省略其他字段扫描...
            &sale.Images,
        )
        if err != nil {
            w.WriteHeader(http.StatusNotFound)
            return
        } else {
            _, err := db.Exec("DELETE FROM sales WHERE sale_id = $1", saleId)
            if err != nil {
                //todo : fix error handling
                w.WriteHeader(http.StatusNotFound)
                return
            }

            json.NewEncoder(w).Encode("User deleted")
        }
    }
}

问题修复步骤 #

1. 修正环境变量不匹配问题 #

docker-compose.yml中配置的环境变量是DSN，但main.go里读取的是DATABASE_URL，导致应用无法获取正确的数据库连接字符串。

方案一：修改docker-compose.yml
将DSN改为DATABASE_URL：

environment:
  DATABASE_URL: "host=postgres port=5432 user=postgres password=password dbname=postgres sslmode=disable timezone=UTC connect_timeout=5"

方案二：修改main.go
将读取环境变量的代码改为读取DSN：

db, err := sql.Open("postgres", os.Getenv("DSN"))

2. 添加数据库连接校验 #

sql.Open仅创建连接池，不会立即验证数据库连接是否可用，需要在启动时主动校验：

// connect to database
db, err := sql.Open("postgres", os.Getenv("DATABASE_URL"))
if err != nil {
    log.Fatal(err)
}
// 添加连接校验
if err := db.Ping(); err != nil {
    log.Fatalf("failed to connect to database: %v", err)
}
defer db.Close()

3. 解决PostgreSQL就绪延迟问题 #

depends_on仅保证postgres服务先启动，但不保证数据库完全就绪。可以在main.go中添加连接重试逻辑：

// 连接重试逻辑
var db *sql.DB
var err error
for i := 0; i < 5; i++ {
    db, err = sql.Open("postgres", os.Getenv("DATABASE_URL"))
    if err == nil {
        if err := db.Ping(); err == nil {
            break
        }
    }
    log.Printf("retrying database connection... attempt %d", i+1)
    time.Sleep(2 * time.Second)
}
if err != nil || db == nil {
    log.Fatalf("failed to connect to database after 5 attempts: %v", err)
}
defer db.Close()

4. 修正路由参数错误 #

路由定义为/sales/{id}，但代码中尝试获取sale_id参数，导致无法正确获取请求ID：

// getSale函数中修正参数获取
vars := mux.Vars(r)
id := vars["id"] // 原代码是vars["sale_id"]

// updateSale和deleteSale函数同理，将vars["sale_id"]改为vars["id"]

5. 优化错误处理避免服务崩溃 #

使用log.Fatal(err)会直接终止进程，应该返回HTTP错误响应给客户端：

// 以getSales为例修改错误处理
rows, err := db.Query("SELECT * FROM sales")
if err != nil {
    log.Printf("failed to query sales: %v", err)
    http.Error(w, "internal server error", http.StatusInternalServerError)
    return
}

内容的提问来源于stack exchange，提问作者Igor Grecco Lacourt