Go语言数据入库PostgreSQL（schema： golang）全面指南
本文详细介绍了Go语言与PostgreSQL数据库（golang schema）的集成方案，采用分层架构设计（models、repositories、services），使用pgx连接池实现高效数据访问，涵盖用户认证、订单管理等完整CRUD操作，包含事务处理、密码加密、分页查询等企业级功能，并提供了从环境配置到API实现的完整代码示例，适用于高并发Web服务、微服务架构及需要强数据一致性的企业级应用场景。
一、适用场景
Go语言与PostgreSQL的组合适用于以下场景：
- 高并发Web服务：Go的并发模型与PostgreSQL的连接池结合，可处理高流量应用
- 微服务架构：作为独立服务的数据持久层，便于服务解耦
- 实时数据处理：处理IoT设备数据、实时交易系统等
- 数据分析平台：利用PostgreSQL强大的分析能力进行数据挖掘
- 企业级应用：需要ACID事务保障的财务、订单系统
- 云原生应用：容器化部署的数据库操作服务
- API后端：为移动应用、单页应用提供数据接口
二、环境准备
1. PostgreSQL创建schema
CREATE SCHEMA golang;
CREATE TABLE golang.users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
password_hash VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE golang.orders (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES golang.users(id),
amount DECIMAL(10,2) NOT NULL,
status VARCHAR(20) DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
2. 依赖安装
go get github.com/jackc/pgx/v5
go get github.com/jackc/pgx/v5/pgxpool
go get github.com/joho/godotenv
go get golang.org/x/crypto/bcrypt
三、代码结构设计
go-PostgreSQL-demo/
├── config/
│ └── database.go # 数据库配置与连接
├── models/
│ ├── user.go # 用户模型
│ └── order.go # 订单模型
├── repositories/
│ ├── user_repository.go # 用户数据访问
│ └── order_repository.go # 订单数据访问
├── services/
│ ├── user_service.go # 业务逻辑
│ └── order_service.go
├── utils/
│ └── password.go # 工具函数
├── .env # 环境变量
└── main.go # 应用入口
四、详细代码实现
1. config/database.go
package config
import (
"context"
"fmt"
"github.com/jackc/pgx/v5/pgxpool"
"github.com/joho/godotenv"
"log"
"os"
"time"
)
var dbPool *pgxpool.Pool
// InitDB 初始化数据库连接池
func InitDB() {
// 加载环境变量
err := godotenv.Load()
if err != nil {
log.Println("警告：.env文件未找到，尝试使用系统环境变量")
}
// 从环境变量获取配置
dbHost := os.Getenv("DB_HOST")
dbPort := os.Getenv("DB_PORT")
dbUser := os.Getenv("DB_USER")
dbPassword := os.Getenv("DB_PASSWORD")
dbName := os.Getenv("DB_NAME")
dbSchema := os.Getenv("DB_SCHEMA")
maxConns := os.Getenv("DB_MAX_CONNECTIONS")
// 构建连接字符串
connStr := fmt.Sprintf("postgres://%s:%s@%s:%s/%s?search_path=%s",
dbUser, dbPassword, dbHost, dbPort, dbName, dbSchema)
// 配置连接池
config, err := pgxpool.ParseConfig(connStr)
if err != nil {
log.Fatalf("解析数据库配置失败: %v", err)
}
// 设置连接池参数
if maxConns != "" {
config.MaxConns = 10 // 默认值
// 可以根据环境变量设置
}
config.MaxConnLifetime = 30 * time.Minute
config.MaxConnIdleTime = 10 * time.Minute
config.HealthCheckPeriod = 5 * time.Minute
config.ConnConfig.ConnectTimeout = 5 * time.Second
// 创建连接池
dbPool, err = pgxpool.NewWithConfig(context.Background(), config)
if err != nil {
log.Fatalf("连接数据库失败: %v", err)
}
// 测试连接
if err := dbPool.Ping(context.Background()); err != nil {
log.Fatalf("数据库连接测试失败: %v", err)
}
log.Println("成功连接到数据库")
}
// GetDB 返回数据库连接池
func GetDB() *pgxpool.Pool {
if dbPool == nil {
log.Fatal("数据库未初始化，请先调用InitDB()")
}
return dbPool
}
// CloseDB 关闭数据库连接
func CloseDB() {
if dbPool != nil {
dbPool.Close()
log.Println("数据库连接已关闭")
}
}
2. models/user.go
package models
import "time"
// User 用户模型
type User struct {
ID int `json:"id"`
Username string `json:"username" validate:"required,min=3,max=50"`
Email string `json:"email" validate:"required,email"`
PasswordHash string `json:"-"`
CreatedAt time.Time `json:"created_at"`
UpdatedAt time.Time `json:"updated_at"`
}
// CreateUserRequest 创建用户的请求结构
type CreateUserRequest struct {
Username string `json:"username" validate:"required,min=3,max=50"`
Email string `json:"email" validate:"required,email"`
Password string `json:"password" validate:"required,min=8"`
}
// UpdateUserRequest 更新用户的请求结构
type UpdateUserRequest struct {
Username string `json:"username,omitempty" validate:"min=3,max=50"`
Email string `json:"email,omitempty" validate:"email"`
}
// UserResponse 返回给客户端的用户信息
type UserResponse struct {
ID int `json:"id"`
Username string `json:"username"`
Email string `json:"email"`
CreatedAt time.Time `json:"created_at"`
UpdatedAt time.Time `json:"updated_at"`
}
3. models/order.go
package models
import "time"
// Order 订单模型
type Order struct {
ID int `json:"id"`
UserID int `json:"user_id"`
Amount float64 `json:"amount"`
Status string `json:"status"`
CreatedAt time.Time `json:"created_at"`
UpdatedAt time.Time `json:"updated_at"`
}
// CreateOrderRequest 创建订单的请求结构
type CreateOrderRequest struct {
UserID int `json:"user_id" validate:"required,gt=0"`
Amount float64 `json:"amount" validate:"required,gt=0"`
}
// OrderResponse 返回给客户端的订单信息
type OrderResponse struct {
ID int `json:"id"`
UserID int `json:"user_id"`
Username string `json:"username"`
Amount float64 `json:"amount"`
Status string `json:"status"`
CreatedAt time.Time `json:"created_at"`
UpdatedAt time.Time `json:"updated_at"`
}
4. utils/password.go
package utils
import (
"golang.org/x/crypto/bcrypt"
)
// HashPassword 生成密码哈希
func HashPassword(password string) (string, error) {
hashedPassword, err := bcrypt.GenerateFromPassword([]byte(password), bcrypt.DefaultCost)
if err != nil {
return "", err
}
return string(hashedPassword), nil
}
// CheckPasswordHash 验证密码
func CheckPasswordHash(password, hash string) bool {
err := bcrypt.CompareHashAndPassword([]byte(hash), []byte(password))
return err == nil
}
5. repositories/user_repository.go
package repositories
import (
"context"
"errors"
"fmt"
"github.com/jackc/pgx/v5"
"github.com/jackc/pgx/v5/pgconn"
"github.com/jackc/pgx/v5/pgxpool"
"go-PostgreSQL-demo/config"
"go-PostgreSQL-demo/models"
"log"
"time"
)
type UserRepository interface {
Create(ctx context.Context, user *models.User) error
GetByID(ctx context.Context, id int) (*models.User, error)
GetByEmail(ctx context.Context, email string) (*models.User, error)
Update(ctx context.Context, user *models.User) error
Delete(ctx context.Context, id int) error
List(ctx context.Context, limit, offset int) ([]*models.User, error)
Count(ctx context.Context) (int, error)
}
type userRepository struct {
db *pgxpool.Pool
}
func NewUserRepository() UserRepository {
return &userRepository{
db: config.GetDB(),
}
}
func (r *userRepository) Create(ctx context.Context, user *models.User) error {
query := `
INSERT INTO golang.users (username, email, password_hash, created_at, updated_at)
VALUES ($1, $2, $3, $4, $5)
RETURNING id
`
now := time.Now()
user.CreatedAt = now
user.UpdatedAt = now
err := r.db.QueryRow(ctx, query, user.Username, user.Email, user.PasswordHash, user.CreatedAt, user.UpdatedAt).Scan(&user.ID)
if err != nil {
var pgErr *pgconn.PgError
if errors.As(err, &pgErr) {
switch pgErr.Code {
case "23505": // 唯一约束冲突
if pgErr.ConstraintName == "users_username_key" {
return fmt.Errorf("用户名已存在")
}
if pgErr.ConstraintName == "users_email_key" {
return fmt.Errorf("邮箱已存在")
}
}
}
log.Printf("创建用户失败: %v", err)
return fmt.Errorf("创建用户失败")
}
return nil
}
func (r *userRepository) GetByID(ctx context.Context, id int) (*models.User, error) {
query := `
SELECT id, username, email, password_hash, created_at, updated_at
FROM golang.users
WHERE id = $1
`
user := &models.User{}
err := r.db.QueryRow(ctx, query, id).Scan(
&user.ID,
&user.Username,
&user.Email,
&user.PasswordHash,
&user.CreatedAt,
&user.UpdatedAt,
)
if errors.Is(err, pgx.ErrNoRows) {
return nil, fmt.Errorf("用户不存在")
}
if err != nil {
log.Printf("查询用户失败: %v", err)
return nil, fmt.Errorf("查询用户失败")
}
return user, nil
}
func (r *userRepository) GetByEmail(ctx context.Context, email string) (*models.User, error) {
query := `
SELECT id, username, email, password_hash, created_at, updated_at
FROM golang.users
WHERE email = $1
`
user := &models.User{}
err := r.db.QueryRow(ctx, query, email).Scan(
&user.ID,
&user.Username,
&user.Email,
&user.PasswordHash,
&user.CreatedAt,
&user.UpdatedAt,
)
if errors.Is(err, pgx.ErrNoRows) {
return nil, fmt.Errorf("用户不存在")
}
if err != nil {
log.Printf("查询用户失败: %v", err)
return nil, fmt.Errorf("查询用户失败")
}
return user, nil
}
func (r *userRepository) Update(ctx context.Context, user *models.User) error {
query := `
UPDATE golang.users
SET username = $1, email = $2, updated_at = $3
WHERE id = $4
RETURNING updated_at
`
now := time.Now()
var updatedAt time.Time
err := r.db.QueryRow(ctx, query, user.Username, user.Email, now, user.ID).Scan(&updatedAt)
if err != nil {
if errors.Is(err, pgx.ErrNoRows) {
return fmt.Errorf("用户不存在")
}
var pgErr *pgconn.PgError
if errors.As(err, &pgErr) && pgErr.Code == "23505" {
if pgErr.ConstraintName == "users_username_key" {
return fmt.Errorf("用户名已存在")
}
if pgErr.ConstraintName == "users_email_key" {
return fmt.Errorf("邮箱已存在")
}
}
log.Printf("更新用户失败: %v", err)
return fmt.Errorf("更新用户失败")
}
user.UpdatedAt = updatedAt
return nil
}
func (r *userRepository) Delete(ctx context.Context, id int) error {
query := `
DELETE FROM golang.users
WHERE id = $1
`
result, err := r.db.Exec(ctx, query, id)
if err != nil {
log.Printf("删除用户失败: %v", err)
return fmt.Errorf("删除用户失败")
}
if result.RowsAffected() == 0 {
return fmt.Errorf("用户不存在")
}
return nil
}
func (r *userRepository) List(ctx context.Context, limit, offset int) ([]*models.User, error) {
query := `
SELECT id, username, email, created_at, updated_at
FROM golang.users
ORDER BY created_at DESC
LIMIT $1 OFFSET $2
`
rows, err := r.db.Query(ctx, query, limit, offset)
if err != nil {
log.Printf("查询用户列表失败: %v", err)
return nil, fmt.Errorf("查询用户列表失败")
}
defer rows.Close()
var users []*models.User
for rows.Next() {
user := &models.User{}
err := rows.Scan(
&user.ID,
&user.Username,
&user.Email,
&user.CreatedAt,
&user.UpdatedAt,
)
if err != nil {
log.Printf("扫描用户数据失败: %v", err)
return nil, fmt.Errorf("获取用户数据失败")
}
users = append(users, user)
}
if err = rows.Err(); err != nil {
log.Printf("遍历用户列表时出错: %v", err)
return nil, fmt.Errorf("获取用户列表失败")
}
return users, nil
}
func (r *userRepository) Count(ctx context.Context) (int, error) {
query := `SELECT COUNT(*) FROM golang.users`
var count int
err := r.db.QueryRow(ctx, query).Scan(&count)
if err != nil {
log.Printf("获取用户总数失败: %v", err)
return 0, fmt.Errorf("获取用户总数失败")
}
return count, nil
}
6. repositories/order_repository.go
package repositories
import (
"context"
"errors"
"fmt"
"github.com/jackc/pgx/v5"
"github.com/jackc/pgx/v5/pgconn"
"github.com/jackc/pgx/v5/pgxpool"
"go-PostgreSQL-demo/config"
"go-PostgreSQL-demo/models"
"log"
"time"
)
type OrderRepository interface {
Create(ctx context.Context, order *models.Order) error
GetByID(ctx context.Context, id int) (*models.Order, error)
GetByUserID(ctx context.Context, userID int, limit, offset int) ([]*models.Order, error)
UpdateStatus(ctx context.Context, id int, status string) error
Delete(ctx context.Context, id int) error
List(ctx context.Context, limit, offset int) ([]*models.OrderResponse, error)
Count(ctx context.Context) (int, error)
}
type orderRepository struct {
db *pgxpool.Pool
}
func NewOrderRepository() OrderRepository {
return &orderRepository{
db: config.GetDB(),
}
}
func (r *orderRepository) Create(ctx context.Context, order *models.Order) error {
query := `
INSERT INTO golang.orders (user_id, amount, status, created_at, updated_at)
VALUES ($1, $2, $3, $4, $5)
RETURNING id, created_at, updated_at
`
now := time.Now()
order.CreatedAt = now
order.UpdatedAt = now
err := r.db.QueryRow(ctx, query, order.UserID, order.Amount, order.Status, order.CreatedAt, order.UpdatedAt).Scan(
&order.ID,
&order.CreatedAt,
&order.UpdatedAt,
)
if err != nil {
var pgErr *pgconn.PgError
if errors.As(err, &pgErr) && pgErr.Code == "23503" { // 外键约束
return fmt.Errorf("关联的用户不存在")
}
log.Printf("创建订单失败: %v", err)
return fmt.Errorf("创建订单失败")
}
return nil
}
func (r *orderRepository) GetByID(ctx context.Context, id int) (*models.Order, error) {
query := `
SELECT id, user_id, amount, status, created_at, updated_at
FROM golang.orders
WHERE id = $1
`
order := &models.Order{}
err := r.db.QueryRow(ctx, query, id).Scan(
&order.ID,
&order.UserID,
&order.Amount,
&order.Status,
&order.CreatedAt,
&order.UpdatedAt,
)
if errors.Is(err, pgx.ErrNoRows) {
return nil, fmt.Errorf("订单不存在")
}
if err != nil {
log.Printf("查询订单失败: %v", err)
return nil, fmt.Errorf("查询订单失败")
}
return order, nil
}
func (r *orderRepository) GetByUserID(ctx context.Context, userID int, limit, offset int) ([]*models.Order, error) {
query := `
SELECT id, user_id, amount, status, created_at, updated_at
FROM golang.orders
WHERE user_id = $1
ORDER BY created_at DESC
LIMIT $2 OFFSET $3
`
rows, err := r.db.Query(ctx, query, userID, limit, offset)
if err != nil {
log.Printf("查询用户订单失败: %v", err)
return nil, fmt.Errorf("查询用户订单失败")
}
defer rows.Close()
var orders []*models.Order
for rows.Next() {
order := &models.Order{}
err := rows.Scan(
&order.ID,
&order.UserID,
&order.Amount,
&order.Status,
&order.CreatedAt,
&order.UpdatedAt,
)
if err != nil {
log.Printf("扫描订单数据失败: %v", err)
return nil, fmt.Errorf("获取订单数据失败")
}
orders = append(orders, order)
}
if err = rows.Err(); err != nil {
log.Printf("遍历订单列表时出错: %v", err)
return nil, fmt.Errorf("获取订单列表失败")
}
return orders, nil
}
func (r *orderRepository) UpdateStatus(ctx context.Context, id int, status string) error {
query := `
UPDATE golang.orders
SET status = $1, updated_at = $2
WHERE id = $3
RETURNING updated_at
`
now := time.Now()
var updatedAt time.Time
err := r.db.QueryRow(ctx, query, status, now, id).Scan(&updatedAt)
if err != nil {
if errors.Is(err, pgx.ErrNoRows) {
return fmt.Errorf("订单不存在")
}
log.Printf("更新订单状态失败: %v", err)
return fmt.Errorf("更新订单状态失败")
}
return nil
}
func (r *orderRepository) Delete(ctx context.Context, id int) error {
query := `
DELETE FROM golang.orders
WHERE id = $1
`
result, err := r.db.Exec(ctx, query, id)
if err != nil {
log.Printf("删除订单失败: %v", err)
return fmt.Errorf("删除订单失败")
}
if result.RowsAffected() == 0 {
return fmt.Errorf("订单不存在")
}
return nil
}
func (r *orderRepository) List(ctx context.Context, limit, offset int) ([]*models.OrderResponse, error) {
query := `
SELECT o.id, o.user_id, u.username, o.amount, o.status, o.created_at, o.updated_at
FROM golang.orders o
JOIN golang.users u ON o.user_id = u.id
ORDER BY o.created_at DESC
LIMIT $1 OFFSET $2
`
rows, err := r.db.Query(ctx, query, limit, offset)
if err != nil {
log.Printf("查询订单列表失败: %v", err)
return nil, fmt.Errorf("查询订单列表失败")
}
defer rows.Close()
var orders []*models.OrderResponse
for rows.Next() {
order := &models.OrderResponse{}
err := rows.Scan(
&order.ID,
&order.UserID,
&order.Username,
&order.Amount,
&order.Status,
&order.CreatedAt,
&order.UpdatedAt,
)
if err != nil {
log.Printf("扫描订单响应数据失败: %v", err)
return nil, fmt.Errorf("获取订单响应数据失败")
}
orders = append(orders, order)
}
if err = rows.Err(); err != nil {
log.Printf("遍历订单响应列表时出错: %v", err)
return nil, fmt.Errorf("获取订单响应列表失败")
}
return orders, nil
}
func (r *orderRepository) Count(ctx context.Context) (int, error) {
query := `SELECT COUNT(*) FROM golang.orders`
var count int
err := r.db.QueryRow(ctx, query).Scan(&count)
if err != nil {
log.Printf("获取订单总数失败: %v", err)
return 0, fmt.Errorf("获取订单总数失败")
}
return count, nil
}
7. services/user_service.go
package services
import (
"context"
"fmt"
"go-PostgreSQL-demo/models"
"go-PostgreSQL-demo/repositories"
"go-PostgreSQL-demo/utils"
"log"
)
type UserService interface {
CreateUser(ctx context.Context, req *models.CreateUserRequest) (*models.UserResponse, error)
GetUserByID(ctx context.Context, id int) (*models.UserResponse, error)
UpdateUser(ctx context.Context, id int, req *models.UpdateUserRequest) (*models.UserResponse, error)
DeleteUser(ctx context.Context, id int) error
ListUsers(ctx context.Context, page, pageSize int) ([]*models.UserResponse, int, error)
Authenticate(ctx context.Context, email, password string) (*models.UserResponse, error)
}
type userService struct {
userRepo repositories.UserRepository
}
func NewUserService(userRepo repositories.UserRepository) UserService {
return &userService{
userRepo: userRepo,
}
}
func (s *userService) CreateUser(ctx context.Context, req *models.CreateUserRequest) (*models.UserResponse, error) {
// 验证用户是否已存在
_, err := s.userRepo.GetByEmail(ctx, req.Email)
if err == nil {
return nil, fmt.Errorf("邮箱已存在")
}
// 哈希密码
hashedPassword, err := utils.HashPassword(req.Password)
if err != nil {
log.Printf("密码哈希失败: %v", err)
return nil, fmt.Errorf("内部服务器错误")
}
// 创建用户
user := &models.User{
Username: req.Username,
Email: req.Email,
PasswordHash: hashedPassword,
}
if err := s.userRepo.Create(ctx, user); err != nil {
return nil, err
}
// 准备响应
return &models.UserResponse{
ID: user.ID,
Username: user.Username,
Email: user.Email,
CreatedAt: user.CreatedAt,
UpdatedAt: user.UpdatedAt,
}, nil
}
func (s *userService) GetUserByID(ctx context.Context, id int) (*models.UserResponse, error) {
user, err := s.userRepo.GetByID(ctx, id)
if err != nil {
return nil, err
}
return &models.UserResponse{
ID: user.ID,
Username: user.Username,
Email: user.Email,
CreatedAt: user.CreatedAt,
UpdatedAt: user.UpdatedAt,
}, nil
}
func (s *userService) UpdateUser(ctx context.Context, id int, req *models.UpdateUserRequest) (*models.UserResponse, error) {
// 获取现有用户
user, err := s.userRepo.GetByID(ctx, id)
if err != nil {
return nil, err
}
// 更新字段
if req.Username != "" {
user.Username = req.Username
}
if req.Email != "" {
// 检查新邮箱是否已被使用
existingUser, err := s.userRepo.GetByEmail(ctx, req.Email)
if err == nil && existingUser.ID != id {
return nil, fmt.Errorf("邮箱已被其他用户使用")
}
user.Email = req.Email
}
// 保存更新
if err := s.userRepo.Update(ctx, user); err != nil {
return nil, err
}
// 准备响应
return &models.UserResponse{
ID: user.ID,
Username: user.Username,
Email: user.Email,
CreatedAt: user.CreatedAt,
UpdatedAt: user.UpdatedAt,
}, nil
}
func (s *userService) DeleteUser(ctx context.Context, id int) error {
return s.userRepo.Delete(ctx, id)
}
func (s *userService) ListUsers(ctx context.Context, page, pageSize int) ([]*models.UserResponse, int, error) {
if page < 1 {
page = 1
}
if pageSize < 1 {
pageSize = 10
}
if pageSize > 100 {
pageSize = 100
}
offset := (page - 1) * pageSize
// 获取用户列表
users, err := s.userRepo.List(ctx, pageSize, offset)
if err != nil {
return nil, 0, err
}
// 获取总用户数
total, err := s.userRepo.Count(ctx)
if err != nil {
return nil, 0, err
}
// 转换为响应格式
responses := make([]*models.UserResponse, len(users))
for i, user := range users {
responses[i] = &models.UserResponse{
ID: user.ID,
Username: user.Username,
Email: user.Email,
CreatedAt: user.CreatedAt,
UpdatedAt: user.UpdatedAt,
}
}
return responses, total, nil
}
func (s *userService) Authenticate(ctx context.Context, email, password string) (*models.UserResponse, error) {
user, err := s.userRepo.GetByEmail(ctx, email)
if err != nil {
return nil, fmt.Errorf("无效的邮箱或密码")
}
// 验证密码
if !utils.CheckPasswordHash(password, user.PasswordHash) {
return nil, fmt.Errorf("无效的邮箱或密码")
}
return &models.UserResponse{
ID: user.ID,
Username: user.Username,
Email: user.Email,
CreatedAt: user.CreatedAt,
UpdatedAt: user.UpdatedAt,
}, nil
}
8. services/order_service.go
package services
import (
"context"
"fmt"
"go-PostgreSQL-demo/models"
"go-PostgreSQL-demo/repositories"
"log"
)
type OrderService interface {
CreateOrder(ctx context.Context, req *models.CreateOrderRequest) (*models.OrderResponse, error)
GetOrderByID(ctx context.Context, id int) (*models.OrderResponse, error)
GetOrdersByUserID(ctx context.Context, userID, page, pageSize int) ([]*models.OrderResponse, int, error)
UpdateOrderStatus(ctx context.Context, id int, status string) (*models.OrderResponse, error)
DeleteOrder(ctx context.Context, id int) error
ListOrders(ctx context.Context, page, pageSize int) ([]*models.OrderResponse, int, error)
}
type orderService struct {
orderRepo repositories.OrderRepository
userRepo repositories.UserRepository
}
func NewOrderService(orderRepo repositories.OrderRepository, userRepo repositories.UserRepository) OrderService {
return &orderService{
orderRepo: orderRepo,
userRepo: userRepo,
}
}
func (s *orderService) CreateOrder(ctx context.Context, req *models.CreateOrderRequest) (*models.OrderResponse, error) {
// 验证用户是否存在
_, err := s.userRepo.GetByID(ctx, req.UserID)
if err != nil {
return nil, fmt.Errorf("用户不存在")
}
// 创建订单
order := &models.Order{
UserID: req.UserID,
Amount: req.Amount,
Status: "pending", // 默认状态
}
if err := s.orderRepo.Create(ctx, order); err != nil {
return nil, err
}
// 获取用户信息用于响应
user, err := s.userRepo.GetByID(ctx, req.UserID)
if err != nil {
// 这里可以记录错误，但不应该阻止订单创建成功
log.Printf("获取用户信息失败，但订单已创建: %v", err)
}
// 准备响应
return &models.OrderResponse{
ID: order.ID,
UserID: order.UserID,
Username: user.Username,
Amount: order.Amount,
Status: order.Status,
CreatedAt: order.CreatedAt,
UpdatedAt: order.UpdatedAt,
}, nil
}
func (s *orderService) GetOrderByID(ctx context.Context, id int) (*models.OrderResponse, error) {
order, err := s.orderRepo.GetByID(ctx, id)
if err != nil {
return nil, err
}
// 获取用户信息
user, err := s.userRepo.GetByID(ctx, order.UserID)
if err != nil {
return nil, fmt.Errorf("获取用户信息失败")
}
return &models.OrderResponse{
ID: order.ID,
UserID: order.UserID,
Username: user.Username,
Amount: order.Amount,
Status: order.Status,
CreatedAt: order.CreatedAt,
UpdatedAt: order.UpdatedAt,
}, nil
}
func (s *orderService) GetOrdersByUserID(ctx context.Context, userID, page, pageSize int) ([]*models.OrderResponse, int, error) {
if page < 1 {
page = 1
}
if pageSize < 1 {
pageSize = 10
}
if pageSize > 100 {
pageSize = 100
}
offset := (page - 1) * pageSize
// 获取用户验证
_, err := s.userRepo.GetByID(ctx, userID)
if err != nil {
return nil, 0, fmt.Errorf("用户不存在")
}
// 获取订单列表
orders, err := s.orderRepo.GetByUserID(ctx, userID, pageSize, offset)
if err != nil {
return nil, 0, err
}
// 获取用户信息
user, err := s.userRepo.GetByID(ctx, userID)
if err != nil {
return nil, 0, fmt.Errorf("获取用户信息失败")
}
// 计算总订单数
total, err := s.orderRepo.Count(ctx)
if err != nil {
return nil, 0, err
}
// 转换为响应格式
responses := make([]*models.OrderResponse, len(orders))
for i, order := range orders {
responses[i] = &models.OrderResponse{
ID: order.ID,
UserID: order.UserID,
Username: user.Username,
Amount: order.Amount,
Status: order.Status,
CreatedAt: order.CreatedAt,
UpdatedAt: order.UpdatedAt,
}
}
return responses, total, nil
}
func (s *orderService) UpdateOrderStatus(ctx context.Context, id int, status string) (*models.OrderResponse, error) {
// 更新状态
if err := s.orderRepo.UpdateStatus(ctx, id, status); err != nil {
return nil, err
}
// 获取更新后的订单
order, err := s.orderRepo.GetByID(ctx, id)
if err != nil {
return nil, err
}
// 获取用户信息
user, err := s.userRepo.GetByID(ctx, order.UserID)
if err != nil {
return nil, fmt.Errorf("获取用户信息失败")
}
return &models.OrderResponse{
ID: order.ID,
UserID: order.UserID,
Username: user.Username,
Amount: order.Amount,
Status: order.Status,
CreatedAt: order.CreatedAt,
UpdatedAt: order.UpdatedAt,
}, nil
}
func (s *orderService) DeleteOrder(ctx context.Context, id int) error {
return s.orderRepo.Delete(ctx, id)
}
func (s *orderService) ListOrders(ctx context.Context, page, pageSize int) ([]*models.OrderResponse, int, error) {
if page < 1 {
page = 1
}
if pageSize < 1 {
pageSize = 10
}
if pageSize > 100 {
pageSize = 100
}
offset := (page - 1) * pageSize
// 获取订单列表
orders, err := s.orderRepo.List(ctx, pageSize, offset)
if err != nil {
return nil, 0, err
}
// 获取总订单数
total, err := s.orderRepo.Count(ctx)
if err != nil {
return nil, 0, err
}
return orders, total, nil
}
9. .env
# 数据库配置
DB_HOST=localhost
DB_PORT=5432
DB_USER=postgres
DB_PASSWORD=postgres
DB_NAME=app_golang
DB_SCHEMA=golang
DB_MAX_CONNECTIONS=20
# 服务器配置
SERVER_PORT=8080
10. main.go
package main
import (
"context"
"fmt"
"github.com/gin-gonic/gin"
"go-PostgreSQL-demo/config"
"go-PostgreSQL-demo/models"
"go-PostgreSQL-demo/repositories"
"go-PostgreSQL-demo/services"
"log"
"net/http"
"os"
"os/signal"
"syscall"
"time"
)
func main() {
// 初始化数据库
config.InitDB()
defer config.CloseDB()
go config.MonitorConnectionPool()
// 初始化仓库
userRepo := repositories.NewUserRepository()
orderRepo := repositories.NewOrderRepository()
// 初始化服务
userService := services.NewUserService(userRepo)
orderService := services.NewOrderService(orderRepo, userRepo)
// 设置Gin
r := gin.Default()
// 用户路由
userRoutes := r.Group("/api/users")
{
userRoutes.POST("/", func(c *gin.Context) {
var req models.CreateUserRequest
if err := c.ShouldBindJSON(&req); err != nil {
c.JSON(http.StatusBadRequest, gin.H{"error": "无效的请求数据"})
return
}
user, err := userService.CreateUser(c.Request.Context(), &req)
if err != nil {
c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
return
}
c.JSON(http.StatusCreated, user)
})
userRoutes.GET("/:id", func(c *gin.Context) {
id := c.Param("id")
// 验证ID
// ...
fmt.Println(id)
user, err := userService.GetUserByID(c.Request.Context(), 1) // TODO: 转换id
if err != nil {
c.JSON(http.StatusNotFound, gin.H{"error": err.Error()})
return
}
c.JSON(http.StatusOK, user)
})
userRoutes.PUT("/:id", func(c *gin.Context) {
id := c.Param("id")
// 验证ID
// ...
fmt.Println(id)
var req models.UpdateUserRequest
if err := c.ShouldBindJSON(&req); err != nil {
c.JSON(http.StatusBadRequest, gin.H{"error": "无效的请求数据"})
return
}
user, err := userService.UpdateUser(c.Request.Context(), 1, &req) // TODO: 转换id
if err != nil {
c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
return
}
c.JSON(http.StatusOK, user)
})
userRoutes.DELETE("/:id", func(c *gin.Context) {
id := c.Param("id")
// 验证ID
// ...
fmt.Println(id)
if err := userService.DeleteUser(c.Request.Context(), 1); err != nil { // TODO: 转换id
c.JSON(http.StatusNotFound, gin.H{"error": err.Error()})
return
}
c.JSON(http.StatusOK, gin.H{"message": "用户已删除"})
})
userRoutes.GET("/", func(c *gin.Context) {
page := c.DefaultQuery("page", "1")
pageSize := c.DefaultQuery("page_size", "10")
// 验证和转换
// ...
fmt.Println(page, pageSize)
users, total, err := userService.ListUsers(c.Request.Context(), 1, 10) // TODO: 转换参数
if err != nil {
c.JSON(http.StatusInternalServerError, gin.H{"error": "获取用户列表失败"})
return
}
c.JSON(http.StatusOK, gin.H{
"users": users,
"total": total,
"page": 1,
"pages": (total + 9) / 10, // 简化的总页数计算
})
})
}
// 订单路由
orderRoutes := r.Group("/api/orders")
{
orderRoutes.POST("/", func(c *gin.Context) {
var req models.CreateOrderRequest
if err := c.ShouldBindJSON(&req); err != nil {
c.JSON(http.StatusBadRequest, gin.H{"error": "无效的请求数据"})
return
}
order, err := orderService.CreateOrder(c.Request.Context(), &req)
if err != nil {
c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
return
}
c.JSON(http.StatusCreated, order)
})
orderRoutes.GET("/:id", func(c *gin.Context) {
id := c.Param("id")
// 验证ID
// ...
fmt.Println(id)
order, err := orderService.GetOrderByID(c.Request.Context(), 1) // TODO: 转换id
if err != nil {
c.JSON(http.StatusNotFound, gin.H{"error": err.Error()})
return
}
c.JSON(http.StatusOK, order)
})
orderRoutes.PUT("/:id/status", func(c *gin.Context) {
id := c.Param("id")
status := c.Query("status")
// 验证参数
// ...
fmt.Println(id)
order, err := orderService.UpdateOrderStatus(c.Request.Context(), 1, status) // TODO: 转换id
if err != nil {
c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
return
}
c.JSON(http.StatusOK, order)
})
orderRoutes.DELETE("/:id", func(c *gin.Context) {
id := c.Param("id")
// 验证ID
// ...
fmt.Println(id)
if err := orderService.DeleteOrder(c.Request.Context(), 1); err != nil { // TODO: 转换id
c.JSON(http.StatusNotFound, gin.H{"error": err.Error()})
return
}
c.JSON(http.StatusOK, gin.H{"message": "订单已删除"})
})
orderRoutes.GET("/", func(c *gin.Context) {
page := c.DefaultQuery("page", "1")
pageSize := c.DefaultQuery("page_size", "10")
// 验证和转换
// ...
fmt.Println(page, pageSize)
orders, total, err := orderService.ListOrders(c.Request.Context(), 1, 10) // TODO: 转换参数
if err != nil {
c.JSON(http.StatusInternalServerError, gin.H{"error": "获取订单列表失败"})
return
}
c.JSON(http.StatusOK, gin.H{
"orders": orders,
"total": total,
"page": 1,
"pages": (total + 9) / 10, // 简化的总页数计算
})
})
orderRoutes.GET("/user/:user_id", func(c *gin.Context) {
userID := c.Param("user_id")
page := c.DefaultQuery("page", "1")
pageSize := c.DefaultQuery("page_size", "10")
// 验证和转换
// ...
fmt.Println(userID, page, pageSize)
orders, total, err := orderService.GetOrdersByUserID(c.Request.Context(), 1, 1, 10) // TODO: 转换参数
if err != nil {
c.JSON(http.StatusInternalServerError, gin.H{"error": "获取用户订单失败"})
return
}
c.JSON(http.StatusOK, gin.H{
"orders": orders,
"total": total,
"page": 1,
"pages": (total + 9) / 10, // 简化的总页数计算
})
})
}
// 启动服务器
port := os.Getenv("SERVER_PORT")
if port == "" {
port = "8080"
}
srv := &http.Server{
Addr: ":" + port,
Handler: r,
}
// 在goroutine中启动服务器
go func() {
log.Printf("服务器启动于端口 %s", port)
if err := srv.ListenAndServe(); err != nil && err != http.ErrServerClosed {
log.Fatalf("服务器启动失败: %v", err)
}
}()
// 等待中断信号优雅关闭服务器
quit := make(chan os.Signal, 1)
signal.Notify(quit, syscall.SIGINT, syscall.SIGTERM)
<-quit
log.Println("正在关闭服务器...")
// 设置5秒超时
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()
if err := srv.Shutdown(ctx); err != nil {
log.Printf("服务器优雅关闭失败: %v", err)
}
log.Println("服务器已关闭")
}
curl -X POST http://localhost:8080/api/users \
-H "Content-Type: application/json" \
-d '{
"username": "johndoe",
"email": "john.doe@example.com",
"password": "securepassword123"
}'
curl -X GET http://localhost:8080/api/users/1
curl -X PUT http://localhost:8080/api/users/1 \
-H "Content-Type: application/json" \
-d '{
"username": "johnupdated",
"email": "john.updated@example.com"
}'
curl -X GET "http://localhost:8080/api/users?page=1&page_size=10"
curl -X DELETE http://localhost:8080/api/users/1
curl -X POST http://localhost:8080/api/orders \
-H "Content-Type: application/json" \
-d '{
"user_id": 1,
"amount": 99.99
}'
curl -X GET http://localhost:8080/api/orders/1
curl -X PUT "http://localhost:8080/api/orders/1/status?status=completed"
curl -X GET "http://localhost:8080/api/orders?page=1&page_size=10"
curl -X GET "http://localhost:8080/api/orders?page=1&page_size=10"
五、高级特性
1. 事务处理示例
在需要跨表操作时，可以添加事务支持：
// 在order_service.go中添加
func (s *orderService) CreateOrderWithTransaction(ctx context.Context, req *models.CreateOrderRequest) (*models.OrderResponse, error) {
tx, err := config.GetDB().Begin(ctx)
if err != nil {
return nil, fmt.Errorf("启动事务失败")
}
defer tx.Rollback(ctx)
// 在事务中执行操作
// 1. 检查用户是否存在
var exists bool
err = tx.QueryRow(ctx, "SELECT EXISTS(SELECT 1 FROM golang.users WHERE id = $1)", req.UserID).Scan(&exists)
if err != nil {
return nil, fmt.Errorf("验证用户失败")
}
if !exists {
return nil, fmt.Errorf("用户不存在")
}
// 2. 创建订单
order := &models.Order{
UserID: req.UserID,
Amount: req.Amount,
Status: "pending",
}
query := `
INSERT INTO golang.orders (user_id, amount, status, created_at, updated_at)
VALUES ($1, $2, $3, $4, $5)
RETURNING id, created_at, updated_at
`
now := time.Now()
err = tx.QueryRow(ctx, query, order.UserID, order.Amount, order.Status, now, now).Scan(
&order.ID,
&order.CreatedAt,
&order.UpdatedAt,
)
if err != nil {
return nil, fmt.Errorf("创建订单失败")
}
// 3. 提交事务
if err := tx.Commit(ctx); err != nil {
return nil, fmt.Errorf("事务提交失败")
}
// 获取用户信息用于响应
user, err := s.userRepo.GetByID(ctx, req.UserID)
if err != nil {
log.Printf("获取用户信息失败，但订单已创建: %v", err)
}
return &models.OrderResponse{
ID: order.ID,
UserID: order.UserID,
Username: user.Username,
Amount: order.Amount,
Status: order.Status,
CreatedAt: order.CreatedAt,
UpdatedAt: order.UpdatedAt,
}, nil
}
2. 连接池监控
// 在config/database.go中添加
func MonitorConnectionPool() {
ticker := time.NewTicker(1 * time.Minute)
defer ticker.Stop()
for range ticker.C {
stats := dbPool.Stat()
log.Printf("连接池状态 - 总连接: %d, 空闲连接: %d, 正在使用: %d",
stats.TotalConns(), stats.IdleConns(), stats.AcquiredConns())
}
}
// 在main.go中启动监控
go config.MonitorConnectionPool()
六、性能优化建议
- 批处理操作：对于大量数据操作，使用批量插入/更新
- 索引优化：为常查询的字段创建索引
- 连接池配置：根据应用负载调整连接池大小
- 使用预编译语句：对于频繁执行的查询
- 监控慢查询：启用PostgreSQL的慢查询日志
- 读写分离：对读多写少的场景，可以配置读写分离
七、常见问题解决
- 连接泄漏：确保所有查询后关闭Rows
- 死锁：注意事务中操作表的顺序
- 时区问题：统一应用和数据库的时区设置
- OOM问题：限制查询结果集大小，避免一次性加载过多数据
- SQL注入：始终使用参数化查询，避免拼接SQL
八、总结
本文详细介绍了Go语言与PostgreSQL（golang schema）的集成方案，包含：
- 完整的项目结构设计
- 数据库连接池配置
- CRUD操作实现
- 事务处理
- 错误处理
- 密码加密
- 分页处理
- API设计
通过这种分层架构（模型层、仓库层、服务层），代码具有高可维护性和可测试性。实际项目中，还可以添加缓存层、日志监控、指标收集等进一步增强系统能力。
阅读建议
评论
匿名评论
隐私政策
你无需删除空行，直接评论以获取最佳展示效果
