AI 中文社网站切换到 GORM 之前，测试原生 SQL、SQLX、GORM 性能

package main

import (
	"database/sql"
	"fmt"
	"testing"

	"github.com/jmoiron/sqlx"
	_ "github.com/lib/pq"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
)

// go test -bench=. -benchmem -run=none
var (
	dsn = "postgres://postgres:123456@localhost:5432/demo?sslmode=disable"
)

type Person struct {
	Id        int    `db:"id"`
	FirstName string `db:"first_name"`
	LastName  string `db:"last_name"`
	Email     string `db:"email"`
}

func (p Person) TableName() string {
	return "person"
}

func Benchmark(b *testing.B) {
	limits := []int{
		10,
		100,
		1000,
		10000,
	}

	// 原生 database/sql 连接池
	sqlDB, _ := sql.Open("postgres", dsn)
	sqlDB.SetMaxOpenConns(500)
	sqlDB.SetMaxIdleConns(100)

	// jmoiron/sqlx 连接池
	sqlxDB, _ := sqlx.Connect("postgres", dsn)
	sqlxDB.SetMaxOpenConns(500)
	sqlxDB.SetMaxIdleConns(100)

	// gorm.io/gorm
	gormDB, _ := gorm.Open(postgres.Open(dsn), &gorm.Config{
		SkipDefaultTransaction: true,
		PrepareStmt:            true,
		DisableAutomaticPing:   true,
	})
	db, _ := gormDB.DB()
	db.SetMaxOpenConns(500)
	db.SetMaxIdleConns(100)

	for _, lim := range limits {
		// Benchmark database/sql
		b.Run(fmt.Sprintf("database/sql limit:%d", lim), func(b *testing.B) {
			var res []Person
			stmt, _ := sqlDB.Prepare("SELECT id,first_name,last_name,email FROM person LIMIT $1")
			for i := 0; i < b.N; i++ {
				rows, err := stmt.Query(lim)
				if err != nil {
					b.Fatal(err)
				}
				defer rows.Close()

				for rows.Next() {
					p := Person{}
					rows.Scan(&p.Id, &p.FirstName, &p.LastName, &p.Email)
					res = append(res, p)
				}
			}
		})

		// Benchmark jmoiron/sqlx
		b.Run(fmt.Sprintf("jmoiron/sqlx limit:%d", lim), func(b *testing.B) {
			stmt, _ := sqlxDB.Preparex("SELECT id,first_name,last_name,email FROM person LIMIT $1")
			for i := 0; i < b.N; i++ {
				var res []Person
				err := stmt.Select(&res, lim)
				if err != nil {
					b.Fatal(err)
				}
			}
		})

		// Benchmark gorm.io/gorm
		b.Run(fmt.Sprintf("gorm.io/gorm limit:%d", lim), func(b *testing.B) {
			for i := 0; i < b.N; i++ {
				var res []Person
				err := gormDB.Limit(lim).Find(&res).Error
				if err != nil {
					b.Fatal(err)
				}
			}
		})

		fmt.Println("#######################################")
	}
}