企业官网建设流程全解析

Go Gin + SQL 全栈：CRUD、事务、迁移、监控全攻略

Gin + SQL 是后端项目最常见的搭配。本文带你看一眼真实项目的"完整闭环"。

一、项目布局

cmd/server internal/{handler, service, repo} internal/config pkg/types configs/config.yaml

二、main.go

funcmain(){cfg,_:=config.Load()db,_:=sql.Open("mysql",cfg.DB.DSN)db.SetMaxOpenConns(50)db.SetMaxIdleConns(10)deferdb.Close()userRepo:=repo.NewUserRepo(db)userSvc:=service.NewUserService(userRepo)handler:=handler.NewUserHandler(userSvc)r:=gin.Default()handler.Register(r)log.Println("server listens on :8080")http.ListenAndServe(":8080",r)}

三、配置加载

typeConfigstruct{DBstruct{DSNstringMaxint}PortstringAppEnvstring}funcLoad()(*Config,error){viper.SetConfigName("config")viper.AddConfigPath("./configs")err:=viper.ReadInConfig()varc Configreturn&c,err}

四、Repo 层

typeUserRepointerface{Find(idint)(User,error);Create(u User)(int64,error)}typeuserRepostruct{db*sql.DB}funcNewUserRepo(db*sql.DB)UserRepo{return&userRepo{db:db}}func(r*userRepo)Find(idint)(User,error){varu User err:=r.db.QueryRow("SELECT id, name FROM users WHERE id=?",id).Scan(&u.ID,&u.Name)returnu,err}func(r*userRepo)Create(u User)(int64,error){res,err:=r.db.Exec("INSERT INTO users(name) VALUES(?)",u.Name)iferr!=nil{return0,err}returnres.LastInsertId()}

五、Service 层

typeuserServicestruct{repo UserRepo}func(s*userService)CreateUser(ctx context.Context,namestring)(int64,error){ifname==""{return0,ErrEmptyName}returns.repo.Create(User{Name:name})}

六、Handler 层

func(h*UserHandler)Register(r*gin.Engine){r.GET("/users/:id",h.Get)r.POST("/users",h.Post)}func(h*UserHandler)Get(c*gin.Context){id,_:=strconv.Atoi(c.Param("id"))u,err:=h.svc.Find(c.Request.Context(),id)iferr!=nil{c.JSON(http.StatusNotFound,gin.H{"err":"not found"});return}c.JSON(http.StatusOK,u)}func(h*UserHandler)Post(c*gin.Context){varreqstruct{Namestring`json:"name"`}iferr:=c.ShouldBindJSON(&req);err!=nil{c.JSON(400,gin.H{"err":err.Error()});return}id,err:=h.svc.CreateUser(c.Request.Context(),req.Name)iferr!=nil{c.JSON(500,gin.H{"err":err.Error()});return}c.JSON(200,gin.H{"id":id})}

七、事务与一致性

func(s*userService)TransferMoney(ctx context.Context,from,toint,amountint64)error{tx,err:=s.db.BeginTx(ctx,nil)iferr!=nil{returnerr}defertx.Rollback()if_,err:=tx.Exec("UPDATE balances SET amount=amount-? WHERE user=?",amount,from);err!=nil{returnerr}if_,err:=tx.Exec("UPDATE balances SET amount=amount+? WHERE user=?",amount,to);err!=nil{returnerr}returntx.Commit()}

八、迁移 + 版本管理

migrate create-extsql-dirmigrations-seqadd_users migrate-pathmigrations-database"mysql://user:pwd@host:3306/app"up

九、监控

import"github.com/prometheus/client_golang/prometheus/promhttp"http.Handle("/metrics",promhttp.Handler())

业务层用中间件记录 QPS、error_rate：

r.Use(func(c*gin.Context){deferfunc(){requestCount.WithLabelValues(c.Request.Method,c.FullPath(),strconv.Itoa(c.Writer.Status())).Inc()}()c.Next()})

十、链路追踪

import"go.opentelemetry.io/contrib/instrumentation/github.com/gin-gonic/gin/otelgin"r.Use(otelgin.Middleware("user-svc"))

业务也加上：

ctx,span:=tracer.Start(ctx,"service.CreateUser")deferspan.End()

十一、中间件：限流

import"golang.org/x/time/rate"limiter:=rate.NewLimiter(10,20)r.Use(func(c*gin.Context){if!limiter.Allow(){c.AbortWithStatusJSON(429,gin.H{"err":"rate limited"})return}c.Next()})

十二、CI/CD

test:script:-go test ./...-racebuild:script:-go build-o app .deploy:script:-kubectl apply-f deployment.yaml

十三、踩坑清单

1. 忘记读取 ctx：所有 repo / service 方法必带 ctx
2. 业务事务跨多个流程 → 改 saga
3. json.bigint 精度丢失

十四、未来

• Serverless 部署
• 一体化可观测平台

十五、总结与展望

三层架构 + Gin + SQL 是 Go 后端的金标准。掌握它能为以后快速搭建业务铺路。

十六、参考文献

• Gin 文档
• Go database/sql 标准库
• sqlmigrate