Go Fiber RESTAPI中PostgreSQL Prepare语句触发空指针panic问题

我正在开发基于Fiber的Go REST API，调用PostgreSQL插入数据的Prepare语句时触发了空指针引用panic，错误栈如下：

panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x20 pc=0x90b6f3]

goroutine 66 [running]:
database/sql.(*DB).conn(0x0, {0xb0e920, 0xc0001c6048}, 0x1)
    /usr/local/go/src/database/sql/sql.go:1290 +0x53
database/sql.(*DB).prepare(0xc00013e680?, {0xb0e920, 0xc0001c6048}, {0xa4ae4a, 0x9d}, 0x75?)
    /usr/local/go/src/database/sql/sql.go:1588 +0x45
database/sql.(*DB).PrepareContext(0xb0b360?, {0xb0e920, 0xc0001c6048}, {0xa4ae4a, 0x9d})
    /usr/local/go/src/database/sql/sql.go:1557 +0x98
database/sql.(*DB).Prepare(...)
    /usr/local/go/src/database/sql/sql.go:1578
github.com/davidshare/property-website-backend/repository/postgres.(*User).CreateUser(0xc0000b2000)
    /home/davidshare/go/src/github.com/davidshare/property-website-backend/repository/postgres/user.go:27 +0xbd
github.com/davidshare/property-website-backend/service.CreateUser(0xc0000b2000)
    /home/davidshare/go/src/github.com/davidshare/property-website-backend/service/user.go:15 +0xa7
github.com/davidshare/property-website-backend/controllers.CreateUser(0xc0001d9080)
    /home/davidshare/go/src/github.com/davidshare/property-website-backend/controllers/user.go:37 +0x351
github.com/gofiber/fiber/v2.(*App).next(0xc0017da5a0, 0xc0001d9080)
    /home/davidshare/go/pkg/mod/github.com/gofiber/fiber/v2@v2.34.1/router.go:132 +0x1be
github.com/gofiber/fiber/v2.(*Ctx).Next(0x40ef45?)
    /home/davidshare/go/pkg/mod/github.com/gofiber/fiber/v2@v2.34.1/ctx.go:802 +0x53
github.com/gofiber/fiber/v2/middleware/logger.New.func2(0xc0001d9080)
    /home/davidshare/go/pkg/mod/github.com/gofiber/fiber/v2@v2.34.1/middleware/logger/logger.go:160 +0x1f4
github.com/gofiber/fiber/v2.(*App).next(0xc0017da5a0, 0xc0001d9080)
    /home/davidshare/go/pkg/mod/github.com/gofiber/fiber/v2@v2.34.1/router.go:132 +0x1be
github.com/gofiber/fiber/v2.(*App).handler(0xc0017da5a0, 0x4d24f7?)
    /home/davidshare/go/pkg/mod/github.com/gofiber/fiber/v2@v2.34.1/router.go:159 +0x45
github.com/valyala/fasthttp.(*Server).serveConn(0xc00185a6c0, {0xb104a0?, 0xc000010028})
    /home/davidshare/go/pkg/mod/github.com/valyala/fasthttp@v1.37.0/server.go:2338 +0x1268
github.com/valyala/fasthttp.(*workerPool).workerFunc(0xc001855e00, 0xc00010e060)
    /home/davidshare/go/pkg/mod/github.com/valyala/fasthttp@v1.37.0/workerpool.go:224 +0xa9
github.com/valyala/fasthttp.(*workerPool).getCh.func1()
    /home/davidshare/go/pkg/mod/github.com/valyala/fasthttp@v1.37.0/workerpool.go:196 +0x38
created by github.com/valyala/fasthttp.(*workerPool).getCh
    /home/davidshare/go/pkg/mod/github.com/valyala/fasthttp@v1.37.0/workerpool.go:195 +0x1b0

数据库连接初始化代码 #

package postgres

import (
    "database/sql"
    "fmt"

    "github.com/davidshare/property-website-backend/config"
    _ "github.com/lib/pq"
)

var (
    DbClient *sql.DB
)

func Init() {
    fmt.Println("we got here")
    config := config.GetConfig()
    connectionString := fmt.Sprintf("host=%s port=%d user=%s "+"password=%s dbname=%s sslmode=disable",
        config.DB.Host, config.DB.Port, config.DB.Username, config.DB.Password, config.DB.Name)

    var err error
    DbClient, err = sql.Open("postgres", connectionString)
    if err != nil {
        panic(err)
    }
    defer DbClient.Close()
    fmt.Println((DbClient))
    fmt.Println("Successfully connected to database!")
}

触发错误的CreateUser代码 #

func (user *User) CreateUser() (int, error) {
    fmt.Println(user)

    stmt, err := db.DbClient.Prepare("INSERT INTO public.users (firstname,middlename,lastname,phonenumber,email,address,passport,role,password,created_at) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10);")
    print("got here in the repo")
    if err != nil {
        return user.ID, err
    }

    //closing the statement to prevent memory leaks
    defer stmt.Close()
    _, err = stmt.Exec(user.Firstname, user.Middlename, user.Lastname, user.Phonenumber, user.Email, user.Address, user.Passport, user.Role, user.Password, time.Now().Format("2017-09-07 17:06:06 Wednesday"))

    if err != nil {
        return user.ID, err
    }

    return user.ID, nil
}

问题根源与修复方案 #

核心问题 #

Init函数中使用了defer DbClient.Close()，这会导致Init函数执行完毕后立即关闭数据库连接，全局变量DbClient虽然不是nil，但已经是一个被关闭的无效连接，后续调用Prepare时会触发空指针panic（底层连接池已被销毁，尝试获取连接时访问了空地址）。

修复步骤 #

1. 移除Init中的defer关闭语句，将数据库连接的关闭逻辑移到应用入口（比如main函数），在应用退出时再关闭：

   // main.go示例
   func main() {
       postgres.Init()
       defer postgres.DbClient.Close()
   
       // 初始化Fiber应用并启动服务
       app := fiber.New()
       // ...路由配置
       log.Fatal(app.Listen(":3000"))
   }
   

2. 增加连接有效性检查，sql.Open只是创建连接池，不会实际测试连接，在Init中调用Ping()确保连接成功：

   func Init() {
       fmt.Println("we got here")
       config := config.GetConfig()
       connectionString := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable",
           config.DB.Host, config.DB.Port, config.DB.Username, config.DB.Password, config.DB.Name)
   
       var err error
       DbClient, err = sql.Open("postgres", connectionString)
       if err != nil {
           panic(err)
       }
       // 新增：检查连接有效性
       if err = DbClient.Ping(); err != nil {
           panic(err)
       }
       fmt.Println(DbClient)
       fmt.Println("Successfully connected to database!")
   }
   

额外优化建议 #

• 避免使用全局DbClient，改用依赖注入的方式将连接传递到repository层，更利于单元测试和代码维护。
• 不要在每次CreateUser调用时都重新Prepare语句，可以在repository初始化时提前Prepare并复用，提升性能。

内容的提问来源于stack exchange，提问作者David Essien