Go语言中跨包共享SQL数据库连接的惯用方法与最佳实践

package main

import (
    "database/sql"
    "fmt"
    "log"
    "myproject/repository" // 假设存在一个名为repository的包
    _ "github.com/mattn/go-sqlite3" // 导入SQLite驱动
)

// 定义一个全局变量，但在实际应用中更推荐通过参数传递
// var Db *sql.DB // 尽管可行，但通过参数传递是更好的实践

func main() {
    var err error
    // 初始化数据库连接池
    db, err := sql.Open("sqlite3", "./foo.db")
    if err != nil {
        log.Fatalf("Error opening database: %v", err)
    }
    // 确保在程序退出时关闭数据库连接
    defer func() {
        if closeErr := db.Close(); closeErr != nil {
            log.Printf("Error closing database: %v", closeErr)
        }
    }()

    // 检查数据库连接是否正常
    if pingErr := db.Ping(); pingErr != nil {
        log.Fatalf("Error connecting to the database: %v", pingErr)
    }
    fmt.Println("Database connection established successfully.")

    // 创建一个UserRepository实例，并将db传递给它
    userRepo := repository.NewUserRepository(db)

    // 使用userRepo进行数据库操作
    // 例如：userRepo.CreateUser(...)
    // 例如：user, err := userRepo.GetUserByID(1)
    // if err == nil {
    //     fmt.Printf("Fetched user: %+v\n", user)
    // } else {
    //     fmt.Printf("Error fetching user: %v\n", err)
    // }

    fmt.Println("Application running...")
    // 应用程序的其他逻辑
}

package repository

import (
    "database/sql"
    "fmt"
)

// User represents a user in the database
type User struct {
    ID   int
    Name string
    Email string
}

// UserRepository provides methods for interacting with user data
type UserRepository struct {
    db *sql.DB
}

// NewUserRepository creates a new UserRepository instance
func NewUserRepository(db *sql.DB) *UserRepository {
    return &UserRepository{db: db}
}

// GetUserByID fetches a user by their ID
func (r *UserRepository) GetUserByID(id int) (*User, error) {
    row := r.db.QueryRow("SELECT id, name, email FROM users WHERE id = ?", id)

    user := &User{}
    err := row.Scan(&user.ID, &user.Name, &user.Email)
    if err != nil {
        if err == sql.ErrNoRows {
            return nil, fmt.Errorf("user with ID %d not found", id)
        }
        return nil, fmt.Errorf("failed to scan user: %w", err)
    }
    return user, nil
}

// CreateUser inserts a new user into the database
func (r *UserRepository) CreateUser(user *User) error {
    stmt, err := r.db.Prepare("INSERT INTO users(name, email) VALUES(?, ?)")
    if err != nil {
        return fmt.Errorf("failed to prepare statement: %w", err)
    }
    defer stmt.Close()

    res, err := stmt.Exec(user.Name, user.Email)
    if err != nil {
        return fmt.Errorf("failed to execute insert: %w", err)
    }

    lastID, err := res.LastInsertId()
    if err != nil {
        return fmt.Errorf("failed to get last insert ID: %w", err)
    }
    user.ID = int(lastID) // 更新用户ID
    return nil
}